Chapter 6 Structured Query Language SQL Database Systems

Chapter 6 Structured Query Language (SQL) Database Systems: Design, Implementation, and Management Peter Rob & Carlos Coronel

In this chapter, you will learn: 4 The basic commands and functions of SQL 4 How SQL is used for data manipulation (to add, modify, delete, and retrieve data) 4 How to use SQL to query a database to extract useful information 4 How SQL is used for data administration (to create tables, indexes, and views) 4 About more advanced SQL features such as updatable views, stored procedures, and triggers

Introduction to SQL 4 Ideal database language u. Create database and table structures u. Perform basic data management chores (add, delete, and modify) u. Perform complex queries to transform data into useful information

Introduction to SQL 4 SQL (Structured Query Language) meets ideal database language requirements: u SQL l coverage fits into two categories: Data definition – Database objects such as tables, indexes, and views – Commands to define access rights to those database objects l Data manipulation – Includes commands to insert, update, delete, and retrieve data within the database tables

Introduction to SQL 4 SQL (Structured Query Language) meets ideal database language requirements: u SQL is a Nonprocedural language u SQL is relatively easy to learn. u ANSI prescribes a standard SQL. l SQL 2 : SQL-92 l SQL 3 : SQL-98/99 support object-oriented data management

SQL Data Definition Commands

Data Manipulation Commands

Data Definition Commands 4 The Database Model u Simple Database -- PRODUCT and VENDOR tables l Each product is supplied by only a single vendor. l A vendor may supply many products.

Data Definition Commands 4 The Tables and Their Components u. The VENDOR table contains vendors who are not referenced in the PRODUCT table. PRODUCT is optional to VENDOR. u. Some vendors have never supplied a product ( 0, N )

Data Definition Commands 4 The Tables and Their Components u. Existing V_CODE values in the PRODUCT table must have a match in the VENDOR table. u. 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 )

PRODUCT VENDER FIGURE 6. 2

The Database Model

The Chen Representation of the Invoicing Problem

Creating the Database 4 Two tasks must be completed u create the database structure u create the tables that will hold the end-user data 4 First task u RDBMS creates the physical files that will hold the database u Tends u It 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 4 Authentication u Process through which the DBMS verifies that only registered users are able to access the database u Log on to the RDBMS using a user ID and a password created by the database administrator 4 Schema u Group of database objects—such as tables and indexes— that are related to each other

Data Definition Commands 4 Create database structure u Holds all tables and is a collection of physical files stored on disk u DBMS automatically creates tables to store metadata u Database administrator creates structure or schema l l l Logical group of tables or logical database Groups tables by owner Enforces security

Data Definition Commands 4 Creating the Database Structure CREATE SCHEMA AUTHORIZATION <creator>; u Example: CREATE SCHEMA AUTHORIZATION JONES; u Schema : logical database structure a group of database objects- such as tables and indexes – that are related to each other. CREATE DATABASE <database name>; u Example: CREATE DATABASE CH 6;

Data Dictionary Table 6. 3

Data Types 4 Data type selection is usually dictated by the nature of the data and by the intended use 4 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 4 Creating Table Structures CREATE TABLE <table name>( <attribute 1 name and attribute 1 characteristics, attribute 2 name and attribute 2 characteristics, attribute 3 name and attribute 3 characteristics, primary key designation, foreign key designation and foreign key requirements>);

Creating Table Structures 4 Use one line per column (attribute) definition 4 Use spaces to line up the attribute characteristics and constraints 4 Table and attribute names are capitalized 4 Primary key attributes contain both a NOT NULL and a UNIQUE specification 4 RDBMS will automatically enforce referential integrity foreign keys 4 Command sequence ends with a semicolon

Other SQL Constraints 4 NOT NULL constraint u Ensures that a column does not accept nulls 4 UNIQUE constraint u Ensures that all values in a column are unique 4 DEFAULT constraint u Assigns a value to an attribute when a new row is added to a table 4 CHECK constraint u Validates data when an attribute value is entered

Data Definition Commands CREATE TABLE VENDOR (V_CODE INTEGER NOT UNIQUE, V_NAME VARCHAR(35) V_CONTACT VARCHAR(15) NOT V_AREACODE CHAR(3) NOT V_PHONE CHAR(3) NOT V_STATE CHAR(2) NOT V_ORDER CHAR(1) NOT PRIMARY KEY (V_CODE)); NULL NOT 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 P_DESCRIPT P_INDATE P_ONHAND P_MIN P_PRICE P_DISCOUNT V_CODE VARCHAR(35) NOT DATE NOT NULL, SMALLINT NOT DECIMAL(8, 2) NOT DECIMAL(4, 1) NOT SMALLINT, UNIQUE, NULL, NULL, PRIMARY KEY (P_CODE), FOREIGN KEY (V_CODE) REFERENCES VENDOR ON DELETE RESTRICT ON UPDATE CASCADE); 4 ON DELETE RESTRICT cannot delete a vender as long as there is a product that references that vender 4 ON UPDATE CASCADE update V_CODE in VENDER → update V_CODE in PRODUCT

SQL Indexes 4 When a primary key is declared, DBMS automatically creates a unique index 4 Often need additional indexes 4 Using the CREATE INDEX command, SQL indexes can be created on the basis of any selected attribute

SQL Indexes 4 SQL Indexes u. Improve the efficiency of data search u. Created to meet particular search criteria CREATE INDEX P_CODEX ON PRODUCT(P_CODE); u. 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 4 Composite index u Index based on two or more attributes u Often used to prevent data duplication u Try to enter duplicate data → Error message: ”duplicate value in index” CREATE UNIQUE INDEX EMP_TESTDEX ON TEST(EMP_NUM, TEST_CODE, TEST_DATE);

Common SQL Data Manipulation Commands

Data Manipulation Commands 4 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 4 End-user applications are best created with utilities to create a form-based data view and entry screen.

Data Manipulation Commands 4 Saving table changes COMMIT [WORK]; COMMIT; u Any changes made to the table contents are not physically saved on disk until l Database is closed l Program is closed l COMMIT command is used

Data Manipulation Commands 4 SELECT command - list table contents 4 UPDATE command – modify data in the table 4 ROLLBACK command - restores database back to previous condition if COMMIT hasn’t been used 4 DELETE command - removes table row

Data Manipulation Commands 4 Listing Table Rows u SELECT l Used to list contents of table u Syntax l SELECT columnlist FROM tablename u Columnlist represents one or more attributes, separated by commas u Asterisk ( * )can be used as wildcard character to list all attributes

Data Manipulation Commands 4 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;

Figure 6. 4 The Contents of the PRODUCT Table

Data Manipulation Commands 4 Updating Table Rows u UPDATE l Modify data in a table u Syntax l u 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 4 Updating table rows UPDATE PRODUCT SET P_INDATE = ‘ 18 -Jan-2004’ WHERE P_CODE = ‘ 13 -Q 2/P 2’; UPDATE PRODUCT SET P_INDATE = ‘ 18 -Jan-2004’, P_PRICE = 15. 99, P_MIN = 10 WHERE P_CODE = ‘ 13 -Q 2/P 2’;

Data Manipulation Commands 4 Restoring Table Contents u ROLLBACK l l 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 u Syntax l ROLLBACK; COMMIT and ROLLBACK only work with data manipulation commands that are used to add, modify, or delete table rows u Oracle will automatically COMMIT data changes when issuing data definition commands u

Data Manipulation Commands 4 Deleting Table Rows u DELETE l Deletes a table row u Syntax l DELETE FROM tablename [WHERE conditionlist ]; u WHERE u If condition is optional WHERE condition is not specified, all rows from the specified table will be deleted

Data Manipulation Commands 4 Deleting Table Rows DELETE FROM PRODUCT WHERE P_CODE = ‘ 2238/QPD’; DELETE FROM PRODUCT WHERE P_MIN = 5;

Data Manipulation Commands 4 Inserting Table Rows with a Select Subquery u INSERT l l u Inserts multiple rows from another table (source) Uses SELECT subquery – Query that is embedded (or nested) inside another query – Executed first Syntax l INSERT INTO tablename SELECT columnlist FROM tablename 4 Subquery – nested query / inner query is a query that is embedded inside another query. u Is always executed first u 4 INSERT INTO PRODUCT SELECT * FROM P;

SELECT Queries 4 Selecting Rows with Conditional Restrictions u Select partial table contents by placing restrictions on rows to be included in output u Add conditional restrictions to the SELECT statement, using WHERE clause u Syntax l SELECT columnlist FROM tablelist [ WHERE conditionlist ] ;

SELECT Queries 4 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 4 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 4 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 4 Using Comparison Operators on Character Attributes SELECT P_DESCRIPT, P_ONHAND, P_MIN, P_PRICE FROM PRODUCT WHERE P_CODE < ‘ 1558 -QW 1’;

SELECT Queries 4 Using Comparison Operators on Dates SELECT P_DESCRIPT, P_ONHAND, P_MIN, P_PRICE, P_INDATE FROM PRODUCT WHERE P_INDATE >= ‘ 20 -Jan-2004’;

SELECT Queries 4 SELECT Statement with a Computed Column u New columns can be created through valid expressions u Formulas may contain mathematical operators u 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 4 SELECT Statement with a Computed Column and an Alias u 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 4 Arithmetic Operators: The Rule of Precedence u Perform operations within parentheses u Perform power operations u Perform multiplications and divisions u Perform additions and subtractions

SELECT Queries 4 Logical Operators: AND, OR, NOT SELECT * FROM PRODUCT WHERE V_CODE = 21344 OR V_CODE = 24288; 4 Rules of precedence u Conditions within parenthesis executed first u Boolean algebra 4 Special u BETWEEN - defines limits u IS NULL - checks for nulls u LIKE - checks for similar string u IN - checks for value in a set u EXISTS - opposite of IS NULL

SELECT Queries 4 Logical Operator: OR SELECT P_DESCRIPT, P_INDATE, P_PRICE, V_CODE FROM PRODUCT WHERE V_CODE=21344 OR V_CODE=24288;

SELECT Queries 4 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 4 Special Operators u BETWEEN u IS NULL - used to check whether an attribute value is null u LIKE u 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. u EXISTS - used to check if a subquery returns any rows or not - the opposite of IS NULL.

SELECT Queries 4 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 4 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 4 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%’; u% : cn , c=any character, n≧ 0 u_ : c 1

SELECT Queries 4 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 4 EXISTS used to check if a subquery returns any rows or not. SELECT * FROM VENDER WHERE EXISTS (SELECT * FROM PRODUCT WHERE P_ONHAND <= P_MIN AND VENDER. V_CODE = PRODUCT. V_CODE);

Advanced Data Definition Commands 4 All changes in the table structure are made by using the ALTER command u Followed u Three by a keyword that produces specific change options are available l ADD l MODIFY l DROP

Changing a Column’s Data Type 4 ALTER can be used to change data type ALTER TABLE PRODUCT MODIFY (V_CODE CHAR(5)); 4 Some RDBMSs (such as Oracle) do not permit changes to data types unless the column is empty

Changing a Column’s Data Characteristics 4 Use ALTER to change data characteristics ALTER TABLE PRODUCT MODIFY (P_PRICE DECIMAL(9, 2)); 4 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 4 Use ALTER to add a column ALTER TABLE PRODUCT ADD (P_SALECODE CHAR(1)); 4 Use ALTER to drop a column ALTER TABLE VENDOR DROP COLUMN V_ORDER; u Some RDBMSs impose restrictions on the deletion of an attribute

Advanced Data Updates 4 The Effect of Data Entry into the New P_SALECODE Column UPDATE PRODUCT SET P_SALECODE = ‘ 2’ WHERE P_CODE = ‘ 1546 -QQ 2’;

Advanced Data Updates 4 Update of the P_SALECODE Column in Multiple Data Rows UPDATE PRODUCT SET P_SALECODE = ‘ 1’ WHERE P_CODE IN (‘ 2232/QWE’, ‘ 2232/QTY’);

Advanced Data Updates 4 The Effect of Multiple Data Updates in the PRODUCT Table (MS Access) UPDATE PRODUCT SET P_SALECODE = ‘ 2’ WHERE P_INDATE < ‘ 25 -Dec-2003’; UPDATE PRODUCT SET P_SALECODE = ‘ 1’ WHERE P_INDATE >= ‘ 16 -Jan-2004’ AND P_INDATE < ‘ 10 -Feb-2004’;

Advanced Data Updates

Copying Parts of Tables 4 SQL permits copying contents of selected table columns so that the data need not be reentered manually into newly created table(s) u First u Next create the PART table structure add rows to new PART table using PRODUCT table rows

Copying Parts of Tables 4 Need not be identical: l l Column names Number of columns 4 Column characteristics must match CREATE TABLE 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 4 For reestablish the integrity rules u Forgot to define u 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 4 DROP TABLE <table name>; DROP TABLE PART;

Advanced Select Queries 4 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

4 Cascading order sequence SELECT EMP_LNAME, EMP_FNAME, EMP_INITIAL, EMP_AREACODE, EMP_PHONE FROM EMPLOYEE ORDER BY EMP_LNAME, EMP_FNAME, EMP_INITIAL;

4 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; DESC

A Listing of Distinct (Different) V_CODE Values in the PRODUCT Table u. SELECT DISTINCT V_CODE FROM PRODUCT;

Some Basic SQL Aggregate Functions

4 COUNT SELECT COUNT(DISTINCT V_CODE) FROM PRODUCT;

4 MAX SELECT P_CODE, P_DESCRIPT, P_PRICE FROM PRODUCT WHERE P_PRICE = (SELECT MAX(P_PRICE) FROM PRODUCT);

4 SUM SELECT SUM(P_ONHAND*P_PRICE) AS TOTVALUE FROM PRODUCT;

4 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.

4 Grouping Data - GROUP BY SELECT P_SALECODE, MIN(P_PRICE) FROM PRODUCT GROUP BY P_SALECODE;

GROUP BY clause 4 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; 4 Otherwise, you will generate a “not a GROUP BY expression” error. Ø SELECT V_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 4 WHERE ( SELECT ) u Applies to columns and expressions for individual rows 4 HAVING ( GROUP BY ) u Applies to the output of a GROUP BY operation 4 SELECT V_CODE, COUNT(DISTINCT(P_CODE)), AVG(P_PRICE) FROM PRODUCT GROUP BY V_CODE; 4 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 4 View A virtual table based on a SELECT query u Logical table exists only in memory u Can be treated as though it were a real table u 4 CREATE VIEW PRODUCT_3 AS SELECT P_DESCROPT, P_ONHAND, P_PRICE FROM PRODUCT WHERE P_PRICE > 50. 00; SELECT * FROM PRODUCT_3;

Creating a Virtual Table with the CREATE VIEW Command

Joining Database Tables 4 Ability to combine (join) tables on common attributes is most important distinction between a relational database and other databases 4 Join is performed when data are retrieved from more than one table at a time 4 Join is generally composed of an equality comparison between the foreign key and the primary key of related tables

Creating Links Through Foreign Keys

4 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;

The Results of a Join

An Ordered and Limited Listing After a JOIN 4 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-2004’ ORDER BY P_PRICE ;

Joining Tables With a Alias 4 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 4 An alias is especially useful when a table must be joined to itself in a recursive query. 4 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;

The Contents of the EMP Table

Using an Alias to Join a Table to Itself

Left Outer Join 4 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;

The Left Outer Join Results

Right Outer Join 4 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;

The Right Outer Join Results

Converting an ER Model into a Database Structure 4 Requires following specific rules that govern such a conversion 4 Decisions made by the designer to govern data integrity are reflected in the foreign key rules

The Ch 06_Artist Database ERD and Tables

A Data Dictionary for the Ch 06_Artist Database

A Summary of Foreign Key Rules 4 M: N Relationships u Foreign key location: Composite entity 1 GALLERY M exhibit PAINING

A Summary of Foreign Key Rules 4 1: M Relationships u Foreign key location: Many side Foreign key 1 GALLERY M exhibit PAINING

A Summary of Foreign Key Rules

Procedural language 4 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. 4 Common procedural languages include Basic, Pascal, C. 4 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. 4 The most common examples of declarative languages are logic programming languages such as Prolog and functional languages like Haskell.

Procedural SQL 4 Shortcomings of SQL u SQL doesn’t support execution of a stored set of procedures based on some logical condition. IF-THEN-ELSE u SQL fails to support the looping operations. DO-WHILE 4 Solutions u Embedded SQL l SQL statements can be inserted within the procedural programming language u Shared Code l Critical code is isolated and shared by all application programs. l This approach allows better maintenance. u Procedural SQL(PL/SQL)

Procedural SQL 4 Procedural SQL u Procedural SQL allows the use of procedural code and SQL statements that are stored within the database. u The procedural code is executed by the DBMS when it is invoked by the end user. u End l l l users can use PL/SQL to create: Triggers Stored procedures PL/SQL functions

Procedural SQL 4 Triggers u. A trigger is procedural SQL code that is automatically invoked by the RDBMS upon the occurrence of a data manipulation event. l A trigger is always invoked before or after a data row is selected, inserted, or updated. l A trigger is always associated with a database table. l Each database table may have one or more triggers. l A trigger is executed as part of the transaction that triggered it. ECA (Event-Condition-Action)

Procedural SQL u. Role of triggers l Triggers can be used to enforce constraints that cannot be enforced at the design and implementation levels. l Triggers add functionality by automating critical actions and providing appropriate warnings and suggestions for remedial action. l Triggers can be used to update table values, insert records in tables, and call other stored procedures.

The Revised PRODUCT Table u If P_ONHAND(庫存) <= P_MIN(安全存量) set P_REORDER = “Yes”

The PRODUCT List Output in the Oracle RDBMS u in Oracle : P_REORDER = 1/0 for Yes/No

Procedural SQL 4 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 4 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 = ’ 11 QER/31’;

The P_REORDER Value Mismatch UPDATE PRODUCT SET P_MIN = 7 WHERE P_CODE = ’ 2232/QWE’;

4 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 = ’ 11 QER/31’; 4 25 Never reset it to 0!

4 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

4 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 4 Stored Procedures u. A stored procedure is a named collection of procedural and SQL statements. u Stored procedures are stored in the database and invoked by name. u Stored procedures are executed as a unit.

Procedural SQL 4 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; 4 Syntax to invoke a stored procedure EXEC store_procedure_name(parameter, …)

Procedural SQL 4 Stored Procedures u DECLARE is used to specify the variables used within the procedure. u Argument specifies the parameters that are passed to the stored procedure. u IN / OUT indicates whether the parameter is for INPUT or OUTPUT or both. u 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 VARCHAR 2, 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 4 PL/SQL Stored Functions u. A stored function is a named group of procedural and SQL statements that returns a value. u 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;