Ch 9 - Structured Query Language
SQL - Structured Query Language -SQL-92; SQL3; SQLPlus; SQL Server; …
Differences between SQL versions
***Use the SQL structure demonstrated in textbook on Quizzes / Exams ***
Advantages of SQL -
Standard across relational database (some object-oriented)
Standard for programmer to learn (low training costs in switching from Access to Oracle)
Speed when used with telecommunications (256 characters long)
Different types of SQL language -
Data Definition language (DDL) - commands used to define the database (chapter 15)
Data Manipulation Language (DML) - used to manipulate data and create information
Data Control Language (DCL) - used to control the database (privileges/saving changes)
***We are focusing on Data Manipulation Language which most of you will use.
Naming Conventions/Command Line Structure -
Same as before - NO spaces, operators, reserved words, dashes, starting with numbers
SQL commands are capitalized
Brackets [ ] denote contents of a list
Parentheses ( ) denote order of operations and functions; like VB
Lowercase or a combination of Lower and Upper case note user supplied data or values
Separate parts of a command with a comma ,
Text is denoted with single quotes ' '
Creating extracts or information - SELECTing data which meets a specific criteria
Look at the SELECT commands in the textbook pg. 221
SELECT - What columns or data
FROM - What table or entity
WHERE - What criteria? Data which matches specific criteria
SELECT Name, Age
FROM STUDENT
WHERE Manor = 'Math'
Put sample data from movie library on board…
Together - list movie titles that are family films
What would the results look like?
SELECT Movie_Title
FROM Movie_t
WHERE Category_ID = 'Family'
Results -
It's a Bugs Life
Bambi
Toy Story
Individually - list Movie Title and Category which are rated 'G'
SELECT Movie_Title, Category
FROM Movie_t
WHERE Rating = 'G'
Results -
It's a Bugs Life Family
Bambi Family
Toy Story Family
If we want all the fields and we are doing a SINGLE table extract, use the * (wildcard)
SELECT *
FROM Movie_t
WHERE Rating = 'G'
Results -
001 It's a Bugs Life Family G 15.99 06-Jan-96
002 Bambi Family G 12.99 05-Jun-80
003 Toy Story Family G 17.99 02-May-90
Together - how would we change the family films extract to pull all attributes?
SELECT *
FROM Movie_t
WHERE Category_ID = 'Family'
What are functions? COUNT, MIN, MAX, SUM, AVG (must meet the criteria)
Distribute copies of the data found on pg 330 of the Database Implementation textbook.
Write down what you think the results are given the data distributed.
SELECT COUNT (*) AS TotalQty ' the * means not a specified field (includes nulls)
FROM Product_T
WHERE On-Hand = 5
…
WHERE Product_Finish = 'Natural Ash'
SELECT COUNT(Product_Description) AS TotWProdDesc ' Count values which are not NULL
FROM Product_T
SELECT MAX(Unit Price) AS MaxNatAsh
FROM Product_T
WHERE Product_Finish = 'Natural Ash'
SELECT MAX(Unit_Price) AS MaxOnHand
FROM Product_T
WHERE On_Hand > 3 ' Operators = > and !=Not Equal
SELECT Product_Name
FROM Product_T
WHERE Unit_Price = MAX(Unit_Price)
‘*** NOT VALID MUST USE NESTED QUERY ***
In-Class Quiz - Individually answer the following questions -
1) How many Family movies are there?
2) What is the average price of a movie?
3) What is the average price of movies priced over $10?
Answers -
1) SELECT COUNT(Movie_Title) AS TotalFamily
FROM Movie_t
WHERE Category = ‘Family’
Results -
3
2) SELECT Avg(Movie_PPrice) AS AvgMoviePrice
FROM Movie_t
Results -
15.76
3) SELECT AVG(Movie_Title) AS AvgPrice
FROM Movie_t
WHERE Movie_PPrice >10
What are Boolean Operators? AND OR NOT (used to further define criteria)
Write down what you think the results are given the distributed data.
SELECT Product_ID, Product_Name
FROM Product_T
WHERE Unit_Price > 300 AND On_Hand > 0
SELECT *
FROM Product_T
WHERE Unit_Price >=250 AND Unit_Price 0
What order are they sorted in? The order they appear in the table - typically by Order ID
To use a user specified sort, use the WHERE clause…
SELECT Product_Name, On_Hand
FROM Product_T
WHERE On-Hand > 0
ORDER BY On_Hand ' lowest to highest
ORDER BY On_Hand DESC ' highest to lowest
ORDER BY On_Hand DESC, Product_Name
' highest to lowest and alphabetical
What if we wanted to find out how many times each Product has been sold?
Which table will tell us Product IDs as they appear on Orders? Order_Line_T
SELECT COUNT(Product_ID) ' doesn't matter what you count as long as not NULL
FROM Order_Line_T
What does this give us? Total records in the Order_Line_T that have Order_IDs
We want it by Product ID …
SELECT Product_ID, COUNT(Product_ID)
FROM Order_Line_T
GROUP BY Product_ID
HAVING - like the WHERE clause but happens after GROUPs are created
i.e. Show ONLY Product Ids which have appeared on more than 3 Orders
SELECT Product_ID, COUNT(Product_ID)
FROM Order_Line_T
GROUP BY Product_ID
HAVING COUNT(Product_ID) > 3
In-Class Quiz - individually answer the following questions -
1) Which categories are available to be assigned to movies?
2) Which categories have movies already assigned to them?
3) Show all the movies we have and display them alphabetically by rating
4) How many movies for each rating assigned to a movie do we have?
5) Only show those ratings with one movie assigned to them.
Answers -
1) SELECT Category_ID
FROM Category_t
2) SELECT Category_ID
FROM Movie_t
3) SELECT *
FROM Movie_t
ORDER BY Rating_ID
4) SELECT Rating_ID, COUNT(Movie_ID)
FROM Movie_t
GROUP BY Rating_ID
5) SELECT Rating_ID, COUNT(Movie_ID)
FROM Movie_t
GROUP BY Rating_ID
HAVING COUNT(Movie_ID) = 1
***************Add Nested Queries******************
Multiple table queries/extracts
Looking at the distributed data, what if we wanted to find out…
Which Order # contains Products with a finish of 'Cherry'? We have an over-stock.
Nested Query
SELECT Order_ID, Product_ID
FROM Order_Line_T
WHERE Product_ID IN
(SELECT Product_ID
FROM Product_T
WHERE Product_Finish = 'Cherry')
OR, Join tables together
The FROM statement needs to include all tables required to obtain the information
FROM Product_T, Order_Line_T
The SELECT statement needs to list the fields and designate which table they come from
SELECT Order_Line_T.Order_ID, Product_T.Product_ID, Product_T.Product_Finish
The WHERE statement not only includes any criteria but also the linking information
WHERE Product_T.Product_ID = Order_Line_T.Product_ID
AND Product_T.Product_Finish = 'Cherry'
Using Aliases to condense the SQL statements,
SELECT O.Order_ID, P.Product_ID, P.Product_Finish
FROM Product_T P, Order_Line_T O
WHERE P.Product_ID = O.Product_ID
AND P.Product_Finish = 'Cherry'
This is considered a Natural Join.
In-Class Quiz - What is a Natural Join? What are the two other kinds of joins?
Natural Join - values must exist in both tables and only show one linking field value
Equi-Join - same as Natural except 'Product_ID' would be displayed twice
Add Order_Line_T.Product_ID to the above SELECT statement
Outer-Join - include all the records from one table even if there is not a match
Look at bottom of page 356 and top of page 357
Customers without orders still appear
When would you use an Outer-Join? List of Customers who have never placed and order
SELECT Customer_ID
FROM Customer_T LEFT JOIN Order_T
ON Customer_ID = Customer_ID
WHERE Order_T.Customer_ID IS NULL;
Change the SQL statement to show all Customers who have not ordered since 11/1/98
WHERE Order_T.Order_Date > '1-Nov-98' ;
In-Class Quiz - individually answer the following questions -
1) Which ratings have not been assigned to a movie?
2) Which actors have been assigned to a movie?
Answers -
1) SELECT Rating_t.Rating_ID
FROM Rating_t RIGHT JOIN Movie_t ON Rating_ID = Rating_ID
WHERE Movie_t.Rating_ID IS NULL
2) SELECT Assign_t.Actor_ID
FROM Assign_t, Actors_t
WHERE Assign_t.Actor_ID = Actors_t.Actor_ID
CREATE VIEW (virtual table) - SQL statement not covered in Chapter 9, useful on Project 2
Advantages -
1) Commonly used queries don't have to be executed each time
2) Can secure particular data (Employee table isolate SSN)
3) Break down complex queries into manageable pieces
CREATE VIEW Prod_Cherry_V AS
SELECT O.Order_ID, P.Product_ID, P.Product_Finish
FROM Product_T P, Order_Line_T O
WHERE P.Product_ID = O.Product_ID
SELECT Order_ID, Product_ID
FROM Prod_Cherry_V
WHERE Product_Finish = 'Cherry'
WHERE Product_Finish = 'Natural Ash'
SELECT commands are used to extract information
SQL commands needed to manipulate data
Inserting data into you tables -
Be sure to follow the structure defined in your Physical Model
Insert a single line of data per command
INSERT INTO Product_T
(Product_ID, Product_Name, Product_Finish, Product_Description, Unit_Price)
VALUES ('123', 'Chair', 'Cherry', 'Nice Cherry Chair', 12)
OR don't list the fields and input data in the same order the columns appear.
INSERT INTO Product_T
VALUES('123', …
To remove data from a table,
DELETE Product_T
WHERE Product_ID = '123'
Modifying the existing data, Products get a 10% increase
UPDATE Product_T
SET Unit_Price = Unit_Price * 1.10
OR selected products, …
WHERE Product_Finish = 'Cherry'
InClass – FiredUP small group – page 254