Chapter 3 Structured Query Language (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