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

10-114 检索出course表中前3门课程的课号及课程名称的记录

select cno,cname
from course
limit 0,3

10-115 检索出students表中“信息学院”的学生姓名、性别和出生日期的记录

select sname,ssex,bday
from students
where sdept = '信息学院'

10-116 检索出students表中所有系名的记录，要求结果中系名不重复

select sdept
from students
group by sdept

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

select sno
from sc
where cno='C001'
-- and grade <=> null -- MSSQL不支持
and grade is null

10-118 查询学号为‘S001’或‘S003’的学生选修的课程，输出结果集按课程号升序排序，且不包含重复的课程记录

select distinct 
    cou.cno 课程号,
    cou.cname 课程
from cou
join sc on cou.cno = sc.cno
where sc.sno in ('S001', 'S003')
order by 课程号 asc

-- 多表连接，避免 ambiguous 错误
-- 排序
-- 去重

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

select *
from stu

10-120 检索出 stu 表中学生的学号、姓名、性别.（MSSQL）

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

select
    sno,
    sname,
    -- if(sex,'男','女') sex   -- MSSQL不支持
    case sex when 1 then '男'
             when 0 then '女'
             end sex
from stu

-- MSSQL和SQL通用函数为case when

10-121 检索出 stu 表中所有的女生记录。注意：sex为1时表示 男生，sex为0时表示女生

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

10-122 检索出stu表中所有姓‘李’的学生记录（MSSQL）

select 
    sno 学号,
    sname 姓名,
    sex 性别,
    mno 专业,
    birdate 出生日期,
    memo 备注
from stu
-- where sname like '李%'  -- MSSQL语法不支持
where sname like N'李%'   -- ok

10-123 查询单价少于500的货品信息

select 
    gid 商品编号,
    gname 商品名称,
    price 单价,
    stock 库存
from good
where price < 500

10-124 找出所有姓“张”的学生学号、姓名、院部和联系电话

select 
    sno 学号,
    sname 姓名,
    dept 院部,
    phone 电话
from student
where sname like '张%'

10-125 查看1998年出生的学生信息，查询结果以学号升序排列

select 
    sno 学号,
    sname 姓名,
    pnum 身份证号码
from student
where year(birth)=1998
order by 学号 asc

-- SQL执行顺序
from ...,... -> on -> (left/rigth join) -> where -> group by
-> having -> select -> distinct -> order by -> limit 

10-126 检索所有女会员的基本信息

select *
from customer
where sex='女'

10-127 查询库存数量少于150的货品信息

select
    gid 商品编号,
    gname 商品名称,
    price 单价,
    stock 库存
from good
where stock <= 150

-- 观察输出样例

10-128 查看2010年的销售记录，查询结果以销售日期升序排列

select *
from recorder
where year(sale_date)=2010
order by sale_date asc

10-129 检索出所有课程性质为“必修”的课程号、课程名和学分

select 
    cno 课程号,
    cname 课程名,
    credit 学分
from course
where attribute = '必修'

10-130 查看所有学生的基本信息

select *
from student

10-131 检索所有女同学的基本信息

select *
from student
where sex='女'

10-132 找出所有姓“李”的学生姓名、民族和联系电话

select
    sname,
    nation,
    phone
from student
where sname like '李%'

10-133 检索出所有成绩为空的学号，课号

select sno,cno
from score
where grade <=> null

10-134 检索出Student表中所有符合id > 50的记录（题目错误）

select *
from Student
where id < 50
order by id asc

-- 抽象！？！

10-135 通过订单表和顾客表，查询订单编号，顾客编号，公司 名称和订单日期

select 订单编号,订单.顾客编号,公司名称,订单日期
from 订单
join 顾客 on 订单.顾客编号=顾客.顾客编号

-- 多表连接 避免ambiguous 错误

10-136 在员工表中查询入职最晚的员工的编号，姓名和入职日期

select 员工编号,姓名,入职日期
from 员工
where 入职日期 = (
    select max(入职日期) 
    from 员工
)

10-137 在员工表中查询每年入职的员工人数，结果按入职年份升序排列

select 年份,count(*) 入职人数
from (
    select 
        员工编号,
        姓名,
        性别,
        出生日期,
        year(入职日期) 年份,
        电话
    from 员工
) temp
group by 年份

-- 派生表查询

10-138 在员工表中查询全体员工的编号，姓名和年龄

提示：年龄需要计算得到，假定当前系统年份是2021年，不要用计算年份的函数

select 
    员工编号,
    姓名,
    2021 - year(出生日期) 年龄
from 员工

10-139 在顾客表中查询公司城市在“济南”的顾客数目

select count(*) 济南顾客数
from 顾客
where 城市 in ('济南')

10-140 在员工表中查询姓陈的男职工的全部信息

select *
from 员工
where 姓名 like '陈%'
and 性别 = '男'

10-141 在订单表中查询不是023号员工处理的订单的信息，要求包括订单编号，顾客编号，员工编号和订单日期

select 订单编号,顾客编号,员工编号,订单日期
from 订单
where 员工编号 <> '023'

10-142 在顾客表中查询各个城市的顾客数目

select 城市,count(*) 顾客数
from 顾客
group by 城市

10-143 在订单表中查询运费的平均值

select avg(运费) 平均运费
from 订单

10-144 在订单表中查询运费的最大值和最小值

select 
    max(运费) 最高运费,
    min(运费) 最低运费
from 订单

10-145 删除顾客表(customers)中城市（City）为London的顾客信息

delete 
from customers
where City in ('London')

10-146 在区域表（region）中添加一条记录：区域编号（RegionID）为5，区域描述（RegionDescription）为Center

insert into region(RegionID,RegionDescription)
values
(5,'Center')

10-147 在员工表中查询所有男性员工的编号，姓名和入职日期，结果按员工编号升序排列

select 员工编号,姓名,入职日期
from 员工
where 性别 = '男'
order by 员工编号 asc

10-148 在顾客表中查询顾客编号，公司名称和所在城市这三项内容

select 顾客编号,公司名称,城市
from 顾客

10-149 修改订单表（orders）中的运费，将每单运费（Freight）增加50%

update orders
set Freight = Freight *1.5

10-150 修改订单表（orders）中员工编号（EmployeeID）为3和4的员工的订单，将每单运费（Freight）减少5%

update orders
set Freight=Freight*0.95
where EmployeeID in(3,4)

10-151 在读者表中查询所有等级为“白银”的读者的余额之和

select sum(余额) 白银读者余额之和
from 读者
where 等级 = '白银'

10-152 查询图书表中售价最高的图书的全部信息

select *
from 图书
where 售价 = (
    select max(售价)
    from 图书
)