6.8 SQL

use lianxi0608
go
--创建院系表
create table yuanxibiao
(
pcode int primary key identity(100,1) not null,--主键
pname varchar(20),
pteacher varchar(20),
ptel varchar(20)
)
--创建选修课程表
create table kechengbiao
(
kcode int primary key identity(200,1) not null, --主键
kname varchar(20),
kteacher varchar(20),
ktel varchar(20)
)
--创建学生表
create table xueshengbiao
(
xcode int primary key identity(300,1)not null,--主键
xname varchar(20),
xsex char(10),
xpart int,--外键
xlesson int ,--外键
)
--输入院系信息
insert into yuanxibiao values('计算机系','王法','123')
insert into yuanxibiao values ('物理系','韩可','456')
insert into yuanxibiao values ('化学系','孙膑','789')
--输入选修课数据
insert into kechengbiao values('计算机安全','王强','123')
insert into kechengbiao values('生物学','王勇','456')
insert into kechengbiao values ('化学','孙楠','789')
--输入学生数据
insert into xueshengbiao values('张三','男',101,102)
insert into xueshengbiao values('李四','男',103,104)
insert into xueshengbiao values('王五','女',105,106)
insert into xueshengbiao values('赵六','女',107,108)
insert into xueshengbiao values('冯七','男',109,110)

--1.查看选修课最多的课程名称
select kname from kechengbiao where kcode=
(select top 1 xlesson from xueshengbiao group by xlesson order by COUNT(*))
--2.查看男生选修女生选修的最多的课程的名称
select * from kechengbiao where kcode =
(select top 1 xlesson from xueshengbiao where xsex='男' group by xlesson order by COUNT(*) desc)
--3.查看计算机系的人数
select COUNT(*) from xueshengbiao where xpart=
(select pcode from yuanxibiao where pname='计算机系')
--4.查看计算机系女生的人数
select COUNT(*) from xueshengbiao where xpart=
(select pcode from yuanxibiao where pname='计算机系') and xsex='女'
--5.查看哪个院系男生最多
select pname from yuanxibiao where pcode=
(select top 1 xpart from xueshengbiao where xsex='男' group by xpart order by COUNT(*) desc)
--6、查看钱进老师的课程有多少人选修
select COUNT(*) from xueshengbiao where xlesson=
(select kcode from kechengbiao where kteacher='王强')
--7、查看李莫愁同学的系院的电话
select ptel from yuanxibiao where pcode =
(select xpart from xueshengbiao where xname='张三')
--8、查看李莫愁同学的选修课程任课老师的名字及联系方式
select kteacher,ktel from kechengbiao where kcode=
(select xlesson from xueshengbiao where xname='张三')