SQL编程题复习（24/9/19）

10-145 查询S001学生选修而S003学生未选修的课程（MSSQL）

-- 在S001选修的课程中，但S003未选的课程
select cno 课程号
from sc
where sno='S001'
and cno not in (
    select cno        -- 1.S003选修的课程
    from sc
    where sno='S003'
)

10-146 检索出 sc表中至少选修了’C001’与’C002’课程的学生学号

select sno 学号
from sc
where cno in('C001','C002')  -- 1.至少选C001、C002
group by sno
having count(cno) >=2        -- 2.至少选修两门

10-147 查询平均分高于60分的课程（MSSQL）

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

-- 避免 ambiguous 错误

10-148 检索C002号课程的成绩最高的二人学号，姓名与成绩（——建议二刷）

select top 2
    sc.sno,sname,grade
from sc,stu
where cno='C002'
and sc.sno=stu.sno
order by grade desc

-- limit num 不行就使用 top num
-- 建议二刷

10-149 统计每个民族的学生总人数

select nation 民族,count(*) 总人数
from student
group by nation

10-150 统计每种商品的销售数量

select gid 商品编号,sum(quantity) 销售总数量
from recorder
group by gid

10-151 将student表中的数计学院的学生信息插入到stu表中。

insert into stu
select *
from student
where dept = '数计学院'

10-152 按城市所属的省份统计省份下所有城市的人口（——建议二刷）

浙江省：杭州，宁波，温州
江苏省：苏州，南京，无锡
请写sql统计出浙江省和江苏省所有人口

select name,sum(population) population
from (
    select case when name in('杭州','宁波','温州') then '浙江' 
                when name in('苏州','南京','无锡') then '江苏'
                end name,population
    from city
) temp
group by name

-- 派生表查询 + case when 条件 then value
--                  end 取别名

10-153 查询各厂商生产的PC的最高价格

select maker,max(price) max_price
from product,pc
where product.model=pc.model
and type='个人电脑'
group by maker

10-154 查询至少生产三种不同速度PC的厂商（——建议二刷）

-- 至少生产 三种不同速度PC的 厂商

select maker
from (
    select distinct maker,speed   -- 1.一定要去重
    from pc,product
    where pc.model=product.model
    and type='个人电脑'
) temp
group by maker
having count(speed)>=3            -- 2.为了使用count()函数

10-155 查询生产三种不同型号的PC的厂商

-- select maker
-- from product
-- where type='个人电脑'
-- group by maker
-- having count(model) >= 3


select maker
from product
where model in (
    select model
    from pc
)
group by maker
having count(model) >= 3

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

-- 速度 低于任何PC 的便携式电脑
-- laptop速度比最小的pc的速度还小
select model
from laptop
where speed < (
    select min(speed)
    from pc
)

10-157 查询在两种或两种以上PC机上出现的硬盘容量

select hd
from pc
group by hd
having count(*) >= 2

10-158 查询拥有相同速度和内存的PC机的成对的型号，输出结果属性名分别为model1,model2（———建议二刷）

-- 拥有 相同速度和内存 的PC机 的成对 的型号
select a.model model1,b.model model2
from pc a,pc b
where a.speed=b.speed
and a.ram=b.ram
and a.model < b.model   -- 避免重叠计算

-- ！！！建议二刷

10-159 查询选修张老师讲授所有课程的学生（——建议二刷）

-- 张老师 所讲授的(所有课程) 的学生

-- 查询张老师所教课程的课程号
select sname 
from stu
where sno in (
    select sno             -- 2.查询满足条件的人 的学号
    from sc
    where cno in (
        select cno         -- 1.张老师所教课程 的课程号
        from cou
        where teacher='张老师'
    )
    group by sno           -- 3.通过学号分组
    having count(cno) = (  -- 4.每个人选课数量
        select count(*)    -- 2.张老师所教课程 的课程号 的数量
        from cou
        where teacher='张老师'
    )
)

-- 多表嵌套查询
-- 建议二刷

10-160 计算每位同学获得的总学分，并将所有学生的总学分按学号升序排序后一起插入到totalcredit表中（——建议二刷）

注意：
1）当某门课程成绩在60分以上时才能合计计入总学分
2）如果某学生尚未选修任何课程时，总学分计为0，并插入到totalcredit表中

-- 当某门课程成绩在60分以上时 才能合计计入总学分
-- case when grade>=60 then credit
--      else 0 end 取别名

insert into totalcredit
select sno,sum(credit)
from (
    select stu.sno,case when grade>=60 then credit
                        else 0 
                        end credit
    from stu
    left join sc on stu.sno=sc.sno
    left join cou on sc.cno=cou.cno
) temp
group by sno 
order by sno asc

-- 多表连接
-- 左连接 stu 表
-- 建议二刷

10-161 检索出学生‘张三’选修的所有及格的课程及成绩，最后计算他所获得的总学分。输出成绩结果集按课程号升序排序（——建议二刷）

注意：选课成绩在60分以上才能获得相应的学分。cou表中credit列为某课程的学分值 。假定学生姓名没有重名的情

-- select 
--     cno 课程号,
--     cname 课程名,
--     grade 成绩,
--     credit 学分
-- from 


-- 方法一
-- select 
--     cou.cno 课程号,
--     cname 课程名,
--     grade 成绩,
--     credit 学分
-- from cou
-- join (
--     select cno,grade   -- 查询张三选修的课程和成绩
--     from sc
--     where grade > 60
--     and sno in (
--         select sno-- 1.查询张三的学号
--         from stu
--         where sname = '张三'
--     )
-- ) temp
-- on cou.cno=temp.cno

-- union
-- select 
--     stu.sname,
--     '所有及格课程',
--     '合计总学分',
--     sum(credit)
-- from stu,cou,sc
-- where stu.sno=sc.sno
-- and cou.cno=sc.cno
-- and stu.sname='张三'
-- and grade > 60

-- order by 课程号

-- -- 子查询、派生表查询、union
-- -- order by 不能写在union之前

-- 方法二
select cou.cno 课程号,cname 课程名,grade 成绩,credit 学分
from stu,cou,sc
where stu.sno=sc.sno
and cou.cno=sc.cno
and grade >= 60
and sname='张三'

union

select sname,'所有及格课程','合计总学分',sum(credit) 学分
from stu,cou,sc
where stu.sno=sc.sno
and cou.cno=sc.cno
and grade >= 60
and sname='张三'
-- order by cou.cno -- err
-- -- Table 'cou' from one of the SELECTs cannot be used in field list
order by 课程号

-- 多表等值连接
-- 建议二刷

10-162 查询平均成绩最高的前3名同学的学号，姓名，性别及年龄。假设当前为2020年,年龄仅按年计算.

select
    temp.sno 学号,
    sname 姓名,
    sex 性别,
    2020 - year(birdate) 年龄,
    平均成绩
from stu
join ( 
    select sno,avg(grade) 平均成绩  -- 1.
    from sc
    group by sno
) temp 
on stu.sno=temp.sno
order by temp.平均成绩 desc
limit 3

-- 1.在sc表中查询各个同学的平均成绩
-- 2.多表连接stu

10-163 显示已修数据库的同学信息，包括学号、姓名、班级名称

-- select SId,SName,GName
-- from 

select temp.SId,temp.SName,grade.GName
from grade
join (
    select SId,GId,SName
    from student
    where SId in (
        select SId        -- 2.查询选数据库学生的学号
        from sc
        where CId = (
            select CId    -- 1.查询数据库的课程序号CId
            from course
            where CName='数据库'
        )
    )
) temp
on grade.GId=temp.GId

-- 派生表查询

10-164 请设计一个视图V_average_point，计算学生平均绩点（——建议二刷）

成绩转换成绩点规则

create view V_average_point as
select Sdept,temp.Sno,avg(point) Average_point
from (
    select 
        Sdept,
        SC.Sno,
        case when Grade>=60 then (Grade-50)/10
             when Grade<60 then 0
             end point
    from SC,Student,Course
    where SC.Sno=Student.Sno
    and SC.Cno=Course.Cno
) temp
group by temp.Sno

-- 先多表连接查询学生的绩点
-- 使用派生表查询
-- 建议二刷！！！

10-165 建立’天津’的供应商视图vSup（创建视图）

create view vSup as
select *
from supplier
where City = '天津'

10-166 删除没有销售过的产品

delete
from product
where Pid not in (
    select Pid    -- 1.查询有销售记录的商品
    from orders
)

10-167 查询’A01’仓库中的职工中比’A02’任意一个职工薪水少的职工编号与姓名

select Eid,EName
from employee
where Salary < any(
    select Salary
    from employee
    where Wno = 'A02'
)
and Wno = 'A01'

-- any函数
-- 查询比任意一个员工薪水少的人

10-168 创建一个每种货物的销售数量的视图good_total，要求是在2010年04月01日到2010年7月31日之间销售的货品，字段包括（gid,total）（创建视图）

create view good_total as
select gid,sum(quantity) total
from sale_recorder
where sale_date >= '2010-04-01' 
and sale_date <= '2010-07-31'
group by gid

-- create view good_total as
-- select gid,sum(quantity) total
-- from sale_recorder
-- where sale_date between '2010-04-01' 
-- and '2010-07-31'
-- group by gid

-- 创建视图
-- create view 视图名 as

10-169 检索李玉敏选修的课程编号及成绩

select cno,grade
from student
join score on student.sno=score.sno
where sname='李玉敏'