Chapter 8: Advanced SQL

1
Chapter 8Advanced SQL
2
Objectives

• Definition of terms
• Write multiple table SQL queries
• Define and use three types of joins
• Write correlated and noncorrelated subqueries
• Establish referential integrity in SQL
• Understand triggers and stored procedures
• Discuss SQL1999 standard and its extension of
  SQL-92

3
Processing Multiple TablesJoins

• Joina relational operation that causes two or
  more tables with a common domain to be combined
  into a single table or view
• Equi-join (inner join) a join in which the
  joining condition is based on equality between
  values in the common columns common columns
  appear redundantly in the result table
• Natural joinan equi-join in which one of the
  duplicate columns is eliminated in the result
  table automatically look for common column so
  you dont need to specify join condition
• Outer joina join in which rows that do not have
  matching values in common columns are nonetheless
  included in the result table (as opposed to inner
  join, in which rows must have matching values in
  order to appear in the result table)
• Cross joinperform a Cartesian product on the
  rows of two tables

The common columns in joined tables are usually
the primary key of the dominant table and the
foreign key of the dependent table in 1M
relationships
4
The following slides create tables for this
enterprise data model
5
Figure 8-1 Pine Valley Furniture Company Customer
and Order tables with pointers from customers to
their orders
These tables are used in queries that follow
6
3 ways of writing join statement

• Use where clause to match common columns (without
  explicit keyword join, traditional way)
• Joinon commands embedded in from clause
• Joinusing embedded in from clause (only if the
  attribute has the same column name in two tables)

7
Equi-join (inner join)

• List all customers who has placed orders
• SELECT CUSTOMER_T.CUSTOMER_ID,
  ORDER_T.CUSTOMER_ID, CUSTOMER_NAME, ORDER_ID
• FROM CUSTOMER_T, ORDER_T
• WHERE CUSTOMER_T.CUSTOMER_IDORDER_T.CUSTOMER_ID
  
• Note that equivocal columns must be qualified by
  table name
• The system first generate a Cartesian join (cross
  join), then remove unmatched rows

8
JoinOn and JoinUsing

• SELECT CUSTOMER_T.CUSTOMER_ID, ORDER_T.CUSTOMER_ID
  , CUSTOMER_NAME, ORDER_ID
• FROM CUSTOMER_T INNER JOIN ORDER_T ON
• CUSTOMER_T.CUSTOMER_IDORDER_T.CUSTOMER_ID
• SELECT CUSTOMER_T.CUSTOMER_ID, ORDER_T.CUSTOMER_ID
  , CUSTOMER_NAME, ORDER_ID
• FROM CUSTOMER_T INNER JOIN ORDER_T USING
• CUSTOMER_ID

Note from Fig. 1, you see that only 10 Customers
have links with orders. ? Only 10 rows will be
returned from this INNER join.
9
Natural Join Example

• For each customer who placed an order, what is
  the customers name and order number?
• SELECT CUSTOMER_ID, CUSTOMER_NAME, ORDER_ID
• FROM CUSTOMER_T NATURAL JOIN ORDER_T

10
Cross join

• Perform a Cartesian product on the two tables
• Select
• From CUTOMER Cross Join ORDER
• Is equivalent to
• Select
• From CUTOMER, ORDER

11
Outer Join Example (Microsoft Syntax)

• List the customer name, ID number, and order
  number for all customers. Include customer
  information even for customers that do not have
  an order
• SELECT CUSTOMER_T.CUSTOMER_ID, CUSTOMER_NAME,
  ORDER_ID
• FROM CUSTOMER_T, LEFT OUTER JOIN ORDER_T
• ON CUSTOMER_T.CUSTOMER_ID ORDER_T.CUSTOMER_ID

Unlike INNER join, this will include customer
rows with no matching order rows
12
Results
Unlike INNER join, this will include customer
rows with no matching order rows
13
Outer Join Example (Microsoft Syntax)

• List the customer name, ID number, and order
  number for all orders. Include order information
  even there is no customer data available for that
  order.
• SELECT CUSTOMER_T.CUSTOMER_ID, CUSTOMER_NAME,
  ORDER_ID
• FROM CUSTOMER_T, RIGHT OUTER JOIN ORDER_T
• ON CUSTOMER_T.CUSTOMER_ID ORDER_T.CUSTOMER_ID

14
Outer join

• Left outer join all rows from table on the left
  hand side will be returned, regardless of whether
  there is a matching record, Null values will be
  inserted into unmatched records for columns from
  the other table
• Right outer join table on the right hand side
• Full outer join rows from both sides will be
  returned regardless of matching, with Null values
  inserted for the columns from the other table

15
Multiple Table Join Example

• Assemble all information necessary to create an
  invoice for order number 1006
• SELECT CUSTOMER_T.CUSTOMER_ID, CUSTOMER_NAME,
  CUSTOMER_ADDRESS, CITY, SATE, POSTAL_CODE,
  ORDER_T.ORDER_ID, ORDER_DATE, QUANTITY,
  PRODUCT_DESCRIPTION, STANDARD_PRICE,
  (QUANTITY UNIT_PRICE)
• FROM CUSTOMER_T, ORDER_T, ORDER_LINE_T, PRODUCT_T
• WHERE CUSTOMER_T.CUSTOMER_ID
  ORDER_LINE.CUSTOMER_ID AND ORDER_T.ORDER_ID
  ORDER_LINE_T.ORDER_ID
• AND ORDER_LINE_T.PRODUCT_ID
  PRODUCT_PRODUCT_ID
• AND ORDER_T.ORDER_ID 1006

16
Figure 8-2 Results from a four-table join
17
Processing Multiple Tables Using Subqueries

• Subquery (or nested query)placing an inner query
  (SELECT statement) inside an outer query
• Options
• In a condition of the WHERE clause
• As a table of the FROM clause
• Within the HAVING clause
• Subqueries can be
• Noncorrelatedexecuted once for the entire outer
  query
• Correlatedexecuted once for each row returned by
  the outer query

18
Subquery and join

• What is the name and address of the customer who
  placed order 1008?
• SELECT CUSTOMER_NAME, CUSTOMER_ADDRESS
• FROM CUSTOMER_T, ORDER_T
• WHERE CUSTOMER_T.CUSTOMER_IDORDER_T.CUSTOMER_
  ID AND ORDER_ID1008
• SELECT CUSTOMER_NAME, CUSTOMER_ADDRESS
• FROM CUSTOMER_T WHERE CUSTOMER_T.CUSTOMER_ID
• (SELECT ORDER_T.CUSTOMER_ID FROM ORDER_T WHERE
  ORDER_ID1008)

19
Subquery Example

• Show all customers who have placed an order
• SELECT CUSTOMER_NAME FROM CUSTOMER_T
• WHERE CUSTOMER_ID IN
• (SELECT DISTINCT CUSTOMER_ID FROM ORDER_T)

20
Subquery example

• Which customers have not placed any orders for
  computer desks?
• SELECT CUSTOMER_NAME FROM CUSTOMER_T
• WHERE CUSTOMER_ID NOT IN
• (SELECT CUSTOMER_ID FROM ORDER_T, ORDER_LINE_T,
  PRODUCT_T
• WHERE ORDER_T.ORDER_IDORDER_LINE_T.ORDER_ID
• AND ORDER_LINE_T.PRODUCT_IDPRODUCT_T.PRODUCT_ID
• AND PRODUCT_DESCRIPTIONCOMPUTER DESK)

21
Correlated vs. Noncorrelated Subqueries

• Noncorrelated subqueries
• Do not depend on data from the outer query
• Subquery execute once for the entire outer query
• Processed inside out, result of inner query used
  to limit processing of outer query
• Correlated subqueries
• Make use of data from the outer query
• Subquery execute once for each row of the outer
  query
• Can use the EXISTS operator
• Processed outside in, result of outer query used
  to limit processing of inner query

22
Figure 8-3a Processing a noncorrelated subquery
No reference to data in outer query, so subquery
executes once only

• The subquery executes and returns the customer
  IDs from the ORDER_T table
• The outer query on the results of the subquery

These are the only customers that have IDs in the
ORDER_T table
23
Correlated Subquery Example

• Show all orders that include furniture finished
  in natural ash
• SELECT DISTINCT ORDER_ID FROM ORDER_LINE_T
• WHERE EXISTS
• (SELECT FROM PRODUCT_T
• WHERE PRODUCT_ID ORDER_LINE_T.PRODUCT_ID
• AND PRODUCT_FINISH Natural ash)

24
Figure 8-3b Processing a correlated subquery
Subquery refers to outer-query data, so executes
once for each row of outer query
Note only the orders that involve products with
Natural Ash will be included in the final results
25
Correlated subquery example

• List the details about the product with the
  highest unit price
• SELECT PRODUCT_DESCRIPTION, PRODUCT_FINISH,
  STANDARD_PRICE
• FROM PRODUCT_T PA
• WHERE STANDARD_PRICEgtALL
• (SELECT STANDARD_PRICE FROM PRODUCT_T PB
• WHERE PB.PRODUCT_ID!PA.PRODUCT_ID)

26
Subquery Example derived table

• Subquery is nested in a from clause
• Show all products whose standard price is higher
  than the average price
• SELECT PRODUCT_DESCRIPTION, STANDARD_PRICE,
  AVGPRICE
• FROM
• (SELECT AVG(STANDARD_PRICE) AVGPRICE FROM
  PRODUCT_T) AS TEMP,
• PRODUCT_T
• WHERE STANDARD_PRICE gt AVGPRICE

27
Union and Union all

• Union horizontally combines results of queries,
  eliminate redundant rows
• Union all does not eliminate redundant rows
• SELECT from customer where state in ('ca','fl')
• union
• select from customer where postal_code like
  '9'
• SELECT from customer where state in ('ca','fl')
• Union all
• select from customer where postal_code like
  '9'

28
Union Queries
create another column With content smallest
quantity, Title QUANTITY

• Who place the order which has largest product
  quantity and smallest product quantity?

29
Conditional Expressions Using Case Syntax

• This is available with newer versions of SQL,
  previously not part of the standard

30
Example of Case syntax

• Add a field of product line indicator
• select product_id, product_description,
• (case
• when product_line_id1 then 'In Product
  Line 1'
• when product_line_id2 then 'In Product
  Line 2'
• else 'Not in Prduct Line 1 or 2'
• end) as line_indicator
• from product

31
Ensuring Transaction Integrity

• Transaction A discrete unit of work that must
  be completely processed or not processed at all
• May involve multiple updates
• If any update fails, then all other updates must
  be cancelled
• SQL commands for transactions
• BEGIN TRANSACTION/END TRANSACTION
• Marks boundaries of a transaction
• COMMIT
• Makes all updates permanent
• ROLLBACK
• Cancels updates since the last COMMIT

32
Transaction

• Typical example transfer 1000 from person As
  bank account to person Bs bank account
• Step 1 check balance of account A is greater
  than 1000
• Step 2 withdraw 1000 from As account
• Step 3 deposit 1000 to Bs account
• The three steps must all be executed successful,
  or roll back to original status if error occurs
  in any step

33
Figure 8-5 An SQL Transaction sequence (in
pseudocode)
34
Data Dictionary Facilities

• System tables that store metadata
• Users usually can view some of these tables
• Users are restricted from updating them
• Some examples in Oracle 10g
• DBA_TABLESdescriptions of tables
• DBA_CONSTRAINTSdescription of constraints
• DBA_USERSinformation about the users of the
  system
• Examples in Microsoft SQL Server 2005
• SYSCOLUMNStable and column definitions
• SYSDEPENDSobject dependencies based on foreign
  keys
• SYSPERMISSIONSaccess permissions granted to
  users

35
Data dictionary Examples

• SQL SERVER 2000 query user account information,
  including user name, creation date, update date,
  password
• SELECT FROM SYSUSERS
• MySQL
• Select from mysql.users
• SELECT FROM information_schema.TABLES T

36
SQL1999 and SQL2003 Enhancements/Extensions

• Persistent Stored Modules (SQL/PSM)
• Capability to create and drop code modules
• New statements
• Flow control CASE, IF, LOOP, FOR, WHILE, etc.
• Makes SQL into a procedural language
• Oracle has propriety version called PL/SQL, and
  Microsoft SQL Server has Transact/SQL

37
Routines and Triggers

• Routines
• Program modules that execute on demand
• Functionsroutines that return one values and
  take only input parameters
• Proceduresroutines that can take both input and
  output parameters
• Triggers
• Routines that execute automatically in response
  to a database event (INSERT, UPDATE, or DELETE)
• Used to ensure referential integrity, enforce
  business rules, create audit trails, replicate
  tables, or activate a procedure

38
Figure 8-6 Triggers contrasted with stored
procedures
Procedures are called explicitly
Triggers are event-driven
Source adapted from Mullins, 1995.
39
Figure 8-7 Simplified trigger syntax, SQL2003

• Example Inventory manage wants to know the price
  change history (through a table Price_Updates_T)
• CREATE TRIGGER STANDARD_PRICE_UPDATE
• AFTER UPDATE OF STANDARD_PRICE ON PRODCUT_T
• FOR EACH ROW
• INSERT INTO PRICE_UPDATES_T VALUES
  (PRODUCT_DESCRIPTION, SYSDATE, STANDARD_PRICE)

40
Figure 8-8 Create routine syntax, SQL2003

• We have added another column SALE_PRICE to
  PRODUCT_T, and want to set the values for it
• CREATE OR REPLACE PROCEDURE PRODUCT_LINE_SALE AS
• BEGIN
• UPDATE PRODUCT_T SET SALE_PRICE
  .90STANDARD_PRICE WHERE STANDARD_PRICEgt400
• UPDATE PRODUCT_T SET SALE_PRICE.85STANDARD_PRICE
  WHERE STANDARD_PRICElt400
• END

41
Embedded and Dynamic SQL

• Embedded SQL
• Including hard-coded SQL statements in a program
  written in another language such as C or Java
• Dynamic SQL
• Ability for an application program to generate
  SQL code on the fly, as the application is
  running
• Programmers write to API (ODBC, JDBC), then
  passed through to database