sql查询练习题

sql查询练习题(1-3 检索数据,4-7 排序检索数据,8-11 过滤数据,12-15 高级数据过滤,16-19 用通配符进行过滤,20-21 创建计算字段,22-23 使用函数处理数据,24-26 汇总数据,27-31 分组数据,32-36 使用子查询,37-41 联结表,42-46 创建高级联结,47-50 组合查询)

1、编写 SQL 语句,从 Customers 表中检索所有的cust_id

答案:select cust_id from Customers;

2、编写SQL 语句,从表OrderItems中检索并列出所有已订购商品(prod_id)的去重后的清单

答案:select distinct prod_id from OrderItems;

3、现在有Customers 表(表中含有列cust_id代表客户id,cust_name代表客户姓名)。需要编写 SQL语句,检索所有列

答案:select cust_id,cust_name from Customers;

4、有表Customers,cust_id代表客户id,cust_name代表客户姓名。从 Customers 中检索所有的顾客名称(cust_name),并按从 Z 到 A 的顺序显示结果。

答案:select cust_name from Customers order by cust_name desc;

5、编写 SQL 语句,从 Orders 表中检索顾客 ID(cust_id)和订单号(order_num),并先按顾客 ID 对结果进行排序,再按订单日期倒序排列。

答案:select cust_id,order_num from Orders order by cust_id,order_date desc;

6、编写 SQL 语句,显示 OrderItems 表中的数量(quantity)和价格(item_price),并按数量由多到少、价格由高到低排序。

答案:select quantity,item_price from OrderItems order by quantity desc,item_price desc;

7、下面的 SQL 语句有问题吗?尝试将它改正确,使之能够正确运行,并且返回结果根据vend_name逆序排列

SELECT vend_name, 
FROM Vendors 
ORDER vend_name DESC;

答案:

SELECT vend_name 
FROM Vendors 
ORDER by vend_name DESC;

8、从 Products 表中检索产品 ID(prod_id)和产品名称(prod_name),只返回价格(prod_price)为 9.49 美元的产品。

答案:select prod_id,prod_name from Products where prod_price=9.49;

9、编写 SQL 语句,从 Products 表中检索产品 ID(prod_id)和产品名称(prod_name),只返回价格(prod_price)为 9 美元或更高的产品

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

10、编写 SQL 语句,返回 Products 表中所有价格在 3 美元到 6 美元之间的产品的名称(prod_name)和价格(prod_price),然后按价格对结果进行排序

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

11、从 OrderItems 表中检索出所有不同且不重复的订单号(order_num),其中每个订单都要包含 100 个或更多的产品

答案:select distinct order_num from OrderItems where quantity>=100;

12、Vendors表有字段供应商名称(vend_name)、供应商国家(vend_country)、供应商州(vend_state)。编写 SQL 语句,从 Vendors 表中检索供应商名称(vend_name),仅返回加利福尼亚州的供应商(这需要按国家[USA]和州[CA]进行过滤,没准其他国家也存在一个CA)

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

13、OrderItems 表包含了所有已订购的产品(有些已被订购多次)。编写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');

14、有表Products。编写 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;

15、修改正确下面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;

16、编写 SQL 语句,从 Products 表中检索产品名称(prod_name)和描述(prod_desc),仅返回描述中包含 toy 一词的产品名称

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

17、编写 SQL 语句,从 Products 表中检索产品名称(prod_name)和描述(prod_desc),仅返回描述中未出现 toy 一词的产品,最后按”产品名称“对结果进行排序

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

18、编写 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%';

19、编写 SQL 语句,从 Products 表中检索产品名称(prod_name)和描述(prod_desc),仅返回在描述中以先后顺序同时出现 toy 和 carrots 的产品。提示:只需要用带有三个 % 符号的 LIKE 即可

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

20、编写 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 vend_name;

21、我们的示例商店正在进行打折促销,所有产品均降价 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;

22、编写 SQL 语句,返回顾客 ID(cust_id)、顾客名称(cust_name)和登录名(user_login),其中登录名全部为大写字母,并由顾客联系人的前两个字符(cust_contact)和其所在城市的前三个字符(cust_city)组成。提示:需要使用函数、拼接和别名

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

23、Orders订单表。编写 SQL 语句,返回 2020 年 1 月的所有订单的订单号(order_num)和订单日期(order_date),并按订单日期升序排序

答案:select order_num,order_date from Orders where order_date like '2020-01%' order by order_date;

24、OrderItems表代表售出的产品,quantity代表售出商品数量。编写 SQL 语句,确定已售出产品的总数。返回items_ordered列名,表示已售出商品的总数

答案:select sum(quantity) as items_ordered from OrderItems;

25、OrderItems表代表售出的产品,quantity代表售出商品数量,产品项为prod_id。返回items_ordered列名,表示已售出商品的总数,确定已售出产品项(prod_id)为"BR01"的总数

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

26、编写 SQL 语句,确定 Products 表中价格不超过 10 美元的最贵产品的价格(prod_price)。将计算所得的字段命名为 max_price。返回max_price

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

27、OrderItems 表包含每个订单的每个产品。编写 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;

28、有Products表,含有字段prod_price代表产品价格,vend_id代表供应商id。编写 SQL 语句,返回名为 cheapest_item 的字段,该字段包含每个供应商成本最低的产品(使用 Products 表中的 prod_price),然后从最低成本到最高成本对结果进行升序排序。返回供应商id vend_id和对应供应商成本最低的产品cheapest_item

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

29、OrderItems代表订单商品表,包括:订单号order_num和订单数量quantity。请编写 SQL 语句,返回订单数量总和不小于100的所有订单号,最后结果按照订单号升序排序。返回order_num订单号(where过滤行;having过滤分组)

答案:select distinct order_num from OrderItems group by order_num having sum(quantity)>=100 order by order_num;

30、OrderItems表代表订单信息,包括字段:订单号order_num和item_price商品售出价格、quantity商品数量。编写 SQL 语句,根据订单号聚合,返回订单总价不小于1000 的所有订单号,最后的结果按订单号进行升序排序(提示:总价 = item_price 乘以 quantity)

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

31、OrderItems表含有order_num订单号。将下面代码修改正确后执行,返回订单号order_num和出现的次数items

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 items, order_num;

32、OrderItems表示订单商品表,含有字段订单号:order_num、订单价格:item_price;Orders表代表订单信息表,含有顾客id:cust_id和订单号:order_num。使用子查询,返回购买价格为 10 美元或以上产品的顾客列表,结果无需排序。注意:你需要使用 OrderItems 表查找匹配的订单号(order_num),然后使用Order 表检索这些匹配订单的顾客 ID(cust_id)

答案:select distinct cust_id from Orders where order_num in (select order_num from OrderItems where item_price >=10);

33、表OrderItems代表订单商品信息表,prod_id为产品id;Orders表代表订单表有cust_id代表顾客id和订单日期order_date。编写 SQL 语句,使用子查询来确定哪些订单(在 OrderItems 中)购买了 prod_id 为 "BR01" 的产品,然后从 Orders 表中返回每个产品对应的顾客 ID(cust_id)和订单日期(order_date),按订购日期对结果进行升序排序

OrderItems表

prod_id order_num
BR01 a0001
BR01 a0002
BR02 a0003
BR02 a0013

Orders表

order_num cust_id order_date
a0001 cust10 2022-01-01 00:00:00
a0002 cust1 2022-01-01 00:01:00
a0003 cust1 2022-01-02 00:00:00
a0013 cust2 2022-01-01 00:20:00

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

34、你想知道订购 BR01 产品的日期,有表OrderItems代表订单商品信息表,prod_id为产品id;Orders表代表订单表有cust_id代表顾客id和订单日期order_date;Customers表含有cust_email 顾客邮件和cust_id顾客id

OrderItems表

prod_id order_num
BR01 a0001
BR01 a0002
BR02 a0003
BR02 a0013

Orders表

order_num cust_id order_date
a0001 cust10 2022-01-01 00:00:00
a0002 cust1 2022-01-01 00:01:00
a0003 cust1 2022-01-02 00:00:00
a0013 cust2 2022-01-01 00:20:00

Customers表代表顾客信息,cust_id为顾客id,cust_email为顾客email

cust_id cust_email
cust10 cust10@cust.com
cust1 cust1@cust.com
cust2 cust2@cust.com

返回购买 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'));

35、 我们需要一个顾客 ID 列表,其中包含他们已订购的总金额。

OrderItems表代表订单信息,OrderItems表有订单号:order_num和商品售出价格:item_price、商品数量:quantity。

order_num item_price quantity
a0001 10 105
a0002 1 1100
a0002 1 200
a0013 2 1121
a0003 5 10
a0003 1 19
a0003 7 5

Orders表订单号:order_num、顾客id:cust_id

order_num cust_id
a0001 cust10
a0002 cust1
a0003 cust1
a0013 cust2

编写 SQL语句,返回顾客 ID(Orders 表中的 cust_id),并使用子查询返回total_ordered 以便返回每个顾客的订单总数,将结果按金额从大到小排序(提示:你之前已经使用 SUM()计算订单总数)。

答案:

select cust_id,
(select sum(item_price*quantity) 
    from OrderItems 
    where OrderItems.order_num=Orders.order_num group by order_num) 
as total_ordered from Orders order by total_ordered desc;
##############################################################################
select cust_id,
    (select total_ordered 
    from
        (select distinct order_num,round(sum(item_price*quantity)) as total_ordered
        from OrderItems
        group by order_num
        ) as t1
    where t1.order_num=Orders.order_num
    ) as total_ordered
from Orders
order by total_ordered desc;

36、Products 表中检索所有的产品名称:prod_name、产品id:prod_id

prod_id prod_name
a0001 egg
a0002 sockets
a0013 coffee
a0003 cola

OrderItems代表订单商品表,订单产品:prod_id、售出数量:quantity

prod_id quantity
a0001 105
a0002 1100
a0002 200
a0013 1121
a0003 10
a0003 19
a0003 5

编写 SQL 语句,从 Products 表中检索所有的产品名称(prod_name),以及名为 quant_sold 的计算列,其中包含所售产品的总数(在 OrderItems 表上使用子查询和 SUM(quantity)检索)

答案:

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

37、描述

Customers 表有字段顾客名称cust_name、顾客id cust_id

cust_id cust_name
cust10 andy
cust1 ben
cust2 tony
cust22 tom
cust221 an
cust2217 hex

Orders订单信息表,含有字段order_num订单号、cust_id顾客id

order_num cust_id
a1 cust10
a2 cust1
a3 cust2
a4 cust22
a5 cust221
a7 cust2217

编写 SQL 语句,返回 Customers 表中的顾客名称(cust_name)和Orders 表中的相关订单号(order_num),并按顾客名称再按订单号对结果进行升序排序。你可以尝试用两个不同的写法,一个使用简单的等联结语法,另外一个使用 INNER JOIN。

答案:

  1. select cust_name,order_num from Customers,Orders where Customers.cust_id=Orders.cust_id
    order by cust_name,order_num;
  2. select cust_name,order_num from Customers inner join Orders on Customers.cust_id=Orders.cust_id
    order by cust_name,order_num;

38、描述

Customers 表有字段,顾客名称:cust_name、顾客id:cust_id

cust_id cust_name
cust10 andy
cust1 ben
cust2 tony
cust22 tom
cust221 an
cust2217 hex

Orders订单信息表,含有字段,订单号:order_num、顾客id:cust_id

order_num cust_id
a1 cust10
a2 cust1
a3 cust2
a4 cust22
a5 cust221
a7 cust2217

OrderItems表有字段,商品订单号:order_num、商品数量:quantity、商品价格:item_price

order_num quantity item_price
a1 1000 10
a2 200 10
a3 10 15
a4 25 50
a5 15 25
a7 7 7

除了返回顾客名称和订单号,返回 Customers 表中的顾客名称(cust_name)和Orders 表中的相关订单号(order_num),添加第三列 OrderTotal,其中包含每个订单的总价,并按顾客名称再按订单号对结果进行升序排序。

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

39、描述

表OrderItems代表订单商品信息表,prod_id为产品id;Orders表代表订单表有cust_id代表顾客id和订单日期order_date

OrderItems表

prod_id order_num
BR01 a0001
BR01 a0002
BR02 a0003
BR02 a0013

Orders表

order_num cust_id order_date
a0001 cust10 2022-01-01 00:00:00
a0002 cust1 2022-01-01 00:01:00
a0003 cust1 2022-01-02 00:00:00
a0013 cust2 2022-01-01 00:20:00

编写 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;

40、描述

有表OrderItems代表订单商品信息表,prod_id为产品id;Orders表代表订单表有cust_id代表顾客id和订单日期order_date;Customers表含有cust_email 顾客邮件和cust_id顾客id

OrderItems表

prod_id order_num
BR01 a0001
BR01 a0002
BR02 a0003
BR02 a0013

Orders表

order_num cust_id order_date
a0001 cust10 2022-01-01 00:00:00
a0002 cust1 2022-01-01 00:01:00
a0003 cust1 2022-01-02 00:00:00
a0013 cust2 2022-01-01 00:20:00

Customers表代表顾客信息,cust_id为顾客id,cust_email为顾客email

cust_id cust_email
cust10 cust10@cust.com
cust1 cust1@cust.com
cust2 cust2@cust.com

返回购买 prod_id 为BR01 的产品的所有顾客的电子邮件(Customers 表中的 cust_email),结果无需排序(提示:涉及到 SELECT 语句,最内层的从 OrderItems 表返回 order_num,中间的从 Customers 表返回 cust_id,但是必须使用 INNER JOIN 语法)。

答案:

  1. 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'));
  2. select cust_email from Customers inner join Orders on Customers.cust_id=Orders.cust_id inner join OrderItems on Orders.order_num=OrderItems.order_num where prod_id='BR01';

41、描述

OrderItems表代表订单信息,确定最佳顾客的另一种方式是看他们花了多少钱,OrderItems表有订单order_num和item_price商品售出价格、quantity商品数量

order_num item_price quantity
a1 10 105
a2 1 1100
a2 1 200
a4 2 1121
a5 5 10
a2 1 19
a7 7 5

Orders表含有字段order_num 订单号、cust_id顾客id

order_num cust_id
a1 cust10
a2 cust1
a3 cust2
a4 cust22
a5 cust221
a7 cust2217

顾客表Customers有字段cust_id 客户id、cust_name 客户姓名

cust_id cust_name
cust10 andy
cust1 ben
cust2 tony
cust22 tom
cust221 an
cust2217 hex

编写 SQL 语句,返回订单总价不小于1000 的客户名称和总额(OrderItems 表中的order_num)(提示:需要计算总和(item_price 乘以 quantity)。按总额对结果进行排序,请使用INNER JOIN 语法)。

答案:select cust_name,total_price from Customers inner join Orders on Customers.cust_id = Orders.cust_id inner join (select order_num,sum(item_price*quantity) as total_price from OrderItems group by order_num) t1 on t1.order_num = Orders.order_num where total_price >= 1000 order by total_price;

42、描述

Customers表代表顾客信息含有顾客id cust_id和 顾客名称 cust_name

cust_id cust_name
cust10 andy
cust1 ben
cust2 tony
cust22 tom
cust221 an
cust2217 hex

Orders表代表订单信息含有订单号order_num和顾客id cust_id

order_num cust_id
a1 cust10
a2 cust1
a3 cust2
a4 cust22
a5 cust221
a7 cust2217

使用 INNER JOIN 编写 SQL语句,检索每个顾客的名称(Customers表中的 cust_name)和所有的订单号(Orders 表中的 order_num),最后根据顾客姓名cust_name升序返回。

答案:select cust_name,order_num from Customers inner join Orders on Orders.cust_id=Customers.cust_id order by cust_name;

43、描述

Orders表代表订单信息含有订单号order_num和顾客id cust_id

order_num cust_id
a1 cust10
a2 cust1
a3 cust2
a4 cust22
a5 cust221
a7 cust2217

Customers表代表顾客信息含有顾客id cust_id和 顾客名称 cust_name

cust_id cust_name
cust10 andy
cust1 ben
cust2 tony
cust22 tom
cust221 an
cust2217 hex
cust40 ace

检索每个顾客的名称(Customers表中的 cust_name)和所有的订单号(Orders 表中的 order_num),列出所有的顾客,即使他们没有下过订单。最后根据顾客姓名cust_name升序返回。

答案:select cust_name,order_num from Customers left join Orders on Customers.cust_id=Orders.cust_id order by cust_name;

分析:关键词:left join

用法:

  • 内联结:inner join。取两列的交集。
  • 外联结:
    • left join。左连接,以左边表的列为主,取两列的交集,对于不在右边列存在的名称取null。
    • right join。右连接,以右边表的列为主,取两列的交集,对于不在左边列存在的名称取null。

44、描述

Products表为产品信息表含有字段prod_id产品id、prod_name产品名称

prod_id prod_name
a0001 egg
a0002 sockets
a0013 coffee
a0003 cola
a0023 soda

OrderItems表为订单信息表含有字段order_num订单号和产品id prod_id

prod_id order_num
a0001 a105
a0002 a1100
a0002 a200
a0013 a1121
a0003 a10
a0003 a19
a0003 a5

使用 OUTER JOIN 联结 Products 表和 OrderItems 表,返回产品名称(prod_name)和与之相关的订单号(order_num)的列表,并按照产品名称升序排序。

答案:select prod_name,order_num from Products left join OrderItems on Products.prod_id=OrderItems.prod_id order by prod_name;

45、描述

Products表为产品信息表含有字段prod_id产品id、prod_name产品名称

prod_id prod_name
a0001 egg
a0002 sockets
a0013 coffee
a0003 cola
a0023 soda

OrderItems表为订单信息表含有字段order_num订单号和产品id prod_id

prod_id order_num
a0001 a105
a0002 a1100
a0002 a200
a0013 a1121
a0003 a10
a0003 a19
a0003 a5

使用 OUTER JOIN 联结 Products 表和 OrderItems 表,返回产品名称(prod_name)和每一项产品的总订单数(不是订单号),并按产品名称升序排序。

答案:select prod_name,if(orders is null,0,orders) as orders from Products left join (select prod_id,count(prod_id) as orders from OrderItems group by prod_id) as t1 on Products.prod_id = t1.prod_id order by prod_name;

46、描述

有Vendors表含有vend_id供应商id.

vend_id
a0002
a0013
a0003
a0010

有Products表含有供应商id和供应产品id

vend_id prod_id
a0001 egg
a0002 prod_id_iphone
a00113 prod_id_tea
a0003 prod_id_vivo phone
a0010 prod_id_huawei phone

列出供应商(Vendors 表中的 vend_id)及其可供产品的数量,包括没有产品的供应商。你需要使用 OUTER JOIN 和 COUNT()聚合函数来计算 Products 表中每种产品的数量,最后根据vend_id 升序排序(注意:vend_id 列会显示在多个表中,因此在每次引用它时都需要完全限定它)。

答案:select Vendors.vend_id,if(prod_num is null,0,prod_num) as prod_id from Vendors left join (select vend_id,count(prod_id) as prod_num from Products group by vend_id) t1 on t1.vend_id=Vendors.vend_id order by Vendors.vend_id;

47、描述

表OrderItems包含订单产品信息,字段prod_id代表产品id、quantity代表产品数量

prod_id quantity
a0001 105
a0002 100
a0002 200
a0013 1121
a0003 10
a0003 19
a0003 5
BNBG 10002

将两个 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;

分析

关键词:union

用法:

  • join---连接表,对列操作
  • union--连接表,对行操作。
    • union--将两个表做行拼接,同时自动删除重复的行。
    • union all---将两个表做行拼接,保留重复的行。

48、描述

表OrderItems包含订单产品信息,字段prod_id代表产品id、quantity代表产品数量。

prod_id quantity
a0001 105
a0002 100
a0002 200
a0013 1121
a0003 10
a0003 19
a0003 5
BNBG 10002

将两个 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;

49、描述

Products表含有字段prod_name代表产品名称

prod_name
flower
rice
ring
umbrella

Customers表代表顾客信息,cust_name代表顾客名称

cust_name
andy
ben
tony
tom
an
lee
hex

编写 SQL 语句,组合 Products 表中的产品名称(prod_name)和 Customers 表中的顾客名称(cust_name)并返回,然后按产品名称对结果进行升序排序。

答案:select prod_name from Products union select cust_name from Customers order by prod_name;

50、描述

表Customers含有字段cust_name顾客名、cust_contact顾客联系方式、cust_state顾客州、cust_email顾客email

cust_name cust_contact cust_state cust_email
cust10 8695192 MI cust10@cust.com
cust1 8695193 MI cust1@cust.com
cust2 8695194 IL cust2@cust.com

【问题】修正下面错误的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;

【示例结果】

返回顾客名称:cust_name、顾客联系方式:cust_contact、顾客email:cust_email

cust_name cust_contact cust_email
cust1 8695193 cust1@cust.com
cust10 8695192 cust10@cust.com
cust2 8695194 cust2@cust.com

【示例解析】

返回住在"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;
posted @ 2022-06-01 18:29  wydilearn  阅读(76)  评论(0编辑  收藏  举报