数据库第六次实验

目录

10-1 5-1 查询销售便携式电脑但不销售PC的厂商

10-2 5-2 查询至少生产两种不同的计算机(PC或便携式电脑)且机器速度至少为133的厂商

10-3 5-3 查询生产最高速度的计算机(PC或便携式电脑)厂商

10-4 5-4 查询至少生产三种不同速度PC的厂商

10-5 5-5 查询只卖三种不同型号PC的厂商

10-6 查询选修张老师讲授所有课程的学生

10-7 查询平均分高于60分的课程

10-8 查询平均成绩高于75分的学生

10-9 查询各专业学生的平均成绩

10-10 查询没有选修'C语言'课程的学生

---

10-1 5-1 查询销售便携式电脑但不销售PC的厂商

select distinct a.maker from(
    select pr.maker from product pr
        join laptop l on l.model=pr.model
    
) a
where a.maker not in(
    select pr.maker from product pr
    join pc p on p.model = pr.model
)

10-2 5-2 查询至少生产两种不同的计算机(PC或便携式电脑)且机器速度至少为133的厂商

select distinct maker 
    from
        (select  maker
                from
                    (select maker, count(maker)
                          from (select  maker
                                        from pc,product
                                        where pc.model = product.model
                                        and pc.speed >= 133) as temp1
                          group by maker  having count(maker) >= 2     
                    union     
                    select maker, count(maker)
                          from (select  maker
                                        from laptop,product
                                        where laptop.model = product.model
                                        and laptop.speed >= 133) as temp2
                          group by maker  having count(maker) >= 2) as temp3
        union
        select maker
               from laptop,product
               where laptop.model = product.model
               and  product.maker in (select maker
                                            from pc,product
                                            where pc.model = product.model)) as temp4
      order by maker;

10-3 5-3 查询生产最高速度的计算机(PC或便携式电脑)厂商

select  a.maker from (select pr.maker,p.speed 
from product pr join pc p on p.model=pr.model
   where p.speed=(select max(speed) from pc)
union
select pr.maker,l.speed
from product pr join laptop l on l.model=pr.model
   where l.speed=(select max(speed) from laptop)) a
group by maker

10-4 5-4 查询至少生产三种不同速度PC的厂商

SELECT maker FROM(
SELECT a.maker,b.speed 
FROM product a JOIN pc b ON a.model=b.model
GROUP BY b.speed,a.maker)a
GROUP BY maker 
HAVING COUNT(*)>=3

10-5 5-5 查询只卖三种不同型号PC的厂商

select distinct a.maker from(
select pr.maker,pr.model,pr.type
    from product pr join pc p on p.model=pr.model
) a
where a.maker not in 
(select distinct maker from product where type<>'个人电脑')
group by a.maker
having count(model)=3

10-6 查询选修张老师讲授所有课程的学生

select sname
from stu
where not exists
(select * 
from cou
where not exists
(select *
from sc 
where sc.sno=stu.sno and sc.cno=cou.cno) and cou.teacher = '张老师')

10-7 查询平均分高于60分的课程

select  cou.cno 课程号,max(cou.cname)  课程名 from cou 
join sc on cou.cno=sc.cno
where cou.cno in
(select cno from sc  group by cno having avg(grade)>60 )
group by cou.cno

10-8 查询平均成绩高于75分的学生

select sno as 学号,avg(grade) as 平均成绩
from sc
group by sno
having avg(grade)>75

10-9 查询各专业学生的平均成绩

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

10-10 查询没有选修'C语言'课程的学生

select stu.sno 学号,stu.sname 姓名 from
stu 
where
stu.sno not in (
select sno from sc
    join cou on sc.cno=cou.cno
    where cou.cname='C语言'
)
order by sno asc