[Reprint] SQL statement exercises 50 questions, SQL statements

[Reprint] SQL statement exercises 50 questions, SQL statements

Student (Sid, Sname, Sage, Ssex) Student table

Course (Cid, Cname, Tid) curriculum SC (Sid, Cid, score) classroom table Teacher (Tid, Tname) Instructor table exercise content: 1. query the student ID of all students whose score is higher than that of the "1" course. SELECT. sid FROM (SELECT sid, score from SC where cid = 1) a, (SELECT sid, score from SC where cid = 3) B WHERE. score> B. score AND. sid = B. sid; this topic knowledge point, nested query and alias for the retrieved table 2. query the student ID and average score of students whose average score is greater than 60. SELECT sid, avg (score) FROM SC GROUP BY sid having avg (score)> 60, the group by statement is used in combination with the aggregate function to GROUP result sets based on one or more columns. Group by cannot be followed by where. having replaces where3. query the Student ID, name, number of course selections, and total score of all students. SELECT Student. sid, Student. sname, count (SC. cid), sum (score) FROM Student left Outer join SC on Student. sid = SC. cid group by Student. sid, Sname4. query the number of teachers surnamed "Li"; select count (teacher. tid) from teacher where teacher. tname like 'Lee % '5. query the Student ID and name of the Student who has not learned the course "ye ping"; SELECT Student. sid, Student. sname FROM Student WHERE sid not in (SELECT distinct (SC. sid) from SC, Course, Teacher where SC. cid = Course. cid AND Teacher. id = Course. tid AND Teacher. tname = 'peiping '); distinct is used to remove duplicates. query the student ID and name of the student who has learned the course "'''" and has also learned the course number '''. select. SID,. SNAME from (select student. SNAME, student. SID from student, course, SC where cname = 'C ++ 'and SC. sid = student. sid and SC. cid = course. cid) a, (select student. SNAME, student. SID from student, course, SC where cname = 'English 'and SC. sid = student. sid and SC. cid = co Urse. cid) B where. sid = B. sid; standard answer (but it seems difficult) 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'); exists searches for data in the Set, AND as stands for alias 7. query the student ID and name of all the students taught by instructor ye ping. select. sid,. sname from (select student. sid, student. sname from student, teacher, course, SC where teacher. TNAME = 'yang Weiwei 'and teacher. tid = cours E. tid and course. cid = SC. cid and student. sid = SC. sid) a standard answer: SELECT sid, Sname FROM Student WHERE sid in (SELECT sid from SC, Course, Teacher where SC. cid = Course. cid AND Teacher. tid = Course. tid AND Teacher. tname = 'yang weiwei' group by sid having count (SC. cid) = (SELECT count (cid) FROM Course, Teacher WHERE Teacher. tid = Course. tid AND Tname = 'yang Weiwei ') 8. query the student ID and name of all students whose score is lower than the course number. select. sid,. sname from (select Student. SID, student. sname, SC. SCORE from student, SC where student. sid = SC. sid and SC. cid = 1) a, (select student. SID, student. sname, SC. score from student, SC where student. sid = SC. sid and SC. cid = 2) B where. score <B. score and. sid = B. sid standard answer: SELECT sid, Sname FROM (SELECT Student. sid, Student. sname, score, (SELECT score from SC SC _2 WHERE SC _2.sid = Student. sid AND SC _2.cid = 1) score2 FROM Student, SCWHERE Stude Nt. sid = SC. sid AND cid = 1) S_2 WHERE score2 <score; 9. query the Student ID and name of all students whose scores are less than the score. SELECT sid, Sname FROM Student WHERE sid not in (SELECT Student. sid FROM Student, SC WHERE Student. sid = SC. sid AND score> 60); first, find the knowledge point greater than 60, AND then not in is 10 less than 60. query the Student ID and name of the Student who has not completed all the courses; SELECT Student. sid, Student. sname FROM Student, SC WHERE Student. sid = SC. sid group by Student. sid, Student. sname having count (cid) <(SELECT count (cid) FR OM Course); 11. query the student ID and name of at least one course and the student whose student ID is "". 12. query the student ID and name of all the other students whose student ID is "". SELECT student. sid, student. sname FROM Student, SC WHERE Student. sid = SC. sid AND cid in (SELECT cid from SC where sid = 1) knowledge point, SELECT sid, Sname FROM Student, SC WHERE Student. sid = SC. sid AND cid in (SELECT cid from SC WHERE sid = 1) is incorrect because from is followed by two tables, it is not clear which table contains the sid and sname. Therefore, the error message is "columns not explicitly defined" 13. change the average score of the Course taught by "ye ping" in the "SC" table; update SC set score = (select avg (score) from SC, course, teacher where course. cid = SC. cid and course. tid = teacher. tid and teacher. tname = 'yang Weiwei ') 14. query the student ID and name of the student whose course is exactly the same as that of ""; SELECT sid from SC where cid in (SELECT cid from SC WHERE sid = 6) group by sid having count (*) = (SELECT count (*) from SC WHERE sid = 6); this knowledge point is determined BY the number of 15. delete the SC table record for learning "ye ping"; delete from SC s where s. cid in (select c. cid from teacher t, course c Where t. tid = c. tid and tname = 'plum ') This topic knowledge point, nested query can be considered for distribution, first find out the id of the class that the plum teacher has handed in, and then delete the value of those IDs 16. insert some records into the SC table. These records must meet the following requirements: the student ID and average score of the course that has not passed the "" course; Insert into SC SELECT sid, 2, (SELECT avg (score) from SC where cid = 2) FROM Student WHERE sid not in (SELECT sid from SC WHERE cid = 2); 17. displays the "Database", "enterprise management", and "English" course scores of all students based on the average score, in the form of student ID, database, enterprise Management, English, number of valid courses, and average effective score (not available) 18. query the highest and lowest scores of each subject: the course ID, highest score, lowest score; select cid As course number, max (score) as highest score, min (score) as lowest score from SC group by cid standard answer (but not running well) SELECT L. cid As course ID, L. score AS highest score, R. score AS distinct from SC l, SC AS R WHERE L. cid = R. cid and l. score = (select max (IL. score) from SC AS IL, Student AS IM WHERE L. cid = IL. cid and im. sid = IL. sid group by il. cid) and r. score = (select min (IR. score) from SC AS IR WHERE R. cid = IR. cid group by ir. cid); 19. the average score of each subject is from low to high and the percentage of pass rate is from high To a lower order 26. query the number of students selected for each course select SC. cid, count (SC. sid) from SC, course where SC. cid = course. cid group by SC. cid27. query the student ID and name of all students who have selected only one course. select SC. sid, Student. sname, count (cid) AS course number from SC, Student where SC. sid = Student. sid group by SC. sid, Student. sname having count (cid) = 1; 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 the SELECT Cid and Avg (score) are sorted in descending order by course number) from SC GROUP BY cid ORDER BY Avg (score), cid DESC; 37. query failed courses and follow the course number from SELECT cid in ascending ORDER, sid FROM SC WHERE score <60 ORDER BY cid 38. query the student ID and name of the student whose course number is higher than the score. select student. sid, student. sname from SC, student where SC. cid = 1 and SC. score> 60 and SC. sid = student. sid40. query the names of the students with the highest scores and the select student scores of the students who take the course "ye ping. sname, SC. score from SC, student, teacher, course c where teacher. tname = 'plum 'and teacher. tid = c. tid and c. cid = SC. cid and SC. sid = student. sid and SC. score = (select max (SC Ore) from SC where SC. cid = c. cid) 41. query each course and the number of electives select SC. cid, count (SC. sid) from SC, student where SC. sid = student. sid group by SC. cid 43. query the first two of the best scores for each course. count the number of students in each course (only when the number of students exceeds the number of students ). The course number and the 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 SC is sorted in ascending order of the course number. cid, count (SC. cid) from SC, course where SC. cid = course. cid group by SC. cid order by SC. cid desc45. retrieve SELECT sid FROM SC group by sid having count (*)> = 2 rownum usage query all the scores from the second to the Fourth: select * from (select rownum p, t. score from (SELECT s. score FROM SC s ORDER BY score desc) t) tt where tt. p> 1 and tt. p <5 47. query the names of students who have not completed any of the courses taught by "ye ping". select distinct sid from SC where sid not in (select SC. sid from SC, course, teacher where SC. cid = course. cid and course. tid = teacher. tid and teacher. tname = 'yang Weiwei ') 48. query the student ID and average score of two or more failed courses 49. retrieve the select SC students whose score is smaller than or in descending order of the score. sid from SC, course where SC. cid = course. cid and course. cname = 'java' and SC. score <9050. delete the score of the "" course of "" student "from SC where sid = 1 and cid = 1