T SQL

Note: does not open anything from design mode always use T-SQL to solve problems.
1. Read this book and solved Exercises
After this you must handed on experience of
• Data filtering (where ,in and Between)
• Sorting Data( Order by )
• Date and Time Functions
• String function
• Case/IF statement
• Joins
• Sub-Queries
• Set Operators
• Aggregate Functions and Group by
• Window Function (Over ,Partition, Ranks etc)
• CTE and loops
• Pivoting and Un Pivoting Data
• View , UDF(user define function) and store procedures.
• DML statement (create , alter and drop )
• Indexes

2. Please solved following problems using T SQL

• Write UPSERT Statement with and without joins (to move data from one table to
another table).
• Using Merge statement to UPSERT and Write down its Limitations.
• Write Merge / UPSERT in Store Procedure.
• Find out Invoice which have no parent and child.
In following Data set you can see invoice -004, 005 and 006 have no parent and no
child ,you task write T-SQL statement find out those invoices.
CREATE TABLE dbo.Invoices ( [InvoiceCode]
varchar(20), [ParentInvoiceCode] varchar(20),
[InvoiceDate] date )
INSERT INTO dbo.Invoices
VALUES
( 'INV-001', 'INV-001', N'2015-01-01 00:00:00.000' ),
( 'INV-002', 'INV-001', N'2015-01-01 00:00:00.000' ),
( 'INV-003', 'INV-001', N'2015-01-01 00:00:00.000' ),
( 'INV-004', 'INV-004', N'2015-01-01 00:00:00.000' ),
( 'INV-005', 'INV-005', N'2015-01-01 00:00:00.000' ),
( 'INV-006', 'INV-006', N'2015-01-01 00:00:00.000' ),
( 'INV-007', 'INV-007', N'2015-01-01 00:00:00.000' ),
( 'INV-008', 'INV-007', N'2015-01-01 00:00:00.000' ),
( 'INV-009', 'INV-007', N'2015-01-01 00:00:00.000' ),
( 'INV-010', 'INV-007', N'2015-01-01 00:00:00.000' )
 • Update ParentKey
o Create new table DimInvoices from Invoices table with two columns
o Invoice Key (on the basic of invoice Code)
o And invoice Parent Key and update it.
Require Output
InvoiceKey InvoiceParentKey InvoiceCode ParentInvoiceCode
1 1 INV-001 INV-001
2 1 INV-002 INV-001
3 1 INV-003 INV-001
4 4 INV-004 INV-004
5 5 INV-005 INV-005
6 6 INV-006 INV-006
7 7 INV-007 INV-007
8 7 INV-008 INV-007
9 7 INV-009 INV-007
10 7 INV-010 INV-007
2. Table 1

• Second Highest Value

Use “AdventureWorksDW2012” DB

o Find out Second Highest “SalesTerritoryRegion” on this basic of “ExtendedAmount”

of Fact Internet Sale Table.
o Create Produces Named “spHighestValues (N)” to find out Nth Highest values.

• Repeat Rows n time according to values

o Create table “Student” with Key,Name,Marks
o Insert One Row in this table (1,Ali,3)
o Now Write Statement to Store Student Name 3 time (According to marks) on
another table Dim Student.
o Insert one more Row (2,Ahmad ,4)
o Now “Ali” should Store 3 time and “Ahmad” 4 time in Dim Student.

• Sales Territory Summary

o Before doing this, you should have knowledge about:
o Views, Subqueries, String Function, Sorting Function, Case Statements, Aggregate
Functions,
o Union and Joins
o Show following Results using FactInternetSale ExtendedAmount Total Sales and
DimSalesTerritory Tables
SalesTerritoryRegion TotalSales Millions Percentage Status Description
Result 0
Australia 9061000.584 US Dollar 10.07 M 30.86% High ⬆ Australia Sale is 'High'.
Canada 1977844.862 US Dollar 1.978 M 6.74% Low ⬇ Canada Sale is 'Low'.
Central 3000.829 US Dollar 0.003 M 0.01% Low ⬇ Central Sale is 'Low'.
France 2644017.714 US Dollar 2.644 M 9.01% Low ⬇ France Sale is 'Low'.
Germany 2894312.338 US Dollar 2.894 M 9.86% Low ⬇ Germany Sale is 'Low'.
Northeast 6532.468 US Dollar 0.007 M 0.02% Low ⬇ Northeast Sale is 'Low'.
Northwest 3649866.551 US Dollar 3.650 M 12.43% High ⬆ Northwest Sale is 'High'.
Southeast 12238.849 US Dollar 0.012 M 0.04% Low ⬇ Southeast Sale is 'Low'.
Southwest 5718150.812 US Dollar 5.718 M 19.48% High ⬆ Southwest Sale is 'High'.
United Kingdom 3391712.211 US Dollar 3.392 M 11.55% High ⬆ United Kingdom Sale is 'High'.
Grand Total 29358677.2207 US Dollar 29.358 M 100.00%

1) Create View vwSalesTerritorySummary to show about Result.

2) Make Sure First Row should be Result and Last Row Grand Total.
3) Total Sales should be 3 Decimal places and should contain US Dollar.
4) Convert Total Sales in Millions, M for Million should Display with no.
5) Percentage (Total Sales/Grand Total), % sign should display after 2 Decimal Places.
6) Status High and Low depend on Percentage ,
If Percentage more than 10 then High
Less than 10 for Low
Also display ⬆ and ⬇ Arrows.
7) Description should contain Single Quote ‘Surrounded by High and Low

• Sales Territory Hierarchy Summary

Create Store Produce SpSalesTerritoryHierarchySummary.
1. Most Sure you have “PercentID” of TerritoryGroup
1 for North America
2 for Europe and
3 for Pacific

Figure 1

Your Produce should return result Figure 1 Result As it is.

Hint: You can calculate separately than combine Results using unions.
 • Fill Missing Days with Zero Values
SELECT DueDateKey,
*
FROM dbo.FactInternetSales
WHERE LEFT(DueDateKey, 6) = '200507' AND
SalesTerritoryKey = 1

If you run this query you will find, follow results

Figure 2

If you look Some DueDatekey are missing.

You Task to find Missing Data with other Values zero.

Like for DueDateKey 20050701 ProductKey, OrderDateKey, shipDate,CustomerKey and other rows
would be Zero.

• Find out Mean, Median, Mode, Row Number, Rank Dense Rank, Running Total, Q1, Q2
and Q3
Let first teach what is Mean, Median and Mode

• Mean= (1 + 2 + 2 + 3 + 4 + 7 + 9) / 7 = 4
• Median (the middle value ordered ascending) = 1, 2, 2, 3, 4, 7, 9 = 3
• Mode (most frequent value) = 1, 2, 2, 3, 4, 7, 9 = 2

o Create Table StatisticData with one column Value

o Insert data into StatisticData (1, 2, 2, 3, 4, 7, and 9)
o Now Write Statement to find Mean Median and Mode.
 o Use FactInternetSales to find Mean, Median and Mode of ExtendedAmount for Every
Month of 2005.
o Use StatisticData Table to Find Rank , Dense Rank , RowNumber,and Running Total
o Table 2 have output Result.

Values Rank Dense Rank RowNumber Running Total

1 1 1 1 1
2 2 2 1 3
2 2 2 2 5
3 4 3 3 8
4 5 4 4 12
7 6 5 5 19
9 7 6 6 28
Table 2

o Now move to FactInternetSales to Find RowNumber, Rank, DenseRank, and Running

Total.

• Customer Code Creation

---------------