1.1查询account表中客户的姓名(fullname)、地址(address)和电话(phone),显示的列标题要求显示“姓名”“地址”“电话”
Select fullname as 姓名,address as 地址, phoneas 电话 from account;
1.2 查询lineitem表中商品号(itemid)和单价(unitprice),要求清除重复行
Select distinct itemid,unitprice from lineitem;
1.3 计算lineitem表中每条记录的商品金额
Select orderid,itemid,quantity * unitprice as 金额 from lineitem;
1.4 查询account表中客户的姓名(fullname)和性别(sex),要求性别为“男”时显示1,性别为女时显示0.
Select fullname,
Case when sex = ‘男’ then ‘1’、
When sex = ‘女’ then ‘0’
End as sex
From account;
1.5 查询product表中商品名(name)和档次。档次按单价(unitcost)划分,1000元一下显示为“低价商品”,1000元到2000元显示为“中档商品”,2000元及以上显示为“高档商品”
Select name,
Case
When unitcost <1000 then ‘低价商品’
When unitcost >=1000 and unitcost <2000 then ‘中档商品’
Else ‘高档商品’
End as 档次
From product;
2.1显示orders表中单笔订单金额大于等于200元的 用户号(userid)、订单总价(totalprice)和订单状态(status)
Select userid,totalprice,status from orders where totalprice >=200;
2.2查询orders表中2020年4月的所有订单
Select * from orders
Where orderdate >= ‘2020-04-01’ and orderdate <=’2020-04-30’ ;
2.3 查询account表中女客户的姓名(fullname)地址(address)和电话(phone),显示的列标题要求用中文“姓名”“地址”“电话”表示
Select fullname as 姓名,address as 地址,phone as 电话
From account where sex = ‘女’;
2.4查询account表中姓吴的客户信息
Select * from account where fullname like ‘吴%’;
2.5 查询orders 表中订单总价再200--500元内的订单信息
Select *from orders where totalprice >200 and totalprice <=500;
2.6查询product表中商品号(productid)倒数第四个标号为‘W’的商品信息
Select * from product where productid like ‘%W____’;
3.1 查询lineitem表中的订单号、商品名称和购买数量
Select orderid, name,quantity from lineitem
Join product on(itemid=productid);
3.2显示orders表中单笔订单金额大于等于300元的用户名、订单总价
Select fullanme,totalprice from orders
Join account on (orders.userid=account.userid)
Where totalprice>300;
3.3查询刘晓和的基本情况和订单情况
Select *from orders join account
On (orders.userid=account.userid)
Where fullname = ‘刘晓和’;
3.4统计2020年5月以前订购了商品女客户姓名和订单总价
Select fullname,totalprice from orders
Join account on(orders.userid=account.userid)
Where orderdate <=’2020-05-01’ and sex =’女’;
3.5 查找购买了商品为‘FL-SW-02’的商品的订单号、用户号和订单日期
Select orderid,userid,orderdate from orders
Where orderid in
(select orderid from lineitem where itemid = ‘FI-SW-02’);
3.6查找product表中价格不低于’波斯猫’的商品信息
Select * from product where unitcost >= any
(select unitcost from product where name =‘波斯猫’);
4.1 统计客户总数
Select count(*) as 总人数 from account;
4.2 计算 orders表中的每单的平均价
Select avg(totalprice) as 每单平均价 from orders;
4.3 计算orders 表中的成交总额
Select sum(totalprice) as 成交总额 from orders;
4.4 显示orders表中的单笔最高交易成交额和最低交易成交额
Select max(totalprice)as 最高成绩额
Min(totalprice)as 最低成交额
From orders;
4.5 按性别统计客户人数
Select sex,count(*)from account group by sex;
4.6 按照商品类别统计各类商品总数、平均单价
Select catid,sum(qty),avg(unitcost) from product group by catid;
4.7 将客户信息按电话号码从小到大排列
Select * from account order by phone desc;
4.8将orders表按照用户号从小到大排序,用户号相同的按订单日期从大到小排序
Select * from orders order by userid ,orderdate desc;
4.9显示lineitem 表中的商品购买总数量超过两件的商品号和购买总数量,并按照购买的数量从小到大排列
Select itemid ,sum(quantity)from lineitem
Group by itemid
Having sum (quantity)>=2
Order by sum (quantity);