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 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='李玉敏'