三个表的关系

create database 选课表
go
use 选课表
go
create table xiyuan
(
pcode int primary key not null,
pname varchar(20),
pteacher varchar(20),
ptel varchar(20)
)
go
create table kecheng
(
kcode int primary key not null,
kname varchar(20),
kteacher varchar(20),
ktel varchar(20)
)
go
create table xuesheng
(
xcode int primary key identity(10001,1) not null,
xname varchar(20),
xsex char(10),
xpart int,
xlesson int 
)
go

--系院
insert into xiyuan values(2001,'计算机系','季老师','1234567')
insert into xiyuan values(2002,'化工系','化老师','2234567')
insert into xiyuan values(2003,'电气系','电老师','3334567')
insert into xiyuan values(2004,'护理系','护老师','4444567')
go

--课程
insert into kecheng values(3001,'数学','属老师','2345678')
insert into kecheng values(3002,'体育','体老师','3345678')
insert into kecheng values(3003,'音乐','音老师','4445678')
insert into kecheng values(3004,'美术','美老师','5555678')
insert into kecheng values(3005,'语文','雨老师','6666678')
go

--学生
insert into xuesheng values('张三','男',2001,3001)
insert into xuesheng values('李四','男',2002,3002)
insert into xuesheng values('王雨','女',2002,3003)
insert into xuesheng values('马六','男',2003,3004)
insert into xuesheng values('赵四','男',2001,3001)
insert into xuesheng values('小雨','女',2004,3002)
insert into xuesheng values('萧萧','女',2003,3002)
insert into xuesheng values('李栋','男',2004,3004)
insert into xuesheng values('马云','男',2001,3003)
insert into xuesheng values('张捷','男',2004,3002)
insert into xuesheng values('张云','女',2001,3003)
go

--查看选修人数最多的课程名称
select xlesson, COUNT(*) from xuesheng group by xlesson
select xlesson from xuesheng group by xlesson having COUNT(*)>3
select kname from kecheng where kcode=3002
--总的
select kname from kecheng where kcode=
(select xlesson from xuesheng group by xlesson having COUNT(*)>3)


--查看男生选修/女生选修最多的课程所有信息
--男生
select xlesson,COUNT(*) from xuesheng where xsex='男' group by xlesson
select top 1 xlesson from xuesheng where xsex='男' group by xlesson order by COUNT(*)desc
select*from kecheng where kcode=3002
--总的
select*from kecheng where kcode=
(select top 1 xlesson from xuesheng where xsex='男' group by xlesson order by COUNT(*)desc)
--女生
select xlesson,COUNT(*) from xuesheng where xsex='女' group by xlesson
select top 1 xlesson from xuesheng where xsex='女' group by xlesson order by COUNT(*)desc
select*from kecheng where kcode=3003
--总的
select*from kecheng where kcode=
(select top 1 xlesson from xuesheng where xsex='女' group by xlesson order by COUNT(*)desc)


--查看计算机系的人数
select pcode from xiyuan where pname='计算机系'
select COUNT(*) as 学计算机的人数 from xuesheng where xpart=2001
--总的
select COUNT(*) as 学计算机的人数 from xuesheng where xpart=
(select pcode from xiyuan where pname='计算机系')


--查看计算机系的女生人数，男生人数
select COUNT(*) as 学计算机的人数 from xuesheng where xpart=
(select pcode from xiyuan where pname='计算机系')and xsex='女'

select COUNT(*) as 学计算机的人数 from xuesheng where xpart=
(select pcode from xiyuan where pname='计算机系')and xsex='男'


--查看那个戏院的男生最多  女生最多
select top 1 xpart from xuesheng where xsex='男' group by xpart order by COUNT(*)desc
--总的
select pname from xiyuan where pcode=
(select top 1 xpart from xuesheng where xsex='男' group by xpart order by COUNT(*)desc)

--查看属老师的课程有多少人选修
select COUNT(*) from xuesheng where xlesson=
(select kcode from kecheng where kteacher='属老师')


--查看马云同学的戏院电话
select ptel from xiyuan where pcode = 
(select xpart from xuesheng where xname='马云')

--查看马云同学的选修课程任课老师的名字及联系方式
select kteacher,ktel from kecheng where kcode=
(select xlesson from xuesheng where xname='马云')

--删除所有
delete from kecheng
delete from xiyuan
delete from xuesheng
go