SQL statement Exercise 50 questions (I wrote the answer, not finished)

Student (sid,sname,sage,ssex) Student table
Course (Cid,cname,tid) timetable
SC (sid,cid,score) score table
Teacher (tid,tname) Teacher table
Practice content:
1. Check the number of all students who have a "1" course with a higher grade than "a 2" course;
SELECT A.sid
From SC as a, SC as b
Where a.cid in (SELECT Cid
From Course
Where cname= "some 1")
and B.cid in (SELECT Cid
From Course
Where cname= "some 2")
and A.score>b.score
and A.sid=b.sid

2. Search for students with average scores greater than 60 points, and average scores;
SELECT Sid, AVG (Score)
From SC
GROUP by Sid
Having AVG (score) >60

3. Check all students ' student number, name, number of courses selected, total
SELECT Sid,
Sname,
(SELECT count (*)
From SC
WHERE SC. SID=STUDENT.SID),
(SELECT count (score)
From SC
WHERE SC. SID=STUDENT.SID),
From Student;
Using the junction table:
SELECT Student.sid,
Student.sname,
Count (SC. Cid)
SUM (sc.score)
From Student left OUTER JOIN SC
On STUDENT.SID=SC. Sid
GROUP by Student.sid,student.sname;

4. Query the number of teachers whose surname is "Li";
SELECT COUNT (*)
From Teacher
WHERE tname like "Li%";

5. Inquiry did not learn the "cotyledons" teacher class students of the school number, name;

SELECT distinc Student.sid, DISTINCT student.sname
From Student,course,sc,teacher
WHERE STUDENT.SID=SC. Sid
and SC. Cid=course.cid
and Course.tid=teacher.tid
and tname! = "Cotyledons";

6. The query learns "'" and has also learned the number and name of the students of the course;
Working with sub-queries
SELECT Sid,sname
From Student
WHERE Sid in (SELECT SID
From SC
WHERE cid= "1")
and SID in (SELECT SID
From SC
WHERE cid= "2");
Working with junction tables
SELECT Student.sid,student.sname
From STUDENT,SC as a,sc as B
WHERE A.sid =student.sid
and B.sid =student.sid
and a.cid= "1"
and b.cid= "2";

7. Inquire about the students ' School number and name of all the classes taught by the "cotyledons" teacher;
SELECT Sid,sname
From Student
WHERE Sid in (SELECT SID
From Sc,course,teacher
WHERE SC. Cid=course.cid
and Course.tid=teacher.tid
and teacher.tname= "Cotyledons"
GROUP by Sid
Having count (CID) = (SELECT count (CID)
From Course,teacher
WHERE Course,tid=teacher.tid
and teacher.tname= "cotyledons"));

8. Find the number and name of all students who have a lower grade than the course number "" for the course number "";
SELECT A.sid,a.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 A.score<b.score

9. Check the student's number and name of all the students whose course scores are less than 60 points;
SELECT DISTINCT sid,distinct Sname
From (SELECT sid,sname,count (Cid)
From STUDENT,SC
WHERE STUDENT.SID=SC. Sid
and sc.score<60
GROUP by Sid,sname
Having count (CID) = (SELECT count (CID)
From STUDENT,SC
WHERE STUDENT.SID=SC. Sid
GROUP by Sid,sname)) A;
Another solution:
SELECT Sid,sname
From Student
WHERE Sid not in (SELECT sid,sname
From STUDENT,SC
WHERE STUDENT.SID=SC. Sid
and sc.score>60);
10. Inquire about the student's number and name of the students who have not studied all the classes;
SELECT DISTINCT sid,distinct Sname
From (SELECT sid,sname,count (Cid)
From STUDENT,SC
WHERE STUDENT.SID=SC. Sid
GROUP by Sid,sname
Having count (CID) < (SELECT count (CID)
From SC)) A;
11. Inquiry at least one class with the student number is "" The students learn the same student number and name;

12. Inquiry at least to learn the number of students to "" All of the classmates of the other students study number and name;

13. Change the performance of the class "Cotyledons" in the "SC" table to the average grade of the course;

14. Inquiries and the "" number of students to study the course of the same class of other students and the name of the school;

15. Delete the SC table record of the "cotyledons" teacher class;

16. Insert some records into the SC table, which require the following conditions: not numbered "" The student number of the course, the average grade of the course;

17. Average grades from high to low show all students "database", "Enterprise Management", "English" of the course results, as shown in the following form: Student ID, database, business management, English, effective course number, effective average score;

18. Find the highest and lowest scores for each section: shown in the following form: Course ID, highest score, lowest score;

19. From low to high and the percentage of passing rates from highest to lowest in each section

26. Check the number of students enrolled in each course

27. Find out the number and name of all students who have only one course of study

32. Check the average score for each course, the results are ranked in ascending order by average, and the average grade is the same, descending by course number

37. Check for a failing course and arrange it from the largest to the smaller course number

38. Check the student's number and name for the course number and the course score above;

40. Check the names and achievements of the students who have the highest scores in the courses offered by the "cotyledons" teacher

41. Check each course and the corresponding number of electives

44. Count the number of students enrolled in each course (more than a person's course is counted). Require the output of the course number and the number of elective, query results in descending order of numbers, query results in descending order of numbers, if the number is the same, in ascending order by course number

45. Retrieve the student number of at least two elective courses

The use of rownum
Check the results of the second to fourth place in all grades.

47. Inquire about the names of students who have not learned any of the courses taught by the "cotyledons" teacher

48. Check the number of students with two or more failed courses and their average scores

49. Search for "" course score is less than, in descending order by fractions of the student number

50. Delete the results of the "classmate" course

SQL statement Exercise 50 questions (I wrote the answer, not finished)