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

10-74 获取商品表中商品名称含有“pad”的商品

select id,name,price
from goods
where name like '%pad%'

10-75 获取指定商品的商品分类名称（多表查询）

结合sh_goods表和sh_goods_category表，查询商品id为5的商品分类名称

select name 
from sh_goods_category
where id in (
    select category_id
    from sh_goods
    where id=5
)

10-76 为sh_goods表添加一行记录

insert into sh_goods
values(2,3,'钢笔','001','练字必不可少',15,300,3.9,500)

10-77 检索出sh_goods表中每项keyword对应的商品数量，查询结果显示字段依据输出样例设置

select keyword,count(*) goodscount
from sh_goods
group by keyword

10-78 查询sh_goods表中id、category_id和name字段

select id,category_id,name
from sh_goods

10-79 删除sh_goods表中商品分类id不等于3的商品数据

-- 写法一
delete 
from sh_goods
where category_id <> 3

-- 写法二
-- delete 
-- from sh_goods
-- where id not in (
--     select id              -- 1.查询category_id=3的商品的id
--     from sh_goods
--     where category_id = 3
-- )

10-80 查询sh_goods表中价格在2000到6000元之间的商品编号、名称和价格

注意此范围包括2000元和6000元的商品

select id,name,price
from sh_goods
where price between 2000 and 6000

10-81 查询goods表中价格为NULL的商品信息（！？！）

-- 正确的SQL语法，但PTA编译不通过
-- select id,name,price
-- from goods
-- where price <=> NULL

-- 若使用 <=> NULL 编译不通过，则使用 is NULL
select id,name,price
from goods
where price is NULL

10-82 为sh_goods表添加一行记录，其中：

商品编号：1
商品分类：3
商品名称：2B铅笔
关键词编号：001
商品详情：考试专用
其余字段使用默认值

insert into sh_goods(id,category_id,name,keyword,content)
values(1,3,'2B铅笔','001','考试专用')

10-83 查询sh_goods表中用户评分score在前20%的商品名称

-- MySQL、PostgreSQL、SQLite 
-- select name
-- from sh_goods
-- order by score desc
-- limit 2

-- MSSQL、SQL Server
select top 2
    name
from sh_goods
order by score desc

-- 不同SQL间的语法存在差异

10-84 动态更新员工薪水（建议二刷）

1. 对当前工资在30万（包括）以上的员工，降薪10%
   对当前工资在25万（包括）到28万（包括）之间的员工，加薪20%
2. 对当前工资在30万（包括）以上的员工，降薪10%
   对当前工资在25万（包括）到28万（包括）之间的员工，加薪20%

-- 建议二刷（update、case when ... then ... end）

-- 更新 使用update...set
update Salary
set `count` = (
    case when `count`>=300000 then `count`*0.9
         when `count` between 250000 and 280000 then `count`*1.2
         else `count`  -- 不变
         end
)

-- update 语法
-- set 列名 = 
-- where 条件

-- case when 条件 then value1
--      when 条件 then value2
--      ...
--      else value
--      end (取别名)

-- 做人要有边界感，做题也是

10-85 查询sh_goods表中所有的keyword字段值，要求查询结果中不能包含重复值

select distinct keyword
from sh_goods

10-86 查询sh_goods表，先按商品分类category_id升序排列，对于相同分类的商品再按商品价格price降序排列

select name,category_id,price
from sh_goods
order by category_id asc,price desc

10-87 检索出Department表中编号为“Dp02”的系部对应的系部名称和系部主任

select DepartmentName,DepartmentHeader
from Department
where DepartmentID='Dp02'

10-88 将学号为“1911201”的学生系别改为“经管学院”，班级改为“19经管1”

update students
set sdept='经管学院',class='19经管1'
where sno='1911201'

10-89 将学号为“1911203”的学生的联系电话改为“590987”

update students
set phone = '590987'
where sno in ('1911203')

10-90 删除选课表SC中尚无成绩的选课记录

delete
from sc
where score <=> null

10-91 向students表中插入一条记录

(“2002031”，“胡静”，“20财管1”,“女”，“2000-3-21”，“黑龙江齐齐哈尔”,“230200200003210023”,“经管学院”,“6583891”)

-- 写法一
-- insert into students(sno,sname,class,ssex,bday,bplace,IDNum,sdept,phone)
-- values("2002031","胡静","20财管1","女","2000-3-21","黑龙江齐齐哈尔","230200200003210023","经管学院","6583891")

-- 写法二
insert into students
values("2002031","胡静","20财管1","女","2000-3-21","黑龙江齐齐哈尔","230200200003210023","经管学院","6583891")

10-92 插入学号为“2003024”、姓名为“李光”的学生信息

insert into students(sno,sname)
values('2003024','李光')

10-93 删除学号为“1911102”的学生记录

delete 
from students
where sno = '1911102'

10-94 把“李小鹏”同学的成绩全部删除

delete 
from sc
where sno in (
    select sno
    from students
    where sname = '李小鹏'
)

10-95 查询选修了“0000008”课程的学生的学号及其成绩，查询结果按分数降序排列

select sno,score
from sc
where cno='0000008'
order by score desc

10-96 检索 sc 表中成绩不及格的学生学号、课号和成绩，并按成绩降序排列

select sno,cno,score
from sc
where score < 60
order by score desc

10-97 检索出students表中信息学院的男生信息，查询结果按出生日期升序排序，出生日期相同的按生源地降序排序

select *
from students
where ssex='男'
and sdept = '信息学院'
order by bday asc,bplace desc

10-98 统计出students表中各系的男、女生人数，结果中包含系别、性别、人数这三个中文列标题

select 
    sdept 系别,
    ssex 性别,
    count(*) 人数
from students
group by sdept,ssex

10-99 统计出 teachers 表中各系的老师人数，并按人数升序排序，要求结果中列名分别显示“系别”、“教师人数”

select 
    tdept 系别,
    count(*) 教师人数
from teachers
group by tdept

10-100 检索出course表中“0000010”课程的课名、先修课号和学分记录

select cname,cpno,ccredit
from course
where cno = '0000010'

10-101 检索出 sc 表中有成绩的学生学号和课号的记录

select sno,cno
from sc
where not score <=> null
and not point <=> null

10-102 检索出students表中生源地不是“山东”省的学生信息

select *
from students
where not bplace like '%山东%'

-- 列名 like '山东' 包含山东
-- not 列名 like '山东' 不包含山东

10-103 检索出 sc 表中成绩在80～90分之间的选课成绩情况

select *
from sc
where score between 80 and 90

10-104 检索出 sc 表中成绩为69分、79分或89分的记录

select *
from sc
where score in (69,79,89)

10-105 检索出 teachers 表中在1970年1月1日之前出生的男教师信息

select *
from teachers
where tsex='男'
and tbirthday < '1970-01-01'

10-106 检索出students表中所有姓“刘”的学生信息

select *
from students 
where sname like '刘%'

10-107 检索出students表中姓名是两个字的学生信息

select *
from students
where sname like '__'

-- 模糊查询 '__' 表示匹配任意两个字符

10-108 检索出students表中非信息学院和机电学院的学生信息

select *
from students
where sdept not in('信息学院','机电学院')

10-109 从students表中统计总的学生人数，要求结果中列名显示“人数”

select count(*) 人数
from students

10-110 检索出 sc 表中各个课程号及相应的选课人数，要求结果中列名分别显示“课程号”、“选课人数”

select
    cno 课程号,
    count(*) 选课人数
from sc
group by cno

10-111 检索出students表中姓名中含有“明”字的男生的学生姓名和班级

select sname,class
from students
where sname like '%明%'
and ssex='男'

10-112 检索出students表中没有联系电话的学生信息

select *
from students
where phone <=> null

10-113 检索出students表中所有学生的学号、姓名、性别和出生日期的记录

select sno,sname,ssex,bday
from students