计算机专业计算机技能测试.docx

《计算机专业计算机技能测试.docx》由会员分享，可在线阅读，更多相关《计算机专业计算机技能测试.docx（5页珍藏版）》请在淘文阁 - 分享文档赚钱的网站上搜索。

1、计算机专业计算机技能测试.计算机专业计算机技能测试 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、查询至少有一

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),

3、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-20

4、1,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

5、 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-201order by grade desc) as tl (grade) as t2(grad

6、e)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-201order by gra

7、de desc) as t2(grade)where tl.gradet2. 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-201order 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