SQL编程题复习（24/9/19）
练习题 x25
- 10-145 查询S001学生选修而S003学生未选修的课程（MSSQL）
- 10-146 检索出 sc表中至少选修了’C001’与’C002’课程的学生学号
- 10-147 查询平均分高于60分的课程（MSSQL）
- 10-148 检索C002号课程的成绩最高的二人学号，姓名与成绩（`——建议二刷`）
- 10-149 统计每个民族的学生总人数
- 10-150 统计每种商品的销售数量
- 10-151 将student表中的数计学院的学生信息插入到stu表中。
- 10-152 按城市所属的省份统计省份下所有城市的人口（`——建议二刷`）
- 10-153 查询各厂商生产的PC的最高价格
- 10-154 查询至少生产三种不同速度PC的厂商（`——建议二刷`）
- 10-155 查询生产三种不同型号的PC的厂商
- 10-156 查询速度低于任何PC的便携式电脑
- 10-157 查询在两种或两种以上PC机上出现的硬盘容量
- 10-158 查询拥有相同速度和内存的PC机的成对的型号，输出结果属性名分别为model1,model2（`———建议二刷`）
- 10-159 查询选修张老师讲授所有课程的学生（`——建议二刷`）
- 10-160 计算每位同学获得的总学分，并将所有学生的总学分按学号升序排序后一起插入到totalcredit表中（`——建议二刷`）
- 10-161 检索出学生‘张三’选修的所有及格的课程及成绩，最后计算他所获得的总学分。输出成绩结果集按课程号升序排序（`——建议二刷`）
- 10-162 查询平均成绩最高的前3名同学的学号，姓名，性别及年龄。假设当前为2020年,年龄仅按年计算.
- 10-163 显示已修数据库的同学信息，包括学号、姓名、班级名称
- 10-164 请设计一个视图V_average_point，计算学生平均绩点（`——建议二刷`）
- 10-165 建立’天津’的供应商视图vSup（`创建视图`）
- 10-166 删除没有销售过的产品
- 10-167 查询’A01’仓库中的职工中比’A02’任意一个职工薪水少的职工编号与姓名
- 10-168 创建一个每种货物的销售数量的视图good_total，要求是在2010年04月01日到2010年7月31日之间销售的货品，字段包括（gid,total）（`创建视图`）
- 10-169 检索李玉敏选修的课程编号及成绩
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='李玉敏'
原文地址：https://blog.csdn.net/Virgo_616/article/details/142406313
免责声明：本站文章内容转载自网络资源，如本站内容侵犯了原著者的合法权益，可联系本站删除。更多内容请关注自学内容网（zxcms.com）！