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 = '数据库系统概论'
)

select top 1 1 from...主要是用来判断表是否有记录

查询学生上课人数超过 “Eastern Heretic” 的任意一门课的学生人数的课程信息，请使用 ANY 操作符实现多行子查询。（Lintcode刷题记录）

Courses表：

Teachers表：

本题涉及到多层的嵌套:

第一层的父查询为在课程表 courses 中查询满足条件的全部课程信息，这个条件由子查询来完成，即为，查询学生上课人数超过 ”Eastern Heretic“ 的任意一门课的学生人数。这一部分的子查询中需要结合 ANY 操作符实现。之后，再将子查询进行拆分，形成第二层的嵌套子查询。

第二层的父查询为在课程表 courses 中根据教师 id 查询学生上课人数， 其子查询为在教师表 teachers 中查找教师名 name 为 “Eastern Heretic” 的教师 id。

条件限制：由于我们最终得到的课程信息中肯定不包含 “Eastern Heretic” 的课程，所以我们要在 WHERE 条件中再设置一项：不为 “Eastern Heretic” 所开的课程 。

结合以上，使用 SQL 中子查询的方式如下：)

SELECT *
FROM `courses`
WHERE `student_count` > ANY (
		SELECT `student_count`
		FROM `courses`
		WHERE `teacher_id` = (
			SELECT `id`
			FROM `teachers`
			WHERE `name` = 'Eastern Heretic'
		)
	)
-- <> 代表不等于，也可以用!= 但是有些数据库会报错，例如SQL2000 —--
	AND `teacher_id` <> (
		SELECT `id`
		FROM `teachers`
		WHERE `name` = 'Eastern Heretic'
	);