牛客网sql必知必会50题（笔试，巩固基础）

select * from Customers;

select distinct * from OrderItems;

select cust_id, cust_name from Customers 

 

select cust_name from Customers order by cust_name desc;

 

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

 

 

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

 

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

 

SELECT vend_name
FROM Vendors 
ORDER by vend_name DESC;

 

select prod_id, prod_name from Products where prod_price = 9.49;

 

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

select prod_name, prod_price from Products 
where prod_price between 3 and 6 order by prod_price;

 

select distinct order_num from OrderItems where quantity >= 100;

 

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

 

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

select prod_name, prod_price from Products 
where prod_price between 3 and 6 
order by prod_price;

 

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

 

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

 

select prod_name, prod_desc from Products 
where prod_desc not like '%toy%' 
order by prod_name;

 

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

 

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

 

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

 

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

 最开始没写出来

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

注意sql的索引是从1开始算的

 

select order_num, order_date from Orders 
where order_date >= '2020-01-01 00:00:00' and order_date < '2020-02-01 00:00:00'
order by order_date; 

 

select sum(quantity) items_ordered from OrderItems;

 

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

 

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

 

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

最开始写的错了

 分组聚合一定不要忘记加上 group by，不然只会有一行结果

 

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

 

select order_num from OrderItems where sum(quantity) >= 100 
group by order_num order by order_num;

刚开始写的不对

 注意过滤分组使用having（对分组后的条件进行过滤），与 group by 连用

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

 最开始写的错误

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

 

select cust_id from OrderItems oi, Orders od 
where oi.order_num = od.order_num and oi.item_price >= 10;

 

select cust_id, order_date from OrderItems oi, Orders od 
where oi.order_num = od.order_num and prod_id = 'BR01';

 

select c.cust_email from OrderItems oi, Orders od, Customers c
where oi.order_num = od.order_num and c.cust_id = od.cust_id and oi.prod_id = 'BR01';

 

select distinct od.cust_id, temp.total_ordered from OrderItems oi, Orders od, 
(select order_num, sum(item_price * quantity) total_ordered from OrderItems 
group by order_num having total_ordered) temp
where od.order_num = oi.order_num and od.order_num = temp.order_num
order by temp.total_ordered desc;

最开始写用的时间比较久，看看别人的写法

 

select prod_name, quant_sold from Products pt, 
(select prod_id, sum(quantity) quant_sold from OrderItems group by prod_id) qt
where pt.prod_id = qt.prod_id;

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

 

 

select order_num,sum(quantity*item_price) OrderTotal 
from OrderItems group by order_num;

select o.cust_id,t.order_num,t.OrderTotal from Orders o
join 
(select order_num,sum(quantity*item_price) OrderTotal 
from OrderItems group by order_num) t
on t.order_num=o.order_num;

select c.cust_name,t.order_num,t.OrderTotal from Orders o
join 
(select order_num,sum(quantity*item_price) OrderTotal 
from OrderItems group by order_num) t
on t.order_num=o.order_num
join Customers c
on c.cust_id=o.cust_id
order by c.cust_name,t.order_num;

 改成直接内连接

select c.cust_name,t.order_num,t.OrderTotal from Orders o, 
(select order_num,sum(quantity*item_price) OrderTotal 
from OrderItems group by order_num) t, Customers c
where t.order_num=o.order_num and c.cust_id=o.cust_id
order by c.cust_name,t.order_num;

注意分组计算订单的总价

(select order_num,sum(quantity*item_price) OrderTotal 
from OrderItems group by order_num) t

select c.cust_name,os.order_num,sum(os.quantity*os.item_price) OrderTotal 
from Orders o
join OrderItems os
on os.order_num=o.order_num
join Customers c
on c.cust_id=o.cust_id
group by c.cust_name,os.order_num
order by c.cust_name,os.order_num;

 

select od.cust_id, od.order_date from Orders od,
(select oi.order_num from OrderItems oi where oi.prod_id = 'BR01') temp
where od.order_num = temp.order_num
order by od.order_date;

 

 

select c.cust_email from Customers c, Orders od,
(select oi.order_num from OrderItems oi where prod_id = 'BR01') temp
where temp.order_num = od.order_num 
and c.cust_id = od.cust_id;

 

select c.cust_name, temp.total_price from Orders od, Customers c,
(select order_num, sum(item_price*quantity) total_price 
from OrderItems oi 
group by order_num having total_price >= 1000) temp
where temp.order_num = od.order_num and od.cust_id = c.cust_id;

 

select c.cust_name, od.order_num 
from Customers c, Orders od
where c.cust_id = od.cust_id
order by c.cust_name;

 

 

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

 

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

刚开始写得不对

 

 

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

最开始写的错了

SELECT prod_name,IF(orders IS NULL,0,orders) AS orders
FROM Products AS p 
LEFT JOIN (SELECT prod_id,COUNT(*) AS orders FROM OrderItems GROUP BY prod_id) AS oi
ON p.prod_id = oi.prod_id
ORDER BY prod_name;

 

 

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

 

((select o1.prod_id, o1.quantity 
from OrderItems o1
where o1.quantity = 100)

union

(select o2.prod_id, o2.quantity 
from OrderItems o2
where o2.prod_id like 'BNBG%'))
order by prod_id;

 

 

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

 

 

(select p.prod_name,
from Products p)

union

(select c.prod_name prod_name,
from Customers c)

order by prod_name;

最开始写的不对

(select p.prod_name
from Products p)

union

(select c.cust_name prod_name
from Customers c)

order by prod_name;

 

SELECT c1.cust_name, c1.cust_contact, c1.cust_email 
FROM Customers c1
WHERE c1.cust_state = 'MI' 

UNION 

SELECT c2.cust_name, c2.cust_contact, c2.cust_email 
FROM Customers c2
WHERE c2.cust_state = 'IL'

ORDER BY cust_name;

 结束，基础还得打牢