《microsoft sql server 2008技术内幕 t-sql语言基础》

第一章 TSQL编程基础

源代码下载：TSQLFundamentals2008

创建表

USE testdb;
CREATE TABLE dbo.Employess
(
empid INT NOT NULL,
firstname VARCHAR(30) NOT NULL,
lastname VARCHAR(30) NOT NULL,
hiredate DATE NOT NULL,
mgrid INT NULL,
ssn VARCHAR(20) NOT NULL,
salary MONEY NOT NULL
)

• 主键约束

ALTER TABLE Employess
ADD CONSTRAINT PK_Employess
PRIMARY KEY(empid)

• 唯一约束

--唯一约束
ALTER TABLE Employess
ADD CONSTRAINT UN_Employess_ssn
UNIQUE(ssn)

• 外键约束
  外键约束用于实施的完整性。在引用表的一组属性上进行定义，并指向被引用表中的一组候选键。引用表和被引用表可能是同一个表。
  外键的目的是为了将外键列允许的值域限制为被引用列中的现有的值。

--创建订单表
CREATE TABLE Orders
(
orderid INT NOT NULL,
empid INT NOT NULL,
custid VARCHAR(10) NOT NULL,
orderts DATETIME NOT NULL,
qty INT NOT NULL,
CONSTRAINT PK_Orders
PRIMARY KEY(orderID)
);

--将Orders 表的 empid 列支持的值域限制为现有Employees 表中empid列。
ALTER TABLE Orders
ADD CONSTRAINT FK_Orders_Employees
FOREIGN KEY(empid)
REFERENCES Employess(empid);

• 检查约束
  检查约束用于定义在表中输入或修改一行数据前必须满足得一个谓词。
  例如，检查约束可以保证 Employees 表的 salary 列只支持正数：

ALTER TABLE Employess
ADD CONSTRAINT CHK_Emplyees_salary
CHECK(salary>0);

• 默认约束
  插入数据，没有显示指定值，可以用表达式为其默认值。

ALTER TABLE Orders
ADD CONSTRAINT DTF_Orders_orderts
DEFAULT(CURRENT_TIMESTAMP) FOR orderts

主键约束：列内容不能重复，且不能为空
外键约束：内容必须是所关联主键的值其中的值
空值约束：不能为空
默认约束：当插入时没有赋值，会使用默认值
唯一约束：列的内容都是唯一的，不以重复
检查约束：列的内容必须符合要求。如CHECK (JOB IN(‘Engineer’,’Sales’,’Manager’));

第二章 单表查询

练习题：
1.返回2007年6月的订单，数据库：TSQLFundamentals2008，表：Sales.Orders
期望的输出：

(30 row(s) affected)

2.（可选，高级）
返回每个月的最后一天生成的订单。表：Sales.Orders
期望的输出：

3.返回姓氏（last name）包含字母‘a’两次或更多的雇员。涉及表：HR.Employees
期望的输出：

4.返回总价格（数量*单价）大于10000的所有订单，并按总价格排序。涉及表：Sales.OrderDetails
期望的输出：

5.返回2007年平均运费最高的三个发货国家。涉及表：Sales.Orders
期望的输出：

6.为每个顾客单独根据订单日期的顺序（用 orderID作为附加属性）来计算其订单的行号。涉及表：Sales.Orders
期望的输出：

7.构造一个 SELECT 语句，让它根据每个雇员的友好称谓，而返回其性别，对于‘Ms.’ 和 ‘Mrs.’，则返回 'Female'；
对于'Mr.'，则返回'Male'；对于其他情况（例如'Dr.'），则返回 'Unkonwn'。涉及表：HR.Employees
期望的输出：

8.返回每个客户的客户ID和所在区域。对输出中的行按区域排序，NULL 值排在最后面（在所有非 NULL 值之后）。注意，T-SQL中 NULL 值的默认排序行为是把 NULL 值排在前面（所有非 NULL 值之前）。涉及表：Sales.Customers 表
期望的输出：

--1.返回2007年6月的订单
SELECT  orderid,orderdate,custid,empid
FROM    Sales.Orders
WHERE   YEAR(orderdate) = 2007
        AND MONTH(orderdate) = 6;

--2.返回每个月的最后一天生成的订单
-- MONTH(DATEADD(day,1,orderdate)) <> MONTH(orderdate)
-- orderdate+1天的月份数不等于当前月
SELECT * FROM Sales.Orders
WHERE orderdate = (CASE when MONTH(DATEADD(day,1,orderdate)) <> MONTH(orderdate) THEN orderdate
ELSE NULL END)	

--3.返回姓氏（last name）包含字母‘a’两次或更多的雇员
-- LEN(lastname) - LEN(REPLACE(lastname, 'a', ''))
-- lastname字段字符‘a’出现的次数 = lastname的长度 - lastname字段把a去掉后的长度
SELECT  empid,firstname,lastname
FROM    HR.Employees
WHERE   LEN(lastname) - LEN(REPLACE(lastname, 'a', '')) >= 2;

--4.返回总价格（数量*单价）大于10000的所有订单，并按总价格排序。
SELECT orderid, SUM(unitprice*qty) as totalvalue FROM Sales.OrderDetails
GROUP BY (orderid)
HAVING SUM(unitprice*qty)>10000
ORDER BY totalvalue DESC

--5.返回2007年平均运费最高的三个发货国家
SELECT TOP(3) AVG(freight) AS avgfreight,shipcountry FROM Sales.Orders
WHERE YEAR(orderdate)=2007
GROUP BY shipcountry
ORDER BY avgfreight DESC

--6.为每个顾客单独根据订单日期的顺序（用 orderID作为附加属性）来计算其订单的行号。
SELECT  custid, orderdate, orderid,
        ROW_NUMBER() OVER ( PARTITION BY custid ORDER BY orderdate ) AS rownum
FROM    Sales.Orders
ORDER BY custid;

--7.构造一个 SELECT 语句，让它根据每个雇员的友好称谓，而返回其性别，对于‘Ms.’ 和 ‘Mrs.’，则返回 'Female'；
--对于'Mr.'，则返回'Male'；对于其他情况（例如'Dr.'），则返回 'Unkonwn'。
SELECT  empid, firstname, lastname, titleofcourtesy,
        CASE WHEN titleofcourtesy = 'Ms.'
                  OR titleofcourtesy = 'Mrs.' THEN 'Female'
             WHEN titleofcourtesy = 'Mr.' THEN 'Male'
             ELSE 'Unkonwn'
        END AS gender
FROM    HR.Employees;

--8.返回每个客户的客户ID和所在区域。对输出中的行按区域排序，NULL 值排在最后面（在所有非 NULL 值之后）。
--注意，T-SQL中 NULL 值的默认排序行为是把 NULL 值排在前面（所有非 NULL 值之前）。
SELECT custid,region
 FROM Sales.Customers
 ORDER BY 
 (CASE WHEN region IS NULL THEN 1 ELSE 0 END)