All Powder Board and Ski SQL Server Workbook
All Powder Board and Ski SQL Server Workbook Chapter 4: Queries Jerry Post Copyright © 2003 1
Primary Tables 2
Action Copy files from Build. All. Powder. Drop or rename conflicting tables. Switch to command mode. Run the batch file Build. All. Powder. SQL server username password Check the tables. 3
File/Get External Data/Import (1) Copy Build. All. Powder folder to machine running SQL Server (2) Drop or rename any conflicting tables (1) By hand (2) In SQL Plus, run the file Drop. All. Powder. Tables. SQL. sql (3) Switch to command mode: Start/All Programs/Accessories/Command Prompt (4) Build. All. Powder. SQL server username password 4
All Powder Tables After Import 5
Starting a Query: Query Analyzer Execute query Database connection Default database SQL statement Results 6
Enterprise Manager View Execute query Tables and Joins Database SQL text Results 7
Action Start a new View. Right-click and Add the Item. Model table. Select columns: Category, List. Price, Weight. Max, Color, and Graphics. Enter conditions: Category=’Board’ AND List. Price<300 AND Weight. Max>150. Check the SQL text. Run the query. 8
Sample Query Display snowboards with a list price under $300 and max weight over 150 pounds. Tables Constraints SQL Results 9
More Complex Query Ski for jumping. Composite material. Red or Yellow main color. Yellow skis must be under $300. Red skis must be under $400 Three main conditions WHERE Category=‘Ski’ AND Item. Material=‘Composite’ AND Style=‘Jump’ 10
Action Start a new query. Add the Item. Model table. SELECT Category, Color, Item. Material, Style, List. Price Enter conditions: Category=’Ski’ And Style=’Jump’ And Item. Material=’Composite’ Run the query to ensure it works Add the conditions for Color=’Yellow’ and List. Price<300 Test the query Add the conditions for Color=’Red’ and List. Price<400 Add the correct parentheses Run the query and test it 11
Color Options Yellow and price conditions All 5 conditions must hold, so only one row matches 12
Multiple Conditions (Category=N’Ski’ AND Style=N’Jump’ AND Item. Material=N’Composite’) AND ( (Color=‘Yellow’ AND List. Price<300) OR (Color=‘Red’ AND List. Price<400) ) 13
JOIN Query: Sales in May Cash payment 14
Action Start with a blank query Add SELECT, FROM, WHERE Set Sale. ID, Sale. Date, Customer. ID, and Payment. Method Use only the Sale table Set the Sale. Date between 01 -May-2004 AND 31 -May 2004 Set Payment. Method to Cash Run the query to test it 15
JOIN Tables: Sale + Customer Additional columns Join condition 16
Action Add the Customer table Check the join condition: ON Sale. Customer. ID = Customer. ID Add Customer Last. Name and First. Name to the SELECT statement Run the query to test it 17
JOIN: SQL SELECT Sale. ID, Sale. Date, Sale. Customer. ID, Last. Name, First. Name, Payment. Method FROM Sale INNER JOIN Customer ON Sale. Customer. ID = Customer. ID WHERE Sale. Date Between ’ 01/05/2004' AND '31/05/2004' AND Payment. Method='Cash' 18
Building a more complex query Which customers bought Atomic skis in January or February? What do you want to see? Customer names, Sale. Date What do you know? Manufacturer name, Sale. Date range, Category is Ski What tables are involved? How are they joined? Customer … Sale … Item. Model, Manufacturer SELECT Last. Name, First. Name, Sale. Date FROM Customer, …, Sale, …, Item. Model, Manufacturer JOIN WHERE Manufacturer. Name=N‘Atomic”’ AND Sale. Date BETWEEN ’ 01/01/2004’ AND ’ 29/02/2004’ AND Item. Model. Category = N‘Ski’ 19
Join: Many Tables 20
SQL: Many Table JOINs SELECT Last. Name, First. Name, powder. Item. Model. Category, Name, Sale. Date FROM powder. Manufacturer INNER JOIN powder. Item. Model ON powder. Manufacturer. ID = powder. Item. Model. Manufacturer. ID INNER JOIN powder. Inventory ON powder. Item. Model. ID = powder. Inventory. Model. ID INNER JOIN powder. Sale. Item ON powder. Inventory. SKU = powder. Sale. Item. SKU INNER JOIN powder. Sale ON powder. Sale. Item. Sale. ID = powder. Sale. ID INNER JOIN powder. Customer ON powder. Sale. Customer. ID = powder. Customer. ID WHERE (powder. Item. Model. Category = N'Ski') AND (Name = N'Atomic') AND (powder. Sale. Date BETWEEN CONVERT(DATETIME, '2004 -01 -01 00: 00', 102) AND CONVERT(DATETIME, '2004 -02 -29 00: 00', 102)) 21
Older JOIN Method List tables separated by commas Place join condition in the WHERE clause 22
Calculations Calculated column 23
Action Create a new query using only the Item. Model table In the SELECT row, add a new pseudo column to compute List. Price-Cost As Profit Add the ORDER BY line to sort by Category and List Price descending Run the query 24
Common Functions Lower Len Substring LTrim, RTrim Upper To lower case Length/number of characters Get substring Remove leading and trailing spaces To upper case Get. Date. Add Date. Diff Convert Day, Month, Year, Date. Part Current date Add a number to a date Subtract two dates Highly detailed formatting Abs Cos Floor Round Absolute value Cosine, all common trig functions Integer, drop decimal values Round-off Get parts of a date Books Online: Transact-SQL Reference/Functions 25
Format Dates: Convert 26
Add Days and Date. Add Sale. Date + 30 days Sale. Date + one month 27
Action Create a new query Use only the Sale table SELECT Sale. ID and Sale. Date Add 30 days to the Sale. Date to get Late. Date Use Date. Add to add one month to the Sale. Date to get Sale. Month Run the query 28
Query: Sum function 29
Action Create a new query Add the Sale table SELECT Ship. State and Sales. Tax WHERE Ship. State = ‘CA’ Run the query Verify the correct states are displayed Remove Ship. State from SELECT Sum(Sales. Tax) AS Sum. Of. Sales. Tax Run the query 30
SQL: Sum SELECT Sum(Sales. Tax) AS Sum. Of. Sales. Tax FROM Sale WHERE Sale. Ship. State=‘CA’ 31
Query: Group By produces subtotals for all values in the specified column 32
SQL: Group By SELECT Ship. State, Sum(Sales. Tax) AS Sum. Of. Sales. Tax FROM Sale GROUP BY Ship. State; 33
Action Create a new query Use the Sale table Select columns: Ship. State and Sum(Sales. Tax) AS Sum. Of. Sales. Tax Click the Group By button and set Group By for the Sale. State Run the query 34
Total Sales Value in Colorado Multiply on each row and sum across rows 35
CREATE VIEW—Save a Query CREATE VIEW Colorado. Sales AS SELECT Sum(Quantity. Sold*Sale. Price) AS Sale. Total FROM Sale INNER JOIN Sale. Item ON Sale. ID = Sale. Item. Sale. ID WHERE Ship. State=’CO’ 36
- Slides: 36