Question

求SQL查询语句答案！！！！

1、设学校环境如下:一个系有若干个专业，每个专业一个班，每个班有若干个学生。已建立关于系、学
生、班级的数据库，关系模式为：
班CLASS :（班号classid，专业名subject，系号deptid，入学年份enrolltime）
学生STUDENT :（学号studentid，姓名name，年龄age，班号classid）
系 DEPARTMENT :（系号deptid，系名deptname）
以下为表的示例数据：
A.班CLASS：
classid
subject
deptid
enrolltime
101
软件
002
1995
102
微电子
002
1996
111
无机化学
003
1995
112
高分子化学
003
1996
121
统计数学
001
1995
131
现代语言
004
1996
141
国际贸易
005
1997

B. 学生STUDENT:
studentid
name
age
classid
8101
'张三'
18
101
8102
'钱四'
16
121
8103
'王玲'
17
131
8109
'赵四'
18
141
8110
'李可'
20
142
8201
'张飞'
18
111
8302
'周瑜'
16
112
8203
'王亮'
17
111
8305
'董庆'
19
102
8409
'赵龙'
18
101
8510
'李丽'
20
142
8105
'李飞'
19
102

C. 系 DEPARTMENT :
deptid
deptname
001
数学
002
计算机
003
化学
004
中文
005
经济

请完成以下查询功能
（1） 统计每一个系有多少个学生并且系的人数必须超过300，显示内容：系名、系号、学生人数。（一条SQL）
（2）找出所有姓李的学生，显示内容: 系名、学号、姓名、专业名，并且按系名升序、姓名降序进行排序。（一条SQL）
（3）通过建立视图，查询年龄在16到18岁之间的学生，显示内容：系名、姓名、年龄、入学年份。（一条SQL）
（4）查询学生人数最多的系的所有学生名单（可能存在两个或以上的系），显示内容：系名、姓名、专业名、年龄。（一条SQL）





Answer 1

（1） 统计每一个系有多少个学生并且系的人数必须超过300，显示内容：系名、系号、学生人数。（一条SQL）
select t1.*,t2.人数 from #DEPARTMENT t1,
(select count(*) as 人数 , deptid from #CLASS group by deptid having count(*)>300) t2
where t1.deptid=t2.deptid

（2）找出所有姓李的学生，显示内容: 系名、学号、姓名、专业名，并且按系名升序、姓名降序进行排序。（一条SQL）
select t3.deptname,t2.studentid,t2.name,t1.subject
from #CLASS t1,#STUDENT t2,#DEPARTMENT t3
where t1.classid=t2.classid
and t1.deptid=t3.deptid
and t2.name like '李%'
order by t3.deptname , t2.name desc

（3）通过建立视图，查询年龄在16到18岁之间的学生，显示内容：系名、姓名、年龄、入学年份。（一条SQL）

create view test
as
select t3.deptname,t2.name,t2.age ,t1.enrolltime
from #CLASS t1,#STUDENT t2,#DEPARTMENT t3
where t1.classid=t2.classid
and t1.deptid=t3.deptid
and t2.age between 16 and 18

（4）查询学生人数最多的系的所有学生名单（可能存在两个或以上的系），显示内容：系名、姓名、专业名、年龄。（一条SQL）

select t3.deptname,t2.name,t1.subject,t2.age
from #CLASS t1,#STUDENT t2,#DEPARTMENT t3, (select count(t1.studentid) as 人数 , t2.subject from #STUDENT t1, #CLASS t2 where t1.classid=t2.classid group by t2.subject) t4
where t1.classid=t2.classid
and t1.deptid=t3.deptid
and t1.subject=t4.subject
and t4.人数= (select max(t.人数) from ( select count(t1.studentid) as 人数 , t2.subject from #STUDENT t1, #CLASS t2 where t1.classid=t2.classid group by t2.subject) t )

Answer 2

班CLASS :（班号classid，专业名subject，系号deptid，入学年份enrolltime）
学生STUDENT :（学号studentid，姓名name，年龄age，班号classid）
系 DEPARTMENT :（系号deptid，系名deptname）
(2) select deptname,studentid,name,subject from student,class,deparement where name in '李 ' order by deptname,name desc.

Answer 3

1)
select d.deptid,d.deptname,count(s.studentid) as s_count
from department d inner join class c on d.deptid = c.deptid
inner join student s on s.classid = c.classid
group by d.deptid,d.deptname
having count(s.studentid) > 300

2)
select d.deptname,s.studentid,s.[name], c.subject
from department d inner join class c on d.deptid = c.deptid
inner join student s on s.classid = c.classid
where s.[name] like '李%'
order by d.deptname asc ,s.[name] desc

3)create view vStudent
as
select d.deptname,s.[name], s.age,c.enrolltime
from department d inner join class c on d.deptid = c.deptid
inner join student s on s.classid = c.classid
where s.age between 16 and 18

4)
select d.deptname,s.[name],c.subject ,s.age
from department d inner join class c on d.deptid = c.deptid
inner join student s on s.classid = c.classid
where d.deptid in
(
select d.deptid
from department d inner join class c on d.deptid = c.deptid
inner join student s on s.classid = c.classid
group by d.deptid
having count(s.studentid) =
(
select max(ct)
from
(
select count(s.studentid) as ct
from department d inner join class c on d.deptid = c.deptid
inner join student s on s.classid = c.classid
group by d.deptid
) x
)
)