Chapter 4 An Introduction to SQL

1
Chapter 4 An Introduction to SQL
2
Outline

• Introduction and Overview
• The Catalog
• Views
• Transactions
• Embedded SQL
• Dynamic SQL and SQL/CLI
• SQL Is Not Perfect
• SQL fails in all too many ways to support the
  relational model properly
• There is not product on the market today that
  supports the relational model in its entirely

3
Introduction

• SQL is the standard language for relational
  systems
• Developed by IBM in 1970s
• Early prototype called System R
• Current version SQL2003
• All major database vendors support SQL
• All support a superset of a subset of SQL
• SQL Persistent Stored Modules (in 1996)
• CALL, RETURN, SET, CASE, IF, LOOP, LEAVE, WHILE,
  and REPEAT, as well as the ability to declare
  variables and exception handlers
• SQL terms
• Table relation and relvar
• Row tuple
• Column attribute

4
Overview

• Supports data definition (DDL) and data
  manipulation (DML)

5
DDL

• DDL CREATE TABLE, CREATE TYPE, ALTER TABLE

• CREATE TABLE is for creating base tables
• not legal in the standard
• statement terminator (depends on the context)
• CHAR required an associated length
• CREATE TYPE may not exist in products

6
Relational operators in SQL

• Restrict is implemented by SELECT
• Subset of rows
• Uses WHERE clause to narrow result
• Uses SELECT to copy entire table
• Project is also implemented by SELECT
• Subset of columns
• Join is also implemented by SELECT
• Result is a single table matched by values in the
  join columns of each source table
• Uses WHERE clause to specify source tables
• Uses dot operator to qualify like column names
  (dot-qualified name)

7
The Suppliers-and-Parts Database (Sample Values)
8
Restrict, Project, and Join Example in SQL
SELECT S, P, QTY SELECT
9
Update operators in SQL - Insert

• Insert is implemented by INSERT INTO
• Example INSERT INTO TEMP (P, Weight) SELECT
  P, Weight FROM PWHERE COLOR COLOR (Red)
• Inserts part number and weight from P into part
  number and weight in TEMP for all red parts
• Table TEMP must be declared beforehand

10
Update operators in SQL - Delete

• Delete is implemented by DELETE
• Example DELETE FROM SP
• WHERE P P (P2)
• Deletes all shipments for part P2

11
Update operators in SQL - Update

• Update is implemented by UPDATE
• Example UPDATE S
• SET STATUS 2 STATUS, CITY
  RomeWHERE CITY Paris
• Doubles the status of the Parisian suppliers and
  moves them to Rome.

12
Note

• SQL does not include a direct analog of the
  relational assignment operation
• But we can simulate that operation by first
  deleting all rows for the target table and then
  performing an INSERTSELECT into that table
• INSERT, DELETE, and UPDATE are all set-level
  operations, in general

13
The Catalog in SQL

• Catalog and schema
• An SQL catalog consists of the descriptors for an
  individual DB
• An SQL schema consists of the descriptors for the
  portion of that DB that belongs to some
  individual user
• Every database has a catalog, and A catalog has
  many schemas, one per user
• Each catalog includes one Information Schema,
  which performs the normal catalog function
• The information consists of a set of SQL tables
  whose contents effectively echo all of the
  definitions from all of the other schemas in the
  catalog in question
• The Information Schema is a collection of all
  other schemas as views
• Represents a hypothetical Definition Schema for
  all users

14
The Information Schema - Examples

• Tables (includes views)
• Views (does not include tables)
• Table constraints
• Referential constraints
• Column constraints
• Privileges
• (Because these objects are themselves tables,
  they can be queried)

15
Views

• CREATE VIEW GOOD_SUPPLIERAS SELECT S, STATUS,
  CITYFROM SWHERE STATUS gt 15
• You can now operate on the view
• SELECT S, STATUSFROM GOOD_SUPPLIERWHERE CITY
  London
• Creating a view, and then selecting from the view
  is equivalent to a select sub-query
• SELECT GOOD_SUPPLIER.S, GOOD_SUPPLIER
  .STATUSFROM (SELECT S, STATUS, CITY FROM S
  WHERE STATUS gt 15 ) AS
  GOOD_SUPPLIER WHERE GOOD_SUPPLIER.CITY
  London

16
Views (Cont.)

• Simplify the previous example
• SELECT S, STATUSFROM SWHERE STATUS gt 15AND
  CITY London
• Another example
• DELETE FROM GOOD_SUPPLIERWHERE CITY London
• THE DELETE actually executed looks something like
  this
• DELETE FROM SWHERE STATUS gt 15 AND CITY
  London

17
Views - Example

• Creating a view, and then selecting from the view
  is equivalent to a select sub-query
• SELECT GOOD_SUPPLIER.S, GOOD_SUPPLIER
  .STATUSFROM (SELECT S, STATUS, CITY FROM S
  WHERE STATUS gt 15 ) AS GOOD_SUPPLIER
  WHERE GOOD_SUPPLIER.CITY London

18
Transactions

• SQL includes direct analogs of the BEGIN
  TRANSACTION, COMMIT, and ROLLBACK statement,
  called START TRANSACTION, COMMIT WORK, and
  ROLLBACK WORK (the keyword WORK is optional)

19
Embedded SQL
20
Preliminaries

• Most SQL products allow SQL statements to be
  executed both directly (i.e., interactively from
  an online terminal) and as part of an application
  program (embedded)
• SQL statements can be embedded in a host program
• C, COBOL, PL/1, Java, and many others
• Dual-mode principle any SQL statement that can
  be used at terminal (interactive), can also be
  used in an application program (programmable).
• The converse is not true

21
Fragment of a PL/I Program with Embedded SQL
22
Preliminaries (Cont.) pp. 9293

• Embedded SQL statements are prefix by EXEC SQL.
• Executable statements can appear wherever.
• non-executable statements e.g. DECLARE TABLE,
  DECLARE CURSOR
• SQL statements can reference host variable, by a
  prefix (colon)
• Using INTO in SELECT to specify the target host
  variables into which values are to be retrieved
• All host variables must be declared within an
  embedded SQL declare section
• Each program must include a host variable called
  SQLSTATE (or SQLCODE) and test it to know the
  execution status of each SQL statement
• Host variables must have compatible data type
  with SQL field.
• Host variables can have same name as database
  fields.

23
Operations Not Involving Cursors

• Singleton SELECT
• INSERT
• DELETE (except the CURRENT form)
• UPDATE (except the CURRENT form)

24
Singleton SELECT

• Singleton SELECT a SELECT expression that
  evaluates to a table containing at most one row
• EXEC SQL SELECT STATUS, CITY INTO RANK,
  TOWN FROM S WHERE
  S S(GIVENS)
• SQLSTATE
• 00000 if exact one row
• 02000 if no row
• Error code if more than one row

25
INSERT, DELETE, and UPDATE

• INSERT a new part (part number, name, and weight
  given by host variables color and city unknown
  (accept default values or null))
• EXEC SQL INSERT INTO P (P, PNAME, WEIGHT)
  VALUES (P, PNAME, PMT)
• DELETE all shipments for suppliers whose city is
  given by the host variable
• EXEC SQL DELETE FROM SP WHERE CITY (SELECT
  CITY FROM S WHERE S.S SP.S)
• If no supplier rows satisfy the condition,
  SQLSTATE will be set to 02000
• UPDATE increase the status of all London
  suppliers by the amount given by the host
  variable
• EXEC SQL UPDATE S SET STATUS STATUS
  RAISEWHERE CITY LONDON
• SQLSTATE will be set to 02000 if no SP rows
  satisfy the condition

26
Operations Involving Cursors
Cursor

• Multiple SELECT and Cursor
• SQL retrieval returns sets of rows
• Host languages handle one row at a time
• To allow this, SQL sets up a CURSOR to hold
  returned row sets, so they can be processed one
  row at a time
• Cursor is a kind of pointer that can be run
  through a set of records.
• A cursor is a buffer holding a row set
• The buffer is called the context area
• A cursor maintains a pointer to the currently
  active row, or record, and begins before the
  first row
• You can process each row, one at a time, and then
  advance the cursor

27
Multi-Row Retrieval Example
28
Cursor Explanation

• A cursor must be declared here is where you
  place your SQL statement that will return a set
• Next OPEN the cursor
• You may then FETCH the cursor into variables you
  have declared
• Each FETCH reads a row into your variables
• Often this is accomplished within a loop
• If there is no next row when FETCH is executed,
  SQLSTATE is set to 02000 and no data is retrieved
• Your first FETCH reads the first row
• CLOSE when finished, please

29
DELETE, UPDATE and CURSOR

• EXEC SQL UPDATE SSET STATUS STATUS
  RAISEWHERE CURRENT OF X
• If a cursor, X say, is currently positioned on a
  particular row, then it is possible to DELETE or
  UPDATE the current of X that is, the row on
  which X is positioned

30
Dynamic SQL and SQL/CLI
31
Overview

• The embedded SQL (up to now) assumed we could
  compile any given program in its entirety SQL
  statements and all ahead of time, as it were
  (prior to run time)
• Suppose an online application
• Accept a command from the terminal
• Analyze the command
• Execute appropriate SQL statements on the
  database
• Return a message and/or results to the terminal

What if a variety of commands?
We need a mechanism to construct the necessary
SQL statements dynamically,and then compile and
execute those constructed statements dynamically
Dynamic SQL and SQL/CLI
32
Dynamic SQL

• DCL SQLSOURCE CHAR VARYING (65000)SQLSOURCE
  DELETE FROM SP WHERE QTY lt QTY (300)EXEC SQL
  PREPARE SQLPREPPED FROM SQLSOURCEEXEC SQL
  EXECUTE SQLPREPPED
• Note
• PREPARE compiles the SQL statement indicated by
  SQLSOURCE (host variable) and stores the
  executable version to SQLPREPPED (SQL variable)
• EXECUTE executes the executable version and
  causes the actual DELETE to occur
• EXECUTE IMMEDIATE PREPARE EXECUTE
• Much more to dynamic SQL than PREPARE and EXECUTE

33
SQL Call-Level Interface (CLI)

• SQL/CLI is heavily based on MS ODBC (Open
  Database Connectivity)
• Both permit an application written in one of the
  usual host languages to issue DB requests, not
  via embedded SQL, but rather by invoking certain
  vendor-provided routines
• Those routines, which must have been linked to
  the application in question, then use dynamic SQL
  to perform the requested DB operations on the
  applications behalf
• Better than Dynamic SQL for two reasons
  pp.99100
• CLI Example pp. 100

char sqlsource65000strcpy(sqlsource,
DELETE)rc SQLExecDirect(hstmt, (SQLCHAR )
sqlsource, SQL_NTS)