Transcript Chapter 7

7
Chapter 7
Introduction to Structured Query
Language (SQL)
Database Systems:
Design, Implementation, and Management,
Seventh Edition, Rob and Coronel
1
7
In this chapter, you will learn:
• The basic commands and functions of SQL
• How to use SQL for data administration (to
create tables, indexes, and views)
• How to use SQL for data manipulation (to
add, modify, delete, and retrieve data)
• How to use SQL to query a database to
extract useful information
Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel
2
7
Introduction to SQL
• SQL functions fit into two broad categories:
– Data definition language
• SQL includes commands to:
– Create database objects, such as tables,
indexes, and views
– Define access rights to those database objects
– Data manipulation language
• Includes commands to insert, update, delete,
and retrieve data within database tables
Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel
3
7
Introduction to SQL (continued)
• SQL is relatively easy to learn
• Basic command set has vocabulary of less
than 100 words
• Nonprocedural language
• American National Standards Institute (ANSI)
prescribes a standard SQL
• Several SQL dialects exist
Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel
4
7
Introduction to SQL (continued)
Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel
5
7
Introduction to SQL (continued)
Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel
6
7
Introduction to SQL (continued)
Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel
7
7
Data Definition Commands
• Examine simple database model and
database tables that will form basis for many
SQL examples
• Understand data environment
Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel
8
7
The Database Model
Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel
9
7
The Database Model (continued)
Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel
10
7
Creating the Database
• Following 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
– Tends to differ substantially from one RDBMS
to another
Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel
11
7
The Database Schema
• Authentication
– Process through which DBMS verifies that
only registered users are able to access
database
– Log on to RDBMS using user ID and
password created by database administrator
• Schema
– Group of database objects—such as tables
and indexes—that are related to each other
Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel
12
7
Data Types
• Data type selection is usually dictated by
nature of data and by intended use
• Pay close attention to expected use of
attributes for sorting and data retrieval
purposes
Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel
13
7
Data Types (continued)
Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel
14
7
Creating Table Structures
• Use one line per column (attribute) definition
• Use spaces to line up attribute characteristics
and constraints
• Sql commands are capitalized
• NOT NULL specification
• UNIQUE specification
Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel
15
7
Creating Table Structures (continued)
• Primary key attributes indicate both a NOT
NULL and a UNIQUE specification
• RDBMS will automatically enforce referential
integrity for foreign keys
• Command sequence ends with semicolon
Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel
16
7
SQL Constraints
• NOT NULL constraint
– Ensures that column does not accept nulls
• UNIQUE constraint
– Ensures that all values in column are unique
• DEFAULT constraint
– Assigns value to attribute when a new row is added to
table
• CHECK constraint
– Validates data when attribute value is entered
Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel
17
7
Creating Table Structures (continued)
• CREATE TABLE CUSTOMER(
CUS_CODE NUMBER(7) PRIMARY KEY,
CUS_LNAME VARCHAR2 (20),
CUS_ZIP CHAR (5),
AGENT_CODE NUMBER (3) DEFAULT
412);
Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel
18
7
Creating Table Structures (continued)
CREATE TABLE AGENT(
AGENT_CODE NUMBER(3) PRIMARY KEY,
AGENT_PHONE CHAR (10));
Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel
19
7
Creating Table Structures (continued)
CREATE TABLE Product(
p_code CHAR(10),
/*...*/
p_descript VARCHAR2(35),
--...
p_indate DATE,
p_onhand SMALLINT,
p_min SMALLINT,
p_price NUMBER(8,2) NOT NULL,
p_discount NUMBER(4,2),
v_code INTEGER CONSTRAINT v_code_fk
REFERENCES Vendor(v_code),
CONSTRAINT p_code_pk PRIMARY KEY (p_code));
Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel
20
7
Creating Table Structures (continued)
CREATE TABLE Product(
p_code CHAR(10) CONSTRAINT p_code_pk PRIMARY KEY,
p_descript VARCHAR2(35),
p_indate DATE,
p_onhand SMALLINT,
p_min SMALLINT,
p_price NUMBER(8,2) NOT NULL,
p_discount NUMBER(4,2),
v_code INTEGER CONSTRAINT v_code_fk
REFERENCES Vendor(v_code));
Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel
21
7
Creating Table Structures (continued)
CREATE TABLE Employee(
ssn CHAR(10),
salary NUMBER(9,2),
title VARCHAR2(20),
class NUMBER(1) constraint class_ck CHECK
(class BETWEEN 1 AND 4),
CONSTRAINT title_uk UNIQUE (title),
CONSTRAINT ssn_salary_pk PRIMARY KEY
(ssn, salary));
Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel
22
7
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
• Composite index
– Index based on two or more attributes
– Often used to prevent data duplication
Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel
23
7
SQL Indexes (continued)
CREATE [UNIQUE] INDEX indexname ON
tablename (column1 [, column2])
CREATE INDEX Emptest on Test (emp_num)
Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel
24
7
SQL Indexes (continued)
Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel
25
7
Data Manipulation Commands
• Adding table rows
• Saving table changes
• Listing table rows
• Updating table rows
• Restoring table contents
• Deleting table rows
• Inserting table rows with a select subquery
Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel
26
7
Adding Table Rows
• INSERT
– Used to enter data into table
– Insert a row each time
– Syntax:
• INSERT INTO tablename
VALUES (value1, value2, … , valuen);
Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel
27
7
Adding Table Rows (continued)
• 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
Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel
28
7
Adding Table Rows (continued)
INSERT INTO Product VALUES (‘BRT-345’,
‘Titanium drill bit’, TO_DATE(’18-Oct-05’, ’DDMON-YY’), 75, 10, 4.5, 0.06, NULL);
or TO_DATE (’18/Oct/05’, ’DD/MON/YY’)
or TO_DATE(’10/18/2005’, ‘MM/DD/YYYY’)
or TO_DATE(‘October 18, 2005’, ‘Month DD,
YYYY’)
Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel
29
7
Saving Table Changes
• Changes made to table contents are not
physically saved on disk until, one of the
following occurs:
– 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
Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel
30
7
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
Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel
31
7
Listing Table Rows (continued)
SELECT p_code, p_descript, p_indate, p_qoh,
p_min, p_price, p_discount, v_code
FROM Product;
SELECT *
FROM Product;
Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel
32
7
Listing Table Rows (continued)
Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel
33
7
Updating Table Rows
• UPDATE
– Modify data in a table
– Syntax:
• UPDATE tablename
SET columnname = expression [, columname =
expression]
[WHERE conditionlist];
• If more than one attribute is to be updated in
row, separate corrections with commas
Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel
34
7
Updating Table Rows (continued)
UPDATE Product
SET p_indate = ’18-Jan-2006’, p_price = 7.6
WHERE p_code = ‘13-Q2/P2’;
Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel
35
7
Restoring Table Contents
• ROLLBACK
– Used to restore database to its previous condition
– Only applicable if COMMIT command has not
been used to permanently store changes in
database
• Syntax:
– ROLLBACK;
• COMMIT and ROLLBACK only work with data
manipulation commands that are used to add,
modify, or delete table rows
Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel
36
7
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: Design, Implementation, & Management, 7th Edition, Rob & Coronel
37
7
Deleting Table Rows (continued)
DELETE FROM Product
WHERE p_code = ‘BRT-345’;
Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel
38
7
Inserting Table Rows with a
Select Subquery
• INSERT
– Inserts multiple rows from another table
(source)
– Uses SELECT subquery
• Query that is embedded (or nested) inside
another query
• Executed first
– Syntax:
• INSERT INTO tablename SELECT columnlist
FROM tablename;
Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel
39
7
Inserting Table Rows with a
Select Subquery (continued)
INSERT INTO p SELECT * from Product;
Employee(name, address, emp_num, salary)
Emp1(addr, sal, empno)
INSERT INTO Emp1
SELECT address, salary, emp_num
FROM Employee;
Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel
40
7
Inserting Table Rows with a
Select Subquery (continued)
• INSERT INTO Emp1
SELECT address, emp_num, salary
FROM Employee;
(The order of attributes matter in the above)
• INSERT INTO Emp1
SELECT * FROM Employee;
(The number of attributes are not the same!)
Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel
41
7
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 ] ;
Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel
42
7
Selecting Rows with
Conditional Restrictions (continued)
SELECT p_descript, p_indate, p_price, v_code
FROM Product
WHERE v_code = 21344;
Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel
43
7
Selecting Rows with
Conditional Restrictions (continued)
Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel
44
Selecting Rows with
Conditional Restrictions (continued)
Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel
7
45
7
Selecting Rows with
Conditional Restrictions (continued)
Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel
46
7
Selecting Rows with
Conditional Restrictions (continued)
SELECT p_descript, p_indate, p_price, v_code
FROM Product
WHERE v_code <>21344;
Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel
47
7
Selecting Rows with
Conditional Restrictions (continued)
Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel
48
7
Selecting Rows with
Conditional Restrictions (continued)
SELECT p_descript, p_qoh, p_min, p_price
FROM Product
WHERE p_price <= 10;
Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel
49
7
Selecting Rows with
Conditional Restrictions (continued)
Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel
50
7
Selecting Rows with
Conditional Restrictions (continued)
SELECT p_code, p_descript, p_qoh, p_min,
p_price
FROM Product
WHERE p_code <= ‘1558-QW1’;
Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel
51
7
Selecting Rows with
Conditional Restrictions (continued)
Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel
52
7
Selecting Rows with
Conditional Restrictions (continued)
SELECT p_descript, p_qoh, p_min, p_price,
p_indate
FROM Product
WHERE p_indate >= ’20-Jan-2006’;
Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel
53
7
Selecting Rows with
Conditional Restrictions (continued)
Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel
54
7
Selecting Rows with
Conditional Restrictions (continued)
SELECT p_descript, p_qoh, p_price,
p_qoh*p_price
FROM Product;
Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel
55
7
Selecting Rows with
Conditional Restrictions (continued)
Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel
56
7
Selecting Rows with
Conditional Restrictions (continued)
SELECT p_descript, p_qoh, p_price,
p_qoh*p_price AS totalvalue
FROM Product;
Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel
57
7
Selecting Rows with
Conditional Restrictions (continued)
Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel
58
7
Arithmetic Operators:
The Rule of Precedence
• Perform operations within parentheses
• Perform power operations
• Perform multiplications and divisions
• Perform additions and subtractions
Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel
59
7
Arithmetic Operators:
The Rule of Precedence (continued)
Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel
60
7
Logical Operators:
AND, OR, and NOT
SELECT p_descript, p_indate, p_price, v_code
FROM Product
WHERE v_code = 21344 OR v_code = 24288;
Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel
61
7
Logical Operators:
AND, OR, and NOT (continued)
Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel
62
7
Logical Operators:
AND, OR, and NOT (continued)
SELECT p_descript, p_indate, p_price, v_code
FROM Product
WHERE p_price < 50 AND p_indate > ’15-Jan2006’;
Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel
63
7
Logical Operators:
AND, OR, and NOT (continued)
Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel
64
7
Logical Operators:
AND, OR, and NOT (continued)
SELECT p_descript, p_indate, p_price, v_code
FROM product
WHERE (p_price < 50 AND p_indate > ’15-Jan2006’) OR v_code = 24288;
Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel
65
7
Logical Operators:
AND, OR, and NOT (continued)
Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel
66
7
Special Operators
• BETWEEN
– Used to check whether attribute value is within
a range
• IS NULL
– Used to check whether attribute value is null
• LIKE
– Used to check whether attribute value
matches given string pattern
Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel
67
7
Special Operators (continued)
• IN
– Used to check whether attribute value
matches any value within a value list
• EXISTS
– Used to check if subquery returns any rows
Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel
68
7
Special Operators (continued)
• SELECT *
FROM Product
WHERE p_price BETWEEN 50.00 AND 100.00;
• SELECT *
FROM product
WHERE p_price > 50.00 AND p_price<100.00;
• SELECT p_code, p_descript, v_code
FROM Product
WHERE v_code IS NULL;
Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel
69
7
Special Operators (continued)
• The LIKE special operator is used in conjunction with
wildcards to find patterns within string attributes.
– % means any and all following characters are eligible.
For example,
‘J%’ includes Johnson, Jones, July, etc.
‘Jo%’ includes Johnson, Jones, etc.
– _ means any one character may be substituted for the
underscore. For example,
‘_o_es’ includes Jones, Cones, Cokes, totes, ets.
Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel
70
7
Special Operators (continued)
• SELECT v_name, v_contact, v_areacode
FROM Vendor
WHERE v_contact LIKE ‘Smith%’;
• SELECT v_name, v_contact, v_areacode
FROM Vendor
WHERE UPPER(v_contact) LIKE ‘SMITH%’;
• SELECT v_name, v_contact, v_areacode
FROM Vendor
WHERE v_contact NOT LIKE ‘Smith%’;
Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel
71
7
Special Operators (continued)
•
SELECT *
FROM Product
WHERE v_code IN (21344, 24288);
•
SELECT v_code, v_name
FROM Vendor
WHERE v_code IN (SELECT v_code FROM Product);
•
SELECT *
FROM Vendor
WHERE v_code EXIST (SELECT *
FROM Product
WHERE p_qoh <= p_min);
Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel
72
7
Advanced Data Definition Commands
• All changes in table structure are made by
using ALTER command
– Followed by keyword that produces specific
change
– Following three options are available:
• ADD
• MODIFY
• DROP
Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel
73
7
Changing a Column’s Data Type
• ALTER can be used to change data type
• Some RDBMSs (such as Oracle) do not
permit changes to data types unless column
to be changed is empty
Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel
74
7
Changing a Column’s Data
Characteristics
• Use ALTER to change data characteristics
• If column to be changed already contains
data, changes in column’s characteristics are
permitted if those changes do not alter the
data type
Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel
75
7
Adding a Column
• Use ALTER to add/modify column
ALTER TABLE Customer
ADD address VARCHAR2(20);
ALTER TABLE Customer
MODIFY balance NUMBER(8,2);
Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel
76
7
Dropping a Column
• Use ALTER to drop column
– Some RDBMSs impose restrictions on the
deletion of an attribute
ALTER TABLE Product
DROP CONSTRAINT v_code_fk;
Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel
77
7
Dropping a Column (continued)
• UPDATE Product
SET p_salecode = ‘2’
WHERE p_code = ‘1546-QQ2’;
• UPDATE Product
SET p_salecode = ‘2’
WHERE p_code = ‘1546-QQ2’ OR p_code = ‘2232/QTY’;
• UPDATE Product
SET p_salecode = ‘2’
WHERE p_indate < ’25-Dec-2005’;
Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel
78
7
Advanced Data Updates
Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel
79
7
Copying Parts of Tables
• SQL permits copying contents of selected
table columns so that the data need not be
reentered manually into newly created
table(s)
• First create the PART table structure
• Next add rows to new PART table using
PRODUCT table rows
Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel
80
7
Copying Parts of Tables (continued)
• CREATE TABLE Part AS
SELECT p_code AS part_code, p_descript AS
part_descript, p_price AS part_price, v_code
FROM Product;
Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel
81
7
Copying Parts of Tables (continued)
Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel
82
7
Adding Primary and Foreign Key
Designations
• When table is copied, integrity rules do not
copy, so primary and foreign keys need to be
manually defined on new table
• User ALTER TABLE command
– Syntax:
• ALTER TABLE tablename ADD
PRIMARY KEY(fieldname);
• For foreign key, use FOREIGN KEY in place of
PRIMARY KEY
Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel
83
7
Deleting a Table from the Database
• DROP
– Deletes table from database
– Syntax:
• DROP TABLE tablename;
Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel
84
7
Advanced Select Queries
• SQL provides useful functions that can:
– Count
– Find minimum and maximum values
– Calculate averages
• SQL allows user to limit queries to only those
entries having no duplicates or entries whose
duplicates may be grouped
Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel
85
7
Ordering a Listing (continued)
• SELECT p_code, p_descript, p_indate, p_price
FROM Product
ORDER BY p_price;
• SELECT p_code, p_descript, p_indate, p_price
FROM Product
ORDER BY p_price DESC;
• SELECT emp_lname, emp_fname, emp_initial, emp_areacode,
emp_phone
FROM Employee
ORDER BY emp_lname, emp_fname, emp_initial;
Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel
86
7
Ordering a Listing
Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel
87
7
Ordering a Listing (continued)
Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel
88
7
Ordering a Listing (continued)
• SELECT p_descript, v_code, p_indate,
p_price
FROM
Product
WHERE p_indate < ’21-Jan-2006’ AND
p_price <= 50.00
ORDER BY v_code, p_price DESC;
Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel
89
7
Ordering a Listing (continued)
Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel
90
7
Listing Unique Values (continued)
• SELECT DISTINCT v_code
FROM Product;
Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel
91
7
Listing Unique Values
Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel
92
7
Aggregate Functions
Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel
93
7
Aggregate Functions (continued)
Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel
94
7
Aggregate Functions (continued)
Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel
95
7
Aggregate Functions (continued)
Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel
96
7
Aggregate Functions (continued)
Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel
97
7
Grouping Data
Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel
98
7
Grouping Data (continued)
Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel
99
7
Grouping Data (continued)
Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel
100
7
Virtual Tables: Creating a View
• View is virtual table based on SELECT query
– Can contain columns, computed columns,
aliases, and aggregate functions from one or
more tables
• Base tables are tables on which view is
based
• Create view by using CREATE VIEW
command
Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel
101
7
Virtual Tables: Creating a View
(continued)
Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel
102
7
Joining Database Tables
• Ability to combine (join) tables on common
attributes is most important distinction
between relational database and other
databases
• Join is performed when data are retrieved
from more than one table at a time
• Join is generally composed of an equality
comparison between foreign key and primary
key of related tables
Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel
103
7
Joining Database Tables (continued)
Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel
104
7
Joining Database Tables (continued)
• SLECT p_descript, p_price, v_name,
v_contact, v_areacode, v_phone
FROM Product, Vendor
WHERE product.v_code = vendor.v_code;
Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel
105
7
Joining Database Tables (continued)
Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel
106
7
Joining Database Tables (continued)
• SLECT 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-2006’;
Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel
107
7
Joining Database Tables (continued)
Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel
108
7
Joining Tables with an Alias
• 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
Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel
109
7
Recursive Joins
Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel
110
7
Recursive Joins (continued)
• SELECT E.emp_mgr, E.emp_lname,
M.emp_num, E.emp_lname
FROM Emp E, Emp M
WHERE E.emp_mgr = M.emp_num
ORDER BY E.emp_mgr;
Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel
111
7
Recursive Joins (continued)
Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel
112
7
Outer Joins
• SELECT p_code, V.v_code, v_name
FROM product P, vendor V
WHERE V.v_code = P.v_code(+);
• SELECT p_code, V.v_code, v_name
FROM product P, vendor V
WHERE P.v_code = V.v_code(+);
Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel
113
7
Left Outer Joins
Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel
114
7
Right Outer Joins (continued)
Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel
115
7
Grant and Revoke Privileges on Tables
• Security is an issue in the access of a table
from a foreign account.
• The creator of the table has no restriction on
access and may or may not pass along
manipulation privileges to others.
GRANT {priv | ALL}
ON table-name
TO {specific user | PUBLIC};
Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel
116
7
Grant and Revoke Privileges on Tables
GRANT SELECT, INSERT
ON Customer
TO fred, mary, joe;
GRANT ALL
GRANT SELECT
ON Customer
ON Customer
TO fred;
TO PUBLIC;
Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel
117
7
Grant and Revoke Privileges on Tables
SELECT *
FROM mary.Customer;
REVOKE SELECT
ON Customer
FROM PUBLIC;
Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel
118
7
Exercises
1. Student(stno, sname)
Grade_report(student_number,
course_no,grade)
(1) List the names of students who have earned
A’s or B’s in any course?
(2)List the names and grades of all students
Database Systems: Design, Implementation, & Management, 7 Edition, Rob & Coronel
who
have earned A’s or B’s in any course?
th
119
7
Exercises
Course(course_number, credit_hours,
course_name, offering_dept)
Department(dept_code, dept_name)
Find the names of all departments that offer a
course with “INTRO” in the title)
Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel
120
7
Exercises
Student(stno, sname, major, class)
Grade_report(student_number, section_id,
grade)
Section(section_id, course_num, semester,
year, instructor, bldg, room)
List the student name, student major code, and
section identifier of students who earned C’s
in courses taught by Professor Johns
(JOHNS)?
121
Database Systems: Design, Implementation, & Management, 7 Edition, Rob & Coronel
th
7
Exercises
Room(room_id, capacity)
List all room have a below-average capacity?
Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel
122
7
Exercises
SAILORS(S_ID, S_NAME, S_RATING, S_AGE)
BOATS(B_ID, B_NAME, B_COLOR)
RESERVES(S_ID, B_ID, R_DAY)
A. Find all sailors with a rating above 7.
B. Find the name of sailors who have reserved a red or
a green boat.
C. Find the name of sailors who are older than the
oldest sailors with a rating of 10.
Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel
123
7
Nulls Revisited
Name
Salary
Joe
1000
Sam
2000
Bill
3000
Dave
SELECT count(*), avg(salary), sum(salary),
max(salary), min(salary)
Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel
FROM
124
7
Nulls Revisited
Name
Salary
Joe
1000
Sam
2000
Bill
3000
Dave
SELECT count(*), avg(salary), sum(salary),
max(salary), min(salary)
Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel
FROM Sal;
125
7
Nulls Revisited
count(*) avg
4
2000
sum
min
max
6000
1000
3000
Select avg(NVL(salary, 0))
from Sal;
AVG(NVL(SALARY, 0))
Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel
1500
126
7
Delete and Referential Constraint
• On Delete Restrict
– Default referential integrity delete option, which is not
explicitly allowed in Oracle.
• On Delete Cascade (dangerous!)
Create table employee
( empno number (3) primary key,
empname varchar2(20),
dept number(3) references
department(deptno) on delete cascade);
Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel
127
7
Delete and Referential Constraint
• On Delete Set NULL
Create table Employee
( empno number (3) primary key,
empname varchar2(20),
dept number(3) references
Department(deptno) on delete set
NULL);
Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel
128
7
Summary
• SQL commands can be divided into two
overall categories:
– Data definition language commands
– Data manipulation language commands
• The ANSI standard data types are supported
by all RDBMS vendors in different ways
• Basic data definition commands allow you to
create tables, indexes, and views
Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel
129
7
Summary (continued)
• DML commands allow you to add, modify, and delete
rows from tables
• The basic DML commands are SELECT, INSERT,
UPDATE, DELETE, COMMIT, and ROLLBACK
• INSERT command is used to add new rows to tables
• SELECT statement is main data retrieval command
in SQL
Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel
130
7
Summary (continued)
• Many SQL constraints can be used with
columns
• The column list represents one or more
column names separated by commas
• WHERE clause can be used with SELECT,
UPDATE, and DELETE statements to restrict
rows affected by the DDL command
Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel
131
7
Summary (continued)
• Aggregate functions
– Special functions that perform arithmetic
computations over a set of rows
• ORDER BY clause
– Used to sort output of SELECT statement
– Can sort by one or more columns and use
either an ascending or descending order
• Join output of multiple tables with SELECT
statement
Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel
132
7
Summary (continued)
• Natural join uses join condition to match only
rows with equal values in specified columns
• Right outer join and left outer join used to
select rows that have no matching values in
other related table
Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel
133