SQL Database Pen Questions

Known relational pattern:

S (sno,sname) student relationship. SNO for school number, SNAME for name

C (cno,cname,cteacher) course relationship. CNO as course number, CNAME as course name, Cteacher as Instructor

SC (sno,cno,scgrade) course selection relationship. Scgrade as a result

The following 5 processes are required:

1. Find out the names of all students who have not been enrolled in the "Li Ming" teacher course

Select SNAME from S
Where not EXISTS (
Select * from Sc,c
Where SC. Cno=c.cno
And Cname= ' Li Ming '
and SC. SNO=S.SNO)

2. Names of students with two or more (including two) failed courses and their average scores are listed

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 all students who have studied the "1" course and learned the "2" course in the name
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 "1" class scores than the "2" students who have high grades of the student's school number  

Select S.sno,s.sname
From S, (
Select SC1. SNO
From SC sc1,c c1,sc sc2,c C2
Where SC1. Cno=c1. CNO and c1.name= ' 1 '
and SC2. Cno=c2. CNO and C2.name= ' 2 '
and SC1. SCGRADE>SC2. Scgrade
) SC Where S.SNO=SC. SNO

5. List the scores of all students with "1" class scores higher than the "2" class and their "1" and "2" lessons
Select s.sno,s.sname,sc.[1 results],sc.[2 class score]
From S, (
Select SC1. The result of Sno,[1 class is]=SC1. The result of Scgrade,[2 class is]=SC2. Scgrade
From SC sc1,c c1,sc sc2,c C2
Where SC1. Cno=c1. CNO and c1.name= ' 1 '
and SC2. Cno=c2. CNO and C2.name= ' 2 '
and SC1. SCGRADE>SC2. Scgrade
) SC Where S.SNO=SC. SNO

SQL Database Pen Questions