today a friend issued a topic for me, I put this topic out, so that people look at the joint study. Title:known relational pattern:
1, S (sno,sname) student relations. Sno for school number, sname for name
2, C (cno,cname,cteacher) curriculum relationship CNO As course number, CNAME as course name, Cteacher as Instructor
3, SC (sno,cno,scgrade) Elective course 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
2. Names of students with two or more (including two) failed courses and their average scores are listed
3. Names of all students who have studied the course "1" and who have studied "2"
4. List the number of all students with "1" class score higher than the "2" Student's course.
5. List the scores of all students with "1" class scores higher than the "2" class and their "1" and "2" lessons
Problem Solving: 1. Find out the names of all students who have not been enrolled in the "Li Ming" teacher course
Select Sname as student name from s where not Exists (SELECT * from C, Sc Where c.cno = Sc.cno and cteacher = ' Li Ming ' and sc.sno = S.sno); Select Sname as student name From s where Sno not in (Select Sno from C, Sc Where c.cno = Sc.cno and cteacher = ' Li Ming '); /c13>
2. List of the names of students with two or more (including two) failed courses and their average scores
Select S.sno as student number, s.sname as student name, AVG (Sc.scgrade) as average score from S, Sc Where sc.sno = s.sno and Sc.sno in (Select sc.sno from Sc Where sc.scgrade < Group by Sc.sno Have Count (*) > 2) Group by S.sno, S.sname;
3. List the names of all students who have studied the course "01" and have studied "02"Select S.sno as student number, s.sname as student name from S where Sno in (select Sc.sno as student number from C,SC where C.cno=sc.cno and C.cno In (' Sno ', ' a ') ' GROUP BY ' has count (distinct sc.cno) =2);4. List the number of all students with "01" Class score higher than "02" Class
Select Sc1.sno as student number from SC as sc1,c as c1,sc as sc2,c as C2where sc1.cno=c1.cno and c1.cno= ' ' and S C2.cno=c2.cno and c2.cno= 'on ' andSc1.scgrade>sc2.scgrade Group by Sc1.sno; 5. List the academic numbers of all students with the "01" Grade higher than the "02" class and their results in "01" and "02" classes
Select Sc1.sno as student number, sc1.scgrade as No1grade, Sc2.scgrade as No2gradefromSC as sc1,c as c1,sc as SC2, C as C2where sc1.cno=c1.cno and c1.cno= ' sc2.cno=c2.cno ' and c2.cno= '"and sc1.scgrade> Sc2.scgrade GROUP by Sc1.sno;
