本教程详细介绍了SQL Server 2008中Join操作的基础知识，并提供了实践练习题。内容涵盖创建辅助表、员工数据复制、日期范围查询、客户订单统计、客户订单详情、客户查询、特定日期订单查询等主题。
---------------------------------------------------------------------
-- Microsoft SQL Server 2008 T-SQL Fundamentals
-- Chapter 3 - Joins
-- Exercises
-- ?2008 Itzik Ben-Gan
---------------------------------------------------------------------
-- 1
-- 1-1
-- Run the following code to create the Nums auxiliary table
-- in the TSQLFundamentals2008 database:
-- Auxiliary table of nums
SET NOCOUNT ON;
USE TSQLFundamentals2008;
IF OBJECT_ID('dbo.Nums', 'U') IS NOT NULL DROP TABLE dbo.Nums;
CREATE TABLE dbo.Nums(n INT NOT NULL PRIMARY KEY);
DECLARE @i AS INT = 1;
BEGIN TRAN
WHILE @i <= 100000
BEGIN
INSERT INTO dbo.Nums VALUES(@i);
SET @i = @i + 1;
END
COMMIT TRAN
SET NOCOUNT OFF;
GO
-- 1-2
-- Write a query that generates 5 copies out of each employee row
-- Tables involved: TSQLFundamentals2008 database, Employees and Nums tables
--Desired output
empid firstname lastname n
----------- ---------- -------------------- -----------
1 Sara Davis 1
2 Don Funk 1
3 Judy Lew 1
4 Yael Peled 1
5 Sven Buck 1
6 Paul Suurs 1
7 Russell King 1
8 Maria Cameron 1
9 Zoya Dolgopyatova 1
1 Sara Davis 2
2 Don Funk 2
3 Judy Lew 2
4 Yael Peled 2
5 Sven Buck 2
6 Paul Suurs 2
7 Russell King 2
8 Maria Cameron 2
9 Zoya Dolgopyatova 2
1 Sara Davis 3
2 Don Funk 3
3 Judy Lew 3
4 Yael Peled 3
5 Sven Buck 3
6 Paul Suurs 3
7 Russell King 3
8 Maria Cameron 3
9 Zoya Dolgopyatova 3
1 Sara Davis 4
2 Don Funk 4
3 Judy Lew 4
4 Yael Peled 4
5 Sven Buck 4
6 Paul Suurs 4
7 Russell King 4
8 Maria Cameron 4
9 Zoya Dolgopyatova 4
1 Sara Davis 5
2 Don Funk 5
3 Judy Lew 5
4 Yael Peled 5
5 Sven Buck 5
6 Paul Suurs 5
7 Russell King 5
8 Maria Cameron 5
9 Zoya Dolgopyatova 5
(45 row(s) affected)
-- 1-3. (Optional, Advanced)
-- Write a query that returns a row for each employee and day
-- in the range June 12, 2009 ?June 16 2009.
-- Tables involved: TSQLFundamentals2008 database, Employees and Nums tables
--Desired output
empid dt
----------- -----------------------
1 2009-06-12 00:00:00.000
1 2009-06-13 00:00:00.000
1 2009-06-14 00:00:00.000
1 2009-06-15 00:00:00.000
1 2009-06-16 00:00:00.000
2 2009-06-12 00:00:00.000
2 2009-06-13 00:00:00.000
2 2009-06-14 00:00:00.000
2 2009-06-15 00:00:00.000
2 2009-06-16 00:00:00.000
3 2009-06-12 00:00:00.000
3 2009-06-13 00:00:00.000
3 2009-06-14 00:00:00.000
3 2009-06-15 00:00:00.000
3 2009-06-16 00:00:00.000
4 2009-06-12 00:00:00.000
4 2009-06-13 00:00:00.000
4 2009-06-14 00:00:00.000
4 2009-06-15 00:00:00.000
4 2009-06-16 00:00:00.000
5 2009-06-12 00:00:00.000
5 2009-06-13 00:00:00.000
5 2009-06-14 00:00:00.000
5 2009-06-15 00:00:00.000
5 2009-06-16 00:00:00.000
6 2009-06-12 00:00:00.000
6 2009-06-13 00:00:00.000
6 2009-06-14 00:00:00.000
6 2009-06-15 00:00:00.000
6 2009-06-16 00:00:00.000
7 2009-06-12 00:00:00.000
7 2009-06-13 00:00:00.000
7 2009-06-14 00:00:00.000
7 2009-06-15 00:00:00.000
7 2009-06-16 00:00:00.000
8 2009-06-12 00:00:00.000
8 2009-06-13 00:00:00.000
8 2009-06-14 00:00:00.000
8 2009-06-15 00:00:00.000
8 2009-06-16 00:00:00.000
9 2009-06-12 00:00:00.000
9 2009-06-13 00:00:00.000
9 2009-06-14 00:00:00.000
9 2009-06-15 00:00:00.000
9 2009-06-16 00:00:00.000
(45 row(s) affected)
-- 2
-- Return US customers, and for each customer the total number of orders
-- and total quantities.
-- Tables involved: TSQLFundamentals2008 database, Customers, Orders and OrderDetails tables
--Desired output
custid numorders totalqty
----------- ----------- -----------
32 11 345
36 5 122
43 2 20
45 4 181
48 8 134
55 10 603
65 18 1383
71 31 4958
75 9 327
77 4 46
78 3 59
82 3 89
89 14 1063
(13 row(s) affected)
-- 3
-- Return customers and their orders including customers who placed no orders
-- Tables involved: TSQLFundamentals2008 database, Customers and Orders tables
-- Desired output
custid companyname orderid orderdate
----------- --------------- ----------- ------------------------
85 Customer ENQZT 10248 2006-07-04 00:00:00.000
79 Customer FAPSM 10249 2006-07-05 00:00:00.000
34 Customer IBVRG 10250 2006-07-08 00:00:00.000
84 Customer NRCSK 10251 2006-07-08 00:00:00.000
...
73 Customer JMIKW 11074 2008-05-06 00:00:00.000
68 Customer CCKOT 11075 2008-05-06 00:00:00.000
9 Customer RTXGC 11076 2008-05-06 00:00:00.000
65 Customer NYUHS 11077 2008-05-06 00:00:00.000
22 Customer DTDMN NULL NULL
57 Customer WVAXS NULL NULL
(832 row(s) affected)
-- 4
-- Return customers who placed no orders
-- Tables involved: TSQLFundamentals2008 database, Customers and Orders tables
-- Desired output
custid companyname
----------- ---------------
22 Customer DTDMN
57 Customer WVAXS
(2 row(s) affected)
-- 5
-- Return customers with orders placed on Feb 12, 2007 along with their orders
-- Tables involved: TSQLFundamentals2008 database, Customers and Orders tables
-- Desired output
custid companyname orderid orderdate
----------- --------------- ----------- -----------------------
66 Customer LHANT 10443 2007-02-12 00:00:00.000
5 Customer HGVLZ 10444 2007-02-12 00:00:00.000
(2 row(s) affected)
-- 6 (Optional, Advanced)
-- Return customers with orders placed on Feb 12, 2007 along with their orders
-- Also return customers who didn't place orders on Feb 12, 2007
-- Tables involved: TSQLFundamentals2008 database, Customers and Orders tables
-- Desired output
custid companyname orderid orderdate
----------- --------------- ----------- -----------------------
72 Customer AHPOP NULL NULL
58 Customer AHXHT NULL NULL
25 Customer AZJED NULL NULL
18 Customer BSVAR NULL NULL
91 Customer CCFIZ NULL NULL
...
33 Customer FVXPQ NULL NULL
53 Customer GCJSG NULL NULL
39 Customer GLLAG NULL NULL
16 Customer GYBBY NULL NULL
4 Customer HFBZG NULL NULL
5 Customer HGVLZ 10444 2007-02-12 00:00:00.000
42 Customer IAIJK NULL NULL
34 Customer IBVRG NULL NULL
63 Customer IRRVL NULL NULL
73 Customer JMIKW NULL NULL
15 Customer JUWXK NULL NULL
...
21 Customer KIDPX NULL NULL
30 Customer KSLQF NULL NULL
55 Customer KZQZT NULL NULL
71 Customer LCOUJ NULL NULL
77 Customer LCYBZ NULL NULL
66 Customer LHANT 10443 2007-02-12 00:00:00.000
38 Customer LJUCA NULL NULL
59 Customer LOLJO NULL NULL
36 Customer LVJSO NULL NULL
64 Customer LWGMD NULL NULL
29 Customer MDLWA NULL NULL
...
(91 row(s) affected)
-- 7 (Optional, Advanced)
-- Return all customers, and for each return a Yes/No value
-- depending on whether the customer placed an order on Feb 12, 2007
-- Tables involved: TSQLFundamentals2008 database, Customers and Orders tables
-- Desired output
custid companyname HasOrderOn20070212
----------- --------------- ------------------
1 Customer NRZBB No
2 Customer MLTDN No
3 Customer KBUDE No
4 Customer HFBZG No
5 Customer HGVLZ Yes
6 Customer XHXJV No
7 Customer QXVLA No
8 Customer QUHWH No
9 Customer RTXGC No
10 Customer EEALV No
...
(91 row(s) affected)
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
