第七章 select查询

SELECT 姓名+' '+称呼,DATEDIFF(yy,出生日期,GETDATE()) FROM 员工; /* 文件名称：CH7_Demo002.sql */ USE 北风贸易; GO

SELECT $IDENTITY, 姓名FROM 员工; SELECT 员工编号, 姓名FROM 员工;

/* 文件名称：CH7_Demo003.sql */

SELECT $ROWGUID,Title FROM AdventureWorks.HumanResources.Employee; /* 文件名称：CH7_Demo004.sql */

SELECT 身份证号码,姓名,电话号码FROM 北风贸易.dbo.飞狐工作室; /* 文件名称：CH7_Demo005.sql */ USE 北风贸易; GO

SELECT 作者代号= au_id,

姓名= au_fname+au_lname, 电话号码= phone, 住址= address FROM authors;

/* 文件名称：CH7_Demo006.sql */ USE 北风贸易; GO

SELECT au_id AS 作者代号,

au_fname+au_lname AS 姓名, phone AS 电话号码, address AS 住址 FROM authors;

/* 文件名称：CH7_Demo007.sql */ USE 北风贸易; GO

SELECT au_fname+au_lname AS \"Name of Author\", phone FROM authors ORDER BY \"Name of Author\"; /* 文件名称：CH7_Demo008.sql */ USE 北风贸易; GO

SELECT 姓名, DateDiff(yy,出生日期,GETDATE()) AS 年龄 FROM 飞狐工作室ORDER BY 年龄DESC;

SELECT 姓名, DateDiff(yy,出生日期,GETDATE()) AS 年龄 FROM 飞狐工作室ORDER BY 2 DESC;

SELECT DateDiff(yy,出生日期,GETDATE()) AS 年龄, COUNT(*) AS 人数 FROM 飞狐工作室

GROUP BY DateDiff(yy,出生日期,GETDATE()) HAVING DateDiff(yy,出生日期,GETDATE()) > 21

ORDER BY 年龄DESC;

/* 文件名称：CH7_Demo009.sql */ USE 北风贸易; GO

SELECT DISTINCT 雇用日期FROM 飞狐工作室; /* 文件名称：CH7_Demo010.sql */ USE 北风贸易; GO

SELECT * INTO MyTmpTable FROM 飞狐工作室; /* 文件名称：CH7_Demo011.sql */ USE 北风贸易; GO

SELECT * INTO AdventureWorks.dbo.MyTmpTable FROM 飞狐工作室; /* 文件名称：CH7_Demo012.sql */ USE 北风贸易; GO

SELECT 姓名,目前薪水FROM 飞狐工作室

WHERE 目前薪水> 6000; /* 文件名称：CH7_Demo013.sql */ USE 北风贸易; GO

SELECT 姓名,部门FROM 飞狐工作室

WHERE 部门IN ('信息部','营销部','业务部'); /* 文件名称：CH7_Demo014.sql */ USE 北风贸易; GO

SELECT 姓名,部门FROM 飞狐工作室

WHERE 部门NOT IN ('信息部','营销部','业务部'); /* 文件名称：CH7_Demo015.sql */ USE 北风贸易; GO

SELECT 姓名,目前薪水FROM 飞狐工作室

WHERE 目前薪水>= 3000 AND 目前薪水<= 3500;

/* 文件名称：CH7_Demo016.sql */ USE 北风贸易; GO

SELECT 姓名,目前薪水FROM 飞狐工作室

WHERE 目前薪水BETWEEN 3000 AND 3500; /* 文件名称：CH7_Demo017.sql */ USE 北风贸易; GO

SELECT 姓名,目前薪水FROM 飞狐工作室

WHERE 目前薪水< 3000 OR 目前薪水> 3500; /* 文件名称：CH7_Demo018.sql */ USE 北风贸易; GO

SELECT 姓名,目前薪水FROM 飞狐工作室

WHERE 目前薪水NOT BETWEEN 3000 AND 3500; /* 文件名称：CH7_Demo019.sql */ USE 北风贸易; GO

SELECT 姓名,出生日期FROM 飞狐工作室

WHERE MONTH(出生日期) = MONTH(GETDATE()); /* 文件名称：CH7_Demo020.sql */ USE 北风贸易; GO

SELECT 姓名, DATEDIFF(yy,出生日期,GETDATE()) AS 年龄

FROM 飞狐工作室;

/* 文件名称：CH7_Demo021.sql */ USE 北风贸易; GO

SELECT 姓名, DATEDIFF(yy,出生日期,GETDATE()) AS 年龄 FROM 飞狐工作室

WHERE DATEDIFF(yy,出生日期,GETDATE()) BETWEEN 30 AND 40; /* 文件名称：CH7_Demo022.sql */ USE 北风贸易; GO

SELECT * FROM 章立民研究室

WHERE 出生日期BETWEEN '01/01/1980' AND '12/31/1981'; /* 文件名称：CH7_Demo023.sql */

USE 北风贸易; GO

SELECT 姓名, 目前薪水FROM 飞狐工作室 WHERE 目前薪水BETWEEN 3000 AND 3500 OR 目前薪水BETWEEN 4000 AND 4500 OR

目前薪水BETWEEN 5000 AND 5500; /* 文件名称：CH7_Demo024.sql */ USE 北风贸易; GO

SELECT 姓名, 出生日期FROM 飞狐工作室

WHERE 出生日期BETWEEN '12/01/1980' AND '12/31/1980' OR

出生日期BETWEEN '11/01/1982' AND '11/30/1982'; /* 文件名称：CH7_Demo025.sql */ USE 北风贸易; GO

SELECT 姓名, 目前薪水FROM 飞狐工作室 WHERE 目前薪水< 1100 OR

目前薪水BETWEEN 1900 AND 2000 OR 目前薪水BETWEEN 2900 AND 3000 OR 目前薪水BETWEEN 3900 AND 4000 OR

目前薪水> 5500; /* 文件名称：CH7_Demo026.sql */ USE 北风贸易; GO

SELECT 姓名, DateDiff(yy,出生日期,GETDATE()) AS 年龄 FROM 飞狐工作室

WHERE DateDiff(yy,出生日期,GETDATE()) < 20 OR

DateDiff(yy,出生日期,GETDATE()) BETWEEN 25 AND 30 OR DateDiff(yy,出生日期,GETDATE()) BETWEEN 35 AND 40 OR DateDiff(yy,出生日期,GETDATE()) BETWEEN 45 AND 50 OR

DateDiff(yy,出生日期,GETDATE()) > 60; /* 文件名称：CH7_Demo027.sql */ USE 北风贸易; GO

SELECT 姓名, 出生日期FROM 飞狐工作室

WHERE 出生日期BETWEEN '01/01/1940' AND '06/30/1942' OR 出生日期BETWEEN '01/01/1961' AND '12/31/1961' OR

出生日期>= '07/01/1982'; /* 文件名称：CH7_Demo028.sql */

USE 北风贸易; GO

SELECT 姓名, 出生日期, 自传, 相片FROM 飞狐工作室

WHERE 自传IS NOT NULL AND 相片IS NOT NULL; /* 文件名称：CH7_Demo029.sql */ USE 北风贸易; GO

SELECT 姓名, 出生日期, 自传FROM 飞狐工作室

WHERE 自传IS NULL;

/* 文件名称：CH7_Demo030.sql */ USE 北风贸易; GO

SELECT *

FROM 客户INNER JOIN 订货主档

ON 客户.客户编号= 订货主档.客户编号

WHERE 订货主档.订单日期BETWEEN '08/01/1996' AND '08/31/1996'; /* 文件名称：CH7_Demo031.sql */ USE 北风贸易; GO

SELECT 客户.公司名称,订货主档.* FROM 客户INNER JOIN 订货主档

ON 客户.客户编号= 订货主档.客户编号

WHERE 订货主档.订单日期BETWEEN '08/01/1996' AND '08/31/1996'; /* 文件名称：CH7_Demo032.sql */ USE 北风贸易; GO

SELECT 姓名FROM 飞狐工作室WHERE 姓名LIKE '%光%'; /* 文件名称：CH7_Demo033.sql */ USE 北风贸易; GO

SELECT 姓名,家庭住址,电话号码FROM 飞狐工作室

WHERE 家庭住址LIKE '%北京市%'; /* 文件名称：CH7_Demo034.sql */ USE 北风贸易; GO

SELECT 姓名,电话号码FROM 飞狐工作室WHERE 姓名LIKE '张%'; /* 文件名称：CH7_Demo035.sql */

USE 北风贸易; GO

SELECT 姓名,家庭住址FROM 飞狐工作室

WHERE 家庭住址LIKE '%上海市%' AND 家庭住址LIKE '%中正路%'; /* 文件名称：CH7_Demo036.sql */ USE 北风贸易; GO

SELECT 姓名, 自传FROM 飞狐工作室 WHERE 自传LIKE '%章立民%' AND 自传LIKE '%微软%' AND 自传LIKE '%MVP%' AND

相片IS NOT NULL; /* 文件名称：CH7_Demo037.sql */ USE 北风贸易; GO

SELECT 姓名FROM 飞狐工作室WHERE 姓名LIKE '__文%'; /* 文件名称：CH7_Demo038.sql */ USE 北风贸易; GO

SELECT 姓名FROM 飞狐工作室WHERE 姓名LIKE '__文'; /* 文件名称：CH7_Demo039.sql */ USE 北风贸易; GO

SELECT 姓名, 家庭住址FROM 飞狐工作室 WHERE (家庭住址LIKE '%北京市%') AND

(家庭住址LIKE '%10楼%' OR 家庭住址LIKE '%10_楼%'); /* 文件名称：CH7_Demo040.sql */ USE 北风贸易; GO

SELECT au_fname,au_lname FROM authors

WHERE au_lname LIKE '[P-Z]inger'; /* 文件名称：CH7_Demo041.sql */ USE 北风贸易; GO

SELECT 姓名FROM 飞狐工作室

WHERE 姓名LIKE '[ACD张李]%' ORDER BY 姓名; /* 文件名称：CH7_Demo042.sql */

USE 北风贸易; GO

SELECT 身份证号码,姓名FROM 飞狐工作室

WHERE 身份证号码LIKE '[1-5]%'; /* 文件名称：CH7_Demo043.sql */ USE 北风贸易; GO

SELECT 身份证号码,姓名FROM 飞狐工作室

WHERE 身份证号码LIKE '[15]%'; /* 文件名称：CH7_Demo044.sql */ USE 北风贸易; GO

SELECT 姓名FROM 飞狐工作室

WHERE 姓名LIKE '张[素俊]%'; /* 文件名称：CH7_Demo045.sql */ USE 北风贸易; GO

SELECT 姓名FROM 飞狐工作室

WHERE 姓名LIKE '[a-z]%'; /* 文件名称：CH7_Demo046.sql */ USE 北风贸易; GO

SELECT 姓名FROM 飞狐工作室

WHERE 姓名NOT LIKE '%[a-z]%' AND

姓名NOT LIKE '%[0-9]%'; /* 文件名称：CH7_Demo047.sql */ USE 北风贸易; GO

SELECT au_fname,au_lname FROM authors

WHERE au_fname LIKE '[^H-K]ichel'; /* 文件名称：CH7_Demo048.sql */ USE 北风贸易; GO

SELECT 姓名FROM 飞狐工作室

WHERE 姓名LIKE '[^ACD杜陈高]%'; /* 文件名称：CH7_Demo049.sql */ USE 北风贸易;

GO

SELECT 身份证号码,姓名FROM 飞狐工作室

WHERE 身份证号码LIKE '[^5-8]%'; /* 文件名称：CH7_Demo050.sql */ USE 北风贸易; GO

SELECT 身份证号码,姓名FROM 飞狐工作室

WHERE 身份证号码LIKE '[^135]%'; /* 文件名称：CH7_Demo051.sql */ USE 北风贸易; GO

SELECT 姓名FROM 飞狐工作室

WHERE 姓名LIKE '张[^启素俊]%'; /* 文件名称：CH7_Demo052.sql */ USE 北风贸易; GO

SELECT 姓名,家庭住址FROM 飞狐工作室

WHERE 家庭住址LIKE '%\\_%' ESCAPE '\\'; /* 文件名称：CH7_Demo053.sql */ USE 北风贸易; GO

SELECT 姓名,家庭住址FROM 飞狐工作室

WHERE 家庭住址LIKE '%\\%%' ESCAPE '\\'; /* 文件名称：CH7_Demo054.sql */ USE 北风贸易; GO

SELECT * FROM 飞狐工作室

WHERE 家庭住址LIKE '%-%--%' ESCAPE '-'; /* 文件名称：CH7_Demo055.sql */ USE 北风贸易; GO

SELECT 客户.客户编号,客户.公司名称,客户.联系人,客户.电话, 订货主档.订单号码,订货主档.订单日期,订货主档.要货日期, 订货主档.送货日期,订货主档.送货方式,订货主档.运费, 订货主档.收货人,订货主档.送货地址 FROM 客户INNER JOIN 订货主档

ON 客户.客户编号= 订货主档.客户编号;

/* 文件名称：CH7_Demo056.sql */ USE 北风贸易; GO

SELECT 客户.客户编号,客户.公司名称,订货主档.订单号码, 订货主档.订单日期,订货明细.产品编号,订货明细.单价, 订货明细.数量, 订货明细.折扣

FROM 客户INNER JOIN 订货主档ON 客户.客户编号= 订货主档.客户编号

INNER JOIN 订货明细ON 订货主档.订单号码= 订货明细.订单号码; /* 文件名称：CH7_Demo057.sql */ USE 北风贸易; GO

SELECT 客户.客户编号, 客户.公司名称, 客户.联系人, 订货主档.订单号码, 订货主档.收货人

FROM 客户LEFT OUTER JOIN 订货主档 ON 客户.客户编号= 订货主档.客户编号

ORDER BY 4 DESC; /* 文件名称：CH7_Demo058.sql */ USE 北风贸易; GO

SELECT 订货明细.订单号码, 订货明细.单价, 订货明细.数量, 订货明细.折扣, 产品数据.产品编号, 产品数据.产品

FROM 订货明细RIGHT OUTER JOIN 产品数据 ON 订货明细.产品编号= 产品数据.产品编号 ORDER BY 5;

/* 文件名称：CH7_Demo059.sql */ USE 北风贸易; GO

SELECT 客户.客户编号, 客户.公司名称, 客户.联系人, 客户.电话,

订货主档.订单号码, 订货主档.订单日期,

订货主档.要货日期, 订货主档.送货日期, 订货主档.送货方式, 订货主档.运费, 订货主档.收货人, 订货主档.送货地址

FROM 客户INNER JOIN 订货主档

ON 客户.客户编号= 订货主档.客户编号; /* 文件名称：CH7_Demo060.sql */ USE 北风贸易; GO

SELECT a.客户编号, a.公司名称, a.联系人, a.电话, b.订单号码, b.订单日期, b.要货日期, b.送货日期, b.送货方式, b.运费, b.收货人, b.送货地址

FROM 客户a INNER JOIN 订货主档b

ON a.客户编号= b.客户编号; /* 文件名称：CH7_Demo061.sql */ USE 北风贸易; GO

SELECT a.客户编号, a.公司名称, a.地址, a.联系人, a.电话, b.订单号码,b.订单日期, b.运费, b.收货人, c.产品编号, c.单价, c.数量, c.折扣

FROM 客户a INNER JOIN 订货主档b ON a.客户编号= b.客户编号 INNER JOIN 订货明细c ON b.订单号码= c.订单号码 WHERE a.地址LIKE '%北京市%' AND

b.订单日期BETWEEN '07/01/1996' AND '07/31/1996'; /* 文件名称：CH7_Demo062.sql */ USE 北风贸易; GO

SELECT a.公司名称,b.订单日期,d.*

FROM 客户a INNER JOIN 订货主档b ON a.客户编号= b.客户编号 INNER JOIN 订货明细c ON b.订单号码= c.订单号码 INNER JOIN 产品数据d ON c.产品编号= d.产品编号

WHERE YEAR(b.订单日期) = 1996; /* 文件名称：CH7_Demo063.sql */ USE 北风贸易; GO

SELECT a.公司名称,b.订单号码,b.订单日期,c.*

FROM 客户a INNER JOIN 订货主档b ON a.客户编号= b.客户编号 INNER JOIN 员工c ON b.员工编号= c.员工编号

WHERE a.公司名称= '正人资源' AND YEAR(b.订单日期) = 1998; /* 文件名称：CH7_Demo0.sql */ USE 北风贸易; GO

SELECT COUNT(*) AS 人数FROM 飞狐工作室; /* 文件名称：CH7_Demo065.sql */ USE 北风贸易; GO

SELECT COUNT(*) AS 人数FROM 飞狐工作室

WHERE 家庭住址LIKE '%北京市%'; /* 文件名称：CH7_Demo066.sql */ USE 北风贸易; GO

SELECT COUNT(*) AS 人数FROM 飞狐工作室

WHERE DATEDIFF(yy,出生日期,GETDATE()) BETWEEN 30 AND 40; /* 文件名称：CH7_Demo067.sql */ USE 北风贸易; GO

SELECT COUNT(*) AS 客户数目

FROM 客户a INNER JOIN 订货主档b ON a.客户编号= b.客户编号 WHERE a.地址LIKE '%北京市%' AND

b.订单日期BETWEEN '07/01/1996' AND '07/31/1996'; /* 文件名称：CH7_Demo068.sql */ USE 北风贸易; GO

SELECT MIN(目前薪水) AS 最低薪水FROM 飞狐工作室; /* 文件名称：CH7_Demo069.sql */

USE 北风贸易; GO

SELECT YEAR(GETDATE()) - YEAR(MIN(出生日期)) AS 最高年龄 FROM 飞狐工作室;

SELECT DATEDIFF(yyyy,MIN(出生日期),GETDATE()) AS 最高年龄 FROM 飞狐工作室;

SELECT MAX(DATEDIFF(yyyy,出生日期,GETDATE())) AS 最高年龄 FROM 飞狐工作室;

/* 文件名称：CH7_Demo070.sql */ USE 北风贸易; GO

SELECT MAX(目前薪水) AS 最高薪水FROM 飞狐工作室; /* 文件名称：CH7_Demo071.sql */ USE 北风贸易; GO

SELECT YEAR(GETDATE()) - YEAR(MAX(出生日期)) AS 最小年龄 FROM 飞狐工作室;

SELECT DATEDIFF(yyyy,MAX(出生日期),GETDATE()) AS 最小年龄 FROM 飞狐工作室;

SELECT MIN(DATEDIFF(yyyy,出生日期,GETDATE())) AS 最小年龄

FROM 飞狐工作室;

/* 文件名称：CH7_Demo072.sql */ USE 北风贸易; GO

SELECT AVG(目前薪水) AS 平均薪水FROM 飞狐工作室; /* 文件名称：CH7_Demo073.sql */ USE 北风贸易; GO

SELECT AVG(DATEDIFF(yyyy,出生日期,GETDATE())) AS 平均年龄

FROM 飞狐工作室;

/* 文件名称：CH7_Demo074.sql */ USE 北风贸易; GO

SELECT SUM(订货明细.单价*订货明细.数量*(1-订货明细.折扣)) AS 客户采买总金额

FROM 订货明细;

/* 文件名称：CH7_Demo075.sql */ USE 北风贸易;

GO

DECLARE @AverageSalary money;

-- 将计算所得的平均薪水储存到变量@AverageSalary 中 SELECT @AverageSalary = AVG(目前薪水) FROM 飞狐工作室;

PRINT '薪水大于平均薪水'+STR(@AverageSalary,10,4)+ ' 的员工如下:'; PRINT REPLICATE('-',35);

SELECT 姓名,目前薪水FROM 飞狐工作室 WHERE 目前薪水> @AverageSalary; /* 文件名称：CH7_Demo076.sql */ USE 北风贸易; GO

SELECT MAX(目前薪水) AS 最高薪水, MIN(目前薪水) AS 最低薪水, AVG(目前薪水) AS 平均薪水, SUM(目前薪水) AS 薪水总额 FROM 飞狐工作室;

/* 文件名称：CH7_Demo077.sql */ USE 北风贸易; GO

SELECT COUNT(DISTINCT 客户编号) AS 客户数目 FROM 订货主档

WHERE 订单日期BETWEEN '08/01/1996' AND '08/31/1996'; /* 文件名称：CH7_Demo078.sql */ USE 北风贸易; GO

SELECT 部门,

COUNT(*) AS 部门员工人数, MAX(目前薪水) AS 部门最高薪水, MIN(目前薪水) AS 部门最低薪水, AVG(目前薪水) AS 部门平均薪水 FROM 飞狐工作室GROUP BY 部门; /* 文件名称：CH7_Demo079.sql */ USE 北风贸易; GO

SELECT a.公司名称,

COUNT(DISTINCT b.订单号码) AS 采购次数,

SUM(c.单价* c.数量* (1 - c.折扣)) AS 采购总金额 FROM 客户a

INNER JOIN 订货主档b ON a.客户编号= b.客户编号 INNER JOIN 订货明细c ON b.订单号码= c.订单号码 GROUP BY a.公司名称;

/* 文件名称：CH7_Demo080.sql */ USE 北风贸易; GO

SELECT a.员工编号, a.姓名, SUM(c.数量) AS 销售总数 FROM 员工a

INNER JOIN 订货主档b ON a.员工编号= b.员工编号 INNER JOIN 订货明细c ON b.订单号码= c.订单号码 GROUP BY a.员工编号, a.姓名; /* 文件名称：CH7_Demo081.sql */ USE 北风贸易; GO

SELECT a.员工编号,a.姓名,SUM(c.数量) AS 销售总数 INTO aMount

FROM 员工a INNER JOIN 订货主档b ON a.员工编号= b.员工编号 INNER JOIN 订货明细c ON b.订单号码= c.订单号码 GROUP BY a.员工编号, a.姓名;

SELECT a.员工编号, a.姓名, a.销售总数,

COUNT(b.员工编号) AS 业绩比此人好的业务员的人数, AVG(b.销售总数) AS 业绩比此人好的业务员的平均销售总数 FROM aMount a, aMount b WHERE a.销售总数< b.销售总数

GROUP BY a.员工编号, a.姓名, a.销售总数;

DROP TABLE aMount;

/* 文件名称：CH7_Demo082.sql */ USE 北风贸易; GO

SELECT 部门,

性别= CASE 员工性别 WHEN 0 THEN '女' WHEN 1 THEN '男' END,

婚姻状况= CASE 婚姻状况

WHEN 0 THEN '未婚'

WHEN 1 THEN '已婚' END,

最低薪水= MIN(目前薪水), 最高薪水= MAX(目前薪水), 平均薪水= AVG(目前薪水) FROM 飞狐工作室 GROUP BY 部门, CASE 员工性别

WHEN 0 THEN '女' WHEN 1 THEN '男' END,

CASE 婚姻状况

WHEN 0 THEN '未婚' WHEN 1 THEN '已婚' END

ORDER BY 部门;

/* 文件名称：CH7_Demo083.sql */ USE 北风贸易; GO

SELECT 部门,AVG(目前薪水) AS 部门平均薪水 FROM 飞狐工作室

WHERE 家庭住址LIKE '%长春市%'

GROUP BY 部门;

/* 文件名称：CH7_Demo084.sql */ USE 北风贸易; GO

SELECT 部门,AVG(目前薪水) AS 部门平均薪水 FROM 飞狐工作室

WHERE 家庭住址LIKE '%长春市%'

GROUP BY ALL 部门; /* 文件名称：CH7_Demo085.sql */ USE 北风贸易; GO

SELECT 部门,

AVG(目前薪水) AS 部门的平均薪水, COUNT(*) AS 各部门的人数, MAX(目前薪水) AS 部门中的最高薪, MIN(目前薪水) AS 部门中的最低薪 FROM 飞狐工作室 GROUP BY 部门

HAVING AVG(目前薪水) > 3000;

/* 文件名称：CH7_Demo086.sql */ USE 北风贸易; GO

SELECT 姓名AS 重复的姓名, COUNT(*) AS 重复的人数 FROM 飞狐工作室

GROUP BY 姓名

Having COUNT(*) > 1; /* 文件名称：CH7_Demo087.sql */ USE 北风贸易; GO

SELECT a.公司名称,

COUNT(DISTINCT b.订单号码) AS 订单张数,

SUM(c.单价* c.数量* (1 - c.折扣)) AS 采购总金额 FROM 客户a

INNER JOIN 订货主档b INNER JOIN 订货明细c ON b.订单号码= c.订单号码 ON a.客户编号= b.客户编号 GROUP BY a.公司名称

HAVING COUNT(DISTINCT b.订单号码) > 10; /* 文件名称：CH7_Demo088.sql */ USE 北风贸易; GO

SELECT 姓名,

性别= CASE 员工性别 WHEN 0 THEN '女' WHEN 1 THEN '男' END, 目前薪水 FROM 飞狐工作室

ORDER BY 目前薪水DESC; /* 文件名称：CH7_Demo0.sql */ USE 北风贸易; GO

SELECT 部门, AVG(目前薪水) AS 平均薪水 FROM 飞狐工作室 GROUP BY 部门

ORDER BY 2;

/* 文件名称：CH7_Demo090.sql */ USE 北风贸易;

GO

SELECT 部门, 姓名,

性别= CASE 员工性别 WHEN 0 THEN '女' WHEN 1 THEN '男' END, 目前薪水 FROM 飞狐工作室

ORDER BY 部门,性别DESC,目前薪水DESC; /* 文件名称：CH7_Demo091.sql */ USE 北风贸易; GO

SELECT a.公司名称,

COUNT(DISTINCT b.订单号码) AS 采购次数,

SUM(c.单价* c.数量* (1 - c.折扣)) AS 采购总金额 FROM 客户a

INNER JOIN 订货主档b INNER JOIN 订货明细c ON b.订单号码= c.订单号码 ON a.客户编号= b.客户编号 GROUP BY a.公司名称 ORDER BY 2 DESC, 3;

/* 文件名称：CH7_Demo092.sql */ USE 北风贸易; GO

SELECT TOP (1)

姓名AS 薪水最低的员工姓名, 目前薪水 FROM 飞狐工作室

ORDER BY 目前薪水;

/* 文件名称：CH7_Demo093.sql */ USE 北风贸易; GO

SELECT TOP (1)

姓名AS 薪水最高的员工姓名, 目前薪水 FROM 飞狐工作室

ORDER BY 目前薪水DESC; /* 文件名称：CH7_Demo094.sql */

USE 北风贸易; GO

SELECT TOP (10)

姓名AS 薪水最高前十名的员工姓名, 目前薪水 FROM 飞狐工作室

ORDER BY 目前薪水DESC; /* 文件名称：CH7_Demo095.sql */ USE 北风贸易; GO

SELECT TOP (10)

姓名AS 薪水倒数十名的员工姓名, 目前薪水 FROM 飞狐工作室

ORDER BY 目前薪水;

/* 文件名称：CH7_Demo096.sql */ USE 北风贸易; GO

SELECT TOP (3)

部门AS 平均薪水最高的前三个部门, AVG(目前薪水) AS 平均薪水 FROM 飞狐工作室 GROUP BY 部门

ORDER BY 2 DESC;

/* 文件名称：CH7_Demo097.sql */ USE 北风贸易; GO

SELECT TOP (10) PERCENT 姓名, 目前薪水 FROM 飞狐工作室

ORDER BY 目前薪水DESC; /* 文件名称：CH7_Demo098.sql */ USE 北风贸易; GO

SELECT TOP (3)

a.员工编号, a.姓名AS 业务员姓名,

SUM(c.单价* c.数量*(1 - c.折扣)) AS 销售总金额 FROM 员工a

INNER JOIN 订货主档b ON a.员工编号= b.员工编号 INNER JOIN 订货明细c ON b.订单号码= c.订单号码 GROUP BY a.员工编号,a.姓名

ORDER BY 3 DESC; /* 文件名称：CH7_Demo099.sql */ USE 北风贸易; GO

SELECT TOP (1) a.公司名称,

COUNT(DISTINCT b.订单号码) AS 采购次数,

SUM(c.单价* c.数量* (1 - c.折扣)) AS 采购总金额 FROM 客户a

INNER JOIN 订货主档b ON a.客户编号= b.客户编号 INNER JOIN 订货明细c ON b.订单号码= c.订单号码 WHERE YEAR(b.订单日期) = 1996 GROUP BY a.公司名称

ORDER BY 3 DESC;

/* 文件名称：CH7_Demo100.sql */ USE 北风贸易; GO

DECLARE @MyTop AS int; SET @MyTop = 10;

SELECT TOP (@MyTop + 5) * FROM 章立民研究室ORDER BY 员工编号DESC;

GO /* 文件名称：CH7_Demo101.sql */ USE 北风贸易; GO SELECT TOP (

SELECT COUNT(*) FROM 飞狐工作室 WHERE 目前薪水> 6000 )

姓名,目前薪水FROM 飞狐工作室ORDER BY 目前薪水DESC; /* 文件名称：CH7_Demo102.sql */ USE 北风贸易; GO

SELECT 订货主档.订单号码,订货主档.订单日期, (SELECT MAX(订货明细.单价) FROM 订货明细

WHERE 订货明细.订单号码= 订货主档.订单号码) AS 最高单价

FROM 订货主档;

/* 文件名称：CH7_Demo103.sql */ USE 北风贸易; GO

SELECT au_lname, au_fname FROM authors WHERE au_id IN

(SELECT au_id FROM titleauthor WHERE title_id IN

(SELECT title_id FROM titles WHERE type = 'popular_comp'));

/* 文件名称：CH7_Demo104.sql */ USE 北风贸易; GO

SELECT a.姓名, a.目前薪水 FROM 飞狐工作室a WHERE a.目前薪水=

(SELECT MIN(b.目前薪水) FROM 飞狐工作室b); /* 文件名称：CH7_Demo105.sql */ USE 北风贸易; GO

SELECT a.姓名, a.目前薪水FROM 飞狐工作室a WHERE a.目前薪水>

(SELECT AVG(b.目前薪水) * 1.50 FROM 飞狐工作室b) ORDER BY a.目前薪水;

/* 文件名称：CH7_Demo106.sql */ USE 北风贸易; GO

SELECT T.title_id, S.stor_id, ST.stor_name, city, state, Revenue=T.price*S.qty FROM titles T

INNER JOIN sales S ON T.title_id=S.title_id INNER JOIN stores ST ON S.stor_id=ST.stor_id WHERE T.price*S.qty <

(SELECT 0.80*AVG(price*qty) FROM titles T2

INNER JOIN sales S2

ON T2.title_id=S2.title_id

WHERE T.title_id=T2.title_id ); /* 文件名称：CH7_Demo107.sql */

USE 北风贸易; GO

SELECT 部门,

COUNT(*) AS 部门人数, AVG(目前薪水) AS 平均薪水 FROM 飞狐工作室 GROUP BY 部门

HAVING COUNT(*) >

(SELECT COUNT(*) FROM 飞狐工作室WHERE 部门= '业务部'); /* 文件名称：CH7_Demo108.sql */ USE 北风贸易; GO SELECT TOP (

SELECT COUNT(*) FROM 飞狐工作室 WHERE 目前薪水> (

SELECT MAX(目前薪水) FROM 飞狐工作室 WHERE 部门= '业务部' ) )

姓名,目前薪水FROM 飞狐工作室ORDER BY 目前薪水DESC; /* 文件名称：CH7_Demo109.sql */ USE 北风贸易; GO

-- 使用内部查询

-- 查询有哪些产品的单价与蕃茄酱相同 SELECT 产品FROM 产品数据 WHERE 单价=

(SELECT 单价FROM 产品数据WHERE 产品= '蕃茄酱');

-- 使用连接表

-- 查询有哪些产品的单价与蕃茄酱相同

SELECT Product1.产品FROM 产品数据AS Product1 INNER JOIN 产品数据AS Product2 ON Product1.单价= Product2.单价

WHERE Product2.产品= '蕃茄酱'; /* 文件名称：CH7_Demo110.sql */ USE 北风贸易; GO

SELECT 客户.客户编号,客户.公司名称FROM 客户 WHERE NOT EXISTS

(SELECT * FROM 订货主档

WHERE 订货主档.客户编号= 客户.客户编号); /* 文件名称：CH7_Demo111.sql */ USE 北风贸易; GO

SELECT ST.stor_id, ST.stor_name, ST.city, ST.state FROM stores ST WHERE NOT EXISTS

(

WHERE NOT EXISTS

(

SELECT * FROM titles T1

SELECT * FROM titles T2

INNER JOIN sales SA ON T2.title_id = SA.title_id

WHERE T2.title_id = T1.title_id AND ST.stor_id = SA.stor_id )

);

/* 文件名称：CH7_Demo112.sql */ USE 北风贸易; GO

SELECT a.公司名称FROM 客户a WHERE a.客户编号NOT IN

(SELECT b.客户编号FROM 订货主档b); /* 文件名称：CH7_Demo113.sql */ USE 北风贸易; GO

SELECT a.公司名称FROM 客户a WHERE a.客户编号IN

(SELECT b.客户编号FROM 订货主档b); /* 文件名称：CH7_Demo114.sql */ USE 北风贸易; GO

SELECT 姓名AS 重复的姓名,电话号码,家庭住址FROM 飞狐工作室 WHERE 姓名IN (

SELECT 姓名FROM 飞狐工作室GROUP BY 姓名Having COUNT(*) > 1 /* 文件名称：CH7_Demo115.sql */ USE 北风贸易;

GO

SELECT a.姓名, a.目前薪水FROM 飞狐工作室a WHERE a.目前薪水> ALL

(SELECT b.目前薪水FROM 飞狐工作室b

WHERE b.部门= '财务部'); /* 文件名称：CH7_Demo116.sql */ USE 北风贸易; GO

SELECT a.姓名, a.目前薪水FROM 飞狐工作室a WHERE a.目前薪水< ALL

(SELECT b.目前薪水FROM 飞狐工作室b

WHERE b.部门= '财务部'); /* 文件名称：CH7_Demo117.sql */ USE 北风贸易; GO

SELECT a.姓名, a.目前薪水FROM 飞狐工作室a WHERE a.目前薪水> ANY

(SELECT b.目前薪水FROM 飞狐工作室b

WHERE b.部门= '财务部'); /* 文件名称：CH7_Demo118.sql */ USE 北风贸易; GO

SELECT * FROM (SELECT * FROM 章立民研究室) AS liming; /* 文件名称：CH7_Demo119.sql */ USE 北风贸易; GO

SELECT C.类别编号, C.类别名称, P.产品, P.单价, CT.类别数目 FROM 产品类别C

INNER JOIN 产品数据P ON C.类别编号= P.类别编号 INNER JOIN (

SELECT C.类别编号, COUNT(*) AS 类别数目FROM 产品类别C INNER JOIN 产品数据P ON C.类别编号= P.类别编号 GROUP BY C.类别编号, C.类别名称 ) AS CT ON C.类别编号= CT.类别编号 ORDER BY C.类别名称;

GO

/* 文件名称：CH7_Demo120.sql */ USE 北风贸易;

GO

SELECT Source1.员工编号, Source1.姓名, Source1.销售总数, COUNT(Source2.员工编号) AS 业绩比此人好的业务员的人数, AVG(Source2.销售总数) AS 业绩比此人好的业务员的平均销售总数 FROM (

SELECT a.员工编号,a.姓名,SUM(c.数量) AS 销售总数

FROM 员工a INNER JOIN 订货主档b ON a.员工编号= b.员工编号 INNER JOIN 订货明细c ON b.订单号码= c.订单号码 GROUP BY a.员工编号,a.姓名 ) AS Source1 INNER JOIN (

SELECT a.员工编号,a.姓名,SUM(c.数量) AS 销售总数

FROM 员工a INNER JOIN 订货主档b ON a.员工编号= b.员工编号 INNER JOIN 订货明细c ON b.订单号码= c.订单号码 GROUP BY a.员工编号,a.姓名 ) AS Source2

ON Source1.销售总数< Source2.销售总数

GROUP BY Source1.员工编号,Source1.姓名,Source1.销售总数 ORDER BY 1;

GO

/* 文件名称：CH7_Demo121.sql */ USE AdventureWorks ; GO

SELECT RTRIM(c.FirstName) + ' ' + LTRIM(c.LastName) AS Name, d.City

FROM Person.Contact c

INNER JOIN HumanResources.Employee e ON c.ContactID = e.ContactID INNER JOIN

(SELECT ea.AddressID, ea.EmployeeID, a.City FROM Person.Address a

INNER JOIN HumanResources.EmployeeAddress ea ON a.AddressID = ea.AddressID) AS d ON e.EmployeeID = d.EmployeeID

ORDER BY c.LastName, c.FirstName; /* 文件名称：CH7_Demo122.sql */ USE 北风贸易; GO

SELECT DISTINCT c1.联系人, c1.地址, c1.城市, c1.行政区

FROM 客户AS c1 INNER JOIN 客户AS c2 ON c1.行政区= c2.行政区

AND c1.联系人<> c2.联系人 ORDER BY c1.行政区, c1.联系人; /* 文件名称：CH7_Demo123.sql */ USE 北风贸易; GO

SELECT DISTINCT a.姓名,a.部门, a.家庭住址 FROM 飞狐工作室a INNER JOIN 飞狐工作室b

ON a.姓名= b.姓名AND a.部门<> b.部门; /* 文件名称：CH7_Demo124.sql */ USE 北风贸易; GO

SELECT DISTINCT pv1.产品编号, pv1.供应商编号

FROM 产品数据pv1 INNER JOIN 产品数据pv2 ON pv1.产品编号= pv2.产品编号 AND pv1.供应商编号<> pv2.供应商编号

ORDER BY pv1.产品编号; /* 文件名称：CH7_Demo125.sql */ USE 北风贸易; GO

SELECT 部门, [男] AS 男性, [女] AS 女性 FROM

(SELECT 员工编号, 性别, 部门FROM 章立民研究室) liming PIVOT (

COUNT(员工编号)

FOR 性别IN ([男], [女]) ) AS pvt

ORDER BY 部门;

/* 文件名称：CH7_Demo136.sql */ USE 北风贸易; GO

SELECT * FROM 成绩_2003 UNION ALL

SELECT * FROM 成绩_2004 UNION ALL

SELECT * FROM 成绩_2005; /* 文件名称：CH7_Demo137.sql */ USE 北风贸易; GO

SELECT 身份证号码AS 学生代号, 语文成绩AS 语文成绩FROM 成绩_2003 UNION ALL

SELECT 身份证号码, 语文成绩FROM 成绩_2004 UNION ALL

SELECT 身份证号码, 语文成绩FROM 成绩_2005; /* 文件名称：CH7_Demo138.sql */ USE 北风贸易; GO

IF OBJECT_ID(N'Score', N'U') IS NOT NULL DROP TABLE Score; GO

-- 将合并后的结果储存到一个名为Score的新表中 SELECT 身份证号码AS 学生代号, 语文成绩AS 语文成绩 INTO Score FROM 成绩_2003 UNION ALL

SELECT 身份证号码, 语文成绩FROM 成绩_2004 UNION ALL

SELECT 身份证号码, 语文成绩FROM 成绩_2005;

-- 查询Score表

SELECT * FROM Score; /* 文件名称：CH7_Demo139.sql */ USE 北风贸易; GO

IF OBJECT_ID(N'Score', N'U') IS NOT NULL DROP TABLE Score; GO

-- 将合并后的结果储存到一个名为Score的新表中

SELECT 身份证号码AS 学生代号, 语文成绩AS 语文成绩INTO Score FROM 成绩_2003 UNION ALL

SELECT 身份证号码, 语文成绩FROM 成绩_2004 UNION ALL

SELECT 身份证号码, 语文成绩FROM 成绩_2005 ORDER BY 学生代号DESC;

-- 查询Score表

SELECT * FROM Score;