SQL 测试题

SQL 测试题

use TESTDB01go--//table 01if (OBJECT_ID(N'ypTBS') is not null) drop table ypTBSgocreate table ypTBS(recid int identity(1,1) primary key, --student idsc varchar(10) not null default '', --codesn varchar(50) null default '', -- namesd varchar(250) null default '', -- companyse varchar(200) null default ''  -- email)goinsert ypTBS(sc,sn,sd,se) values('S001','张三','公司001','[e-mail]')insert ypTBS(sc,sn,sd,se) values('S002','李四','公司002','[e-mail]')insert ypTBS(sc,sn,sd,se) values('S003','王五','公司003','[e-mail]')insert ypTBS(sc,sn,sd,se) values('S004','赵六','公司004','[e-mail]')insert ypTBS(sc,sn,sd,se) values('S005','小明','公司005','[e-mail]')select * from ypTBSgo--//table 02if(OBJECT_ID(N'ypTBC') is not null)drop table ypTBCgocreate table ypTBC(recid int identity(1,1) not null,cc varchar(10) not null default '',cn varchar(250) null default '',primary key([recid]))insert into ypTBC(cc,cn) values('C01','音乐欣赏')insert into ypTBC(cc,cn) values('C02','文学鉴赏')insert into ypTBC(cc,cn) values('C03','地理信息')insert into ypTBC(cc,cn) values('C04','历史学')select * from ypTBCgo--//table 03if(OBJECT_ID(N'ypTBSC') is not null)drop table ypTBSCgocreate table ypTBSC(recid int identity(1,1) not null,sc varchar(10) null,cc varchar(10) null, fen int null,)insert into ypTBSC(sc,cc,fen) values('S001','C01',56)insert into ypTBSC(sc,cc,fen) values('S001','C02',80)insert into ypTBSC(sc,cc,fen) values('S001','C03',86)insert into ypTBSC(sc,cc,fen) values('S001','C04',90)insert into ypTBSC(sc,cc,fen) values('S002','C01',65)insert into ypTBSC(sc,cc,fen) values('S003','C01',78)insert into ypTBSC(sc,cc,fen) values('S002','C02',81)insert into ypTBSC(sc,cc,fen) values('S002','C03',53)insert into ypTBSC(sc,cc,fen) values('S003','C03',43)insert into ypTBSC(sc,cc,fen) values('S004','C03',76)insert into ypTBSC(sc,cc,fen) values('S004','C04',70)insert into ypTBSC(sc,cc,fen) values('S005','C02',66)insert into ypTBSC(sc,cc,fen) values('S005','C03',45)insert into ypTBSC(sc,cc,fen) values('S005','C04',50)select * from ypTBSCgo--//1，求出选择历史学的学生姓名和所属单位select sn,sd from ypTBS where sc in(select sc from ypTBSC where cc = (select cc from ypTBC where cn = '历史学')) /*--数据验证分解select cc from ypTBC where cn = '历史学'select sc from ypTBSC where cc = 'C04'select sn,sd from ypTBS where sc in ('S001','S002','S003')*/--//2，求出选修课程代号为 C01 的学生姓名和所属单位select sn,sd from ypTBS where sc in(select sc from YPTBSC where cc = 'C01' )/*--数据验证分解select sc from ypTBSC where cc = 'C01'select sn,sd from ypTBS where sc in ('S001','S002','S003')*/--//3，求出选修课程数超过2门的学生学号和姓名select sc,sn from yptbs where sc in (select sc from (select sc, COUNT(cc) as xcc from yptbsc group by sc having COUNT(cc) > 2) tb)/*--数据验证分解select sc,count(cc) as xcc from yptbsc group by sc select sc,count(cc) as xcc from yptbsc group by sc having count(cc) >2select sc from( select sc,count(cc) as xcc  from yptbsc group by sc having count(cc) >2) tb*/--//4，求出选修全部课程的学生学号和姓名select sc,sn from yptbs where sc in(select sc from(select sc,count(cc) as xcc from yptbsc group by sc having count(cc) = (select COUNT(*) as cnum from yptbc )) tb1)/*--数据验证分解select COUNT(*) as cnum from yptbc --求课程总数select sc,count(cc) as cc from yptbsc group by sc --依学号汇总，课程数作为汇总条件select sc,count(cc) as xcc from yptbsc group by sc having count(cc) = 4 --求修所有课程的学生select sc,count(cc) as xcc from yptbsc group by sc having count(cc) = ( --动态求出修所有课程的学生select COUNT(*) as cnum from yptbc ) select sc from (  --取出修所有课程学生的学号select sc,count(cc) as xcc from yptbsc group by sc having count(cc) = (select COUNT(*) as cnum from yptbc )) subsc*/--//5，求出两门（含两门）以上课程不及格课程的学生学号和姓名select sc, sn from yptbs where sc in(select sc from (select sc, COUNT(cc) as xcc from yptbsc where fen < 60 group by sc having count(cc) >= 2)tb5)/*--数据验证分解--小于60分的(百分制)学号和课程select sc,cc as xcc from yptbsc where fen < 60 --以课程数位大于1为条件对学号进行汇总select sc,count(cc) as xcc from yptbsc where fen < 60  group by sc having count(cc) >= 2 --求出两门(含)以上不及格的学号select sc from (select sc,count(cc) as xcc from yptbsc where fen < 60  group by sc having count(cc) >= 2 ) tb5*/

提供参考......