牛客SQL刷题第三趴——SQL必知必会
01检索数据
SQL60 从 Customers 表中检索所有的 ID
编写 SQL 语句,从 Customers 表中检索所有的cust_id
select * from Customers;
SQL61 检索并列出已订购产品的清单
【问题】编写SQL 语句,检索并列出所有已订购商品(prod_id)的去重后的清单。
select DISTINCT(prod_id) from OrderItems;
SQL62 检索所有列
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 按照数量和价格排序
select * from OrderItems
order by quantity desc,item_price desc;
SQL66 检查SQL语句
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 检索并列出已订购产品的清单
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 检索产品名称和描述(一)
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 检索产品名称和描述(三)
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 打折
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 每个供应商成本最低的产品
select vend_id,min(prod_price) as cheapest_item
from Products
group by vend_id
order by cheapest_item;
SQL88 返回订单数量总和不小于100的所有订单的订单号
select order_num
from OrderItems
where quantity>=100
order by order_num;
SQL89 计算总和
【描述】OrderItems表代表订单信息,包括字段:订单号order_num和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;
解题思路:️聚合函数+having筛选条件。️group by order_num,按照order_num分组。
SQL90 纠错3
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
select cust_id,order_date from Orders
where order_num in(select order_num from OrderItems where prod_id='BR01');
SQL93 返回购买 prod_id 为 BR01 的产品的所有顾客的电子邮件
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 返回每个顾客不同订单的总金额
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
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。
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
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
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商品数量
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
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
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
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 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 语句结合起来(二)
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;
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;
最新文章
- IT培训行业揭秘(五)
- druid数据库密码加密程序编写
- “static”引发的一个错误
- inside the C++ Object model总结
- Objective C中数组排序几种情况的总结
- Linux操作系统奥秘01-系统引导(MBR - 硬盘的0磁道)
- Js document.frmLogin.action = &#39;/login.htm&#39;;的意义和form表单的target属性
- C++Builder组件
- css之border,dispaly
- Gof-23种设计模式名称列表
- 连接MySQL数据库得到错误&ldquo;Unable to find the requested .Net Framework Data Provider&rdquo;
- php如何做数据库攻击
- cocos2d移植到安卓引入第三方so文件时候编译会删除解决方式
- (原+转)C++中的lambda表达式
- NYOJ--1058--dfs--部分和问题
- Rx系列二 | Observer | Observable
- __x__(27)0907第四天__ float 浮动
- 图解HTTP第四章
- .NET CORE 实践(3)--Visual Studio 2015 Update 3更新之后DotNetCore.1.0.1-VS2015Tools.Preview2.0.2.exe无法正确安装
- 背水一战 Windows 10 (66) - 控件(WebView): 监听和处理 WebView 的事件