PTA沈師資料庫原理——DB（10）

題目和答案是用python寫的爬蟲獲取的，原始碼在這里：
https://blog.csdn.net/a2272062968/article/details/115376592

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;

轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/271441.html

標籤：其他

上一篇：sql server 2008 的資料型別大全

下一篇：MySQL自學（更新中）

PTA沈師資料庫原理——DB（10）_SQL實驗題