数据查询sql习题综合一

1.查询当前同时借有"计算方法"和"组合数学"两本书的读者，输出其借书卡号，并按卡号升序排序输出。

SELECT a.cno
FROM borrow a,book b
WHERE a.bno=b.bno
AND b.bname IN('计算方法','组合数学')
GROUP BY a.cno
HAVING COUNT(*)=2
ORDER BY a.cno ;

2.查询借阅信息，要求输出卡号，姓名，借阅书数，书名列表（按书名升序排列，用下划线_分隔），按照卡号升序排序。

SELECT c.cno,name,COUNT(*),GROUP_CONCAT(DISTINCT bname ORDER BY bname SEPARATOR '_')
FROM  card c,book b, borrow r
WHERE c.cno = r.cno AND r.bno = b.bno 
GROUP BY c.cno
ORDER BY c.cno;

3.查询当前借了"计算方法"，但没有借"计算方法习题集"的读者，输出其借书卡号，并按卡号降序排序输出。

SELECT cno
FROM book b,borrow r
WHERE b.bno =r.bno  AND bname = '计算方法' AND cno NOT IN(
    SELECT  cno
    FROM book b,borrow r
    WHERE b.bno =r.bno AND bname = '计算方法习题集'
)
ORDER BY cno DESC;

4.统计借阅次数前三的信息，输出书号、书名及借阅次数。

SELECT b.bno,bname,COUNT(*)
FROM book b,borrow r
WHERE b.bno =r.bno  
GROUP BY b.bno
ORDER BY COUNT(*) DESC
LIMIT 3;

5.查询书名包括"网络"关键词的图书信息，输出书号、书名、作者。

SELECT bno,bname,author
FROM book
WHERE bname LIKE '%网络%';

6.查询所有人都借阅过的图书信息，列出书号，书名，作者。

SELECT b.bno,bname,author
FROM  card c,book b, borrow r
WHERE c.cno = r.cno AND r.bno = b.bno 
GROUP BY b.bno
HAVING COUNT(DISTINCT c.cno) = (
	SELECT COUNT(c.cno)
    FROM card c

);

7.查询现有图书中价格最高的图书（可能不止一种书），输出书名及作者。

SELECT  bname,author
FROM book 
WHERE price = (
  SELECT price FROM book  ORDER BY price DESC LIMIT 1
);

8.查询没有被订购的商品，输出商品id，商品名称，类别id，按照类别排序。

SELECT DISTINCT p.product_id, p.product_name, p.category_id
FROM product p
WHERE product_id NOT IN (
   SELECT product_id
    FROM order_detail
)
ORDER BY p.category_id;

9.查询“王杰”的客户信息，按客户编号排序。

SELECT c.*
FROM customer c 
WHERE sale_employee_id = (
    SELECT employee_id
    FROM employee e
    WHERE employee_name = '王杰'
)  
ORDER BY  customer_id;

10.查询借阅书名为"水浒"书的读者信息，输出姓名及班级。

SELECT name, class
FROM  card c,book b, borrow r
WHERE c.cno = r.cno AND r.bno = b.bno AND bname = '水浒';

11.查询哪些客户从未有订单，输出客户编号和客户名称。

SELECT c.customer_id , customer_name
FROM customer c 
LEFT JOIN  `order` o  ON c.customer_id = o.customer_id
WHERE o.order_id IS NULL;

12.统计各专业的学生人数，要求输出系名，专业名，总人数，按系名排序。

[注意：SQL表名请用小写]

表结构如下：

1、student（学生表）：

sno 学号 CHAR(9)

sname 姓名 CHAR(10)

ssex 性别 CHAR(2)

sage 年龄 SMALLINT

jg 籍贯 VARCHAR(50)

zydh 专业代号 CHAR(4)

xdh 系代号 CHAR(2)

2、zy（专业表）

zydh 专业代号 CHAR(4)

zymc 专业名称 VARCHAR(20)

xz 学制 SMALLINT

3、xb（系表）

xdh 系代号 CHAR(2)

xmc 系名称 VARCHAR(20)

SELECT xmc,zymc,count(*)
FROM student s, zy, xb
WHERE s.zydh = zy.zydh AND s.xdh = xb.xdh
GROUP BY xmc;

13.查询输出网络工程专业学生的学号，姓名和籍贯，并按学号降序输出。

[注意：SQL表名请用小写]

表结构如下：

1、zy（专业表）：

zydh 专业代号 CHAR(4)

zymc 专业名称 VARCHAR(20)

xz 学制 SMALLINT

2、student（学生表）：

sno 学号 CHAR(9)

sname 姓名 CHAR(10)

ssex 性别 CHAR(2)

sage 年龄 SMALLINT

jg 籍贯 VARCHAR(50)

zydh 专业代号 CHAR(4)

SELECT sno,sname,jg
FROM  student s, zy
WHERE s.zydh = zy.zydh AND zymc = '网络工程'
ORDER BY sno DESC;

14.查询人员信息：包括员工和客户。(员工数据在前）

输出“id”、“name”、“type”，若为员工，type为“员工”，若为客户，type为“客户”。

员工表：employee
客户表：customer

SELECT employee_id id,employee_name name ,'员工' type
FROM employee
UNION
SELECT customer_id id,customer_name name,'客户' type
FROM customer

15.查询单价在5000-6000（含5000和6000）之间的商品。输出商品名称(product_name)、价格(price)、商品类别(category_id)，按照价格降序排序。

产品表：product

SELECT product_name,price,category_id
FROM product p
WHERE price BETWEEN 5000 AND 6000
ORDER BY price DESC;

16.查询选修通过门数超过3门，且平均分在70以上的学生的学号，选修门数，平均成绩，并按平均成绩从高到低排序。

成绩表(表名为cj)包括如下列（学号，课程代号，成绩，教师工号）

SELECT xh,COUNT(*),AVG(cj)
FROM cj 
WHERE  cj >= 60
GROUP BY xh
HAVING AVG(cj) > 70 AND  COUNT(*) > 3
ORDER BY AVG(cj) DESC;

17.cj成绩表包括如下列（学号，课程代号，成绩，教师工号），查询无有效成绩的成绩信息，列出学号，课程代号，成绩和教师工号。

SELECT xh,kcdh,cj,gh
FROM cj
WHERE cj IS NULL;

18.查询订单记录，输出订单日期order_date，顾客编号customer_id，订单总额total_money，订单编号order_id，按照日期升序排序。

SELECT order_date,customer_id,total_money,order_id
FROM orders
ORDER BY order_date;

19.表：customer，结构及部分数据如下：查询id为8的销售员工负责的所有客户的基本信息，按照客户编号降序排序。

SELECT c.*
FROM customer c 
WHERE sale_employee_id=8 
ORDER BY  customer_id DESC;

20.表：customer，结构及部分数据如下：查询上海客户的客户名、地址及联系电话。

SELECT customer_name,address,phone
FROM customer c
WHERE city LIKE '上海';