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';