An Introduction to Structured Query Language SQL

1
Chapter 6 Structured Query Language (SQL)
Database Systems Design, Implementation, and
Management Peter Rob Carlos Coronel
2
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

3
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

4
Introduction to SQL

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

5
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

6
SQL Data Definition Commands
7
Data Manipulation Commands
8
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.

9
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 )
10
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 )
11
PRODUCT
FIGURE 6.2
VENDER
12
The Database Model
13
The Chen Representation of the Invoicing Problem
14
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 creates the physical files that will hold
  the database
• Tends to differ substantially from one RDBMS to
  another
• It is relatively easy to create a database
  structure, regardless of which RDBMS you use.

15
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 objectssuch as tables and
  indexesthat are related to each other

16
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

17
Data Definition Commands

• Creating the Database Structure
• CREATE SCHEMA AUTHORIZATION ltcreatorgt
• ExampleCREATE SCHEMA AUTHORIZATION JONES
• Schema logical database structurea group of
  database objects- such as tables and indexes
  that are related to each other.
• CREATE DATABASE ltdatabase namegt
• ExampleCREATE DATABASE CH6

18
Data Dictionary
Table 6.3
19
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

20
Some Common SQL Data Types
21
Some Common SQL Data Types
Data Type Format
Numeric NUMBER(L,D) INTEGER SMALLINT DEC
IMAL(L,D) Character CHAR(L) VARCHAR(L) Date
DATE
22
Data Definition Commands

• Creating Table Structures
• CREATE TABLE lttable namegt(ltattribute1 name and
  attribute1 characteristics, attribute2 name and
  attribute2 characteristics, attribute3 name and
  attribute3 characteristics, primary key
  designation, foreign key designation and
  foreign key requirementsgt)

23
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

24
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

25
Data Definition Commands

• CREATE TABLE VENDOR (V_CODE INTEGER NOT
  NULL UNIQUE, V_NAME VARCHAR(35) NOT NULL,
  V_CONTACT VARCHAR(15) NOT NULL, V_AREACODE
  CHAR(3) NOT NULL, V_PHONE CHAR(3) NOT
  NULL, V_STATE CHAR(2) NOT NULL, V_ORDER
  CHAR(1) NOT NULL, PRIMARY KEY (V_CODE))

26
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) ), ..., ...,)

27

• CREATE TABLE PRODUCT( P_CODE VARCHAR(10) NOT
  NULL UNIQUE, P_DESCRIPT VARCHAR(35) NOT NULL,
  P_INDATE DATE NOT NULL, P_ONHAND SMALLINT NOT
  NULL, P_MIN SMALLINT NOT NULL,
  P_PRICE DECIMAL(8,2) NOT NULL,
  P_DISCOUNT DECIMAL(4,1) NOT NULL,
  V_CODE SMALLINT, PRIMARY KEY (P_CODE), FOREIGN
  KEY (V_CODE) REFERENCES VENDOR ON
  DELETE RESTRICT ON UPDATE
  CASCADE)
• ON DELETE RESTRICTcannot delete a vender as long
  as there is a product that references that vender
• ON UPDATE CASCADEupdate V_CODE in VENDER ?
  update V_CODE in PRODUCT

28
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

29
SQL Indexes

• SQL Indexes
• Improve the efficiency of data search
• Created to meet particular search criteria
• CREATE INDEX P_CODEX ON PRODUCT(P_CODE)
• When the index field is a primary key whose
  values must not be duplicated
• CREATE UNIQUE INDEX P_CODEXON PRODUCT(P_CODE)

30
A Duplicated TEST Record

• Composite index
• Index based on two or more attributes
• Often used to prevent data duplication
• Try to enter duplicate data ? Error message
  duplicate value in index
• CREATE UNIQUE INDEX EMP_TESTDEX ON
  TEST(EMP_NUM, TEST_CODE, TEST_DATE)

31
Common SQL Data Manipulation Commands
32
Data Manipulation Commands

• Adding table rows
• INSERT INTO lttable namegt VALUES (attribute 1
  value, attribute 2 value, etc.)
• INSERT INTO VENDORVALUES(21225, Bryson, Inc.,
  Smithson, 615,223-3234, TN, Y)
• INSERT INTO PRODUCTVALUES(11 QER/31, Power
  painter, 15 psi., 3-nozzle, 03-Nov-03, 8.5,
  109.99, 0.00, 25595)

33
A Data View and Entry Form

• End-user applications are best created with
  utilities to create a form-based data view and
  entry screen .

34
Data Manipulation Commands

• Saving table changes
• COMMIT WORK
• COMMIT
• Any changes made to the table contentsare not
  physically saved on disk until
• Database is closed
• Program is closed
• COMMIT command is used

35
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 hasnt been used
• DELETE command - removes table row

36
Data Manipulation Commands

• Listing Table Rows
• SELECT
• Used to list contents of table
• Syntax
• SELECT columnlistFROM tablename
• Columnlist represents one or more attributes,
  separated by commas
• Asterisk ( )can be used as wildcard character
  to list all attributes

37
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

38
Figure 6.4 The Contents of the PRODUCT Table
39
Data Manipulation Commands

• Updating Table Rows
• UPDATE
• Modify data in a table
• Syntax
• UPDATE tablenameSET columnname expression ,
  columname expressionWHERE conditionlist
• If more than one attribute is to be updated in
  the row, separate corrections with commas

40
Data Manipulation Commands

• Updating table rows
• UPDATE PRODUCTSET P_INDATE 18-Jan-2004WHERE
  P_CODE 13-Q2/P2
• UPDATE PRODUCTSET P_INDATE 18-Jan-2004,
  P_PRICE 15.99, P_MIN 10WHERE P_CODE
  13-Q2/P2

41
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

42
Data Manipulation Commands

• Deleting Table Rows
• DELETE
• Deletes a table row
• Syntax
• DELETE FROM tablenameWHERE conditionlist
• WHERE condition is optional
• If WHERE condition is not specified, all rows
  from the specified table will be deleted

43
Data Manipulation Commands

• Deleting Table Rows
• DELETE FROM PRODUCTWHERE P_CODE 2238/QPD
• DELETE FROM PRODUCTWHERE P_MIN 5

44
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

45
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 columnlistFROM tablelist WHERE
  conditionlist

46
SELECT Queries

• Selected PRODUCT Table Attributes for VENDOR Code
  21344
• SELECT P_DESCRIPT, P_INDATE, P_PRICE, V_CODEFROM
  PRODUCTWHERE V_CODE 21344

47
The Microsoft Access QBE and its SQL
QBE (Query By Example) query generator
48
Comparison Operators
49
SELECT Queries

• Selected PRODUCT Table Attributes for VENDOR
  Codes Other than 21344
• SELECT P_DESCRIPT, P_INDATE, P_PRICE, V_CODEFROM
  PRODUCTWHERE V_CODE ltgt 21344

50
SELECT Queries

• Selected PRODUCT Table Attributes with a P_PRICE
  Restriction
• SELECT P_DESCRIPT, P_ONHAND, P_MIN, P_PRICEFROM
  PRODUCTWHERE P_PRICE lt 10

51
SELECT Queries

• Using Comparison Operators on Character
  Attributes
• SELECT P_DESCRIPT, P_ONHAND, P_MIN, P_PRICEFROM
  PRODUCTWHERE P_CODE lt 1558-QW1

52
SELECT Queries

• Using Comparison Operators on Dates
• SELECT P_DESCRIPT, P_ONHAND, P_MIN, P_PRICE,
  P_INDATEFROM PRODUCTWHERE P_INDATE gt
  20-Jan-2004

53
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_ONHANDP_PRICE FROM PRODUCT
54
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_ONHANDP_PRICE AS TOTVALUE FROM PRODUCT
55
SELECT Queries

• Arithmetic Operators The Rule of Precedence
• Perform operations within parentheses
• Perform power operations
• Perform multiplications and divisions
• Perform additions and subtractions

56
SELECT Queries

• Logical Operators AND, OR, NOT
• 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 FROM PRODUCTWHERE V_CODE 21344 OR
V_CODE 24288
57
SELECT Queries

• Logical Operator OR
• SELECT P_DESCRIPT, P_INDATE, P_PRICE, V_CODEFROM
  PRODUCTWHERE V_CODE21344 OR V_CODE24288

58
SELECT Queries

• Logical Operator AND
• SELECT P_DESCRIPT, P_INDATE, P_PRICE, V_CODEFROM
  PRODUCTWHERE P_PRICElt50 AND P_INDATEgt15-Jan-2004
  

59
SELECT Queries

• SELECT P_DESCRIPT, P_INDATE, P_PRICE, V_CODEFROM
  PRODUCTWHERE (P_PRICElt50 AND P_INDATEgt15-Jan-200
  4) OR V_CODE24288

60
SELECT Queries

• Special Operators
• BETWEEN - used to define range limits.
• IS NULL - used to check whether an attribute
  value is null
• LIKE - used to check for similar character
  strings.
• IN - 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.

61
SELECT Queries

• Special Operators
• BETWEEN is used to define range limits.
• SELECT FROM PRODUCTWHERE P_PRICE BETWEEN
  50.00 AND 100.00
• SELECT FROM PRODUCTWHERE P_PRICEgt50.00 AND
  P_PRICElt100.00

62
SELECT Queries

• Special Operators
• IS NULL is used to check whether an attribute
  value is null.
• SELECT P_CODE, P_DESCRIPT FROM PRODUCTWHERE
  P_MIN IS NULL
• SELECT P_CODE, P_DESCRIPT FROM PRODUCTWHERE
  P_INDATE IS NULL

63
SELECT Queries

• Special Operators
• LIKE is used to check for similar character
  strings.
• SELECT FROM VENDORWHERE V_CONTACT LIKE
  Smith
• SELECT FROM VENDORWHERE V_CONTACT LIKE
  SMITH
• cn , cany character, n?0
• _ c1

64
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 PRODUCTWHERE V_CODE IN (21344,
  24288)

65
SELECT Queries

• EXISTS used to check if a subquery returns any
  rows or not .
• SELECT FROM VENDERWHERE EXISTS (SELECT
  FROM PRODUCT WHERE
  P_ONHAND lt P_MIN AND VENDER.V_CODE
  PRODUCT.V_CODE)

66
Advanced Data Definition Commands

• All changes in the table structure are made by
  using the ALTER command
• Followed by a keyword that produces specific
  change
• Three options are available
• ADD
• MODIFY
• DROP

67
Changing a Columns Data Type

• ALTER can be used to change data type
• ALTER TABLE PRODUCTMODIFY (V_CODE CHAR(5))
• Some RDBMSs (such as Oracle) do not permit
  changes to data types unless the column is empty

68
Changing a Columns Data Characteristics

• Use ALTER to change data characteristics
• ALTER TABLE PRODUCTMODIFY (P_PRICE
  DECIMAL(9,2))
• If the column to be changed already contains
  data, changes in the columns characteristics are
  permitted if those changes do not alter the data
  type

69
Adding or Dropping a Column

• Use ALTER to add a column
• ALTER TABLE PRODUCTADD (P_SALECODE CHAR(1))
• Use ALTER to drop a column
• ALTER TABLE VENDORDROP COLUMN V_ORDER
• Some RDBMSs impose restrictions on the deletion
  of an attribute

70
Advanced Data Updates

• The Effect of Data Entry into the New P_SALECODE
  Column
• UPDATE PRODUCTSET P_SALECODE 2WHERE P_CODE
  1546-QQ2

71
Advanced Data Updates

• Update of the P_SALECODE Column in Multiple Data
  Rows
• UPDATE PRODUCTSET P_SALECODE 1WHERE P_CODE
  IN (2232/QWE, 2232/QTY)

72
Advanced Data Updates

• The Effect of Multiple Data Updates in the
  PRODUCT Table (MS Access)
• UPDATE PRODUCTSET P_SALECODE 2WHERE
  P_INDATE lt 25-Dec-2003
• UPDATE PRODUCTSET P_SALECODE 1WHERE
  P_INDATE gt 16-Jan-2004 AND P_INDATE lt
  10-Feb-2004

73
Advanced Data Updates
74
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 create the PART table structure
• Next add rows to new PART table using PRODUCT
  table rows

75
Copying Parts of Tables

• Need not be identical
• Column names
• Number of columns
• Column characteristics must match
• CREATE TABLE PARTPART_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_PRI
  CE)SELECT P_CODE, P_DESCRIPT, P_PRICEFROM
  PRODUCT

76
PART Attributes Copied from the PRODUCT Table
77
Adding Primary and Foreign Key Designations

• For reestablish the integrity rules
• Forgot to define
• Imported tables from a different database
• ALTER TABLE PRODUCTADD PRIMARY KEY (P_CODE)
• ALTER TABLE PRODUCTADD FOREIGN KEY (V_CODE)
  REFERENCES VENDOR
• ALTER TABLE PRODUCT ADD PRIMARY KEY (P_CODE)
  ADD FOREIGN KEY (V_CODE) REFERENCES VENDOR

78
Deleting a Table from the Database

• DROP TABLE lttable namegt
• DROP TABLE PART

79
Advanced Select Queries

• Ordering a Listing
• ORDER BY ltattributesgt
• SELECT P_CODE, P_DESCRIPT, P_INDATE, P_PRICEFROM
  PRODUCTORDER BY P_PRICE

80
Selected PRODUCT Table Attributes Ordered by
(Ascending) P_PRICE
81
Partial Listing of EMPLOYEE Table Contents
82

• Cascading order sequence
• SELECT EMP_LNAME,EMP_FNAME,EMP_INITIAL,EMP_AREACOD
  E,EMP_PHONEFROM EMPLOYEEORDER BY EMP_LNAME,
  EMP_FNAME, EMP_INITIAL

83

• Descending order
• SELECT P_CODE, P_DESCRIPT, P_INDATE, P_PRICEFROM
  PRODUCTWHERE P_INDATElt08-20-1999 AND
  P_PRICElt50.00ORDER BY V_CODE, P_PRICE DESC

84
A Listing of Distinct (Different) V_CODE Values
in the PRODUCT Table

• SELECT DISTINCT V_CODE FROM PRODUCT

85
Some Basic SQL Aggregate Functions
86

• COUNT
• SELECT COUNT(DISTINCT V_CODE)FROM PRODUCT

87

• MAX
• SELECT P_CODE, P_DESCRIPT, P_PRICEFROM
  PRODUCTWHERE P_PRICE (SELECT MAX(P_PRICE)
  FROM PRODUCT)

88

• SUM
• SELECT SUM(P_ONHANDP_PRICE) AS TOTVALUE FROM
  PRODUCT

89

• AVG
• SELECT P_DESCRIPT, P_ONHAND, P_PRICE, V_CODEFROM
  PRODUCTWHERE P_PRICE gt (SELECT AVG(P_PRICE)
  FROM PRODUCT)ORDER BY P_PRICE DESC

90

• Determine whether goods that have been in
  inventory for a certain length of time should
  be placed on special sale.

91

• Grouping Data - GROUP BY
• SELECT P_SALECODE, MIN(P_PRICE)FROM
  PRODUCTGROUP BY P_SALECODE

92
GROUP BY clause

• The GROUP BY clause is valid only when used in
  conjunction with one of the SQL aggregate
  functionsCOUNT, MIN, MAX, AVG, SUM
• SELECT V_CODE, COUNT(DISTINCT(P_CODE))
• FROM PRODUCT_2GROUP 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_2GROUP BY V_CODE

93
Incorrect and Correct Use of the GROUP BY Clause
ERROR
94
GROUP BYs 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 PRODUCTGROUP BY V_CODE
• SELECT V_CODE,COUNT(DISTINCT(P_CODE)),AVG(P_PRICE)
  
• FROM PRODUCTGROUP BY V_CODE HAVING
  AVG(P_PRICE)lt10

95
An Application of the HAVING Clause
96
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 PRODUCT_3 AS
• SELECT P_DESCROPT, P_ONHAND, P_PRICE
• FROM PRODUCT WHERE P_PRICE gt 50.00
• SELECT
• FROM PRODUCT_3

97
Creating a Virtual Table with the CREATE VIEW
Command
98
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

99
Creating Links Through Foreign Keys
100

• Joining Database Tables
• SELECT PRODUCT.P_DESCRIPT,PRODUCT.P_PRICE,
  VENDOR.V_NAME, VENDOR.V_CONTACT,
  VENDOR.V_AREACODE, VENDOR.V_PHONEFROM PRODUCT,
  VENDOR WHERE PRODUCT.V_CODEVENDOR.V_CODE

101
The Results of a Join
102
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_CODEVENDOR.V_CODE
  AND P_INDATE gt 15-Jan-2004 ORDER
  BY P_PRICE

103
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

104
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
  managers 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

105
The Contents of the EMP Table
106
Using an Alias to Join a Table to Itself
107
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

108
The Left Outer Join Results
109
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

110
The Right Outer Join Results
111
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

112
The Ch06_Artist Database ERD and Tables
113
A Data Dictionary for the Ch06_Artist Database
114
A Summary of Foreign Key Rules

• MN Relationships
• Foreign key location Composite entity

1
M
exhibit
PAINING
GALLERY
115
A Summary of Foreign Key Rules

• 1M Relationships
• Foreign key location Many side

Foreign key
1
M
exhibit
PAINING
GALLERY
116
A Summary of Foreign Key Rules
117
Procedural language

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

118
Procedural SQL

• Shortcomings of SQL
• SQL doesnt 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
• Solutions
• Embedded SQL
• SQL statements can be inserted within the
  procedural programming language
• Shared Code
• Critical code is isolated and shared by all
  application programs.
• This approach allows better maintenance.
• Procedural SQL(PL/SQL)

119
Procedural SQL

• Procedural SQL
• Procedural SQL allows the use of procedural code
  and SQL statements that are stored 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

120
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)
121
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.

122
The Revised PRODUCT Table

• If P_ONHAND(??) lt P_MIN(????)set P_REORDER
  Yes

123
The PRODUCT List Output in the Oracle RDBMS

• in Oracle P_REORDER 1/0 for Yes/No

124
Procedural SQL

• Syntax to create a trigger in ORACLE
• CREATE OR REPLACE TRIGGER lttrigger_namegtBEFORE/A
  FTERDELETE/INSERT/UPDATE OF ltcolumn_namegt ON
  lttable_namegtFOR EACH ROWBEGIN PL/SQL
  instructions END

125
Procedural SQL

• Creation of the Oracle Trigger for the PRODUCT
  Table
• CREATE OR REPLACE TRIGGER TRG_PRODUCT_REORDERAFTE
  R INSERT OR UPDATE OF P_ONHAND ON
  PRODUCTBEGIN UPDATE PRODUCT
• SET P_REORDER 1
• WHERE P_ONHAND lt P_MINEND

126
Creation of the Oracle Trigger for the PRODUCT
Table
127
The PRODUCT Tables P_REORDER Field is Updated by
the Trigger
UPDATE PRODUCT SET P_ONHAND 4 WHERE P_CODE
11QER/31
128
The P_REORDER Value Mismatch
UPDATE PRODUCT SET P_MIN 7 WHERE P_CODE
2232/QWE
129

• The Second Version of the PRODUCT_REORDER Trigger
• CREATE OR REPLACE TRIGGER TRG_PRODUCT_REORDERAFTE
  R INSERT OR UPDATE OF P_ONHAND, P_MIN ON
  PRODUCTBEGIN UPDATE PRODUCT
• SET P_REORDER 1
• WHILE P_ONHAND lt P_MINEND

130
The Second Version of the PRODUCT_REORDER Trigger
131
UPDATE PRODUCT SET P_MIN 10 WHERE P_CODE
23114-AA
132
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
Never reset it to 0!
4 25
133

• The Third Version of the PRODUCT_REORDER Trigger
• CREATE OR REPLACE TRIGGER TRG_PRODUCT_REORDERBEFO
  RE INSERT OR UPDATE OF P_ONHAND, P_MIN ON
  PRODUCTBEGIN IF NEW.P_ONHAND lt NEW.P_MIN
  THEN
• NEW.P_REORDER 1
• ELSE
• NEW.P_REORDER 0
• END IFEND

134
The Third Version of the Product Reorder Trigger
135

• After creating the new trigger, we can execute
  an UPDATE statement to fire it.
• UPDATE PRODUCT
• SET P_ONHAND P_ONHAND

136
Execution of the Third Trigger Version
137
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.

138
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 statementsEND
• Syntax to invoke a stored procedure
• EXEC store_procedure_name(parameter, parameter, )

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

140
Creating and Invoking A Simple Stored Procedure
141
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
142
Creation of the PROD_SALE Stored Procedure
143
Executing the PROD_SALE Stored Procedure
144
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-typeAS BEGIN PL/SQL
  statements RETURN (value) END