create database MyDBOne;

use MyDBOne;

--創(chuàng)建院系表
create table xiyuan
(
pcode int primary key not null,--主鍵
pname varchar(20),
pteacher varchar(20),
ptel varchar(20),
)

--創(chuàng)建可選修課程表
create table kecheng
(
kcode int primary key not null,--主鍵
kname varchar(20),
kteacher varchar(20),
ktel varchar(20),
)

--創(chuàng)建學生列表

create table xuesheng
(
xcode int primary key identity(001,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,'數(shù)學系','趙強','34567890')
insert into xiyuan values(104,'歷史系','錢進','12345678901')

--輸入選修課數(shù)據(jù)
insert into kecheng values(201,'計算機工程','王強','123123123')
insert into kecheng values(202,'國防安全','劉發(fā)','321321321')
insert into kecheng values(203,'文藝鑒賞','齊值','456456456')
insert into kecheng values(204,'科技創(chuàng)新','李志','654654654')
insert into kecheng values(205,'旅游管理','王明','258258258')

--輸入學生數(shù)據(jù)
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('張?zhí)鹛?,'女',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
select * from xiyuan
select * from kecheng
select * from xuesheng

--查看選修人數(shù)最多的課程的名稱
select kname from kecheng where kcode=
(select top 1 xlesson from xuesheng group by xlesson order by COUNT(*)desc )

--查看男生選修 女生選修課程最多課程的詳細信息
select xlesson,COUNT(*) from xuesheng where xsex='男' group by xlesson
select xlesson,COUNT(*) from xuesheng where xsex='男' group by xlesson order by COUNT(*)desc
select top 1 xlesson,COUNT(*) from xuesheng where xsex='男' group by xlesson order by COUNT(*)desc
select top 1 xlesson from xuesheng where xsex ='男' group by xlesson order by COUNT(xlesson)desc
select * from kecheng where kcode=(select top 1 xlesson from xuesheng where xsex ='男' group by xlesson order by COUNT(*)desc)

--查看計算機系人數(shù)
select xsex,COUNT(*) from xuesheng where xpart=(select pcode from xiyuan where pname='計算機系') group by xsex

--查看計算機系男生，女生人數(shù)
select COUNT(*) from xuesheng where xpart=(select pcode from xiyuan where pname='計算機系')and xsex='男'

--查看哪個系院的男生最多、女生最多
select pname from xiyuan where pcode=
(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='張麗麗')

--查看周芷若同學的選修課程任課老師的名字及聯(lián)系方式
select kteacher,ktel from kecheng where kcode=
(select xlesson from xuesheng where xname='周芷若')