Chapter 4
An Introduction to SQL
Topics in this Chapter
•
•
•
•
•
•
•
SQL: History and Overview
The Catalog
Views
Embedded SQL
Cursors
Dynamic SQL
Call-Level Interface
Copyright © 2004 Pearson Addison-Wesley. All rights reserved.
4-2
History of SQL
•
•
•
•
•
•
•
•
Developed by IBM in 1970’s
Early prototype called System R
Current version is SQL:1999
All major database vendors support SQL
All support a superset of a subset of SQL
Both relvar and relation = table in SQL
Tuple = row in SQL
Attribute = column in SQL
Copyright © 2004 Pearson Addison-Wesley. All rights reserved.
4-3
Overview of SQL
• It is the standard language for relational
systems, although imperfect
• Supports data definition (DDL) and data
manipulation (DML)
• DDL: CREATE TABLE, CREATE TYPE,
ALTER TABLE
• DML: SELECT, INSERT, DELETE,
UPDATE
Copyright © 2004 Pearson Addison-Wesley. All rights reserved.
4-4
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
Copyright © 2004 Pearson Addison-Wesley. All rights reserved.
4-5
Relational operators in SQL
• 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
Copyright © 2004 Pearson Addison-Wesley. All rights reserved.
4-6
Update operators in SQL - Insert
• Insert is implemented by INSERT INTO
– Ex,: INSERT INTO TEMP (P#, Weight)
SELECT P#, Weight FROM P
WHERE COLOR = COLOR (‘Red’);
– Inserts part number and weight from P into part
number and weight in TEMP for all red parts
Copyright © 2004 Pearson Addison-Wesley. All rights reserved.
4-7
Update operators in SQL - Delete
• Delete is implemented by DELETE
– Ex,: DELETE FROM SP
WHERE P# = P# (‘P2’);
– Deletes all shipments for part P2
Copyright © 2004 Pearson Addison-Wesley. All rights reserved.
4-8
Update operators in SQL - Update
• Update is implemented by UPDATE
– Ex,: UPDATE S
SET STATUS = 2 * STATUS,
CITY = ‘Rome’
WHERE CITY = ‘Paris’;
– Doubles the status of the Parisian suppliers and
moves them to Rome.
Copyright © 2004 Pearson Addison-Wesley. All rights reserved.
4-9
The Catalog in SQL
• Every database has a catalog
• A catalog has many schemas, one per user
• Each catalog includes one Information
Schema
• The Information Schema is a collection of all
other schemas as views
• Represents a hypothetical Definition Schema
for all users
Copyright © 2004 Pearson Addison-Wesley. All rights reserved.
4-10
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)
Copyright © 2004 Pearson Addison-Wesley. All rights reserved.
4-11
Views
• CREATE VIEW GOOD_SUPPLIER
AS SELECT S#, STATUS, CITY
FROM S
WHERE STATUS > 15;
• You can now operate on the view
• SELECT S#, STATUS
FROM GOOD_SUPPLIER
WHERE CITY = ‘London’;
Copyright © 2004 Pearson Addison-Wesley. All rights reserved.
4-12
Views - Example
• Creating a view, and then selecting from the
view is equivalent to a select sub-query
• SELECT GOOD_SUPPLIER .S#,
GOOD_SUPPLIER .STATUS
FROM (SELECT S#, STATUS, CITY
FROM S
WHERE STATUS > 15 )
AS GOOD_SUPPLIER;
WHERE GOOD_SUPPLIER.CITY =
‘London’;
Copyright © 2004 Pearson Addison-Wesley. All rights reserved.
4-13
Embedded SQL
• SQL statements can be embedded in a host
program
• C, COBOL, PL/1, Java, and many others
• Initiated in host via EXEC SQL statement
• Any SQL statement is valid after EXEC SQL
Copyright © 2004 Pearson Addison-Wesley. All rights reserved.
4-14
Embedded SQL
• SQL statements after EXEC SQL can
reference host variables
• Declare any such variables in a SQL
DECLARE section
• Then, prefix : (colon) to the variable name
when you reference it
Copyright © 2004 Pearson Addison-Wesley. All rights reserved.
4-15
Embedded SQL - Example
EXEC SQL BEGIN DECLARE SECTION;
DCL SQLSTATE CHAR(5);
DCL P#
CHAR(6);
DCL WEIGHT DECIMAL(5,1);
EXEC SQL END DECLARE SECTION;
P# = ‘P2’;
EXEC SQL SELECT P.WEIGHT
INTO :WEIGHT
FROM P
WHERE P.P# = (:P#);
Copyright © 2004 Pearson Addison-Wesley. All rights reserved.
4-16
Embedded SQL - SQLSTATE
IF SQLSTATE = ‘00000’
THEN …
ELSE …
• Every host language must include a host
variable called SQLSTATE that returns 00000
is execution is ok
• If no data found then it returns 02000
Copyright © 2004 Pearson Addison-Wesley. All rights reserved.
4-17
Embedded SQL – Loosely coupled with the
host
• Data types between host and tables must be
compatible
• Host variables can have the same name as
table columns, but need not
• 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
Copyright © 2004 Pearson Addison-Wesley. All rights reserved.
4-18
Embedded SQL – Sometimes Cursors are
not Needed
•
•
•
•
•
SELECT, when it returns at most one row
INSERT, however many rows
DELETE, likewise
UPDATE, ditto
Example:
EXEC SQL UPDATE S
SET STATUS = STATUS + :RAISE
WHERE CITY = ‘London’;
Copyright © 2004 Pearson Addison-Wesley. All rights reserved.
4-19
Embedded SQL – Cursors
• 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, yes,
before the first row
• You can process each row, one at a time, and
then advance the cursor
• UPDATE and DELETE are supported when
you reference the “CURRENT” row
Copyright © 2004 Pearson Addison-Wesley. All rights reserved.
4-20
Embedded SQL –Cursors
• 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
• Your first FETCH reads the first row
• CLOSE when finished, please
Copyright © 2004 Pearson Addison-Wesley. All rights reserved.
4-21
Dynamic SQL
• Embedded SQL can be constructed and
invoked dynamically
DCL SQLSOURCE CHAR VARYING (65000);
SQLSOURCE = ‘DELETE FROM SP WHERE
QTY < QTY (300)’;
EXEC SQL PREPARE SQLPREPPED FROM
:SQLSOURCE;
EXEC SQL EXECUTE SQLPREPPED;
Copyright © 2004 Pearson Addison-Wesley. All rights reserved.
4-22
SQL Call-Level Interface (CLI)
• Alternative to Embedded SQL
• Similar functionality, based in MS ODBC
• Better than Embedded SQL for two reasons
– Can be compiled by the host compiler and
distributed shrink wrapped to customers
– Greater DBMS independence because they can
interface with anyone’s SQL engine
Copyright © 2004 Pearson Addison-Wesley. All rights reserved.
4-23
Descargar

Date's An Introduction to Database Systems, 8th ed