多表查询题

多表查询


前言

这些题是我个人认为比较难的,希望对大家有帮助。


1.检索出Students表中所有学生的姓名及到2050年时的年龄的记录,要求结果中列名显示中文名。

表结构:
CREATE TABLE students (
sno char(7) ,
sname char(8) NOT NULL,
class char(10),
ssex char(2),
bday date ,
bplace char(10) ,
IDNum char(18) ,
sdept char(16),
phone char(11),
PRIMARY KEY (sno)
) ;
表样例及输出样例:
在这里插入图片描述

select sname as 姓名, (2050-year(bday)) as 年龄 from students;

2.统计各班男、女生人数

表结构:
CREATE TABLE students (
sno char(7) ,
sname char(8) NOT NULL,
class char(10),
ssex char(2),
bday date ,
bplace char(10) ,
IDNum char(18) ,
sdept char(16),
phone char(11),
PRIMARY KEY (sno)
) ;
表样例及输出样例:
在这里插入图片描述

select class 班级,ssex 性别,count(sno) 人数 from students group by class,ssex;

3.查询“王珊”老师所授课程的课程名称

表结构:
CREATE TABLE teachers (
tno char(3) ,
tname char(8),
ps char(10),
tbirthday date ,
tdept char(16) ,
tsex char(2),
PRIMARY KEY (tno)
) ;

CREATE TABLE teaching (
sid int ,
cterm int,
class char(10) DEFAULT NULL,
cno char(7) NOT NULL,
tno char(3) DEFAULT NULL,
period int DEFAULT NULL,
PRIMARY KEY (sid)
) ;

CREATE TABLE course (
cno char(7) ,
cname varchar(20) NOT NULL,
cpno char(7),
ccredit int NOT NULL,
PRIMARY KEY (cno)
) ;

表样例及输出样例:
在这里插入图片描述

select distinct cname
from teachers
join teaching on teachers.tno=teaching.tno
join course on teaching.cno=course.cno
where tname='王珊';

4.查询至少选修2门课程的女生姓名

使用join连接作答
表结构:
CREATE TABLE students (
sno char(7) ,
sname char(8) NOT NULL,
class char(10),
ssex char(2),
bday date ,
bplace char(10) ,
IDNum char(18) ,
sdept char(16),
phone char(11),
PRIMARY KEY (sno)
) ;

CREATE TABLE sc (
sno char(7) ,
cno char(7) ,
score decimal(4,1),
point decimal(2,1),
PRIMARY KEY (sno,cno)
) ;

表样例及输出样例:
在这里插入图片描述

select sname 
from students join sc using(sno) 
where ssex='女' 
group by students.sname 
having count(*)>2;

5.查询选修“0000011”课程的学生至2050年时平均年龄

使用join连接作答
表结构:
CREATE TABLE students (
sno char(7) ,
sname char(8) NOT NULL,
class char(10),
ssex char(2),
bday date ,
bplace char(10) ,
IDNum char(18) ,
sdept char(16),
phone char(11),
PRIMARY KEY (sno)
) ;

CREATE TABLE sc (
sno char(7) ,
cno char(7) ,
score decimal(4,1),
point decimal(2,1),
PRIMARY KEY (sno,cno)
) ;

表样例及输出样例:
在这里插入图片描述

select avg(2050-year(bday)) 平均年龄 
from students join sc using(sno) 
where cno='0000011';

6.查询“谭浩强”教师任课的课程号,选修其课程的学生的学号和成绩,结果中包括该老师没有被选修的课程

表结构:
CREATE TABLE teachers (
tno char(3) ,
tname char(8),
ps char(10),
tbirthday date ,
tdept char(16) ,
tsex char(2),
PRIMARY KEY (tno)
) ;

CREATE TABLE teaching (
sid int ,
cterm int,
class char(10) DEFAULT NULL,
cno char(7) NOT NULL,
tno char(3) DEFAULT NULL,
period int DEFAULT NULL,
PRIMARY KEY (sid)
) ;

CREATE TABLE sc (
sno char(7) ,
cno char(7) ,
score decimal(4,1),
point decimal(2,1),
PRIMARY KEY (sno,cno)
) ;

表样例及输出样例:
在这里插入图片描述

select teaching.cno,sc.sno,sc.score 
from teachers 
left join teaching on(teachers.tno=teaching.tno) 
left join sc on (teaching.cno=sc.cno) 
where teachers.tname='谭浩强';

7.检索出students表和sc表中没有选课的学生学号和姓名。

表结构:
CREATE TABLE students (
sno char(7) ,
sname char(8) NOT NULL,
class char(10),
ssex char(2),
bday date ,
bplace char(10) ,
IDNum char(18) ,
sdept char(16),
phone char(11),
PRIMARY KEY (sno)
) ;

CREATE TABLE sc (
sno char(7) ,
cno char(7) ,
score decimal(4,1),
point decimal(2,1),
PRIMARY KEY (sno,cno)
) ;

表样例及输出样例:
在这里插入图片描述

select students.sno,sname 
from students left join sc on(students.sno=sc.sno) 
where cno is null;

8.检索出students表中与“陆毅”同一个系的同学姓名。

表结构:
CREATE TABLE students (
sno char(7) ,
sname char(8) NOT NULL,
class char(10),
ssex char(2),
bday date ,
bplace char(10) ,
IDNum char(18) ,
sdept char(16),
phone char(11),
PRIMARY KEY (sno)
) ;

表样例及输出样例:
在这里插入图片描述

select sname 
from students 
where sname!='陆毅' and sdept=
(select sdept from students where sname='陆毅');

9.检索出学生‘张三’选修的所有及格的课程及成绩,最后计算他所获得的总学分。输出成绩结果集按课程号升序排序。

注意:选课成绩在60分以上才能获得相应的学分。cou表中credit列为某课程的学分值 。假定学生姓名没有重名的情况。

提示:可使用WITH ROLLUP语句或UNION子句汇总结果。
表结构:
CREATE TABLE stu (
sno char(4) NOT NULL,
sname char(8) NOT NULL,
sex tinyint(1) DEFAULT NULL,
mno char(2) DEFAULT NULL,
birdate datetime DEFAULT NULL,
memo text,
PRIMARY KEY (sno)
);

CREATE TABLE cou (
cno char(4) NOT NULL, – 课程号
cname varchar(30) NOT NULL, – 课程名
credit smallint(6) DEFAULT NULL, – 学分
ptime char(5) DEFAULT NULL,
teacher char(10) DEFAULT NULL,
PRIMARY KEY (cno)
);

CREATE TABLE sc (
sno char(4) NOT NULL,
cno char(4) NOT NULL,
grade decimal(6,1) DEFAULT NULL,
PRIMARY KEY (sno,cno),
CONSTRAINT fk_sc_cno FOREIGN KEY (cno) REFERENCES cou (cno),
CONSTRAINT fk_sc_sno FOREIGN KEY (sno) REFERENCES stu (sno)
);

表样例及输出样例:
在这里插入图片描述

select sc.cno 课程号,cname 课程名,grade 成绩,credit 学分
from stu,cou,sc
where stu.sno=sc.sno and cou.cno=sc.cno and sc.grade>=60 and stu.sname="张三"
union
select sname,'所有及格课程','合计总学分',sum(credit)
from stu,cou,sc
where stu.sno=sc.sno and cou.cno=sc.cno and sc.grade>=60 and stu.sname="张三"
order by 课程号;

10.查询拥有相同速度和内存的PC机的成对的型号,输出结果属性名分别为model1,model2。

表结构:
CREATE TABLE pc
( model CHAR(20) NOT NULL, --型号
speed DECIMAL(6,2), --速度
ram INT, --内存
hd DECIMAL(6,2), --硬盘容量
cd CHAR(4), --光驱
price INT, --价钱
PRIMARY KEY(model)
);

表样例及输出样例:
在这里插入图片描述

select p1.model model1,p2.model model2
from pc p1,pc p2 
where p1.model<p2.model and p1.speed=p2.speed and p1.ram=p2.ram;

11.查询生产三种不同型号的PC的厂商。

表结构:
CREATE TABLE product
( maker CHAR(20) , --制造商
model CHAR(20) NOT NULL, --产品型号
type CHAR(20), --产品类型
PRIMARY KEY(model)
);
CREATE TABLE pc
( model CHAR(20) NOT NULL, --型号
speed DECIMAL(6,2), --速度
ram INT, --内存
hd DECIMAL(6,2), --硬盘容量
cd CHAR(4), --光驱
price INT, --价钱
PRIMARY KEY(model),
FOREIGN KEY(model) REFERENCES product(model)
);

表样例及输出样例:
在这里插入图片描述

select maker from
(select maker
from product join pc using(model)
group by maker,model)a
group by maker
having count(*)=3;

12.统计订单表(orders)中顾客的订单数量,并插入results表中

表结构:
在这里插入图片描述

CREATE TABLE orders (
OrderID int(11) NOT NULL ,
CustomerID varchar(5) ,
EmployeeID int(11),
OrderDate datetime,
RequiredDate datetime,
ShippedDate datetime,
ShipVia int(11),
Freight decimal(10,4) NULL DEFAULT 0.0000 ,
ShipName varchar(40) ,
ShipAddress varchar(60) ,
ShipCity varchar(15) ,
ShipRegion varchar(15) ,
ShipPostalCode varchar(10) ,
ShipCountry varchar(15) ,
PRIMARY KEY (OrderID)
);
CREATE TABLE results (
CustomerID varchar(5) ,
OrderCount double
);
表样例及输出样例:
在这里插入图片描述

insert into results
select CustomerID,count(*) OrderCount
from orders
group by CustomerID
having count(*);

13.检索出所有选修’C语言’课程的学生成绩记录,输出结果集按照成绩降序排序。

表结构:
CREATE TABLE stu (
sno char(4) NOT NULL,
sname char(8) NOT NULL,
sex tinyint(1) DEFAULT NULL,
mno char(2) DEFAULT NULL,
birdate datetime DEFAULT NULL,
memo text,
PRIMARY KEY (sno)
);
CREATE TABLE cou (
cno char(4) NOT NULL,
cname varchar(30) NOT NULL,
credit smallint(6) DEFAULT NULL,
ptime char(5) DEFAULT NULL,
teacher char(10) DEFAULT NULL,
PRIMARY KEY (cno)
) ;
CREATE TABLE sc (
sno char(4) NOT NULL,
cno char(4) NOT NULL,
grade decimal(6,1) DEFAULT NULL,
PRIMARY KEY (sno,cno),
CONSTRAINT fk_sc_cno FOREIGN KEY (cno) REFERENCES cou (CNO),
CONSTRAINT fk_sc_sno FOREIGN KEY (sno) REFERENCES stu (sno)
);

表样例及输出样例:
在这里插入图片描述

select stu.sname 姓名,sc.grade 成绩
from stu,cou,sc
where stu.sno=sc.sno and cou.cno=sc.cno and cou.cname='C语言'
order by 成绩 desc;

14.查询学号为‘S001’或‘S003’的学生选修的课程,输出结果集按课程号升序排序,且不包含重复的课程记录。

提示:请使用UNION语句作答。

表结构:
CREATE TABLE cou (
cno char(4) NOT NULL, – 课程号
cname varchar(30) NOT NULL, – 课程名称
credit smallint(6) DEFAULT NULL,
ptime char(5) DEFAULT NULL,
teacher char(10) DEFAULT NULL,
PRIMARY KEY (cno)
);
CREATE TABLE sc (
sno char(4) NOT NULL,
cno char(4) NOT NULL,
grade decimal(6,1) DEFAULT NULL,
PRIMARY KEY (sno,cno),
CONSTRAINT fk_sc_cno FOREIGN KEY (cno) REFERENCES cou (cno)
);

表样例及输出样例:
在这里插入图片描述

select distinct cou.cno 课程号,cou.cname 课程
from cou join sc using(cno)
where sc.sno='S001'
union
select distinct cou.cno 课程号,cou.cname 课程
from cou join sc using(cno)
where sc.sno='S003';

15.查询每个同学超过他选修的平均成绩的课程。

表结构:
CREATE TABLE cou (
cno char(4) NOT NULL,
cname varchar(30) NOT NULL,
credit smallint(6) DEFAULT NULL,
ptime char(5) DEFAULT NULL,
teacher char(10) DEFAULT NULL,
PRIMARY KEY (cno)
)

CREATE TABLE sc (
sno char(4) NOT NULL,
cno char(4) NOT NULL,
grade decimal(6,1) DEFAULT NULL,
PRIMARY KEY (sno,cno),
CONSTRAINT fk_sc_cno FOREIGN KEY (cno) REFERENCES cou (CNO),
);

表样例及输出样例:
在这里插入图片描述

select sno 学号,cname 课程名,grade 成绩
from cou,sc a
where cou.cno=a.cno
and a.grade>
(
select avg(b.grade)
    from sc b
    where a.sno=b.sno
);

16.统计各专业的学生选课的平均成绩,如果某专业尚未有任何学生选修课程或成绩为空时,平均分计为0。输出结果集按照major表中的mno升序排序。

表结构:
CREATE TABLE major (
mno char(2) NOT NULL,
mname varchar(20) NOT NULL,
PRIMARY KEY (mno)
);
CREATE TABLE stu (
sno char(4) NOT NULL, – 学号
sname char(8) NOT NULL, – 姓名
sex tinyint(1) DEFAULT NULL, – 性别
mno char(2) DEFAULT NULL, – 专业号
birdate datetime DEFAULT NULL, – 出生日期
memo text, – 备注
PRIMARY KEY (sno),
CONSTRAINT fk_stu_mno FOREIGN KEY (mno) REFERENCES major (mno)
);
CREATE TABLE sc (
sno char(4) NOT NULL,
cno char(4) NOT NULL,
grade decimal(6,1) DEFAULT NULL,
PRIMARY KEY (sno,cno),
CONSTRAINT fk_sc_sno FOREIGN KEY (sno) REFERENCES stu (sno)
);

表样例及输出样例:
在这里插入图片描述

select mname 专业,ifnull(avg(grade),0) 平均成绩
from major left join 
(select mno,grade
from stu join sc using(sno))a
on major.mno=a.mno
group by major.mno
order by major.mno;

17.查询选修了2门以上课程的学生学号和平均成绩。

提示:请使用SELECT语句作答,平均成绩四舍五入,保留1位有效数字。
表结构:
CREATE TABLE score (
sno varchar(6) NOT NULL,
cno varchar(6) NOT NULL,
term varchar(15),
grade int(11),
PRIMARY KEY (sno,cno)
) ;
表样例及输出样例:
在这里插入图片描述

select sno 学号,round(avg(grade),1) 平均成绩
from score
group by sno
having count(sno)>=2;

18.以sh_goods_category表为主表,sh_goods表为从表,查询商品分类id为10或11的商品对应的商品id、商品名称、分类id及分类名称。注意:查询结果显示字段依据输出样例设置。

表结构:
CREATE TABLE sh_goods (
id INT PRIMARY KEY, --商品id
category_id INT NOT NULL DEFAULT 0 , – 商品分类id
name VARCHAR(120) NOT NULL, --商品名称
keyword VARCHAR(255) NOT NULL, – 关键词编号
content TEXT NOT NULL , --商品详情
price DECIMAL(10, 2) NOT NULL DEFAULT 0 , --价格
stock INT NOT NULL DEFAULT 0, – 库存
score DECIMAL(3, 2) NOT NULL DEFAULT 0 , – 用户评分
comment_count INT NOT NULL DEFAULT 0 – 评论数量
) ;
CREATE TABLE sh_goods_category (
id INT PRIMARY KEY ,-- 分类id
parent_id INT NOT NULL DEFAULT 0, --上级分类id
name VARCHAR(120) NOT NULL --分类名称
);

表样例及输出样例:
在这里插入图片描述

select s.id gid,s.name gname,c.id cid,c.name cname
from sh_goods_category c left join sh_goods s
on(s.category_id=c.id)
where c.id in(10,11);

  • 1
    点赞
  • 10
    收藏
    觉得还不错? 一键收藏
  • 2
    评论
好的,以下是一个MySQL多表查询的练习: 假设有一个学生表(student)、一个课程表(course)和一个选课表(selection),表结构如下: student表: | Field | Type | Null | Key | Default | Extra | |---------|-------------|------|------|---------|----------------| | id | int(11) | NO | PRI | NULL | auto_increment | | name | varchar(20) | NO | | NULL | | | gender | varchar(2) | NO | | NULL | | | age | int(11) | NO | | NULL | | | address | varchar(50) | NO | | NULL | | course表: | Field | Type | Null | Key | Default | Extra | |---------------|-------------|------|------|---------|----------------| | id | int(11) | NO | PRI | NULL | auto_increment | | name | varchar(20) | NO | | NULL | | | teacher | varchar(20) | NO | | NULL | | | credit | float | NO | | NULL | | | student_count | int(11) | NO | | NULL | | selection表: | Field | Type | Null | Key | Default | Extra | |------------|-------------|------|------|---------|----------------| | id | int(11) | NO | PRI | NULL | auto_increment | | student_id | int(11) | NO | | NULL | | | course_id | int(11) | NO | | NULL | | | score | int(11) | NO | | NULL | | | selection_time | datetime | NO | | NULL | | 请完成以下查询: 1. 查询所有学生姓名、性别和选修的课程名称。 2. 查询选修“数学”课程的学生姓名和成绩,并按照成绩降序排序。 3. 查询每个学生选修的课程数目和平均成绩,并按照平均成绩降序排序。 4. 查询选修课程数目最多的学生姓名和选修课程数目。 5. 查询每个选修课程的平均成绩和选修学生数目,并按照平均成绩降序排序。 6. 查询选修课程平均成绩排名前三的课程的名称和平均成绩。 7. 查询选修成绩排名前十的学生姓名、选修课程名称和成绩。 8. 查询选修成绩在80分以上的学生姓名、选修课程名称和成绩。 9. 查询选修“计算机”课程的学生成绩排名前三的姓名和成绩。 10. 查询选修课程成绩总分排名前三的学生姓名和成绩总分。 (注:数据仅供练习使用。)
评论 2
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值