SQL: Part 2

1
SQL Part 2

Original materials supplied by the Oracle
Academic Initiative (OAI). Edited for classroom
use by Professor Laku Chidambaram. Not for
commercial use. Do not redistribute.
2
OUTLINE

• Basic SELECT statements
• Restricting and Sorting Data
• Using JOINs

3
Basic SELECT Statements
4
Capabilities of SELECT Statements
Projection
Selection
Table 1
Table 1
Join
Table 1
Table 2
5
Basic SELECT Statement
SELECT DISTINCT , column alias,... FROM tab
le

• SELECT identifies what columns.
• FROM identifies which table.

6
Selecting All Columns
SQLgt SELECT 2 FROM dept


DEPTNO DNAME LOC ---------
-------------- ------------- 10 ACCOUNTING
NEW YORK 20 RESEARCH DALLAS
30 SALES CHICAGO 40 OPERATIONS
BOSTON
7
Selecting Specific Columns
SQLgt SELECT deptno, loc 2 FROM dept


DEPTNO LOC --------- ------------- 10
NEW YORK 20 DALLAS 30 CHICAGO
40 BOSTON
8
Operator Precedence

• Multiplication and division take priority over
  addition and subtraction.
• Operators of the same priority are evaluated from
  left to right.
• Parentheses are used to force prioritized
  evaluation and to clarify statements.

9
Operator Precedence
SQLgt SELECT ename, sal, 12sal100 2 FROM emp


ENAME SAL 12SAL100 ----------
--------- ---------- KING 5000
60100 BLAKE 2850 34300 CLARK
2450 29500 JONES 2975
35800 MARTIN 1250 15100 ALLEN
1600 19300 ... 14 rows selected.
10
Using Parentheses
SQLgt SELECT ename, sal, 12(sal100) 2 FROM
emp


ENAME SAL 12(SAL100) ----------
--------- ----------- KING 5000
61200 BLAKE 2850 35400 CLARK
2450 30600 JONES 2975
36900 MARTIN 1250 16200 ... 14
rows selected.
11
Defining a Null Value

• A null is a value that is unavailable,
  unassigned, unknown, or inapplicable.
• A null is not the same as zero or a blank space.

SQLgt SELECT ename, job, comm 2 FROM emp


ENAME JOB COMM ----------
--------- --------- KING PRESIDENT BLAKE
MANAGER ... TURNER SALESMAN
0 ... 14 rows selected.
12
Null Values in Arithmetic Expressions

• Arithmetic expressions containing a null value
  evaluate to null.

SQLgt select ename, 12salcomm 2 from emp
3 WHERE ename'KING'

ENAME 12SALCOMM ----------
----------- KING

13
Defining a Column Alias

• Renames a column heading
• Is useful with calculations
• Immediately follows column name optional AS
  keyword between column name and alias
• Requires double quotation marks if it contains
  spaces or special characters or is case sensitive

14
Using Column Aliases
SQLgt SELECT ename AS name, sal salary 2 FROM
emp

NAME SALARY -------------
---------...


SQLgt SELECT ename "Name", 2 sal12
"Annual Salary" 3 FROM emp


15
Literal Character Strings

• A literal is a character, expression, or number
  included in the SELECT list.
• Date and character literal values must be
  enclosed within single quotation marks.
• Each character string is output once for each row
  returned.

16
Duplicate Rows

• The default display of queries is all rows,
  including duplicate rows.

SQLgt SELECT deptno 2 FROM emp

DEPTNO --------- 10 30
10 20 ... 14 rows selected.
17
Eliminating Duplicate Rows
Eliminate duplicate rows by using the DISTINCT
keyword in the SELECT clause.
SQLgt SELECT DISTINCT deptno 2 FROM emp


DEPTNO --------- 10 20
30
18
Restricting and Sorting Data

19
Limiting Rows Using a Selection
20
Limiting Rows Selected

• Restrict the rows returned by using the WHERE
  clause.
• The WHERE clause follows the FROM clause.

SELECT DISTINCT column alias, ... FROM
table WHERE condition(s)

21
Using the WHERE Clause
SQLgt SELECT ename, job, deptno 2 FROM emp
3 WHERE job'CLERK'

ENAME JOB DEPTNO ----------
--------- --------- JAMES CLERK
30 SMITH CLERK 20 ADAMS
CLERK 20 MILLER CLERK 10

22
Character Strings and Dates

• Character strings and date values are enclosed in
  single quotation marks.
• Character values are case sensitive and date
  values are format sensitive.
• The default date format is DD-MON-YY.

SQLgt SELECT ename, job, deptno 2 FROM emp 3
WHERE ename 'JAMES'
23
Comparison Operators
Operator gt gt lt lt ltgt
Meaning Equal to Greater than Greater than or
equal to Less than Less than or equal to Not
equal to
24
Using the Comparison Operators
SQLgt SELECT ename, sal, comm 2 FROM emp 3
WHERE salltcomm

ENAME SAL COMM ----------
--------- --------- MARTIN 1250 1400

25
Other Comparison Operators
Operator BETWEEN...AND... IN(list) LIKE IS NULL
Meaning Between two values (inclusive) Match
any of a list of values Match a character
pattern Is a null value
26
Using the BETWEEN Operator

• Use the BETWEEN operator to display rows based on
  a range of values.

SQLgt 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

27
Using the IN Operator

• Use the IN operator to test for values in a list.

SQLgt SELECT empno, ename, sal, mgr 2 FROM
emp 3 WHERE mgr IN (7902, 7566, 7788)

EMPNO ENAME SAL
MGR --------- ---------- --------- ---------
7902 FORD 3000 7566 7369
SMITH 800 7902 7788 SCOTT
3000 7566 7876 ADAMS
1100 7788

28
Using the LIKE Operator

• Use the LIKE operator to perform wildcard
  searches of valid search string values.
• Search conditions can contain either literal
  characters or numbers.
• denotes zero or many characters.
• _ denotes one character.

SQLgt SELECT ename 2 FROM emp 3 WHERE ename
LIKE 'S'
29
Using the LIKE Operator

• You can combine pattern-matching characters.

SQLgt SELECT ename 2 FROM emp 3 WHERE ename
LIKE '_A'
ENAME ---------- MARTIN JAMES WARD

30
Using the IS NULL Operator

• Test for null values with the IS NULL operator.

SQLgt SELECT ename, mgr 2 FROM emp 3
WHERE mgr IS NULL

ENAME MGR ---------- --------- KING

31
Logical Operators
Operator ANDOR NOT
Meaning Returns TRUE if both component conditions
are TRUE Returns TRUE if either component
condition is TRUE Returns TRUE if the following
condition is FALSE
32
Using the AND Operator
AND requires both conditions to be TRUE.
SQLgt SELECT empno, ename, job, sal 2 FROM
emp 3 WHERE salgt1100 4 AND job'CLERK'

EMPNO ENAME JOB
SAL --------- ---------- --------- ---------
7876 ADAMS CLERK 1100 7934
MILLER CLERK 1300

33
Using the OR Operator
OR requires either condition to be TRUE.
SQLgt SELECT empno, ename, job, sal 2 FROM
emp 3 WHERE salgt1100 4 OR job'CLERK'


EMPNO ENAME JOB
SAL --------- ---------- --------- ---------
7839 KING PRESIDENT 5000 7698
BLAKE MANAGER 2850 7782 CLARK
MANAGER 2450 7566 JONES
MANAGER 2975 7654 MARTIN SALESMAN
1250 ... 7900 JAMES CLERK
950 ... 14 rows selected.
34
Using the NOT Operator
SQLgt SELECT ename, job 2 FROM emp 3 WHERE
job NOT IN ('CLERK','MANAGER','ANALYST')

ENAME JOB ---------- --------- KING
PRESIDENT MARTIN SALESMAN ALLEN
SALESMAN TURNER SALESMAN WARD SALESMAN

35
Rules of Precedence
Order Evaluated Operator 1 All comparison
operators 2 NOT 3 AND 4 OR

• Override rules of precedence by using parentheses.

36
ORDER BY Clause

• Sort rows with the ORDER BY clause
• ASC ascending order, default
• DESC descending order
• The ORDER BY clause comes last in the SELECT
  statement.

SQLgt SELECT ename, job, deptno, hiredate 2
FROM emp 3 ORDER BY hiredate

ENAME JOB DEPTNO HIREDATE ----------
--------- --------- --------- SMITH CLERK
20 17-DEC-80 ALLEN SALESMAN
30 20-FEB-81 ... 14 rows selected.

37
Sorting in Descending Order
SQLgt SELECT ename, job, deptno, hiredate 2
FROM emp 3 ORDER BY hiredate DESC

ENAME JOB DEPTNO HIREDATE ----------
--------- --------- --------- ADAMS CLERK
20 12-JAN-83 SCOTT ANALYST
20 09-DEC-82 MILLER CLERK 10
23-JAN-82 JAMES CLERK 30
03-DEC-81 FORD ANALYST 20
03-DEC-81 KING PRESIDENT 10
17-NOV-81 MARTIN SALESMAN 30
28-SEP-81 ... 14 rows selected.

38
Sorting by Column Alias
SQLgt SELECT empno, ename, sal12 annsal 2
FROM emp 3 ORDER BY annsal

EMPNO ENAME ANNSAL ---------
---------- --------- 7369 SMITH
9600 7900 JAMES 11400 7876
ADAMS 13200 7654 MARTIN
15000 7521 WARD 15000 7934
MILLER 15600 7844 TURNER
18000 ... 14 rows selected.

39
Sorting by Multiple Columns

• The order of the ORDER BY list is used to sort
  the results of the query.

SQLgt SELECT ename, deptno, sal 2 FROM emp
3 ORDER BY deptno, sal DESC

ENAME DEPTNO SAL ----------
--------- --------- KING 10
5000 CLARK 10 2450 MILLER
10 1300 FORD 20
3000 ... 14 rows selected.

• A column not in the SELECT list can be used to
  sort the query results.

40
Using JOINs
41
Obtaining Data from Multiple Tables
EMP
DEPT


EMPNO ENAME ... DEPTNO------ ----- ... ------
7839 KING ... 10 7698 BLAKE ... 30
... 7934 MILLER ... 10
DEPTNO DNAME LOC ------
---------- -------- 10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS 30 SALES CHICAGO
40 OPERATIONS BOSTON
EMPNO DEPTNO LOC ----- ------- -------- 7839
10 NEW YORK 7698 30 CHICAGO 7782
10 NEW YORK 7566 20 DALLAS 7654 30
CHICAGO 7499 30 CHICAGO ... 14 rows selected.
42
What Is a Join?

• Use a join to query data from more than one
  table.
• 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.

SELECT table1.column, table2.column FROM table1,
table2 WHERE table1.column1 table2.column2
43
Cartesian Product

• A Cartesian product is formed when
• A join condition is omitted
• A join condition is invalid
• All rows in the first table are joined to all
  rows in the second table
• To avoid a Cartesian product, always include a
  valid join condition in a WHERE clause.

44
Generating a Cartesian Product
EMP (14 rows)
DEPT (4 rows)
EMPNO ENAME ... DEPTNO------ ----- ... ------
7839 KING ... 10 7698 BLAKE ... 30
... 7934 MILLER ... 10
DEPTNO DNAME LOC ------
---------- -------- 10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS 30 SALES CHICAGO
40 OPERATIONS BOSTON
ENAME DNAME ------ ---------- KING ACCOUNTIN
G BLAKE ACCOUNTING ... KING RESEARCH BLAKE
RESEARCH ... 56 rows selected.
45
Types of Joins
Equijoin
Non-equijoin
Outer join
Self join
46
What Is an Equijoin?
EMP
DEPT
EMPNO ENAME DEPTNO ------ ------- -------
7839 KING 10 7698 BLAKE 30
7782 CLARK 10 7566 JONES 20
7654 MARTIN 30 7499 ALLEN 30
7844 TURNER 30 7900 JAMES 30
7521 WARD 30 7902 FORD 20
7369 SMITH 20 ... 14 rows selected.


DEPTNO DNAME LOC ------- ----------
-------- 10 ACCOUNTING NEW YORK 30
SALES CHICAGO 10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS 30 SALES
CHICAGO 30 SALES CHICAGO 30
SALES CHICAGO 30 SALES CHICAGO
30 SALES CHICAGO 20 RESEARCH DALLAS
20 RESEARCH DALLAS ... 14 rows selected.
47
Retrieving Records with Equijoins
SQLgt SELECT emp.empno, emp.ename, emp.deptno,
2 dept.deptno, dept.loc 3 FROM emp, dept
4 WHERE emp.deptnodept.deptno


EMPNO ENAME DEPTNO DEPTNO LOC ----- ------
------ ------ --------- 7839 KING 10 10
NEW YORK 7698 BLAKE 30 30 CHICAGO
7782 CLARK 10 10 NEW YORK 7566 JONES
20 20 DALLAS ... 14 rows selected.
48
Qualifying Ambiguous Column Names

• Use table prefixes to qualify column names that
  are in multiple tables.
• Improve performance by using table prefixes.
• Distinguish columns that have identical names but
  reside in different tables by using column
  aliases.

49
Additional Search ConditionsUsing the AND
Operator
EMP
DEPT


EMPNO ENAME DEPTNO ------ ------- -------
7839 KING 10 7698 BLAKE 30
7782 CLARK 10 7566 JONES 20
7654 MARTIN 30 7499 ALLEN 30
7844 TURNER 30 7900 JAMES 30
7521 WARD 30 7902 FORD 20
7369 SMITH 20 ... 14 rows selected.
DEPTNO DNAME LOC ------
--------- -------- 10 ACCOUNTING NEW YORK
30 SALES CHICAGO 10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS 30 SALES CHICAGO
30 SALES CHICAGO 30 SALES CHICAGO
30 SALES CHICAGO 30 SALES CHICAGO
20 RESEARCH DALLAS 20 RESEARCH DALLAS ... 14
rows selected.
50
Using Table Aliases

• Simplify queries by using table aliases.

SQLgt SELECT emp.empno, emp.ename, emp.deptno,
2 dept.deptno, dept.loc 3 FROM emp, dept
4 WHERE emp.deptnodept.deptno

SQLgt SELECT e.empno, e.ename, e.deptno, 2
d.deptno, d.loc 3 FROM emp e, dept d
4 WHERE e.deptnod.deptno

51
Joining More Than Two Tables
CUSTOMER
NAME CUSTID ----------- ------ JOCKSPORTS
100 TKB SPORT SHOP 101 VOLLYRITE 102 JUST
TENNIS 103 KT SPORTS 105 SHAPE UP
106 WOMENS SPORTS 107 ... ... 9 rows selected.
52
Non-Equijoins
EMP
SALGRADE


EMPNO ENAME SAL ------ ------- ------
7839 KING 5000 7698 BLAKE 2850 7782
CLARK 2450 7566 JONES 2975 7654
MARTIN 1250 7499 ALLEN 1600 7844
TURNER 1500 7900 JAMES 950 ... 14 rows
selected.
GRADE LOSAL HISAL ----- ----- ------ 1
700 1200 2 1201 1400 3
1401 2000 4 2001 3000 5 3001 9999
53
Retrieving Records with Non-Equijoins
SQLgt SELECT e.ename, e.sal, s.grade
2 FROM emp e, salgrade s 3 WHERE e.sal
4 BETWEEN s.losal AND s.hisal


ENAME SAL GRADE ----------
--------- --------- JAMES 950
1 SMITH 800 1 ADAMS
1100 1 ... 14 rows selected.
54
Outer Joins
EMP
DEPT


ENAME DEPTNO----- ------KING 10 BLAKE 30 CLARK 1
0 JONES 20 ...
DEPTNO DNAME ------ ---------- 10 ACCOUNTING 30
SALES 10 ACCOUNTING 20 RESEARCH ... 40 OPERATIO
NS
55
Outer Joins

• You use an outer join to also see rows that do
  not usually meet the join condition
• Outer join operator is the asterisk ()

SELECT table1.column, table2.column FROM table1,
table2 WHERE table1.column table2.column
SELECT table1.column, table2.column FROM table1,
table2 WHERE table1.column table2.column
56
Using Outer Joins
SQLgt SELECT e.ename, d.deptno, d.dname 2
FROM emp e, dept d 3 WHERE e.deptno
d.deptno 4 ORDER BY e.deptno


ENAME DEPTNO DNAME ---------- ---------
------------- KING 10
ACCOUNTING CLARK 10 ACCOUNTING ...
40 OPERATIONS 15 rows selected.
57
Self Joins
EMP (WORKER)
EMP (MANAGER)


EMPNO ENAME MGR----- ------ ---- 7839 KING
7698 BLAKE 7839 7782 CLARK 7839
7566 JONES 7839 7654 MARTIN 7698 7499 ALLEN 7698
EMPNO ENAME----- -------- 7839 KING
7839 KING 7839 KING 7698 BLAKE 7698 BLAKE
58
Joining a Table to Itself
SQLgt SELECT worker.ename ' works for '
manager.ename 2 FROM emp worker, emp manager
3 WHERE worker.mgr manager.empno

WORKER.ENAME WORKS FOR MANAGER.ENAME -------------
----------------------- BLAKE works for
KING CLARK works for KING JONES works for
KING MARTIN works for BLAKE ... 13 rows selected.