MySQL-单表查询练习题

July 22 2017

1.返回2007年6月生成的订单。涉及的表：Orders表

期望输出

select orderid, orderdate, custid, empid
from orders
where date_format(orderdate, '%Y%m') = '200706'
;

2.返回每月的最后一天生成的订单。涉及的表：Orders表

期望输出

select orderid, orderdate, custid, empid
from orders
where orderdate = last_day(orderdate)
;

3.返回姓氏（lastname）中包含字母‘a’两次或更多次的雇员。涉及的表：Employees表

期望输出

select empid, firstname, lastname
from employees
where lastname like '%a%a%'
;

4.返回总价格（数量*单价）大于10000的所有订单，并按总价格排序。涉及的表：OrderDetails表

期望输出

select orderid, sum(qty * unitprice) totalvalue
from orderdetails
group by orderid
having sum(qty * unitprice) > 10000
order by totalvalue desc
;

5.返回2007年平均运费最高的三个国家。涉及的表：Orders表

期望输出

select shipcountry, avg(freight) avgfreight
from orders
where year(shippeddate) = 2007
group by shipcountry
order by avgfreight desc
limit 3;

6.为每个顾客单独根据订单日期的顺序（用orderID作为附加属性）来计算其订单的行号。涉及的表：Orders表

期望输出

select custid, orderdate, orderid, rownum
from (
	select t.custid, t.orderdate, t.orderid, @rownum:=@rownum+1,
	if(@pcust=t.custid, @rank:=@rank+1, @rank:=1) rownum,
	@pcust:=t.custid
	from (
		select custid, orderdate, orderid
		from orders
		order by custid, orderdate
	) t, (select @rownum:=0, @pcust:=null, @rank:=0) a
) result
;

7.构造一个SELECT语句，让它根据每个雇员的友好称谓，而返回其性别。对于‘Ms.’和‘Mrs.’，则返回‘Female’；对于‘Mr.’，则返回‘Female’；对于‘Mr；对于其他情况（例如：‘Dr.’），则返回‘Uknown’。涉及的表：Employees表

期望输出

select empid, firstname, lastname, titleofcourtesy, 
case titleofcourtesy
	when 'Ms.' then 'Female'
	when 'Mrs.' then 'Female'
	when 'Mr.' then 'Male'
	else 'Unknown'
end gender
from employees
;

8.返回每个客户的客户ID和所在区域。对输出中的行按区域排序，NULL值排在最后（在所有非NULL值之后）。涉及的表：Customers表

期望输出

select custid, region 
from customers
order by isnull(region), region, custid
;
或
select custid, region 
from customers
order by case when region is null then 1 else 0 end, region, custid
;