Transcript Chapter 7 - Introduction to SQL

Database Systems:
Design, Implementation, and
Management
Tenth Edition
Chapter 7
Introduction to Structured Query
Language (SQL)
Introduction to SQL
• SQL functions fit into two broad categories:
– Data definition language
– Data manipulation language
• Basic command set has vocabulary of fewer
than 100 words
• American National Standards Institute (ANSI)
prescribes a standard SQL
• Several SQL dialects exist
Database Systems, 10th Edition
2
Database Systems, 10th Edition
3
Database Systems, 10th Edition
4
Data Definition Commands
• The database model
– In this chapter, a simple database with these
tables is used to illustrate commands:
•
•
•
•
•
CUSTOMER
INVOICE
LINE
PRODUCT
VENDOR
– Focus on PRODUCT and VENDOR tables
Database Systems, 10th Edition
5
Database Systems, 10th Edition
6
Creating the Database
• Two tasks must be completed:
– Create database structure
– Create tables that will hold end-user data
• First task:
– RDBMS creates physical files that will hold
database
– Differs substantially from one RDBMS to another
Database Systems, 10th Edition
7
Creating the Database (cont’d.)
• Authentication
– DBMS verifies that only registered users are
able to access database
– Log on to RDBMS using user ID and password
created by database administrator
Database Systems, 10th Edition
8
The Database Schema
• Schema
– Group of database objects that are related to
each other
• CREATE SCHEMA AUTHORIZATION
{creator};
– Command is seldom used directly
Database Systems, 10th Edition
9
Data Types
• Data type selection is usually dictated by nature
of data and by intended use
• Supported data types:
–
–
–
–
–
–
Number(L,D), Integer, Smallint, Decimal(L,D)
Char(L), Varchar(L), Varchar2(L)
Date, Time, Timestamp
Real, Double, Float
Interval day to hour
Many other types
Database Systems, 10th Edition
10
Database Systems, 10th Edition
11
Database Systems, 10th Edition
12
Creating Table Structures
• Use one line per column (attribute) definition
• Use spaces to line up attribute characteristics
and constraints
• Table and attribute names are capitalized
• NOT NULL specification
• UNIQUE specification
Database Systems, 10th Edition
13
Creating Table Structures (cont’d.)
• Primary key attributes contain both a NOT
NULL and a UNIQUE specification
• RDBMS will automatically enforce referential
integrity for foreign keys
• Command sequence ends with semicolon
Database Systems, 10th Edition
14
CREATE TABLE VENDOR (
V_CODE
INTEGER
V_NAME
VARCHAR(35)
V_CONTACT VARCHAR(15)
V_AREACODE CHAR(3)
V_PHONE
CHAR(8)
V_STATE
CHAR(2)
V_ORDER
CHAR(1)
PRIMARY KEY (V_CODE));
NOT
NOT
NOT
NOT
NOT
NOT
NOT
NULL UNIQUE,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
CREATE TABLE PRODUCT (
P_CODE
VARCHAR(10) NOT NULL UNIQUE,
P_DESCRIPT VARCHAR(35) NOT NULL,
P_INDATE
DATE
NOT NULL,
P_QOH
SMALLINT
NOT NULL,
P_MIN
SMALLINT
NOT NULL,
P_PRICE
NUMBER(8,2) NOT NULL,
P_DISCOUNT NUMBER(5,2) NOT NULL,
V_CODE
INTEGER,
For mySQL, use
PRIMARY KEY (P_CODE),
FOREIGN KEY (V_CODE)
FOREIGN KEY (V_CODE) REFERENCES VENDOR); REFERENCES VENDOR(V_CODE));
Database Systems, 10th Edition
15
CREATE TABLE CUSTOMER (
CUS_CODE
NUMBER PRIMARY KEY,
CUS_LNAME
VARCHAR(15) NOT NULL,
CUS_FNAME
VARCHAR(15) NOT NULL,
CUS_INITIAL
CHAR(1),
CUS_AREACODE
CHAR(3) DEFAULT '615' NOT NULL
CHECK(CUS_AREACODE IN ('615','713','931')),
CUS_PHONE
CHAR(8) NOT NULL,
CUS_BALANCE
NUMBER(9,2) DEFAULT 0.00,
CONSTRAINT CUS_UI1 UNIQUE(CUS_LNAME,CUS_FNAME));
Database Systems, 10th Edition
16
CREATE TABLE INVOICE (
INV_NUMBER
NUMBER PRIMARY KEY,
CUS_CODE
NUMBER NOT NULL REFERENCES CUSTOMER(CUS_CODE),
INV_DATE
DATE NOT NULL;
/* DOES NUT RUN IN mySQL */
INV_DATE
DATE DEFAULT SYSDATE NOT NULL,
CONSTRAINT INV_CK1 CHECK (INV_DATE > TO_DATE('01-JAN-2012','DD-MONYYYY')));
CREATE TABLE LINE (
INV_NUMBER
NUMBER NOT NULL,
LINE_NUMBER
NUMBER(2,0) NOT NULL,
P_CODE
VARCHAR(10) NOT NULL,
LINE_UNITS
NUMBER(9,2) DEFAULT 0.00 NOT NULL,
LINE_PRICE
NUMBER(9,2) DEFAULT 0.00 NOT NULL,
PRIMARY KEY (INV_NUMBER,LINE_NUMBER),
FOREIGN KEY (INV_NUMBER) REFERENCES INVOICE(INV_NUMBER)
ON DELETE CASCADE,
FOREIGN KEY (P_CODE) REFERENCES PRODUCT(P_CODE),
CONSTRAINT LINE_UI1 UNIQUE(INV_NUMBER, P_CODE));
Database Systems, 10th Edition
17
SQL Constraints
• FOREIGN KEY constraint (in PRODUCT table)
FOREIGN KEY (V_CODE) REFERENCES VENDOR
ON UPDATE CASCADE;
– You can not delete a vendor from the VENDOR
table if at least one product row references that
vendor
– If a change is made in an existing VENDOR
table’s V_CODE, that change must be reflected
automatically in any PRODUCT table V_CODE
reference
• Makes it impossible for a V_CODE value to exists in
the PRODUCT table if there is not parallel entry in
the VENDOR table
18
SQL Constraints
• NOT NULL constraint
– Ensures that column does not accept nulls
• UNIQUE constraint
– Ensures that all values in column are unique
• DEFAULT constraint (NOT IN ACCESS)
– Assigns value to attribute when a new row is added to table
• CUS_AREACODE CHAR(3) DEFAULT ‘615’ NOT NULL
CHECK (CUS_AREACODE IN (‘615’, ‘713’, 931’))
• CHECK constraint (NOT IN ACCESS)
– Validates data when attribute value is entered
• Minimum order amount must be at least 10
• Date must be after Jan 1, 2013
• CONSTRAINT INV_CHK1 CHECK (INV_DATE>TO_DATE(‘01-JAN2012’,’DD-MON-YYYY’))
19
SQL Constraints
• MS Access and SQL Server support ON
DELETE CASCADE and ON UPDATE
CASCADE
– ON DELETE CASCADE recommended for weak
entities to ensure that the deletion of a row in the
strong entity automatically triggers the deletion of
the corresponding rows in the dependent weak
entity
• Oracle supports ON DELETE CASCADE
• Access and SQL Server do not support SET
NULL, Oracle does
20
SQL Indexes
• When primary key is declared, DBMS automatically creates
unique index
– Often need additional indexes
– Using CREATE INDEX command, SQL indexes can be
created on basis of any selected attribute
– Can be in ascending or descending order
… ON PRODUCT (P_PRICE DESC)
• Composite index
– Index based on two or more attributes, Often used to prevent
data duplication
CREATE INDEX P_INDATEX ON PRODUCT(P_INDATE);
CREATE UNIQUE … ensures that no duplicates are allowed in the
index (e.g., a candidate key)
– To delete an index use DROP INDEX P_INDATEX
21
Data Manipulation Commands
•
•
•
•
•
•
INSERT
SELECT
COMMIT
UPDATE
ROLLBACK
DELETE
Database Systems, 10th Edition
22
Adding Table Rows
• INSERT
– Used to enter data into table
– Syntax:
INSERT INTO tablename
VALUES (value1, value2, … , valueN);
• value1 thru valueN are in the order the columns were
defined in the CREATE TABLE
• NULL can be used for those values not known
– To insert only a few columns use
INSERT INTO tabelname(col1,col2,…colN)
VALUES (value1, value2, …valueN);
23
Adding Table Rows (cont’d.)
• When entering values, notice that:
– Row contents are entered between parentheses
– Character and date values are entered between
apostrophes
– Numerical entries are not enclosed in
apostrophes
– Attribute entries are separated by commas
– A value is required for each column
• Use NULL for unknown values
24
Saving Table Changes
• Changes made to table contents are not physically
saved on disk until:
– Database is closed
– Program is closed
– COMMIT command is used
• Syntax:
– COMMIT [WORK];
• Will permanently save any changes made to any table
in the database
• MS Access does not support COMMIT because it
automatically saves changes after the execution of
each SQL command
25
Listing Table Rows
• SELECT
– Used to list contents of table
– Syntax:
SELECT columnlist|* FROM tablename;
• Columnlist represents one or more attributes, separated
by commas
• Asterisk can be used as wildcard character to list all
attributes
• ORACLE allows you to format column data by declaring
– COLUMN P_PRICE FORMAT $99,9999.99
– COLUMN P_DESCRIPT FORMAT A12 TRUNCATE
» Truncates description to first 12 characters
26
Updating Table Rows
• UPDATE
– Modify data in a table
– Syntax:
UPDATE tablename
SET columnname = expression [, columnname = expression]
[WHERE conditionlist];
• If more than one attribute is to be updated in row,
separate corrections with commas
UPDATE PRODUCT
SET
P_INDATE=‘18-JAN-2012’, P_PRICE=17.99, P_MIN=10
WHERE P_CODE=‘13-Q2/P2’;
– Without a WHERE clause, all rows would be updated
27
Restoring Table Contents
• ROLLBACK
– Undoes changes since last COMMIT
– Brings data back to prechange values. Does not
undo a COMMIT but undoes any changes not
COMMIT’d
– mySQL requires START TRANSACTION; and
BEGIN;
• Syntax:
ROLLBACK;
• COMMIT and ROLLBACK only work with
commands to add, modify, or delete table rows
Database Systems, 10th Edition
28
Deleting Table Rows
• DELETE
– Deletes a table row
– Syntax:
DELETE FROM tablename
[WHERE conditionlist ];
• WHERE condition is optional
• If WHERE condition is not specified, all rows
from specified table will be deleted
Database Systems, 10th Edition
29
Inserting Table Rows with a
SELECT Subquery
• INSERT
– Inserts multiple rows from another table (source)
– Uses SELECT subquery
– Subquery: query embedded (or nested or inner)
inside another query
– Subquery executed first
– Syntax:
INSERT INTO tablename SELECT columnlist
FROM tablename;
Database Systems, 10th Edition
30
SELECT Queries
• Fine-tune SELECT command by adding
restrictions to search criteria using:
–
–
–
–
Conditional restrictions
Arithmetic operators
Logical operators
Special operators
Database Systems, 10th Edition
31
Selecting Rows with Conditional Restrictions
• Select partial table contents by placing restrictions on
rows to be included in output
– Add conditional restrictions to SELECT statement, using
WHERE clause
• Syntax:
SELECT columnlist FROM tablelist [ WHERE
conditionlist ] ;
•WHERE V_CODE =21344
•WHERE V_CODE<> 21344
•WHERE PRICE <=10
•WHERE P_CODE < ‘1558-WQ1’
•WHERE P_INDAT>=‘20-Jan-2012’
32
Database Systems, 10th Edition
33
• String comparisons are made from left to right
• Note that 5 will come after 44 even though the number
5 comes before the number 44.
Database Systems, 10th Edition
34
Selecting Rows with
Conditional Restrictions (cont’d.)
• Using comparison operators on dates
– Date procedures are often more software-specific than
other SQL procedures
• Using computed columns and column aliases
– SQL accepts any valid expressions (or formulas) in the
computed columns
• SELECT … ,P_QOH*P_PRICE
– Alias: Alternate name given to a column or table in any
SQL statement
• SELECT … ,P_QOH*P_PRICE AS TOTVALUE
35
Arithmetic Operators:
The Rule of Precedence
•
•
•
•
Perform operations within parentheses
Perform power operations
Perform multiplications and divisions
Perform additions and subtractions
Database Systems, 10th Edition
36
Logical Operators: AND, OR, and NOT
• Searching data involves multiple conditions
• Logical operators: AND, OR, and NOT
• Can be combined
– Parentheses enforce precedence order
• Conditions in parentheses are always executed
first
• Boolean algebra: mathematical field dedicated
to use of logical operators
• NOT negates result of conditional expression
37
Logical Operators: AND, OR, and NOT
• WHERE P_PRICE < 50 AND P_INDATE >’15-Jan-2012’
• WHERE (P_PRICE < 50 AND P_INDATE >’15-Jan-2012’) OR
V_CODE=24288
• WHERE NOT(V_CODE=21344)
38
Special Operators
• BETWEEN: checks whether attribute value is within a range
P_PRICE BETWEEN 50.00 AND 100
• IS NULL: checks whether attribute value is null
V_CODE IS NULL
39
Special Operators
• LIKE: checks whether attribute value matches given string
pattern V_CONTACT LIKE ‘Smith%’
– % mean any and all following or preceding characters
• ‘J%’ includes Johnson, Jones, July and J-231q
• ‘Jo%’ includes Johnson and Jones
• ‘%n’ includes Johnson
– _ means any one character may be substituted for the underscore
• ‘_23-456-6789’ includes 123…, 223…, 323
• ‘_23-_56-678_’ includes 123-156-6781, 123-256-6782 and 823-9566788
– Oracle is case-sensitive on searches. You can use UPPER to
convert column data to upper case in memory only
UPPER(V_CONTACT) LIKE ‘SMITH%’
– Not sure if name is Johnson or Johnsen use
V_CONTACT LIKE ‘Johns_n’
40
Special Operators
• IN: checks whether attribute value matches any value
within a value list V_CODE IN (21344,24288)
– Can be used in subqueries such as
SELECT V_CODE, V_NAME FROM VENDOR WHERE
V_CODE IN (SELECT V_CODE FROM PRODUCT)
• EXISTS: Execute a command based on the result of
another query and runs only if subquery returns at
least one row
SELECT * FROM VENDOR WHERE EXISTS (SELECT
* FROM PRODUCT WHERE P_QOH<=P_MIN);
41
Advanced Data Definition Commands
• All changes in table structure are made by
using ALTER command
• Three options:
– ADD adds a column
– MODIFY changes column characteristics
– DROP deletes a column
• Can also be used to:
– Add table constraints
– Remove table constraints
Database Systems, 10th Edition
42
Changing a Column’s Data Type
• ALTER can be used to change data type
ALTER TABLE PRODUCT MODIFY (V_CODE CHAR(5));
– If the column contains data, changes can be made
to the data type if those changes do not alter the
type but just change the size e.g., DECIMAL (8,2)
to (9,2)
• Some RDBMSs do not permit changes to data
types unless column is empty or only let you
increase the size of the field
Database Systems, 10th Edition
43
Adding a Column
Dropping a Column
• Use ALTER to add column
– Do not include the NOT NULL clause for new column as
the new column will default to a value of NULL
ALTER TABLE PRODUCT ADD (P_SALECODE CHAR(1));
• Use ALTER to drop column
– Some RDBMSs impose restrictions on the deletion of
an attribute
ALTER TABLE VENDOR DROP COLUMN V_ORDER;
Database Systems, 10th Edition
44
Advanced Data Updates
• UPDATE command updates only data in existing rows
• If relationship between entries and existing columns,
can assign values to slots
• Arithmetic operators are useful in data updates
UPDATE PRODUCT SET P_SALECODE=‘2’
WHERE P_CODE =‘1546-QQ2’;
WHERE clause can use IN, logical operators, etc.
UPDATE PRODUCT SET P_QOH=P_QOH+20
WHERE P_CODE =‘1546-QQ2’;
Any mathematical operation can be done on SET
45
Copying Parts of Tables
• SQL permits copying contents of selected table
columns
– Data need not be reentered manually into newly
created table(s)
• First create the table structure (PART)
• Next add rows to new table using table rows from
another table (PRODUCT)
INSERT INTO PART (PART_CODE, PART_DESCRIPT, PART_PRICE)
SELECT P_CODE,P_DESCRIPT,P_PRICE FROM PRODUCT;
Or in one step
CREATE TABLE PART AS
SELECT P_CODE AS PART_CODE, P_DESCRIPT AS PART_DESCRIPT,
P_PRICE AS PART_PRICE
FROM PRODUCT;
46
Adding Primary and Foreign Key Designations
• When table is copied, integrity rules do not copy
– Primary and foreign keys are manually defined on new
table
• User ALTER TABLE command
• ALTER TABLE tablename ADD PRIMARY
KEY(fieldname);
ALTER TABLE PART ADD PRIMARY KEY(PART_CODE)
– For foreign key, use FOREIGN KEY in place of
PRIMARY KEY
ALTER TABLE PART ADD FOREIGN
KEY(V_CODE)REFERENCES VENDOR;
– You can do multiple changes in one ALTER statement
• One ADD follows another
47
Deleting a Table from the Database
• DROP
– Deletes table from database
– Syntax:
• DROP TABLE tablename;
• Can drop a table only if it is not the “one” side
of any relationship
– Otherwise, RDBMS generates an error message
– Foreign key integrity violation
Database Systems, 10th Edition
48
Additional SELECT Query Keywords
• Logical operators work well in the query
environment
• SQL provides useful functions that:
– Count
– Find minimum and maximum values
– Calculate averages, etc.
• SQL allows user to limit queries to:
– Entries having no duplicates
– Entries whose duplicates may be grouped
Database Systems, 10th Edition
49
Ordering a Listing
• ORDER BY clause is useful when listing order is
important
• Syntax:
SELECT columnlist
FROM tablelist
[WHERE conditionlist]
[ORDER BY columnlist [ASC | DESC]];
• Ascending order by default
Database Systems, 10th Edition
50
Listing Unique Values
• DISTINCT clause produces list of only values
that are different from one another
• Example:
SELECT DISTINCT V_CODE
FROM PRODUCT;
• Access places nulls at the top of the list
– Oracle places it at the bottom
– Placement of nulls does not affect list contents
Database Systems, 10th Edition
51
Aggregate Functions
• COUNT function tallies number of non-null values of
an attribute
– Can include DISTINCT
– Takes one parameter: usually a column name
• MAX and MIN find highest (lowest) value in a table
– Compute MAX value in inner query
– Compare to each value returned by the query
– Can be used with DATE fields or as MAX of a
computed column SELECT MAX(A*B) …
• SUM computes total sum for any specified attribute
• AVG function format is similar to MIN and MAX
Database Systems, 10th Edition
52
Aggregate Functions
SELECT COUNT (DISTINCT V_CODE) FROM PRODUCT
WHERE P_PRICE <=10.00;
//TOTAL ROWS INCLUDING NULLS
SELECT COUNT(*) FROM PRODUCT;
SELECT MAX(P_PRICE) FROM PRODUCT;
SELECT P_CODE, P_DESCRIPT, P_PRICE FROM PRODUCT
WHERE P_PRICE =
SELECT MAX(P_PRICE) FROM PRODUCT);
USING P_PRICE = MAX(P_PRICE) DOES NOT WORK AS
MAX CAN ONLY BE USED IN THE COLUMN LIST OF A
SELECT STATEMENT. ALSO IN A COMAPRISON OF
EQUALITY, YOU CAN ONLY USE A SINGLE VALUE TO
THE RIGHT OF THE EQUALS SIGN
53
Grouping Data
• Frequency distributions created by GROUP BY
clause within SELECT statement
• Requires aggregate function in the SELECT
column list
• Syntax:
SELECT
FROM
[WHERE
[GROUP BY
[HAVING
[ORDER BY
Database Systems, 10th Edition
columnlist
tablelist
conditionlist]
columnlist]
conditionlist]
columnlist [ASC | DESC] ] ;
54
Grouping Data
• Rows can be grouped into smaller collections
and an aggregate function can then summarize
the data within each collection
Minimum price within each salecode
Average price within each salecode
55
• The GROUP BY clause’s columnlist must include all nonaggregate
function columns in the SELECT’s columnlist
• The GROUP BY columnlist can include any columns from the tables
in the FROM clause even if they do not appear in the SELECT’s
56
columnlist
HAVING Clause
• Operates like a WHERE clause in the SELECT
statement but applies to the output of the GROUP BY
Limit results to products whose
average price is less than $10
57
Joining Database Tables
• Joining tables is the most important distinction
between relational database and other DBs
• Join is performed when data are retrieved from
more than one table at a time
– Equality comparison between foreign key and
primary key of related tables
• Join tables by listing tables in FROM clause of
SELECT statement
– DBMS creates Cartesian product of every table
• When joining 3 or more tables, a join condition is
needed for each pair (n-1 pairs in all)
Database Systems, 10th Edition
58
Joining Database Tables
• Join condition is generally composed of an equality
compariosn between the FK and the PK of related
tables
SELECT P_DESCRIPT, P_PRICE, V_NAME, V_CONTACT,
V_AREACODE, V_PHONE
FROM PRODUCT, VENDOR
WHERE PRODUCT.V_CODE=VENDOR.V_CODE
AND P_INDATE>’15-JAN-2012’
ORDER BY PRODUCT.PRICE;
59
Joining Tables with an Alias
• Alias identifies the source table from which data
are taken
• Alias can be used to identify source table
• Any legal table name can be used as alias
• Add alias after table name in FROM clause
– FROM tablename alias
SELECT P_DESCRIPT, P_PRICE, V_NAME,
V_CONTACT, V_AREACODE, V_PHONE
FROM PRODUCT P, VENDOR V
WHERE P.V_CODE=V.V_CODE
ORDER BY P.PRICE;
Database Systems, 10th Edition
60
Recursive Joins
• Alias is especially useful when a table must be
joined to itself
– Recursive query
– Use aliases to differentiate the table from itself
Database Systems, 10th Edition
61
Recursive Joins
• Generate a list of all employees with their
managers’ names
62
Recursive Joins
SELECT E.EMP_MGR, M.EMP_LNAME, E.EMP_NUM, E.EMP_LNAME
FROM EMP E, EMP M
WHERE E.EMP_MGR = M.EMP_NUM
ORDER BY E.EMP_MGR;
63