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

练习题 x40

10-193 在顾客表(customers)中找出所在城市(City)为London的公司名(CompanyName)和联系人名(ContactName)
10-194 查询价格低于1600美元的个人计算机的型号、速度及硬盘容量,将"speed"改为"兆赫"，"hd"改为"吉字节"
10-195 查询费用高于2000美元的便携式电脑的型号)、内存容量以及屏幕尺寸
10-196 删除学生姓名为'周强'的所有数据信息
10-197 检索出MovieStar表中影星S1的出生日期
10-198 查询价格低于1600美元的个人计算机的型号(model)、速度(speed)及硬盘容量(hd)
10-199 检索出product表中打印机的制造商(maker)
10-200 在Student表中插入一条id=99, name="test"的记录
10-201 删除Student表中所有id>50的记录
10-202 在stu表中添加一条学生记录：
10-203 在sc表中，为学号为S012的学生添加以下学生选课成绩记录。
10-204 将stu表中的软件工程专业（专业号mno为'02'）的学生记录批量插入到一个学生简表softstu表中
10-205 把所有低于 75 分的女生成绩提高5%
10-206 在SC表中删除成绩为NULL的学生选课记录
10-207 在sc表中查询平均成绩高于75分的学生
10-208 检索出sc表中‘C001’课程未登记成绩的学生学号
10-209 检索出Student表中所有符合id > 50的记录
10-210 检索出 stu 表中学生的学号、姓名、性别.
10-211 检索出 stu表中年龄在18-20之间的学生记录。
10-212 检索出 stu表中‘计算机工程’或‘软件工程’专业的学生的记录，结果集按学号升序排序
10-213 将Student表中所有符合id>50的记录的name字段改成"test"
10-214 检索出stu表中所有学生记录
10-215 检索出 stu 表中所有的女生记录。
10-216 检索出stu表中所有年龄大于19岁的女生的记录。
10-217 检索出stu表中所有姓‘李’的学生记录
10-218 统计xscj表中计算机课程成绩在90至100之间的人数（包含90和100）
10-219 计算xscj表中计算机课程的最低分
10-220 计算xscj表中英语课程的平均分
10-221 计算xsda表中最高同学的身高
10-222 计算xsda表中最矮同学的身高
10-223 查询xscj表中的计算机成绩在80至90之间（包含80和90分）的同学的学号，姓名，计算机三项信息
10-224 计算xscj表中计算机课程的最高分
10-225 查询xscj表中的学号，姓名，计算机三项信息，结果按计算机成绩的降序排列
10-226 检索出每个班级中分数最低的同学id，姓名，分数，班级名称 `—— 建议二刷`
10-227 统计每个班级的人数，显示班级编号，人数（Num），并按人数降序排列
10-228 统计每个班每周上课的课时数，显示班级编号和课时数，并按课时数降序排列 `—— 不会`
10-229 按开课院系统计每个院系开课的学分数（Credit），显示院系编号和总学分，并按学分数降序排列
10-230 将’长城机电’的所在城市修改为’绍兴’
10-231 将产品号为’P21’的产品的重量调整为4
10-232 给所有产品名中含’螺母’的订单的数量增加40%
10-233 删除重量大于100的产品
10-234 将’A02’仓库的员工工资上调10%
10-235 删除没有确定工作仓库的员工信息

10-193 在顾客表(customers)中找出所在城市(City)为London的公司名(CompanyName)和联系人名(ContactName)

sql">select CompanyName,ContactName
from customers
where City in ('London')

10-194 查询价格低于1600美元的个人计算机的型号、速度及硬盘容量,将"speed"改为"兆赫"，“hd"改为"吉字节”

sql">select model,speed 兆赫,hd 吉字节
from pc
where price<1600

10-195 查询费用高于2000美元的便携式电脑的型号)、内存容量以及屏幕尺寸

sql">select model,ram,screen
from laptop
where price>2000

10-196 删除学生姓名为’周强’的所有数据信息

sql">-- error
-- delete
-- from stu
-- where sname ='周强';
-- delete
-- from sc
-- where sno ='S012'-- -- 先删周强，再用周强的学号删表2 或者
-- -- 都用学号删 - 不行delete
from sc
where sno in (select snofrom stuwhere sname='周强'
);
delete
from stu
where sname = '周强'
-- 严格注意两个表 删除的先后顺序！？！

10-197 检索出MovieStar表中影星S1的出生日期

sql">select birthdate
from MovieStar
where name='S1'

10-198 查询价格低于1600美元的个人计算机的型号(model)、速度(speed)及硬盘容量(hd)

sql">select model,speed,hd
from pc
where price<1600

10-199 检索出product表中打印机的制造商(maker)

sql">select distinct maker
from product
where type='打印机'-- 考虑去重 distinct

10-200 在Student表中插入一条id=99, name="test"的记录

sql">insert into Student(id,name)
values(99,'test')

10-201 删除Student表中所有id>50的记录

sql">delete
from Student
where id>50-- 删除delete语法
delete
from 表名
where 条件

10-202 在stu表中添加一条学生记录：

学号：S012，姓名：周强，性别：1，其它属性为NULL

sql">insert into stu(sno,sname,sex)
values('S012','周强',1)-- 其它属性为NULL
-- 不管其它列，即可

10-203 在sc表中，为学号为S012的学生添加以下学生选课成绩记录。

选修C001课程，成绩为90。选修C002课程，成绩为空

sql">insert into sc(sno,cno,grade)
values
('S012','C001',90),
('S012','C002',null)-- 多行插入

10-204 将stu表中的软件工程专业（专业号mno为’02’）的学生记录批量插入到一个学生简表softstu表中

sql">insert into softstu(sno,sname)
select sno,sname
from stu
where mno='02'-- 1.insert基本语法
INSERT INTO table_name (column1, column2, column3, ...)
VALUES (value1, value2, value3, ...);
-- 2.多行插入
INSERT INTO students (id, name, age, class_id)
VALUES (2, 'Jane Smith', 22, 102),(3, 'Alice Johnson', 21, 101),(4, 'Bob Brown', 23, 103);
-- 3.使用 SELECT 进行插入
INSERT INTO new_students (id, name, age, class_id)
SELECT id, name, age, class_id
FROM old_students
WHERE age > 20;

10-205 把所有低于 75 分的女生成绩提高5%

sql">update sc
set grade=grade*1.05
where sno in(select sno -- 1.选择女生from stuwhere sex='0'
)
and grade < 75 -- 2.成绩小于75

10-206 在SC表中删除成绩为NULL的学生选课记录

sql">delete
from sc
where grade <=> null-- 删除
-- delete 
-- from 表名
-- where 条件

10-207 在sc表中查询平均成绩高于75分的学生

sql">select sno 学号,avg(grade) 平均成绩
from sc
group by sno
having avg(grade) > 75-- 注意细节

10-208 检索出sc表中‘C001’课程未登记成绩的学生学号

sql">select sno
from sc
where cno='C001'
and grade <=> null-- 未登记成绩，即成绩为空

10-209 检索出Student表中所有符合id > 50的记录

sql">select *
from Student
where id>50

10-210 检索出 stu 表中学生的学号、姓名、性别.

注意：性别为’1’时显示‘男’，性别为‘0’时显示 ‘女’

sql">select sno,sname,-- if(sex,'男','女') sex       -- 方法一-- case sex when 1 then '男'   -- 方法二--          when 0 then '女'--          end "sex"case when sex=0 then '女'      -- 方法三when sex=1 then '男'end 'sex'
from stu-- 流程控制函数
-- 1.if
if(value,value1,value2)
value为真, 返回value1, 否则返回 
-- 2.ifnull
ifnull(value1,value2)
value1为真返回value1,否则返回value2
-- 3.case when ... then ...
--        when ... then ...
--                 else ...
--        end 取别名
case when 条件1 then value1when 条件2 then value2when 条件3 then value3when ...  then ...(else value) end '取别名'
-- 4.case 列名 when ... then ...
--            when ... then ...
--                     else ...
--            end 取别名
case 列名 when 该列的值 then ...when ... then ...else ...end 取别名

10-211 检索出 stu表中年龄在18-20之间的学生记录。

注意: 计算年龄时以年计算，不考虑出生月份。假定当前日期为‘2020-03-01’。

sql">select sno 学号,sname 姓名,sex 性别,mno 专业,2020 - year(birdate) 年龄,memo 备注
from stu
-- where 年龄 between 18 and 20 -- error
-- Unknown column '年龄' in 'where clause
where 2020 - year(birdate) between 18 and 20

10-212 检索出 stu表中‘计算机工程’或‘软件工程’专业的学生的记录，结果集按学号升序排序

sql">select sno 学号,sname 姓名,sex 性别,mname 专业
from stu
left join major on stu.mno=major.mno
where mname in ('计算机工程','软件工程')
order by sno asc-- 避免 ambiguous 错误
-- 根据输出样例 明确要查询的列名

10-213 将Student表中所有符合id>50的记录的name字段改成"test"

sql">update Student
set name='test'
where id>50

10-214 检索出stu表中所有学生记录

sql">select *
from stu

10-215 检索出 stu 表中所有的女生记录。

注意：sex为1时表示男生，sex为0时表示女生

sql">select sno 学号,sname 姓名
from stu
where sex=0

10-216 检索出stu表中所有年龄大于19岁的女生的记录。

注意：sex为1时表示男生，sex为0时表示女生。
计算年龄时以年计算，不考虑出生月份。
提示：假定当前日期为‘2020-03-01’

sql">select sno 学号,sname 姓名,sex 性别,mno 专业,2020 - year(birdate) 年龄, -- 查询年龄memo 备注
from stu
where (2020 - year(birdate)) > 19
and sex = 0

10-217 检索出stu表中所有姓‘李’的学生记录

sql">select sno 学号,sname 姓名,sex 性别,mno 专业,birdate 出生日期,memo 备注
from stu
where sname like '李%'-- 模糊查询

10-218 统计xscj表中计算机课程成绩在90至100之间的人数（包含90和100）

sql">select count(*) 计算机优秀人数
from xscj
where 计算机 between 90 and 100

10-219 计算xscj表中计算机课程的最低分

sql">select min(计算机) 计算机最低分
from xscj

10-220 计算xscj表中英语课程的平均分

sql">select avg(英语) 英语平均分
from xscj

10-221 计算xsda表中最高同学的身高

sql">select max(身高) 最高同学的身高
from xsda

10-222 计算xsda表中最矮同学的身高

sql">select min(身高) 最矮同学的身高
from xsda

10-223 查询xscj表中的计算机成绩在80至90之间（包含80和90分）的同学的学号，姓名，计算机三项信息

sql">select 学号,姓名,计算机
from xscj
where 计算机 between 80 and 90

10-224 计算xscj表中计算机课程的最高分

sql">select max(计算机) 计算机最高分
from xscj

10-225 查询xscj表中的学号，姓名，计算机三项信息，结果按计算机成绩的降序排列

sql">select 学号,姓名,计算机
from xscj
order by 计算机 desc

10-226 检索出每个班级中分数最低的同学id，姓名，分数，班级名称 `—— 建议二刷`

sql">select id stu_id,name stu_name,class_name,score
from (select  -- 1.代表连接查询 tb_student.id,tb_student.name,tb_class.name,tb_score.score 作为派生表tb_student.id,tb_student.name,tb_class.name class_name,tb_score.scorefrom tb_student_classjoin tb_score on tb_score.stu_id=tb_student_class.stu_idjoin tb_class on tb_class.id=tb_student_class.class_idjoin tb_student on tb_student.id=tb_student_class.stu_id
) temp
where (class_name,score) in ( -- （a,b) in (a,b)select class_name,min(score) -- 2.给 tb_class.name 取别名为 class_name，并根据class_name分组，找最小分数from (select tb_student.id,tb_student.name,tb_class.name class_name,tb_score.scorefrom tb_student_classjoin tb_score on tb_score.stu_id=tb_student_class.stu_idjoin tb_class on tb_class.id=tb_student_class.class_idjoin tb_student on tb_student.id=tb_student_class.stu_id) tempgroup by class_name
)-- 派生表 & group by
-- 1.多表连接保留有用的列，2.分组找各个班级成绩最低的学生 
-- 建议二刷

10-227 统计每个班级的人数，显示班级编号，人数（Num），并按人数降序排列

sql">select GId,count(SId) Num
from student
group by GId
order by Num desc-- 统计人数count
-- 每个班的人数 - 分组

10-228 统计每个班每周上课的课时数，显示班级编号和课时数，并按课时数降序排列 `—— 不会`

sql">select GId,sum(length(ITimeseg)) Hours
from information
group by GId
order by Hours desc-- LENGTH(ITimeseg) 
-- 用于计算 ITimeseg 列中每个字符串的字符数（长度）;
-- SUM(LENGTH(ITimeseg))
-- 计算 LENGTH(ITimeseg) 的总和

10-229 按开课院系统计每个院系开课的学分数（Credit），显示院系编号和总学分，并按学分数降序排列

sql">select DId,sum(CCredit) Credit
from course
group by DId
order by Credit desc-- 1.求和2.分组3.排序

10-230 将’长城机电’的所在城市修改为’绍兴’

sql">update supplier
set City = '绍兴'
where SName = '长城机电'

10-231 将产品号为’P21’的产品的重量调整为4

sql">update product
set Weight=4
where Pid = 'P21'-- 调整/更新
-- update 表名
-- set 列名=
-- where 条件

10-232 给所有产品名中含’螺母’的订单的数量增加40%

sql">update orders
set QTY=QTY*1.4 
where Pid in (select Pidfrom productwhere PName like '%螺母%'
)-- 模糊查询like
-- update 表名
-- from 列名=
-- where 条件

10-233 删除重量大于100的产品

sql">delete 
from product
where Weight>100-- 删除
-- delete 
-- from 表名
-- where 条件

10-234 将’A02’仓库的员工工资上调10%

sql">update employee
set Salary = Salary * 1.1
where Wno = 'A02'-- 更新
-- update 表名
-- set 列名 = 
-- where 条件

10-235 删除没有确定工作仓库的员工信息

sql">delete
from employee
-- where Wno is null
where Wno <=> null