Start up

1. Start up Log on to the network Start Management Studio Connect to Reliant\sql2k5 and your SalesOrders database Start Books Online

2. MIS 431Dr. Steve RossSpring 2007 Interface and Query Basics

3. Connecting to SQL Server MDAC � Microsoft Data Access Components ODBC � Open Database Connectivity DSN � Data Source Name User DSN � one user one computer System DSN � all users one computer File DSN � shared on multiple computers

4. Creating a User DSN Start | Settings* | Control Panel * Settings choice might not be necessary depending on Start Menu configuration Administrative Tools Data Sources (ODBC) User DSN | Add | SQL Native Client Screen 1: Name, Description, Server Screen 2: SQL Server authentication info Screen 3: set default to your database Screen 4: accept defaults

5. The SELECT Statement Clauses: Select � From � Where � Group By � Having � Order By � Yields a Result Set Single value Single row Single column Multiple rows and columns

6. Specifying Columns SELECT * � All columns May encounter permissions problems No control over order of output SELECT col_name,col_name � More selective Columns output in order specified

7. Eliminating Duplicate Rows SELECT DISTINCT col_name � Eliminates duplicate output Only unique values of a single column, or unique combinations of multiple columns

8. Executing Queries in Management Studio Open your database and list of tables Click on one of the tables Click the �New Query� icon (upper left) Type the query Click the checkmark icon to test syntax Click the �! Execute� icon to execute Multiple statements in one window: Highlight statement(s) to be executed

9. Saving Your Work Why? Test queries before embedding them in program code, stored procedures, views How? In Management Studio, click save icon Save in �My Documents\...\Projects� or elsewhere File extension will be .sql � open with Notepad Warning! Before starting on a new query, click the �New Query� icon (otherwise you�ll overwrite previous query)

10. Practical Exercise 1 An ER Diagram for SalesOrders appears on page 477 Using your copy of SalesOrders Extract a list of all customer table data Limit the list to only city and states Eliminate duplicate city/state values

11. Character String Literals SELECT 'The Vendor''s Name is ', VendName � SQL standard delimiter for strings is a single quotation mark at each end Pay attention to the slant: 'this way' not: �this way� Many word processing and presentation programs �help� you by using intelligent quotes To include a single quote mark in the string, double it: 'Joe''s Bar'

12. Numeric Literals SELECT 495,-101.5,.986E+02 �

13. Date and Time Literals SELECT 'January 20, 2004', '01/20/2004' � SELECT '14:30:24','04:24 PM' � Other formats acceptable, check in Books Online

14. Expressions Be aware of data types Some convert automatically Some must be converted by a function Types of expressions String concatenation Mathematical operations Date and time arithmetic

15. String Concatenation I SELECT 'Product Name ' + ProductName AS NameString � In SQL Server 2005, the concatenation operator is the + (plus sign) You must think about where you want spaces

16. String Concatenation II SELECT 'Retail Price: ' + CAST(RetailPrice AS VARCHAR(10)) AS PriceString � Unlike VB, numeric values do not automatically convert when appended to text CAST and CONVERT must be used See �CAST and CONVERT� in Books Online Transact-SQL Reference

17. Practical Exercise 2 An ER Diagram for SalesOrders appears on page 477 Using your copy of SalesOrders Extract a list of all customer city, state, zip codes in the form (one column)Bellingham, WA 98225 List all order numbers and dates in the formOrder 1 Dated 7/1/1999

18. Mathematical Operations SELECT RetailPrice*0.90 AS PriceLess10pct � The normal set of operators: + - * / Also the modulo operator: % See �Operators� in Books Online Transact-SQL Reference Factors of the equation can be fields, constants, or variables

19. Date and Time Arithmetic SELECT OrderDate + 7 AS ResponseGoal � Simple arithmetic (+, - days) is easy SELECT DATEADD(MONTH,1,OrderDate) AS ResponseDeadline � DATEADD and DATEDIFF functions See �Date and Time Functions� in Books Online Transact-SQL Reference � then follow the links to DATEADD and DATEDIFF

20. Practical Exercise 3 An ER Diagram for SalesOrders appears on page 477 Using your copy of SalesOrders Create a list of the retail values (price � quantity) of all products on hand List all shipping lag times (ship date � order date)

21. NULL Missing or unknown value Not the same as 0, ��, or � � Causes most expressions containing it to return NULL SELECT FirstName + ' ' + ISNULL(MiddleName,'') + ' ' + LastName AS Name �

22. Filtering the Result Set Terms that mean almost the same thing Filter Criterion/criteria Search condition Predicate

23. The Five Basic Predicates Comparison Range: BETWEEN Set membership: IN Pattern match: LIKE Null: IS NULL

24. Comparison SELECT � WHERE STATE = 'WA' Operators: = <> < > <= >= When comparing strings, be aware of the collating sequence

25. Range SELECT � WHERE RetailPrice BETWEEN 100 AND 1000 Means = the first value and = the second Lower value first!

26. Set Membership SELECT � WHERE VendState IN ('WA','OR') Specific set of values Must list each value of set

27. Pattern Match SELECT � WHERE VendCity LIKE '%ville%' Wild cards _ any single character % zero or more characters ESCAPE character Use when string to be matched includes _ or %

28. Null SELECT � WHERE VendCity IS NULL

29. NOT SELECT � WHERE RetailPrice NOT BETWEEN 100 AND 1000 SELECT � WHERE VendState NOT IN ('WA','OR') SELECT � WHERE VendCity NOT LIKE '%ville%� SELECT � WHERE VendCity IS NOT NULL

30. AND and OR All must be true � use AND Any must be true � use OR

31. Order of Precedence*

32. Practical Exercise 4 An ER Diagram for SalesOrders appears on page 477 Using your copy of SalesOrders Create a list of the products ... With retail price 100 or more With retail price less than 100 or more than 600 That are �tire(s)�

33. Next Lecture Security, Recovery, and Data Transfer