Introduction to SQL Mike Burr Michael BurrColorado EDU

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

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 Star. UML

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

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

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.

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)

Data Type Selection Access 2007 Age: tinyint First Name: text/character Birthday: datetime Price: money Sale Time: datetime "Exact" Weight: float/decimal Image: image/binary Unique ID: Auto. Number/Auto. Increment

Customers Table Access 2010: Access SQL: CREATE TABLE Customers( ID AUTOINCREMENT, First. Name text NOT NULL, Last. Name text NOT NULL, Phone text, PRIMARY KEY(ID));

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

Orders Table Access 2010: Access SQL: CREATE TABLE Purchase. Orders( ID AUTOINCREMENT, Order. Time Date. Time NOT NULL, Customer. Id Integer NOT NULL, PRIMARY KEY(ID), FOREIGN KEY (Customer. Id) REFERENCES Customers(ID));

Line Items Table Access 2010: CREATE TABLE Line. Items( ID AUTOINCREMENT, Order. Id Integer NOT NULL, Item. Id Integer NOT NULL, Quantity Integer NOT NULL, Price Money NOT NULL, PRIMARY KEY(ID), FOREIGN KEY(Order. Id) REFERENCES Purchase. Orders(ID), FOREIGN KEY(Item. Id) REFERENCES Items(ID));

Final Models

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.

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

The Northwind Model

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

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

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*';

Operators in Where/Having Clauses Operator Meaning = Equals <> Not equal < Less than <= Less than or equal to > Greater than >= Greater than or equal to in (. . . ) Contained in a set of items like Wildcard match (…) Order of Operations and True if left side and right side are true or True if left side or right side are true not Negates condition

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', 'Gratacos Solsona', 'Axen') and Customers. [First Name] in ('Thomas', 'Christina', 'Martin'); SELECT Customers. ID, Customers. [Last Name], Customers. [First Name] FROM Customers WHERE Customers. [Last Name] in ('Bedecs', 'Gratacos Solsona', 'Axen') or Customers. [First Name] in ('Thomas', 'Christina', 'Martin');

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

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

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';

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';

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.

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)

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

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)

Questions?

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

Add Fields and Create Primary Key Constraint

When Done, Save and Close Table

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

Show Desired Tables

Drag Customer ID to Purchase. Orders Customer. Id field

Done

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

Change to SQL View

Enter DDL for a Table and Run Query

Verify Table was Created

Create Other Tables and Constraints

Done

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

Add Desired Data and Save

Appendix: Insert Data (Access Query)

Change to SQL View and Create INSERT statement

Run the Query to Insert the Data

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

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)

Verify the Cartesian product

Insert the Test Data

Done • Test Data is ready to go, other Cartesian products can be used to populate the other tables • Using existing tables in Cartesian products can be used to satisfy foreign key constraints • Example, use a Cartesian product between the Purchase. Orders table and the Items table (in addition to a couple of others holding test data) to fill in the Line. Items table

Appendix: Update Data (Datasheet View) • Open desired Table and click fields to modify

Appendix: Update Data (SQL) • Create a Query

Change to SQL View and Enter Query Run the Query:

Appendix: Install Northwind Traders

Appendix: Create a Query (Designer) • Create a new query

Select Table(s)

Choose Field(s) and Specify Criteria

Run Query

Review Result

Appendix: Create a Query (SQL) • Create a new query

Open SQL View

Enter Query and Execute

Review Result

Appendix: Creating an ERD with Star. UML • Download and install Star. UML • Download staruml-erd and extract to C: Program Files (x 86)Star. UMLmodules

Launch Star. UML

Create a New Project

Enable the ER Profile

Add a Model

Add an ER Diagram

Create Entities, Attributes, and Relationships

Modify Relationship Properties

Modify Attribute Properties