SQl数据库第六章

例6-1 分别显示Sales数据库中的员工表employee、商品表goods、销售表sell_order表和部门表department中的所有记录。

SELECT * FROM employee SELECT * FROM goods

SELECT * FROM sell_order SELECT * FROM department

例6-2 显示employee表中全部员工的姓名和年龄，去掉重名。

SELECT DISTINCT employee_name AS 姓名，YEAR(GETDATE())-YEAR(birth_date) AS 年龄 FROM employee

例6-3 对employee表，分别查询公司的员工总数和公司员工的平均收入。 SELECT COUNT(*) AS 总数 FROM employee

SELECT AVG(wages) AS 平均收入 FROM employee

例6-4 对employee表，列出月工资在2000以上的员工记录。 SELECT * FROM employee WHERE wages>2000

例6-5 对employee表，求出男员工的平均工资。

SELECT AVG(wages) as 平均工资 FROM employee WHERE sex='男‘

例6-6 对employee表，列出市场部和销售部的员工名单。 SELECT d.department_name, e.employee_name

FROM employee e INNER JOIN department d ON e.department_id = d.department_id WHERE d.department_name IN ('市场部', '销售部') 语句中的WHERE子句还有等价的形式：

WHERE (d.department_name = '市场部') OR (d.department_name = '销售部')

例6-7 对employee表，列出月工资在2000到3000之间的员工名单。 SELECT * FROM employee WHERE wages BETWEEN 2000 AND 3000 语句中的WHERE子句还有等价的形式： WHERE wages>=2000 AND wages<=3000

例6-8 对employee表，列出所有的姓“张”的员工名单。

SELECT * FROM employee WHERE employee_name LIKE '张%' 语句中的WHERE子句还有等价的形式： WHERE LEFT(employee_name,1)= '张‘

例6-9 对employee表，列出所有工资为空值的员工编号和姓名。

SELECT employee_id,employee_name FROM employee WHERE wages IS NULL

例6-10 对employee表，按性别顺序列出员工的编号、姓名、性别、部门编号及工资，性别相同的再先按部门后按工资由高到低排序。

SELECT employee_id,employee_name,sex,department_id,wages FROM employee ORDER BY sex,department_id,wages DESC

例6－11 对部门表department 和员工表employee，查询出市场部所有员工的信息，并将结果存入testtable表中

SELECT employee.* INTO testable FROM employee INNER JOIN department ON employee.department_id=department.department_id

WHERE department.department_name=’市场部’

例6-12 对employee表，列出部门编号为“D001”或“D002”的所有员工姓名。

SELECT employee_name,department_id FROM employee WHERE department_id='D001' UNION

SELECT employee_name,department_id FROM employee WHERE department_id='D002'

例6-13 对employee表，分别统计男女员工人数。

SELECT sex,COUNT(sex) as 人数 FROM employee GROUP BY sex

例6-14 对employee表，分别统计各部门男女员工的人数。

SELECT department_id, sex,COUNT(*) as 人数 FROM employee GROUP BY department_id,sex

例6-15 对employee表，列出部门平均工资大于2000的部门编号。

SELECT department_id,AVG(wages) AS 平均工资 FROM employee GROUP BY department_id HAVING AVG(wages)>=2000

例6-16 对employee表中部门编号为“D001”的员工工资，按照其部门编号生成汇总行和明细行。

SELECT department_id, wages FROM employee

WHERE department_id = 'D001' ORDER BY department_id COMPUTE sum(wages)

例6-17 对employee表中部门编号为“D001”或“D002”的员工工资，按照其部门编号生成分组汇总行和明细行。

SELECT department_id, wages FROM employee

WHERE department_id = 'D001' OR department_id = 'D002' ORDER BY department_id

COMPUTE sum(wages) BY department_id

例6-18 对Sales数据库，列出“市场部”的所有员工的编号。 SELECT employee_id FROM employee

WHERE department_id=(SELECT department_id FROM department

WHERE department_name='市场部')

例6-19 对Sales数据库，列出D001号部门中工资比D002号部门的员工最低工资高

的员工编号和工资。

SELECT employee_id,wages FROM employee

WHERE department_id='D001' AND wages>ANY

(SELECT wages FROM employee WHERE department_id='D002')

例6-20 对Sales数据库，列出部门编号为“D001”的员工，这些员工的工资比部门为“D002”的员工的最高工资还要高的员工的编号和工资。 SELECT employee_id,wages FROM employee

WHERE department_id='D001' AND wages>ALL

(SELECT wages FROM employee WHERE department_id='D002')

例6-21 对Sales数据库，列出部门为“市场部”或“销售部”的所有员工的编号。 SELECT employee_id FROM employee

WHERE department_id IN

(SELECT department_id FROM department

WHERE department_name='市场部' OR department_name='销售部')

例6-22 对Sales数据库输出所有员工的销售单，要求给出员工编号、姓名、商品编号、商品名和销售数量。

SELECT employee.employee_id, employee.employee_name, goods.goods_id, goods.goods_name, sell_order.order_num

FROM employee,sell_order,goods

WHERE employee.employee_id = sell_order.employee_id and

sell_order.goods_id = goods.goods_id

例6-23 Sales数据库中部门表department和员工表employee的等值连接。

SELECT * FROM department INNER JOIN

employee ON employee.department_id = department.department_id

例6-24 对Sales数据库，列出销售“G00001”产品的员工中，销售数量大于编号为“E001”的员工销售该类产品销售数量的那些员工的编号和销售数量。

SELECT a.employee_id, a.order_num

FROM sell_order a INNER JOIN

sell_order b ON a.order_num > b.order_num AND a.goods_id = b.goods_id

WHERE (b.goods_id = 'G00001') AND (b.employee_id = 'E001')

例6-25 Sales数据库中部门表department和员工表employee的自然连接。 SELECT a.department_name, b.*

FROM department a INNER JOIN

employee b ON b.department_id = a.department_id

例6-26 员工信息表employee左外连接销售信息表sell_order。

SELECT a.employee_id, a.employee_name, b.goods_id, b.order_num, b.send_date

FROM employee a LEFT OUTER JOIN

sell_order b ON a.employee_id = b.employee_id

例6-27 员工信息表employee右外连接销售信息表sell_order。

为了说明方便，先在sell_order表中插入一条销售信息。

INSERT INTO sell_order (order_id1, goods_id, employee_id, customer_id, transporter_id, order_num, discount, order_date, send_date, arrival_date, cost)

VALUES ('S00006', 'G00005', ' ', 'C0006', 'T002', 21, 0.5, GETDATE(), GETDATE(), GETDATE(),100)

SELECT a.employee_id, a.employee_name, b.goods_id, b.order_num, b.send_date, b.order_id1

FROM employee a RIGHT OUTER JOIN sell_order b ON a.employee_id = b.employee_id

例6-28 员工信息表employee全外连接销售信息表sell_order。

SELECT a.employee_id, a.employee_name, b.goods_id, b.order_num, b.send_date, b.order_id1

FROM employee a FULL OUTER JOIN

sell_order b ON a.employee_id = b.employee_id