![Page 1: Chapter 4 An Introduction to SQL. Copyright © 2004 Pearson Addison-Wesley. All rights reserved.4-2 Topics in this Chapter SQL: History and Overview The](https://reader035.vdocuments.us/reader035/viewer/2022062222/5697bfba1a28abf838ca0408/html5/thumbnails/1.jpg)
Chapter 4
An Introduction to SQL
![Page 2: Chapter 4 An Introduction to SQL. Copyright © 2004 Pearson Addison-Wesley. All rights reserved.4-2 Topics in this Chapter SQL: History and Overview The](https://reader035.vdocuments.us/reader035/viewer/2022062222/5697bfba1a28abf838ca0408/html5/thumbnails/2.jpg)
Copyright © 2004 Pearson Addison-Wesley. All rights reserved. 4-2
Topics in this Chapter
• SQL: History and Overview • The Catalog • Views• Embedded SQL• Cursors• Dynamic SQL• Call-Level Interface
![Page 3: Chapter 4 An Introduction to SQL. Copyright © 2004 Pearson Addison-Wesley. All rights reserved.4-2 Topics in this Chapter SQL: History and Overview The](https://reader035.vdocuments.us/reader035/viewer/2022062222/5697bfba1a28abf838ca0408/html5/thumbnails/3.jpg)
Copyright © 2004 Pearson Addison-Wesley. All rights reserved. 4-3
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
![Page 4: Chapter 4 An Introduction to SQL. Copyright © 2004 Pearson Addison-Wesley. All rights reserved.4-2 Topics in this Chapter SQL: History and Overview The](https://reader035.vdocuments.us/reader035/viewer/2022062222/5697bfba1a28abf838ca0408/html5/thumbnails/4.jpg)
Copyright © 2004 Pearson Addison-Wesley. All rights reserved. 4-4
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
![Page 5: Chapter 4 An Introduction to SQL. Copyright © 2004 Pearson Addison-Wesley. All rights reserved.4-2 Topics in this Chapter SQL: History and Overview The](https://reader035.vdocuments.us/reader035/viewer/2022062222/5697bfba1a28abf838ca0408/html5/thumbnails/5.jpg)
Copyright © 2004 Pearson Addison-Wesley. All rights reserved. 4-5
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
![Page 6: Chapter 4 An Introduction to SQL. Copyright © 2004 Pearson Addison-Wesley. All rights reserved.4-2 Topics in this Chapter SQL: History and Overview The](https://reader035.vdocuments.us/reader035/viewer/2022062222/5697bfba1a28abf838ca0408/html5/thumbnails/6.jpg)
Copyright © 2004 Pearson Addison-Wesley. All rights reserved. 4-6
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
![Page 7: Chapter 4 An Introduction to SQL. Copyright © 2004 Pearson Addison-Wesley. All rights reserved.4-2 Topics in this Chapter SQL: History and Overview The](https://reader035.vdocuments.us/reader035/viewer/2022062222/5697bfba1a28abf838ca0408/html5/thumbnails/7.jpg)
Copyright © 2004 Pearson Addison-Wesley. All rights reserved. 4-7
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
![Page 8: Chapter 4 An Introduction to SQL. Copyright © 2004 Pearson Addison-Wesley. All rights reserved.4-2 Topics in this Chapter SQL: History and Overview The](https://reader035.vdocuments.us/reader035/viewer/2022062222/5697bfba1a28abf838ca0408/html5/thumbnails/8.jpg)
Copyright © 2004 Pearson Addison-Wesley. All rights reserved. 4-8
Update operators in SQL - Delete
• Delete is implemented by DELETE– Ex,: DELETE FROM SP
WHERE P# = P# (‘P2’);
– Deletes all shipments for part P2
![Page 9: Chapter 4 An Introduction to SQL. Copyright © 2004 Pearson Addison-Wesley. All rights reserved.4-2 Topics in this Chapter SQL: History and Overview The](https://reader035.vdocuments.us/reader035/viewer/2022062222/5697bfba1a28abf838ca0408/html5/thumbnails/9.jpg)
Copyright © 2004 Pearson Addison-Wesley. All rights reserved. 4-9
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.
![Page 10: Chapter 4 An Introduction to SQL. Copyright © 2004 Pearson Addison-Wesley. All rights reserved.4-2 Topics in this Chapter SQL: History and Overview The](https://reader035.vdocuments.us/reader035/viewer/2022062222/5697bfba1a28abf838ca0408/html5/thumbnails/10.jpg)
Copyright © 2004 Pearson Addison-Wesley. All rights reserved. 4-10
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
![Page 11: Chapter 4 An Introduction to SQL. Copyright © 2004 Pearson Addison-Wesley. All rights reserved.4-2 Topics in this Chapter SQL: History and Overview The](https://reader035.vdocuments.us/reader035/viewer/2022062222/5697bfba1a28abf838ca0408/html5/thumbnails/11.jpg)
Copyright © 2004 Pearson Addison-Wesley. All rights reserved. 4-11
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)
![Page 12: Chapter 4 An Introduction to SQL. Copyright © 2004 Pearson Addison-Wesley. All rights reserved.4-2 Topics in this Chapter SQL: History and Overview The](https://reader035.vdocuments.us/reader035/viewer/2022062222/5697bfba1a28abf838ca0408/html5/thumbnails/12.jpg)
Copyright © 2004 Pearson Addison-Wesley. All rights reserved. 4-12
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’;
![Page 13: Chapter 4 An Introduction to SQL. Copyright © 2004 Pearson Addison-Wesley. All rights reserved.4-2 Topics in this Chapter SQL: History and Overview The](https://reader035.vdocuments.us/reader035/viewer/2022062222/5697bfba1a28abf838ca0408/html5/thumbnails/13.jpg)
Copyright © 2004 Pearson Addison-Wesley. All rights reserved. 4-13
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, CITYFROM S WHERE STATUS > 15 ) AS GOOD_SUPPLIER;
WHERE GOOD_SUPPLIER.CITY = ‘London’;
![Page 14: Chapter 4 An Introduction to SQL. Copyright © 2004 Pearson Addison-Wesley. All rights reserved.4-2 Topics in this Chapter SQL: History and Overview The](https://reader035.vdocuments.us/reader035/viewer/2022062222/5697bfba1a28abf838ca0408/html5/thumbnails/14.jpg)
Copyright © 2004 Pearson Addison-Wesley. All rights reserved. 4-14
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
![Page 15: Chapter 4 An Introduction to SQL. Copyright © 2004 Pearson Addison-Wesley. All rights reserved.4-2 Topics in this Chapter SQL: History and Overview The](https://reader035.vdocuments.us/reader035/viewer/2022062222/5697bfba1a28abf838ca0408/html5/thumbnails/15.jpg)
Copyright © 2004 Pearson Addison-Wesley. All rights reserved. 4-15
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
![Page 16: Chapter 4 An Introduction to SQL. Copyright © 2004 Pearson Addison-Wesley. All rights reserved.4-2 Topics in this Chapter SQL: History and Overview The](https://reader035.vdocuments.us/reader035/viewer/2022062222/5697bfba1a28abf838ca0408/html5/thumbnails/16.jpg)
Copyright © 2004 Pearson Addison-Wesley. All rights reserved. 4-16
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#);
![Page 17: Chapter 4 An Introduction to SQL. Copyright © 2004 Pearson Addison-Wesley. All rights reserved.4-2 Topics in this Chapter SQL: History and Overview The](https://reader035.vdocuments.us/reader035/viewer/2022062222/5697bfba1a28abf838ca0408/html5/thumbnails/17.jpg)
Copyright © 2004 Pearson Addison-Wesley. All rights reserved. 4-17
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
![Page 18: Chapter 4 An Introduction to SQL. Copyright © 2004 Pearson Addison-Wesley. All rights reserved.4-2 Topics in this Chapter SQL: History and Overview The](https://reader035.vdocuments.us/reader035/viewer/2022062222/5697bfba1a28abf838ca0408/html5/thumbnails/18.jpg)
Copyright © 2004 Pearson Addison-Wesley. All rights reserved. 4-18
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
![Page 19: Chapter 4 An Introduction to SQL. Copyright © 2004 Pearson Addison-Wesley. All rights reserved.4-2 Topics in this Chapter SQL: History and Overview The](https://reader035.vdocuments.us/reader035/viewer/2022062222/5697bfba1a28abf838ca0408/html5/thumbnails/19.jpg)
Copyright © 2004 Pearson Addison-Wesley. All rights reserved. 4-19
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’;
![Page 20: Chapter 4 An Introduction to SQL. Copyright © 2004 Pearson Addison-Wesley. All rights reserved.4-2 Topics in this Chapter SQL: History and Overview The](https://reader035.vdocuments.us/reader035/viewer/2022062222/5697bfba1a28abf838ca0408/html5/thumbnails/20.jpg)
Copyright © 2004 Pearson Addison-Wesley. All rights reserved. 4-20
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
![Page 21: Chapter 4 An Introduction to SQL. Copyright © 2004 Pearson Addison-Wesley. All rights reserved.4-2 Topics in this Chapter SQL: History and Overview The](https://reader035.vdocuments.us/reader035/viewer/2022062222/5697bfba1a28abf838ca0408/html5/thumbnails/21.jpg)
Copyright © 2004 Pearson Addison-Wesley. All rights reserved. 4-21
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
![Page 22: Chapter 4 An Introduction to SQL. Copyright © 2004 Pearson Addison-Wesley. All rights reserved.4-2 Topics in this Chapter SQL: History and Overview The](https://reader035.vdocuments.us/reader035/viewer/2022062222/5697bfba1a28abf838ca0408/html5/thumbnails/22.jpg)
Copyright © 2004 Pearson Addison-Wesley. All rights reserved. 4-22
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;
![Page 23: Chapter 4 An Introduction to SQL. Copyright © 2004 Pearson Addison-Wesley. All rights reserved.4-2 Topics in this Chapter SQL: History and Overview The](https://reader035.vdocuments.us/reader035/viewer/2022062222/5697bfba1a28abf838ca0408/html5/thumbnails/23.jpg)
Copyright © 2004 Pearson Addison-Wesley. All rights reserved. 4-23
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