/* Chapter 11 */
/* Try it out - Joining Two Tables - Step 1 */
SELECT s.ShareDesc,sp.Price,sp.PriceDate
FROM ShareDetails.Shares s
JOIN ShareDetails.SharePrices sp ON sp.ShareId = s.ShareId
/* Try it out - Joining Two Tables - Step 3 */
SELECT s.ShareDesc,sp.Price,sp.PriceDate
FROM ShareDetails.Shares s
JOIN ShareDetails.SharePrices sp ON sp.ShareId = s.ShareId
AND sp.Price = s.CurrentPrice
/* Try it out - Joining Two Tables - Step 5 */
SELECT s.ShareDesc,sp.Price,sp.PriceDate
FROM ShareDetails.Shares s
LEFT OUTER JOIN ShareDetails.SharePrices sp ON sp.ShareId = s.ShareId
/* Try it out - Joining Two Tables - Step 7 */
SELECT s.ShareDesc,sp.Price,sp.PriceDate
FROM ShareDetails.Shares s
LEFT OUTER JOIN ShareDetails.SharePrices sp ON sp.ShareId = s.ShareId
WHERE sp.Price IS NULL
/* Try it out - Joining Two Tables - Step 9 */
SELECT s.ShareDesc,sp.Price,sp.PriceDate
FROM ShareDetails.SharePrices sp
RIGHT OUTER JOIN ShareDetails.Shares s ON sp.ShareId = s.ShareId
/* Try it out - Joining Two Tables - Step 11 */
INSERT INTO ShareDetails.SharePrices
(ShareId, Price, PriceDate)
VALUES (99999,12.34,'1 Aug 2008 10:10AM')
SELECT s.ShareDesc,sp.Price,sp.PriceDate
FROM ShareDetails.SharePrices sp
FULL OUTER JOIN ShareDetails.Shares s ON sp.ShareId = s.ShareId
/* Try it out - Joining Two Tables - Step 13 */
SELECT s.ShareDesc,sp.Price,sp.PriceDate
FROM ShareDetails.SharePrices sp
CROSS JOIN ShareDetails.Shares s
/* Try it out - Declaring and Working with Variables - Step 1 */
DECLARE @MyDate datetime=GETDATE(), @CurrPriceInCents money
SELECT @CurrPriceInCents = CurrentPrice * 100
FROM ShareDetails.Shares
WHERE ShareId = 2
SELECT @MyDate,@CurrPriceInCents
/* Try it out - Declaring and Working with Variables - Step 3 */
DECLARE @MyDate datetime= GETDATE(), @CurrPriceInCents money
SELECT @CurrPriceInCents = CurrentPrice * 100
FROM ShareDetails.Shares
WHERE ShareId = 2
GO
SELECT @MyDate,@CurrPriceInCents
/* Try it out - Declaring and Working with Variables - Step 5 */
DECLARE @MyDate datetime= GETDATE(), @CurrPriceInCents money
SELECT @CurrPriceInCents = CurrentPrice * 100
FROM ShareDetails.Shares
-- WHERE ShareId = 2
--GO
SELECT @MyDate,@CurrPriceInCents
/* Try it out - Temporary Tables - Step 1 */
CREATE TABLE #SharesTmp
(ShareDesc varchar(50),
Price numeric(18,5),
PriceDate datetime)
/* Try it out - Temporary Tables - Step 2 */
INSERT INTO #SharesTmp
SELECT s.ShareDesc,sp.Price,sp.PriceDate
FROM ShareDetails.Shares s
JOIN ShareDetails.SharePrices sp ON sp.ShareId = s.ShareId
/* Try it out - Temporary Tables - Step 3 and 5*/
SELECT * FROM #SharesTmp
/* Try it out - Temporary Tables - Step 7 */
DROP TABLE #SharesTmp
/* Try it out - Temporary Tables - Step 8 */
CREATE TABLE ##SharesTmp
(ShareDesc varchar(50),
Price numeric(18,5),
PriceDate datetime)
INSERT INTO ##SharesTmp
SELECT s.ShareDesc,sp.Price,sp.PriceDate
FROM ShareDetails.Shares s
JOIN ShareDetails.SharePrices sp ON sp.ShareId = s.ShareId
SELECT * FROM ##SharesTmp
/* Try it out - Temporary Tables - Step 10 */
SELECT * FROM ##SharesTmp
/* Try it out - Counting Rows - Step 1 */
SELECT COUNT(*) AS 'Number of Rows'
FROM ShareDetails.Shares
/* Try it out - Counting Rows - Step 3 */
SELECT COUNT(*) AS 'Number of Rows'
FROM ShareDetails.Shares
WHERE CurrentPrice > 10
/* Try it out - Summing Values - Step 1 */
SELECT SUM(Amount) AS 'Amount Deposited'
FROM TransactionDetails.Transactions
WHERE CustomerId = 1
AND TransactionType = 1
/* Try it out - Max and Min - Step 1 */
SELECT MAX(Price) MaxPrice,MIN(Price) MinPrice
FROM ShareDetails.SharePrices
/* Try it out - AVG - Step 1 */
SELECT AVG(Price) AvgPrice
FROM ShareDetails.SharePrices
WHERE ShareId = 1
/* Try it out - GROUP BY - Step 1 */
SELECT ShareId, MIN(Price) MinPrice, Max(Price) MaxPrice
FROM ShareDetails.SharePrices
WHERE ShareId < 9999
GROUP BY ShareId
/* Try it out - GROUP BY - Step 3 */
SELECT sp.ShareId, s.ShareDesc,MIN(Price) MinPrice, Max(Price) MaxPrice
FROM ShareDetails.SharePrices sp
LEFT JOIN ShareDetails.Shares s ON s.ShareId = sp.ShareId
WHERE sp.ShareId < 9999
GROUP BY ALL sp.ShareId, s.ShareDesc
/* Try it out - HAVING - Step 1 */
SELECT sp.ShareId, s.ShareDesc,MIN(Price) MinPrice, Max(Price) MaxPrice
FROM ShareDetails.SharePrices sp
LEFT JOIN ShareDetails.Shares s ON s.ShareId = sp.ShareId
WHERE sp.ShareId < 9999
GROUP BY ALL sp.ShareId, s.ShareDesc
HAVING MIN(Price) > 10
/* Try it out - DISTINCT VALUES - Step 1 */
SELECT s.ShareDesc,sp.Price,sp.PriceDate
FROM ShareDetails.Shares s
JOIN ShareDetails.SharePrices sp ON sp.ShareId = s.ShareId
/* Try it out - DISTINCT VALUES - Step 2 */
SELECT DISTINCT s.ShareDesc,sp.Price,sp.PriceDate
FROM ShareDetails.Shares s
JOIN ShareDetails.SharePrices sp ON sp.ShareId = s.ShareId
/* Try it out - DISTINCT VALUES - Step 3 */
SELECT DISTINCT s.ShareDesc
FROM ShareDetails.Shares s
JOIN ShareDetails.SharePrices sp ON sp.ShareId = s.ShareId
/* Try it out - DATEADD - Step 1 */
DECLARE @OldTime datetime
SET @OldTime = '24 March 2008 3:00 PM'
SELECT DATEADD(hh,4,@OldTime)
/* Try it out - DATEADD - Step 2 */
DECLARE @OldTime datetime
SET @OldTime = '24 March 2008 3:00 PM'
SELECT DATEADD(hh,-6,@OldTime)
/* Try it out - DATEDIFF - Step 1 */
DECLARE @FirstTime datetime, @SecondTime datetime
SET @FirstTime = '24 March 2008 3:00 PM'
SET @SecondTime = '24 March 2008 3:33PM'
SELECT DATEDIFF(ms,@FirstTime,@SecondTime)
/* Try it out - DATENAME - Step 1 */
DECLARE @StatementDate datetime
SET @StatementDate = '24 March 2008 3:00 PM'
SELECT DATENAME(dw,@StatementDate)
/* Try it out - DATEPART - Step 1 */
DECLARE @WhatsTheDay datetime
SET @WhatsTheDay = '24 March 2008 3:00 PM'
SELECT DATEPART(dd, @WhatsTheDay)
/* Try it out - DATEPART - Step 2 */
DECLARE @WhatsTheDay datetime
SET @WhatsTheDay = '24 March 2008 3:00 PM'
SELECT DATENAME(dw, @WhatsTheDay) + ', ' +
CAST(DATEPART(dd,@WhatsTheDay) AS varchar(2)) + ' ' +
DATENAME(mm,@WhatsTheDay) + ' ' +
CAST(DATEPART(yyyy,@WhatsTheDay) AS char(4))
/* Try it out - ASCII - Step 1 */
DECLARE @StringTest char(10)
SET @StringTest = ASCII('Robin ')
/* Try it out - CHAR - Step 1 */
DECLARE @StringTest char(10)
SET @StringTest = ASCII('Robin ')
SELECT CHAR(@StringTest)
SELECT @StringTest
/* Try it out - CHAR - Step 3 */
DECLARE @StringTest int
SET @StringTest = ASCII('Robin ')
SELECT CHAR(@StringTest)
/* Try it out - LEFT - Step 1 */
DECLARE @StringTest char(10)
SET @StringTest = 'Robin '
SELECT LEFT(@StringTest,3)
/* Try it out - LOWER - Step 1 */
DECLARE @StringTest char(10)
SET @StringTest = 'Robin '
SELECT LOWER(LEFT(@StringTest,3))
/* Try it out - LTRIM - Step 1 */
DECLARE @StringTest char(10)
SET @StringTest = ' Robin'
SELECT 'Start-'+LTRIM(@StringTest),'Start-'+@StringTest
/* Try it out - RIGHT - Step 1 */
DECLARE @StringTest char(10)
SET @StringTest = ' Robin'
SELECT RIGHT(@StringTest,3)
/* Try it out - RTRIM - Step 1 */
DECLARE @StringTest char(10)
SET @StringTest = 'Robin'
SELECT @StringTest+'-End',RTRIM(@StringTest)+'-End'
/* Try it out - STR - Step 1 */
SELECT 'A'+82
/* Try it out - STR - Step 3 */
SELECT 'A'+STR(82)
/* Try it out - STR - Step 5 */
SELECT 'A'+LTRIM(STR(82))
/* Try it out - SUBSTR - Step 1 */
DECLARE @StringTest char(10)
SET @StringTest = 'Robin '
SELECT SUBSTRING(@StringTest,3,LEN(@StringTest))
/* Try it out - UPPER - Step 1 */
DECLARE @StringTest char(10)
SET @StringTest = 'Robin '
SELECT UPPER(@StringTest)
/* Try it out - CASE - Step 1 */
SET QUOTED_IDENTIFIER OFF
SELECT CustomerId,
CASE WHEN CreditType = 0 THEN "Debits" ELSE "Credits" END
AS TranType,SUM(Amount)
FROM TransactionDetails.Transactions t
JOIN TransactionDetails.TransactionTypes tt ON
tt.TransActionTypeId = t.TransactionType
WHERE t.DateEntered BETWEEN '1 Aug 2008' AND '31 Aug 2008'
GROUP BY Cust
SQL Server 2008基础教程(最佳SQL Server 2008入门教程)--随书源代码
4星 · 超过85%的资源 需积分: 10 5 浏览量
2013-02-06
11:40:49
上传
评论
收藏 16KB ZIP 举报 
SQL Server 2008基础教程 随书源代码.zip （15个子文件） 
Chapter1.txt 20B Chapter7.txt 4KB Chapter2.txt 20B Chapter3.txt 550B Chapter8.txt 9KB Chapter10.txt 6KB Chapter13.txt 7KB All-PleaseReadMe.txt 269B Chapter12.txt 10KB Chapter14.txt 42B Chapter4.txt 1KB Chapter5.txt 3KB Chapter9.txt 5KB Chapter11.txt 11KB Chapter6.txt 3KB
CruiseYoung
- 粉丝: 156
- 资源: 104
最新资源
- 基于CarNet实现裂缝检测python源码+文档说明+数据+图片(课程设计)
- 课程设计-基于耐火材料裂缝剥落检测python源码+课件
- 基于OpenCV的视频道路车道检测python源码+文档说明+实验演示+图片+使用方法(高分毕业设计)
- 基于OpenCV的案例:图像边缘、角点和轮廓检测，图像分割，图像增强;图片拼接；运动目标检测，颜色直方图比较，三帧帧差法，抠图
- SmartPlug-html大一笔记
- SmartPlug-proteusdemo
- Preliminary Findings on Handmade Rattan Baby Crib andBassinet Designs Regarding.zip
- aveebfq_v1.2.83_downyi.com.apk
- 基于有机发光二极管(OLED)的建模优化算法的matlab仿真源码+数据+文档说明+项目说明(高分课程设计)
- hash01-test.c 本人哈希表（一）的示例代码，仅供参考！
资源上传下载、课程学习等过程中有任何疑问或建议，欢迎提出宝贵意见哦~我们会及时处理！
点击此处反馈
- 1
- 2
前往页