Transcript Advanced SQL lecture

COIS20026 Database
Development & Management
Week 6 – Advanced SQL
Prepared by: Angelika Schlotzer
Updated by: Satish Balmuri
Updated by: Tony Dobele
Last Week




Identified the role of SQL in
relational DBMS
Defined the role of the DDL, DML &
DCL components of SQL
Identified whether an SQL statement
belongs to the DDL or DML
Discussed the SQL commands:
CREATE TABLE, DROP TABLE, ALTER
TABLE, CREATE INDEX
2
Last Week (cont’d)
Learned how to define the primary
key of a table using the CREATE
TABLE & CREATE INDEX commands
 Learned the SQL commands: INSERT,
UPDATE, DELETE
 Constructed correct single table SQL
queries with the SELECT command
using, as appropriate, its various
clauses & options

3
This week: Advanced SQL

The objectives for this week are:




be able to construct multiple table
queries using SQL commands
define the term ‘join’
use the concepts of equi-joins, natural
joins, self joins, & outer joins and be
able to use SELECT statements to
construct examples of these joins
be able to use the set operators UNION,
INTERSECT & MINUS in SQL queries
4
Objectives (cont’d)




Explain the difference between
correlated and non-correlated queries
explain the purpose of a user ‘view’
use the SQL VIEW command to create a
user view
use the SQL GRANT & REVOKE
commands
5
Join

A join is a relational operation that
joins 2 tables based on a common
domain into a single table or view

a general rule of thumb is  for every 2 tables being joined, there
will be at least 1 WHERE condition
that matches up common column
values
6
Figure 7-3 revisited: Sample Pine Valley Furniture data
customers
orders
order lines
products
7
Equi-join


An equi-join joins tables based on
the equality between values in the
common columns. Common columns
are shown in the resulting
table/view.
Eg. see sample query & output on
page 335 of text
8
Natural Join

The natural join is identical to the
equi-join except that one of the
duplicate columns is eliminated.
Most commonly used join operation
 eg.
SELECT ITEM.ITEM_NO, DESCRIPTION
FROM ITEM, ORDER_ITEM
WHERE ITEM.ITEM_NO =
ORDER.ITEM_NO

9
Natural Join Example

For each customer who placed an order, what is the
customer’s name and order number?
Join involves multiple
tables in FROM clause



SELECT CUSTOMER_T.CUSTOMER_ID, CUSTOMER_NAME, ORDER_ID
FROM CUSTOMER_T, ORDER_T
WHERE CUSTOMER_T.CUSTOMER_ID = ORDER_T.CUSTOMER_ID
WHERE clause performs the
equality check for common
columns of the two tables
10
Outer Join

In an outer join, rows that do not
have matching values in common are
nevertheless included in the resulting
table.


MS Access allows the use of outer joins
some DBMSs do not permit this join type
11
Outer Join Example




List the customer name, ID number, and order
number for all customers. Include customer
information even for customers that do have an order
SELECT CUSTOMER_T.CUSTOMER_ID, CUSTOMER_NAME, ORDER_ID
FROM CUSTOMER_T, LEFT OUTER JOIN ORDER_T
WHERE CUSTOMER_T.CUSTOMER_ID = ORDER_T.CUSTOMER_ID
LEFT OUTER JOIN
syntax will cause
customer data to appear
even if there is no
corresponding order data
12
Self Join

A self join joins a single table onto
itself



an alias is used as one of the table
names so ambiguity doesn’t occur
study guide gives one example of when
one might use a self join
given a table called TEACHING with the
attributes - Lecturer and Course; one
might wish to know which lecturer
teaches more than one course
13
Self Join (cont’d)
In this case the sample query might be:
SELECT DISTINCT LECTURER
FROM TEACHING, TEACHING T
WHERE TEACHING.LECTURER =
T.LECTURER AND
TEACHING.COURSE != T.COURSE;
 note the use of the alias T so that the
self join can occur

14
Processing Multiple Tables – Joins

Join –
a relational operation that causes two or more tables with a
common domain to be combined into a single table or view

Equi-join –

Natural join –

Outer join –

Union join – includes all columns from each table in the join, and an
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
an equi-join in which one of the duplicate columns is
eliminated in the result table
a 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)
instance for each row of each table
The common columns in joined tables are usually the primary key of the
dominant table and the foreign key of the dependent table in 1:M relationships.
15
Multiple Table Join Example

Assemble all information necessary to create an
invoice for order number 1006Four tables involved in this
join



SELECT CUSTOMER_T.CUSTOMER_ID, CUSTOMER_NAME,
CUSTOMER_ADDRESS, CITY, SATE, POSTAL_CODE, ORDER_T.ORDER_ID,
ORDER_DATE, QUANTITY, PRODUCT_NAME, UNIT_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.PROEUCT_ID = PRODUCT_PRODUCT_ID
AND ORDER_T.ORDER_ID = 1006;
Each pair of tables requires an equality-check
condition in the WHERE clause, matching
primary keys against foreign keys
16
Subqueries




Involve the use of an inner query
within a WHERE or HAVING clause of
an outer query
subqueries can be nested
the inner query provides values for
the search condition of the outer
query
subqueries are enclosed in
parentheses
17
Subquery Example

Show all customers who have placed an order
The IN operator will test to see if
the CUSTOMER_ID value of a
row is included in the list returned
from the subquery


SELECT CUSTOMER_NAME FROM CUSTOMER_T
WHERE CUSTOMER_ID IN
(SELECT DISTINCT CUSTOMER_ID FROM ORDER_T);
Subquery is embedded in
parentheses. In this case it
returns a list that will be
used in the WHERE clause
of the outer query
18
Subqueries (cont’d)



In some cases you can use either a join
or a subquery, in other cases only a join
or subquery will produce the desired
result
Data from subqueries cannot be used in
the final results of the outer query, in
these cases, a join must be included
Joins can also be used within
subqueries
19


Non-correlated & Correlated
Subqueries
Non-correlated - in these queries the
inner query is examined first and
limits the processing of the outer
query
Correlated - these queries use the
result of the outer query to
determine the processing of the
inner query; ie inner query must be
executed for each outer row
20
Non-correlated & Correlated
Subqueries


Figure 8-3 (a) on p. 344 of the text
identifies a non-correlated subquery
Figure 8-3 (b) on p. 345 describes a
correlated query
21
Non-correlated Subquery
Example
As an example, suppose we wished to
know which customers had not placed
any orders (based on the relations from
last week’s activity)
SELECT ID, NAME
FROM CUSTOMER
WHERE ID NOT IN (
SELECT CUST_ID
FROM ORDER);

22
Non-correlated Subquery
Example

Suppose we wish to know which
customers have not placed orders in
June of 2000
SELECT F_NAME, L_NAME
FROM CUSTOMER
WHERE ID NOT IN (
SELECT CUST_ID
FROM ORDERS
WHERE DATE > #30/05/2000# AND
DATE < #1/07/2000#)
23
Figure 8-3(a) –
Processing a
noncorrelated
subquery
No reference to data
in outer query, so
subquery executes
once only
24
Correlated Query Example


(from text p. 344)
List the details about the product with
the highest unit price
SELECT PRODUCT_NAME,
PRODUCT_FINISH, STANDARD_PRICE
FROM PRODUCT_T PA
WHERE STANDARD_PRICE > ALL (
SELECT STANDARD_PRICE FROM
PRODUCT_T PB
WHERE PB.PRODUCT_ID !=
PA.PRODUCT_ID)
25
Figure 8-3(b)
– Processing a
correlated Subquery refers to outer-query data, so executes
subquery once for each row of outer query
26
Views

Views are valuable for a number of
reasons:




simplify query commands
provide data security
enhance programming productivity
Views are virtual tables established
for users based on their information
display needs
27
Views (cont’d)

Only definition of the view is saved
in the database, not the actual table
with the values


the query to produce the view is
executed each time the view is
requested
the view will include any new data
stored in the underlying table(s) since it
was last executed
28
SQL View Command
Suppose that a specific user needs to
identify which customers placed orders
on which dates
 We might create the view:
CREATE VIEW CUST_ORDERS_V AS
SELECT ID, NAME, ORDER_ID, DATE
FROM CUSTOMER, ORDER
WHERE ID = CUST_ID
ORDER BY NAME, DATE

29
Another VIEW example


(from text p. 324)
What is the total of orders placed for each
furniture product? (Bases one view on
another existing view)
CREATE VIEW ORDER_TOTALS_V AS
SELECT PRODUCT_ID, SUM
(STANDARD_PRICE * QUANTITY) AS
TOTAL
FROM INVOICE_V
GROUP BY PRODUCT_ID;
30
More on Views

Data in a base table can be directly
updated from a VIEW unless the
following situations exist


SELECT clause includes keyword
DISTINCT
SELECT clause contains expressions,
including derived columns, aggregates,
statistical functions, etc
31
More on Views



The FROM clause or a subquery or a
UNION clause references more than one
table
the FROM clause or subquery references
another view that is not updateable
the CREATE VIEW command contains an
ORDER BY, GROUP BY or HAVING clause
32
Data Dictionary Facilities

The text book identifies some of the internal
tables that the Oracle7 DBMS stores for database
administrators


Examples in Oracle8i






these can be queried like any table
DBA_TABLES – descriptions of tables
DBA_CONSTRAINTS – description of constraints
DBA_USERS – information about the users of the system
DBA_TAB_PRIVS – descriptions of grants on objects in the
database
In MS Access system tables are hidden by default; you have
to go to Office button / Access options / Current Database
/ Navigation options / check Show System objects
In earlier versions: go to Tools / Options / View tab then
tick System Objects to see them.
33
Triggers & Procedures

Triggers & procedures are stored in
the database & controlled by the
DBMS



ie code to create them stored in one
location & administered centrally
promotes stronger data integrity &
consistency of use
Triggers & procedures consist of
blocks of procedural code
34
Triggers

Stored in the database & executed
automatically whenever the
triggering event occurs;




update, insert or delete
if a condition within a trigger is met,
then prescribed action is taken
can cascade - ie one trigger can set off
another trigger
execute against all applications
accessing the database
35
Triggers (cont’d)

Triggers can be used to:
ensure referential integrity
 enforce business rules
 create audit trails
 replicate tables
 activate one or more procedures

36
Procedures

Procedures are not automatically
executed
stored blocks of code
 need to be called in order to execute
 can be called by a trigger

37
Figure 8-6: Triggers contrasted with stored procedures
Procedures are called explicitly
Triggers are event-driven
Source: adapted from Mullins, 1995.
38
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
39
Grant & Revoke Commands



The owner of a table has full
privileges to the table
The owner can grant or revoke any
of these privileges to other users
granting privileges to other users
should be carefully considered since
the other user can then pass on
these privileges if WITH GRANT
OPTION is included.
40
Grant & Revoke (cont’d)

Privileges which can be granted or
revoked to another user are:







SELECT - can select table data
UPDATE - can update table rows
INSERT - can add new table rows
DELETE - can delete table rows
INDEX - can create/drop table index
ALTER - can alter table structure
ALL - all of the above
41
Summary

Today we investigated:





Joins - equi-join, natural join, outer-join
& self-join
Subqueries - how they are created &
how they can be used to create user
views
Non-correlated & correlated subqueries
Use of Minus, Intersect & Union
Views - their purpose, advantages, and
how to create them
42
Summary (cont’d)



Identified what triggers & procedures
are & why they might be used
explained the purpose of the SQL GRANT
and REVOKE commands
identified some data dictionary facilities
provided by a DBMS
43