Basic SQL Exercises--A classic example of elective course

To manage the job training information, establish 3 tables:

S (S#,SN,SD,SA) S#,sn,sd,sa respectively represents the student number, the student name, the unit, the Student Age C (C#,CN) C#,CN respectively represents the course number, the course name SC (s#,c#,g) S#,c#,g respectively represents the student number, the elective course number, the study result

　　

1. Use standard SQL nested statements to query the student number and name of the elective course named ' Tax base '

--Implementation code:

Select SN,SD from S Where [s#] In (     Select [s#] from     c,sc     Where c.[c#]=sc.[c#] and cn=n ' tax base ')

　　

2. Use standard SQL nested statements to query the student name and affiliation of the elective course number ' C2 '

--Implementation code:

Select s.sn,s.sd from S,SC Where s.[s#]=sc.[s#] and sc.[c#]= ' C2 '

3. Use standard SQL nested statements to query the name and affiliation of the student who does not take the course number ' C5 '

--Implementation code:

Select SN,SD from S where [s#] not in (     Select [s#] from     SC     Where [c#]= ' C5 ')

　　

4. Use standard SQL nested statements to query the name and affiliation of the student taking all courses

--Implementation code:

Select SN,SD from S Where [s#] In (     Select [s#] from     SC right JOIN C to sc.[c#]=c.[c#]         GROUP by [s#] have COU NT (*) =count ([s#]))            

　　

5. Check the number of participants who have enrolled in the course

--Implementation code:

Select Number of participants =count (DISTINCT [s#]) from SC

　　

6. Enquiry of more than 5 students and their respective units of elective courses

--Implementation code:

Select SN,SD from S Where [s#] In (     Select [s#] from     SC     GROUP by [s#] have COUNT (DISTINCT [C #]) >5)

　　

Topic 2:

Problem Description:

S (sno,sname) student relationship. SNO for the school number, SNAME for the name C (cno,cname,cteacher) curriculum relationship. CNO is the course number, the CNAME is the course name, and Cteacher is the class teacher SC (sno,cno,scgrade) Elective course relationship. Scgrade as a result

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

--Implementation code:

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

　　

2. List of the names of students with two or more (including two) failed courses and their average scores

--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 have     COUNT (DISTINCT CNO) >=2) A Whe Re S.sno=a.sno and SC. Sno=a.sno     GROUP by S.sno,s.sname

　　

3. List the names of all students who have studied the course "1" and have studied "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 ') the GROUP by SNO have COUNT (DISTINCT CNO) =2) SC Where s.sno=sc. SNO

　　

4. List the number of all students with "1" class score higher than "2" class

--Implementation code:

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 academic numbers of all students with the "1" grade higher than the "2" class and their results in "1" and "2" classes

--Implementation code:

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