SQL Server exercise Question 2

Question 2

Problem description:
Known link mode:
S (SNO, sname) Student Relationship. Sno is the student ID, and sname is the name
C (CNO, cname, cteacher) Course relationship. CNO is the course number, cname is the course name, And cteacher is the course teacher
SC (SNO, CNO, scgrade) Course Selection relationship. Scgrade is the score

The following five processes are required:
1. Find out the names of all students who have not taken the course taught by Mr. Li Ming.
2. List the names and average scores of students whose two or more courses fail.
3. List the names of all students who have learned course 1 and course 2
4. List the student IDs of all students whose scores are higher than those of students whose scores are equal to those of the course No.
5. List the student IDs of all students whose scores are higher than those of Lesson 2 and their scores of Lesson 1 and Lesson 2

1. Find out the names of all students who have not taken the course taught by Mr. Li Ming.

-- Implementation code:
Select sname from S
Where not exists (
Select * from SC, c
Where SC. CNO = C. CNO
And C. cteacher = 'lilim'
And SC. Sno = S. SnO)

2. List the names and average scores of students whose two or more courses fail.

-- Implementation code:
Select S. Sno, S. sname, avg_scgrade = AVG (SC. scgrade)
From S, SC ,(
Select SnO
From SC
Where scgrade <60
Group by SnO
Having count (distinct CNO)> = 2
) A where S. Sno = A. SnO and SC. Sno = A. SnO
Group by S. Sno, S. sname

3. List the names of all students who have learned course 1 and course 2

-- Implementation code:
Select S. Sno, S. sname
From S ,(
Select SC. SnO
From SC, c
Where SC. CNO = C. CNO
And C. cname in ('1', '2 ')
Group by SnO
Having count (distinct CNO) = 2
) SC where S. Sno = SC. SnO

4. List the student IDs of all students whose scores are higher than those of students whose scores are equal to those of the course No.

-- Implementation code:
Select S. Sno, S. sname
From S, SC SC1, SC SC2
Where sc1.cno = '1'
And sc2.sno = '2'
And sc1.cno = S. CNO
And sc1.scgrade> sc2.scgrade

5. List the student IDs of all students whose scores are higher than those of Lesson 2 and their scores of Lesson 1 and Lesson 2

-- Implementation code:
Select sc1.sno, [score for Lesson 1] = sc1.scgrade, [score for Lesson 2] = sc2.scgrade
From SC SC1, SC SC2
Where sc1.cno = '1'
And sc2.cno = '2'
And sc1.sno = sc2.sno
And sc1.scgrade> sc2.scgrade