Selecting All Columns

What data are in the EMP table?

SQL> SELECT *
FROM EMP;

You use the * to see all columns of a

table

1
EMP Table

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

7369 SMITH CLERK 7902 17-DEC-80 8000 20
7499 ALLEN SALESMAN 7698 20-FEB-81 16,000 3000 30
7521 WARD SALESMAN 7698 22-FEB-81 12,500 5000 30
7566 JONES MANAG 7839 02-APR-81 29,750 20
7654 MARTIN SALESMAN 7698 28-SEP-81 12,500 14,000 30
Selecting a Specific Column

SQL> SELECT ENAME

FROM EMP;

3
Selecting Multiple Columns
SQL> SELECT EMPNO, ENAME, JOB
FROM EMP;

Place a comma between each column name.

All rows appear for each column selected.

4
Reordering Columns During
Selection
SQL> SELECT EMPNO, ENAME, JOB
FROM EMP;

SQL> SELECT JOB, ENAME, EMPNO

FROM EMP;

The order of selection determines the order

of display

5
Selecting Rows
If you don’t specify a WHERE clause,
all rows will be selected
By specifying a WHERE clause, you
can choose specific rows

SQL> SELECT ENAME

FROM EMP
WHERE DEPTNO =20;

6
Ordering Rows in a Sequence
 In the relational model, rows have no
particular order
 The ORDER BY command is the only way you
can ensure rows will be displayed according
to specific criteria

SQL> SELECT ENAME

FROM EMP;

SQL> SELECT ENAME

FROM EMP
ORDER BY ENAME; 7
Ordering Rows by Multiple
Criteria
 When ordering by multiple criteria,
Primary Order is the first column listed
Secondary Order is the second column listed
And so on ….
 The default is Ascending Sequence (ASC)
 To order in a Descending Sequence, you add the
word DESC after you specify which column to
order by

SQL> SELECT *
FROM DEPT
ORDER BY ENAME, DEPTNO DESC;
8
Selecting Specific Rows:
A Single Condition
In the WHERE clause you can compare a column value
to:
 a CHARACTER constant by using single
quotes
where ename = ‘SMITH’

 an ARITHMETIC EXPRESSION with no

quotes
where deptno = 20

 another table COLUMN values

where emp.deptno = dept.deptno
(This construct, known as a JOIN) 9
Logical Operators for
Selecting Rows
Equality and Inequality Operators

Equal to =
Not equal to != or <>
Greater than >
Greater than or equal >=
Less than <
Less than or equal <=

10
Other Operators
• Equal to any member of the following list IN (list)

• Greater than or equal to one value, BETWEEN low

less than or equal to another number AND
high number
• Matches the following pattern LIKE
– a string of zero or more characters %
– a string of one character _
(EG : S_ _ _ or s%)
• Not applicable / missing value NULL

• Reverses some of the above operators NOT

(e.g. NOT IN, NOT NULL, NOT LIKE)
Lists of Values:
IN and NOT IN
The IN operators lets you select rows that match one of the values
in a list (uses an OR evaluation)
Which employees are either clerks or analysts?

SQL> SELECT ENAME, JOB

FROM EMP
WHERE JOB IN (‘CLERK’, ‘ANALYST’);

Conversely, NOT IN lets you select rows that do not fall in the list

SQL>SELECT ENAME, JOB

FROM EMP
WHERE JOB NOT IN (‘CLERK’, ‘ANALYST’);
12
A Range of Values:
BETWEEN and NOT BETWEEN
The BETWEEN operator lets you select rows that
contain values within a range.
Which employees earn between 20,000 and 30,000?

SQL> SELECT ENAME, JOB, SAL

FROM EMP
WHERE SAL BETWEEN 20000 AND 30000;

Conversely, NOT BETWEEN selects rows outside of a

range.

SQL> SELECT ENAME, JOB, SAL

FROM EMP
WHERE SAL NOT BETWEEN 20000 AND
30000;
13
Looking for a Pattern:
LIKE and NOT LIKE
To search for a string of characters, use the LIKE
operator in the WHERE clause

List all employees whose names begin with letter S.

SQL> SELECT ENAME, DEPTNO

FROM EMP
WHERE ENAME LIKE ‘S%’;

List all employees whose names end with the letter K.

SQL> SELECT ENAME, DEPTNO

FROM EMP
WHERE ENAME LIKE ‘%K’;
14
List all employees whose names begin with a W followed by
exactly 3 characters.

SQL> SELECT ENAME, DEPTNO

FROM EMP
WHERE ENAME LIKE ‘W_ _ _’ ;

Notice that the wildcard symbol % will match a string of any

length, while the _ matches a single character.

Use NOT LIKE to select rows that do not match a pattern.

List all employees whose job titles do not begin with the string
SALES.

SQL> SELECT ENAME, JOB

FROM EMP
WHERE JOB NOT LIKE ‘SALES%’;
Multiple Conditions
The WHERE clause can further qualify what rows are
returned by the specifying more than one condition
needed to satisfy the query.
SQL> SELECT ENAME, JOB
FROM EMP
WHERE DEPTNO = 20
AND JOB != ‘CLERK’;

The AND operator adds further criteria selected rows must

meet.
In contrast, the OR operator specifies selection of rows
where either condition is met.
SQL> SELECT ENAME, JOB
FROM EMP
WHERE DEPTNO = 20
OR JOB != ‘CLERK’;
Precedence of Multiple Operators
You can use AND and OR in a single WHERE clause.
You should use ( ) to establish the order of precedence.
Order of evaluation if you don’t specify ( ) will be;
1. AND
2. OR
For Example:
WHERE DEPTNO = 30
AND JOB = ‘SALESMAN’
OR SAL > 20000 ;
• This refers to salesman in dept 30 or employee who
makes over 20,000

17
But when;

WHERE DEPT = 30
AND (JOB=‘SALESMAN’ OR SAL>20,000);

This refers to employees in department 30 whose

salary is over 20,000 or who are salesmen.

18
Introduction to Expressions
You can use SQL like a calculator to
express values with arithmetic operators.
These include:
+ ADD
- SUBTRACT
* MULTIPLY
/ DIVIDE
These operators can be used in just about
any kind of clause, including
SELECT
WHERE
ORDER BY 19
Numeric Expressions
You can use more than one expression in the
same query:

Who earns a commission that is more than 5% of

their base salary?

SQL> SELECT ENAME, SAL, COMM, COMM / SAL

FROM EMP
WHERE COMM > 0.05*SAL
ORDER BY COMM / SAL DESC;

20
Numeric expressions with Multiple Operators
You may need multiple expressions together
Here’s the order of evaluation:

1. Multiplication *
2. Division /
3. Addition +
4. Subtraction -

Evaluation occurs from left to right, wherever the above

ordering of evaluation does not apply.
You can control the order of evaluation with the use of ().
For instance:
12*(SAL + COMM) != 12*SAL + COMM
21
Column Alias and Expressions
- The column heading that displays normally reflects the one
that you specified when you created the table.

- You can make a different column heading to display by

specifying a COLUMN ALIAS in the SELECT clause.

SQL> SELECT ENAME EMPLOYEE

FROM EMP
WHERE DEPTNO = 10;

If a SELECT clause contains an arithmetic expression, that

expression is used as display heading. An alias can be
used to rename a computed column to make query results
more readable.
22
Eg : Who earns a commission that is more than 5%
of their basic salary?
SQL> SELECT ENAME, SAL, COMM, COMM/SAL “COMMISION RATIO”
FROM EMP
WHERE COMM> .05*SAL
ORDER BY COMM/SAL DESC;

Notice that the expression itself, not the alias, is referenced

in the ORDER BY clause. This rule also applies to all other
clauses in the SELECT statement.
Also notice that an alias containing special characters such
as a blank and a slash (/) must be enclosed in double
quotes.
The alias affects the SELECT command in which it is used. It
has no effect on other queries.
23
The GROUP BY clause
• You use the GROUP BY clause to define multiple
groups of rows
• Every member of the group has at least one value
in common
• You specify the column(s) containing these
common values in the GROUP BY clause.

What is the total and average salary for each

department?
SQL> SELECT DEPTNO, SUM(SAL), AVG(SAL)
FROM EMP
GROUP BY DEPTNO;

24
Multiple Criteria for Grouping Rows
How many employee are there in each
department?
SQL> SELECT DEPTNO, COUNT(*)
FROM EMP
GROUP BY DEPTNO;

How many employees are there in each job

category in each department?
SQL> SELECT DEPTNO, JOB, COUNT(*)
FROM EMP
GROUP BY DEPTNO, JOB; 25
Selecting Specific Groups
• The HAVING clause allows you to select groups that meet
specific condition(s).
• It is similar to the WHERE clause, but it serves a different
purpose:
• The WHERE clause places conditions on the SELECT
clause
• The HAVING clause places conditions on the GROUP BY
clause

Which departments have a payroll exceeding $9,000?

(Do not include commissions.)
SQL> SELECT DEPTNO, SUM(SAL)
FROM EMP
GROUP BY DEPTNO
HAVING SUM(SAL) > 9000; 26
Syntax Review
• Except for clerical staff, which
departments have a payroll exceeding
$8,000?
(Do not include commissions, and list the
department with the lowest amount first)

SQL> SELECT DEPTNO,SUM(SAL)

FROM EMP
WHERE JOB !=‘CLERK’
GROUP BY DEPTNO
HAVING SUM(SAL)> 8000
ORDER BY SUM(SAL);
27