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 scwhere 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 2sc.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,populationfrom 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,productwhere pc.model=product.modeland 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) >= 3select maker
from product
where model in (select modelfrom 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 scwhere cno in (select cno         -- 1.张老师所教课程 的课程号from couwhere teacher='张老师')group by sno           -- 3.通过学号分组having count(cno) = (  -- 4.每个人选课数量select count(*)    -- 2.张老师所教课程 的课程号 的数量from couwhere 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 creditelse 0 end creditfrom stuleft join sc on stu.sno=sc.snoleft 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='张三'unionselect 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年,年龄仅按年计算.

selecttemp.sno 学号,sname 姓名,sex 性别,2020 - year(birdate) 年龄,平均成绩
from stu
join ( select sno,avg(grade) 平均成绩  -- 1.from scgroup 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,SNamefrom studentwhere SId in (select SId        -- 2.查询选数据库学生的学号from scwhere CId = (select CId    -- 1.查询数据库的课程序号CIdfrom coursewhere 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)/10when Grade<60 then 0end pointfrom SC,Student,Coursewhere SC.Sno=Student.Snoand 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 Salaryfrom employeewhere 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='李玉敏'