查询题以及答案

查询题以及答案

已知数据库Test1，包含以下系部表、学生表、班级表

创建数据库Test1

create database Test1

切换到Test1数据库

use Test1

创建系部表,学生表和班级表

create table Department(departid int not null,departname varchar(10) not null)
create table Student(sno int not null,sname varchar(8) not null,age int not null,classid int not null)
create table ClassInfo(classid int not null,sdeptname varchar(20) not null,departid int not null,studydate datetime)

向三表插入数据

insert into Department values
(1,'英语系'),(2,'表演系'),(3,'音乐系')

insert into Student values
(2021001,'小艾',18,101),(2021002,'小白',19,101),
(2021003,'小峰',18,102),(2021004,'小吖',20,102),
(2021005,'小七',17,103),(2021006,'小林',16,103)

insert into ClassInfo values
(101,'戏曲表演',2,'2020-9-1'),(102,'舞蹈表演',2,'2019-9-1'),
(103,'声乐',3,'2020-9-1'),(104,'乐器',3,'2019-9-1')

完成下列任务：

1. 查询“表演”系的学生学号及姓名。
2. 查询各个系的系号，系名及其学生人数。
3. 查询戏曲表演学生的学号和姓名。
4. 查询不是戏曲表演学生的学生学号和姓名。
5. 查询各个系的班级情况，包括系的系名，以及该系所拥有的的班号及其入学年份，即使该系目前没有班级，也要显示其信息。
6. 查询各系的学生情况，包括系名，该系的学生的学号和姓名，即使该系没有学生也要显示其信息。
7. 查询与小艾在同一个专业学习的学生姓名。
8. 查询各班中年龄超过本班平均年龄的学生的姓名及其班号。
9. 查询系中班级超过 2（含 2）的系的系名。

答案:

1. 查询“表演”系的学生学号及姓名。

--分析：学生表和班级表有关系，班级表和系别表有关系，先查询.net系的classId 再根据departid找班级id,最后当作条件再学生表中查询
select sno,sname from Student where classid in (select classid from ClassInfo where departid=(select departid from Department where departname='表演系'))

--三表联查
select s.sno,s.sname from Student s join ClassInfo c on s.classid = c.classid 
join Department d on d.departid = c.departid where d.departname='表演系'

--先查询表演系的班级id,再查询这些班级的学生
--用逗号和where连接的两个表，相当于join和on连接的表
select s.sno,s.sname from Student s,(Select ClassId,Departname From Department d,
ClassInfo c Where d.departid = c.departId and d.departname = '表演系')p
where s.classid=p.classid

2. 查询各个系的系号，系名及其学生人数。

select d.departid,d.departname,COUNT(1) '学生人数' from Student s join ClassInfo c
on s.classid = c.classid join Department d on d.departid = c.departid 
group by d.departname,d.departid

3. 查询戏曲表演学生的学号和姓名。

select sno,sname from Student where 
classid in (select classid from ClassInfo where sdeptname='戏曲表演')

select s.sno,s.sname from ClassInfo c,Student s 
where c.classid=s.classid and sdeptname='戏曲表演'

4. 查询不是戏曲表演学生的学生学号和姓名。

select sno,sname from Student where classid not in 
(select classid from ClassInfo where sdeptname='戏曲表演')

select s.sno,s.sname from ClassInfo c,Student s 
where c.classid=s.classid and sdeptname <> '戏曲表演'

5. 查询各个系的班级情况，包括系的系名，以及该系所拥有的的班号及其入学年份，即使该系目前没有班级，也要显示其信息。

Select *
From department d left outer join classinfo c
On d.departid = c.departid;

6. 查询各系的学生情况，包括系名，该系的学生的学号和姓名，即使该系没有学生也要显示其信息。

select d.departname,s.sno,s.sname from Student s right join ClassInfo c 
on s.classid = c.classid right join Department d on d.departid = c.departid 

7. 查询与小艾在同一个专业学习的学生姓名。

select * from Student where classid in (select classid from Student where sname = '小艾')

8. 查询各班中年龄超过本班平均年龄的学生的姓名及其班号。

Select * from student a where age>(Select avg(age) From student Where classid = a.classid);

9. 查询系中班级超过 2（含 2）的系的系名。

Select d.departid, d.departname, (select count (*) 'count' from classinfo c 
where c.departid = d.departid) From department d 
Group by d.departid, d.departname having (select count (*) 'count' from classinfo c where c.departid = d.departid) >=2