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

2024-12-12 07:07:11

推荐回答（3个）

回答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 )

回答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.

回答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
)
)

最新问答

夫妻吵架20多天了老公有事没事半夜把别人的抖音视频转发给老婆,抖音视频上面？

萝卜莲叶子花苞变黄干枯怎么办

番禺大夫山森林公园需要门票吗？

怎么自己用u盘做系统？360急救盘可以吗？还是去哪里下？

为什么我的QQ号不是靓号?

烤烟很好特别绿要怎么烘烤才会更快黄好？

戴玉要注意些什么啊？我有一块玉，现在戴着，是不是不能取下来了？谢谢了，大神帮忙啊

烤烟特别绿要怎么烘烤才会黄好？

手撕饼的家常做法大全怎么做好吃视频

北京现代伊兰特冬天加的5w_30半合成机油,补缺用0w_40可以吗