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

10-153 向订单表中插入一条新记录，订单编号是10331，顾客编号是J101，员工编号是023

insert into 订单(订单编号,顾客编号,员工编号)
values
('10331','J101','023')

10-154 向顾客表中插入一条新记录，顾客编号是Z001，公司名称是“大胜教育”，城市是“淄博”

insert into 顾客(顾客编号,公司名称,城市)
values
('Z001','大胜教育','淄博')-- 需要插入什么就写什么，其它可以不管

10-155 将订单表中2015年以前（包括2015年）的订单数据删除

delete 
from 订单
where year(订单日期)<=2015-- 审题
-- 包括2015年

10-156 将顾客表中顾客编号最后两位是“10”和“11”的记录删除

-- -- 方法一
-- delete 
-- from 顾客
-- where 顾客编号 like '%10'
-- or 顾客编号 like '%11'  -- 使用 or-- 将顾客表中顾客编号最后两位是“10”和“11”的记录删除-- 方法二，使用 right 函数指定后两位
delete 
from 顾客
where right(顾客编号,2) in ('10','11')

10-157 在读者表中查询账户余额的最高值和最低值

select max(余额) 最高余额,min(余额) 最低余额
from 读者

10-158 向借阅表中插入一条记录，账号是D004，条形码是TP204.2

insert into 借阅(账号,条形码)
values('D004','TP204.2')

10-159 本题目要求编写SQL语句，将图书表中科学出版社出版的图书价格上涨5%

update 图书
set 售价=售价*1.05
where 出版社='科学出版社'

10-160 查询图书表中2018年出版的图书的数目

select count(*) 2018年出版的图书数目
from 图书
where year(出版日期)=2018

10-161 查询图书表中各出版社图书的数目，要求过滤掉数目为1的数据

select 出版社,count(*) 图书数目
from 图书
group by 出版社
having 图书数目 <> 1-- SQL语句 执行顺序
from ...,... -> on -> (left/rigth join) -> where -> group by 
-> having -> select -> distinct -> order by -> limit

10-162 查询图书表中全部图书的平均售价

select avg(售价) 平均售价
from 图书

10-163 查询图书表中各出版社图书的数目，结果按图书数目降序排序

-- ok
-- select 出版社,count(*) 图书数目
-- from 图书
-- group by 出版社
-- order by count(*) descselect 出版社,count(*) 图书数目
from 图书
group by 出版社
order by 图书数目 desc-- SQL语句执行顺序
from ...,... -> on -> (left/rigth join) -> where -> group by 
-> having -> select -> distinct -> order by -> limit 

10-164 查询图书表中全部图书的最高售价

select max(售价) 最高售价
from 图书

10-165 查询图书表中科学出版社出版的价格在50元以上的图书的信息，要求结果中包括书名，作者，出版社和售价4列

select 书名,作者,出版社,售价
from 图书
where 出版社 = '科学出版社'
and 售价>50

10-166 查询图书表中全部图书的最低售价

select min(售价) 最低售价
from 图书

10-167 查询特定订单的详细信息

查找城市（City）是Madrid的顾客，所下的订单详细信息，显示为：顾客编号（CustomerID），公司名称（CompanyName），订单编号（OrderID），产品编号（ProductID）

select orders.CustomerID,CompanyName,orders.OrderID,ProductID
from orders
join orderdetails on orders.OrderID=orderdetails.OrderID
join customers on orders.CustomerID=customers.CustomerID-- 又臭又长的几个表 直接内连接
-- 避免 ambiguous 错误

10-168 查询图书表中未录入出版社信息的图书，要求结果中包含条形码，书名，出版社3列

select 条形码,书名,出版社
from 图书
where 出版社 <=> null
-- limit 0,3
limit 3

10-169 查询图书表中李凯所著的图书，要求查询结果中包括条形码，书名，作者，出版社4列

select 条形码,书名,作者,出版社
from 图书
where 作者 = '李凯'

10-170 查询供应商及其供应的产品情况

查找提供产品的各个供应商名称及其供应的产品，显示为：产品编号（ProductID），产品名称（ProductName），供应商编号(SupplierID)，公司名称（CompanyName）

select ProductID,ProductName,products.SupplierID,CompanyName
from products,suppliers
where products.SupplierID=suppliers.SupplierID-- 又臭又长的表 咔咔连接就行了，
-- 要什么查什么，注意避免 ambiguous 错误

10-171 将下述事实存入数据库:生产厂商C制造的型号为1100的PC机，速度240，内存32，硬盘2.5G，售价2499美元

insert into product(maker,model,type)
values('C','1100','个人电脑');
insert into pc(model,speed,ram,hd,cd,price)
values('1100',240,32,2.50,null,2499)

10-172 统计职工表(employees)中职务（Title）的个数，并重命名为countTitle

select count(distinct Title) countTitle
from employees-- distinct 去重！！！
-- 统计职工表中职务的数量

10-173 查询PC的平均速度

select avg(speed) avg_speed
from pc

10-174 查询价格超过2500美元的便携式电脑的平均速度

select avg(speed) avg_speed
from laptop
where price>2500

10-175 查询订单表(orders)的平均运费，并将其重命名为avgFreight

select avg(Freight) avgFreight
from orders

10-176 在产品表(products)中查询最低的单价(UnitPrice)，并重命名为minUnitPrice

select min(UnitPrice) minUnitPrice
from products

10-177 在产品表(products)中查询最多的库存数量(UnitsInStock)，并重命名为maxUnitsInStock

select max(UnitsInStock) maxUnitsInStock
from products

10-178 在订单表中查找特定国家且平均运费不小于10的信息

查询订单表(orders)中查找所在国家（ShipCountry）为Belgium，Switzerland，且平均运费不小于10的的顾客及运费信息，结果显示为顾客编号(CustomerID)和平均运费(avgFreight)

select CustomerID,avg(Freight) avgFreight
from orders
where ShipCountry in('Belgium','Switzerland')
group by CustomerID
having avg(Freight)>10

10-179 查找产品表中再次订购量大于15的产品信息

在产品表(products)中找出再次订购量(ReorderLevel)大于15的产品的产品编号（ProductID），产品名称（ProductName）和供货商编号（SupplierID）

select ProductID,ProductName,SupplierID
from products
where ReorderLevel > 15

10-180 查找产品表中再次订购量大于等于10且修订量大于订货数量的产品信息

在产品表(products)中找出再次订购量(ReorderLevel)大于等于10，且再次订购量(ReorderLevel)大于订购数量（UnitsOnOrder）的产品的产品编号（ProductID），产品名称（ProductName）和供货商编号（SupplierID）

select ProductID,ProductName,SupplierID
from products
where ReorderLevel>=10
and ReorderLevel > UnitsOnOrder-- 再次订购量(ReorderLevel)大于等于10 并且
-- 再次订购量(ReorderLevel)大于订购数量（UnitsOnOrder）
-- 的产品的信息-- 输出样例纯纯shit

10-181 查询传真号码不为空的供货商信息

在供货商（suppliers）中查找传真号码（Fax）不为NULL的供货商信息供货商编号（SupplierID），公司名称（CompanyName）

select SupplierID,CompanyName
from suppliers
-- where not Fax <=> null   -- 方法一
where Fax is not null       -- 方法二

10-182 在顾客表中找出特定名字的顾客信息

在顾客表(customers)中找出公司名(CompanyName)中包含字符串th的顾客编号和公司名称

select CustomerID,CompanyName
from customers
where CompanyName like '%th%'-- 包含

10-183 查询产品表中单价不在范围内的的产品信息

在产品表(products)中查询单价(UnitPrice)小于15或大于45的产品的产品编号（ProductID），产品名称（ProductName）和种类编号（CategoryID）

select ProductID,ProductName,CategoryID
from products
where UnitPrice < 15 
or UnitPrice > 45 

10-184 查询电影M3中的男影星

select name
from StarsIn
left join MovieStar on StarsIn.starName=MovieStar.name
where StarsIn.movieTitle='M3'
and MovieStar.gender='M'-- 理清思路

10-185 查询st1制片公司的总裁

select name            -- 2
from MovieExec
where certID in(select presCertID  -- 1from Studiowhere name='st1'
)-- 1.在Studio表查st1公司的 presCertID
-- 2.在MovieExec表中certID列 匹配 presCertID

10-186 根据所在国家查找订单信息

在订单表(orders)中找出所在国家(ShipCountry)是Germany或Brazil或France的订单编号(OrderID)和顾客编号(CustomerID)

select OrderID,CustomerID
from orders
where ShipCountry in ("Germany","Brazil","France")-- 注意审题

10-187 查询出顾客表(customers)中所有不重复的所在城市(City)

select distinct City
from customers

10-188 在产品表中找出库存数量大于50的产品的信息

select ProductID,ProductName
from products
where UnitsInStock>50

10-189 查询比电影M1时间更长的电影

select title,`year`
from Movie
where length > (select lengthfrom Moviewhere title='M1'
)-- 若列名和关键字重名,
-- 使用``包围该变量

10-190 检索出printer表中所有彩色打印机的元组

select *
from printer
where color=1

10-191 检索出pc表中具有1GB以上的硬盘容量而价格低于2000美元的所有个人计算机的型号(model)、速度(speed)以及硬盘容量(hd)

select model,speed,hd
from pc
where hd>1 
and price<2000

10-192 检索出 Studio表中制片公司st1的地址

select address
from Studio
where name='st1'