Transcript Database Systems: Design, Implementation, and

SQL – Structured
Query Language
Relational Database
Operators
• Relational algebra determines
table manipulations
• Key operators
• SELECT
• PROJECT
• JOIN
• Other operators
• UNION
• INTERSECT
• DIFFERENCE
• PRODUCT
• DIVIDE
2
Union
Combines all rows
Figure 2.5
3
Intersect
Yields rows that appear in both tables
Figure 2.6
4
Difference
Yields rows not found in other tables
Figure 2.7
5
Product
Yields all possible pairs from two tables
Figure 2.8
6
Select
Yields a subset of rows based on specified
criterion
Figure 2.9
7
Project
Yields all values for selected attributes
Figure 2.10
8
Join
Information from two or more tables is
combined
Figure 2.11
Figure 2.14
9
Natural Join Process
• Links tables by selecting rows with common values
in common attribute(s)
• Three-stage process
• Product creates one table
• Select yields appropriate rows
• Project yields single copy of each attribute to
eliminate duplicate columns
10
Other Joins
• EquiJOIN
• Links tables based on equality condition that
compares specified columns of tables
• Does not eliminate duplicate columns
• Join criteria must be explicitly defined
• Theta JOIN
• EquiJOIN that compares specified columns of
each table using operator other than equality
one
• Outer JOIN
• Matched pairs are retained
• Unmatched values in other tables left null
• Right and left
11
Divide
Requires user of single-column table and two-column table
Figure 2.17
12
Data Dictionary and
System Catalog
• Data dictionary
• Provides detailed account of all tables found within
database
• Metadata
• Attribute names and characteristics
• System catalog
• Detailed data dictionary
• System-created database
• Stores database characteristics and contents
• Tables can be queried just like any other tables
• Automatically produces database documentation
13
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 (DDL)
• Data manipulation language (DML)
14
DDL vs. DML examples
Name
DDL: Data Definition
Language
DML: Date Manipulation
Language
Purpose
Defines Structure of
Database and Database
Objects
Manipulates the Data housed
in the tables
Add
Create table: creates a new Insert Into: adds a new
table
record to a table
Change
Alter table: modifies the
Update: changes the values
tables structure (add a
of an attribute in a record
column, change a datatype,
add constraint, etc.)
Remove
Drop table: deletes the
table from the database
Delete: deletes a record
from a table
15
Most Common Data Types
Data Type
Data Type Description
CHAR(n)
•
•
VARCHAR2(n)
variable length column with a fixed length. If the length of the
data is less than the maximum length of the field, then the
field is not padded with spaces.
• maximum length of the column = 2000.
e.g: a customer’s first name - VARCHAR2(35) since name
length is variable.
NUMBER
Integer and real values occupying up to 40 spaces.
INTEGER
Same as number, but no decimals.
DATE
contains a date and time between the 1st of January 4712 BC
to the 31st of December 4712 AD.
• standard date format: DD-MMM-YY (i.e. 01-JAN-99)
• Any other format will require input mask.
fixed length column can contain any printable characters.
If the data entered into CHAR field < length of field, field is
padded with spaces.
• maximum length of CHAR column = 200.
e.g: a state abbreviation - CHAR(2) since it is always 2
characters long.
16
Creating Table Structure
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>);
Column
Names
Data
types and
size
Null vs. Not Null
(can the field be left
blank when data
entered NOTE:
null <> 0
Check constraints –
show acceptable
values for attribute
and/or default
value.
17
Drop – table or column
• Delete table from database:
• Drop table <tablename> cascade constraints
e.g. drop table student cascade constaints
• Delete column from database
• Alter table <tablename> drop column <column name>
e.g. alter table student drop column student_lastname
18
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>)]
19
SQL Integrity Constraints and
Data Anomolies
• Entity integrity - enforced automatically with
PRIMARY KEY constraint
• Referential integrity - enforced FOREIGN KEY
constraint
• Other specifications to ensure conditions met:
• ON DELETE RESTRICT
• ON UPDATE CASCADE
20
Data Anomalies, Redundacy, and
constraints
•
Data anomalies
• Modification
• Insertion
• Deletion
ON UPDATE CASCADE
PRIMARY KEY constraint
ON DELETE RESTRICT
21
Alter, Add, and Modify
• 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>);
22
Data Manipulation
Commands
Common SQL DML Commands
Table 5.3
23
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> ;
24
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
25
Queries
• Creating partial listings of table contents
SELECT <column(s)>
FROM <table name>
WHERE <conditions>;
Table 5.4
Mathematical Operators
26
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’;
27
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;
28
Operators
•
Logical: AND, OR, NOT
•
Rules of precedence
SELECT *
FROM PRODUCT
WHERE V_CODE = 21344 OR
V_CODE = 24288;
• 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
29
Drop column and update
• Dropping a column
•
ALTER TABLE VENDOR
DROP COLUMN V_ORDER;
Update (add new data to a column)
UPDATE PRODUCT
SET P_SALECODE = ‘2’
WHERE P_CODE = ‘1546-QQ2’;
Table 5.5
30
Arithmetic Operators –
Rules of Precedence
• Arithmetic operators and rules of precedence
Table 5.5
31
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;
32
Aggregate Function
Operations
Count,
Max
and
Min
• COUNT
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);
33
Aggregate Function
Operaters Sum/ Avg
• 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;
34
Order by
•
Ordering a listing
ORDER BY <attributes>
•
Results ascending by default
• Descending order uses DESC
•
Cascading order
sequence
ORDER
BY <attributes>
DESC
ORDER BY <attribute 1, attribute 2, ...>
35
Distinct
•
Listing unique values
• DISTINCT clause produces list of different values
SELECT DISTINCT V_CODE
FROM PRODUCT;
36
Group by/ Having
• Grouping data
• Creates frequency distributions
• Only valid when used with SQL arithmetic functions
SELECT P_SALECODE, MIN(P_PRICE)
FROM PRODUCT_2
• HAVING
clause operates like WHERE for grouping
GROUP BY P_SALECODE;
output
SELECT V_CODE,COUNT(DISTINCT(P_CODE)),AVG(P_PRICE)
FROM PRODUCT_2
GROUP BY V_CODE
HAVING AVG(P_PRICE) < 10;
37
Joins
• Joining database tables
• Data 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;
38