SQL question set (1)

Student (S #, Sname, Sage, Ssex) Student table Course (C #, Cname, T #) curriculum SC (S #, C #, score) Student table Teacher (T #, tname) Instructor table questions: 1. query the student ID of all students whose score is higher than that of the "002" course; select. S # from (select s #, score from SC where C # = '001') a, (select s #, score from SC where C # = '002 ') B where. score> B. score and. s # = B. s #; 2. query the student ID and average score of students whose average score is greater than 60; select S #, avg (score) from SC group by S # having avg (score)> 60; 3. query the student ID, name, number of course selections, and total score of all students; select Studen T. S #, Student. sname, count (SC. C #), sum (score) from Student left Outer join SC on Student. S # = SC. S # group by Student. S #, Sname 4. query the number of teachers surnamed "Li"; select count (distinct (Tname) from Teacher where Tname like 'Li % '; 5. query the Student ID and name of the Student who has not learned the course "ye ping"; select Student. S #, Student. sname from Student where S # not in (select distinct (SC. S #) from SC, Course, Teacher where SC. C # = Course. C # and Teacher. T # = Course. T # and Teacher. tnam E = 'peiping '); 6. query the Student ID and name of the Student who has learned "001" and has also learned "002"; select Student. S #, Student. sname from Student, SC where Student. S # = SC. S # and SC. C # = '001' and exists (Select * from SC as SC _2 where SC _2.S # = SC. S # and SC _2.C # = '002 '); 7. query the student ID and name of all the students who have learned the course taught by "ye ping"; select S #, sname from Student where S # in (select S # from SC, Course, Teacher where SC. C # = Course. C # and Teacher. T # = Course. T # and Teacher. tname = 'epping' group by S # havi Ng count (SC. C #) = (select count (C #) from Course, Teacher where Teacher. T # = Course. T # and Tname = 'peiping '). 8. query the student ID and name of all students whose course number is "002" lower than the course number "001; select S #, Sname from (select Student. S #, Student. sname, score, (select score from SC SC _2 where SC _2.S # = Student. S # and SC _2.C # = '002') score2 from Student, SC where Student. S # = SC. S # and C # = '001') S_2 where score2 <score; 9. query the student ID and name of all students whose score is less than 60; select S #, Sname From Student where S # not in (select Student. S # from Student, SC where S.S # = SC. S # and score> 60); 10. query the Student ID and name of all students who have not completed the course; select Student. S #, Student. sname from Student, SC where Student. S # = SC. S # group by Student. S #, Student. sname having count (C #) <(select count (C #) from Course ); 11. query the Student ID and name of at least one course with the Student ID "1001". select S #, Sname from Student, SC where Student. S # = SC. S # and C # in select C # from SC wher E S # = '000000'; 12. query the student ID and name of all the other students whose student ID is "001". select distinct SC. S #, Sname from Student, SC where Student. S # = SC. S # and C # in (select C # from SC where S # = '001 '); 13. Change the average score of the Course taught by instructor ye Ping in the SC table. update SC set score = (select avg (SC _2.score) from SC SC _2 where SC _2.C # = SC. C #) from Course, Teacher where Course. C # = SC. C # and Course. T # = Teacher. T # and Teacher. tname = 'peiping '); 14. query and "1002" students take exactly the same course Number and name; select S # from SC where C # in (select C # from SC where S # = '000000') group by S # having count (*) = (select count (*) from SC where S # = '000000'); 15. Delete the SC Table Record of the course "ye ping"; Delect SC from course, teacher where Course. C # = SC. C # and Course. T # = Teacher. T # and Tname = 'peiping '; 16. insert some records into the SC table. These records must meet the following requirements: average score of students whose student ID is No. 003, whose student ID is 2, and whose student ID is 003; Insert SC select S #, '002 ', (Select avg (score) from SC where C # = '002') from Student Where S # not in (Select S # from SC where C # = '002 '); 17. Show the "Database", "enterprise management", and "English" course scores of all students based on average scores in the following format: Student ID ,, database, enterprise management, English, number of valid courses, valid average score select s # as student ID, (SELECT score from SC where SC. S # = t. S # and c # = '004 ') AS database, (SELECT score from SC where SC. S # = t. S # and c # = '001') AS enterprise management, (SELECT score from SC where SC. S # = t. S # and c # = '006 ') AS English, COUNT (*) AS number of valid courses, AVG (t. score) AS average score from SC AS t GROUP S # order by avg (t. score) 18. query the highest score and lowest score of each subject. The score is displayed as follows: course ID, highest score, and lowest score select l. C # As course ID, L. score AS highest score, R. score AS distinct from SC l, SC AS R WHERE L. C # = R.C # and L. score = (select max (IL. score) from SC AS IL, Student AS IM WHERE L. C # = IL. C # and IM. S # = IL. S # group by il. C #) and r. score = (select min (IR. score) from SC AS IR WHERE R.C # = IR. C # group by ir. C #); 19. SELECT t from high to low in sequence based on the percentage of average scores of all subjects from low to high and pass rate. C # AS course number, Max (course. cname) AS course name, isnull (AVG (score), 0) AS average score, 100 * SUM (case when isnull (score, 0)> = 60 THEN 1 ELSE 0 END) /COUNT (*) AS pass percentage from SC T, Course where t. C # = course. C # group by t. C # order by 100 * SUM (case when isnull (score, 0)> = 60 THEN 1 ELSE 0 END)/COUNT (*) DESC 20. query the average score and pass percentage of the following courses (displayed in "1 line"): Enterprise Management (001), Marx (002), OO & UML (003 ), database (004) select sum (case when c # = '001' THEN score ELSE 0 END)/SUM (CASE C # WHEN '001' THEN 1 ELSE 0 END) AS average enterprise management score, 100 * SUM (case when c # = '001' AND score> = 60 THEN 1 ELSE 0 END) /SUM (case when c # = '001' THEN 1 ELSE 0 END) AS enterprise management pass percentage, SUM (case when c # = '002' THEN score ELSE 0 END) /SUM (case c # WHEN '002' THEN 1 ELSE 0 END) AS Marx average score, 100 * SUM (case when c # = '002' AND score> = 60 THEN 1 ELSE 0 END)/SUM (case when c # = '002' THEN 1 ELSE 0 END) AS Marx pass percentage, SUM (CA Se when c # = '003 'THEN score ELSE 0 END)/SUM (case c # WHEN '003' THEN 1 ELSE 0 END) as uml average score, 100 * SUM (case when c # = '003 'AND score> = 60 THEN 1 ELSE 0 END)/SUM (case when c # = '003' THEN 1 ELSE 0 END) as uml pass percentage, SUM (case when c # = '004 'THEN score ELSE 0 END)/SUM (case c # WHEN '004' THEN 1 ELSE 0 END) average score of AS database, 100 * SUM (case when c # = '004 'AND score> = 60 THEN 1 ELSE 0 END)/SUM (CASE WHEN C # = '004 'THEN 1 ELSE 0 END) AS database pass percentage from SC 21, query the average score of different courses taught by different teachers FROM high to low show SELECT max (Z. T #) AS instructor ID, MAX (Z. tname) AS instructor name, C. C # AS course ID, MAX (C. cname) AS Course name, AVG (Score) AS average Score from SC AS T, Course AS C, Teacher AS Z where T.C # = C. C # and C. T # = Z. T # group by c. C # order by avg (Score) DESC 22. query the scores of 3rd to 6th students in the following courses: Enterprise Management (001), Marx (002), UML (003 ), database (004) [student ID], [Student name], enterprise management, Marx, UML, database, average score SELECT DISTINCT Top 3 SC. S # As Student ID, Student. sname AS student name, T1.score AS enterprise management, T2.score AS Marx, T3.score as uml, T4.score AS database, ISNULL (T1.score, 0) + ISNULL (T2.score, 0) + ISNULL (T3.score, 0) + ISNULL (T4.score, 0) as total score FROM Student, SC left join SC as T1 on SC. S # = T1.S # AND T1.C # = '001' left join SC as T2 on SC. S # = T2.S # AND T2.C # = '002' left join SC as T3 on SC. S # = T3.S # AND T3.C # = '003 'left join SC AS T4 O N SC. S # = T4.S # AND T4.C # = '004 'WHERE student. S # = SC. S # and ISNULL (T1.score, 0) + ISNULL (T2.score, 0) + ISNULL (T3.score, 0) + ISNULL (T4.score, 0) not in (select distinct top 15 with ties isnull (T1.score, 0) + ISNULL (T2.score, 0) + ISNULL (T3.score, 0) + ISNULL (T4.score, 0) FROM SC left join SC AS T1 ON SC. S # = T1.S # AND T1.C # = 'k1 'left join SC AS T2 ON SC. S # = T2.S # AND T2.C # = 'k2' left join SC AS T3 O N SC. S # = T3.S # AND T3.C # = 'k3' left join SC AS T4 ON SC. S # = T4.S # AND T4.C # = 'k4 'order by isnull (T1.score, 0) + ISNULL (T2.score, 0) + ISNULL (T3.score, 0) + ISNULL (T4.score, 0) DESC); 23. Print the score of each subject in Statistics. Number of students in each score segment: course ID, course name, [100-85], [85-70], [70-60], [<60] select SC. C # as course ID, Cname as course name, SUM (case when score BETWEEN 85 AND 100 THEN 1 ELSE 0 END) AS [100-85], SUM (case when score BETWEEN 70 AND 85 THEN 1 ELSE 0 END) AS [85-70], SUM (case when score BETWEEN 60 AND 70 THEN 1 ELSE 0 END) AS [70-60], SUM (case when score <60 THEN 1 ELSE 0 END) AS [60-] from SC, Course where SC. C # = Course. C # group by SC. C #, Cname; 24. query the average score of students and their rankings SELECT 1 + (select count (distinct average score) FROM (select s #, AVG (score) AS average score from SC GROUP BY S #) AS T1 WHERE average score> T2. average score) as ranking, S # as student ID, average score FROM (SELECT S #, AVG (score) Average score from SC GROUP BY S #) AS T2 ORDER BY average score desc; 25. query the top three records of each score (excluding the parallel score) SELECT t1.S # as student ID, t1.C # as course ID, Score as score from SC t1 WHERE score IN (select top 3 score from SC WHERE t1.C # = C # ORDER BY Score DESC) ORDER BY t1.C #; 26. query the number of students selected for each course select c #, count (S #) from SC group by C #; 27. query the student ID and name of all students who have selected only one course. select SC. S #, Student. sname, count (C #) AS course number from SC, Student where SC. S # = Student. S # group by SC. S #, Student. sname having count (C #) = 1; 28. query boys and girls Select count (Ssex) as boys from Student group by Ssex having Ssex = 'male '; select count (Ssex) as number of girls from Student group by Ssex having Ssex = 'female '; 29. query the SELECT Sname FROM students WHERE Sname like 'zhang % '; 30. query the same-Name same-name Student list and count the number of students with the same name: select Sname, count (*) from Student group by Sname having count (*)> 1; 31. Names of students born on April 9 (Note: Type of the Sage column in Student table) Is datetime) select Sname, CONVERT (char (11), DATEPART (year, Sage) as age from student where CONVERT (char (11), DATEPART (year, Sage )) = '000000'; 32. query the average scores of each course. The results are sorted in ascending order of average scores. The average scores are the same, and Select C # is sorted in descending order of course numbers #, avg (score) from SC group by C # order by Avg (score), C # DESC; 33. query the student ID, name, and average score of all students whose average score is greater than 85 select Sname, SC. S #, avg (score) from Student, SC where Student. S # = SC. S # group by SC. S #, Sname having avg (score)> 85; 34. query Course Select Sname, isnull (score, 0) from Student, SC, Course where SC. S # = Student. S # and SC. C # = Course. C # and Course. cname = 'database' and score <60; 35. query the Course selections of all students; select SC. S #, SC. C #, Sname, Cname from SC, Student, Course where SC. S # = Student. S # and SC. C # = Course. C #; 36. query the names, course names, and scores of any course whose scores are over 70; SELECT distinct student. S #, student. sname, SC. C #, SC. score FROM student, SC where SC. score> = 70 AND SC. S # = student. S #; 37. query failed courses and arrange the courses from large to small by course number select c # from SC where scor e <60 order by C #; 38. query the student ID and name of the student whose course number is 003 and whose score is higher than 80; select SC. S #, Student. sname from SC, Student where SC. S # = Student. S # and Score> 80 and C # = '003 '; 39. select count (*) from SC for the number of students selected for the course; 40. query the names of the students with the highest scores and their scores of the students who take the course taught by "ye ping. sname, score from Student, SC, Course C, Teacher where Student. S # = SC. S # and SC. C # = C. C # and C. T # = Teacher. T # and Teacher. tname = 'peiping 'and SC. score = (select max (score) from SC where C # = C. C #); 41. query each course and select count (*) from SC group by C #; 42. query the student ID, course number, and student score of the same course score. select distinct A.S #, B. score from SC a, SC B where. score = B. score and. C # <> B .C #; 43. query the first two SELECT t1.S # as student IDs, t1.C # as course IDs, score as score from SC t1 WHERE score IN (select top 2 Score from SC WHERE t1.C # = C # ORDER BY score DESC) order by t1.C #; 44. count the number of students in each course (only when the number of students exceeds 10 ). The course number and number of electives must be output. The query results are sorted in descending order of the number of students. If the number of students is the same, select C # as course number in ascending order of course number, count (*) as number of people from SC group by C # order by count (*) desc, c #45. Search for the student ID select S # from SC group by s # having count (*)> = 2 46. query the Course number and name of the Course selected by all students select C #, Cname from Course where C # in (select c # from SC group by c #) 47. query the Student names of any Course taught by instructor ye ping. select Sname from Student where S # not in (select S # from Course, Teacher, SC where Course. T # = Teacher. T # and SC. C # = course. C # and Tname = 'peiping '); 48. query the student ID and average score of two or more failed courses select S #, avg (isnull (score, 0 )) from SC where S # in (select S # from SC where score <60 group by S # having count (*)> 2) group by S #; 49. Retrieve "004" course scores less than 60. select S # from SC where C # = '004 'and score in descending order of scores <60 order by score desc; 50. delete the score of "001" course of "002" from SC where S # = '001' and C # = '001 ';