Transcript Introduction to SQL

Introduction to SQL
Mike Burr
[email protected]
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
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
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:
AutoNumber/AutoIncrement
Customers Table
Access 2010:
Access SQL:
CREATE TABLE Customers(
ID AUTOINCREMENT,
FirstName text NOT NULL,
LastName text NOT NULL,
Phone text,
PRIMARY KEY(ID));
Items Table
Access 2010:
Access SQL:
CREATE TABLE Items(
ID AUTOINCREMENT,
ItemName text NOT NULL,
Description text NOT NULL,
PRIMARY KEY(ID));
Orders Table
Access 2010:
Access SQL:
CREATE TABLE PurchaseOrders(
ID AUTOINCREMENT,
OrderTime DateTime NOT NULL,
CustomerId Integer NOT NULL,
PRIMARY KEY(ID),
FOREIGN KEY (CustomerId) REFERENCES Customers(ID));
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));
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: AdventureWorks
– Comparing AdventureWorks and Northwind
The Northwind Model
Customers Table
Get Everything from the Customers table:
Select * from Customers;
Get a few columns from Customers
SQL:
Result Set:
SELECT Customers.ID,
Customers.[Last Name],
Customers.[First Name]
FROM Customers;
Query Designer:
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 PurchaseOrders
CustomerId 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
PurchaseOrders table and the Items table (in
addition to a couple of others holding test
data) to fill in the LineItems 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
StarUML
• Download and install StarUML
• Download staruml-erd and extract to
C:\Program Files (x86)\StarUML\modules
Launch StarUML
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