学生表的设计及练习

create database student
use student
go
create table xsb
(
xh char(10) not null primary key,
name char(12) not null ,
sex bit default 1,
birthday date,
zy char(20),
zxf int default 0,
bz varchar(500)
)

create table kcb
(
kch char(10) not null primary key,
kcm char(30) not null,
kkxq tinyint(1) default 1,
xs tinyint(1) default 0,
xf tinyint(1) not null default 0,
)

create table cjb
(
xh char(10) not null ,
kch char(10) not null,
cj int default 0,
)

 

insert into xsb values('081101','王林',0,'1990-02-10','计算机',50,'NULL'),
('081102','程明',1,'1991-02-01','计算机',50,'NULL'),
('081103','王燕',0,'1989-10-06','计算机',50,'NULL'),
('081104','韦严平',1,'1990-08-26','计算机',50,'NULL'),
('081106','李方方',1,'1990-11-20','计算机',50,'NULL'),
('081107','李明',1,'1990-05-01','计算机',54,'已提前修完《数据结构》，并获得学分'),
('081108','林一帆',1,'1989-08-05','计算机',52,'已提前修完一门课'),
('081109','张强民',1,'1989-08-11','计算机',50,'NULL'),
('081110','张尉',0,'1991-07-22','计算机',50,'NULL'),
('081111','赵琳',0,'1990-03-18','计算机',50,'NULL'),
('081113','严红',0,'1989-08-11','计算机',48,'NULL'),
('081201','王敏',1,'1989-06-10','通信工程',42,'NULL'),
('081202','王林',1,'1989-01-29','通信工程',40,'有一门不及格，待补考'),
('081203','王玉民',1,'1990-03-26','通信工程',42,'NULL'),
('081204','马琳琳',0,'1989-02-10','通信工程',42,'NULL'),
('081206','李计',1,'1989-09-20','通信工程',42,'NULL'),
('081210','李红厌',1,'1989-05-01','通信工程',44,'已提前修完一门课，并获得学分'),
('081216','孙祥欣',1,'1989-03-19','通信工程',42,'NULL'),
('081218','孙妍',1,'1990-10-09','通信工程',42,'NULL'),
('081220','吴威华',0,'1990-03-18','通信工程',42,'NULL'),
('081321','刘燕敏',0,'1989-11-12','通信工程',42,'NULL'),
('081241','罗林琳',0,'1990-01-30','通信工程',50,'转专业学习')

insert into cjb values('081101','101',84),('081102','206',63),('081103','101',62),('081104','101',90),('081107','302',76),('081108','101',85),('081109','209',80),('081110','101',95),('081111','101',91),('081113','208',69),('081201','101',80),('081202','101',65),('081203','101',87),('081204','101',91),('081210','212',86),('081216','101',81),('081218','301',77),('081220','101',90),('081241','101',90),('081101','102',78),('081101','206',76),('081103','102',70),('081110','210',74),('081108','102',86),('081108','208',82)

insert into kcb values('101','计算机基础',1,20,5),('102','程序设计与语言',2,24,4),
('206','离散数学',4,28,4),('208','数据结构',5,24,4),('209','操作系统',6,26,4),
('210','计算机原理',5,24,5),('212','数据库原理',7,28,4),('301','计算机网络',7,20,3),('302','软件工程',7,20,3)

 

select * from xsb
select * from kcb
select * from cjb

select xh from cjb
where cj>=80
group by xh
having COUNT(*)>=2

/*查询选修课程超过2门且成绩都在80分以上的学生学号*/
select xh,count(*) from cjb
group by xh
having count(*)>=2
intersect
select xh,count(*) from cjb
where cj >=80
group by xh

 

/*查询选修了101课程，但没有选修102课程的学生信息*/
select * from xsb where xh in (select cjb.xh from cjb where kch='101' and cjb.xh =xsb.xh)
except
select * from xsb where xh in (select cjb.xh from cjb where kch='102' and cjb.xh =xsb.xh)

/*查询单科成绩最高分，学号、姓名、课程名、成绩(最高分)*/
select cjb.xh,name,kcm,max(cj) 最高分 from cjb
join xsb on xsb.xh=cjb.xh
join kcb on kcb.kch=cjb.kch
where kcm in (select kcm from kcb where kch in ( select kch from cjb
group by kch ))
group by cjb.xh,name,kcm

select kch,MAX(cj) 最高分 from cjb
group by kch

/*from 子句实现*/
select * from (select xsb.xh,xsb.name,cjb.kch,kcb.kcm,cjb.cj 最高分 from xsb,cjb,kcb
where xsb.xh=cjb.xh and kcb.kch=cjb.kch and cjb.cj in (select MAX(cj) from cjb where cjb.kch=kcb.kch))t

/*in子句实现*/
select xsb.xh,xsb.name,kcb.kcm,cjb.cj 最高分 from xsb join cjb on xsb.xh=cjb.xh
join kcb on kcb.kch=cjb.kch
where cj in (select MAX(cj) from cjb where kcb.kch=cjb.kch)

/*exist子句实现*/