bookshop查询
1、在sales表中查找出销售金额最高的订单(订单号,销售金额,订购日期)
SELECT order_no AS 订单号, total_amt AS 销售金额, order_date AS 订购日期
FROM sales
WHERE total_amt =
(
SELECT MAX(total_amt)
FROM sales
)
2、由sales表中查找出订单金额大于“E0016业务员所接任一张订单的金额”的所有订单,
并显示承接这些订单的业务员和该订单的金额
SELECT sale_id AS 业务员编号, order_no AS 订单编号,
total_amt AS 订单金额
FROM sales
WHERE total_amt >any
(
SELECT total_amt
FROM sales B
WHERE sale_id='E0016' )
AND sale_id<>'E0016'/*<>是不等于*/
--或者
SELECT sale_id AS 业务员编号, order_no AS 订单编号,
total_amt AS 订单金额
FROM sales
WHERE total_amt >
(
SELECT min(total_amt)
FROM sales B
WHERE sale_id='E0016' )
AND sale_id<>'E0016'
3、找出公司女业务员所接的订单
SELECT *
FROM sales
WHERE sale_id in
(
SELECT emp_no
FROM employee
WHERE sex = '女' AND dept = '业务'
)
4、找出目前业绩未超过300元的员工编号和姓名
SELECT emp_no AS 员工编号, emp_name AS 姓名
FROM employee
WHERE emp_no not IN
(
SELECT sale_id
FROM sales
GROUP BY sale_id
HAVING SUM(total_amt) >= 300
)
5、在销售主表sales中查询销售业绩最高的业务员编号及销售业绩
SELECT sale_id AS 业务员编号, SUM(sales.total_amt) AS 销售业绩
FROM sales
GROUP BY sales.sale_id
HAVING SUM(sales.total_amt) >= ALL(
SELECT SUM(total_amt)
FROM sales
GROUP BY sales.sale_id
)
6、找出目前业绩超过300元的员工编号和姓名
SELECT emp_no AS 员工编号, emp_name AS 姓名
FROM employee
WHERE emp_no IN /*或者将emp_no IN换为exists*/
(
SELECT sale_id
FROM sales
GROUP BY sale_id
HAVING SUM(total_amt) > 300
)
7、查询订购的产品至少包含了订单102893中所订购产品的订单
SELECT DISTINCT A.order_no AS 订单编号
FROM sale_item A
WHERE a.order_no!='102893' AND NOT EXISTS
(
SELECT *
FROM sale_item B
WHERE B.order_no = '102893' AND NOT EXISTS (
SELECT *
FROM sale_item C
WHERE A.order_no=C.order_no and B.book_no=C.book_no))
8、查询未承接业务的员工的信息
SELECT *
FROM employee
WHERE NOT EXISTS
(
SELECT sale_id
FROM sales
WHERE emp_no = sale_id
)
--或者
SELECT *
FROM employee
WHERE emp_no not in
(
SELECT sale_id
FROM sales
)
9、查找未被售出的图书编号和书名
SELECT book_no AS 图书编号, book_name AS 书名
FROM books
WHERE book_no not in
(
SELECT book_no
FROM sale_item)
10、找出既购买了“20652”又购买了“22082”的顾客名称
select cust_name as 顾客姓名
from sales
where order_no=any(
select order_no
from sale_item
where book_no='20652'intersect
select order_no
from sale_item
where book_no='22082')
--或者
select distinct cust_name as 顾客姓名
from sale_item a,sales b
where book_No='20652'
and a.order_no=b.order_no
and cust_name in
(select cust_name
from sale_item a,sales b
where book_no='22082'
and a.order_no=b.order_no)
11、统计收件人为“赵毅”的用户所购买的图书总金额,订单笔数
SELECT SUM(total_amt) AS 购书图书总金额, COUNT(order_no) AS 订单数
FROM sales
WHERE sales.cust_name IN
(
SELECT cust_name
FROM customer
WHERE customer.receiver = '赵毅'
)
GROUP BY cust_name
12、查找收件人为“赵毅”的用户所购买的图书信息(书名和书号)
select book_name,book_no
from books
where book_no=any(
select book_no
from sale_item
where order_no=any(
select order_no
from sales
where cust_name =any
(select cust_name
from customer
where receiver like '赵毅')
)
)
13.查找员工“方美美”所承接的订单明细
SELECT *
FROM sale_item
WHERE order_no IN
(
SELECT order_no
FROM sales
WHERE sale_id =
(
SELECT emp_no
FROM employee
WHERE emp_name = '方美美'
)
)
14、统计员工“方美美”所承接的订单笔数,总销售业绩
SELECT COUNT(*) AS 订单笔数, SUM(total_amt) AS 总销售业绩
FROM sales
WHERE sale_id =
(
SELECT emp_no
FROM employee
WHERE emp_name = '方美美')
GROUP BY sale_id
--或者
select count(order_no) 订单笔数,sum(total_amt) 总销售业绩
from sales
where sale_id=any(
select emp_no
from employee
where emp_name='方美美'
)
15、查找购买了“数据库系统概论”的客户信息(客户名称,收件人,电话)
select cust_name,receiver,tel_no
from customer
where cust_name=any(
select cust_name
from sales
where order_no=any(
select order_no
from sale_item
where book_no=any(
select book_no
from books
where book_name='数据库系统概论'))
)
16、统计购买了“数据库系统概论”的订单笔数,总销售量,总金额
SELECT COUNT(*) AS 订单笔数, SUM(qty) AS 总销售量,SUM(qty*unit_price) AS 总金额
FROM sale_item
WHERE book_no =
(
SELECT book_no
FROM books
WHERE book_name = '数据库系统概论'
)