Title: Structured Query Language SQL
1Chapter 5
- Structured Query Language (SQL)
- Database Systems Design, Implementation, and
Management, Fifth Edition, Rob and Coronel
2In 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
3Introduction 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
4Good 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
5(No Transcript)
6(No Transcript)
7Data 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 ltcreatorgt ExampleCRE
ATE SCHEMA AUTHORIZATION JONES
8Creating Table Structure
- Tables store end-user data
- May be based on data dictionary entries
CREATE TABLE lttable namegt(ltattribute1 name and
attribute1 characteristics,attribute2 name and
attribute2 characteristics,attribute3 name and
attribute3 characteristics,primary key
designation,foreign key designation and foreign
key requirementgt)
9Using Domains
- Domain is set of permissible values for a column
- Definition requires
- Name
- Data type
- Default value
- Domain constraint or condition
CREATE DOMAIN ltdomain_namegt AS DATA_TYPEDEFAULT
ltdefault_valuegt CHECK (ltconditiongt)
10SQL 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
11Data Manipulation Commands
Table 5.3
12Data Entry and Saving
- Enters data into a table
- Saves changes to disk
INSERT INTO lttable namegt VALUES (attribute 1
value, attribute 2 value, etc.)
COMMIT lttable namesgt
13Listing Table Contents and Other Commands
- Allows table contents to be listed
- UPDATE command makes data entry corrections
- ROLLBACK command restores database back to
previous condition if COMMIT hasnt been used - DELETE command removes table row
SELECT ltattribute namesgt FROM lttable namesgt
14Queries
- Creating partial listings of table contents
SELECT ltcolumn(s)gtFROM lttable namegtWHERE
ltconditionsgt
Table 5.4 Mathematical Operators
15SELECT P_DESCRIPT, P_INDATE, P_PRICE, V_CODE FROM
PRODUCT WHERE V_CODE21344
16SELECT P_DESCRIPT, P_INDATE, P_PRICE, V_CODE FROM
PRODUCT WHERE V_CODE ltgt21344
17SELECT P_CODE,P_DESCRIPT, P_ONHAND,
P_MIN,P_PRICE FROM PRODUCT WHERE
P_CODElt1558-QW1
18Computed 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_ONHANDP_PRIC
E AS TOTVALUE FROM PRODUCT
19(No Transcript)
20Operators
SELECT FROM PRODUCTWHERE V_CODE 21344 OR
V_CODE 24288
21Operators (cont)
- Rules of precedence
- Conditions within parenthesis executed first
- Boolean algebra
SELECT P_DESCRIPT, P_INDATE, P_PRICE, V_CODE FROM
PRODUCT
WHERE (P_PRICE lt50 AND
P_INDATEgt01/05/2002) OR V_CODE24288
22Operators (cont)
- Special
- BETWEEN - defines limits
- IS NULL - checks for nulls EXISTS - opposite of
IS NULL - LIKE - checks for similar string
- IN - checks for value in a set
SELECT FROM VENDOR WHERE V_CONTACT LIKE
Johns_n
SELECT FROM PRODUCT WHERE V_CODE IN
(21344,24288)
23Advanced Data ManagementCommands
- ALTER - changes table structure
- ADD - adds column
- MODIFY - changes column characteristics
ALTER TABLE lttable namegtMODIFY (ltcolumn namegt
ltnew column characteristicsgt)
ALTER TABLE PRODUCT MODIFY (V_CODE CHAR(5))
ORACLE Only if the column is empty!
24Adding a New Column
ALTER TABLE lttable namegtADD (ltcolumn namegt ltnew
column characteristicsgt)
- Entering data into new column
ALTER TABLE PRODUCT ADD (P_SALECODE CHAR(1))
UPDATE PRODUCTSET P_SALECODE 1WHERE P_CODE
1546-QQ2
UPDATE PRODUCTSET P_SALECODE 2WHERE
P_INDATElt12/25/2001
25Advanced Data Management Commands (cont.)
- Dropping a column (deleting the column!)
- Arithmetic operators and rules of precedence
ALTER TABLE VENDORDROP COLUMN V_ORDER
Table 5.5
26Use of Arithmetic Operators
- Increases P_ONHAND for a specific product
UPDATE PRODUCT SET P_ONHAND P_ONHAND20 WHERE
P_CODE2232/QWE
- Increases PRICE for cheap products
UPDATE PRODUCT SET PRICE PRICE1.1 WHERE PRICE
lt50
27Copying Parts of Tables
INSERT INTO ltreceiving tablegt ltreceiving tables
column namesgtSELECT ltcolumn names of the columns
to be copiedgtFROM ltcontributing table namegt
CREATE TABLE(PART PART_CODE CHAR(8) NOT NULL
UNIQUE, PART_DESCRIPT CHAR(35) PART_PRICE
DECIMAL (8,2), PRIMARY_KEY (PART_CODE))
INSERT INTO PART (PART_CODE, PART_DESCRIPT,
PART_PRICE)SELECT P_CODE, P_DESCRIPT,
P_PRICE FROM PRODUCT
Useful for normalization! Useful for merging two
tables into the third one!
28Advanced Data Management Commands (cont.)
- Deleting a table from database
- Primary and foreign key designation
DROP TABLE PART
ALTER TABLE LINE ADD PRIMARY KEY (INV_NUMBER,
LINE_NUMBER) ADD FOREIGN KEY (INV_NUMBER)
REFERENCES INVOICE ADD FOREIGN KEY (PROD_CODE)
REFERENCES PRODUCT
-We can assign several primary and foreign keys
to the table -Keys can be even composite
29Ordering a Listing
ORDER BY ltattributesgt
SELECT P_CODE, P_DESCRIPT, P_INDATE, P_PRICE FROM
PRODUCT ORDER BY P_PRICE
30Ordering a Listing (cont)
- Results ascending by default
- Descending order uses DESC
ORDER BY ltattributesgt DESC
SELECT P_CODE, P_DESCRIPT, P_INDATE, P_PRICE FROM
PRODUCT ORDER BY P_PRICE
31Ordering a Listing (cont)
- Cascading order sequence (to make an alphabetic
listing_
ORDER BY ltattribute 1, attribute 2, ...gt
SELECT EMP_LNAME, EMP_FNAME, EMP_INITIAL,
EMP_AREACODE, EMP_PHONE, FROM EMPLOYEE ORDER BY
EMP_LNAME, EMP_FNAME, EMP_INITIAL
32More Complex Queries and SQL Functions (cont.)
- Listing unique values
- DISTINCT clause produces list of different values
SELECT DISTINCT V_CODE FROM PRODUCT
33Aggregate Functions
- Aggregate functions
- Mathematical summaries
34Example Aggregate Function Operations
SELECT COUNT(DISTINCT V_CODE)FROM
PRODUCT SELECT COUNT(DISTINCT V_CODE)FROM
PRODUCTWHERE P_PRICE lt 10.00
NESTED query
SELECT MIN(P_PRICE)FROM PRODUCT SELECT P_CODE,
P_DESCRIPT, P_PRICEFROM PRODUCTWHERE P_PRICE
(SELECT MAX(P_PRICE) FROM PRODUCT)
35Example Aggregate Function Operations (cont.)
SELECT SUM(P_ONHAND P_PRICE) AS TOTVALUE FROM
PRODUCT
SELECT P_DESCRIPT, P_ONHAND, P_PRICE, V_CODEFROM
PRODUCTWHERE P_PRICE gt (SELECT AVG(P_PRICE)
FROM PRODUCT)ORDER BY P_PRICE DESC
36Group and Having
- Grouping data
- Creates frequency distributions
- Only valid when used with SQL AGGREGATE functions
- HAVING clause operates like WHERE for grouping
output
SELECT P_SALECODE, MIN(P_PRICE)FROM
PRODUCT_2GROUP BY P_SALECODE
SELECT V_CODE,COUNT(DISTINCT(P_CODE)),AVG(P_PRICE)
FROM PRODUCT_2 GROUP BY V_CODE HAVING
AVG(P_PRICE) lt 10
37Group and Having (cont)
38Virtual tables Creating a View
- CREATE VIEW command
- Creates logical table existing only in virtual
memory - Views can be used to update tables but (stay
tuned ?)
CREATE VIEW ltview_namegt ASSELECT ltlist of
attributesgt FROM ltlist of tablesgt WHERE
ltconditionsgt
This is an usual SQL query (could involve
aggregate functions, HAVING). NOTE Syntax is
analog to the the insertion into a table
39Virtual tables Creating a View (cont)
Names of attributes are copied
40Creating Indexes
- Non-unique index
- Index on unique attributes (faster)
- Index on multiple attributes (yet faster)
CREATE INDEX P_CODEXON PRODUCT(P_CODE)
CREATE UNIQUE INDEX P_CODEXON PRODUCT(P_CODE)
CREATE UNIQUE INDEX VENPRODXON PRODUCT(P_CODE,
V_CODE)
41Joining 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_PHONEFROM PRODUCT,
VENDORWHERE PRODUCT.V_CODE VENDOR.V_CODE
42Recursive Queries
- Recursive queries joins a table to itself
- Using Aliases
SELECT A.EMP_NUM,A.EMP_LNAME,A.EMP_MGR, B.EMP_LNAM
E FROM EMP A, EMP B WHERE A.EMP_MGRB.EMP_NUM ORDE
R BY A.EMP_MGR
43Outer Joins
- Outer joins can be used when null values need
to be included in query result - Left Join (in Oracle)
SELECT P_CODE, PRODUCT.V_CODE, V_NAME, FROM
PRODUCT, VENDOR WHERE PRODUCT.V_CODE()VENDOR.V_C
ODE
44Outer Joins (cont)
SELECT P_CODE, PRODUCT.V_CODE, V_NAME, FROM
PRODUCT, VENDOR WHERE PRODUCT.V_CODEVENDOR.V_CODE
()
45Updatable 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
46Procedural SQL
- SQL shortcomings
- Doesnt 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
47Procedural SQL (cont.)
- 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
48Triggers
- 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
- Derived attributes
49Triggers (cont.)
CREATE OR REPLACE TRIGGER lttrigger_namegtBEFORE/A
FTER DELETE/INSERT/UPDATE OF ltcolumn_name ON
lttable_namegtFOR EACH ROWBEGIN PL/SQL
instructions END
50Trigger Example
- When P_ONHAND changed check ALL rows
51Trigger Example
- Watches both P_ONHAND, P_MIN
52Trigger Example
- Only for affected rows Execute BEFORE
transaction is commited Can access old and new
value
53Stored Procedures
- Named collection of procedural and SQL statements
stored in database - Invoked by name
- Executed as unit
- Invoked with EXEC
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 statementsEND
EXEC store_procedure_name (parameter, parameter,
)
54Stored Procedure
55(No Transcript)
56PL/SQL Stored Functions
- Named group of procedural and SQL statements that
returns a value - Invoked from within stored procedures or triggers
- Cannot be invoked from within SQL statements
CREATE FUNCTION function_name (argument IN
data-type, etc)RETURN data-typeAS BEGIN PL/SQL
statements RETURN (value) END
57Converting ER diagram into Database-Example
58Artist Database ERD and Tables
Figure 5.55
59General Rules Governing Relationships Among
Tables
Figure 5.56 MN, Both Sides Mandatory
Figure 5.57 MN, Both Sides Optional
60General Rules Governing Relationships Among
Tables (Cont.)
Figure 5.58 MN, One Side Optional
Figure 5.59 1M, Both Sides Mandatory
61General Rules Governing Relationships Among
Tables (Cont.)
Figure 5.60 1M, Both Sides Optional
Figure 5.61 1M, Many Side Optional, One Side
Mandatory
62General Rules Governing Relationships Among
Tables (Cont.)
Figure 5.62 1M, One Side Optional, One Side
Mandatory
Figure 5.63 11, Both Sides Mandatory
63General Rules Governing Relationships Among
Tables (Cont.)
Figure 5.64 11, Both Sides Optional
Figure 5.65 11, One Side Optional, One Side
Mandatory
64General Rules Governing Relationships Among
Tables (Cont.)
Figure 5.66 Weak Entity, Foreign Key Located in
Weak Entity
Figure 5.67 Multivalued Attributes (New Table in
1M Relationship, Foreign Key CAR_VIN in the New
Table