牛客SQL刷題第三趴——SQL必知必會

 

01檢索資料

SQL60 從 Customers 表中檢索所有的 ID

撰寫 SQL 陳述句，從 Customers 表中檢索所有的cust_id

select * from Customers;

SQL61 檢索并列出已訂購產品的清單

【問題】撰寫SQL 陳述句，檢索并列出所有已訂購商品（prod_id）的去重后的清單，

select DISTINCT(prod_id) from OrderItems;

SQL62 檢索所有列

【問題】需要撰寫 SQL陳述句，檢索所有列，

select * from Customers;

02 排序檢索資料

SQL63 檢索顧客名稱并且排序

【問題】從 Customers 中檢索所有的顧客名稱（cust_name），并按從 Z 到 A 的順序顯示結果，

select cust_name from Customers
order by cust_name desc;

SQL64 對顧客ID和日期排序

【問題】撰寫 SQL 陳述句，從 Orders 表中檢索顧客 ID（cust_id）和訂單號（order_num），并先按顧客 ID 對結果進行排序，再按訂單日期倒序排列，

select cust_id,order_num from Orders
order by cust_id,order_date desc;

SQL65 按照數量和價格排序

【問題】撰寫 SQL 陳述句，顯示 OrderItems 表中的數量（quantity）和價格（item_price），并按數量由多到少、價格由高到低排序，

select * from OrderItems
order by quantity desc,item_price desc;

SQL66 檢查SQL陳述句

【問題】下面的 SQL 陳述句有問題嗎？嘗試將它改正確，使之能夠正確運行，并且回傳結果根據vend_name逆序排列

SELECT vend_name, 
FROM Vendors 
ORDER vend_name DESC;

select vend_name
from Vendors 
order by vend_name DESC;

03 過濾資料

SQL67 回傳固定價格的產品

【問題】從 Products 表中檢索產品 ID（prod_id）和產品名稱（prod_name），只回傳價格為 9.49 美元的產品，

select prod_id,prod_name
from Products
where prod_price=9.49;

SQL68 回傳更高價格的產品

【問題】撰寫 SQL 陳述句，從 Products 表中檢索產品 ID（prod_id）和產品名稱（prod_name），只回傳價格為 9 美元或更高的產品，

select prod_id,prod_name
from Products
where prod_price>=9;

SQL69 回傳產品并且按照價格排序

【問題】撰寫 SQL 陳述句，回傳 Products 表中所有價格在 3 美元到 6 美元之間的產品的名稱（prod_name）和價格（prod_price），然后按價格對結果進行排序

select prod_name,prod_price
from Products
where prod_price>=3 and prod_price<=6
order by prod_price;

SQL70 回傳更多的產品

【問題】從 OrderItems 表中檢索出所有不同且不重復的訂單號（order_num），其中每個訂單都要包含 100 個或更多的產品，

select DISTINCT(order_num)
from OrderItems
where quantity >100;

04 高級資料過濾

SQL71 檢索供應商名稱

【問題】撰寫 SQL 陳述句，從 Vendors 表中檢索供應商名稱（vend_name），僅回傳加利福尼亞州的供應商（這需要按國家[USA]和州[CA]進行過濾，沒準其他國家也存在一個CA）

select vend_name from Vendors
where vend_country='USA' and vend_state='CA';

 

SQL72 檢索并列出已訂購產品的清單

【問題】撰寫SQL 陳述句，查找所有訂購了數量至少100 個的 BR01、BR02 或BR03 的訂單，你需要回傳 OrderItems 表的訂單號（order_num）、產品 ID（prod_id）和數量（quantity），并按產品 ID 和數量進行過濾，

select order_num,prod_id,quantity
from OrderItems
where quantity>=100 and prod_id in('BR01','BR02','BR03')
order by order_num,prod_id;

SQL73 回傳所有價格在 3美元到 6美元之間的產品的名稱和價格

【問題】撰寫 SQL 陳述句，回傳所有價格在 3美元到 6美元之間的產品的名稱（prod_name）和價格（prod_price），使用 AND運算子，然后按價格對結果進行升序排序

select prod_name,prod_price
from Products
where prod_price>=3 and prod_price<=6
order by prod_price;

SQL74 糾錯2

【問題】修改正確下面sql，使之正確回傳

SELECT vend_name 
FROM Vendors 
ORDER BY vend_name 
WHERE vend_country = 'USA' AND vend_state = 'CA';

SELECT vend_name 
FROM Vendors 
WHERE vend_country = 'USA' AND vend_state = 'CA'
ORDER BY vend_name ;

 05 用通配符進行適配

SQL75 檢索產品名稱和描述（一）

【問題】撰寫 SQL 陳述句，從 Products 表中檢索產品名稱（prod_name）和描述（prod_desc），僅回傳描述中包含 toy 一詞的產品名稱

select prod_name,prod_desc
from Products
where prod_desc LIKE'%toy%';

SQL76 檢索產品名稱和描述（二）

【問題】撰寫 SQL 陳述句，從 Products 表中檢索產品名稱（prod_name）和描述（prod_desc），僅回傳描述中未出現 toy 一詞的產品，最后按”產品名稱“對結果進行排序，

select prod_name,prod_desc from Products where prod_desc NOT LIKE'%toy%';

SQL77 檢索產品名稱和描述（三）

 【問題】撰寫 SQL 陳述句，從 Products 表中檢索產品名稱（prod_name）和描述（prod_desc），僅回傳描述中同時出現 toy 和 carrots 的產品，有好幾種方法可以執行此操作，但對于這個挑戰題，請使用 AND 和兩個 LIKE 比較，

select prod_name,prod_desc
from Products
where prod_desc LIKE'%toy%' and prod_desc LIKE'%carrots%';

SQL78 檢索產品名稱和描述（四）

【問題】撰寫 SQL 陳述句，從 Products 表中檢索產品名稱（prod_name）和描述（prod_desc），僅回傳在描述中以先后順序同時出現 toy 和 carrots 的產品，提示：只需要用帶有三個 % 符號的 LIKE 即可，

select prod_name,prod_desc
from Products
where prod_desc LIKE '%toy%carrots%';

06 創建計算欄位

SQL79 別名

【描述】別名的常見用法是在檢索出的結果中重命名表的列欄位（為了符合特定的報表要求或客戶需求），有表Vendors代表供應商資訊，vend_id供應商id、vend_name供應商名稱、vend_address供應商地址、vend_city供應商城市，

【問題】撰寫 SQL 陳述句，從 Vendors 表中檢索vend_id、vend_name、vend_address 和 vend_city，將 vend_name重命名為 vname，將 vend_city 重命名為 vcity，將 vend_address重命名為 vaddress，按供應商名稱對結果進行升序排序，

select vend_id,
vend_name as vname,
vend_address as vaddress,
vend_city as vcity
from Vendors
order by vname;

SQL80 打折

【描述】我們的示例商店正在進行打折促銷，所有產品均降價 10%，Products表包含prod_id產品id、prod_price產品價格 【問題】撰寫 SQL陳述句，從 Products 表中回傳 prod_id、prod_price 和 sale_price，sale_price 是一個包含促銷價格的計算欄位，提示：可以乘以 0.9，得到原價的 90%（即 10%的折扣）

select prod_id,prod_price,prod_price*0.9 as sale_price
from Products;

07 使用函式處理資料

SQL81 顧客登錄名

【問題】撰寫 SQL 陳述句，回傳顧客 ID（cust_id）、顧客名稱（cust_name）和登錄名（user_login），其中登錄名全部為大寫字母，并由顧客聯系人的前兩個字符（cust_contact）和其所在城市的前三個字符（cust_city）組成，提示：需要使用函式、拼接和別名，

select cust_id,cust_name,
upper(concat(left(cust_contact,2),left(cust_city,3))) as user_login
from Customers;

SQL82 回傳 2020 年 1 月的所有訂單的訂單號和訂單日期

【問題】撰寫 SQL 陳述句，回傳 2020 年 1 月的所有訂單的訂單號（order_num）和訂單日期（order_date），并按訂單日期升序排序

select * from Orders
where order_date>='2020-01-01 00:00:00' and order_date<='2020-01-31 23:59:59'
order by order_date;

08 匯總資料

SQL83 確定已售出產品的總數

【問題】撰寫 SQL 陳述句，確定已售出產品的總數，

select sum(quantity) as items_ordered
from OrderItems;

SQL84 確定已售出產品項 BR01 的總數

【問題】修改創建的陳述句，確定已售出產品項（prod_id）為"BR01"的總數，

select sum(quantity) as items_ordered
from OrderItems
where prod_id='BR01';

SQL85 確定 Products 表中價格不超過 10 美元的最貴產品的價格

【問題】撰寫 SQL 陳述句，確定 Products 表中價格不超過 10 美元的最貴產品的價格（prod_price），將計算所得的欄位命名為 max_price，

select max(prod_price) as max_price
from Products
where prod_price<=10;

09 分組資料

SQL86 回傳每個訂單號各有多少行數

【問題】撰寫 SQL 陳述句，回傳每個訂單號（order_num）各有多少行數（order_lines），并按 order_lines對結果進行升序排序，

select order_num,count(order_num) as order_lines
from OrderItems
group by order_num
order by order_lines;

SQL87 每個供應商成本最低的產品

【問題】撰寫 SQL 陳述句，回傳名為 cheapest_item 的欄位，該欄位包含每個供應商成本最低的產品（使用 Products 表中的 prod_price），然后從最低成本到最高成本對結果進行升序排序，

select vend_id,min(prod_price) as cheapest_item
from Products
group by vend_id
order by cheapest_item;

SQL88 回傳訂單數量總和不小于100的所有訂單的訂單號

【描述】OrderItems代表訂單商品表，包括：訂單號order_num和訂單數量quantity， 【問題】請撰寫 SQL 陳述句，回傳訂單數量總和不小于100的所有訂單號，最后結果按照訂單號升序排序，

select order_num
from OrderItems
where quantity>=100
order by order_num;

SQL89 計算總和

【描述】OrderItems表代表訂單資訊，包括欄位：訂單號order_num和item_price商品售出價格、quantity商品數量，

【問題】撰寫 SQL 陳述句，根據訂單號聚合，回傳訂單總價不小于1000 的所有訂單號，最后的結果按訂單號進行升序排序， 提示：總價 = item_price 乘以 quantity

select order_num,sum(item_price*quantity) as total_price
from OrderItems
group by order_num
having total_price>=1000
order by order_num;

解題思路：1??聚合函式+having篩選條件，2??group by order_num，按照order_num分組，

 

SQL90 糾錯3

【描述】OrderItems表含有order_num訂單號 【問題】將下面代碼修改正確后執行

SELECT order_num, COUNT(*) AS items 
FROM OrderItems 
GROUP BY items 
HAVING COUNT(*) >= 3 
ORDER BY items, order_num;

SELECT order_num, COUNT(*) AS items 
FROM OrderItems 
GROUP BY order_num
HAVING COUNT(*) >= 3 
ORDER BY order_num;

10 使用子查詢

SQL91 回傳購買價格為 10 美元或以上產品的顧客串列

【描述】OrderItems表示訂單商品表，含有欄位訂單號：order_num、訂單價格：item_price；Orders表代表訂單資訊表，含有顧客id：cust_id和訂單號：order_num

【問題】使用子查詢，回傳購買價格為 10 美元或以上產品的顧客串列，結果無需排序，
注意：你需要使用 OrderItems 表查找匹配的訂單號（order_num），然后使用Order 表檢索這些匹配訂單的顧客 ID（cust_id）， 

select cust_id from Orders
where order_num in
(select order_num from OrderItems where item_price>=10);

SQL92 確定哪些訂單購買了 prod_id 為 BR01 的產品（一）

【描述】表OrderItems代表訂單商品資訊表，prod_id為產品id；Orders表代表訂單表有cust_id代表顧客id和訂單日期order_date

【問題】撰寫 SQL 陳述句，使用子查詢來確定哪些訂單（在 OrderItems 中）購買了 prod_id 為 "BR01" 的產品，然后從 Orders 表中回傳每個產品對應的顧客 ID（cust_id）和訂單日期（order_date），按訂購日期對結果進行升序排序，  

select cust_id,order_date from Orders
where order_num in(select order_num from OrderItems where prod_id='BR01');

SQL93 回傳購買 prod_id 為 BR01 的產品的所有顧客的電子郵件

【描述】你想知道訂購 BR01 產品的日期，有表OrderItems代表訂單商品資訊表，prod_id為產品id；Orders表代表訂單表有cust_id代表顧客id和訂單日期order_date；Customers表含有cust_email 顧客郵件和cust_id顧客id OrderItems表 【問題】回傳購買 prod_id 為BR01 的產品的所有顧客的電子郵件（Customers 表中的 cust_email），結果無需排序， 提示：這涉及 SELECT 陳述句，最內層的從 OrderItems 表回傳 order_num，中間的從 Customers 表回傳 cust_id，

select cust_email
from Customers
where cust_id in (select cust_id from Orders where order_num in
                  (select order_num from OrderItems where prod_id='BR01'));

 SQL94 回傳每個顧客不同訂單的總金額

【描述】我們需要一個顧客 ID 串列，其中包含他們已訂購的總金額， OrderItems表代表訂單資訊，OrderItems表有訂單號：order_num和商品售出價格：item_price、商品數量：quantity，   撰寫 SQL陳述句，回傳顧客 ID（Orders 表中的 cust_id），并使用子查詢回傳total_ordered 以便回傳每個顧客的訂單總數，將結果按金額從大到小排序， 提示：你之前已經使用 SUM()計算訂單總數，

select cust_id,t1.total_ordered
from (select order_num,cust_id from Orders) t 
join(select order_num,sum(item_price*quantity) as total_ordered from OrderItems group by order_num) t1
     on t.order_num=t1.order_num
     order by total_ordered desc;

SQL95 從 Products 表中檢索所有的產品名稱以及對應的銷售總數

【描述】Products 表中檢索所有的產品名稱：prod_name、產品id：prod_id

撰寫 SQL 陳述句，從 Products 表中檢索所有的產品名稱（prod_name），以及名為 quant_sold 的計算列，其中包含所售產品的總數（在 OrderItems 表上使用子查詢和 SUM(quantity)檢索），  

select prod_name,t1.quant_sold
from (select prod_id,prod_name from Products) t
join
(select prod_id,sum(quantity) as quant_sold from OrderItems group by prod_id) t1
on t.prod_id=t1.prod_id;

11 聯結表

 SQL96 回傳顧客名稱和相關訂單號

【描述】Customers 表有欄位顧客名稱cust_name、顧客id cust_id，Orders訂單資訊表，含有欄位order_num訂單號、cust_id顧客id，

【問題】撰寫 SQL 陳述句，回傳 Customers 表中的顧客名稱（cust_name）和Orders 表中的相關訂單號（order_num），并按顧客名稱再按訂單號對結果進行升序排序，你可以嘗試用兩個不同的寫法，一個使用簡單的等聯結語法，另外一個使用 INNER JOIN，  

select c.cust_name,o.order_num
from Customers c
join Orders o
on c.cust_id=o.cust_id
order by c.cust_name,o.order_num;

SQL97 回傳顧客名稱和相關訂單號以及每個訂單的總價

【描述】Customers 表有欄位顧客名稱cust_name、顧客id cust_id，Orders訂單資訊表，含有欄位order_num訂單號、cust_id顧客id，OrderItems表有欄位，商品訂單號：order_num、商品數量：quantity、商品價格：item_price

【問題】除了回傳顧客名稱和訂單號，回傳 Customers 表中的顧客名稱（cust_name）和Orders 表中的相關訂單號（order_num），添加第三列 OrderTotal，其中包含每個訂單的總價，并按顧客名稱再按訂單號對結果進行升序排序，

select c.cust_name,o1.order_num,(o2.quantity*o2.item_price) as OrderTotal
from Customers c 
join Orders o1
on c.cust_id=o1.cust_id
join OrderItems o2
on o1.order_num=o2.order_num
order by c.cust_name,o1.order_num;

SQL98 確定哪些訂單購買了 prod_id 為 BR01 的產品（二）

【描述】表OrderItems代表訂單商品資訊表，prod_id為產品id；Orders表代表訂單表有cust_id代表顧客id和訂單日期order_date

撰寫 SQL 陳述句，使用子查詢來確定哪些訂單（在 OrderItems 中）購買了 prod_id 為 "BR01" 的產品，然后從 Orders 表中回傳每個產品對應的顧客 ID（cust_id）和訂單日期（order_date），按訂購日期對結果進行升序排序， 提示：這一次使用聯結和簡單的等聯結語法，  

select cust_id,order_date
from Orders
where order_num in(select order_num from OrderItems where prod_id='BR01')
order by order_date;

SQL99 回傳購買 prod_id 為 BR01 的產品的所有顧客的電子郵件（二）

【描述】OrderItems代表訂單商品資訊表，prod_id為產品id；Orders表代表訂單表有cust_id代表顧客id和訂單日期order_date；Customers表含有cust_email 顧客郵件和cust_id顧客id

【問題】回傳購買 prod_id 為BR01 的產品的所有顧客的電子郵件（Customers 表中的 cust_email），結果無需排序， 提示：涉及到 SELECT 陳述句，最內層的從 OrderItems 表回傳 order_num，中間的從 Customers 表回傳 cust_id，但是必須使用 INNER JOIN 語法，

select c.cust_email
from Customers c
join Orders o1
on c.cust_id=o1.cust_id
join OrderItems o2
on o1.order_num=o2.order_num
where prod_id='BR01';

SQL100 確定最佳顧客的另一種方式（二）

【描述】OrderItems表代表訂單資訊，確定最佳顧客的另一種方式是看他們花了多少錢，OrderItems表有訂單號order_num和item_price商品售出價格、quantity商品數量

【問題】撰寫 SQL 陳述句，回傳訂單總價不小于1000 的客戶名稱和總額（OrderItems 表中的order_num）， 提示：需要計算總和（item_price 乘以 quantity），按總額對結果進行排序，請使用INNER JOIN 語法，

select c.cust_name,sum(o2.item_price*o2.quantity) as total_price
from Customers c
join Orders o1
on c.cust_id=o1.cust_id
join OrderItems o2
on o1.order_num=o2.order_num
group by c.cust_name
having total_price>=1000
order by total_price;

SQL101 檢索每個顧客的名稱和所有的訂單號（一）

【描述】Customers表代表顧客資訊含有顧客id cust_id和 顧客名稱 cust_name

【問題】使用 INNER JOIN 撰寫 SQL陳述句，檢索每個顧客的名稱（Customers表中的 cust_name）和所有的訂單號（Orders 表中的 order_num），最后根據顧客姓名cust_name升序回傳，

select c.cust_name,o.order_num
from Customers c
join Orders o
on c.cust_id=o.cust_id
order by c.cust_name;

SQL102 檢索每個顧客的名稱和所有的訂單號（二）

【描述】Orders表代表訂單資訊含有訂單號order_num和顧客id cust_id

【問題】檢索每個顧客的名稱（Customers表中的 cust_name）和所有的訂單號（Orders 表中的 order_num），列出所有的顧客，即使他們沒有下過訂單，最后根據顧客姓名cust_name升序回傳，

select c.cust_name,o.order_num
from Customers c 
left join Orders o
on c.cust_id=o.cust_id
order by c.cust_name;

SQL103 回傳產品名稱和與之相關的訂單號

【描述】Products表為產品資訊表含有欄位prod_id產品id、prod_name產品名稱；OrderItems表為訂單資訊表含有欄位order_num訂單號和產品id prod_id

【問題】 使用 OUTER JOIN 聯結 Products 表和 OrderItems 表，回傳產品名稱（prod_name）和與之相關的訂單號（order_num）的串列，并按照產品名稱升序排序，

select p.prod_name,o.order_num
from Products p
left join OrderItems o
on p.prod_id=o.prod_id
order by p.prod_name;

 SQL104 回傳產品名稱和每一項產品的總訂單數

【描述】Products表為產品資訊表含有欄位prod_id產品id、prod_name產品名稱；OrderItems表為訂單資訊表含有欄位order_num訂單號和產品id prod_id

【問題】 使用 OUTER JOIN 聯結 Products 表和 OrderItems 表，回傳產品名稱（prod_name）和每一項產品的總訂單數（不是訂單號），并按產品名稱升序排序，

select p.prod_name,count(o.order_num) as orders
from Products p
left join OrderItems o
on p.prod_id=o.prod_id
group by p.prod_name
order by p.prod_name;

 

SQL105 列出供應商及其可供產品的數量

【描述】有Vendors表含有vend_id供應商id.有Products表含有供應商id和供應產品id

【問題】 列出供應商（Vendors 表中的 vend_id）及其可供產品的數量，包括沒有產品的供應商，你需要使用 OUTER JOIN 和 COUNT()聚合函式來計算 Products 表中每種產品的數量，最后根據vend_id 升序排序， 注意：vend_id 列會顯示在多個表中，因此在每次參考它時都需要完全限定它，

select v.vend_id,count(p.prod_id)
from Vendors v 
left join Products p 
on v.vend_id=p.vend_id
group by v.vend_id
order by v.vend_id;

 13 組合查詢

SQL106 將兩個 SELECT 陳述句結合起來（一）

【描述】表OrderItems包含訂單產品資訊，欄位prod_id代表產品id、quantity代表產品數量

【問題】將兩個 SELECT 陳述句結合起來，以便從 OrderItems表中檢索產品 id（prod_id）和 quantity，其中，一個 SELECT 陳述句過濾數量為 100 的行，另一個 SELECT 陳述句過濾 id 以 BNBG 開頭的產品，最后按產品 id 對結果進行升序排序，  

select prod_id,quantity
from OrderItems
where quantity='100'
union
select prod_id,quantity
from OrderItems
where prod_id LIKE'BNBG%'
order by prod_id;

 

SQL107 將兩個 SELECT 陳述句結合起來（二）

【描述】表OrderItems包含訂單產品資訊，欄位prod_id代表產品id、quantity代表產品數量， 【問題】將兩個 SELECT 陳述句結合起來，以便從 OrderItems表中檢索產品 id（prod_id）和 quantity，其中，一個 SELECT 陳述句過濾數量為 100 的行，另一個 SELECT 陳述句過濾 id 以 BNBG 開頭的產品，最后按產品 id 對結果進行升序排序，  注意：這次僅使用單個 SELECT 陳述句，

select prod_id,quantity from OrderItems
where quantity='100' or prod_id LIKE'BNBG%'
order by prod_id;

SQL108 組合 Products 表中的產品名稱和 Customers 表中的顧客名稱

【描述】Products表含有欄位prod_name代表產品名稱，Customers表代表顧客資訊，cust_name代表顧客名稱

撰寫 SQL 陳述句，組合 Products 表中的產品名稱（prod_name）和 Customers 表中的顧客名稱（cust_name）并回傳，然后按產品名稱對結果進行升序排序，

select prod_name
from Products
union all
select cust_name
from Customers
order by prod_name;

SQL109 糾錯4

【描述】表Customers含有欄位cust_name顧客名、cust_contact顧客聯系方式、cust_state顧客州、cust_email顧客email

【問題】修正下面錯誤的SQL

SELECT cust_name, cust_contact, cust_email 
FROM Customers 
WHERE cust_state = 'MI' 
ORDER BY cust_name; 
UNION 
SELECT cust_name, cust_contact, cust_email 
FROM Customers 
WHERE cust_state = 'IL'ORDER BY cust_name;

【示例決議】 回傳住在"IL"和"MI"的顧客資訊，最后根據顧客名稱升序排序，

SELECT cust_name, cust_contact, cust_email 
FROM Customers 
WHERE cust_state = 'MI' 
UNION 
SELECT cust_name, cust_contact, cust_email 
FROM Customers 
WHERE cust_state = 'IL'
ORDER BY cust_name;

 

 

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

標籤：MySQL

上一篇：mysql拆分字串做條件查詢

下一篇：主鍵約束，唯一約束，默認約束，檢查約束，洗掉主鍵，洗掉外鍵按，洗掉列，添加列