pta mysql训练题集 (381-400)

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

-- select sno as 学号, cast(avg(grade) as decimal(10,4)) as 平均分
select sno as 学号, round(avg(grade),4) as 平均分
from score
group by sno
having count(*) >= 2

10-382 查询商品相关信息（多表查询）

select a.id as gid,a.name as gname,category_id as cid,b.name as cname
from sh_goods as a
left join sh_goods_category as b on a.category_id = b.id

10-383 查询五星商品对应的商品分类信息（多表查询）

select a.id as gid,a.category_id as cid,b.name as cname,score
from sh_goods as a
left join sh_goods_category as b on a.category_id = b.id
where score = 5

10-384 查询商品分类id为10或11的商品对应的商品信息（多表查询）

select a.id as gid,a.name as gname,b.id as cid,b.name as cname
from sh_goods as a
right join sh_goods_category as b on a.category_id = b.id
where b.id in (10,11)

10-385 获取指定商品的商品分类名称（多表查询）

select name
from sh_goods_category
where id = (select category_id from sh_goods where id  = 5)

10-386 查询商品价格小于500的商品分类名称（多表查询）

select name
from sh_goods_category as a
where id in (
    select category_id
    from sh_goods
    where price < 500
)

10-387 查询指定分类下符合条件的商品信息

select id,name,price
from sh_goods
where price < (select min(price) from sh_goods where category_id = 8)
and category_id = 3

10-388 创建视图，包含商品打折前后的价格信息

create view view_goods
-- (id,name,old_price,new_price)
as select 
id,name,price as old_price,price*0.5 as new_price
from sh_goods;

10-389 创建视图，包含拥有属性值个数大于1的商品信息

create view view_goods_2
as select
id,name
from sh_goods
where id in(
    select goods_id
    from sh_goods_attr_value
    group by goods_id
    having count(*) > 1
)

10-390 检索出生日期早于伍容华出生日期（不包含此日期）的所有学生信息。

select *
from student
where birth < (select birth from student where sname = '伍容华')

10-391 检索被学生选修的课程编号和课程名称。

select distinct cno,cname
from course
where cno in (select cno from score)

10-392 检索没被学生选修的课程编号和课程名称。

select distinct cno,cname
from course
where cno not in (select cno from score)

10-393 检索选修了三门课以上的学生学号、姓名、院部

select sno,sname,dept
from student
where sno in
(
    select sno
    from score
    group by sno
    having count(*)>=3
)

10-394 查询每门必修课的课程编号，课程名称和选修人数。

select a.cno,cname,count(sno) as total 
from course as a
left join score as b on a.cno=b.cno
where attribute='必修'
group by a.cno;

10-395 检索蒙族学生选修的课程编号、课程名称。

select cno,cname
from course
where cno in
(
    select cno 
    from score
    where sno in 
    (
        select sno
        from student
        where nation = '蒙'
    )
)

10-396 查找所有“大学语文”分数在80以上的学生的姓名，所在院系

select sname,dept
from student
where sno in
(
    select sno
    from score
    where cno = (select cno from course where cname = '大学语文' and grade > 80)
)

10-397 在教师表中查询出所有教师所在部门编号，并消除重复记录。

select distinct DepartmentID
from Teacher

10-398 查询所有教师信息,按教师编号升序排列

select *
from Teacher
order by TeacherID 

10-399 查询出出生日期最大（即年龄最小）的学生姓名及出生日期

select StudentName,Birth
from Student
where Birth = (select max(Birth) from Student)

10-400 查询学生人数大于5人的班级编号

select ClassID
from Class
where studentnum > 5