sql多表联查练习题

1.查询名人表性别为男的作者

select *
from celebrity c
where c.ssex= '男'

2.查询静夜思的作者

select 
c.sname
from 
celebrity c
inner join summary s on c.sid=s.sid 
inner join works w on w.wid = s.wid
where 1=1
and w.wwork = '静夜思'

3.查询李白和杜甫的年纪

select 
c.sname,
c.sage
from 
celebrity c
where 1=1
and c.sname = '李白'
or c.sname = '杜甫'
![在这里插入图片描述](https://img-blog.csdnimg.cn/edbf51836a7044ed88c38ddc032b460b.png)

4.查询名人表中的男女个数

select ssex,count(1)
from celebrity
group by ssex

5.查询白居易写的作品名称和对应的名句

select 
w.wwork,
s.sassess
from
celebrity c
inner join summary s on c.sid=s.sid 
inner join works w on w.wid = s.wid
where 1=1
and c.sname = '白居易'

6.查询年纪在25到30之间的个数

select count(1)
from celebrity 
where sage between 25 and 30

7.查询名人表中的最小两条的数据信息

select *
from celebrity
order by sage limit 0,2

8.查询李白的作品名称,名句和年纪

select 
w.wwork,
s.sassess,
c.sage
from
celebrity c
inner join summary s on c.sid=s.sid 
inner join works w on w.wid = s.wid
where 1=1
and c.sname = '李白'

9.将作品为望月的朝代修改为北宋

update works set wdynasty='北宋' where  wwork='望岳'

10.新增名人表中的一个作者,王伟,年纪25,性别男

insert into  values (7,'王伟',25,'男')

1.查询性孙的用户个数

select * 
from user
where name like '孙%'

2.查询年纪最大的2个用户名

select uname
from user
order by uage desc
limit 2

3.查询李三买的商品名

select p.pname
from user u
 inner join order01 o on o.ouid = u.uid
 inner join product p on o.opid = p.pid
where u.uname = '李三'

4.查询购买过电脑的用户名

select u.uname
from user u
 inner join order01 o on o.ouid = u.uid
 inner join product p on o.opid = p.pid
where 
	p.pname = '电脑'

5.查询订单是909090对应的用户名和商品

select u.uname,p.pname
from user u
 inner join order01 o on o.ouid = u.uid
 inner join product p on o.opid = p.pid
where 
 o.onumber='909090'

6.查询商品表中的最高价格的前两条数据

select * from 
product order by price desc limit 0,2

1.查询sc表中对应的何昊老师所授课的女生的信息

SELECT s.sname,s.age as '年龄', s.sex FROM 
  student s
 join sc on s.sno = sc.sno 
 join c on sc.cno = c.cno
where 1=1
 and c.cteacher = '何昊'
 and s.sex = '女'

2.找出所有没有选修何昊老师的所有学生的名字

SELECT DISTINCT(s.sname) FROM 
  student s
 join sc on s.sno = sc.sno 
 join c on sc.cno = c.cno
where 1=1
 and c.cteacher != '何昊'

3.列出所有不及格的学生姓名

select DISTINCT(s.sname)
from student s
 join sc on s.sno = sc.sno 
 join c on sc.cno = c.cno
where 1=1
 and sc.scgrade < 60