R10-1 A1-3查询顾客表中所有不重复的城市 (2 分)
select distinct City
from customers
R10-2 查询学生表所有学生记录 (2 分)
select * from stu
R10-3 查询图书表中所有记录 (2 分)
select * from 图书
R10-4 查询图书表中有哪些出版社，要求结果没有重复值 (2 分)
select distinct 出版社 from 图书
R10-5 在读者表中查询账号和姓名 (2 分)
select 账号,姓名 from 读者
R10-6 查询读者表中有哪些等级，结果中不能出现重复值 (2 分)
select distinct 等级 from 读者
R10-7 查询员工表全部信息 (2 分)
select * from 员工
R10-8 在顾客表中查询顾客编号，公司名称和所在城市这三项内容 (2 分)
select 顾客编号,公司名称,城市 from 顾客
R10-9 在顾客表中查询出现了那些城市，要求不显示重复值 (2 分)
select distinct 城市 from 顾客
R10-10 2-2-(a)查询价格低于1600美元的个人计算机的型号(model)、速度(speed)及硬盘容量(hd) (3 分)
select model,speed,hd
from pc
where price<1600
R10-11 2-2-(b)查询价格低于1600美元的个人计算机的型号、速度及硬盘容量,将"speed"改为"兆赫"，“hd"改为"吉字节” (3 分)
select model,speed as 兆赫,hd as 吉字节
from pc
where price<1600
R10-12 2-2-?查询打印机的制造商 (3 分)
select distinct maker
from product
where type=‘打印机‘
R10-13 2-2-(d)查询费用高于2000美元的便携式电脑的型号)、内存容量以及屏幕尺寸 (3 分)
select model,ram,screen
from laptop
where price>2000
R10-14 2-2-(e)查询所有彩色打印机的元组 (3 分)
select *
from printer
where color=‘1‘
R10-15 A2-1查找产品表中再次订购量大于15的产品信息 (3 分)
select ProductID,ProductName,SupplierID
from products
where ReorderLevel>15
R10-16 2-1(a) 查询st1制片公司的地址 (3 分)
select address
from Studio
where name=‘st1‘
R10-17 2-1(b)查询影星S1的出生日期 (3 分)
select birthdate
from MovieStar
where name=‘S1‘
R10-18 spj-查询供应工程 j1 的供应商 (3 分)
select distinct sno from spj where jno=‘j1‘;
R10-19 A1-1查询联系人信息 (3 分)
select CompanyName,ContactName
from customers
where City=‘London‘
R10-20 A1-4在产品表中找出库存数量大于50的产品的信息 (3 分)
select ProductID,ProductName
from products
where UnitsInStock>50
R10-21 A1-5在顾客表中找出特定名字的顾客信息 (3 分)
select CustomerID,CompanyName
from customers
where CompanyName like ‘%th%‘
R10-22 A1-7在产品表中找出库存量小于订购量的产品信息 (3 分)
select ProductID,ProductName
from products
where UnitsInStock<UnitsOnOrder
R10-23 A1-8查询传真号码不为空的供货商信息 (3 分)
select SupplierID,CompanyName
from suppliers
where Fax is not null
R10-24 查询学生表中的女生信息 (3 分)
select sno as 学号,sname as 姓名
from stu
where sex=0
R10-25 查询姓‘李’的学生记录 (3 分)
select sno as 学号,sname as 姓名,sex as 性别,mno as 专业,birdate as 出生日期,memo as 备注
from stu
where sname like ‘李%‘
R10-26 查询未登记成绩的学生 (3 分)
select sno
from sc
where grade is null
R10-27 查询xsda表中所有女生的记录 (3 分)
select *
from xsda
where 性别=‘女‘
R10-28 sql-select-sample (3 分)
select * from Student where id>50
R10-29 查询图书表中李凯所著的图书，要求查询结果中包括条形码，书名，作者，出版社4列。 (3 分)
select 条形码,书名,作者,出版社 from 图书 where 作者=‘李凯‘
R10-30 在订单表中，查询运费在50元以下（不包括50元）的订单的全部信息 (3 分)
select * from 订单 where 运费<50
R10-31 单表查询：根据运费查询订单信息 (3 分)
select orderid,customerid,employeeid from orders where Freight between 10 and 50
R10-32 查询年龄18-20之间的学生信息 (3 分)
select sno as 学号,sname as 姓名,sex as 性别,mno as 专业,(year(‘2020-03-01‘)-year(birdate)) as 年龄,memo as 备注
from stu
where (year(‘2020-03-01‘)-year(birdate)) between 18 and 20
R10-33 查询xscj表中的计算机成绩在80至90之间（包含80和90分）的同学的学号，姓名，计算机三项信息。 (3 分)
select 学号,姓名,计算机 from xscj where 计算机 between 80 and 90;
R10-34 查询图书表中售价介于50元到70元之间的图书的全部信息 (3 分)
select * from 图书 where 售价 between 50 and 70;
R10-35 2-2-(f)查询具有1GB以上的硬盘容量而价格低于2000美元的所有个人计算机的型号、速度以及硬盘容量 (3 分)
select model,speed,hd from pc where price<2000 and hd>1
R10-36 A2-2查找产品表中再次订购量大于等于10且修订量大于订货数量的产品信息 (3 分)
select ProductID,ProductName,SupplierID from products where ReorderLevel>=10 and ReorderLevel>UnitsOnOrder
R10-37 2-1-? 查询在1990年拍摄过电影的所有影星，或者拍摄过电影名中含有"3"的电影的所有影星 (3 分)
select distinct starName from StarsIn where movieYear=1990 or movieTitle like ‘%3%‘
R10-38 2-1-(e) 查询所有的男影星或者住址中含有4的影星 (3 分)
select name from MovieStar where gender=‘M‘ or address like ‘%4%‘
R10-39 spj-查询供应工程 j1 零件 p1 的供应商 (3 分)
select sno
from spj
where pno=‘p1‘ and jno=‘j1‘
R10-40 查询zgda表中所有女教授的信息 (3 分)
select * from zgda where 性别=‘女‘ and 职称=‘教授‘
R10-41 查询图书表中科学出版社出版的价格在50元以上的图书的信息，要求结果中包括书名，作者，出版社和售价4列。 (3 分)
select 书名,作者,出版社,售价 from 图书 where 售价>50 and 出版社=‘科学出版社‘
R10-42 查询图书表中书名为“C语言程序设计”和“VB程序设计”的两本书的全部信息 之一 (3 分)
select * from 图书 where 书名=‘C语言程序设计‘ or 书名=‘VB程序设计‘
R10-43 在读者表查询账号D002和D003两位读者的全部信息 (3 分)
select *
from 读者
where 账号=‘D002‘ or 账号=‘D003‘
R10-44 在读者表中查询余额在500元以下（不包括500元）的女性读者的全部信息 (3 分)
select *
from 读者
where 余额<500 and 性别=‘女‘
R10-45 查询读者表中余额介于500到1000（包括500，不包括1000）之间的读者的全部信息 (3 分)
select *
from 读者
where 余额>=500 and 余额<1000
R10-46 在订单表中查询运费在40元到60元之间的订单的全部信息 (3 分)
select *
from 订单
where 运费>=40 and 运费<60
R10-47 在员工表中查询姓陈的男职工的全部信息 (3 分)
select *
from 员工
where 姓名 like ‘陈%‘ and 性别=‘男‘
R10-48 在员工表中查询陈诚瑞和钟鸣的全部信息 (3 分)
select *
from 员工
where 姓名=‘陈诚瑞‘ or 姓名=‘钟鸣‘
R10-49 在订单表中查询011号员工和121号员工承办的订单信息 (3 分)
select *
from 订单
where 员工编号=‘011‘ or 员工编号=‘121‘
R10-50 A1-2根据所在国家查找订单信息 (3 分)
select OrderID,CustomerID from orders where ShipCountry=‘Germany‘ or ShipCountry=‘Brazil‘ or ShipCountry=‘France‘
R10-51 A1-6在顾客表中找出不是特定城市的顾客信息 (3 分)
select CustomerID,Phone from customers where City<>‘Madrid‘ and City<>‘Torino‘ and City<>‘Paris‘
R10-52 A2-3查询产品表中单价不在范围内的的产品信息 (3 分)
select ProductID,ProductName,CategoryID from products where UnitPrice<15 or UnitPrice>45
R10-53 查询xscj表中的学号，姓名，计算机三项信息，结果按计算机成绩的降序排列。 (3 分)
select 学号,姓名,计算机 from xscj order by 计算机 desc
R10-54 查询xsda表中的学号、姓名、性别三项信息，结果按照女生优先的顺序显示。 (3 分)
select 学号,姓名,性别 from xsda order by 性别 desc
R10-55 查询zgda表中的工号，姓名，性别，职称4项信息，显示结果时首先按照女性在前的顺序，如果性别相同则按照职称的升序排列。 (3 分)
select 工号,姓名,性别,职称 from zgda order by 性别 desc,职称 asc
R10-56 查询图书表中的图书的条形码和书名，要求结果按条形码升序排序 (3 分)
select 条形码,书名 from 图书 order by 条形码 asc
R10-57 查询图书的条形码，书名，出版社和出版日期，要求结果按出版社升序排列，出版社相同的数据按出版日期降序排列 (3 分)
select 条形码, 书名, 出版社,出版日期 from 图书 order by 出版社 asc,出版日期 desc
R10-58 在读者表中查询全部读者信息，要求女性在前男性在后，同为女性读者的按账号升序排列 (3 分)
select *
from 读者
order by 性别 desc,账号 asc
R10-59 在员工表中查询所有男性员工的编号，姓名和入职日期，结果按员工编号升序排列 (3 分)
select 员工编号,姓名,入职日期 from 员工 where 性别=‘男‘ order by 员工编号 asc
R10-60 在顾客表中查询青岛的顾客编号，公司名称和电话，结果按顾客编号升序排列 (3 分)
select 顾客编号,公司名称,电话 from 顾客 where 城市=‘青岛‘ order by 顾客编号 asc
R10-61 查询学生表中大于19岁的女生 (3 分)
select sno as 学号,sname as 姓名,sex as 性别,mno as 专业,(year(‘2020-03-01‘)-year(birdate)) as 年龄,memo as 备注
from stu
where sex=0 and (year(‘2020-03-01‘)-year(birdate))>19
R10-62 查询2018年以后出版的图书的全部信息 (3 分)
select *
from 图书
where year(出版日期)>=2018
R10-63 查询图书表中条形码左边开始三个字符是“TP3”的图书的全部信息 (3 分)
select * from 图书 where left(条形码,3)=‘TP3‘
R10-64 查询姓王的读者的账号、姓名和性别，要求使用left函数 (3 分)
select 账号,姓名,性别
from 读者
where left(姓名,1) like ‘王‘
R10-65 在员工表中查询1990年以后出生的职工的全部信息 (3 分)
select * from 员工 where year(出生日期)>=1990
R10-66 查询学生表中部分信息 (3 分)
select sno,sname,(case when sex=1 then ‘男‘ when sex=0 then ‘女‘ end) as sex from stu;
R10-67 6-2 查询价格超过2500美元的便携式电脑的平均速度 (3 分)
select avg(speed) as avg_speed from laptop where price>2500
R10-68 6-1 查询PC的平均速度 (3 分)
select avg(speed) as avg_speed from pc
R10-69 6-3 查询厂商"A"生产的PC的平均价格 (3 分)
select avg(price) as avg_price from pc,product where maker=‘A‘ and product.model=pc.model
R10-70 A3-3查找产品表中最低的单价 (3 分)
select min(UnitPrice) as minUnitPrice
from products
R10-71 A3-4查询产品表中最大库存量 (3 分)
select max(UnitsInStock) as maxUnitsInStock from products
R10-72 A3-1查询订单表中的平均运费 (3 分)
select avg(Freight) as avgFreight from orders
R10-73 A3-2查询国家为Mexico、Germany的客户数量 (3 分)
select count(*) as custCount from customers where Country=‘Mexico‘ or Country=‘Germany‘
R10-74 A4-5统计职工表中职务的数量 (3 分)
select count(distinct Title) as countTitle
from employees
R10-75 计算xscj表中计算机课程的最高分 (3 分)
select max(计算机) as 计算机最高分 from xscj
R10-76 计算xscj表中计算机课程的最低分 (3 分)
select min(计算机) as 计算机最低分 from xscj
R10-77 计算xscj表中英语课程的平均分 (3 分)
select avg(英语) as 英语平均分 from xscj
R10-78 统计xscj表中计算机课程成绩在90至100之间的人数（包含90和100） (3 分)
select count(*) as 计算机优秀人数
from xscj
where 计算机 between 90 and 100
R10-79 计算xsda表中最高同学的身高 (3 分)
select max(身高) as 最高同学的身高 from xsda
R10-80 计算xsda表中最矮同学的身高 (3 分)
select min(身高) as 最矮同学的身高 from xsda
R10-81 查询图书表中全部图书的最高售价 (3 分)
select max(售价) as 最高售价 from 图书
R10-82 查询图书表中全部图书的最低售价 (3 分)
select min(售价) as 最低售价 from 图书
R10-83 查询图书表中全部图书的平均售价 (3 分)
select avg(售价) as 平均售价 from 图书
R10-84 查询图书表中全部图书的最高售价、最低售价和平均售价 (3 分)
select max(售价) as 最高售价,min(售价) as 最低售价,avg(售价) as 平均售价 from 图书
R10-85 查询图书表中2018年出版的图书的数目 (3 分)
select count(*) as 2018年出版的图书数目 from 图书 where year(出版日期)=‘2018‘
R10-86 在读者表中查询账户余额的最高值和最低值 (3 分)
select max(余额) as 最高余额,min(余额) as 最低余额 from 读者
R10-87 在读者表中查询所有等级为“白银”的读者的余额之和 (3 分)
select sum(余额) as 白银读者余额之和 from 读者 where 等级=‘白银‘
R10-88 在订单表中查询运费的最大值和最小值 (3 分)
select max(运费) as 最高运费,min(运费) as 最低运费 from 订单
R10-89 在顾客表中查询公司城市在“济南”的顾客数目 (3 分)
select count(*) as 济南顾客数 from 顾客 where 城市=‘济南‘
R10-90 在订单表中查询运费的平均值 (3 分)
select avg(运费) as 平均运费 from 订单
R10-91 6-5 查询各种不同速度的PC的平均价格 (3 分)
select speed,avg(price) as avg_price from pc group by speed
R10-92 6-9查询速度超过150MHZ的各种速度的PC的平均价格 (3 分)
select speed,avg(price) as avg_price from pc where speed>150 group by speed
R10-93 A4-1查找订单表中每位顾客的平均运费 (3 分)
select CustomerID,avg(Freight) as avgFreight from orders group by CustomerID
R10-94 A4-2统计顾客表中每个国家的顾客数量 (3 分)
select Country,count(*) as custCount
from customers
group by Country
R10-95 统计xsda表中男女生的人数 (3 分)
select 性别,count(*) as 人数 from xsda group by 性别
R10-96 计算xsda表中男女生的平均身高 (3 分)
select 性别,avg(身高) as 平均身高
from xsda
group by 性别
R10-97 查询图书表中各出版社图书的数目，结果按图书数目降序排序 (3 分)
select 出版社,count(*) as 图书数目 from 图书 group by 出版社 order by 图书数目 desc
R10-98 查询读者表中男女读者各自的人数 (3 分)
select 性别,count(*) as 人数 from 读者 group by 性别
R10-99 在读者表中查询不同等级各自的人数，结果按人数降序排列 (3 分)
select 等级,count(*) as 人数 from 读者 group by 等级 order by count(*) desc
R10-100 在订单表中查询各位员工承办的订单数目 (3 分)
select 员工编号,count(*) as 订单数
from 订单
group by 员工编号
R10-101 在员工表中查询男女职工的平均年龄 (3 分)
select 性别,avg(2020 - year(出生日期)) as 平均年龄
from 员工
group by 性别
-- 2020年的题今年做出bug,只好卡bug了
R10-102 在顾客表中查询各个城市的顾客数目 (3 分)
select 城市,count(1) as 顾客数
from 顾客
group by 城市
R10-103 在员工表中查询每年入职的员工人数 (3 分)
select year(入职日期) as 年份,count(*) as 入职人数 from 员工 group by 年份 order by 年份 asc
R10-104 A4-6查找订单表中特定顾客编号的相关信息 (3 分)
select CustomerID,sum(Freight) as sumFreight from orders where CustomerID like ‘V%‘ group by CustomerID
R10-105 查询平均成绩高于75分的学生 (3 分)
select sno as 学号,avg(grade) as 平均成绩
from sc
group by 学号
having 平均成绩>75
R10-106 A4-4查找产品表中平均订购数大于特定值的产品信息 (3 分)
select ProductID,sum(UnitsOnOrder) as sumUnitsOnOrder from products group by ProductID having sumUnitsOnOrder>15
R10-107 A4-7在订单详细信息表中查找包含产品种类数超过特定值的订单信息 (3 分)
select OrderID,sum(Quantity) as totalQuantity
from orderdetails
group by OrderID
having count(OrderID)>2
R10-108 3-2-(d)查询在两种或两种以上PC机上出现的硬盘容量 (3 分)
select hd from pc group by hd having count(hd)>=2
R10-109 3-1-(a) 查询电影“M3”中的男影星 (3 分)
select name from MovieStar,StarsIn where MovieStar.name=StarsIn.starName and gender=‘M‘ and movieTitle=‘M3‘
R10-110 3-1-(b) 查询st1制片公司的总裁 (3 分)
select MovieExec.name from MovieExec,Studio where MovieExec.certID=Studio.presCertID and Studio.name=‘st1‘
R10-111 3-1-?查询在st1公司于2018年制作的电影中出演的影星 (3 分)
select distinct starName
from StarsIn,Movie
where StarsIn.movieTitle=Movie.title and StarsIn.movieYear=Movie.year and year=2018 and studioName=‘st1‘;
R10-112 2-1-(d) 查询净资产至少200万美元的所有行政长官 (3 分)
select MovieExec.name from MovieExec,Studio where MovieExec.certID=Studio.presCertID and netWorth>200
R10-113 3-2-(a)查询配置了容量至少为1G字节硬盘的便携式电脑的生产厂商及其速度 (3 分)
select maker,speed from product,laptop where product.model=laptop.model and hd>=1 and type=‘便携式电脑‘
R10-114 3-2-(e)查询拥有相同速度和内存的PC机的成对的型号 (3 分)
select pc1.model as model1,pc2.model as model2
from pc pc1,pc pc2
where pc1.speed=pc2.speed and pc1.ram=pc2.ram and pc1.model<pc2.model
R10-115 spj-查询供应工程 j1 的红色零件的供应商 (3 分)
select sno
from spj,p
where spj.pno=p.pno and jno=‘j1‘ and color=‘红‘
R10-116 4-1 查询速度至少为160MHz的PC的制造商 (3 分)
select distinct maker
from product,pc
where product.model=pc.model and speed>=160
R10-117 B1-3查询特定供应商及其供应的产品情况 (3 分)
select products.ProductID,products.ProductName,suppliers.SupplierID,suppliers.CompanyName
from products,suppliers
where suppliers.SupplierID=products.SupplierID and suppliers.Country in (‘Japan‘,‘USA‘)
R10-118 B1-1查找职员信息及其直接上级的相关信息 (3 分)
select A.LastName,A.FirstName,A.ReportsTo EmployeeID,B.Title
from employees A,employees B
where A.ReportsTo=B.EmployeeID
R10-119 B1-2查询供应商及其供应的产品情况 (3 分)
select products.ProductID,products.ProductName,suppliers.SupplierID,suppliers.CompanyName
from products,suppliers
where suppliers.supplierID = products.supplierID
R10-120 B1-8查询特定订单的详细信息 (3 分)
select customers.CustomerID,customers.CompanyName,orderdetails.OrderID,orderdetails.ProductID
from orders,orderdetails,customers
where orders.ShipCity=‘Madrid‘ and orders.OrderID=orderdetails.OrderID and orders.CustomerID=customers.CustomerID
R10-121 通过图书表和借阅表，查询图书的借阅情况，要求结果中包括以下几列：账号，条形码，书名和借书日期 (3 分)
select 账号,图书.条形码,书名,借书日期
from 图书 inner join 借阅 on 图书.条形码=借阅.条形码
R10-122 通过三个表，查询读者借阅图书的信息 (3 分)
select 读者.账号,姓名,图书.条形码,图书.书名,借书日期,还书日期
from 图书,借阅,读者
where 图书.条形码=借阅.条形码 and 借阅.账号=读者.账号
R10-123 通过读者和借阅表，查询读者信息，包括以下字段：账号，姓名，性别，条形码，借书日期和还书日期 (3 分)
SELECT 读者.账号,姓名,性别,条形码,借书日期,还书日期
FROM 读者 inner join 借阅 on 读者.账号=借阅.账号
R10-124 通过订单表和顾客表，查询订单编号，顾客编号，公司 名称和订单日期 (3 分)
select 订单编号,订单.顾客编号,公司名称,订单日期
from 订单 inner join 顾客 on 订单.顾客编号=顾客.顾客编号
R10-125 查询部分专业的学生 (3 分)
select sno as 学号,sname as 姓名,sex as 性别,mname as 专业
from stu,major
where stu.mno=major.mno and mname in (‘计算机工程‘,‘软件工程‘)
order by sno;
R10-126 查询选修‘C语言’课程的学生 (3 分)
select sname as 姓名,grade as 成绩
from stu,sc,cou
where stu.sno=sc.sno and cou.cno=sc.cno and cname=‘C语言‘
order by grade desc
R10-127 查询同专业的学生 (3 分)
select sno as 学号,sname as 姓名
from stu
where sname<>‘张三‘ and mno in (select mno
from stu
where sname=‘张三‘)
R10-128 查询选修某两门课程的学生 (3 分)
select sno as 学号
from sc
where cno=‘C001‘and sno in (
select distinct sno
from sc
where cno=‘C002‘
)
R10-129 查询课程成绩最高二人 (3 分)
select stu.sno,sname,grade
from sc,stu
where sc.sno=stu.sno and sc.cno=‘C002‘
order by grade desc limit 0,2
R10-130 4-3 查询速度低于任何PC的便携式电脑 (3 分)
select model
from laptop
where speed<all (
select speed
from pc
)
R10-131 查询图书表中售价最高的图书的全部信息 (3 分)
select *
from 图书
where 售价 in (
select max(售价)
from 图书
)
R10-132 在员工表中查询入职最晚的员工的编号，姓名和入职日期 (3 分)
select 员工编号,姓名,入职日期
from 员工
where 入职日期=(select max(入职日期) from 员工)
R10-133 4-5 查询具有最低价格的的彩色打印机的制造商 (3 分)
-- 这个测试通过了，可是下面2个不知道为什么通过不了，查询结果都一样。。
select distinct maker
from product,printer
where product.model=printer.model and color=1 and price in (select min(price) from printer where color=1)
-- select pp.maker as maker
-- from (select maker,p2.model,color,price
-- from printer p2 join product p1 on p1.model=p2.model where color=‘0‘) as pp
-- order by price asc limit 1;
-- ----------
-- select maker
-- from product
-- where model=any (
-- select model
-- from printer
-- where price<=all (
-- select price
-- from printer
-- where color=‘1‘
-- )
-- );
R10-134 4-2 查询价格最高的打印机型号 (3 分)
select model
from printer
where price>=all(select max(price) from printer)
R10-135 查询比“网络工程”专业所有学生年龄都小的学生姓名 (3 分)
select sname
from stu
where birdate > (select max(birdate) from stu where mno=(select mno from major where mname=‘网络工程‘))
R10-136 查询软件工程专业中年龄最大的同学姓名 (3 分)
select t.sname
from(select s.sname,s.mno,year(now())-year(birdate) year
from stu s join major m on s.mno = m.mno) t
order by t.year desc limit 1
R10-137 在读者表中查询余额最高的读者的全部信息 (3 分)
select *
from 读者
where 余额>=all (select max(余额) from 读者)
R10-138 3-1-(e)查询比a1更富有的行政长官 (3 分)
select name
from MovieExec
where certID in(select presCertID from Studio) and netWorth>all(
select netWorth
from MovieExec
where name=‘a1‘)
R10-139 3-2-?查询所有出售便携式电脑（而不出售PC机）的生产厂商 (3 分)
select distinct maker
from product,laptop
where product.model=laptop.model and maker not in (
select maker
from product,pc
where product.model=pc.model
);
R10-140 spj-查询同时使用红色的螺母零件和蓝色的螺丝刀零件的工程 (3 分)
select jname
from j
where jno in(
select p1.jno
from (select pname,color,sno,spj.jno,p.pno from p,spj where p.pno=spj.pno) p1,(select pname,color,sno,spj.jno,p.pno from p,spj where p.pno=spj.pno) as p2
where (p1.pname=‘螺母‘ and p1.color=‘红‘) and (p2.pname=‘螺丝刀‘ and p2.color=‘蓝‘) and p1.sno=p2.sno
)
order by jname asc
R10-141 spj-查询比p6零件供应数量都高的零件 (3 分)
select distinct pno
from spj x
where pno not in
(select pno
from spj y
where y.qty<=(
select max(qty)
from spj
where pno=‘p6‘)
)
--注意任何一次
--每次供应的数量都高于p6最大值，那么反面有任何一次供应数量低于p6最大值，就不考虑该零件(not in)
R10-142 5-1 查询销售便携式电脑但不销售PC的厂商 (3 分)
select distinct maker
from product
where type=‘便携式电脑‘ and maker not in(
select maker
from product
where type=‘个人电脑‘
)
R10-143 4-6 查询在具有最小内存容量的所有PC中具有最快处理器的PC制造商 (3 分)
select maker
from product,pc
where product.model=pc.model
and ram in(
select min(ram)
from pc
)
and speed in (
select max(speed)
from product,pc
where product.model=pc.model
and ram in(
select min(ram)
from pc
)
)
R10-144 查询平均成绩以上的课程 (3 分)
select sno as 学号,cname as 课程名,grade as 成绩 from sc x,cou
where grade>(select avg(grade) from sc y where x.sno=y.sno) and x.cno=cou.cno
R10-145 6-10 查询所有生产打印机的厂商生产的PC的硬盘平均容量 (3 分)
select avg(hd) as avg_hd
from pc,product
where pc.model=product.model and maker in (select maker from product where type=‘打印机‘);
R10-146 spj-查找在同一个城市的所有工程项目 (3 分)
select city,jname
from j
where city in (select city from j group by city having count(city)>1)
order by city ASC,jname ASC;
R10-147 5-2 查询至少生产两种不同的计算机(PC或便携式电脑)且机器速度至少为133的厂商 (3 分)
select maker
from (
select maker,model
from product
where model in (
select model
from pc
where speed>=133
)
union
select maker,model
from product
where model in (
select model
from laptop
where speed>=133
)
) as a
group by maker
having count(maker)>=2;
R10-148 5-4 查询至少生产三种不同速度PC的厂商 (3 分)
select maker from(
select product.maker,pc.speed
from product join pc on product.model=pc.model
group by pc.speed,product.maker
)as a
group by maker having count(*)>=3
R10-149 5-5 查询只卖三种不同型号PC的厂商 (3 分)
select maker
from (
select maker,model
from product
where model in (
select model
from pc
)
) as a
group by maker
having count(maker)=3 and maker not in (
select maker
from product
where model in (
select model
from laptop
union
select model
from printer
)
);
-- 分组，pc型号数=3，只买pc即型号不在便携式电脑和打印机中
R10-150 查询选修课程超过2门且成绩都在80分以上的学生 (3 分)
select sname 姓名,mno 专业,sum(credit) 总学分 from stu join sc on stu.sno=sc.sno
join cou on sc.cno=cou.cno
where grade is not null
group by stu.sno
having count(sname)>=2 and min(grade)>=80
R10-151 查询成绩最高的前三名同学 (3 分)
select sname as 姓名,grade as 成绩
from sc,stu
where sc.cno in(select cno from cou where cname=‘C语言‘)
and sc.sno=stu.sno
order by grade desc limit 0,3
R10-152 B1-4统计各个供应商及其供应情况 (3 分)
select Country,sum(UnitsInStock) as sumUnitsInStock,avg(UnitPrice) as avgUnitPrice
from suppliers join products on suppliers.SupplierID=products.SupplierID
group by Country;
R10-153 B1-6统计客户的订单信息 (3 分)
select CompanyName,count(*) as countOrder,avg(Freight) as avgFreight
from orders,customers
where customers.CustomerID=orders.CustomerID
group by CompanyName
R10-154 B1-7查找每位领导的直接下属数量 (3 分)
select A.EmployeeID,count(B.ReportsTo) as countSub
from employees A,employees B
where B.ReportsTo=A.EmployeeID
group by A.EmployeeID;
R10-155 6-6 查询各厂商生产的便携式电脑的显示器平均尺寸 (3 分)
select maker,avg(screen) as avg_screen
from laptop left join product on laptop.model=product.model
group by maker
R10-156 6-8 查询各厂商生产的PC的最高价格 (3 分)
select maker,max(price) as max_price
from pc left join product on pc.model=product.model
group by maker
R10-157 查询平均分高于80分的学生 (3 分)
select sname
from stu
where sno in(
select sno from sc group by sno having avg(grade)>80
)
R10-158 查询平均分高于60分的课程 (3 分)
select cno as 课程号,cname as 课程名
from cou
where cno in(
select cno from sc group by cno having avg(grade)>60
)
R10-159 A4-3在订单表中查找特定国家且平均运费不小于10的信息 (3 分)
select CustomerID,avg(Freight) as avgFreight
from orders
where ShipCountry in (‘Belgium‘,‘Switzerland‘)
group by CustomerID
having avg(Freight)>=10;
R10-160 6-7 查询生产三种不同型号的PC的厂商 (3 分)
select maker
from product join pc on product.model=pc.model
group by maker having count(product.model)=3
R10-161 3-2-(b)查询由生产厂商B生产的所有产品的型号（model) 和价格(price) (3 分)
select model,price
from (
select model,price
from pc
union
select model,price
from laptop
union
select model,price
from printer
)as a
where model in (
select model
from product
where maker=‘B‘
);
R10-162 查询学生选修的课程 (3 分)
select sc.cno as 课程号,cname as 课程 from cou,sc where sno=‘S001‘ and cou.cno=sc.cno
union
select sc.cno as 课程号,cname as 课程 from cou,sc where sno=‘S003‘ and cou.cno=sc.cno
R10-163 6-4 查询厂商"D"生产的PC和便携式电脑的平均价格 (3 分)
select avg(price) as avg_price
from (
select model,price
from pc
union
select model,price
from laptop
)as a
where model in (
select model
from product
where maker=‘D‘
);
R10-164 查找课程选修的情况 (3 分)
select cou.cno 课程号,cname 课程名,count(sno) as 选课人数,ifnull(max(grade),0) 最高成绩,ifnull(min(grade),0) 最低成绩,ifnull(avg(grade),0) 平均成绩
from cou left join sc on sc.cno = cou.cno
group by cou.cno,cou.cname;
-- 另一种方法(最开始是用并集写的，做下一道题时发现了简单的方法回来又写了上面的。。)
select sc.cno 课程号,cname 课程名,count(sno) as 选课人数,max(grade) 最高成绩,min(grade) 最低成绩,avg(grade) 平均成绩
from sc join cou on sc.cno = cou.cno
group by cou.cno,cou.cname
union
select cno 课程号,cname 课程名,0 as 选课人数,0 as 最高成绩,0 as 最低成绩,0 as 平均成绩
from cou
where cno not in (select cno from sc)
group by cno;
R10-165 查询各专业的学生人数 (3 分)
select major.mno as 专业号,mname as 专业,count(sno) as 人数
from major left join stu on major.mno=stu.mno
group by major.mno
R10-166 查询各专业学生的平均成绩 (3 分)
select mname 专业,ifnull(avg(grade),0)平均成绩
from major left outer join(select mno,grade from stu,sc where stu.sno=sc.sno)as A on major.mno=A.mno
group by major.mno
order by major.mno;
R10-167 spj-查询没有使用天津供应商生产的红色零件的工程 (6 分)
select jno
from j
where jno not in (select jno from s,p,spj where s.sno=spj.sno and p.pno=spj.pno and p.color=‘红‘ and s.city=‘天津‘)
R10-168 spj-查询至少使用s1供应商所供应的全部零件的工程 (6 分)
select jno
from (select pno,jno from spj where sno=‘s1‘) as a
group by jno
having count(jno)=(select count(distinct pno) from spj where sno=‘s1‘)
-- 因为sno,pno,jno3个都是主键约束，所以求s1供应商中count(pno)(零件种类)=count(jno)(每个工程jno使用的零件种类和)即可
R10-169 spj-统计各供应商的零件供应量 (6 分)
select a.sno as 供应商号,sname as 供应商,sum(qty) as 供应总量
from spj as a,s
where a.sno=s.sno and not exists(
select distinct sno
from spj as b
where qty<100 and b.sno=a.sno
)
group by a.sno,sname;
R10-170 查询选修了“C语言”课程，但是没有选修“数据结构”课程的学生 (6 分)
select sname
from stu
where sno in(
select sno
from sc,cou
where sc.cno=cou.cno and cname=‘C语言‘
)and sno not in(
select sno
from sc,cou
where sc.cno=cou.cno and cname=‘数据结构‘
)
R10-171 查询计算机工程专业学生选修但软件工程专业学生没有选修的课程。 (6 分)
select distinct cname
from cou
where cno not in (
select cno
from sc,stu,major
where sc.sno=stu.sno and stu.mno=major.mno and mname=‘软件工程‘
)and cno in(
select cno
from sc,stu,major
where sc.sno=stu.sno and stu.mno=major.mno and mname=‘计算机工程‘
);
R10-172 查询选修人数超过2人且成绩都在60分以上的课程 (6 分)
select sc.cno 课程号,cname 课程名,max(grade) 最高成绩,min(grade) 最低成绩,avg(grade) 平均成绩
from sc join cou on sc.cno = cou.cno
group by sc.cno,cou.cname having count(sc.cno) > 2 and min(grade) >= 60 and count(*) = count(grade);
--最后count(*) = count(grade)去掉选课中grade为null的记录
R10-173 查询没有选修’C语言’课程的学生 (6 分)
select sno as 学号,sname as 姓名 from stu
where sno not in
(select distinct sno from sc where
not exists(select * from cou where cou.cno = sc.cno and cname != ‘C语言‘) )
order by sno asc;
--in是把外表和内表作hash连接，而exists是对外表作loop循环，每次loop循环再对内表进行查询
R10-174 查询S001学生选修而S003学生未选修的课程 (6 分)
select cno as 课程号
from sc
where cno not in(select s1.cno as 课程号
from sc as s1 inner join sc as s2 on s1.cno = s2.cno
where(s1.sno = ‘S001‘ and s2.sno = ‘S003‘))
and sno = ‘s001‘;
--自连接 查询课程号不在S001和S002一起选的课中，并且学号为S001的，这样就可以排除掉他俩一起选的课
R10-175 查询选修张老师讲授所有课程的学生 (6 分)
select sname
from stu
where not exists(
select *
from cou
where not exists(
select *
from sc
where sc.sno=stu.sno and sc.cno=cou.cno
)
and cou.teacher = ‘张老师‘)
R10-176 spj-查询各工程项目使用所提供零件最多的供应商 (8 分)
select jno,sno,qty
from (select sno,jno,sum(qty) qty from spj group by jno,sno)as x
where x.qty=
(select max(qty)
from(select jno,sum(qty) qty from spj group by jno,sno)as y
where x.jno=y.jno
)
order by jno;
-- 先统计各工程项目(jno)的各供应商(sno)提供的零件数量和(sum(qty))，记作派生表x
-- 再从表x中选出每个工程(group by jno)使用最多的零件
-- 最后再嵌套一层查询，将使用最多零件表记作y，使用相关子查询x.jno=y.jno即可
R10-177 spj-显示每个工程项目使用零件的供应列表 (8 分)
select a.jno,jname,sname,pname,qty
from (
select spj.jno,jname
from j,spj
where j.jno=spj.jno and qty<100
) a inner join (
select spj.jno,sname
from s,spj
where s.sno=spj.sno and qty<100
) b on b.jno=a.jno
inner join (
select spj.jno,pname,qty
from p,spj
where p.pno=spj.pno and qty<100
) c on c.jno=a.jno
union
select jno,jname,NULL as sname,NULL as pname,0 as qty
from j
where jno not in (
select distinct jno
from spj
)
order by jno asc;
R10-178 spj-显示供应商供应零件的汇总列表 (8 分)
select
coalesce(sno,‘所有供应商‘)‘供应商‘,
coalesce (pno,‘所有零件‘) ‘零件‘,
sum(qty) ‘供应量‘
from spj
group by sno,pno
with rollup;
-- 使用WITH ROLLUP：在group分组字段的基础上进行统计数据。
-- 函数COALESCE(expression_1,…,expression_n)依次参考各参数表达式，遇到非null值即停止并返回该值。如果所有的表达式都是空值，最终将返回一个空值。
R10-179 5-3 查询生产最高速度的计算机(PC或便携式电脑)厂商 (8 分)
select maker
from (
select maker,speed
from pc,product
where pc.model=product.model
union
select maker,speed
from laptop,product
where laptop.model=product.model
)a
where a.speed in(
select max(speed)
from (
select speed
from pc,product
where pc.model=product.model
union
select speed
from laptop,product
where laptop.model=product.model
)b
)
order by maker asc;
R10-180 B2-1查找订单数最多的员工信息 (8 分)
select LastName,FirstName,Title
from employees
where EmployeeID in (
select t.EmployeeID
from ( select EmployeeID,count(*) as size
from orders
group by EmployeeID
order by size desc
limit 1
)t
)
R10-181 4-4 查询具有最高价格的机器的型号，机器包括PC、Laptop、Printer (8 分)
select a.model model
from (
select price,model
from pc
union
select price,model
from laptop
union
select price,model
from printer
)as a
where a.price =(
select max(b.price)
from (
select price,model
from pc
union
select price,model
from laptop
union
select price,model
from printer
)b
);
-- 先将三个表的型号做连接，再将三个表的价格做连接，再利用max函数取出价格最大值，与型号做连接，即找出了价格最高的机器的型号
R10-182 统计每个专业的男生与女生人数 (8 分)
select stu.mno as 专业号,mname as 专业名,case sex when ‘0‘ then ‘女‘ when ‘1‘ then ‘男‘ end as 性别,count(sno) as 人数
from major,stu
where major.mno=stu.mno
group by stu.mno,major.mname,sex
R10-183 C1-1新增一个区域 (3 分)
insert into region values(5,‘Center‘);
R10-184 C1-2新增订单统计信息 (3 分)
insert into results(CustomerID,OrderCount)
select CustomerID,count(*) as OrderCount
from orders
group by CustomerID;
R10-185 批量插入学生记录 (3 分)
insert into softstu(sno,sname)
select sno,sname
from stu
where mno=‘02‘;
R10-186 添加学生成绩记录 (3 分)
insert into sc values(‘S012‘,‘C001‘,90);
insert into sc values(‘S012‘,‘C002‘,null);
R10-187 添加一条学生记录 (3 分)
insert into stu values(‘S012‘,‘周强‘,1,null,null,null);
R10-188 7-1 将下述事实存入数据库:生产厂商C制造的型号为1100的PC机，速度240，内存32，硬盘2.5G，售价2499美元。 (3 分)
INSERT INTO product VALUES (‘C‘,‘1100‘,‘pc‘) ;
INSERT INTO pc VALUES (‘1100‘, 240,32,2.5,null,2499 ) ;
R10-189 sql-insert-sample (3 分)
insert into Student values(99,‘test‘);
R10-190 在图书表中插入一条记录，内容是：条形码“TP211.3”，书名“狼图腾”，作者“姜戎”，售价 44.5元 (3 分)
insert into 图书(条形码,书名,作者,售价) values("TP211.3","狼图腾","姜戎",44.5);
R10-191 已有一个名为“读者”的表，使用INSERT命令向其中插入一条记录 (3 分)
insert into 读者(账号,姓名,性别,等级) values(‘D005‘,‘张兴‘,‘男‘,‘青铜‘);
R10-192 向借阅表中插入一条记录 (3 分)
insert into 借阅(账号,条形码) values(‘D004‘,‘TP204.2‘);
R10-193 向订单表中插入一条新记录 (3 分)
insert into 订单(订单编号,顾客编号,员工编号) values(‘10331‘,‘J101‘,‘023‘)
R10-194 向顾客表中插入一条新记录 (3 分)
insert into 顾客(顾客编号,公司名称,城市) values(‘Z001‘,‘大胜教育‘,‘淄博‘);
R10-195 删除选修C语言课程的女生成绩记录 (3 分)
delete from sc where sno in (select sno from stu where sex=‘0‘)
and cno in (select cno from cou where cname=‘C语言‘);
R10-196 删除学生所有信息 (3 分)
delete from sc where sno in (select sno from stu where sname=‘周强‘);
delete from stu where sname=‘周强‘;
R10-197 删除成绩为空的学生选课记录 (3 分)
delete from sc where grade is null;
R10-198 C3-1删除特定城市的顾客信息 (3 分)
delete from customers where City=‘London‘;
R10-199 C3-2删除没有下过订单的顾客信息 (3 分)
delete from customers where CustomerID not in (select CustomerID from orders);
R10-200 sql-delete-sample (3 分)
delete from Student where id>50;
R10-201 将图书表中条形码为TP204.2的图书信息删除 (3 分)
delete from 图书 where 条形码=‘TP204.2‘;
R10-202 将订单表中2015年以前的订单数据删除 (3 分)
delete from 订单 where year(订单日期)<=2015;
R10-203 将顾客表中顾客编号最后两位是“10”和“11”的记录删除 (3 分)
delete from 顾客 where right(顾客编号,2)=‘10‘ or right(顾客编号,2)=‘11‘;
R10-204 修改高数不及格的学生成绩 (3 分)
update sc
set grade=60
where grade<60 and cno in (select cno from cou where cname=‘高等数学‘)
R10-205 修改女生成绩 (3 分)
update sc
set grade=grade+grade*0.05
where grade<75 and sno in (select sno from stu where sex=0);
R10-206 计算并填写学生获得的总学分 (3 分)
UPDATE stu,
(select sno,sum(credit) as SUM
from(
select stu.sno as sno,case when sc.grade>=60 then credit else NULL end as credit
from stu left outer join sc on stu.sno=sc.sno left outer join cou on sc.cno=cou.cno
group by stu.sno,credit,grade) a
group by sno) B
SET stu.totalcredit = B.SUM
where stu.sno = B.sno;
--https://blog.csdn.net/qq_45552117/article/details/110086939
R10-207 C2-1修改订单运费 (3 分)
update orders
set Freight=Freight*1.5
R10-208 C2-2修改特定职工的订单运费 (3 分)
update orders
set Freight=Freight*0.95
where EmployeeID=3 or EmployeeID=4
R10-209 C2-3根据运费调整订单单价 (3 分)
update orderdetails
set UnitPrice=UnitPrice*1.15
where OrderID in(
select OrderID
from orders
where Freight>30
)
R10-210 C2-4修改订货数量最少的产品单价 (3 分)
update orderdetails
set unitprice = unitprice - 1
where productid in (
select t.productid
from (
select productid
from orderdetails
order by quantity asc
limit 1
) t
);
R10-211 sql-update-sample (3 分)
update Student
set name=‘test‘
where id>50;
R10-212 将图书表中科学出版社出版的图书价格上涨5% (3 分)
update 图书
set 售价=售价*1.05
where 出版社=‘科学出版社‘;
R10-213 将编号为“10330”的订单的运费下调10% (3 分)
update 订单 set 运费=运费*0.9 where 订单编号="10330"
R10-214 将员工表中编号为133的员工的性别修改为“男” (3 分)
update 员工
set 性别=‘男‘
where 员工编号=‘133‘
R10-215 检索部分教师并插入新表 (5 分)
insert into faculty
select distinct teacher
from cou
where teacher not in (
select teacher
from (select cno ,avg(grade) as avg_grade from sc group by sc.cno) a inner join cou on cou.cno=a.cno
where avg_grade<=70
)
and teacher in (
select teacher
from sc inner join cou on cou.cno=sc.cno);
-- https://blog.csdn.net/zhangjielong_/article/details/105042831
R10-216 插入学生总学分表 (5 分)
insert into totalcredit
select sno,sum(credit)
from(
select stu.sno sno,case when sc.grade>=60 then credit else 0 end credit
from stu left outer join sc on stu.sno=sc.sno left outer join cou on sc.cno=cou.cno
group by stu.sno,credit,grade) a
group by sno;
-- https://blog.csdn.net/zhangjielong_/article/details/105043906
R10-217 添加成绩等级rank字段 (5 分)
update sc set rank=‘E‘ where grade<60;
update sc set rank=‘D‘ where grade between 60 and 69;
update sc set rank=‘C‘ where grade between 70 and 79;
update sc set rank=‘B‘ where grade between 80 and 89;
update sc set rank=‘A‘ where grade>90;
R10-218 修改学生选课成绩 (5 分)
update sc
set grade=grade*1.04
where cno=‘C001‘ and grade>=70;
update sc
set grade=grade*1.05
where cno=‘C001‘ and grade<70;