Structured Query Language

1
Structured Query Language

• Summary

2
Structure Query Language (SQL)

• A data query, definition, manipulation language
  for relational databases.
• It is an internationally accepted standard for
  relational database systems (ANSI approved)

3
Data Definition Commands

• SQL Integrity Constraints
• Entity Integrity
• PRIMARY KEY
• NOT NULL
• Referential Integrity
• FOREIGN KEY

4
SQL Command Coverage
5
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.

6
Some Basic SQL Numeric Functions
7
More Complex Queries and SQL Functions

• Virtual Tables Creating a View

8
SUMMARY OF KEY SQL CLAUSES

• GROUP BY clause
• use to specify how to group the rows
• often useful in conjunction with the COUNT()
  statement
• DISTINCT
• use to eliminate duplicate rows from the result
• AND, OR, NOT
• use for compound conditions

9
SUMMARY

• LIKE clause
• use for pattern-matching
• COUNT clause
• use for counting the of rows that satisfy the
  where condition
• ORDER BY clause
• use to sort the result of the query
• use DESC to change the order to descending

10
SUMMARY

• WHERE clause
• use to limit the rows to be included in the
  result of the query
• HAVING clause
• use to limit the groups that are included in the
  result of the query
• often used in conjunction with the COUNT ( )
  statement

11
SUMMARY

• Subqueries
• may be used on one or multiple tables
• subqueries are often used when the selection
  criteria in the WHERE clause must be derived
• inner subquery is always executed first
• if the inner subquery returns multiple values,
  use IN, ANY or ALL
• note that IN is the same as ANY

12
SUMMARY

• Joining the tables
• use the WHERE clause OR
• use the subquery with IN
• IN -- the outer query uses the WHERE clause to
  restrict the rows to be compared with the result
  of the inner query

13
SUMMARY

• When querying multiple tables use subquery within
  a subquery and the IN clause or the WHERE clause
  with compound conditions (e.g. AND, OR, etc.)
• SELECT tagnum, compid, Employee.empno, empname
• FROM Employee
• WHERE empnum IN
• (SELECT
  empnum
• FROM
  Pc
• WHERE
  location Home)

14
Summary

• ALL clause vs ANY clause
• use the ALL clause to ensure that the outer query
  result satisfies all the values obtained from the
  inner query results.
• use the ANY clause to ensure that the outer query
  result satisfies at least one value produced by
  the subquery
• Select packid, packname
• From Package
• Where packcost lt ALL
• (Select
  softcost
• from Software
• Where packid
  WP08)

15
Procedural SQL

• Triggers and Stored Procedures

16
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

17
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

18
Procedural SQL

• Triggers
• A trigger is procedural SQL code (stored
  procedure) that is automatically invoked by the
  RDBMS upon the occurrence of a data manipulation
  event (INSERT, UPDATE, DELETE).
• 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.

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

20
The PRODUCT List Output in the Oracle RDBMS
Figure 3.34
21
Creation of the Oracle Trigger for the PRODUCT
Table
Figure 3.35
22
The PRODUCT Tables P_REORDER Field is Updated by
the Trigger
Figure 3.36
23
Trigger example

• CREATE TABLE Customer_type (
• Customer_type_id CHAR(3) NOT NULL,
• Type_description CHAR(11) NULL,
• Type_discount NUMERIC(3,2) NULL,
• PRIMARY KEY (Customer_type_id)
• )
• CREATE TABLE Customer (
• Customer_id CHAR(8) NOT NULL,
• Customer_lname VARCHAR(10) NULL,
• Customer_fname VARCHAR(8) NULL,
• Customer_address CHAR(20) NULL,
• Zip_code INTEGER NOT NULL,
• Customer_type_id CHAR(3) NOT NULL,
• PRIMARY KEY (Customer_id),
• FOREIGN KEY (Customer_type_id)
• REFERENCES
  Customer_type,
• FOREIGN KEY (Zip_code)
• REFERENCES City
• )

24
Trigger example

• create trigger tD_Customer_type after DELETE on
  Customer_type for each row
• -- DELETE trigger on Customer_type
• declare numrows INTEGER
• begin
• / Customer_type describes Customer ON PARENT
  DELETE RESTRICT /
• select count() into numrows
• from Customer
• where
• / JoinFKPK(Customer,Old," ","
  and") /
• Customer.Customer_type_id
  old.Customer_type_id
• if (numrows gt 0)
• then
• raise_application_error(
• -20001,
• 'Cannot DELETE Customer_type because
  Customer exists.'
• )
• end if

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

26
EXAMPLE OF A STORED PROCEDURE

• CREATE PROCEDURE saleOrderProc _at_inputmonth int as
• select orderid, convert(varchar, orderdate, 107)
  as 'Order Date'
• from salesorder
• where DATEPART(month, orderdate) gt _at_inputmonth
• order by datepart(month, orderdate)
• EXEC saleorderproc _at_inputmonth 3

27
EXAMPLE OF A STORED PROCEDURE

• CREATE PROCEDURE insertCustomer
• _at_custsId int
• AS
• INSERT customer
• VALUES (_at_custId)
• EXEC insertCustomer 12345
• SELECT FROM customer

28
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