SQL example (2)

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 (stu_no, cla_no, score) Course Selection relationship. Score

Initialization table:

Insert into S values ("001", "zhangsan"); insert into S values ("002", "padern"); insert into S values ("003 ", "wangwu"); insert into S values ("004", "Xiaohong"); insert into S values ("005", "Linda "); insert into S values ("006", "Lucy"); insert into S values ("007", "Lily"); insert into S values ("008 ", "Jim"); insert into S values ("009", "green"); insert into S values ("010", "brone "); insert into S values ("011", "peng"); insert into S values ("012", "Chern"); insert into S values ("013 ", "Tom"); insert into S values ("014", "wei"); insert into SC values ("003", "1002", 83 ); insert into SC values ("003", "1004", 56); insert into SC values ("003", "1005", 92 ); insert into SC values ("003", "1007", 91); insert into SC values ("003", "1009", 60 ); insert into SC values ("004", "1005", 91); insert into SC values ("004", "1006", 90 ); insert into SC values ("004", "1003", 92); insert into SC values ("004", "1001", 76 ); insert into SC values ("004", "1007", 88); insert into SC values ("004", "1009", 82 ); insert into SC values ("005", "1004", 91); insert into SC values ("005", "1006", 56 ); insert into SC values ("005", "1008", 54); insert into SC values ("005", "1009", 67 ); insert into SC values ("006", "1002", 91); insert into SC values ("006", "1003", 89 ); insert into SC values ("006", "1005", 88); insert into SC values ("006", "1001", 78 ); insert into SC values ("006", "1004", 91); insert into SC values ("006", "1006", 100 ); insert into SC values ("006", "1007", 80); insert into SC values ("006", "1009", 53 ); insert into SC values ("007", "1003", 91); insert into SC values ("007", "1005", 80 ); insert into SC values ("007", "1007", 95); insert into SC values ("007", "1009", 66 ); insert into SC values ("007", "1008", 71); insert into SC values ("008", "1002", 95 ); insert into SC values ("008", "1004", 90); insert into SC values ("008", "1006", 95 ); insert into SC values ("009", "1001", 90); insert into SC values ("009", "1002", 99 ); insert into SC values ("010", "1002", 92); insert into SC values ("010", "1006", 96 ); insert into SC values ("010", "1007", 91); insert into SC values ("010", "1009", 70 ); insert into SC values ("011", "1001", 91); insert into SC values ("011", "1002", 80 ); insert into SC values ("011", "1004", 75); insert into SC values ("011", "1006", 76 ); insert into SC values ("011", "1008", 41); insert into SC values ("011", "1009", 50 ); insert into SC values ("012", "1001", 90); insert into SC values ("012", "1002", 91 ); insert into SC values ("012", "1003", 75); insert into SC values ("013", "1006", 66 ); insert into SC values ("013", "1008", 41); insert into SC values ("013", "1009", 50 ); insert into SC values ("014", "1004", 81); insert into SC values ("014", "1006", 86 ); insert into SC values ("014", "1007", 91); insert into C values ("1001", "English", "Zhao Cheng "); insert into C values ("1002", "Mathematics", "Chen Yu"); insert into C values ("1003", "", "Liu Bang "); insert into C values ("1004", "physical", "Li shaofen"); insert into C values ("1005", "Geography", "Xu Chun "); insert into C values ("1006", "chemistry", "Liu Qing"); insert into C values ("1007", "creature", "Song Jiang "); insert into C values ("1008", "Politics", "Yang Ke"); insert into C values ("1009", "History", "Shen kuha ");

Problem:

1. Find out the names of all students who have not taken the course taught by instructor Song Jiang.

Select sname from s where not exists (select * from SC, C where SC. cla_no = C. CNO and ctecher = "" and SC. stu_no = S. SnO)

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

Wrong. This syntax only counts the average scores of the courses on which he is assigned the subject.

select SNAME,avgscore from S,(select stu_No,AVG(score) as avgscore from scwhere score<60group by stu_Nohaving count(distinct cla_No)>=2) bwhere S.SNO=b.stu_Noselect SNAME, SNO, AVG(score) from S,SCwhere S.SNO in(select stu_No from scwhere score<60group by stu_Nohaving count(distinct cla_No)>=2)and S.SNO=SC.stu_Nogroup by stu_Noselect SNAME,SNO,AVG(SC.score) from S,SC,(select stu_No from scwhere score<60group by stu_Nohaving count(distinct cla_No)>=2) bwhere S.SNO=SC.stu_No and b.stu_No=S.SNOgroup by SNO

3. List the names of all students who have learned course 1001 and course 1002

select SNAME from S,(select stu_No from SCwhere cla_No in ("1001","1002")group by stu_No having count(distinct cla_No)=2) bwhere S.SNO=b.stu_Noselect SNAME from Swhere S.SNO in(select stu_No from SCwhere cla_No in ("1001","1002")group by stu_No having count(distinct cla_No)=2)

4. List the student IDs of all students whose score is higher than that of the course number 002

select stu_No from sc awhere a.score>(select score from scwhere sc.stu_No="002" and sc.cla_No="1001")and a.cla_No="1001"

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

select sc1.stu_No,sc1.score cla1001,sc2.score cla1002 from SC sc1,SC sc2where sc1.stu_No=sc2.stu_Noand sc1.score>sc2.score and sc1.cla_No="1001"and sc2.cla_No="1002"