Database Application

SSK 3408

CHAPTER 3
DATA MANIPULATION
LANGUAGE
Learning Objectives

 Define key terms: join, equijoin, self-join,

nonequijoin, natural join, outer join, set
operators and subquery.
 Applying the DML command to insert, update
and delete data from table.
 Write single and multiple table queries using
SQL commands
 Write queries using subqueries.
Data Manipulation Language
(DML)
Used to create, modify and retrieve data
 Insert
 Select
 Update
 Delete
DML – Adding Data
Use the Insert keyword and specify:
 table name
 field names - optional
 values for each field

INSERT INTO customer VALUES(‘Teplow’,’MA’,23445.67);

OR
INSERT INTO customer (last_name, state_cd, sales)
VALUES (‘Teplow’ ,’MA’,23445.67);
DML – Update Data

Use the Update and Set keywords and specify:

 table name
 field name(s)
 where clause (optional)

UPDATE inventory
SET price = price*1.05;

UPDATE inventory
SET price = price*1.05
WHERE product_id = 'P103';
DML – Remove Data

Use the Delete From keywords and specify:

 table name
 where clause (optional)

DELETE FROM customer;

DELETE FROM customer

WHERE sales < 10000;
DML – Select Data
 Select statement retrieves information from the
databases.
 Capabilities of SQL Select Statements:
Projection Selection

Join

Table 1 Table 2 Table 1 Table 2

 Basic Select Statement

SELECT *|{[DISTINCT] column_name | expression [alias],…..}

FROM table_name;

 DISTINCT suppresses duplicates

 * select all columns
 alias gives the selected columns different headings
 FROM identifies the table containing those columns.
 Expressions
 Create expression with number and date by using
arithmetic operators (i.e. +, -, *, /).
 If an expression contains more than one operator, *
and / are evaluated first. If operators in an
expression are of the same priority, evaluation is
done from left to right.
LAST_NAME SALARY SALARY*
SELECT last_name, salary, salary*12 12

FROM employee; Siti Razilah 1000 12000

Musa Ahmad 1200 14400

Martin Luke 1500 18000

 Expressions ..cont.
LAST_NAME SALAR SALARY*
SELECT last_name, salary, 12*salary+100 Y 12

FROM employee; Siti Razilah 1000 12100

Musa Ahmad 1200 14500

Martin Luke 1500 18100

LAST_NAME SALAR SALARY*

SELECT last_name, salary, Y 12

12*(salary+100) Siti Razilah 1000 13200

Musa Ahmad 1200 15600
FROM employee;
Martin Luke 1500 19200
Defining a Null Value
 Null is a value that is unavailable, unassigned,
unknown, or inapplicable.
 Null is not the same as zero or a blank space.
SELECT last_name, salary, commission
FROM employee;
LAST_NAME SALARY COMMISSIO
N
Siti Razilah 1000 (null)
Musa Ahmad 1200 0.2

Martin Luke 1500 0.6

Ammar Faizul 1800 0.3

Safiyah Shahizan 1230 (null)

Taylor Duff 2500 0.8

Defining a Null Value ..cont.

SELECT last_name, salary, commission,

salary*commission
FROM employee;
LAST_NAME SALARY COMMISSION SALARY*COMMISSION

Siti Razilah 1000 (null) (null)

Musa Ahmad 1200 0.2 240

Martin Luke 1500 0.6 900

Ammar Faizul 1800 0.3 540

Safiyah Shahizan 1230 (null) (null)

Taylor Duff 2500 0.8 2000

 Column Heading Default
 Uppercase, however can be override the column heading with
heading display with an alias.
 A column alias:
 Is useful with calculations
 Immediately follows the column name (There can be also the optional AS
keyword between column name and the alias.)
 Requires double quotation marks if it contains spaces of special characters, or
if it is case-sensitive.
 Example:

NAME IDENTITYNO
SELECT last_name as name, nokp as identityno
Siti Razilah 790120-14-6222
FROM employee; Musa 800828-10-6323
Ahmad
Martin Luke 870401-12-6087
Column Heading Default ..cont.
SELECT last_name as “NaMe”, nokp as “identity no”
FROM employee;
NaMe Identity no

Siti Razilah 790120-14-6222

Musa 800828-10-6323
Ahmad
Martin Luke 870401-12-6087

SELECT last_name as “**Name**”, nokp as “#identityno”

FROM employee;
**Name** #identityno

Siti Razilah 790120-14-6222

Musa 800828-10-6323
Ahmad
Martin Luke 870401-12-6087
 Concatenation Operator
 Links columns or character strings to other columns
 Is represented by two vertical bars (||)
 Create a resultant column that is a character
expression
employeeName
SELECT last_name || first_name as
Siti Razilah
“employeeName” Musa Ahmad
FROM employee; Martin Luke
 If a null value with a character string, the result is
Ammar Faizul
a character string. Safiyah Shahizan

e.g: last_name || null  last_name Taylor Duff

Using Literal Character Strings
 Literal is a character, a number, or a date that is
included in the select statement.
 It is not a column name or a column alias.
 Date and character literal values must be enclosed
within single quotation marks; number literals need
not be enclosed in a similar manner;
 Each character string is output once for each row
returned.
Using Literal Character Strings ..cont.

SELECT last_name ||’: 1 month salary =‘||salary Monthly

FROM employee;

MONTHLY

Siti Razilah :1 month salary = 1000

Musa Ahmad: 1 month salary = 1200
Martin Luke : 1 month salary = 1500

Ammar Faizul: 1 month salary = 1800

Safiyah Shahizan: 1 month salary = 1230

Taylor Duff: 1 month salary = 2500

Using Literal Character Strings ..cont.

SELECT last_name ||’’’s 1 month salary =‘||salary Monthly

FROM employee;
MONTHLY

Siti Razilah ‘s1 month salary = 1000

Musa Ahmad’s 1 month salary = 1200
Martin Luke ‘s 1 month salary = 1500
Or you can use Quote (q) Operator
Ammar Faizul ‘s 1 month salary = 1800
[ ],< >,{ }, ( ) are allowed
Safiyah Shahizan’s 1 month salary = 1230

Taylor Duff ‘s 1 month salary = 2500

SELECT last_name ||q’[‘s 1 month salary =]’||salary Monthly

FROM employee;
Duplicate Rows
 The default display of queries is all rows, including
duplicate rows.
SELECT department_id SELECT DISTINCT department_id
FROM employee; FROM employee;

DEPARTMENT_ID DEPARTMENT_ID

10 10
20 20
30 30

20

10
Note: If you specify multiple columns after the DISTINCT
qualifier, the result is every distinct combination of the
20
columns.
Displaying the Table Structure
 Use DESCRIBE command
e.g:
desc faculty;
Name Null Type
------------------------- -------------- -----------------------------------------------------------------
FACULTYID NOT NULL NUMBER
LNAME NOT NULL VARCHAR2(30)
FNAME NOT NULL VARCHAR2(20)
DEPT VARCHAR2(5)
OFFICEID NUMBER
PHONE VARCHAR2(15)
EMAIL VARCHAR2(75)
RANK CHAR(4)
8 rows selected
 Quiz
 Identify the SELECT statements that execute successfully.

1. SELECT first_name, last_name, job_id, salary*12 as yearly salary

FROM employees;

2.
SELECT first_name, last_name, job_id, salary*12 “yearly salary”
FROM employees;

SELECT first_name, last_name, job_id, salary*12 as “yearly salary”

3.
FROM employees;

SELECT first_name || last_name name||’q{‘s total salary is}’||salary

4.
FROM employees;
Limiting Rows using a Selection
 Restrict the rows that are returned by using the
WHERE clause:
SELECT *|{[DISTINCT] column_name | expression [alias],…..}
FROM table_name
[WHERE conditon(s)];

 WHERE clause consists of three elements:

 column name
 comparison condition
 column name, constant or list of values
Limiting Rows using a
Selection ..cont.
LAST_NAME DEPT_ID SALAR COMMISSIO SALARY*COMMISSIO
Y N N
Siti Razilah 10 1000 (null) (null)
Musa Ahmad 20 1200 0.2 240

Martin Luke 30 1500 0.6 900

Ammar Faizul 20 1800 0.3 540

Safiyah Shahizan 10 1230 (null) (null)

Taylor Duff 20 2500 0.8 2000

SELECT last_name, dept_id, salary,

commission
FROM employee
WHERE dept_id = 20;
LAST_NAME DEPT_ID SALAR COMMISSIO
Y N
Musa Ahmad 20 1200 0.2

Ammar Faizul 20 1800 0.3

Taylor Duff 20 2500 0.8

 Limiting Rows using a
Selection ..cont.
LAST_NAME DEPT_ID SALAR COMMISSIO SALARY*COMMISSIO
Y N N
Siti Razilah 10 1000 (null) (null)
Musa Ahmad 20 1200 0.2 240
1. Character strings and data
Martin Luke 30 1500 0.6 900 values are enclosed with
single quotation marks.
Ammar Faizul 20 1800 0.3 540

Safiyah Shahizan 10 1230 (null) (null) 2. Character values are case-

Taylor Duff 20 2500 0.8 2000 sensitive and date value are
format-sensitive.
SELECT last_name, dept_id, salary,
commission
FROM employee
WHERE last_name = ‘Siti Razilah’;
LAST_NAME DEPT_ID SALAR COMMISSIO
Y N
Siti Razilah 10 1000 (null)
Comparison Operators
Operator Meaning
= Equal to
> Greater than
>= Greater than or equal to
< Less than
<= Less than or equal to
<> Not equal to
BETWEEN…AND… Between two values (inclusive)
IN(set) Many any of a list of values
LIKE Match a character pattern
IS NULL Is a Null value
Limiting Rows using a
Selection ..cont.
LAST_NAME DEPT_ID SALAR COMMISSIO SALARY*COMMISSIO
Y N N
Siti Razilah 10 1000 (null) (null)
Musa Ahmad 20 1200 0.2 240

Martin Luke 30 1500 0.6 900

Ammar Faizul 20 1800 0.3 540

Safiyah Shahizan 10 1230 (null) (null)

Taylor Duff 20 2500 0.8 2000

SELECT last_name, dept_id, salary,

commission
FROM employee LAST_NAME DEPT_ID SALAR COMMISSIO
Y N
WHERE dept_id between 10 and 20 ;
Siti Razilah 10 1000 (null)

Musa Ahmad 20 1200 0.2

Ammar Faizul 20 1800 0.3

Safiyah Shahizan 10 1230 (null)

Taylor Duff 20 2500 0.8

Limiting Rows using a
Selection ..cont.
LAST_NAME DEPT_ID SALAR COMMISSIO SALARY*COMMISSIO
Y N N
Siti Razilah 10 1000 (null) (null)
Musa Ahmad 20 1200 0.2 240

Martin Luke 30 1500 0.6 900

Ammar Faizul 20 1800 0.3 540

Safiyah Shahizan 10 1230 (null) (null)

Taylor Duff 20 2500 0.8 2000

SELECT last_name, dept_id, salary,

commission
FROM employee
WHERE last_name like ‘M%’ ;
LAST_NAME DEPT_ID SALAR COMMISSIO
Y N
Musa Ahmad 20 1200 0.2

Martin Luke 30 1500 0.6

Limiting Rows using a
Selection ..cont.
LAST_NAME DEPT_ID SALAR COMMISSIO SALARY*COMMISSIO
Y N N
Siti Razilah 10 1000 (null) (null)
Musa Ahmad 20 1200 0.2 240

Martin Luke 30 1500 0.6 900

Ammar Faizul 20 1800 0.3 540

Safiyah Shahizan 10 1230 (null) (null)

Taylor Duff 20 2500 0.8 2000

SELECT last_name, dept_id, salary,

commission
FROM employee
WHERE last_name like ‘_a%’ ;
LAST_NAME DEPT_ID SALAR COMMISSIO
Y N
Martin Luke 30 1500 0.6

Safiyah Shahizan 10 1230 (null)

 Limiting Rows using a
Selection ..cont.
LAST_NAME DEPT_ID SALAR COMMISSIO SALARY*COMMISSIO
Y N N
Siti Razilah 10 1000 (null) (null)
Musa Ahmad 20 1200 0.2 240

Martin Luke 30 1500 0.6 900

Ammar Faizul 20 1800 0.3 540

Safiyah Shahizan 10 1230 (null) (null)

Taylor Duff 20 2500 0.8 2000

SELECT last_name, dept_id, salary,

commission
LAST_NAME DEPT_ID SALAR COMMISSIO
FROM employee Y N
WHERE dept_id in (10, 20) ; Siti Razilah 10 1000 (null)

Musa Ahmad 20 1200 0.2

Ammar Faizul 20 1800 0.3

Safiyah Shahizan 10 1230 (null)

Taylor Duff 20 2500 0.8

Limiting Rows using a
Selection ..cont.
LAST_NAME DEPT_ID SALAR COMMISSIO SALARY*COMMISSIO
Y N N
Siti Razilah 10 1000 (null) (null)
Musa Ahmad 20 1200 0.2 240

Martin Luke 30 1500 0.6 900

Ammar Faizul 20 1800 0.3 540

Safiyah Shahizan 10 1230 (null) (null)

Taylor Duff 20 2500 0.8 2000

SELECT last_name, dept_id, salary,

commission
FROM employee
WHERE commission is null;
LAST_NAME DEPT_ID SALAR COMMISSIO
Y N
Siti Razilah 10 1000 (null)

Safiyah Shahizan 10 1230 (null)

Conditions Using the Logical Operators

Operator Meaning
AND Return TRUE If both component conditions are true
OR Return TRUE If either component condition is true
NOT Return TRUE If the condition is true
AND Logical Operator
LAST_NAME DEPT_ID SALAR COMMISSIO SALARY*COMMISSIO
Y N N
Siti Razilah 10 1000 (null) (null)
Musa Ahmad 20 1200 0.2 240

Martin Luke 30 1500 0.6 900

Ammar Faizul 20 1800 0.3 540

Safiyah Shahizan 10 1230 (null) (null)

Taylor Duff 20 2500 0.8 2000

SELECT last_name, dept_id, salary,

commission
FROM employee
WHERE last_name like ‘M%’
AND dept_id = 20;
LAST_NAME DEPT_ID SALAR COMMISSIO
Y N
Musa Ahmad 20 1200 0.2
AND Truth Table

AND TRUE FALSE NULL

TRUE TRUE FALSE NULL

FALSE FALSE FALSE FALSE

NULL NULL FALSE NULL

OR Logical Operator
LAST_NAME DEPT_ID SALAR COMMISSIO SALARY*COMMISSIO
Y N N
Siti Razilah 10 1000 (null) (null)
Musa Ahmad 20 1200 0.2 240

Martin Luke 30 1500 0.6 900

Ammar Faizul 20 1800 0.3 540

Safiyah Shahizan 10 1230 (null) (null)

Taylor Duff 20 2500 0.8 2000

SELECT last_name, dept_id, salary,

commission
FROM employee LAST_NAME DEPT_ID SALAR COMMISSIO
Y N
WHERE last_name like ‘M%’
Musa Ahmad 20 1200 0.2
OR dept_id = 20;
Martin Luke 30 1500 0.6

Ammar Faizul 20 1800 0.3

Taylor Duff 20 2500 0.8

OR Truth Table

OR TRUE FALSE NULL

TRUE TRUE TRUE TRUE

FALSE TRUE FALSE NULL

NULL TRUE NULL NULL

NOT Operator
LAST_NAME DEPT_ID SALAR COMMISSIO SALARY*COMMISSIO
Y N N
Siti Razilah 10 1000 (null) (null)
Musa Ahmad 20 1200 0.2 240

Martin Luke 30 1500 0.6 900

Ammar Faizul 20 1800 0.3 540

Safiyah Shahizan 10 1230 (null) (null)

Taylor Duff 20 2500 0.8 2000

SELECT last_name, dept_id, salary,

commission
FROM employee
WHERE dept_id NOT IN (‘10’,’30’);
LAST_NAME DEPT_ID SALAR COMMISSIO
Y N
Musa Ahmad 20 1200 0.2

Ammar Faizul 20 1800 0.3

Taylor Duff 20 2500 0.8

 Sorting Rows
 Sort the retrieved rows with the ORDER BY clause:
 ASC: Ascending order, default
 DESC: Descending order
LAST_NAME DEPT_I SALAR COMMISSI SALARY*COMMISSIO
D Y ON N
Siti Razilah 10 1000 (null) (null)
Musa Ahmad 20 1200 0.2 240

Martin Luke 30 1500 0.6 900

LAST_NAME DEPT_I SALARY COMMISSIO
Ammar Faizul D N
20 1800 0.3 540
Siti Razilah 10 1000 (null)
Safiyah Shahizan 10 1230 (null) (null)
Safiyah Shahizan 10 1230 (null)
Taylor Duff 20 2500 0.8 2000
Musa Ahmad 20 1200 0.2

SELECT last_name, dept_id, salary, Ammar Faizul 20 1800 0.3

commission Taylor Duff 20 2500 0.8

FROM employee Martin Luke 30 1500 0.6
ORDER BY dept_id;
 Sorting Rows ..cont.
 Sort the retrieved rows with the ORDER BY clause:
 ASC: Ascending order, default
 DESC: Descending order
LAST_NAME DEPT_I SALAR COMMISSI SALARY*COMMISSIO
D Y ON N
Siti Razilah 10 1000 (null) (null)
Musa Ahmad 20 1200 0.2 240

Martin Luke 30 1500 0.6 900

LAST_NAME DEPT_I SALARY COMMISSIO
Ammar Faizul D N
20 1800 0.3 540
Martin Luke 30 1500 0.6
Safiyah Shahizan 10 1230 (null) (null)
Taylor Duff 20 2500 0.8
Taylor Duff 20 2500 0.8 2000
Ammar Faizul 20 1800 0.3

SELECT last_name, dept_id, salary, Musa Ahmad 20 1200 0.2

commission Siti Razilah 10 1000 (null)

FROM employee Safiyah Shahizan 10 1230 (null)
ORDER BY dept_id DESC;
 Sorting Rows ..cont.
 Sort the retrieved rows with the ORDER BY clause:
 ASC: Ascending order, default
 DESC: Descending order
LAST_NAME DEPT_I SALAR COMMISSI SALARY*COMMISSIO
D Y ON N
Siti Razilah 10 1000 (null) (null)
Musa Ahmad 20 1200 0.2 240

Martin Luke 30 1500 0.6 900

LAST_NAME DEPT_I SALARY COMMISSIO
Ammar Faizul D N
20 1800 0.3 540
Martin Luke 30 1500 0.6
Safiyah Shahizan 10 1230 (null) (null)
Taylor Duff 20 2500 0.8
Taylor Duff 20 2500 0.8 2000
Ammar Faizul 20 1800 0.3

SELECT last_name, dept_id, salary, Musa Ahmad 20 1200 0.2

commission Siti Razilah 10 1000 (null)

FROM employee Safiyah Shahizan 10 1230 (null)
ORDER BY 2 DESC
 Sorting Rows ..cont.
 Sort the retrieved rows with the ORDER BY clause:
 ASC: Ascending order, default
 DESC: Descending order
LAST_NAME DEPT_I SALAR COMMISSI SALARY*COMMISSIO
D Y ON N
Siti Razilah 10 1000 (null) (null)
Musa Ahmad 20 1200 0.2 240

Martin Luke 30 1500 0.6 900

LAST_NAME DEPT_I SALARY COMMISSIO
Ammar Faizul D N
20 1800 0.3 540
Martin Luke 30 1500 0.6
Safiyah Shahizan 10 1230 (null) (null)
Taylor Duff 20 2500 0.8
Taylor Duff 20 2500 0.8 2000
Ammar Faizul 20 1800 0.3

SELECT last_name, dept_id, salary, Musa Ahmad 20 1200 0.2

commission Safiyah Shahizan 10 1230 (null)

FROM employee Siti Razilah 10 1000 (null)
ORDER BY dept_id, salary desc;
Substitution Variables
 Use a variable prefixed with an ampersand (&) to
prompt the user for a value:
SELECT last_name, dept_id, salary,
commission
FROM employee
WHERE dept_id = &dept_id;

LAST_NAME DEPT_ID SALAR COMMISSIO

Y N
Musa Ahmad 20 1200 0.2

20 Ammar Faizul 20 1800 0.3

Taylor Duff 20 2500 0.8

Using Functions to Customize Output

FUNCTIONS

MULTIPLE-
SINGLE-ROW
ROW
FUNCTIONS
FUNCTIONS
Return one result Return one result
per row per set of rows
Single Row Functions – Character Data Type

 LOWER(column/expression)
 UPPER(column/expression)
 INITCAP(column/expression)
 CONCAT(column/expression, column/expression)
 SUBSTR(column/expression, m[,n])
 m – character position (if m is negative, the count starts from the
end of the character value.
 n – character long (if n is omitted, all characters to the end of the
string are returned)
 LENGTH(column/expression)
 INSTR(column/expression, ‘string’ [, m] , [n])
An Example
FUNCTION RESULT
LOWER(‘Database application’) database application
UPPER(‘Database application’) DATABASE
APPLICATION
INITCAP(‘Database application’) Database Application
CONCAT(‘Hello’, ’World’) HelloWorld
SUBSTR(‘HelloWorld’,2,5) elloW
INSTR(‘HelloWorld’, ’W’) 6
LPAD(salary,10,’*’) *****24000
RPAD(salary,10,’*’) 24000*****
REPLACE(‘JACK AND JUE’, ’J’, ’BL’) BLACK AND BLUE
Single Row Functions – Number Data
Type
 ROUND: Rounds value to a special decimal
 TRUNC: Truncates value to specified decimal
 MOD: Returns remainder of division

FUNCTION RESULT
ROUND(45.926, 2) 45.93
TRUNC(45.926, 2) 45.92
MOD(1600, 300) 100
 Nesting Functions
LAST_NAME DEPT_ID SALAR COMMISSIO SALARY*COMMISSIO
Y N N
Siti Razilah 10 1000 (null) (null)
Musa Ahmad 20 1200 0.2 240

Martin Luke 30 1500 0.6 900

Ammar Faizul 20 1800 0.3 540

Safiyah Shahizan 10 1230 (null) (null)

Taylor Duff 20 2500 0.8 2000

SELECT last_name, LAST_NAM NAME

UPPER(CONCAT(SUBSTR(last_name,1,8), ‘_US’)) E
name Siti Razilah SITI RAZ_US

FROM employee; Musa Ahmad MUSA AHM_US

Martin Luke MARTIN L_US

Ammar Faizul AMMAR FA_US

Safiyah Shahizan SAFIYAH _US

Taylor Duff TAYLOR D_US

NVL FUNCTION
 Convert a null value to an actual value:
 Data types that can be used are date, character and
number.
 Data types must match:
 NVL(commission, 0)
 NVL(hire_date, ‘01-JAN-10’)
 NVL(job, ‘No Job Yet’)
 CASE EXPRESSION
Not available in Microsoft Access. It is in SQL Server and Oracle.

Select AnimalID,
CASE
WHEN Date()-DateBorn < 90 Then ‘Baby’
WHEN Date()-DateBorn >= 90
AND Date()-DateBorn < 270 Then ‘Young’
WHEN Date()-DateBorn >= 270
AND Date()-DateBorn < 365 Then ‘Grown’
ELSE ‘Experienced’
END “STATUS”
FROM Animal;
Used to change data to a different context.
Example: Define age categories for the animals.
Less than 3 months
Between 3 months and 9 months
Between 9 months and 1 year
Over 1 year
Multiple-Row Functions
 Group functions operate on sets of rows to give one
result per group.
LAST_NAME DEPT_ID SALAR
Y
Siti Razilah 10 1000

EMPLOYE

Maximum
salary in
ES table
Musa Ahmad 20 1200 MAX(SALAR
Ammar Faizul 20 1800
Y)
Safiyah Shahizan 10 1230
2500
Taylor Duff 20 2500
Type of Multiple Row Functions
 AVG(column)
 MAX(column)
 MIN(column)
 SUM(column)
 COUNT(*) or COUNT(column) or COUNT(DISTINCT column)
 COUNT(*) returns the number of rows in a table.
 COUNT(commission) returns the number of rows with non-null values.
 COUNT(DISTINCT commission) returns the number of distinct
commission values.
Multiple-Row Functions and Null
Values
 Group functions ignore null values in the column:
SELECT
AVG(commission)
FROM employee;
 The NVL function forces group functions to
include null values:
SELECT
AVG(NVL(commission,0))
FROM employee;
LAST_NAM DEPT_I SALAR COMMISSI
E D Y ON
Siti Razilah
10 1000 (null)
Musa Ahmad
20 1200 0.2
Martin Luke
30 1500 0.6
Ammar Faizul
20 1800 0.3
Safiyah Shahizan
10 1230 (null)
Taylor Duff
20 2500 0.8
GROUP BY clause
 Group functions operate on sets of rows to give one
result per group.

LAST_NAME DEPT_ID SALAR

Y
Siti Razilah 10 1000

Musa Ahmad 20 1200 DEPT_ID MAX(SALARY)

salary for each

EMPLOYEES
department in 10 1230
Maximum
Martin Luke 30 1500
table

Ammar Faizul 20 1800 20 2500

Safiyah Shahizan 10 1230

30 1500

Taylor Duff 20 2500

Creating Groups of Data
 To divide rows in a table into smaller groups by
using GROUP BY clause.

SELECT *|{[DISTINCT] column_name | expression [alias],…..}

FROM table_name
[WHERE conditon(s)];
[GROUP BY group_by_expression]
[ORDER BY column];
Using the GROUP BY clause
 All the columns in the SELECT list that are not in group
functions must be in the GROUP BY clause.
 However, the GROUP BY column does not have to be in the
SELECT list.

SELECT dept_id, SELECT MAX(salary)

MAX(salary) FROM employee
FROM employee GROUP BY dept_id;
GROUP
DEPT_ID
BY dept_id;
MAX(SALARY) MAX(SALARY)

10 1230 1230
20 2500 2500
30 1500 1500
Use Group Functions in ORDER
BY
SELECT dept_id, MAX(salary)
FROM employee
GROUP BY dept_id
ORDER BY MAX(salary); DEPT_ID MAX(SALARY)

20 2500
30 1500
10 1230
Restricting Group Results
LAST_NAME DEPT_ID SALAR
Y
Siti Razilah 10 1000

Musa Ahmad 20 1200

only for those who

average salary is

Maximum salary
DEPT_ID MAX(SALARY)

department BUT
greater than 1

for each
Martin Luke 30 1500

1300
20 2500
Ammar Faizul 20 1800 30 1500
Safiyah Shahizan 10 1230

Taylor Duff 20 2500

Restricting Group with HAVING
clause

SELECT *|{[DISTINCT] column_name | expression [alias],…..}

FROM table_name
[WHERE conditon(s)];
[GROUP BY group_by_expression]
[HAVING group_coundition]
[ORDER BY column];

SELECT dept_id, MAX(salary)

FROM employee
GROUP BY dept_id
HAVING MAX(salary) > 1300;
End with… displaying data from a table
Then, we continue with displaying data from
multiple tables or known as join tables.
Shall we start?
EMPLOYEE DEPENDENT
DEPI NAME DOB STATU EMPID
EMPI LAST_NAME DEPT_I SALAR COMMISSI D S
D D Y ON 101 Muhammad 12-10-1960 Suami 1
1 Siti Razilah 10 1000 (null) 102 Sarah 03-03-1990 Anak 1
2 Musa Ahmad 20 1200 0.2 103 Linda 12-06-1993 Anak 1

3 Martin Luke 30 1500 0.6 104 Ali 27-09-1997 Anak 1

4 Ammar Faizul 105 Sarimah 11-08-1976 Isteri 2

20 1800 0.3
106 Adibah 24-07-2003 Anak 2
5 Safiyah Shahizan 10 1230 (null)
107 Haziq 06-03-2005 Anak 2
6 Taylor Duff 20 2500 0.8
108 Elizabeth 04-04-1973 Isteri 6
109 Ridzuan 09-02-1990 Anak 4

LAST_NAM SALARY NAME STATUS

E
Siti Razilah 1000 Muhammad Suami
Siti Razilah 1000 Sarah Anak
Siti Razilah 1000 Linda Anak
Siti Razilah 1000 Ali Anak
Musa Ahmad 1200 Sarimah Isteri
Musa Ahmad 1200 Adibah Anak
Musa Ahmad 1200 Haziq Anak
Ammar 1800 Ridzuan Anak
Faizul
Taylor Duff 2500 Elizabeth Isteri
Table Joins
 Process of combining data from two or more tables, normally using primary
and/or foreign keys.
 Basic types of joins:
 Natural Joins:
 NATURAL JOIN clause
 USING clause
 ON clause
 Self-Joins
 Nonequijoins
 Outer Joins:
 LEFT OUTER JOIN clause
 RIGHT OUTER JOIN clause
 FULL OUTER JOIN clause
 CROSS JOIN clause
 Joining Tables Using SQL:1999
syntax

SELECT table1.column, table2.column

FROM table1
[NATURAL JOIN table2] |
[JOIN table2 USING (column_name)] |
[JOIN table2 on (table1.column_name = table2.column_name)] |
[LEFT | RIGHT | FULL OUTER JOIN table2 on table1.column_name =
table2.column_name)]|
[CROSS JOIN table2];
Creating NATURAL JOIN clause
 Example of Using NATURAL JOIN
clause
EMPLOYEE DEPARTMENT
EMPI LAST_NAME DEPT_I SALAR COMMISSI DEPT_ID DEPT_NAME
D D Y ON
10 Department of Networking
1 Siti Razilah 10 1000 (null)
20 Department of Info. Science
2 Musa Ahmad 20 1200 0.2
30 Department of Multimedia
3 Martin Luke 30 1500 0.6
40 Department of Computer
4 Ammar Faizul 20 1800 0.3 Science
5 Safiyah Shahizan 10 1230 (null) 50 Department of Applied Science
6 Taylor Duff 20 2500 0.8 60 Department of Robotics

LAST_NAME SALAR DEPT_NAME

Y
SELECT last_name, salary, dept_name Siti Razilah 1000 Department of Networking
FROM employee Musa Ahmad 1200 Department of Info. Science
NATURAL JOIN department Martin Luke 1500 Department of Multimedia
Ammar Faizul 1800 Department of Info. Science
Safiyah Shahizan 1230 Department of Networking
Taylor Duff 2500 Department of Info. Science
Creating join with the USING
clause
 Example of Using NATURAL JOIN
clause
EMPLOYEE DEPARTMENT
EMPI LAST_NAME DEPT_I SALAR COMMISSI DEPT_ID DEPT_NAME
D D Y ON
10 Department of Networking
1 Siti Razilah 10 1000 (null)
20 Department of Info. Science
2 Musa Ahmad 20 1200 0.2
30 Department of Multimedia
3 Martin Luke 30 1500 0.6
40 Department of Computer
4 Ammar Faizul 20 1800 0.3 Science
5 Safiyah Shahizan 10 1230 (null) 50 Department of Applied Science
6 Taylor Duff 20 2500 0.8 60 Department of Robotics

LAST_NAME SALAR DEPT_NAME

SELECT last_name, salary, dept_name Y
FROM employee Siti Razilah 1000 Department of Networking
JOIN department Musa Ahmad 1200 Department of Info. Science
USING (dept_id) Martin Luke 1500 Department of Multimedia
Ammar Faizul 1800 Department of Info. Science
Safiyah Shahizan 1230 Department of Networking
Taylor Duff 2500 Department of Info. Science
Using Table Aliases with the USING
clause
 Do not qualify a column that is used in the USING

x
clause.
SELECT e.last_name, e.salary, d.dept_name
FROM employee e
JOIN department d
USING (dept_id)
WHERE d.dept_id in (‘10’,’20’);

SELECT e.last_name, e.salary, d.dept_name

FROM employee e
JOIN department d
USING (dept_id)
WHERE dept_id in (‘10’,’20’);
Creating Joins with the ON clause
 NATURAL JOIN is basically equijoin of all columns
with the same name.
 For arbitrary conditions (selected columns) use the ON
clause
 Example:

SELECT e.last_name, e.salary, d.dept_name

FROM employee e
JOIN department d
ON (e.dept_id = d.dept_id);
Applying Additional Conditions to a Join

 Use the AND clause or the WHERE clause to

specify additional conditions.
 Example:
SELECT e.last_name, e.salary, d.dept_name
FROM employee e
JOIN department d
ON (e.dept_id = d.dept_id)
AND e.salary > 1000;

SELECT e.last_name, e.salary, d.dept_name

FROM employee e
JOIN department d
ON (e.dept_id = d.dept_id)
WHERE e.salary > 1000;
 SELF-JOIN
 Joining a table to itself.
EMPLOYEE (WORKER)
EMPI LAST_NAME DEPT_I SALAR COMMISSI MGR_I
D D Y ON D
1 Siti Razilah 10 1000 (null) 6
2 Musa Ahmad 20 1200 0.2 4
3 Martin Luke 30 1500 0.6 6
4 Ammar Faizul 20 1800 0.3 6
5 Safiyah Shahizan 10 1230 (null) 1
6 Taylor Duff 20 2500 0.8 (null)

EMPLOYEE (MANAGER)
EMPI LAST_NAME DEPT_I SALAR COMMISSI MGR_I
D D Y ON D
1 Siti Razilah 10 1000 (null) 6
2 Musa Ahmad 20 1200 0.2 4
3 Martin Luke 30 1500 0.6 6
4 Ammar Faizul 20 1800 0.3 6
5 Safiyah Shahizan 10 1230 (null) 1
6 Taylor Duff 20 2500 0.8 (null)
 Self-Join using the ON clause

SELECT worker.last_name emp, worker.dept_id department,

worker.salary salary, manager.last_name manager
FROM employee worker
JOIN employee manager
ON (worker.mgr_id = manager.empid);

EMP DEPARTME SALAR MANAGER

NT Y
Safiyah Shahizan 10 1230 Siti Razilah
Musa Ahmad 20 1200 Ammar
Faizal
Ammar Faizul 20 1800 Taylor Duff
Siti Razilah 10 1000 Taylor Duff
Martin Luke 30 1500 Taylor Duff
 Nonequijoins
A join condition containing something other than


an equality operator.
EMPLOYEE JOB_GRADE
EMPI LAST_NAME DEPT_I SALAR COMMISSI MGR_I
GRADE_LEV LOW_SA MAX_SA
D D Y ON D
EL L L
1 Siti Razilah 10 1000 (null) 6
A 0 999
2 Musa Ahmad 20 1200 0.2 4
B 1000 1999
3 Martin Luke 30 1500 0.6 6
C 2000 2999
4 Ammar Faizul 20 1800 0.3 6
D 3000 3999
5 Safiyah Shahizan 10 1230 (null) 1
E 4000 10000
6 Taylor Duff 20 2500 0.8 (null)
LAST_NAME SALAR GRADE_LE
Y VEL
SELECT e.last_name, e.salary, j.grade_level Siti Razilah 1000 B
FROM employee e Musa Ahmad 1200 B
JOIN job_grade j Martin Luke 1500 B

ON e.salary Ammar Faizul 1800 B

BETWEEN j.low_sal AND j.max_sal Safiyah Shahizan 1230 B

Taylor Duff 2500 C
Nonequijoins ..cont.
AccountsReceivable
Categorize by Days Late
30, 90, 120+
Three queries?
New table for business rules
AR(TransactionID, CustomerID, Amount, DateDue)

LateCategory(Category, MinDays, MaxDays, Charge, …)

Month 30 90 3%
Quarter 90 120 5%
Overdue 120 9999 10%
SELECT *
FROM AR JOIN LateCategory
ON ((Date() - AR.DateDue) >= LateCategory.MinDays)
AND ((Date() - AR.DateDue) < LateCategory.MaxDays)
Returning Record with No Direct
Match
 Use OUTER joins.
 LEFT OUTER JOIN clause
 RIGHT OUTER JOIN clause
 FULL OUTER JOIN clause
LEFT OUTER JOIN
 Used when you may not have matching data in the
secondary table, but still want to include the data
you have in the primary table.

NOTE: The primary table is normally listed on the left side of

the equation, the secondary on the right side.
 An Example of Left Joins
Insert a new department in table dept (for example,
dept_id=50).
Select d.dept_id, d.dept_name, e.dept_id, e.last_name
FROM dept d LEFT OUTER JOIN employee e
ON d.dept_id = e.dept_id;

DEPT_I DEPT_NAME DEPT_I LAST_NAME

D D
10 Department of Networking 10 Siti Razilah

20 Department of Info. 20 Musa Ahmad

Science
30 Department of Multimedia 30 Martin Luke

20 Department of Info. 20 Ammar Faizul

Science
10 Department of Networking 10 Safiyah Shahizan

20 Department of Info. 20 Taylor Duff

Science
RIGHT OUTER JOINS
 Used when you may have data in the secondary table
with no matching data in the primary table.
 Example:
Insert a new employee in table employee (for example, empid=7).

Select d.dept_id, d.dept_name, e.dept_id, e.last_name

FROM dept d RIGHT OUTER JOIN employee e
ON d.dept_id = e.dept_id;
 RIGHT OUTER JOINS ..cont.
Insert a new employee in table employee (for example, empid=7).

Select d.dept_id, d.dept_name, e.dept_id, e.last_name

FROM dept d RIGHT OUTER JOIN employee e
ON d.dept_id = e.dept_id;

DEPT_I DEPT_NAME DEPT_I LAST_NAME

D D
Gopal

10 Department of Networking 10 Siti Razilah

20 Department of Info. 20 Musa Ahmad

Science
30 Department of Multimedia 30 Martin Luke

20 Department of Info. 20 Ammar Faizul

Science
10 Department of Networking 10 Safiyah Shahizan

20 Department of Info. 20 Taylor Duff

 FULL OUTER JOIN

Select d.dept_id, d.dept_name, e.dept_id, e.last_name

FROM dept d RIGHT OUTER JOIN employee e
ON d.dept_id = e.dept_id;
DEPT_I DEPT_NAME DEPT_I LAST_NAME
D D
Gopal

10 Department of Networking 10 Siti Razilah

20 Department of Info. 20 Musa Ahmad

Science
30 Department of Multimedia 30 Martin Luke

20 Department of Info. 20 Ammar Faizul

Science
10 Department of Networking 10 Safiyah Shahizan

20 Department of Info. 20 Taylor Duff

Science
50 Department of Math
Generating a Cartesian Product
 Use CROSS JOIN clause
EMPLOYEE (6 rows) DEPART (4 rows)
LAST_NAME DEPT_I SALAR COMMISSI
DEPT_ID DEPT_NAME
D Y ON
Siti Razilah 10 1000 (null) 10 Department of Networking
Musa Ahmad 20 1200 0.2 20 Department of Info. Science
30 Department of Multimedia
Martin Luke 30 1500 0.6
40 Department of Computer
Ammar Faizul 20 1800 0.3 Science

Safiyah Shahizan 10 1230 (null)

Taylor Duff 20 2500 0.8

Cartesian Product: 6 X 4 = 24 rows

Generating a Cartesian Product ..cont.
Using Subqueries to Solve Queries

Main Query:

Which employees have salaries greater than Ammar Faizul’s salary?

Sub Query:

How much is Ammar Faizul’s

salary?
SubQuery Syntax

SELECT select_list
FROM table
WHERE expr operator
(Select select_list
FROM TABLE);

 The subquery (inner query) executes before the main

query (outer query)
 The result of subquery is used by the main query.
 An Example of Subquery
SELECT last_name, salary
FROM employee
where salary > (SELECT salary
FROM employee
WHERE last_name = ‘Ammar Faizul’);
LAST_NAME DEPT_I SALAR
LAST_NAME SALAR
D Y
Y
Siti Razilah 10 1000 Taylor Duff 2500
Musa Ahmad 20 1200

Martin Luke 30 1500

Ammar Faizul 20 1800

Safiyah Shahizan 10 1230

Taylor Duff 20 2500

Types of Subqueries
 Single-row subquery
Main Query
returns
SubQuery 1000

 Multiple-row subquery
Main Query
returns 1200
SubQuery 1800
2500
 x
An Example
SELECT last_name, salary
FROM employee Single-row operator with
multiple-row subquery
where salary = (SELECT salary
FROM employee
WHERE dept_id = 20); EMPI LAST_NAME DEPT_I SALAR COMM
D D Y ON
1 Siti Razilah 10 1000
2 Musa Ahmad 20 1200
3 Martin Luke 30 1500
SELECT last_name, salary 4 Ammar Faizul 20 1800

FROM employee 5

6
Safiyah Shahizan

Taylor Duff
10 1230
20 2500
where salary in (SELECT salary
FROM employee
WHERE dept_id = 20);
Subquery Characteristics
 Can be used in the SELECT, CREATE, INSERT,
UPDATE and DELETE statements
 Can be used as part of a condition in the WHERE clause
 Can be used in multiple AND or OR predicates of the same
SQL statement
 Is enclosed in parenthesis and must appear on the right in a
WHERE clause condition
 Cannot include an ORDER BY clause
 The number of rows returned by the subquery must match
the number expected by the main query
Combining Subqueries
 Multiple subqueries can be used to check for more than one
condition in a statement.
 Example:
SELECT last_name, salary
FROM employees
WHERE dept_id = (SELECT dept_id
FROM employee
WHERE last_name = ‘Taylor Duff’)
AND salary > (SELECT salary
FROM employee
WHERE last_name = ‘Taylor Duff’);
 Same or different types can be nested.

NOTE: Nested subqueries are executed from the most deeply nested to the least deeply nested
subquery.
 Example

Show all customers who have placed an order

SELECT customer_name FROM customer_t

WHERE customer_ID IN
(SELECT DISTINCT customer_ID FROM order_t);

Subquery is embedded in
parentheses. In this case it
returns a list that will be used
in the WHERE clause of the
outer query
Sally Pet Store
 Subquery for Calculation
Which cats sold for more than the average sale price of cats?
Assume we know the average price is $170.
Usually we need to compute it first.

SELECT SaleAnimal.AnimalID, Animal.Category,

SaleAnimal.SalePrice
FROM Animal
SaleAnimal ON Animal.AnimalID = SaleAnimal.AnimalID
WHERE ((Animal.Category="Cat") AND (SaleAnimal.SalePrice>170));

SELECT SaleAnimal.AnimalID, Animal.Category, SaleAnimal.SalePrice

FROM Animal
JOIN SaleAnimal ON Animal.AnimalID = SaleAnimal.AnimalID
WHERE ((Animal.Category="Cat") AND (SaleAnimal.SalePrice>
( SELECT AVG(SalePrice)
FROM Animal
JOIN SaleAnimal ON Animal.AnimalID = SaleAnimal.AnimalID
WHERE (Animal.Category=“Cat”)
)
) );
Using NOT IN with a Subquery

Which animals have not been sold?

Start with list of all animals.
Subtract out list of those who were sold.

SELECT Animal.AnimalID, Animal.Name, Animal.Category

FROM Animal
WHERE (Animal.AnimalID Not In
(SELECT AnimalID From SaleAnimal));
UNION, INTERSECT, MINUS

A B C
List the name of any employee
who has worked for both the
East and West regions.
T1 T2

SELECT EID, Name

FROM EmployeeEast
INTERSECT
SELECT EID, Name
FROM EmployeeWest
UNION
SELECT EID, Name, Phone, Salary, ‘East’ AS Office
FROM EmployeeEast
UNION
SELECT EID, Name, Phone, Salary, ‘West’ AS Office
FROM EmployeeWest

EID Name Phone Salary Office

352 Jones 3352 45,000 East
876 Inez 8736 47,000 East
372 Stoiko 7632 38,000 East

890 Smythe 9803 62,000 West

361 Kim 7736 73,000 West

Offices in Los Angeles and New York.

Each has an Employee table (East and West).
Need to search data from both tables.
Columns in the two SELECT lines must match the column number and
column type.
Summary
 In this chapter you learned how to:
 Define key terms: join, equijoin, self-join, nonequijoin,
natural join, outer join, set operators and subquery.
 Apply the DML command to insert, update and delete
data from table.
 Write single and multiple table queries using SQL
commands
 Write queries using subqueries.