MIS480 4
Please check chapter 6
Introduction to SQL Queries
Chapter 6
Lesson content
• What is SQL?
• How to learn and Practice SQL?
• Creating Tables in SQL
• Four tables
• Linking tables
• Referential Integrity controls
• Operations on Tables:
• Changing Tables
•
Removing Tables
• Adding records
• Deleting Records
• Updating Records
2
• Viewing Records using SELECT:
• All Records
• Specific Columns
• Selective records using WHERE
• Functions:
• Count(*)
• AND, OR, NOT
• Parenthesis
• ORDER BY
• GROUP BY
• HAVING
• Alias names AS
• Creating views with CREATE VIEW
3
SQL Data Types
The following slides create tables for this enterprise data model
4
5
Figure 6-5 General syntax for CREATE TABLE statement used in data
definition language
Example:
Let us see the demonstration in MS Access
The following slides create tables for this enterprise data model
7
Defining attributes and their data types
8
Order Table
:
9
Order Table
Now your turn: Try to write the code to Create Order Line
11
Data Integrity Controls
•Referential integrity –constraint that ensures that
foreign key values of a table must match primary key
values of a related table in 1:M relationships
•Restricting:
• Deletes of primary records
• Updates of primary records
• Inserts of dependent records
12
13
Relational integrity is
enforced via the
primary-key to
foreign-key match
Figure 6-7 Ensuring data integrity through updates
Changing Tables
• ALTER TABLE statement allows you to change column specifications:
• Table Actions:
• Example (adding a new column with a default value):
14
Removing Tables
•DROP TABLE statement allows you to remove (Delete)
tables from your schema:
•DROP TABLE CUSTOMER_T
• Note that MS Access as a good DBMs will not allow you to delete the Customer_T
because it is in a relationship with Order_T. You have to Delete Order_T first!
15
Delete Statement
•Removes rows from a table
•Delete certain rows
• DELETE FROM CUSTOMER_T WHERE CUSTOMERSTATE =
‘HI’;
•Delete all rows
• DELETE FROM CUSTOMER_T;
• This is different from drop, because this only deletes
the content but keeps the table.
16
Insert Statement
• Adds one or more rows to a table
• Inserting into a table
• Inserting a record that has some null attributes requires identifying the fields that
actually get data
• Inserting from another table
17
Update Statement
•Modifies data in existing rows: using UPDATE….SET
19
SELECT Statement
• Used for queries on single or multiple tables
• Clauses of the SELECT statement:
• SELECT
• List the columns (and expressions) to be returned from the query
• FROM
• Indicate the table(s) or view(s) from which data will be obtained
• WHERE
• Indicate the conditions under which a row will be included in the result
• GROUP BY
• Indicate categorization of results
• HAVING
• Indicate the conditions under which a category (group) will be included
• ORDER BY
• Sorts the result according to specified criteria
20
SELECT Example
• SELECT is a statement that allows us to ‘search’, ‘sort’, ‘organize’, and
‘view’ the content of one or more tables.
• SELECT is what we have been doing when using MS Access queries.
• SELECT is the SQL way of saying ‘Show me’.
• The symbol * means ‘everything’ in SQL.
Example:
SELECT * FROM Sales_Table;
This means: ‘show me’ ‘everything’ FROM the Sales_Table;
21
Lets try it
SELECT Example
• Instead of * (everything) you could specify the exact columns and ther
order of columns you want to see.
Example:
SELECT Customer_ID, Sale_Amount FROM
Sales_Table;
This means: ‘show me’ just Customer_ID & Sale_Amount ’ FROM the
Sales_Table;
23
Lets try it
SELECT Example with WHERE
• WHERE is a way to set a condition.
• Example: Find products WHERE the standard price less than $
27
5
25
Lets try it
Before
After
Note: This does not change the table, it just gives us a view of the information we want. Exactly what we used to do in Queries.
SELECT Example Using a Functions
• A Function is an calculation that you want the system
to do. Functions could be anything such as:
• COUNT,
• SUM,
• AVG (average)
• MIN, MAX,
• STDEV( standard Deviation)
• VAR (Variable)
• FIRST, LAST,
• EXPRESSION (Formula)
• WHERE (Condition).
• We already saw the Function: ‘WHERE’.
27
SELECT Example Using a Functions
• Using the COUNT aggregate function to find totals
• SELECT COUNT(*) FROM Sales_Table;
• Note: With aggregate functions you can’t have single-valued columns
included in the SELECT clause, unless they are included in the GROUP BY
clause.
SELECT Example–Boolean Operators
• AND, OR, and NOT Operators for customizing conditions in WHERE clause
29
Note: The LIKE operator allows you to compare strings using wildcards. For example, the %
wildcard in ‘%Desk’ indicates that all strings that have any number of characters preceding the
word “Desk” will be allowed. So it will accept: Office Desk, Home Desk, Casual Desk, Dinning
Desk…etc.
30
Figure 6-8 Boolean query A without use of parentheses
By default, processing
order of Boolean
operators is NOT, then
AND, then OR
SELECT Example–Boolean Operators
• Warning parentheses change everything, they override the
normal precedence of Boolean operators
• We always start with the parentheses.
31
With parentheses, you can override normal precedence rules. In this case parentheses
make the OR take place before the AND.
32
Figure 6-9 Boolean query B with use of parentheses
Sorting Results with ORDER BY Clause
• Sort the results first by STATE, and within a state by the CUSTOMER NAME
33
Note: The IN operator in this example allows you to include rows whose
CustomerState value is either FL, TX, CA, or HI. It is more efficient than separate OR
conditions.
Sorting Results with ORDER BY Clause
• We can view the results by sorting them.
• SELECT * FROM Sales_Table ORDER BY Sale_Amount;
34
Sorting Results with ORDER BY Clause
• By default SQL sorts them Ascending. We can change it to
Descending. DESC
• SELECT * FROM Sales_Table ORDER BY Sale_Amount DESC;
35
Categorizing Results Using GROUP BY Clause
• Can only be used with aggregate functions
• Scalar aggregate: single value returned from SQL query with aggregate function,
Example the ‘Count’ we just did.
• Vector aggregate: multiple values returned from SQL query with aggregate function
(via GROUP BY)
You can use single-value fields with aggregate functions if they are included in the
GROUP BY clause
36
Categorizing Results Using GROUP BY
Clause
• SELECT Customer_ID, SUM(Sale_Amount) FROM
Sales_Table GROUP BY Customer_ID;
37
Before
After
Qualifying Results by Categories
Using the HAVING Clause
• Use HAVING if we are using GROUP BY; otherwise you can just use
WHERE.
Like a WHERE clause, but it operates on groups (categories), not on
individual rows. Here, only those groups with total numbers greater than
1 will be included in final result.
38
Categorizing Results Using HAVING Clause
SELECT Customer_ID, SUM(Sale_Amount)
FROM Sales_Table
GROUP BY Customer_ID
HAVING SUM(Sale_Amount) > 80;
39
Before
SELECT Example Using Alias
Alias is an alternative column or table name
SELECT CUST.CUSTOMERNAME AS NAME,
CUST.CUSTOMERADDRESS
FROM CUSTOMER_V AS CUST
WHERE NAME = ‘Home Furnishings’;
40
Lets try Alias for a previous example
• Before:
• After
Using and Defining Views
• Views provide users controlled access to tables
• Base Table–table containing the raw data
• Dynamic View
• A “virtual table” created dynamically upon request by a user
• No data actually stored; instead data from base table made available to user
• Based on SQL SELECT statement on base tables or other views
• Materialized View
• Copy or replication of data
• Data actually stored
• Must be refreshed periodically to match corresponding base tables
42
Sample CREATE VIEW
43
View has a name.
View is based on a SELECT statement.
CHECK_OPTION works only for updateable views and prevents
updates that would create rows not included in the view.
Advantages of Views
• Simplify query commands
• Assist with data security (but don’t rely on views for security, there are
more important security measures)
• Enhance programming productivity
• Contain most current base table data
• Use little storage space
• Provide customized view for user
• Establish physical data independence
44
Disadvantages of Views
•Use processing time each time view is referenced
•May or may not be directly updateable
45
46
Sequence SQL engine
reads SQL Statements: