An Introduction to Structured Query Language SQL

1
Chapter 3 Structured Query Language (SQL)
Database Systems Design, Implementation, and
Management 4th Edition Peter Rob Carlos
Coronel
2
Introduction to SQL

• SQL meets ideal database language requirements
• SQL coverage fits into two categories
• Data definition
• Data manipulation
• SQL is relatively easy to learn.
• ANSI prescribes a standard SQL.

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

Figure 3.1
4
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.
• 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.

5
(No Transcript)
6
Data Definition Commands

• Creating the Database Structure
• CREATE SCHEMA AUTHORIZATION ltcreatorgt
• ExampleCREATE SCHEMA AUTHORIZATION JONES
• CREATE DATABASE ltdatabase namegt
• ExampleCREATE DATABASE CH3

7
A Data Dictionary for the CH3 Database
Table 3.1
8
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
9
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)

10
Data Definition Commands

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

11
Data Definition Commands

• CREATE TABLE PRODUCT(P_CODE VCHAR(10) NOT
  NULL UNIQUE, P_DESCRIPT VCHAR(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)

12
Data Definition Commands

• SQL Integrity Constraints
• Entity Integrity
• PRIMARY KEY
• NOT NULL and UNIQUE
• Referential Integrity
• FOREIGN KEY
• ON DELETE
• ON UPDATE

13
SQL Command Coverage
Table 3.3
14
Basic Data Management

• Data Entry
• 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, 07/02/1999, 8.5,
  109.99, 0.00, 25595)

15
Figure 3.3 A Data View and Entry Screen
16
Basic Data Management

• Saving the Table Contents
• COMMIT lttable namesgt
• COMMIT PRODUCT
• Listing the Table Contents
• SELECT FROM PRODUCT
• SELECT P_CODE, P_DESCRIPT, P_INDATE, P_ONHAND,
  P_MIN, P-PRICE, P_DISCOUNT, V_CODE FROM PRODUCT

17
Figure 3.4 The Contents of the PRODUCT Table
18
Basic Data Management

• Making a Correction
• UPDATE PRODUCTSET P_INDATE 12/11/96WHERE
  P_CODE 13-Q2/P2
• UPDATE PRODUCTSET P_INDATE 12/11/96, P_PRICE
  15.99, P_MIN10WHERE P_CODE 13-Q2/P2
• Restoring the Table Contents
• ROLLBACK

19
Basic Data Management

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

20
Queries

• Partial Listing of Table Contents
• SELECT ltcolumn(s)gtFROM lttable namegtWHERE
  ltconditionsgt
• SELECT P_DESCRIPT, P_INDATE, P_PRICE, V_CODEFROM
  PRODUCTWHERE V_CODE 21344

Figure 3.5
21
Figure 3.6 The Microsoft Access QBE and Its SQL
22
Queries
Mathematical Operators
Table 3.4
23
Queries

• SELECT P_DESCRIPT, P_INDATE, P_PRICE, V_CODEFROM
  PRODUCTWHERE V_CODE ltgt 21344

Figure 3.7
24
Queries

• SELECT P_DESCRIPT, P_ONHAND, P_MIN, P_PRICEFROM
  PRODUCTWHERE P_PRICE lt 10

Figure 3.8
25
Queries

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

Figure 3.9
26
Queries

• Using Mathematical Operators on Dates
• SELECT P_DESCRIPT, P_ONHAND, P_MIN, P_PRICE,
  P_INDATEFROM PRODUCTWHERE P_INDATE gt
  08/15/1999

Figure 3.10
27
Queries

• Logical Operators AND, OR, and NOT
• SELECT P_DESCRIPT, P_INDATE, P_PRICE, V_CODEFROM
  PRODUCTWHERE V_CODE 21344OR V_CODE 24288

Figure 3.11
28
Queries

• SELECT P_DESCRIPT, P_INDATE, P_PRICE, V_CODEFROM
  PRODUCTWHERE P_PRICE lt 50AND P_INDATE gt
  07/15/1999

Figure 3.12
29
Queries

• SELECT P_DESCRIPT, P_INDATE, P_PRICE, V_CODEFROM
  PRODUCTWHERE (P_PRICE lt 50 AND P_INDATE gt
  07/15/1999)OR V_CODE 24288

Figure 3.13
30
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 a value contained within a (sub)set of
  listed values.
• EXISTS - used to check whether an attribute has a
  value. In effect, EXISTS is the opposite of IS
  NULL.

31
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_PRICE gt 50.00AND
  P_PRICE lt 100.00

32
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

33
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

34
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)
• EXISTS is used to check whether an attribute has
  value.
• DELETE FROM PRODUCTWHERE P_CODE EXISTS
• SELECT FROM PRODUCTWHERE V_CODE EXISTS

35
Advanced Data Management Commands

• Changing Table Structures
• ALTER TABLE lttable namegtMODIFY (ltcolumn namegt
  ltnew column characteristicsgt)
• ALTER TABLE lttable namegtADD (ltcolumn namegt ltnew
  column characteristicsgt)

36
Advanced Data Management Commands

• Changing a Columns Data Type
• ALTER TABLE PRODUCTMODIFY (V_CODE CHAR(5))
• Changing Attribute Characteristics
• ALTER TABLE PRODUCTMODIFY (P_PRICE
  DECIMAL(9,2))
• Adding a New Column to the Table
• ALTER TABLE PRODUCTADD (P_SALECODE CHAR(1))

37
Advanced Data Management Commands

• UPDATE PRODUCTSET P_SALECODE 2WHERE P_CODE
  1546-QQ2

Figure 3.14 Selected PRODUCT Table Attributes
Multiple Data Entry
38
Advanced Data Management Commands

• UPDATE PRODUCTSET P_SALECODE 1WHERE P_CODE
  IN (2232/QWE, 2232/QTY)

Figure 3.15 Selected PRODUCT Table Attributes
Multiple Data Entry
39
Advanced Data Management Commands

• UPDATE PRODUCTSET P_SALECODE 2WHERE
  P_INDATE lt 07/10/1999
• UPDATE PRODUCTSET P_SALECODE 1WHERE
  P_INDATE gt 08/15/1999AND P_INDATE lt
  08/20/1999

40
Advanced Data Management Commands
Selected PRODUCT Table Attributes Multiple
Update Effect
Figure 3.16
41
The Arithmetic Operators
Table 3.5
42
Advanced Data Management Commands

• Copying Parts of Tables
• 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_PRICE)SELECT P_CODE, P_DESCRIPT,
  P_PRICEFROM PRODUCT

43
The Part Attributes Copied from the PRODUCT Table
Figure 3.17
44
Advanced Data Management Commands

• Deleting a Table from the Database
• DROP TABLE lttable namegt
• DROP TABLE PART

45
Advanced Data Management Commands

• Primary and Foreign Key Designation
• 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

46
Chapter 3Structured Query Language (SQL)
Database Systems Design, Implementation, and
Management 4th Edition Peter Rob Carlos Coronel
47
More Complex Queries and SQL Functions

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

48
Selected PRODUCT Table Attributes Ordered
by (Ascending) P_PRICE
Figure 3.18
49
The Partial Listing of the EMPLOYEE Table
Figure 3.19
50

• SELECT EMP_LNAME, EMP_FNAME, EMP_INITIAL,
  EMP_AREACODE, EMP_PHONEFROM EMPLOYEEORDER BY
  EMP_LNAME, EMP_FNAME, EMP_INITIAL

Figure 3.20
51

• SELECT P_CODE, P_DESCRIPT, P_INDATE, P_PRICEFROM
  PRODUCTWHERE P_INDATE lt 08/20/1999AND P_PRICE
  lt 50.00ORDER BY V_CODE, P_PRICE, DESC

Figure 3.21 A Query Based on Multiple
Restrictions
52
More Complex Queries and SQL Functions

• Listing Unique Values
• SELECT DISTINCT V_CODEFROM PRODUCT

Figure 3.22 A Listing of Distinct V_CODE
Values in the PRODUCT
Table
53
Some Basic SQL Numeric Functions
Table 3.6
54
Querying a Query Nested Process
Figure 3.23
55
COUNT Function Output Examples
Figure 3.24
56
MAX and MIN Function Output Examples
Figure 3.25
57
More Complex Queries and SQL Functions

• SUM
• SELECT SUM(P_ONHANDP_PRICE)FROM PRODUCT
• 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

58
AVG Function Output Examples
Figure 3.26
59

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

60
Improper Use of the GROUP BY Clause
Figure 3.28
61
An Application of the HAVING Clause
Figure 3.29
62
More Complex Queries and SQL Functions

• Virtual Tables Creating a View

Figure 3.30
63
More Complex Queries and SQL Functions

• SQL Indexes
• CREATE INDEX P_CODEXON PRODUCT(P_CODE)
• CREATE UNIQUE INDEX P_CODEXON PRODUCT(P_CODE)

64
More Complex Queries and SQL Functions

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

Table 3.7 Creating Links Through Foreign Keys
65
The Results of a JOIN
Figure 3.31
66
More Complex Queries and SQL Functions

• SELECT P_DESCRIPT, P_PRICE, V_NAME, V_CONTACT,
  V_AREACODE, V_PHONEFROM PRODUCT, VENDORWHERE
  PRODUCT.V_CODE VENDOR.V_CODEAND P_INDATE gt
  08/15/1999

Figure 3.32 An Ordered and Limited Listing
After a JOIN
67
Procedural SQL

• Shortcomings of SQL
• SQL doesnt support execution of a stored set of
  procedures based on some logical condition.
• SQL fails to support the looping operations.
• Solutions
• Embedded SQL
• To remedy the above shortcomings, SQL statements
  can be inserted within the procedural programming
  language
• The embedded SQL approach involves the
  duplication of application code in many programs.
• Shared Code
• Critical code is isolated and shared by all
  application programs.
• This approach allows better maintenance and logic
  control.
• Procedural SQL

68
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 procedural SQL (PL/SQL) to
  create
• Triggers
• Stored procedures
• PL/SQL functions

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

70
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.
• Triggers add processing power to the RDBMS and to
  the database system.

71
The Revised PRODUCT Table
Figure 3.33
72
The PRODUCT List Output in the Oracle RDBMS
Figure 3.34
73
Procedural SQL

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

74
Creation of the Oracle Trigger for the PRODUCT
Table
Figure 3.35
75
The PRODUCT Tables P_REORDER Field is Updated by
the Trigger
Figure 3.36
76
The P_REORDER Value Mismatch
Figure 3.37
77
The Second Version of the PRODUCT_REORDER Trigger
Figure 3.38
78
Figure 3.39
79
The P_REORDER Flag Has Not Been Properly Set
After Increasing the P_ONHAND Value
Figure 3.40
80
The Third Version of the Product Reorder Trigger
Figure 3.41
81
Execution of the Third Trigger Version
Figure 3.42
82
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.
• The use of stored procedures reduces network
  traffic, thus improving performance.

83
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,
  )

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

85
Creating and Invoking A Simple Stored Procedure
Figure 3.43
86
The PROD_SALE Stored Procedure
Figure 3.44
87
Creation of the PROD_SALE Stored Procedure
Figure 3.45
88
Executing the PROD_SALE Stored Procedure
Figure 3.46
89
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

90
The Y2K Problem

• Problem
• Many database vendors use 2-digit date formats as
  the default. How the 2-digit year is viewed
  depends on how the DBMS vendor treats dates.
• Solutions
• Design and implement database applications that
  always enter and display dates with four-digit
  years and use a Julian date field format.
• Julian date stores date field values as the
  number of days since a predetermined date.

91
The Default P_INDICATE Two-Digit Year Format
Figure 3.47
92
Formatting the Date Fields to Four-Digit Years
Figure 3.48
93
Using the Input Mask to Force Four-Digit Year
Entries in MS Access
Figure 3.49