计算机专业计算机技能测试.docx
计算机专业计算机技能测试.计算机专业计算机技能测试 student （学生）：sno sname421周远行123王义平120王大力119李维203林美course （课程）：eno semestercs-110秋cs-201春cs221秋ee-122秋ee-201春sc （选课）snoenograde421cs-11090421ee-201100123ee-12291123ee-20183120cs-221NULL119cs-11072119cs-20165203cs-11082.5203cs-20180203ee-20175用SQL语言写出下列查询：1、查询春季学期的课程号2、查询至少有一门课程成绩大于等于90的学生姓名。3、查询有3个学生选修的课程号。探索性问题：1、查询选课门数唯一的学生的学号（例如，120号学生选1门课，203号学生选3门课，其余学生都选了2门课，因此120号和203号是选课门数唯一的学生）。2、查询EE-201课成绩第3名的学生的学号。要求：运行成功，并生成正确结果，打印SQL语句。计算机专业计算机技能测试Create Table SC(Sno CHAR (3) NOT NULL,Cno CHAR (6) NOT NULL,Grade SMALLINT CHECK (Grade IS NULL) OR (Grade BETWEEN 0 AND 100),PRIMARY KEY(Sno, Cno);INSERT INTO SC VALUES ('421',' CS-110,90);INSERT INTO SC VALUES ('421',' EE-20f ,100);INSERT INTO SC VALUES ('123',' EE-122*,91);INSERT INTO SC VALUES ('123',' EE-20 V ,83);INSERT INTO SC VALUES ('120',' CS-221', NULL);INSERT INTO SC VALUES (*119*,* CS-110,72);INSERT INTO SC VALUESC 119，,>CS-201',65);INSERT INTO SC VALUES ('203',' CST10',82.5);INSERT INTO SC VALUES ('203',' CS-201',80);INSERT INTO SC VALUES ('203',' EE-201',75);探索性问题：1、查询选课门数唯一的学生的学号（例如，120号学生选1门课，203号学生选3门课，其余学生都选了2门课，因此120号和203号是选课门数唯一的学生）。select snofrom (select ctfrom (select sno,count (*)from scgrade by sno ) as t (sno,ct)group by ct having count(*)=1) as tl(ct),(select sno,count(*) as ctfrom scgroup by sno) as t2(sno,ct)where tl. ct=t2. ct2、查询EE-201课成绩第3名的学生的学号。要求：运行成功，并生成正确的结果，打印SQL语句。2、select sc.snofrom (select min(grade)from (select distinct top 3 gradefrom scwhere cno=' ee-201'order by grade desc) as tl (grade) as t2(grade)inner join sc on t2. grade=sc. gradewhere cno=' ee-201,3、查询EE-201课成绩第3-5名的学生的学号。要求：运行成功，并生成正确的结果，打印SQL语句。3、select sc.snofrom (select tl. gradefrom (select distinct top 5 gradefrom scwhere cno=' ee-2015order by grade desc) as tl (grade),(select distinct top 2 gradefrom scwhere cno=' ee-201'order by grade desc) as t2(grade)where tl.grade<t2. grade)as t3(grade)inner join sc on t3.grade=sc. gradewhere cno=' ee-201)另一解更好：select sc.snofrom (select top 3 tl. gradefrom (select top 5 gradefrom scwhere cno=，ee-201'order by grade desc) as tl (grade)order by tl.grade asc)as t3(grade)inner join sc on t3. grade=sc. gradewhere cno=' ee-201)分步完成1、 select top 5 grade into tlfrom scwhere cno=' ee-201,order by grade desc2、select top 3 tl. grade into t2from tlorder by tl. grade asc3 select sc. snofrom t2 inner join sc on t2. grade=sc. grade where cno=，ee-20T4、 drop table tldrop table t2
