Database Systems: Design, Implementation, and Management THIRD EDITION - PowerPoint PPT Presentation

Loading...

PPT – Database Systems: Design, Implementation, and Management THIRD EDITION PowerPoint presentation | free to view - id: 18f1b6-ZDc1Z



Loading


The Adobe Flash plugin is needed to view this content

Get the plugin now

View by Category
About This Presentation
Title:

Database Systems: Design, Implementation, and Management THIRD EDITION

Description:

Data Definition Commands. The Tables and Their Components ... Data Definition Commands. Creating Table Structures. CREATE TABLE table name ... – PowerPoint PPT presentation

Number of Views:179
Avg rating:3.0/5.0
Slides: 60
Provided by: chan227
Category:

less

Write a Comment
User Comments (0)
Transcript and Presenter's Notes

Title: 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
About PowerShow.com