Transcript product

Chapter 7
Introduction to
Structured Query Language (SQL)
Database Systems: Design, Implementation, and Management
Peter Rob & Carlos Coronel
In this chapter, you will learn:
 The basic commands and functions of SQL
 How SQL is used for data manipulation (to add,
modify, delete, and retrieve data)
 How to use SQL to query a database to extract
useful information
 How SQL is used for data administration (to
create tables, indexes, and views)
 About more advanced SQL features such as
updatable views, stored procedures, and triggers
Introduction to SQL
Ideal database language
Create
database and table structures
Perform basic data management chores (add,
delete, and modify)
Perform complex queries to transform data
into useful information
Introduction to SQL
SQL (Structured Query Language)
meets ideal database language requirements:
 SQL
coverage fits into two categories:

Data definition language (DDL)
– Includes commands to:
» Create database objects, such as tables, indexes, and views
» Define access rights to those database objects

Data manipulation language (DML)
– Includes commands to insert, update, delete, and retrieve data
within the database tables
Introduction to SQL
SQL (Structured Query Language)
meets ideal database language requirements:
 SQL
is a Nonprocedural language
 SQL
is relatively easy to learn.
 ANSI
prescribes a standard SQL.

SQL2 : SQL-92

SQL3 : SQL-98/99
support object-oriented data management
SQL Data Definition Commands
Data Manipulation Commands
Data Definition Commands
The Database Model
 Simple
Database -- PRODUCT and VENDOR tables
 Each product is supplied by only a single vendor.
 A vendor may supply many products.
Data Definition Commands
The Tables and Their Components
The
VENDOR table contains vendors who are
not referenced in the PRODUCT table.
PRODUCT is optional to VENDOR.
Some
vendors have never supplied a product
( 0,N )
Data Definition Commands
The Tables and Their Components
Existing
V_CODE values in the PRODUCT
table must have a match in the VENDOR
table.
A few
products are supplied factory-direct, a
few are made in-house, and a few may have
been bought in a special warehouse sale. That
is, a product is not necessarily supplied by a
vendor.
VENDOR is optional to PRODUCT.
( 0,1 )
The Database Model
The Database Model
The Chen Representation
of the Invoicing Problem
Creating the Database
Two tasks must be completed
 create
the database structure
 create
the tables that will hold the end-user data
First task
 RDBMS
 Tends
 It
creates the physical files that will hold the database
to differ substantially from one RDBMS to another
is relatively easy to create a database structure,
regardless of which RDBMS you use.
The Database Schema
Authentication
 Process
through which the DBMS verifies that only
registered users are able to access the database
 Log on to the RDBMS using a user ID and a password
created by the database administrator
Schema
 Group
of database objects—such as tables and
indexes—that are related to each other
Data Definition Commands
Create database structure
 Holds
all tables and is a collection of physical files
stored on disk
 DBMS automatically creates tables to store
metadata
 Database administrator creates structure or schema



Logical group of tables or logical database
Groups tables by owner
Enforces security
Data Definition Commands
Creating the Database Structure
CREATE SCHEMA AUTHORIZATION <creator>;
 Example:
(For most RDBMS, it is optional)
CREATE SCHEMA AUTHORIZATION JONES;
 Schema
: logical database structure
a group of database objects- such as tables and indexes
– that are related to each other.
Data Dictionary
Table 7.3
Data Types
Data type selection is usually dictated
by the nature of the data and
by the intended use
Pay close attention to the expected use of
attributes for sorting and data retrieval
purposes
Some Common SQL Data Types
Some Common SQL Data Types
Data Type
Format
Numeric
NUMBER(L,D)
INTEGER
SMALLINT
DECIMAL(L,D)
Character
CHAR(L)
VARCHAR(L)
Date
DATE
Data Definition Commands
Creating Table Structures
CREATE TABLE <table name>(
<attribute1 name and attribute1 characteristics,
attribute2 name and attribute2 characteristics,
attribute3 name and attribute3 characteristics,
primary key designation,
foreign key designation and foreign key
requirements>);
Creating Table Structures
Use one line per column (attribute) definition
Use spaces to line up the attribute
characteristics and constraints
Table and attribute names are capitalized
Primary key attributes contain both a NOT
NULL and a UNIQUE specification
RDBMS will automatically enforce referential
integrity for foreign keys
Command sequence ends with a semicolon
Other SQL Constraints
NOT NULL constraint
 Ensures
that a column does not accept nulls
UNIQUE constraint
 Ensures
that all values in a column are unique
DEFAULT constraint
 Assigns
a value to an attribute when a new row is
added to a table
CHECK constraint
 Validates
data when an attribute value is entered
Data Definition Commands
CREATE TABLE VENDOR
(V_CODE
INTEGER
UNIQUE,
V_NAME
VARCHAR(35)
V_CONTACT VARCHAR(15)
V_AREACODE CHAR(3)
V_PHONE
CHAR(3)
V_STATE
CHAR(2)
V_ORDER
CHAR(1)
PRIMARY KEY (V_CODE));
NOT NULL
NOT
NOT
NOT
NOT
NOT
NOT
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
Data Definition Commands
CREATE TABLE CUSTOMER
(CUS_CODE NUMBER
PRIMARY KEY,
...,
...,
CUS_AREACODE CHAR(3) DEFAULT ‘615’
NOT NULL
CHECK(CUS_AREACODE
IN(‘615’,’713’,’931’) ),
...,
...,);
CREATE TABLE PRODUCT(
P_CODE
VARCHAR(10) NOT NULL UNIQUE,
P_DESCRIPT
P_INDATE
P_ONHAND
P_MIN
P_PRICE
P_DISCOUNT
V_CODE
VARCHAR(35)
DATE
SMALLINT
SMALLINT
DECIMAL(8,2)
DECIMAL(4,1)
SMALLINT,
NOT
NOT
NOT
NOT
NOT
NOT
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
PRIMARY KEY (P_CODE),
FOREIGN KEY (V_CODE) REFERENCES VENDOR
ON UPDATE CASCADE);
ON UPDATE CASCADE
update V_CODE in VENDER →
update V_CODE in PRODUCT
SQL Indexes
When a primary key is declared,
DBMS automatically creates a unique index
Often need additional indexes
Using the CREATE INDEX command,
SQL indexes can be created on the basis of
any selected attribute
SQL Indexes
SQL Indexes
Improve
the efficiency of data search
Created to meet particular search criteria
CREATE INDEX P_INDATEX
ON PRODUCT(P_INDATE);
When
the index field is a primary key
whose values must not be duplicated
CREATE UNIQUE INDEX P_CODEX
ON PRODUCT(P_CODE);
A Duplicated TEST Record
Composite index
 Index
based on two or more attributes
 Often
used to prevent data duplication
to enter duplicate data →
Error message: ”duplicate value in index”
CREATE UNIQUE INDEX EMP_TESTDEX
ON TEST(EMP_NUM, TEST_CODE, TEST_DATE);
 Try
Common SQL Data Manipulation
Commands
Data Manipulation Commands
Adding table rows
INSERT INTO <table name> VALUES
(attribute 1 value, attribute 2 value, … etc.);
INSERT INTO VENDOR
VALUES(‘21225, ’Bryson, Inc.’,
’Smithson’, ’615’,’223-3234’, ’TN’,
’Y’);
INSERT INTO PRODUCT
VALUES(‘11 QER/31’, ’Power painter, 15
psi., 3-nozzle’, ’03-Nov-03’, 8.5,
109.99, 0.00, 25595);
A Data View and Entry Form
 End-user applications are best created with utilities
to create a form-based data view and entry screen .
Data Manipulation Commands
Saving table changes
COMMIT [WORK];
COMMIT;
 Will
permanently save any changes made to any
table in the database
 Any
changes made to the table contents
are not physically saved on disk
until



Database is closed
Program is closed
COMMIT command is used
Data Manipulation Commands
SELECT command - list table contents
UPDATE command – modify data in the table
ROLLBACK command - restores database back
to previous condition if COMMIT hasn’t been
used
DELETE command - removes table row
Data Manipulation Commands
Listing Table Rows
 SELECT

Used to list contents of table
 Syntax

SELECT columnlist
FROM tablename
 Columnlist
represents one or more attributes,
separated by commas
 Asterisk
( * )can be used as wildcard character to
list all attributes
Data Manipulation Commands
Listing Table Rows
SELECT * FROM PRODUCT;
SELECT P_CODE, P_DESCRIPT, P_INDATE, P_ONHAND,
P_MIN, P-PRICE, P_DISCOUNT, V_CODE
FROM PRODUCT;
Listing Table Rows
Data Manipulation Commands
Updating Table Rows
 UPDATE

Modify data in a table
 Syntax

 If
UPDATE tablename
SET columnname = expression [, columname = expression]
[WHERE conditionlist];
more than one attribute is to be updated in the row,
separate corrections with commas
Data Manipulation Commands
Updating table rows
UPDATE PRODUCT
SET P_INDATE = ‘18-Jan-2004’
WHERE P_CODE = ‘13-Q2/P2’;
UPDATE PRODUCT
SET P_INDATE = ‘18-Jan-2004’,
P_PRICE = 15.99, P_MIN = 10
WHERE P_CODE = ‘13-Q2/P2’;
Data Manipulation Commands
Restoring Table Contents
 ROLLBACK


Used restore the database to its previous condition
Only applicable if COMMIT command has not been used
to permanently store the changes in the database
 Syntax
 ROLLBACK;


COMMIT and ROLLBACK only work with data manipulation
commands that are used to add, modify, or delete table rows
Oracle will automatically COMMIT data changes when
issuing data definition commands
Data Manipulation Commands
Deleting Table Rows
 DELETE

Deletes a table row
 Syntax

DELETE FROM tablename
[WHERE conditionlist ];
 WHERE
 If
condition is optional
WHERE condition is not specified, all rows from
the specified table will be deleted
Data Manipulation Commands
Deleting Table Rows
DELETE FROM PRODUCT
WHERE P_CODE = ‘2238/QPD’;
DELETE FROM PRODUCT
WHERE P_MIN = 5;
Data Manipulation Commands
 Inserting Table Rows with a Select Subquery

INSERT



Inserts multiple rows from another table (source)
Uses SELECT subquery
– Query that is embedded (or nested) inside another query
– Executed first
Syntax

INSERT INTO tablename SELECT columnlist FROM tablename
 Subquery – nested query / inner query


is a query that is embedded inside another query.
Is always executed first
 INSERT INTO PRODUCT SELECT * FROM P;
SELECT Queries
Selecting Rows with Conditional Restrictions
 Select
partial table contents by placing restrictions on
rows to be included in output
 Add
conditional restrictions to the SELECT statement,
using WHERE clause
 Syntax

SELECT columnlist
FROM tablelist
[ WHERE conditionlist ] ;
SELECT Queries
Selected PRODUCT Table Attributes for VENDOR
Code 21344
SELECT P_DESCRIPT, P_INDATE, P_PRICE, V_CODE
FROM PRODUCT
WHERE V_CODE = 21344;
The Microsoft Access QBE and its SQL
QBE (Query By Example)
query generator
Comparison Operators
SELECT Queries
Selected PRODUCT Table Attributes for VENDOR
Codes Other than 21344
SELECT P_DESCRIPT, P_INDATE, P_PRICE,
FROM PRODUCT
WHERE V_CODE <> 21344;
V_CODE
SELECT Queries
Selected PRODUCT Table Attributes
with a P_PRICE Restriction
SELECT P_DESCRIPT, P_ONHAND,
FROM PRODUCT
WHERE P_PRICE <= 10;
P_MIN, P_PRICE
SELECT Queries
Using Comparison Operators on Character
Attributes
SELECT P_DESCRIPT, P_ONHAND, P_MIN, P_PRICE
FROM PRODUCT
WHERE P_CODE < ‘1558-QW1’;
SELECT Queries
Using Comparison Operators on Dates
SELECT P_DESCRIPT, P_ONHAND, P_MIN, P_PRICE, P_INDATE
FROM PRODUCT
WHERE P_INDATE >= ‘20-Jan-2006’;
SELECT Queries
SELECT Statement with a Computed Column
 New
columns can be created through valid expressions
 Formulas may contain mathematical operators
 May contain attributes of any tables specified in FROM
clause
SELECT P_DESCRIPT,P_ONHAND,P_PRICE,
P_ONHAND*P_PRICE
FROM PRODUCT;
SELECT Queries
SELECT Statement with a Computed Column
and an Alias
 Alias
is alternate name given to table or column in SQL
statement
SELECT P_DESCRIPT,P_ONHAND,P_PRICE,
P_ONHAND*P_PRICE AS TOTVALUE
FROM PRODUCT;
SELECT Queries
Arithmetic Operators:
The Rule of Precedence
 Perform
operations within parentheses
 Perform
power operations
 Perform
multiplications and divisions
 Perform
additions and subtractions
SELECT Queries
Logical Operators: AND, OR, NOT
SELECT *
FROM PRODUCT
WHERE V_CODE = 21344 OR V_CODE = 24288;
Rules of precedence
 Conditions
within parenthesis executed first
 Boolean algebra
Special
 BETWEEN
- defines limits
 IS NULL - checks for nulls
 LIKE - checks for similar string
 IN - checks for value in a set
 EXISTS - opposite of IS NULL
SELECT Queries
Logical Operator: OR
SELECT P_DESCRIPT, P_INDATE, P_PRICE, V_CODE
FROM PRODUCT
WHERE V_CODE=21344 OR V_CODE=24288;
SELECT Queries
Logical Operator: AND
SELECT P_DESCRIPT, P_INDATE, P_PRICE, V_CODE
FROM PRODUCT
WHERE P_PRICE<50 AND P_INDATE>‘15-Jan-2004’;
SELECT Queries
SELECT P_DESCRIPT, P_INDATE, P_PRICE, V_CODE
FROM PRODUCT
WHERE (P_PRICE<50 AND P_INDATE>’15-Jan-2004’)
OR V_CODE=24288;
SELECT Queries
Special Operators
 BETWEEN
 IS
NULL - used to check whether an attribute
value is null
 LIKE
 IN
- used to define range limits.
- used to check for similar character strings.
- used to check whether an attribute value
matches any value within a value list.
 EXISTS
- used to check
if a subquery returns any rows or not
- the opposite of IS NULL.
SELECT Queries
Special Operators
BETWEEN is used to define range limits.
SELECT *
FROM PRODUCT
WHERE P_PRICE BETWEEN 50.00 AND 100.00;
SELECT *
FROM PRODUCT
WHERE P_PRICE>=50.00 AND P_PRICE<=100.00;
SELECT Queries
Special Operators
IS NULL is used to check whether an
attribute value is null.
SELECT P_CODE, P_DESCRIPT
FROM PRODUCT
WHERE P_MIN IS NULL;
SELECT P_CODE, P_DESCRIPT
FROM PRODUCT
WHERE P_INDATE IS NULL;
SELECT Queries
Special Operators
LIKE is used to check for similar character strings.
SELECT *
FROM VENDOR
WHERE V_CONTACT LIKE ‘Smith%’;
SELECT *
FROM VENDOR
WHERE V_CONTACT LIKE ‘SMITH%’;
%
: cn , c=any character, n≧0
_
: c1
SELECT Queries
Special Operators
IN is used to check whether an attribute value
matches a value contained within a (sub)set
of listed values.
SELECT *
FROM PRODUCT
WHERE V_CODE IN (21344, 24288);
SELECT Queries
EXISTS used to check if a subquery returns
any rows or not .
SELECT *
FROM VENDER
WHERE EXISTS (SELECT *
FROM PRODUCT
WHERE P_QOH <= P_MIN
AND VENDER.V_CODE = PRODUCT.V_CODE);
P_QOH : Quantity On Hand
Advanced Data Definition Commands
All changes in the table structure are made by
using the ALTER command
 Followed
 Three
by a keyword that produces specific change
options are available

ADD

MODIFY

DROP
Changing a Column’s Data Type
ALTER can be used to change data type
ALTER TABLE PRODUCT
MODIFY (V_CODE CHAR(5));
Some RDBMSs (such as Oracle) do not
permit changes to data types unless the
column is empty
Changing a Column’s Data
Characteristics
Use ALTER to change data characteristics
ALTER TABLE PRODUCT
MODIFY (P_PRICE DECIMAL(9,2));
If the column to be changed already contains
data, changes in the column’s characteristics
are permitted if those changes do not alter
the data type
Adding or Dropping a Column
Use ALTER to add a column
ALTER TABLE PRODUCT
ADD (P_SALECODE CHAR(1));
Use ALTER to drop a column
ALTER TABLE VENDOR
DROP COLUMN V_ORDER;
 Some
RDBMSs impose restrictions on the deletion of
an attribute
Advanced Data Updates
 The Effect of Data Entry into the New
P_SALECODE Column
UPDATE PRODUCT
SET P_SALECODE = ‘2’
WHERE P_INTADE < ’25-Dec-2005’;
Advanced Data Updates
 Update of the P_SALECODE Column in Multiple Data
Rows
UPDATE PRODUCT
SET P_SALECODE = ‘1’
WHERE P_INTADE >= ’16-Jan-2006’ AND
P_INTADE <= ’10-Feb-2006’;
Advanced Data Updates
The Effect of Multiple Data Updates in the
PRODUCT Table (MS Access)
UPDATE PRODUCT
SET P_SALECODE = ‘2’
WHERE P_INDATE < ‘25-Dec-2005’;
UPDATE PRODUCT
SET P_SALECODE = ‘1’
WHERE P_INDATE >= ‘16-Jan-2006’
AND P_INDATE < ‘10-Feb-2006’;
Advanced Data Updates
Copying Parts of Tables
SQL permits copying contents of selected
table columns so that the data need not be
reentered manually into newly created
table(s)
 First
 Next
create the PART table structure
add rows to new PART table using PRODUCT
table rows
Copying Parts of Tables
Need not be identical:


Column names
Number of columns
Column characteristics must match
CREATE TABLE PART
PART_CODE CHAR(8) NOT NULL UNIQUE,
PART_DESCRIPT
CHAR(35),
PART_PRICE DECIMAL(8,2),
PRIMARY KEY(PART_CODE));
INSERT INTO PART (PART_CODE,PART_DESCRIPT,PART_PRICE)
SELECT P_CODE, P_DESCRIPT, P_PRICE
FROM PRODUCT;
PART Attributes Copied
from the PRODUCT Table
Adding Primary and Foreign Key Designations
For reestablish the integrity rules
 Forgot
to define
 Imported tables from a different database
ALTER TABLE PRODUCT
ADD PRIMARY KEY (P_CODE);
ALTER TABLE PRODUCT
ADD FOREIGN KEY (V_CODE) REFERENCES
VENDOR;
ALTER TABLE PRODUCT
ADD PRIMARY KEY (P_CODE)
ADD FOREIGN KEY (V_CODE) REFERENCES
VENDOR;
Deleting a Table from the Database
DROP TABLE <table name>;
DROP TABLE PART;
Advanced Select Queries
Ordering a Listing
ORDER BY <attributes>
SELECT P_CODE, P_DESCRIPT,
FROM PRODUCT
ORDER BY P_PRICE;
P_INDATE, P_PRICE
Selected PRODUCT Table Attributes:
Ordered by (Ascending) P_PRICE
Partial Listing of
EMPLOYEE Table Contents
Cascading order sequence
SELECT EMP_LNAME,EMP_FNAME,EMP_INITIAL,EMP_AREACODE,EMP_PHONE
FROM EMPLOYEE
ORDER BY EMP_LNAME, EMP_FNAME, EMP_INITIAL;
Descending order
SELECT P_CODE, P_DESCRIPT, P_INDATE, P_PRICE
FROM PRODUCT
WHERE P_INDATE<‘08-20-1999’ AND P_PRICE<=50.00
ORDER BY V_CODE, P_PRICE DESC;
A Listing of Distinct (Different) V_CODE
Values in the PRODUCT Table
SELECT
DISTINCT V_CODE
FROM PRODUCT;
Aggregate Functions
COUNT
SELECT COUNT(DISTINCT V_CODE)
FROM PRODUCT;
MAX
SELECT P_CODE, P_DESCRIPT, P_PRICE
FROM PRODUCT
WHERE P_PRICE = (SELECT MAX(P_PRICE)
FROM PRODUCT);
SUM
SELECT SUM(P_ONHAND*P_PRICE) AS TOTVALUE
FROM PRODUCT;
AVG
SELECT P_DESCRIPT, P_ONHAND, P_PRICE, V_CODE
FROM PRODUCT
WHERE P_PRICE > (SELECT AVG(P_PRICE)
FROM PRODUCT)
ORDER BY P_PRICE DESC;
• Determine whether goods that have been in inventory for a certain length of time
should be placed on special sale.
Grouping Data - GROUP BY
SELECT P_SALECODE, MIN(P_PRICE)
FROM PRODUCT
GROUP BY P_SALECODE;
GROUP BY clause
 The GROUP BY clause is valid only
when used in conjunction with one of the SQL aggregate
functions:
COUNT, MIN, MAX, AVG, SUM
 SELECT V_CODE, COUNT(DISTINCT(P_CODE))
FROM PRODUCT_2
GROUP BY V_CODE;
 Otherwise,
you will generate a “not a GROUP BY expression” error.
 SELECT V_CODE, P_CODE, P_DESCRIPT, P_PRICE
FROM PRODUCT_2
GROUP BY V_CODE;
Incorrect and Correct Use
of the GROUP BY Clause
ERROR
GROUP BY’s HAVING clause
 WHERE ( SELECT )

Applies to columns and expressions for individual rows
 HAVING ( GROUP BY )

Applies to the output of a GROUP BY operation
SELECT V_CODE,COUNT(DISTINCT(P_CODE)),AVG(P_PRICE)
FROM PRODUCT
GROUP BY V_CODE;
SELECT V_CODE,COUNT(DISTINCT(P_CODE)),AVG(P_PRICE)
FROM PRODUCT
GROUP BY V_CODE
HAVING AVG(P_PRICE)<10;
An Application of the HAVING Clause
Virtual Tables: Creating a View
View
A
virtual table based on a SELECT query
 Logical table exists only in memory
 Can be treated as though it were a real table
 CREATE VIEW PRICEGT50 AS
SELECT P_DESCROPT, P_ONHAND, P_PRICE
FROM PRODUCT
WHERE P_PRICE > 50.00;
SELECT *
FROM PRICEGT50;
Virtual Tables: Creating a View
Joining Database Tables
Ability to combine (join) tables on common
attributes is most important distinction between
a relational database and other databases
Join is performed when data are retrieved from
more than one table at a time
Join is generally composed of an equality
comparison between the foreign key and the
primary key of related tables
Joining Database Tables
Creating Links Through Foreign Keys
Joining Database Tables
SELECT PRODUCT.P_DESCRIPT,PRODUCT.P_PRICE,
VENDOR.V_NAME, VENDOR.V_CONTACT,
VENDOR.V_AREACODE, VENDOR.V_PHONE
FROM PRODUCT, VENDOR
WHERE PRODUCT.V_CODE=VENDOR.V_CODE;
Joining Database Tables
An Ordered and Limited Listing After a JOIN
SELECT P_DESCRIPT, P_PRICE, V_NAME,
V_CONTACT, V_AREACODE, V_PHONE
FROM PRODUCT, VENDOR
WHERE PRODUCT.V_CODE=VENDOR.V_CODE
AND P_INDATE > ‘15-Jan-2006’
ORDER BY P_PRICE ;
Joining Tables With a Alias
SELECT P_DESCRIPT, P_PRICE, V_NAME,
V_CONTACT, V_AREACODE, V_PHONE
FROM PRODUCT P, VENDOR V
WHERE P.V_CODE = V.V_CODE
ORDER BY P_PRICE;
Recursive Joins
An alias is especially useful when a table must
be joined to itself in a recursive query.
Generate a list of all employees with their
manager’s names.
SELECT E.EMP_NUM, E.EMP_LNAME,
E.EMP_MGR, M.EMP_LNAME
FROM EMP E, EMP M
WHERE E.EMP_MGR = M.EMP_NUM
ORDER BY E.EMP_MGR;
Recursive Joins
The Contents of the EMP Table
Using an Alias to Join a Table to Itself
manager’s names
Left Outer Join
Show all VENDOR rows and all matching
PRODUCT rows.
SELECT P_CODE, VENDOR.V_CODE, V_NAME
FROM VENDOR LEFT JOIN PRODUCT
ON VENDOR.V_CODE = PRODUCT.V_CODE;
Left Outer Joins
Right Outer Join
Show all PRODUCT rows and all matching
VENDOR rows.
SELECT P_CODE, VENDOR.V_CODE, V_NAME
FROM VENDOR RIGHT JOIN PRODUCT
ON VENDOR.V_CODE = PRODUCT.V_CODE;
Right Outer Joins
Summary
SQL commands can be divided into two
overall categories:
 Data
definition language commands
 Data manipulation language commands
The ANSI standard data types are supported
by all RDBMS vendors in different ways
Basic data definition commands allow you to
create tables, indexes, and views
Summary (continued)
 DML commands allow you to add, modify, and delete
rows from tables
 The basic DML commands are SELECT, INSERT,
UPDATE, DELETE, COMMIT, and ROLLBACK
 INSERT command is used to add new rows to tables
 SELECT statement is main data retrieval command in
SQL
Summary (continued)
Many SQL constraints can be used with
columns
The column list represents one or more
column names separated by commas
WHERE clause can be used with SELECT,
UPDATE, and DELETE statements to restrict
rows affected by the DDL command
Summary (continued)
Aggregate functions
 Special
functions that perform arithmetic computations
over a set of rows
ORDER BY clause
 Used
to sort output of SELECT statement
 Can sort by one or more columns and use either an
ascending or descending order
Join output of multiple tables with SELECT
statement
Summary (continued)
Natural join uses join condition to match only
rows with equal values in specified columns
Right outer join and left outer join used to
select rows that have no matching values in
other related table
Converting an ER Model
into a Database Structure
Requires following specific rules that govern
such a conversion
Decisions made by the designer to govern
data integrity are reflected in the foreign key
rules
The Ch06_Artist Database ERD and Tables
A Data Dictionary for the Ch06_Artist Database
A Summary of Foreign Key Rules
M:N Relationships
 Foreign
key location: Composite entity
A Summary of Foreign Key Rules
1:M Relationships
 Foreign
key location: Many side
Foreign key
1
GALLERY
M
exhibit
PAINING
A Summary of Foreign Key Rules
Procedural language
(CH7 Advanced SQL)
 A term used in contrast to declarative language to
describe a language where the programmer specifies an
explicit sequences of steps to follow to produce a result.
 Common procedural languages include Basic, Pascal, C.
 Declarative languages
describe relationships between variables in terms of
functions or inference rules and the language executor
(interpreter or compiler) applies some fixed algorithm to
these relations to produce a result.
 The most common examples of declarative languages are
logic programming languages such as Prolog and
functional languages like Haskell.
Procedural SQL
Shortcomings of SQL
doesn’t support execution of a stored set of
procedures based on some logical condition.
IF-THEN-ELSE
 SQL fails to support the looping operations.
DO-WHILE
 SQL
To remedy the lack of procedural functionality in
SQL
 PSM (Persistent Stored Module)
 SQL99 standard defined
 A block of code (containing standard SQL statements and
procedural extensions) that is stored and executed at the
DBMS server.
 Oracle
implements PSMs through its procedural SQL
language - Procedural SQL(PL/SQL)
Procedural SQL
Procedural SQL
 Procedural
SQL make it possible to use and store
procedural code and SQL statements within the
database.
 The
procedural code is executed by the DBMS
when it is invoked by the end user.
 End



users can use PL/SQL to create:
Triggers
Stored procedures
PL/SQL functions
Procedural SQL
Triggers
A
trigger is procedural SQL code
that is automatically invoked by the RDBMS upon the
occurrence of a data manipulation event.

A trigger is always
invoked before or after a data row is selected, inserted, or
updated.

A trigger is always
associated with a database table.

Each database table may have one or more triggers.

A trigger is executed as part of the transaction that triggered it.
ECA (Event-Condition-Action)
Procedural SQL
Role
of triggers
 Triggers
can be used to enforce constraints that
cannot be enforced at the design and
implementation levels.
 Triggers
add functionality by automating critical
actions and providing appropriate warnings and
suggestions for remedial action.
 Triggers
can be used to update table values, insert
records in tables, and call other stored procedures.
The Revised PRODUCT Table
 If
P_ONHAND(庫存) <= P_MIN(安全存量)
set P_REORDER = “Yes”
The PRODUCT List Output in the Oracle RDBMS
 in
Oracle :
P_REORDER = 1/0 for Yes/No
Procedural SQL
Syntax to create a trigger in ORACLE
CREATE OR REPLACE TRIGGER <trigger_name>
[BEFORE/AFTER]
[DELETE/INSERT/UPDATE OF <column_name>]
ON <table_name>
[FOR EACH ROW]
BEGIN
PL/SQL instructions;
……………
END;
Procedural SQL
Creation of the Oracle Trigger for the PRODUCT
Table
CREATE OR REPLACE TRIGGER
TRG_PRODUCT_REORDER
AFTER INSERT OR UPDATE OF P_ONHAND
ON PRODUCT
BEGIN
UPDATE PRODUCT
SET P_REORDER = 1
WHERE P_ONHAND <= P_MIN;
END;
Creation of the Oracle Trigger for the PRODUCT Table
The PRODUCT Table’s P_REORDER Field is
Updated by the Trigger
UPDATE PRODUCT
SET P_ONHAND = 4
WHERE P_CODE = ’11QER/31’;
The P_REORDER Value Mismatch
UPDATE PRODUCT
SET P_MIN = 7
WHERE P_CODE = ’2232/QWE’;
The Second Version of the PRODUCT_REORDER
Trigger
CREATE OR REPLACE TRIGGER
TRG_PRODUCT_REORDER
AFTER INSERT OR UPDATE OF P_ONHAND,
ON PRODUCT
BEGIN
UPDATE PRODUCT
SET P_REORDER = 1
WHILE P_ONHAND <= P_MIN;
END;
P_MIN
The Second Version of the PRODUCT_REORDER Trigger
UPDATE PRODUCT
SET P_MIN = 10
WHERE P_CODE = ’23114-AA’;
The P_REORDER Flag Has Not Been Properly Set After
Increasing the P_ONHAND Value
UPDATE PRODUCT
SET P_ONHAND = P_ONHAND + P_MIN
WHERE P_CODE = ’11QER/31’;
4
25
Never reset it to 0!
 The Third Version of the PRODUCT_REORDER Trigger
CREATE OR REPLACE TRIGGER
TRG_PRODUCT_REORDER
BEFORE INSERT OR UPDATE OF P_ONHAND, P_MIN
ON PRODUCT
BEGIN
IF :NEW.P_ONHAND <= NEW.P_MIN THEN
:NEW.P_REORDER = 1;
ELSE
:NEW.P_REORDER = 0;
END IF;
END;
The Third Version of the Product Reorder Trigger
After creating the new trigger,
we can execute an UPDATE statement to
fire it.
UPDATE PRODUCT
SET P_ONHAND = P_ONHAND
Execution of the Third Trigger Version
Procedural SQL
Stored Procedures
A
stored procedure
is a named collection of procedural and SQL
statements.
 Stored
procedures are
stored in the database and
invoked by name.
 Stored
procedures are executed as a unit.
Procedural SQL
Syntax to create a stored procedure
CREATE OR REPLACE PROCEDURE procedure_name
(argument IN/OUT data-type, etc)
IS/AS BEGIN
DECLARE variable name and data type
PL/SQL or SQL statements;
END;
Syntax to invoke a stored procedure
EXEC store_procedure_name(parameter, parameter, …)
Procedural SQL
Stored Procedures
 DECLARE
is used to specify the variables used within the
procedure.
 Argument
specifies the parameters that are passed to the
stored procedure.
 IN
/ OUT
indicates whether the parameter is
for INPUT or OUTPUT or both.
 Data-type
is one of the procedural SQL data types used in the
RDBMS.
Creating and Invoking A Simple Stored Procedure
The PROD_SALE Stored Procedure
CREATE OR REPLACE PROCEDURE PROD-SALE
(CODE IN VARCHAR2, QTYSOLD IN NUMBER)
AS BEGIN
UPDATE PRODUCT
SET P_ONHAND = P_ONHAND - QTYSOLD
WHERE P_CODE = CODE;
END;
Creation of the PROD_SALE Stored Procedure
Executing the PROD_SALE Stored Procedure
Procedural SQL
PL/SQL Stored Functions
A
stored function
is a named group of procedural and SQL
statements that returns a value.
 Syntax
to create a function:
CREATE FUNCTION function_name
(argument IN data-type, etc)
RETURN data-type
AS BEGIN
PL/SQL statements;
RETURN (value); ……
END;