数据库第五次实验

目录

10-1 4-1 查询速度至少为160MHz的PC的制造商

10-2 4-2 查询价格最高的打印机型号

10-3 4-3 查询速度低于任何PC的便携式电脑

10-4 4-4 查询具有最高价格的机器的型号，机器包括PC、Laptop、Printer

10-5 4-5 查询具有最低价格的的彩色打印机的制造商

10-6 4-6 查询在具有最小内存容量的所有PC中具有最快处理器的PC制造商

10-7 查询选修‘C语言’课程的学生

10-8 查询S001学生选修而S003学生未选修的课程

10-9 查询所有学生的平均成绩

10-10 查询选修了“C语言”课程，但是没有选修“数据结构”课程的学生

---

10-1 4-1 查询速度至少为160MHz的PC的制造商

SELECT DISTINCT product.maker  
FROM product  
JOIN pc ON product.model = pc.model  
WHERE pc.speed > 160;

或

SELECT distinct product.maker
FROM product,pc
WHERE pc.speed>160 and product.model = pc.model

10-2 4-2 查询价格最高的打印机型号

SELECT  model
FROM printer
WHERE price = (
    SELECT MAX(price)
    FROM printer
)

10-3 4-3 查询速度低于任何PC的便携式电脑

SELECT laptop.model
FROM pc as p,laptop
WHERE p.speed = (
    SELECT MIN(pc.speed)
    FROM pc
) and laptop.speed < p.speed

10-4 4-4 查询具有最高价格的机器的型号，机器包括PC、Laptop、Printer

select model
from (select model, price from pc
      UNION
      select model, price from laptop
      UNION
      select model, price from printer) AS A
order by A.price DESC limit 1

10-5 4-5 查询具有最低价格的的彩色打印机的制造商

select distinct maker
from printer , product 
where product.model = printer.model
and price in (
    select min(price)
    from product , printer 
    where product.model = printer.model
    and printer.color = '1'
)

10-6 4-6 查询在具有最小内存容量的所有PC中具有最快处理器的PC制造商

select maker
    from pc,product
    where pc.model = product.model
    and ram = (select min(ram) from pc)
    and speed = (select max(speed) from pc where ram = (select min(ram) from pc));

10-7 查询选修‘C语言’课程的学生

select distinct s.sname as 姓名  ,c.grade as 成绩
from stu as s , cou ,sc as c
where c.cno = (
    select cno
    from cou
    where cou.cname = 'C语言'
) and s.sno = c.sno  
order by grade desc

10-8 查询S001学生选修而S003学生未选修的课程

select distinct sc.cno as 课程号
from sc
where sc.sno = 'S001' and sc.cno not in (
    select sc.cno
    from sc
    where sc.sno = 'S003'
)

10-9 查询所有学生的平均成绩

select stu.sno as 学号 ,ifnull(avg(grade),0) as 平均成绩
from stu left join sc on stu.sno = sc.sno
group by stu.sno

10-10 查询选修了“C语言”课程，但是没有选修“数据结构”课程的学生

select stu.sname from sc
join cou on cou.cno=sc.cno
join stu on stu.sno=sc.sno
where cou.cname='C语言'
and sname not in(
    select stu.sname from sc
    join cou on cou.cno=sc.cno
    join stu on stu.sno=sc.sno
    where cou.cname='数据结构'
)