Introduction to SQL

1. Introduction to SQL Mike Burr Michael.Burr@Colorado.EDU

2. Rough Outline • A Few Resources • Differentiating Yourself • Creating Tables and Keys • Single Table Queries • Multiple Table Queries • Aggregation • Appendices: • Tutorials for Access • Create a table using the table designer • Create a foreign key constraint using the relationships view • Create tables and FK constraint using Access query • Insert Data (Datasheet View) • Insert Data (Query) • Using Cartesian Products to generate test data • Update Data (Datasheet View) • Update Data (Query) • Install NW Traders DB • Create Query (Access Designer) • Create Query (SQL) • Non-DB Tutorial (For your benefit, but not required) • Create a data model with StarUML

3. A Few Resources • Free Software: www.dreamspark.com • Access 2007 SQL Reference • W3Schools SQL Tutorial/Reference • Microsoft SQL Server T-SQL Reference • SQL Server Books Online

4. Differentiating Yourself(Database Certifications) • Oracle • Microsoft – IT Professional • Microsoft – Office Professional • IBM • Sybase (SAP) • Many others exist inside and outside the database world…

5. Concept Review • Processes give rise to data that needs to be stored and queried. • Actors in the process define the data that needs to be collected and maintained (views). • Each view has multiple entities, attributes, and relationships. All views are combined to form a single data model. • Entities are transformed into database tables, attributes become columns, and relationships usually become foreign keys. • Data is extracted from the database and presented in a meaningful format.

6. Introductory Example: Items/Orders • Represents minimalistic view from order taker • 4 tables, 15 columns, 3 foreign key constraints • 1 attribute can be blank (null) for each customer row • Goal: Create the tables using Access Designer and SQL Data Definition Language (DDL)

7. Data Type Selection

8. Customers Table Access 2010: Access SQL: CREATE TABLE Customers( ID AUTOINCREMENT, FirstName text NOT NULL, LastName text NOT NULL, Phone text, PRIMARY KEY(ID));

9. Items Table Access 2010: Access SQL: CREATE TABLE Items( ID AUTOINCREMENT, ItemName text NOT NULL, Description text NOT NULL, PRIMARY KEY(ID));

10. Orders Table Access 2010: Access SQL: CREATE TABLE PurchaseOrders( ID AUTOINCREMENT, OrderTimeDateTime NOT NULL, CustomerId Integer NOT NULL, PRIMARY KEY(ID), FOREIGN KEY (CustomerId) REFERENCES Customers(ID));

11. Line Items Table Access 2010: CREATE TABLE LineItems( ID AUTOINCREMENT, OrderId Integer NOT NULL, ItemId Integer NOT NULL, Quantity Integer NOT NULL, Price Money NOT NULL, PRIMARY KEY(ID), FOREIGN KEY(OrderId) REFERENCES PurchaseOrders(ID), FOREIGN KEY(ItemId) REFERENCES Items(ID));

12. Final Models

13. Notes • Access tables can be modified in the table designer view (see tutorial appendix) or by creating a query and using an alter table statement.

14. Now: Querying Data • We will be using the example databases provided by Microsoft • Access: Northwind Traders • In the tutorials (not required): • SQL Server 2008: AdventureWorks • Comparing AdventureWorks and Northwind

15. The Northwind Model

16. Customers Table Get Everything from the Customers table: Select * from Customers;

17. Get a few columns from Customers SQL: SELECT Customers.ID, Customers.[Last Name], Customers.[First Name] FROM Customers; Query Designer: Result Set:

18. Narrow Query to Certain Customers Get all customers with last name of ‘Bedecs’ SELECT Customers.ID, Customers.[Last Name], Customers.[First Name] FROM Customers WHERE Customers.[Last Name] = 'Bedecs'; Get all customers with last name starting with B SELECT Customers.ID, Customers.[Last Name], Customers.[First Name] FROM Customers WHERE Customers.[Last Name] like 'B*';

19. Operators in Where/Having Clauses

20. More Examples Using multiple conditions with and/or SELECT Customers.ID, Customers.[Last Name], Customers.[First Name] FROM Customers WHERE Customers.[Last Name] in ('Bedecs', 'GratacosSolsona', 'Axen') andCustomers.[First Name] in ('Thomas', 'Christina', 'Martin'); SELECT Customers.ID, Customers.[Last Name], Customers.[First Name] FROM Customers WHERE Customers.[Last Name] in ('Bedecs', 'GratacosSolsona', 'Axen') orCustomers.[First Name] in ('Thomas', 'Christina', 'Martin');

21. Order By Ascending: SELECT Customers.ID, Customers.[Last Name], Customers.[First Name] FROM Customers ORDER BY Customers.[Last Name] Descending: SELECT Customers.ID, Customers.[Last Name], Customers.[First Name] FROM Customers ORDER BY Customers.[Last Name] DESC

22. Joining Tables • Look at the model: • Want to use foreign keys to get all orders, line items, and products for customer “Anna Bedecs”

23. The Query Select Customers.[First Name], Customers.[Last Name], Orders.[Order Date], [Order Details].[Quantity], [Order Details].[Unit Price], [Order Details].[Quantity] * [Order Details].[Unit Price] as "Line Total", Products.[Product Name] FROM Customers INNER QUERY ( Orders INNER JOIN (Products INNER JOIN [Order Details] on ([Order Details].[Product ID] = Products.ID) ) on (Orders.[Order ID] = [Order Details].[Order ID]) ) on (Customers.ID = Orders.[Customer ID]) WHERE Customers.[First Name] = 'Anna' and Customers.[Last Name] = 'Bedecs';

24. A Cleaner Version (same result) Select Customers.[First Name], Customers.[Last Name], Orders.[Order Date], [Order Details].[Quantity], [Order Details].[Unit Price], [Order Details].[Quantity] * [Order Details].[Unit Price] as "Line Total", Products.[Product Name] FROM Customers, Orders, Products, [Order Details] WHERE Customers.ID = Orders.[Customer ID] and Orders.[Order ID] = [Order Details].[Order ID] and [Order Details].[Product ID] = Products.ID and Customers.[First Name] = 'Anna' and Customers.[Last Name] = 'Bedecs';

25. Counting Orders for Customers Select Orders.[Customer ID], count(Orders.[Order ID]) FROM Orders WHERE Customers.ID = Orders.[Customer ID] This query crashes and burns. We need another tool, aggregation with the group by clause.

26. Aggregation: Group By No Group By: Total Orders SELECT Count(Orders.[Order ID]) FROM Orders; Group By: Orders per Customer SELECT Orders.[Customer ID], Count(Orders.[Order ID]) FROM Orders GROUP BY Orders.[Customer ID]; Other Aggregating Functions: Microsoft Access Optional: Transact-SQL (SQL Server)

27. Aggregation: Having Group By: Orders per Customer SELECT Orders.[Customer ID], Count(Orders.[Order ID]) FROM Orders GROUP BY Orders.[Customer ID] HAVING Count(Orders.[Order ID]) > 5

28. Meaningful Data: Subqueries • One possible use is to use aggregation to create a “table” to use in a join Select Customers.[First Name], Customers.[Last Name], counted.aggcount From Customers INNER JOIN (SELECT Orders.[Customer Id], Count(Orders.[Order ID]) as aggcount FROM Orders GROUP BY Orders.[Customer ID] HAVING Count(Orders.[Order ID]) > 5) as counted on (counted.[Customer ID] = Customers.ID)

29. Questions?

30. Appendix: Create Tables Using Table Designer • Create a blank database and create a new table in design view

31. Add Fields and Create Primary Key Constraint

32. When Done, Save and Close Table

33. Appendix: Create Foreign Key Constraints using Access Designer • After Creating the Tables, Open the Relationships View

34. Show Desired Tables

35. Drag Customer ID to PurchaseOrdersCustomerId field

36. Done

37. Appendix: Create Tables and Foreign Key Constraints in Access 2010 SQL • Create a blank database and create a new query:

38. Change to SQL View

39. Enter DDL for a Table and Run Query

40. Verify Table was Created

41. Create Other Tables and Constraints

42. Done

43. Appendix: Insert Data (Access Datasheet View) • Open Desired Table

44. Add Desired Data and Save

45. Appendix: Insert Data (Access Query)

46. Change to SQL View and Create INSERT statement

47. Run the Query to Insert the Data

48. Appendix: Getting Test Data with Cartesian Products • The Basics: • A cartesian product results from a select statement using 2 or more tables without a join condition. • This causes the RDBMS to return all of the combinations of the rows in the 2 (or more tables) • This can be combined with an INSERT INTO statement to populate test data for queries • I will be generating test first names and last names for the customers table

49. First Step • I have created 2 tables with 1 column each (matching the data type on one of the columns the data type of the column that I want to populate with test data)

50. Verify the Cartesian product