SQL 测试题

SQL 测试题

use TESTDB01
go

--//table 01
if (OBJECT_ID(N'ypTBS') is not null) 
drop table ypTBS
go

create table ypTBS(
recid int identity(1,1) primary key, --student id
sc varchar(10) not null default '', --code
sn varchar(50) null default '', -- name
sd varchar(250) null default '', -- company
se varchar(200) null default ''  -- email
)
go
insert ypTBS(sc,sn,sd,se) values('S001','张三','公司001','email01@163.com')
insert ypTBS(sc,sn,sd,se) values('S002','李四','公司002','email02@163.com')
insert ypTBS(sc,sn,sd,se) values('S003','王五','公司003','email03@163.com')
insert ypTBS(sc,sn,sd,se) values('S004','赵六','公司004','email04@163.com')
insert ypTBS(sc,sn,sd,se) values('S005','小明','公司005','email05@163.com')
select * from ypTBS
go

--//table 02
if(OBJECT_ID(N'ypTBC') is not null)
drop table ypTBC
go

create 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 ypTBC
go

--//table 03
if(OBJECT_ID(N'ypTBSC') is not null)
drop table ypTBSC
go

create 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 ypTBSC
go

--//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) >2
select 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
*/

提供参考......