Use of group by, count, and inner join queries in SQL

Reference Problem description:
There are three tables:
Student Information table S (sno, sname)
Course information table C (cno, cname, cteacher)
Student-course relationship table SC (sno, cno, scroce)
Question 1: find out the names of all students who have not taken the course taught by Mr. Li Ming.
Question 2: The name of two or more failed students is displayed.
Question 3: find out the names of students who have selected both course "1" and course "2 ".

Main test: group by, inner join, having filtering, and use of the count function
Group by groups result sets and uses having to filter data,

SQL code

1. Drop table S, SC, C
2. Create table S (
3. Sno varchar (15), -- Student ID
4. Sname varchar (15) -- Student name
5. )
6. Create table C (
7. Cno varchar (15), -- course No.
8. Cname varchar (15), -- Course name
9. Cteacher varchar (15)-Instructor
10. )
11. Create table SC (
12. Sno varchar (15), -- Student ID
13. Cno varchar (15), -- course No.
14. Scroce float -- score
15. )
17. Select * from S;
18. Delete from S;
19. Insert into S (sno, sname) values ('s001', 'Jerry ');
20. Insert into S (sno, sname) values ('s002 ', 'Tom ');
21. Insert into S (sno, sname) values ('s003 ', 'jason ');
23. Delete from C;
24. Select * from C;
25. Insert into C (cno, cname, cteacher) values ('c001', '1', 'lilim ');
26. Insert into C (cno, cname, cteacher) values ('c002 ', '2', 'Li Ming 0 ');
27. Insert into C (cno, cname, cteacher) values ('c003 ', '3', 'Li Ming 1 ');
28. Insert into C (cno, cname, cteacher) values ('c004 ', '4', 'Li Ming 2 ');
30. Delete from SC;
31. Select * from SC;
32. Insert into SC (sno, cno, scroce) values ('s001', 'c001', 40 );
33. Insert into SC (sno, cno, scroce) values ('s001', 'c002', 50 );
34. Insert into SC (sno, cno, scroce) values ('s002', 'c001', 50 );
35. Insert into SC (sno, cno, scroce) values ('s002 ', 'c003', 50 );
37. Select
38. A. sno, a. sname, B. cno
39. From S as
40. Inner join SC as B on B. sno = a. sno
41. Where not exists (
42. Select * from SC where sno = B. sno and cno in (select cno from C where cteacher = 'liming ')
43. );
46. -- Two failing students
47. Select
48. A. sname
49. From S as
50. Inner join SC as B on B. sno = a. sno
51. Where B. scroce <60
52. Group by a. sname
53. Having count (sname)> 1;
55. -- Names of students who have selected course 1 and course 2
57. Select
58. A. sname
59. From S
60. Inner join SC B on A. Sno = B. SnO
61. Inner join C on C. CNO = B. CNO
62. Where B. CNO in ('c001', 'c002 ')
63. Group by A. sname
64. Having count (A. sname) = 2;