练习. SQL--选修课程练习

create database XuanXiuke
use XuanXiuke

--创建院系表
create table xiyuan
(
pcode int primary key not null,--主键
pname varchar(20),
pteacher varchar(20),
ptel     varchar(20),
)

--创建可选修课程表
create table kecheng
(
kcode int primary key not null,--主键
kname varchar(20),
kteacher varchar(20),
ktel     varchar(20),
)

--创建学生列表
create table xuesheng
(
xcode int primary key identity(10001,1) not null,--主键
xname varchar(20),
xsex   char(10),
xpart   int,--外键
xlesson int,--外键
)

--输入院系信息
insert into xiyuan values(101,'计算机系','王法','12345678')
insert into xiyuan values(102,'外语系','张能','23456789')
insert into xiyuan values(103,'数学系','赵强','34567890')
insert into xiyuan values(104,'历史系','钱进','12345678901')

--输入选修课数据
insert into kecheng values(201,'计算机工程','王强','123123123')
insert into kecheng values(202,'国防安全','刘发','321321321')
insert into kecheng values(203,'文艺鉴赏','齐值','456456456')
insert into kecheng values(204,'科技创新','李志','654654654')
insert into kecheng values(205,'旅游管理','王明','258258258')

--输入学生数据
insert into xuesheng values('张三','男',101,201)
insert into xuesheng values('李四','男',101,202)
insert into xuesheng values('王五','男',101,203)
insert into xuesheng values('赵六','男',101,204)
insert into xuesheng values('张七','男',101,205)
insert into xuesheng values('李五','男',101,202)
insert into xuesheng values('王五','男',101,203)
insert into xuesheng values('赵丽','女',101,205)

insert into xuesheng values('赵颖','女',102,204)
insert into xuesheng values('张丽丽','女',102,201)
insert into xuesheng values('李莉','女',102,201)
insert into xuesheng values('王器','男',102,203)
insert into xuesheng values('王丽','女',102,205)

insert into xuesheng values('张甜甜','女',103,201)
insert into xuesheng values('任盈盈','女',103,203)
insert into xuesheng values('周芷若','女',103,205)

insert into xuesheng values('李莫愁','女',104,201)
insert into xuesheng values('黄蓉','女',104,202)
insert into xuesheng values('小龙女','女',104,202)
insert into xuesheng values('林胡冲','男',104,203)
insert into xuesheng values('林平之','男',104,205)

go

--题目：

--1、查看选修课人数最多的课程名称

select kname from kecheng where kcode = 
(select top 1 xlesson from xuesheng group by xlesson order by COUNT(*) desc)

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

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

--4、查看计算机系女生人数
select COUNT(*) from xuesheng where xpart=
(select pcode from xiyuan where pname='计算机系') and xsex='女'

--5、查看哪个系院的男生最多
select pname from xiyuan where pcode=
(select top 1 xpart from xuesheng where xsex='男' group by xpart order by COUNT(*) desc)

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

--7、查看李莫愁同学的系院的电话
select ptel from xiyuan where pcode = 
(select xpart from xuesheng where xname='李莫愁')

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