Tuesday, June 7, 2016

INSERT
Tbl_lecturer
INSERT INTO `tbl_lecturer`(`lecturer_id`, `lecturer_name`, `course_id`) VALUES ('','Jenelyn','1');
INSERT INTO `tbl_lecturer`(`lecturer_id`, `lecturer_name`, `course_id`) VALUES ('','Dawn','2');
INSERT INTO `tbl_lecturer`(`lecturer_id`, `lecturer_name`, `course_id`) VALUES ('','Mareden','3');
INSERT INTO `tbl_lecturer`(`lecturer_id`, `lecturer_name`, `course_id`) VALUES ('','Lorenzo','4');
INSERT INTO `tbl_lecturer`(`lecturer_id`, `lecturer_name`, `course_id`) VALUES ('','Maya','5');
Results:
Tbl_course
INSERT INTO `tbl_course`(`course_id`, `course_name`) VALUES ('','HND');
INSERT INTO `tbl_course`(`course_id`, `course_name`) VALUES ('','DIT');
INSERT INTO `tbl_course`(`course_id`, `course_name`) VALUES ('','L4DC');
INSERT INTO `tbl_course`(`course_id`, `course_name`) VALUES ('','L5DC');
INSERT INTO `tbl_course`(`course_id`, `course_name`) VALUES ('','L3DC');

Results:



Tbl_course
INSERT INTO `tbl_studentinfo`(`std_id`, `std_name`, `std_address`, `std_contact`, `std_age`, `std_gender`, `course_id`) VALUES ('','Jim','St. George Strt.','8908900','12','Male','1');
INSERT INTO `tbl_studentinfo`(`std_id`, `std_name`, `std_address`, `std_contact`, `std_age`, `std_gender`, `course_id`) VALUES ('','Jim','St. Suleiman Rd.','8918911','21','Male','2');
INSERT INTO `tbl_studentinfo`(`std_id`, `std_name`, `std_address`, `std_contact`, `std_age`, `std_gender`, `course_id`) VALUES ('','Tim','Jerudong Road Lot 2','8768761','14','Male','3');
INSERT INTO `tbl_studentinfo`(`std_id`, `std_name`, `std_address`, `std_contact`, `std_age`, `std_gender`, `course_id`) VALUES ('','Karena','Malayan Strt.','8128122','20','Female','4');
INSERT INTO `tbl_studentinfo`(`std_id`, `std_name`, `std_address`, `std_contact`, `std_age`, `std_gender`, `course_id`) VALUES ('','Jennipher','Malayan Strt','8908912','21','Female','5');
Results:



SELECT QUERY
1.    Retrieve all data of the students.
SELECT `std_id`, `std_name`, `std_address`, `std_contact`, `std_age`, `std_gender`, `course_id` FROM `tbl_studentinfo`;
2.    Retrieve all data of different courses
SELECT `course_id`, `course_name` FROM `tbl_course`;



3.    Retrieve only the name and contact of all students.
SELECT `std_name`, `std_contact`  FROM `tbl_studentinfo`;



4.    Retrieve all teacher’s names and student’s names.
SELECT `tbl_studentinfo`.`std_name`, `tbl_lecturer`.`lecturer_name` FROM
`tbl_lecturer`, `tbl_studentinfo`;



5.    Retrieve all teacher’s names and student names with the same course.
SELECT `tbl_studentinfo`.`std_name`, `tbl_lecturer`.`lecturer_name` FROM
`tbl_lecturer`, `tbl_studentinfo`
where `tbl_studentinfo`.`course_id` = `tbl_lecturer`.`course_id`;



6.    Retrieve student’s name and course’s names with the course ID.
SELECT `tbl_studentinfo`.`std_name`, `tbl_course`.`course_name` FROM
 `tbl_studentinfo`, `tbl_course`
where `tbl_studentinfo`.`course_id` = `tbl_course`.`course_id`;
7.    Retrieve unique values in student table
SELECT DISTINCT `std_name` FROM `tbl_studentinfo`;



8.    Retrieve student’s name and age with the age with less than 21.
SELECT `std_name`, `std_age` FROM `tbl_studentinfo` WHERE `std_age`< '21';
9.    Retrieve student’s name with the age less than 25 and the gender is male
SELECT `std_name`, `std_age` FROM `tbl_studentinfo` WHERE `std_age`< '25'
AND `std_gender` = 'male';
10. Retrieve all lecturer’s name which has the letter ‘n’
SELECT * FROM `tbl_lecturer` WHERE `lecturer_name` LIKE '%j%'



11. Sorting Student table by age
SELECT * FROM `tbl_studentinfo` ORDER BY `std_age`;
12. Sorting Student table by name ASCENDING order
SELECT * FROM `tbl_studentinfo` ORDER BY `tbl_studentinfo`.`std_name` ASC
13. Sorting student table by7 name in Descending order
SELECT * FROM `tbl_studentinfo` ORDER BY `tbl_studentinfo`.`std_name` DESC



14. Retrieve lecturer’s name LIMIT by three (only shows first three in the table).
SELECT `lecturer_name` FROM `tbl_lecturer` LIMIT 3;
15. Retrieve Student’s name LIMIT by three (only shows first three name in table).
SELECT `std_name` FROM `tbl_studentinfo` LIMIT 3;
16. Retrieve Student’s name LIMIT by three IN ASCENDING order
SELECT `std_name` FROM `tbl_studentinfo`ORDER by `tbl_studentinfo`.`std_name` ASC LIMIT 3;



17. Update student table SET a student name to ANY name
UPDATE `tbl_studentinfo` SET `std_name` = 'jOHNNY'
WHERE `STD_ID` = '1';


18. Update COURSE HND into HND in Computing
UPDATE `tbl_course` SET `course_name` = 'HND in Computing'
WHERE `course_name` = 'HND';
19. Delete A COURSE
DELETE FROM `tbl_course` WHERE `course_id` = '1';

No comments:

Post a Comment