Transcript Chapter 9:

Chapter 9:
Structured Query
Language (SQL)
Benefits of a Standardized
Relational Language
 Reduced
training costs
 Productivity
 Application
portability
 Application
longevity
 Reduced
dependence on a single vendor
 Cross-system
communication
Disadvantages of a standardized
relational language
 Stifle
creativity and innovation
 never
enough to meet all needs
 offspring
 difficult
 Using
of compromises
to change
special features may result in loss of
portability
Terminology
 Data
Definition Language (DDL):
• Commands that define a database, including creating,
altering, and dropping tables and establishing
constraints.
 Data
Manipulation Language (DML)
• Commands that maintain and query a database.
 Data
Control Language (DCL)
• Commands that control a database, including
administering privileges and committing data.
Common SQL Commands
 Data
Definition Language (DDL):
 Create
 Alter
 Data
Manipulation Language (DML):
 Select
 Insert
 Data
 Drop
 Rename
 Update
 Delete
Control Language (DCL):
 Grant
 Revoke
Querying a Database with SQL
SQL statement
is entered
SQL> SELECT loc
2 FROM dept;
Statement is sent to
database
Database
Data is displayed
LOC
-----------NEW YORK
DALLAS
CHICAGO
BOSTON
Writing SQL Statements
 SQL statements
are not case sensitive
(but criteria within quotation marks are)
 SQL statements
 Clauses
can be on one or more lines
are usually placed on separate lines
 Keywords
cannot be split across lines
 Tabs
and spaces are allowed to enhance
readability
 Each
SQL statement (not line) ends with a
semicolon (;)
Data Definition Language (DDL)
Creating tables:
 Identify
appropriate datatypes
 Identify
columns that should accept null values
 Identify
columns that need to be unique
 Identify
all PK/FK mates
 Determine
any default values to be inserted
 Identify
columns which need a domain
specification
 Create
the table
DDL example in SQL
CREATE TABLE ORDER
(Order_Id
char
not null,
Order_Date
date
default sysdate,
Customer_Id char
not null,
Constraint Order_pk primary key (order_Id) ,
Constraint Order_fk foreign key (Customer_Id)
references Customer(Customer_Id));
Cust_ID
Order_ID
Customer
Order
Order_Date
Data Manipulation Language
(DML)
 This
is the major focus of our coverage of
SQL
 Most
useful for querying database based on
specific criteria
 Includes:
insert, delete, update, and select
DML - Inserting
 Useful
for populating a database
 Syntax
is:
INSERT INTO Product(Product_ID, Product
_Name, Unit_Price, On_Hand)
VALUES (1, ‘End Table’, 175, 8);
 Or
INSERT INTO Ca_Customer
SELECT * FROM Customer
WHERE State=‘CA’;
DML - Deleting
DELETE FROM Customer
WHERE State=‘HI’;
Or
DELETE FROM Customer;
DML - Updating
UPDATE Product
SET Unit_Price = 775
WHERE Product_ID = 7;
DML - SELECT
Statement Syntax:
SELECT [DISTINCT] column_list
FROM table_list
[WHERE conditional expression]
[GROUP BY column_list]
[HAVING conditional expression]
[ORDER BY column_list] ;
SELECT
statement
processing order
The Basic SELECT Statement
SELECT column1, column2, column3,...
FROM table;

SELECT identifies what columns

FROM identifies which table
Example SELECT Statement
SQL> SELECT deptno, loc
2 FROM dept;
DEPTNO
--------10
20
30
40
LOC
------------NEW YORK
DALLAS
CHICAGO
BOSTON
Selecting All Columns
SQL> SELECT *
2 FROM dept;
DEPTNO
--------10
20
30
40
DNAME
-------------ACCOUNTING
RESEARCH
SALES
OPERATIONS
LOC
––––––––––––NEW YORK
DALLAS
CHICAGO
BOSTON
Specifying Output Headings
SQL> SELECT loc AS location
2 FROM dept;
LOCATION
------------NEW YORK
DALLAS
CHICAGO
BOSTON
Note Upper-Case Heading
Specifying Output Headings
Double quotes
SQL> SELECT loc AS “Location”
2 FROM dept;
Location
------------NEW YORK
DALLAS
CHICAGO
BOSTON
Note Mixed-Case Heading
Duplicate Output
SQL> SELECT job
2 FROM emp;
JOB
--------PRESIDENT
MANAGER
MANAGER
MANAGER
SALESMAN
SALESMAN
...
A total of 14 records display
Suppressing Duplicate Output
SQL> SELECT DISTINCT job
2 FROM emp;
JOB
--------ANALYST
CLERK
MANAGER
PRESIDENT
SALESMAN
Each unique job is listed only once
Limiting Rows with WHERE
SQL> SELECT ename, job, sal
2 FROM emp
3 WHERE job = ‘CLERK’;
ENAME
---------JAMES
SMITH
ADAMS
MILLER
JOB
SAL
--------- --------CLERK
950
CLERK
800
CLERK
1100
CLERK
1300
Case sensitive;
single quotes
WHERE Clause Criteria
 Text
and dates (sometimes) are enclosed in single
quotes
 Numbers
are not enclosed in quotes
 Text
values are case sensitive
 Date
values are format sensitive
 Oracle’s
default date format is DD-MON-YY,
Access’ default is #MM/DD/YY#
SQL> SELECT ename, hiredate
2 FROM emp
3 WHERE hiredate >= ’01-Jan-82';
SQL Comparison Operators
OPERATOR
=
>
>=
<
<=
<> or !=
MEANING
Equal to
Greater than
Greater than or equal to
Less than
Less than or equal to
Not equal to
SQL Comparison Operators
OPERATOR
MEANING
BETWEEN . . . AND . . . Is between the values
specified (inclusive)
IN(list)
Is in a list of values
specified
LIKE
Matches a pattern
(may include wildcards)
IS NULL
Is a null value
BETWEEN Operator Example
SQL> SELECT ename, sal
2 FROM emp
3 WHERE sal BETWEEN 1000 AND 1500;
ENAME
SAL
---------- --------MARTIN
1250
TURNER
1500
WARD
1250
ADAMS
1100
MILLER
1300
IN Operator Example
SQL> SELECT ename, job
2 FROM emp
3 WHERE job IN(‘PRESIDENT’,’MANAGER’);
ENAME
---------KING
BLAKE
CLARK
JONES
JOB
--------PRESIDENT
MANAGER
MANAGER
MANAGER
LIKE Operator Example
SQL> SELECT ename
2 FROM emp
3 WHERE ename LIKE ‘J%’;
ENAME
---------JONES
JAMES
The % is a wildcard character
that stands for zero to many
characters. The underscore
character (_) can be used to
stand for exactly one character.
[ LIKE is not the same as = ]
IS NULL Operator Example
SQL> SELECT ename, mgr
2 FROM emp
3 WHERE mgr IS NULL;
ENAME
MGR
---------- --------KING
Boolean (Logical) Operators
OPERATOR
AND
OR
NOT
MEANING
Joins two or more conditions
and returns results only
when all conditions are true
Joins two or more conditions
and returns results when
any condition is true
Negates an expression
AND Operator Example
SQL>
2
3
4
SELECT ename, job, sal
FROM emp
WHERE job = ‘CLERK’
AND sal > 1000;
ENAME
---------ADAMS
MILLER
JOB
SAL
--------- --------CLERK
1100
CLERK
1300
Both conditions
must be true
OR Operator Example
SQL>
2
3
4
SELECT ename
FROM emp
WHERE ename LIKE ‘J%’
OR ename LIKE ‘M%’;
ENAME
---------JONES
MARTIN
JAMES
MILLER
At least one
condition
must be true
NOT Operator Example
SQL> SELECT DISTINCT job
2 FROM emp
3 WHERE job NOT LIKE ‘C%’;
JOB
--------ANALYST
MANAGER
PRESIDENT
SALESMAN
Arithmetic Operators
OPERATOR
+
–
*
/
MEANING
Addition
Subtraction
Multiplication
Division
Arithmetic Expression Example
SQL> SELECT ename, sal, sal*12 AS annual
2 FROM emp
3 WHERE job = ‘CLERK’;
ENAME
SAL
ANNUAL
---------- --------- --------JAMES
950
11400
SMITH
800
9600
ADAMS
1100
13200
MILLER
1300
15600
Sorting Rows with ORDER BY
SQL> SELECT ename, job, sal
2 FROM emp
3 ORDER BY ename;
ENAME
---------ADAMS
ALLEN
BLAKE
CLARK
FORD
...
JOB
SAL
--------- --------CLERK
1100
SALESMAN
1600
MANAGER
2850
MANAGER
2450
ANALYST
3000
...
...
Sorting by Multiple Fields
SQL> SELECT ename, job, sal
2 FROM emp
3 ORDER BY job, ename;
ENAME
---------FORD
SCOTT
ADAMS
JAMES
MILLER
...
JOB
SAL
--------- --------ANALYST
3000
ANALYST
3000
CLERK
1100
CLERK
950
CLERK
1300
...
...
The order of the
list determines
the precedence
of the sort order
Sorting in Descending Order
SQL> SELECT ename, job, sal
2 FROM emp
3 ORDER BY sal DESC;
ENAME
---------KING
FORD
SCOTT
JONES
BLAKE
...
JOB
SAL
--------- --------PRESIDENT
5000
ANALYST
3000
ANALYST
3000
MANAGER
2975
MANAGER
2850
...
...
Aggregate Functions
FUNCTION
COUNT
MIN
MAX
SUM
AVG
MEANING
Number of occurrences
Minimum value
Maximum value
Sum of values
Average of values
Aggregate Function Example
SQL> SELECT AVG(sal) AS “SALARY AVG”
2 FROM emp
3 WHERE job = ‘SALESMAN’;
SALARY AVG
---------1400
Aggregate Function Example
SQL> SELECT COUNT(*)
2 FROM emp;
COUNT(*)
--------14
Note: COUNT(*)
returns the number
of rows in a table
while COUNT(field)
returns the number
of rows that are
nonnull for the
field counted
Subqueries
 Subqueries
are useful when a query is based
on unknown values (e.g., “Who has a salary
greater than Blake?” when Blake’s salary is
unknown)
 Subqueries
involve placing an inner query
(SELECT, FROM, WHERE) within a WHERE or
HAVING clause of another (outer) query
 The
inner query is a complete query that could
stand on its own and serves to provide values
for the search condition of the outer query
Subquery Example
The inner query
is enclosed in
parentheses;
indenting is
optional
SQL> SELECT ename
2 FROM emp
3 WHERE sal >
4
(SELECT sal
5
FROM emp
6
WHERE empno = 7698);
ENAME
---------KING
JONES
FORD
SCOTT
The inner query
determines the
salary of Blake
(empno = 7698)
and returns it to
the outer query
Aggregate Function Subquery
SQL> SELECT ename, job, sal
2 FROM emp
3 WHERE sal =
4
(SELECT MIN(sal)
5
FROM emp);
The inner query
ENAME
JOB
SAL
---------- --------- --------SMITH
CLERK
800
determines the
minimum salary
of all employees
and returns it to
the outer query
Processing Multiple Tables
 When
relationships exist between tables,
the tables can be linked together in queries
 Relationships
between tables are
established by setting up primary key to
foreign key relationships between columns
that are common to both tables
 We
link related tables together in SQL
queries by using either joins or subqueries
Joins
is defined as: “A relational operation that
causes two tables with a common domain to be
combined into a single table”
 A join
 A join
is specified in SQL by using a WHERE
clause to match values for the common field
between the two tables (if you were joining three
tables, you would need two joining WHERE
clauses)
 Each
row in the resultant table (i.e., the join
product) contains data from rows in the input
tables where values for the common field match
Data from Multiple Tables
EMP
EMPNO
-----7839
7698
7782
7566
...
DEPT
ENAME
----KING
BLAKE
CLARK
JONES
...
... DEPTNO
... -----...
10
...
30
...
10
...
20
...
...
DEPTNO
-----10
20
30
40
DNAME
---------ACCOUNTING
RESEARCH
SALES
OPERATIONS
EMPNO DEPTNO LOC
----- ------- -------7839
10 NEW YORK
7698
30 CHICAGO
7782
10 NEW YORK
7566
20 DALLAS
7654
30 CHICAGO
7499
30 CHICAGO
...
... ...
LOC
-------NEW YORK
DALLAS
CHICAGO
BOSTON
DEPTNO is the
common field
joining the
EMP and
DEPT tables
Creating a Join in SQL
SELECT table1.field, table2.field, ...
FROM
table1, table2
WHERE table1.fieldX = table2.fieldX;
 Write
the join condition in the WHERE clause
 Prefix
the column name with the table name when
the same column name appears in more than one
table (to avoid ambiguity)
SELECT empno, emp.deptno, loc
FROM
emp, dept
WHERE emp.deptno = dept.deptno;
This is the
query that is
shown on the
previous slide
3 Table Join Example
SQL>
2
3
4
SELECT ord.orderdate, item.qty, product.descrip
FROM ord, item, product
Note: Table name
WHERE ord.ordid = item.ordid
prefixes are used
AND item.prodid = product.prodid;
here for all
fields for clarity
ORDERDATE
QTY DESCRIP
--------- --------- -----------------------------07-JAN-87
1 ACE TENNIS NET
11-JAN-87
1 ACE TENNIS RACKET II
15-JAN-87
100 ACE TENNIS RACKET I
01-MAY-86
1 SB ENERGY BAR-6 PACK
05-JUN-86
20 ACE TENNIS BALLS-3 PACK
15-JUN-86
3 ACE TENNIS NET
...
... ...
Join Types
“A join in which the joining
condition is based on equality between values in
the common columns. Common columns
appear (redundantly) in the result table.”
 Equi-join:
join: “Same as equi-join except one of
the duplicate columns is eliminated in the result
table.”
 Natural
join: “A join in which rows that do not
have matching values in common columns are
nevertheless included in the result table.”
 Outer
 Self
join: A join that represents a recursive
unary relationship of a table with itself.
Equi-join Example
Note table name prefixes used to
specify names for common fields
Note joining
WHERE clause
SQL> SELECT ename, emp.deptno, dept.deptno, dname
2 FROM emp, dept
3 WHERE emp.deptno = dept.deptno; Note duplicate
columns
ENAME
DEPTNO
DEPTNO DNAME
---------- --------- --------- -------------KING
10
10 ACCOUNTING
BLAKE
30
30 SALES
CLARK
10
10 ACCOUNTING
JONES
20
20 RESEARCH
MARTIN
30
30 SALES
ALLEN
30
30 SALES
...
...
... ...
Natural Join Example
Note elimination
of duplicate field
SQL> SELECT ename, emp.deptno, dname
2 FROM emp, dept
3 WHERE emp.deptno = dept.deptno;
Note elimination
of duplicate column
ENAME
DEPTNO DNAME
---------- --------- -------------KING
10 ACCOUNTING
BLAKE
30 SALES
CLARK
10 ACCOUNTING
JONES
20 RESEARCH
MARTIN
30 SALES
ALLEN
30 SALES
...
... ...
Outer Join Example
First, let’s run this
as a natural join
SQL> SELECT ename, emp.deptno, dname
2 FROM emp, dept
3 WHERE emp.deptno = dept.deptno;
A total of 14 rows
ENAME
DEPTNO DNAME
are returned
---------- --------- -------------KING
10 ACCOUNTING
BLAKE
30 SALES
CLARK
10 ACCOUNTING
...
... ...
MILLER
10 ACCOUNTING
Outer Join Example
Now, we’ll run this
as an outer join;
note the (+) symbol
SQL> SELECT ename, emp.deptno, dname
2 FROM emp, dept
3 WHERE emp.deptno (+) = dept.deptno;
ENAME
DEPTNO
---------- --------KING Included is this 10
BLAKE department 30
CLARK that has no
10
employees ...
...
MILLER
10
A total of 15 rows
DNAME
are returned
-------------ACCOUNTING
SALES
ACCOUNTING
...
ACCOUNTING
OPERATIONS
Using Outer Joins
 The
outer join operator (+) can appear on
only one side of the equal sign in the
WHERE clause; it is placed on the side
that is deficient in information
 A condition
involving an outer join cannot
be linked to another condition by the OR
operator
 A condition
involving an outer join cannot
use the IN operator
Group by/Having
 The
“group by” function groups rows
where the values in those rows are the
same for one or more columns
 The
“having” function can only be used
in conjunction with the “group by”
function. It acts as a secondary where
clause.
Categorizing with GROUP BY
SQL> SELECT job, AVG(sal)
2 FROM emp
3 GROUP BY job;
JOB
AVG(SAL)
--------- --------ANALYST
3000
CLERK
1037.5
MANAGER
2758.3333
PRESIDENT
5000
SALESMAN
1400
Note: Any field or
expression in the
SELECT list that
is not an aggregate
function must be in
the GROUP BY clause
Categorizing with GROUP BY
SQL> SELECT deptno, COUNT(deptno)
2 FROM emp
3 GROUP BY deptno;
DEPTNO COUNT(DEPTNO)
--------- ------------10
3
20
5
30
6
Note: The GROUP BY
field does not
have to be in
the SELECT list
Limiting GROUP BY with
HAVING
SQL>
2
3
4
SELECT deptno, COUNT(deptno)
FROM emp
GROUP BY deptno
HAVING COUNT(deptno) >= 5;
DEPTNO COUNT(DEPTNO)
--------- ------------20
5
30
6
Advanced topics….
Self Joins
 Used
to join a table to itself
 Useful
to query a table
involved in a recursive
unary relationship with itself
 Aliases
are used in order to be able to
distinguish the side of the relationship the table
being referenced is on
• e.g., we could reference the emp table on the
employee side of a “manages” unary relationship
as “e” and the same table on the manager side of
the relationship as “m”
Preparation for Self Join
Identify the
common fields
SQL> SELECT empno, ename, mgr
2 FROM emp;
EMPNO
--------7839
7698
7782
7566
7654
7499
...
ENAME
MGR
---------- --------KING
BLAKE
7839
CLARK
7839
JONES
7839
MARTIN
7698
ALLEN
7698
...
...
Self Join Example
Aliases
SQL> SELECT e.ename, m.ename AS manager
2 FROM emp e, emp m
3 WHERE e.mgr = m.empno (+);
ENAME
---------KING
BLAKE
CLARK
JONES
MARTIN
...
MANAGER
---------KING
KING
KING
BLAKE
...
Note: Aliases can be
useful in other
situations besides
self joins. Any time
you wish to abbreviate
a table name you
can use them
Correlated sub-queries
 A query
(outer query) with a nested query
(inner query) in which processing the inner
query depends on data from the outer
query.
 The
inner query is processed for each outer
row.
 See
number 10 in practice set 2
Exists/Not exists
 Existential
qualifier: used in a where clause to test
whether a table contains at least one row
satisfying a specific condition. Returns a true or
false.
 List
all courses which were taken by no students
in the most recent semester
Select name from course
where not exists (Select * from Schedule
where course.course_ID=schedule.course_ID);
Universal Qualifier
 SQL does
not provide direct support for a
universal qualifier, but it can be
accomplished…
 List
all the students who have taken every
MIS class.
Select name from student
where not exists (Select * from course
where not exists (Select * from schedule
where student.student_ID=schedule.student_ID and
course.course_ID=schedule.course_ID));
Referencing a Table by Owner
 If
you are not the owner of a table (i.e., if
you did not create the table), you must
append the table owner’s User ID to the
table name when you reference the table
 Example:
SQL> SELECT ename, mgr
2 FROM demo.emp;
 Synonyms
The User ID of the owner
of the emp table is demo
can be created by the DBA to
reduce the necessity of this referencing
A Final Example
SQL> SELECT ename, emp.deptno, loc as city
2 FROM demo.emp, demo.dept
3 WHERE emp.deptno = dept.deptno
4 AND emp.deptno <> 10
How many
5 ORDER BY city, ename;
different
ENAME
DEPTNO CITY
---------- ---------- ------------ALLEN
30 CHICAGO
BLAKE
30 CHICAGO
...
... ...
ADAMS
20 DALLAS
...
... ...
SMITH
20 DALLAS
skills are
shown here?
Do you
understand
the result of
this query?
(11 records)