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
- Drop table S, SC, C
- Create table S (
- Sno varchar (15), -- Student ID
- Sname varchar (15) -- Student name
- )
- Create table C (
- Cno varchar (15), -- course No.
- Cname varchar (15), -- Course name
- Cteacher varchar (15)-Instructor
- )
- Create table SC (
- Sno varchar (15), -- Student ID
- Cno varchar (15), -- course No.
- Scroce float -- score
- )
- Select * from S;
- Delete from S;
- Insert into S (sno, sname) values ('s001', 'Jerry ');
- Insert into S (sno, sname) values ('s002 ', 'Tom ');
- Insert into S (sno, sname) values ('s003 ', 'jason ');
- Delete from C;
- Select * from C;
- Insert into C (cno, cname, cteacher) values ('c001', '1', 'lilim ');
- Insert into C (cno, cname, cteacher) values ('c002 ', '2', 'Li Ming 0 ');
- Insert into C (cno, cname, cteacher) values ('c003 ', '3', 'Li Ming 1 ');
- Insert into C (cno, cname, cteacher) values ('c004 ', '4', 'Li Ming 2 ');
- Delete from SC;
- Select * from SC;
- Insert into SC (sno, cno, scroce) values ('s001', 'c001', 40 );
- Insert into SC (sno, cno, scroce) values ('s001', 'c002', 50 );
- Insert into SC (sno, cno, scroce) values ('s002', 'c001', 50 );
- Insert into SC (sno, cno, scroce) values ('s002 ', 'c003', 50 );
- Select
- A. sno, a. sname, B. cno
- From S as
- Inner join SC as B on B. sno = a. sno
- Where not exists (
- Select * from SC where sno = B. sno and cno in (select cno from C where cteacher = 'liming ')
- );
- -- Two failing students
- Select
- A. sname
- From S as
- Inner join SC as B on B. sno = a. sno
- Where B. scroce <60
- Group by a. sname
- Having count (sname)> 1;
- -- Names of students who have selected course 1 and course 2
- Select
- A. sname
- From S
- Inner join SC B on A. Sno = B. SnO
- Inner join C on C. CNO = B. CNO
- Where B. CNO in ('c001', 'c002 ')
- Group by A. sname
- Having count (A. sname) = 2;