Database Systems: Design, Implementation, and Management THIRD EDITION

1
Database Systems Design, Implementation, and
ManagementTHIRD EDITION

• CHAPTER 3
• An Introduction to Structured Query Language(SQL)

2
Chapter Objectives

• Understand basics of database languages
• Learn how to create a database using SQL
• Learn how to manipulate and manage a database
  using SQL

3
Chapter Overview

• 3.1 Introduction to SQL
• 3.2 Data Definition Commands
• 3.3 Basic Data Management
• 3.4 Queries
• 3.5 Advanced Data Management Commands
• 3.6 More Complex Queries and SQL Functions

4
Introduction to SQL

• SQL meets ideal database language requirements
• SQL coverage fits into three categories
• (1) Data definition.
• (2) Data management.
• (3) Data query.
• SQL is relatively easy to learn.
• ANSI prescribes a standard SQL.

5
Introduction to SQL

• Reasons for Studying SQL
• The ANSI standardization effort has led to a de
  facto query standard for relational databases.
• SQL has become the basis for present and expected
  future DBMS integration efforts.
• SQL has become the catalyst in the development of
  distributed databases and database client/server
  architecture.

6
Data Definition Commands

• The Database Model
• CH3 Database -- PRODUCT and VENDOR tables
• Each product is supplied by only a single vendor.
• A vendor may supply many products.

Figure 3.1 The Database Model
7
Data Definition Commands

• The Tables and Their Components
• Some observations on the PRODUCT and VENDOR
  tables
• The VENDOR table contains vendors who are not
  referenced in the PRODUCT table. PRODUCT is
  optional to VENDOR.
• All 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.

8
Data Definition Commands

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

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 requirementgt)

10
Data Definition Commands
Table 3.1 Some Common SQL Data Types
11
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))

12
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)

13
Data Definition Commands

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

14
Basic Data Management

• Data Entry
• INSERT INTO lttable namegt VALUES (attribute 1
  value, attribute 2 value, etc.)
• Examples
• INSERT INTO VENDORVALUES(21225, Brson, Inc.,
  Smithson, 615,223-3234, TN, Y)
• INSERT INTO PRODUCTVALUES(11 QER/31, Power
  painter, 15 psi., 3-nozzle, 12/2/96, 8.5,
  109.99, 0.00, 25595)

15
Basic Data Management

• Checking the Table Contents
• SELECT ltattribute namesgt FROM lttable namesgt
• Examples
• SELECT FROM PRODUCT
• SELECT P_CODE, P_DESCRIPT, P_INDATE, P_ONHAND,
  P_MIN, P-PRICE, P_DISCOUNT, V_CODE FROM PRODUCT

Database Table 3.2 The Product Tables First Two
Rows
16
Basic Data Management

• Saving the Table Contents
• COMMIT lttable namesgt
• Example
• COMMIT PRODUCT

17
Basic Data Management

• Adding Data to the Table
• INSERT INTO lttable namegt VALUES(attribute
  values)
• Example
• INSERT INTO PRODUCTVALUES(14-Q1/L3, 9.00-in.
  Pwr. saw lade, 11/12/96, 18,12, 17.49, 0.00,
  21344)

18
Basic Data Management
Database Table 3.3 The Completed PRODUCT Table
19
Basic Data Management

• Making a Correction
• UPDATE lttable namegtSET ltattribute namegt
  ltattribute valuegtWHERE ltattribute namegt
  ltattribute valuegt
• Examples
• 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

20
Basic Data Management

• Deleting Table Rows
• DELETE FROM lttable namesgtWHERE ltattribute namegt
  ltattribute valuegt
• Example
• DELETE FROM PRODUCTWHERE P_CODE 2238/QPD
• DELETE FROM PRODUCTWHERE P_MIN 5

21
Queries

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

Database Table 3.4 Selected PRODUCT Table
Attributes for the VENDOR CODE 21344
22
Queries

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

Database Table 3.5 Selected PRODUCT Table
Attributes for VENDOR CODE Other Than 21344
23
Queries

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

Database Table 3.6 Selected PRODUCT Table
Attributes with a P-PRICE Restriction
24
Queries

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

Database Table 3.7 Selected PRODUCT Table
Attributes The ASCII Code Effect
25
Queries
Using Mathematical Operators on Dates SELECT
P_DESCRIPT, P_ONHAND, P_MIN, P_PRICEFROM
PRODUCTWHERE P_INDATE gt 11/25/96
Database Table 3.8 Selected PRODUCT Table
Attributes Date Restriction
26
Queries

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

Database Table 3.9 Selected PRODUCT Table
Attributes The Logical OR
27
Queries

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

Database Table 3.10 Selected PRODUCT Table
Attributes The Logical AND
28
Queries

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

Database Table 3.11 Selected PRODUCT Table
Attributes The Logical AND and OR
SELECT FROM PRODUCTWHERE V_CODE NOT 21344
29
Queries

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

30
Queries

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

31
Queries

• LIKE is used to check for similar character
  strings.
• Examples
• SELECT FROM VENDORWHERE V_CONTACT LIKE
  Smith

Database Table 3.12 Selected PRODUCT Table
Attributes Partial String Match
32
Queries

• SELECT FROM VENDORWHERE UPPER(V_CONTACT) LIKE
  SMITH
• SELECT FROM VENDORWHERE V_CONTACT NOT LIKE
  Smith

Database Table 3.13 Selected PRODUCT Table
Attributes Partial String Comparisons
SELECT FROM VENDORWHERE V_CONTACT LIKE
Johns_n
33
Queries

• IN is used to check whether an attribute value
  matches a value contained within a (sub)set of
  listed values.
• Example
• SELECT FROM PRODUCTWHERE V_CODE IN (21344,
  24288)
• EXISTS is used to check whether an attribute has
  value.
• Example
• DELETE FROM PRODUCTWHERE P_CODE EXISTS
• SELECT FROM PRODUCTWHERE V_CODE EXISTS

34
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

35
Advanced Data Management Commands

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

36
Advanced Data Management Commands

• Entering Data into the New Column
• UPDATE lttable namegtSET ltattribute namegt
  ltattribute valuegtWHERE ltconditiongt
• Examples
• UPDATE PRODUCTSET P_SALECODE 2WHERE P_CODE
  1546-QQ2

Database Table 3.14 Selected PRODUCT Table
Attributes Effect of Data Entry into the New
P_SALECODE Attribute
37
Advanced Data Management Commands

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

Database Table 3.15 Selected PRODUCT Table
Attributes Multiple Data Entry
38
Advanced Data Management Commands

• UPDATE PRODUCTSET P_SALECODE 2WHERE
  P_INDATE lt 8/15/96
• UPDATE PRODUCTSET P_SALECODE 1WHERE
  P_INDATE gt 11/15/96AND P_INDATE lt 12/1/96

39
Advanced Data Management Commands
Database Table 3.16 Selected PRODUCT Table
Attributes Multiple Update Effect
40
Advanced Data Management Commands

• Copying Parts of Tables
• INSERT INTO ltreceiving tablegt ltreceiving tables
  column namesgtSELECT ltcolumn names of the columns
  to be copiedgtFROM ltcontributing table namegt
• Example
• INSERT INTO PART (PART_CODE, PART_DESCRIPT,
  PART_PRICE)SELECT P_CODE, P_DESCRIPT,
  P_PRICEFROM PRODUCT

41
Advanced Data Management Commands
Database Table 3.17 The PART Attributes Copied
from the PRODUCT Table
42
Advanced Data Management Commands

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

43
Advanced Data Management Commands

• Primary and Foreign Key Designation
• Examples
• ALTER TABLE PRODUCTADD PRIMARY KEY (P_CODE)
• ALTER TABLE PRODUCTADD FOREIGN KEY (V_CODE)
  REFERENCES VENDOR

44
More Complex Queries and SQL Functions

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

45
More Complex Queries and SQL Functions
Database Table 3.18 Selected PRODUCT Table
Attributes Ordered by (Ascending) P_PRICE
46
More Complex Queries and SQL Functions

• SELECT P_CODE, P_DESCRIPT, P_INDATE, P_PRICEFROM
  PRODUCTORDER BY P_PRICE DESC

Database Table 3.19 Selected PRODUCT Table
Attributes Ordered by (Descending) P_PRICE
47
More Complex Queries and SQL Functions

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

Database Table 3.21 The Telephone List Query
Results
48
More Complex Queries and SQL Functions

• SELECT P_CODE, P_DESCRIPT, P_INDATE, P_PRICEFROM
  PRODUCTWHERE P_INDATE lt 9/15/96AND P_PRICE lt
  50.00ORDER BY V_CODE, P_PRICE, DESC

Database Table 3.22 A Query Based on Multiple
Restriction
49
More Complex Queries and SQL Functions

• Listing Unique Values
• SELECT DISTINCT ltattributesgt ...
• Example
• SELECT DISTINCT V_CODEFROM PRODUCT

Database Table 3.23 A Listing of Distinct
(Different) V_CODE Values in the PRODUCT Table
50
More Complex Queries and SQL Functions
Table 3.4 The SQL Numeric Functions
51
More Complex Queries and SQL Functions

• SQLs Numeric Functions
• COUNT
• Examples
• SELECT COUNT(DISTINCT V_CODE)FROM PRODUCT
• SELECT COUNT(DISTINCT V_CODE)FROM PRODUCTWHERE
  P_PRICE lt 10.00

52
More Complex Queries and SQL Functions

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

53
More Complex Queries and SQL Functions

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

54
More Complex Queries and SQL Functions

• SUM
• Example
• SELECT SUM(P_ONHAND P_PRICE)FROM PRODUCT

55
More Complex Queries and SQL Functions

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

56
More Complex Queries and SQL Functions

• Virtual Tables Creating a View
• CREATE VIEW ltview namegt ASSELECT FROM WHERE
  
• Example
• CREATE VIEW PRODUCT_3 ASSELECT P_DESCRIPT,
  P_ONHAND, P_PRICEFROM PRODUCTWHERE P_PRICE gt
  50.00

57
More Complex Queries and SQL Functions

• SQL Indexes
• CREATE INDEX ltindex namegt ON lttable
  namegt(ltattribute namegt)
• Examples
• CREATE INDEX P_CODEXON PRODUCT(P_CODE)
• CREATE UNIQUE INDEX P_CODEXON PRODUCT(P_CODE)

58
More Complex Queries and SQL Functions

• Joining Database Tables
• Examples
• 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
• SELECT P_DESCRIPT, P_PRICE, V_NAME, V_CONTACT,
  V_AREACODE, V_PHONEFROM PRODUCT, VENDORWHERE
  PRODUCT.V_CODE VENDOR.V_CODEORDER BY P_PRICE

59
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
  11/15/96
• SELECT P_DESCRIPT, P_PRICE, V_NAME, V_CONTACT,
  V_AREACODE, V_PHONEFROM PRODUCT A, VENDOR
  BWHERE A.V_CODE B.V_CODEORDER BY P_PRICE

Basic Oracle SQL