Experiment 4 Data Query Experiment

Use studentdb; /******************** Experiment 4 Data Query experiment ************ * *****************/-- 1. query the basic information of a student. Select * from student; -- 2. query the basic information of "CS" students. Select * from student where sdept = 'cs '; -- 3. query the student ID and name of the CS students aged between 19 and 21. Select SnO, sname from studentwhere sdept = 'cs 'and Sage not between 19 and 21; -- 4. find the maximum age; select max (SAGE) maximum age from student; -- 5. find the student with the largest age in Cs and display his student ID and name. Select SnO, sname from studentwhere sdept = 'cs 'and sage = (select max (SAGE) from student where sdept = 'cs '); -- 6. find the oldest student in each department and display his/her student ID and name. *************-related subqueries are used in this question, which is relatively difficult, exercise update student set sage = 23 Where SnO = '000000'; select * from student; select SnO, sname, sdeptfrom student awhere sage = (select max (SAGE) from student B where. sdept = B. sdept); -- 7. count the number of students in the CS series; select count (*) Number of students in the computer series from studentwhere sdept = 'cs '; -- 8. count the number of students in each department. The results are sorted in ascending order. Select sdept, count (*) cntfrom SC, studentwhere student. sno = SC. sno group by sdeptorder by CNT; -- 9. the average age of the students in each department is calculated by the Department, and the results are sorted in descending order. Select sdept, AVG (SAGE) agefrom SC, studentwhere student. sno = SC. snogroup by sdeptorder by age DESC; -- 10. query the course name of each course; select cname from course; -- 11. query the course name and score of a course without a first-mover. Select cname, ccreditfrom coursewhere cpno is null; -- 12. count the total number of credits for a course without a prior course; select sum (ccredit) from coursewhere cpno is null; -- 13. count the number of optional courses, credits, and average scores of each student. Select SC. sno, count (SC. CNO), sum (ccredit), AVG (grade) from SC, coursewhere SC. CNO = course. cnogroup by SnO; -- 14. count the number of students enrolled in each course and the average score of each course. Select CNO, count (CNO), AVG (grade) from scgroup by CNO; -- 15. find out the students with an average score of more than 85. The results are grouped by department and sorted in ascending order of the average score. select student. sno, AVG (grade) avg_grade, sdeptfrom SC, studentwhere SC. sno = student. snogroup by student. sno, sdepthaving AVG (grade)> 85 order by avg_grade; select student. sno, AVG (grade) avg_grade, sdeptfrom SC join studentusing (SNO) group by student. sno, sdepthaving avg_grade> 85 order by AVG (grade); -- note that clustering functions can be used after order by -- 16. query the student ID and name of the course "1" or "2"; ********* -- Method 1 select distinct student. sno, snamefrom student, scwhere SC. sno = student. snO and (CNO = '1' or CNO = '2'); -- method 2 select distinct student. sno, snamefrom student, scwhere SC. sno = student. snO and CNO in ('1', '2'); -- Method 3 select distinct student. sno, snamefrom student, scwhere SC. sno = student. snO and CNO = '1' unionselect distinct student. sno, snamefrom student, scwhere SC. sno = student. snO and CNO = '2'; -- 17. query the student ID and name of the course "1" and "2"; ********* select distinct student. sno, snamefrom student, scwhere SC. sno = student. snO and CNO = '1' and student. snO IN (select distinct SnO from SC where CNO = '2');/* -- Intersect has no select distinct student in MySQL. sno, snamefrom student, scwhere SC. sno = student. snO and CNO = '1' intersectselect distinct student. sno, snamefrom student, scwhere SC. sno = student. snO and CNO = '2'; */-- 18. query the student ID, name, and score of the course named "Database" and whose score is below 60; insert into SC values ('123', '1', 58 ); select student. sno, sname, gradefrom SC, course, studentwhere SC. sno = student. snO and SC. CNO = course. CNO and cname = 'database' and Grade <60; delete from SC where SnO = '000000' and CNO = '1'; -- 19. query the student information (student ID, name, course number, course name, and score) of each selected course. select student. sno, sname, course. CNO, cname, gradefrom student, course, scwhere student. sno = SC. snO and course. CNO = SC. CNO; select SnO, sname, CNO, cname, gradefrom student join SC using (SNO) join course using (CNO); -- 20. query the basic information of students who do not have an elective course. ************** -- Method 1 select student. * From studentwhere SnO not in (select SnO from SC); -- method 2 select student. * From studentwhere not exists (select * from SC where SnO = student. snO); -- 21. query the student ID of more than three courses; select SnO from scgroup by snohaving count (SNO)> 2; -- 22. query the scores of an optional course with at least one student ID of 85 or higher; select SnO from scgroup by snohaving max (grade)> 85; -- 23. query Number of students whose scores are above 85; select SnO from scgroup by snohaving min (grade)> 85; -- 24. query the student ID with an average score of more than 85 for the Elective Course; select SnO from scgroup by snohaving AVG (grade)> 85;