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

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

update employee
set Salary = Salary * 1.1
where Wno = 'A02'

-- 更新
-- update 表名
-- set 列名 = 
-- where 条件

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

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

10-238 删除数量为0的订单

delete 
from orders
where QTY = 0

-- 删除
-- delete
-- from 表名
-- where 条件

10-239 增加一个供货商，编号为’S05’，名称为’长城机电’，城市为’杭州’

insert into supplier(Sid,SName,City)
values
('S05','长城机电','杭州')

-- 增加/插入
-- insert into 表名(列名1,列名2,列名3...)
-- values
-- (值1,值2,值3...)

10-240 将’B01’号仓库的面积扩大20%

update warehouse
set Size=Size*1.2
where Wno = 'B01'

-- update 表名
-- set 列名 = 
-- where 条件

10-241 增加一名新员工，工号为’0031’，姓名为’王林’，工资为3500

insert into employee(Eid,EName,Wno,Salary)
values
('0031','王林','',3500)
-- 注意细节

10-242 增加一种产品，编号为’P21’,名称为’螺钉’,重量为3

insert into product(Pid,PName,Weight)
values
('P21','螺钉',3)

-- 增加/插入
-- insert into 表名(列名1,列名2,列名3...)
-- values
-- (值1,值2,值3...)

10-243 查询所有产品名中包含’螺母’的产品种类数

select count(*)
from product
where PName like '%螺母%'

-- 模糊查询

10-244 查询工资高于4000元的员工信息

select *
from employee
where Salary > 4000

10-245 查询所在城市为‘上海’或‘杭州’的仓库编号

select Wno
from warehouse
where City in ('上海','杭州') -- 加引号！？！

10-246 查询仓库号为’A01’的所有员工信息，并按照工资降序排列

select *
from employee
where Wno = 'A01'
order by Salary desc

10-247 查询销售过的产品编号

select distinct Pid
from orders
where QTY>0

10-248 查询重量在[40,65]之间的产品信息

select *
from product
where Weight between 40 and 65

10-249 查询姓刘的员工信息

select *
from employee
where EName like '刘%'

10-250 查询重量大于60的产品编号及产品名，并按照产品名升序排列

select Pid,PName
from product
where Weight> 60
order by PName asc

10-251 检索出生日期早于伍容华出生日期（不包含此日期）的所有学生信息。

select *
from student
where birth < (
    select birth -- 查询伍容华的出生日期
    from student
    where sname = '伍容华'
)

-- 直接比较birth列的值即可

10-252 显示出所有学生的学号，课号以及提高1分后的成绩 —— 不懂

-- update score
-- set grade = grade + 1;
-- select sno,cno,grade
-- from score

-- 为什么上面这种写法报错？！？
select 
    sno,
    cno,
    grade+1 grade
from score

10-253 检索出姓“王”的学生姓名和联系电话。（采用系统函数实现）

select sname,phone
from student
where sname like '王%'

10-254 显示所有课程的基本信息，课程编号不足6位的，左侧补0 —— LPAD填充函数

-- 报错
-- 1.更新 cno 列，使用LAPD函数 确保其长度为 6，并在左侧补充零
update 
set cno = LPAD(cno,6,'0');
-- 2.显示所有课程的基本信息
select cno 课程编号,cname 课程名称,credit 学分,attribute 类别
from course

10-255 检索出students表和sc表中“0000008”号课程不及格的学生信息。

select *
from students
where sno in (
    select sno
    from sc
    where cno = '0000008'
    and score < 60
)

10-256 检索出sc表、course表中选修课名中含有“数据库”三个字的课程且成绩在80～90分之间的学生学号及成绩。

select sno,score
from course 
join sc on course.cno=sc.cno
where cname like '%数据库%'
and score between 80 and 90

10-257 检索出students表中与“陆毅”同一个系的同学姓名。

select sname
from students
where sdept in (
    select sdept
    from students
    where sname = '陆毅'
)
and sname <> '陆毅'

10-258 检索出students表和sc表中选修了课号为“0000034”的学生学号和姓名。

select students.sno,sname
from students,sc
where students.sno=sc.sno
and sc.cno = '0000034'

10-259 将身份证号码为152221199710182012的学生所在院部改为“数计学院”。

update student
set dept = '数计学院'
where pnum = '152221199710182012'

10-260 将爱国者的库存数量减少10个。（模糊查询）

update good
set stock = stock -10
where gname like '爱国者%'

-- 模糊查询
-- 1.匹配以特定字符开头的字符串：'A%'
-- 2.匹配以特定字符结尾的字符串：'%height'
-- 3.匹配包含特定字符的字符串：'main'
-- 4.匹配特定长度的字符串：
-- 例，查找 users 表中所有用户名为三字符的记录（假设用户名只能有三个字符）
-- SELECT * 
-- FROM users
-- WHERE username LIKE '___';  -- 三个下划线表示三个字符
-- 5.忽略大小写匹配：
-- MySQL 的 LIKE 默认是大小写不敏感;
-- 如果需要区分大小写，可以使用 BINARY 关键字：
-- SELECT * 
-- FROM employees
-- WHERE BINARY name LIKE 'j%';

10-261 删除马齐的购物记录。

delete
from recorder
where cid in (
    select cid
    from customer
    where cname = '马齐'
)

-- 删除
-- delete
-- from 表名
-- where 条件

10-262 在recorder表中，为编号号为C008的会员添加以下销售记录。购买G001商品，数量是3。 购买G002商品，数量是1。日期暂时为空值。

insert into recorder(id,cid,gid,quantity,sale_date)
values
(11,'C008','G001',3,null),
(12,'C008','G002',1,null)

10-263 修改杰克的购物记录，将他购买G006商品的数量改为1。

update recorder
set quantity = 1
where gid = 'G006'
and cid in (
    select cid
    from customer
    where cname = '杰克'
)

-- update 表名
-- set 要修改的列名 = 值
-- where

-- update 语句中可以使用子查询

查询马齐的购物情况，查询结果以商品编号升序排列。

select good.gid 商品编号,gname 商品名称,price 单价,quantity 购买数量,sale_date 购买日期
from recorder
join good on good.gid=recorder.gid
join customer on customer.cid=recorder.cid
where cname = '马齐'
order by id asc
-- 避免 ambiguous 错误

10-265 没有被购买过的商品信息

select * 
from good
where gid not in (
    select gid
    from recorder
)

10-266 将学号为10001的学生电话号码改为13807589912。

update student
set phone = 13807589912
where sno = '10001'

-- update 表名
-- set 要更新列名 = 值
-- where 条件

-- update 中可以使用子查询

10-267 检索所有选修了40008课的学生学号和成绩，查询结果按学号升序排列。

select sno 学号,grade 成绩
from score
where cno = '40008'

10-268 查询数计学院学生总人数

select count(sno) 总人数
from student
where dept = '数计学院'

10-269 统计每个学院的学生总人数

select dept 院部,count(sno) 总人数
from student
group by dept

-- 每个学院，通过学院分组

10-270 查询所有1997出生的学生的学号、姓名、民族和身份证号码。

select sno,sname,nation,pnum  -- 2.
from student 
where sno in (
    select sno  -- 1.
    from student
    where year(birth) = 1997
)

-- 1.查询在1997年出生的学生的学号
-- 2.根据该学号查询结果

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

select cno,grade
from score
where sno in (
    select sno         
    from student
    -- where sname = "李玉敏"
    where sname like '李玉敏'
)

-- 1.在student表中查询 李玉敏 的学号
-- 2. 根据学号在score表中查询 李玉敏 的课程号和成绩

10-272 6-9查询速度超过150MHZ的各种速度的PC的平均价格

select speed,avg(price) avg_price
from pc
where speed>150
group by speed
-- 查询...的各种... - 考虑使用group by

10-273 6-5 查询各种不同速度的PC的平均价格

select speed,avg(price) avg_price
from pc
group by speed

-- 查询各种... - 考虑使用group by
-- 使用 group by 后，可不使用 distinct

10-274 查询各厂商生产的便携式电脑的显示器平均尺寸

select maker,avg(screen) avg_screen
from product,laptop
where product.model=laptop.model
group by maker

10-275 检索所授每门课程平均成绩均大于70分的教师姓名，并将检索的值插入一个新的教师表faculty表中 —— 建议二刷

insert into faculty(tname)
select teacher
from ( -- 1.查询每个老师每门课程的平均成绩
    select teacher,sc.cno,avg(grade) avg_grade
    from sc
    join cou on cou.cno=sc.cno
    group by teacher,sc.cno -- 每个
) tmp
group by teacher   -- 2.根据老师分组
having min(avg_grade) > 70   -- 3.满足最小的平均成绩都大于70

-- 使用 GROUP BY 函数可以避免使用 DISTINCT