Chapter%203%20Structured%20Query%20Language%20(SQL) - PowerPoint PPT Presentation

About This Presentation
Title:

Chapter%203%20Structured%20Query%20Language%20(SQL)

Description:

Chapter 3 Structured Query Language (SQL) Database Systems: Design, Implementation, and Management 4th Edition Peter Rob & Carlos Coronel – PowerPoint PPT presentation

Number of Views:246
Avg rating:3.0/5.0
Slides: 49
Provided by: Chan3156
Category:

less

Transcript and Presenter's Notes

Title: Chapter%203%20Structured%20Query%20Language%20(SQL)


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

3
Selected PRODUCT Table Attributes Ordered
by (Ascending) P_PRICE
Figure 3.18
4
The Partial Listing of the EMPLOYEE Table
Figure 3.19
5
  • SELECT EMP_LNAME, EMP_FNAME, EMP_INITIAL,
    EMP_AREACODE, EMP_PHONEFROM EMPLOYEEORDER BY
    EMP_LNAME, EMP_FNAME, EMP_INITIAL

Figure 3.20
6
  • 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
7
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
8
Some Basic SQL Numeric Functions
Table 3.6
9
Querying a Query Nested Process
Figure 3.23
10
COUNT Function Output Examples
Figure 3.24
11
MAX and MIN Function Output Examples
Figure 3.25
12
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

13
AVG Function Output Examples
Figure 3.26
14
  • Grouping Data
  • GROUP BY
  • SELECT P_SALECODE, MIN(P_PRICE)FROM
    PRODUCT_2GROUP BY P_SALECODE

15
Improper Use of the GROUP BY Clause
Figure 3.28
16
An Application of the HAVING Clause
Figure 3.29
17
More Complex Queries and SQL Functions
  • Virtual Tables Creating a View

Figure 3.30
18
More Complex Queries and SQL Functions
  • SQL Indexes
  • CREATE INDEX P_CODEXON PRODUCT(P_CODE)
  • CREATE UNIQUE INDEX P_CODEXON PRODUCT(P_CODE)

19
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
20
The Results of a JOIN
Figure 3.31
21
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
22
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

23
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

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

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

26
The Revised PRODUCT Table
Figure 3.33
27
The PRODUCT List Output in the Oracle RDBMS
Figure 3.34
28
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

29
Creation of the Oracle Trigger for the PRODUCT
Table
Figure 3.35
30
The PRODUCT Tables P_REORDER Field is Updated by
the Trigger
Figure 3.36
31
The P_REORDER Value Mismatch
Figure 3.37
32
The Second Version of the PRODUCT_REORDER Trigger
Figure 3.38
33
Figure 3.39
34
The P_REORDER Flag Has Not Been Properly Set
After Increasing the P_ONHAND Value
Figure 3.40
35
The Third Version of the Product Reorder Trigger
Figure 3.41
36
Execution of the Third Trigger Version
Figure 3.42
37
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.

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

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

40
Creating and Invoking A Simple Stored Procedure
Figure 3.43
41
The PROD_SALE Stored Procedure
Figure 3.44
42
Creation of the PROD_SALE Stored Procedure
Figure 3.45
43
Executing the PROD_SALE Stored Procedure
Figure 3.46
44
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

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

46
The Default P_INDICATE Two-Digit Year Format
Figure 3.47
47
Formatting the Date Fields to Four-Digit Years
Figure 3.48
48
Using the Input Mask to Force Four-Digit Year
Entries in MS Access
Figure 3.49
Write a Comment
User Comments (0)
About PowerShow.com