多表查询
- 前言
- 1.检索出Students表中所有学生的姓名及到2050年时的年龄的记录,要求结果中列名显示中文名。
- 2.统计各班男、女生人数
- 3.查询“王珊”老师所授课程的课程名称
- 4.查询至少选修2门课程的女生姓名
- 5.查询选修“0000011”课程的学生至2050年时平均年龄
- 6.查询“谭浩强”教师任课的课程号,选修其课程的学生的学号和成绩,结果中包括该老师没有被选修的课程
- 7.检索出students表和sc表中没有选课的学生学号和姓名。
- 8.检索出students表中与“陆毅”同一个系的同学姓名。
- 9.检索出学生‘张三’选修的所有及格的课程及成绩,最后计算他所获得的总学分。输出成绩结果集按课程号升序排序。
- 10.查询拥有相同速度和内存的PC机的成对的型号,输出结果属性名分别为model1,model2。
- 11.查询生产三种不同型号的PC的厂商。
- 12.统计订单表(orders)中顾客的订单数量,并插入results表中
- 13.检索出所有选修'C语言'课程的学生成绩记录,输出结果集按照成绩降序排序。
- 14.查询学号为‘S001’或‘S003’的学生选修的课程,输出结果集按课程号升序排序,且不包含重复的课程记录。
- 15.查询每个同学超过他选修的平均成绩的课程。
- 16.统计各专业的学生选课的平均成绩,如果某专业尚未有任何学生选修课程或成绩为空时,平均分计为0。输出结果集按照major表中的mno升序排序。
- 17.查询选修了2门以上课程的学生学号和平均成绩。
- 18.以sh_goods_category表为主表,sh_goods表为从表,查询商品分类id为10或11的商品对应的商品id、商品名称、分类id及分类名称。注意:查询结果显示字段依据输出样例设置。
前言
这些题是我个人认为比较难的,希望对大家有帮助。
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);