Transcript Database Systems: Design, Implementation, and Management

5
Chapter 5
Structured Query Language
(SQL)
Database Systems: Design, Implementation, and
Management, Fifth Edition, Rob and Coronel
5
In this chapter, you will learn:
• The basic commands and functions of SQL
• How SQL is used for data manipulation (to add,
modify, delete, and retrieve data)
• How to use SQL to query a database to extract
useful information
• How SQL is used for data administration (to
create tables, indexes, and views)
• About more advanced SQL features such as
updatable views, stored procedures, and triggers
Database Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel
2
5
Introduction to SQL
• Ideal database language
– Create database and table structures
– Perform basic data management chores (add,
delete, and modify)
– Perform complex queries to transform data into
useful information
• SQL is the ideal DB language
– Data definition language
– Data manipulation language
Database Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel
3
5
Good Reasons to Study SQL
• ANSI standardization effort led to de facto query
standard for relational database
• Forms basis for present and future DBMS
integration efforts
• Becomes catalyst in development of distributed
databases and database client/server architecture
Database Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel
4
5
Data Definition Commands
• Create database structure
– Holds all tables and is a collection of physical files
stored on disk
– DBMS automatically creates tables to store metadata
– Database administrator creates structure or schema
• Logical group of tables or logical database
• Groups tables by owner
• Enforces security
CREATE SCHEMA AUTHORIZATION <creator>
Example:
CREATE SCHEMA AUTHORIZATION JONES
Database Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel
5
5
Creating Table Structure
• Tables store end-user data
• May be based on data dictionary entries
CREATE TABLE <table name>
(<attribute1 name and attribute1 characteristics,
attribute2 name and attribute2 characteristics,
attribute3 name and attribute3 characteristics,
primary key designation,
foreign key designation and foreign key requirement>);
Database Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel
6
5
Using Domains
• Domain is set of permissible values for a column
• Definition requires:
–
–
–
–
Name
Data type
Default value
Domain constraint or condition
CREATE DOMAIN <domain_name> AS DATA_TYPE
[DEFAULT <default_value>]
[CHECK (<condition>)]
Database Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel
7
5
SQL Integrity Constraints
• Adherence to entity integrity and referential
integrity rules is crucial
– Entity integrity enforced automatically if primary
key specified in CREATE TABLE command
sequence
– Referential integrity can be enforced in
specification of FOREIGN KEY
– Other specifications to ensure conditions met:
• ON DELETE RESTRICT
• ON UPDATE CASCADE
Database Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel
8
5
Data Manipulation Commands
Common SQL Commands
Table 5.3
Database Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel
9
5
Data Entry and Saving
• Enters data into a table
INSERT INTO <table name>
VALUES (attribute 1 value, attribute 2 value, … etc.);
• Saves changes to disk
COMMIT <table names> ;
Database Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel
10
5
Listing Table Contents and Other
Commands
• Allows table contents to be listed
SELECT <attribute names> FROM <table names>;
• UPDATE command makes data entry corrections
• ROLLBACK command restores database back to
previous condition if COMMIT hasn’t been used
• DELETE command removes table row
Database Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel
11
5
Queries
• Creating partial listings of table contents
SELECT <column(s)>
FROM <table name>
WHERE <conditions>;
Table 5.4
Mathematical Operators
Database Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel
12
5
Examples
• Mathematical operators
SELECT P_DESCRIPT, P_INDATE, P_PRICE, V_CODE
FROM PRODUCT
WHERE V_CODE <> 21344;
• Mathematical operators on character attributes
SELECT P_CODE,P_DESCRIPT,P_ONHAND,P_MIN,P_PRICE
FROM PRODUCT
WHERE P_CODE < ‘1558-QWI’;
• Mathematical operators on dates
SELECT P_DESCRIPT,P_ONHAND,P_MIN,P_PRICE,PINDATE
FROM PRODUCT
WHERE P_INDATE >= ‘01/20/2002’;
Database Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel
13
5
Computed Columns
• New columns can be created through valid
computations or formulas
– Formulas may contain mathematical operators
– May contain attributes of any tables specified in
FROM clause
• Alias is alternate name given to table or column
in SQL statement
SELECT P_DESCRIPT,P_ONHAND,P_PRICE,P_ONHAND*P_PRICE
AS TOTVALUE
FROM PRODUCT;
Database Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel
14
5
Operators
• Logical: AND, OR, NOT
SELECT *
FROM PRODUCT
WHERE V_CODE = 21344 OR V_CODE = 24288;
• Rules of precedence
– Conditions within parenthesis executed first
– Boolean algebra
• Special
–
–
–
–
–
BETWEEN - defines limits
IS NULL - checks for nulls
LIKE - checks for similar string
IN - checks for value in a set
EXISTS - opposite of IS NULL
Database Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel
15
5
Advanced Data Management
Commands
• ALTER - changes table structure
• ADD - adds column
• MODIFY - changes column characteristics
ALTER TABLE <table name>
ADD (<column name> <new column characteristics>);
ALTER TABLE <table name>
MODIFY (<column name> <new column characteristics>);
• Entering data into new column
UPDATE PRODUCT
SET P_SALECODE = ‘2’
WHERE P_CODE = ‘1546-QQ2’;
Database Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel
16
5
Advanced Data Management
Commands (con’t.)
• Dropping a column
ALTER TABLE VENDOR
DROP COLUMN V_ORDER;
• Arithmetic operators and rules of precedence
Table 5.5
Database Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel
17
5
Advanced Data Management
Commands (con’t.)
• Copying parts of tables
INSERT INTO <receiving table> <receiving table’s column names>
SELECT <column names of the columns to be copied>
FROM <contributing table name>;
• Deleting a table from database
DROP TABLE PART;
• Primary and foreign key designation
ALTER TABLE LINE
ADD PRIMARY KEY (INV_NUMBER, LINE_NUMBER)
ADD FOREIGN KEY (INV_NUMBER) REFERENCES INVOICE
ADD FOREIGN KEY (PROD_CODE) REFERENCES PRODUCT;
Database Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel
18
• COUNT
Example Aggregate
Function Operations
5
SELECT COUNT(DISTINCT V_CODE)
FROM PRODUCT;
SELECT COUNT(DISTINCT V_CODE)
FROM PRODUCT
WHERE P_PRICE <= 10.00;
• MAX and MIN
SELECT MIN(P_PRICE)
FROM PRODUCT;
SELECT P_CODE, P_DESCRIPT, P_PRICE
FROM PRODUCT
WHERE P_PRICE = MAX(P_PRICE);
Database Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel
19
5
Example Aggregate
Function Operations (con’t.)
• SUM
SELECT SUM(P_ONHAND * P_PRICE)
FROM PRODUCT;
• AVG
SELECT P_DESCRIPT, P_ONHAND, P_PRICE, V_CODE
FROM PRODUCT
WHERE P_PRICE >
(SELECT AVG(P_PRICE) FROM PRODUCT)
ORDER BY P_PRICE DESC;
Database Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel
20
5
More Complex Queries and SQL
Functions
• Ordering a listing
ORDER BY <attributes>
• Results ascending by default
– Descending order uses DESC
ORDER BY <attributes> DESC
• Cascading order sequence
ORDER BY <attribute 1, attribute 2, ...>
Database Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel
21
5
More Complex Queries and SQL
Functions (con’t.)
• Listing unique values
– DISTINCT clause produces list of different values
SELECT DISTINCT V_CODE
FROM PRODUCT;
• Aggregate functions
– Mathematical summaries
Table 5.6
Database Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel
22
5
More Complex Queries and
SQL Functions (con’t.)
• Grouping data
– Creates frequency distributions
– Only valid when used with SQL arithmetic functions
SELECT P_SALECODE, MIN(P_PRICE)
FROM PRODUCT_2
GROUP BY P_SALECODE;
– HAVING clause operates like WHERE for grouping
output
SELECT V_CODE,COUNT(DISTINCT(P_CODE)),AVG(P_PRICE)
FROM PRODUCT_2
GROUP BY V_CODE
HAVING AVG(P_PRICE) < 10;
Database Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel
23
5
More Complex Queries and
SQL Functions (con’t.)
• Virtual tables: creating a view
– CREATE VIEW command
– Creates logical table existing only in virtual memory
CREATE VIEW PRODUCT_3 AS
SELECT P_DESCRIPT, P_ONHAND, P_PRICE
FROM PRODUCT
WHERE P_PRICE > 50.00;
– SQL indexes
CREATE INDEX P_CODEX
ON PRODUCT(P_CODE);
Database Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel
24
5
More Complex Queries and
SQL Functions (con’t.)
• Joining database tables
– Data are retrieved from more than one table
SELECT PRODUCT.P_DESCRIPT, PRODUCT.P_PRICE,
VENDOR.V_NAME, VENDOR.V_CONTACT,
VENDOR.V_AREACODE, VENDOR.V_PHONE
FROM PRODUCT, VENDOR
WHERE PRODUCT.V_CODE = VENDOR.V_CODE;
– Recursive queries joins a table to itself
SELECT A.EMP_NUM,A.EMP_LNAME,A.EMP_MGR,
B.EMP_LNAME FROM EMP A, EMP B
WHERE A.EMP_MGR=B.EMP_NUM
ORDER BY A.EMP_MGR
– Outer joins can be used when ‘null’ values need to be
included in query result
Database Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel
25
5
Updatable Views
• Common operation in production environments is
use of batch routines to update master table
attributes using transaction data
• Overnight batch jobs
• Not all views are updatable
• Restrictions
– GROUP BY expressions cannot be used
– Cannot use set operators---UNION,
INTERSECTION, etc.
– Most restrictions based on use of JOINS or group
operators in views
Database Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel
26
Procedural SQL
5
• SQL shortcomings
– Doesn’t support execution of stored procedures based
on logical condition
– Fails to support looping operations
• Solutions
– Embedded SQL can be called from within procedural
programming languages
– Shared Code is isolated and used by all application
programs.
– Procedural SQL (PL/SQL) stored within the database,
executed by DBMS, and invoked by the end user
• Triggers
• Stored procedures
• PL/SQL functions
Database Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel
27
5
Procedural SQL (con’t.)
• 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
Database Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel
28
5
Triggers
• Procedural SQL code invoked before or after
data row is selected, inserted, or updated
• Associated with a database table
• Table may have multiple triggers
• Executed as part of transaction
• Can enforce particular constraints
• Automate critical actions and provide warnings
for remedial action
• Can update values, insert records, and call
procedures
• Add processing power
Database Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel
29
5
Triggers (con’t.)
• Oracle example
CREATE OR REPLACE TRIGGER
<trigger_name>
[BEFORE/AFTER]
[DELETE/INSERT/UPDATE OF <column_name]
ON <table_name>
[FOR EACH ROW]
BEGIN
PL/SQL instructions;
……………
END;
Database Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel
30
5
Stored Procedures
• Named collection of procedural and SQL
statements stored in database
• Invoked by name
• Executed as unit
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 statements;
END;
• Invoked with EXEC
EXEC store_procedure_name
(parameter, parameter, …)
Database Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel
31
5
PL/SQL Stored Functions
• Named group of procedural and SQL statements
that returns a value
CREATE FUNCTION function_name (argument IN
data-type, etc)
RETURN data-type
AS BEGIN
PL/SQL statements;
RETURN (value); ……
END;
• Invoked from within stored procedures or
triggers
• Cannot be invoked from within SQL statements
Database Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel
32
5
Artist Database ERD and Tables
Figure 5.55
Database Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel
33
5
General Rules Governing
Relationships Among Tables
Figure 5.56: M:N, Both Sides Mandatory
Figure 5.57: M:N, Both Sides Optional
Database Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel
34
5
General Rules Governing
Relationships Among Tables (Con’t.)
Figure 5.58: M:N, One Side Optional
Figure 5.59: 1:M, Both Sides Mandatory
Database Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel
35
5
General Rules Governing
Relationships Among Tables (Con’t.)
Figure 5.60: 1:M, Both Sides Optional
Figure 5.61: 1:M, Many Side Optional, One Side Mandatory
Database Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel
36
5
General Rules Governing
Relationships Among Tables (Con’t.)
Figure 5.62: 1:M, One Side Optional, One Side Mandatory
Figure 5.63: 1:1, Both Sides Mandatory
Database Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel
37
5
General Rules Governing
Relationships Among Tables (Con’t.)
Figure 5.64: 1:1, Both Sides Optional
Figure 5.65: 1:1, One Side Optional, One Side Mandatory
Database Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel
38
5
General Rules Governing
Relationships Among Tables (Con’t.)
Figure 5.66: Weak Entity, Foreign Key Located in Weak Entity
Figure 5.67: Multivalued Attributes (New Table in 1:M
Relationship, Foreign Key CAR_VIN in the New Table
Database Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel
39