Database 2

1
Course Objectives
• Exposure to Oracle DB
• Exposure to SQL developer Environment
• Learn the Advanced SQL
• Revisiting ERD to introduce (EERD)
• Learn Triggers
• Learn PL/SQL
• Learn Stored procedures
• Utilize DB for data science purposes
• Exposure o relational algebra

2
Course Description
• This course aims to equip students with the necessary skill to design
and build a database solution applying the standard techniques and
methodologies for that. In addition, the course will expose students
to real database samples in order to give them hands on experience
on one of the prominent DB technologies (e.g. Oracle).
• The expected outcome of this course is that student will be able to
handle the complexity of building a RDBMS, opening new horizon for
possible working position and collaboration with industry.

3
Grading System
• The distribution of the marks in this course will be as follows:
• 20 % Midterm exam
• 40 % Final Exam
• 10 % project Report
• 15 % weekly Quizzes
• 15 % attendance

4
Students Obligations!
• Must follow COVID-19 University Regulations
• Don’t be late to class
• Don’t raise hand to go out during the class
• Must be active and take notes during class – don’t be a guest!
• Write down your questions, think about it, then ask when appropriate!
• Must spend 3 hours minimum weekly doing self-study ,homework ,
revision, practice

• Bring your laptop(if you can) for better learning exposure

5
DB1 Revision

6
Definitions
• RDBMS : Relational Database Management systems
• Oracle , SQL server, MySQL … etc
• Schema: in Oracle terms it’s the database in some other Engines!
• Create new Schema in Oracle -> new Database in (e.g. MySQL)
• A Schema is associated with a User
• ERD: is the data modeling/design of the schema (Entity-Relationship Diagram)
• SQL: Structured Query Language :
• DML: Data Manipulation Language
• update/insert/delete/(select? DQL)
• DDL: Data Definition Language
• create/alter/drop/rename/truncate
• DCL: Data Control Language
• grant/revoke
• TCL: Transaction Control Language
• commit/rollback/savepoint

7
Definitions(2)
• Table: is the physical storage of data in Relational Database (RDB)
• Rows (records)
• Columns ( attributes/features)
• Relationship: a table (A) might depend on another table (B) to complete its details.
Hence (A)must have a relationship with (B).
• Constraints: every table can define a number of constrains that will help in data
validation and ensure consistency, e.g.:
• Primary Key: one or more columns in a table that is uniquely identify a record.
• Foreign Key: one or more columns in a table that is referenced to another table( usually
referenced to PK of the parent table).
• Composite Key: a primary key with more than one column.
• Compound Key: foreign key with more than one column, but all are referenced to PKs in their
tables.
• Alternative key: is a key that has similar characteristics to a PK but its not!

8
Definitions(3)
• Normalization: is the transformation of tables from one form to another in order to
be usable by an application:
• 0NF:
• All data in a single table
• Duplication allowed
• 1NF:
• Duplication of columns/rows are eliminated
• PK is defined
• 2NF:
• Is 1NF
• FK is defined between tables
• 3NF:
• Is 2NF
• Transitive dependency is eliminated -> more table splitting!

9
Example:
A customer requested you to develop a web application for their
company to manage the HR department. Every employee is allocated in
a department and they are supervised by a single manager. Your
customer requested that they need to identify manager of employees
either by department or by the employees themselves.

You need to do analysis of customer requirements ( use software

engineering principles!) in order to build this application!

10
ERD in Crow’s Foot Notation

11
ERD in Chen’s Notation

12
13
14
• HR Schema already created in Oracle XE.. Will use it for now 

• If user is locked do the following:

• Connect to database through SQL-Plus using credentials:
• Username:Sys as sysdba
• Password: {leave empty}
• Type:
• Alter user hr account unlock; (or use SQL developer to do this!)
• To set password:
• Alter user hr identified by hr;

15
Useful References
• SQL Lanuage Reference 115R2 (Jan 2016):
https://docs.oracle.com/cd/E11882_01/server.112/e41084.pdf
• Oracle Database Concepts:
https://docs.oracle.com/en/database/oracle/oracle-database/19/cnc
pt/database-concepts.pdf
• TechonTheNet: https://www.techonthenet.com/oracle/index.php
• W3school: https://www.w3schools.com/sql/

16
Table Structure

17
 Table Example
Column/Field Name

Row/Record

18
One table contains all data

19
Tables with relationship

20
• Explore hr schema
Select * from user_tables;

• Check Employees table structure:

desc Employees;

• Select statement:
• Select [column(s)_name] from [table(s)_name] {conditions} {grouping
<<conditions>>} {sorting}
• List all employees details:
Select * from Employees;

• List employee id, last name, email of employees

Select employee_id, last_name, email from
Employees;
21
Select Statement Cont ( Order by)
• Sort data ascending/descending order
• Use order by keyword
Select * from Employees order by salary desc;

• Sort by two columns

Select * from Employees order by salary desc,
employee_id asc;

22
 Select Statement Cont (where
clause)
• The “where” clause is used to filter data based on satisfying certain
condition
• Analogy from other programming languages: If (x = 10000) then print all_data;
• Use it when you need to get a subset of data from a table.

Select * from <<Table_Name>> where <<column_name>> = <<value>>;

• caution: use ‘=‘ when you need to check exact value match!
• For example: find all records of employees those first name is ‘Alex’
• select * from employees where first_name = ‘Alex’;

23
 Operators and Null
• Basic Comparison operators:
• Equal to an exact value: “=“
• Greater than an exact value: “>”
• Less than an exact value: “<“
• Greater than or equal to an exact value: “>=“
• Less than or equal to an exact value: “<=“
• Not equal an exact value: “<>” or “!=“
• Basic Logical operators:
• not, and, or
• Null value: its used when a field doesn’t contain any value (empty).
• Associated with “not” for exclusion
• Used with “IS” : return true or false
• Caution: never use: “= null”.
• comparison to unknown!!!!

24
Where clause – multiple conditions
• Multiple conditions: when you need to filter data by more than one
filter
• Use where in association with “and”, “or”

Select * from <<Table_Name>> where <<column_name1>> =

<<value1>>
And <<column_name2>> = <<value2>>
Or <<column_name3>> = <<value3>>;

25
Where clause (cont) – multiple conditions

26
Where clause (cont) – multiple conditions
• Carful when using multiple conditions
• E.g.: for illustration purpose only!

Select * from employees where salary = 17000 and

salary = 9000;
Select * from employees where salary > 11000 and
salary < 9000;
• What do you expect the results are?

27
 Date functions
• Dealing with Dates is crucial in any database as data are usually
associated with timeline for tracking some events or attributes values, eg:
• Hire date of employees
• Graduation date
• Current date
• Many functions listed at :
https://www.techonthenet.com/oracle/functions/
• Some examples:
• Sysdate: select sysdate from ?????
• Extract:
select employee_id, extract(month from hire_date) from employees;
• Months_between:
select months_between(sysdate,hire_date) from employees;

28
 Date functions (cont.)
• List employees those join the company in September
select * from employees where extract(month from hire_date) = 9;

• List Employees those joined the company within 12 months

before “Steven King” joining date
select * from employees where
  months_between ('06/17/2003', hire_date) >= 0
and months_between ('06/17/2003', hire_date) <= 12

29
 Statistical Functions
• These functions are used to aggregate records in a way defined by
the operation of the function itself, some of them are:
• Count() : to count the number of records
• Min(): to find the minimum value
• Max(): to find the maximum value
• Avg(): to calculate the average value
• Sum(): to add values
• Median: the middle value of a set of ordered values

30
 Statistical Functions (cont.)

• Examples:

Select count(*), sum(salary) from employees where salary >

5000;

Select min(salary), max(salary), avg(salary) from employees;

31
 Alias

• Use alias to make column name more readable in the results.

• Can be applied to column name or table name
• If need a name with spaces used it in between “”
• Example:
Select sum(salary) as sum from employees E;
Select sum(salary) as "sum of values" from employees E;

32
Between/IN operators

33
 Between/IN operators (cont.)
• Can use Between/in operators to specify range or group of values.
• List employees (id, last name) for those who get salary between 5000
and 10000:
Select employee_id, last_name from employees where salary between 5000 and
10000;
Equivalent to:
Select employee_id, last_name from employees where salary >= 5000 and
salary <= 10000;

• List employees details those salary in the range of values(2000, 3000,

8000)
Select * from employees where salary in (2000,3000,8000);
Equivalent to:
Select * from employees where salary = 2000
Or salary = 3000
Or salary = 8000;
34
Where with more than two conditions
• What is the output of this query?
select * from employees where salary > 5000 or salary < 
4000 and hire_date > '01/01/2006'order by hire_date desc

Any comments?!!

35
Where with more than two conditions (cont.)
• Always use round brackets to tell compiler the order by which conditions should
be executed.

select * from employees where 
(salary > 5000 or salary<4000)
 and hire_date > '01/01/2006'order by hire_date desc

36
 Like operator and Wildcards character (% ,
_)
• Wildcards characters are used to  substitute one or more characters in
a string. They are used with Like operator in the condition of a query to
filter records based on pattern matching.
• % : represent zero or more characters occurrence
• _ : represent a single character occurrence

• List all employees details those first name starts with “Alex”
Select * from employees where first_name like 'Alex%' order by salary desc;

• List all employees details those first name starts with “Alex” but name
length is 6 characters
Select * from employees where first_name like 'Alex__' order by salary desc;

37
Distinct Keyword
• To eliminate duplicated values or complete record , you can use
distinct
• Select distinct * from Table_Name -- for full record
• Select distinct c1,c2 from Table_Name -- for values in c1 and c2 only

38
Aggregation functions and records grouping:
Group by clause
• Aggregation functions:
• min,max,avg,sum,count,stdev .…
Select count(salary) from employees;

• Grouping: count the number of employees in every department

Select department_id, count(*) from employees group by department_id;

39
Having clause
• You can add a condition after grouping records using having clause,
e.g:

Select department_id, count(*) from employees

group by department_id having count(*) > 3;

Select department_id, count(*) from employees

group by department_id having min(salary) <
15000;

40
41
 Update clause
• is used to update values in a record or a group of records
Syntax:
Update [Table_name] set [column_name] = [value|sub-query]
{condition};

e.g:
Update Employees set salary = salary *1.2 where job_id = ‘IT_PROG’;

• For multiple columns:

Update Employees set salary = salary *1.2, manager_id = 100 where
job_id = ‘IT_PROG’;

42
 Insert clause
• When you need to add a new record use the insert command.
Syntax:
Insert into [Table_name] {columns_list} values [values_list| sub-query];
• If didn’t mention “columns_list” then must insert values in all columns.

Example:
Insert into employees (employee_id, email)
values (100,’[email protected]’);
• In case you need to insert values from another table you can use insert into select
clause.
INSERT INTO target_table (col1, col2, col3)
SELECT col1, col2, col3 FROM source_table WHERE
condition;

43
 Delete clause
• To delete one or more rows from a table
• Syntax:
• Delete from [Table_name] {condition} ;
• Example:
Delete from Employees where
employee_id = 100;

44
Joins
Joining two or more tables is the action of combining rows from those
tables based on related columns between them

45
Joins – inner join

46
Joins- Left join

47
 Natural clause
• is a type of tables’ join that requires the two tables to
have common column ( same name & data type), e.g
Select * from table_1 natural join table_2;

48
Join with Using keyword
• Use the USING keyword to specify the common columns where tables
can be joined upon
• Syntax:
Select * from table_1 join table_2 using (column1); -- this join is made on
a single column

• Example:
select * from employees t1 join departments t2 on t1.department_id =
t2.department_id;

49
Join with ON keyword
• Join tables with ON keyword to specify the common columns where the new
generated table will be generated.
• Doesn’t require similar column names
• Datatype must match
• Syntax:
Select * from table_1 Alias_1 {join_type} join table_2
Alias_2
ON Alias_1.column_name = Alias_2.column_name

• Example:
Select * from Employees e inner join Departments d
ON e.department_id = d.department_id;

50
 Cross join
• The CROSS JOIN is used to generate a paired
combination of each row of the first table with each row
of the second table. This join type is also known as
cartesian join.

51
Which one to use?!!
• Natural join: the engine will do the mapping between common columns
automatically
• Join with Using keyword: tables are joined only based on the specified common
column
• Join with ON keyword: this is the more general way to join tables where you
specify what columns need to be mapped for the join to be accomplished
• Cross join is easier to type but need to add condition to filter out unmatched values
which might impact performance.

52
 Set operators
• Set operators combine the results of two queries into a
single result. they operate by matching full record
similarity
• Union
• Union all
• Intersect
• minus

53
54
Union vs Join

55
 Union
SELECT first_name, last_name, email, 'contact‘ FROM
contacts
UNION
SELECT first_name, last_name, email, 'employee‘ FROM
employees;

56
Select statement summary

57
 Data Definition Language (DDL)
• This group of commands deals with the structure of a table in
terms of
• Table: create/drop/rename
• Columns: add/modify/rename/drop
• Every table must have a unique name within the schema
• Columns:
• Name should not exceed 30 characters
• Should start with a letter not a number!
• Must have a datatype
• Optionally can define one or more constraints

58
 Data Integrity
Data Integrity is used to maintain Accuracy and Consistency of data in the
Table.

59
 Constraints
• Define a Primary key: employee_id number primary key;
If composite, add new line as follows:
primary key (employee_id, phone_num);
• Define Foreign key: sid number references Student(sid);
if multiple columns, add new line as follows:
foreign key (sid, email) references Student(sid,email);
• Default value: student_id number(5) default(0);
• Check format: email varchar(50) check(email like ‘%@%.com’);
• Unique value: mobile_Number number(11) unique;
• Prevent null values: hire_date date not null;
• Can check all constraints by querying the table “all_constraints”

60
 Primary Key
A primary key is a column in a relational database that's used to uniquely
identify each record in a table. When selecting a primary key, you'll need to
choose a column that contains unique values for all rows.

- No Duplicated values
- No Null values
- Should not be changed (if auto generate -> surrogate key)

61
 Foreign Key
• A FOREIGN KEY is a field (or collection of fields) in one
table, that refers to the PRIMARY KEY in another table
(or a field with PK characteristics)
• Can contain duplicated values
• Must stick with values in the PK of parent table

62
Relationship Cardinality

63
Relationship Cardinality on ERD

64
 Create clause
• Syntax:
Create Table|View [Table_Name|View_Name]
(
[column_name] [datatype] {constraints},
[column_name] [datatype] {constraints}
);
• datatypes: https://www.techonthenet.com/oracle/datatypes.php

• Example:
create table student (
student_id number(10) primary key,
student_Name varchar(255)
);

65
Create clause (2)
create table customer
(
cid number(8) primary key,
cName varchar(200),
cmobile number(11)

);

create table orders

(
ord_id number(10) primary key,
ord_quantity number (2),
ord_date date,
ord_cid number(8) references customer(cid)

);

66
Exercise
• Consider the ERD given below, create the corresponding
tables and define the necessary constraints(PK and FK)
as needed.

67
Representing Many-to-Many
relationship

68
Representing Many-to-Many
relationship (Cont.)
• Use Junction Table to replace simplify the many-to-
many relationship into two one-to-many relationships.
• The junction table should contain at least two fields those
represent FKs (one for each parent)

69
ERD Revisited

70
 ERD Revisited (Cont.)
• Weak Entity : is an entity that its existence depends on another entity.
• e.x.: employee’s family data for health insurance

• What if an employee left the company, how to deal with his/her dependents
data?

71
 Cascade Delete
• The best way to make sure all dependents in weak entity
are deleted once an employee left the company is to add
on delete cascade to the FK of the weak entity.
• What is a foreign key with Cascade DELETE?
• A foreign key with cascade delete means that if a record in
the parent table is deleted, then the corresponding
records in the child table will automatically be deleted.
This is called a cascade delete in Oracle.
CREATE TABLE table_name (

column1 datatype not null,

column2 datatype not null REFERENCES parent_table (column1, column2) ON
DELETE CASCADE
); 72
 Cascade Delete (cont.)
CREATE TABLE table_name (
column1 datatype null/not null,
column2 datatype null/not null,
CONSTRAINT fk_column FOREIGN KEY (column1, column2,
column_n) REFERENCES parent_table (column1,
column2, column_n) ON DELETE CASCADE
);

NOTE: CONSTRAINT fk_column is an optional part that is used to give a more

readable name to the constraint instead of the default name
To check table constraints:
select * from USER_CONSTRAINTS where table_name
='EMPLOYEES';

73
Cascade Delete - Example

74
 Create clause (3)
• Can create a table from another table using create as select
• It will copy data from another table to the newly created table with all
the defined constraints in the original table but the PK!
• Example:
Create table Student as ( select * from
Employees where job_id = ‘IT_PROG’);

Create table Student as ( select Employee_id,

Last_name from Employees where job_id =
‘IT_PROG’);

75
 Alter clause
• Is used when you need to modify the structure of the table
• Syntax:
Alter Table [Table_Name] <<Command>>
<<Command>> can be:
- To add columns:
Alter Table T1 add [column_name] [column definition]
{constraints};
- To modify columns:
Alter Table T1 modify [column_name] [column definition]
{constraints};
- To drop columns:
Alter Table T1 drop Column [column_name];
- To rename columns:
Alter Table T1 rename Column [old column_name] to [new
column_name]; 76
 What is Normalization?
• Is the process of organizing large database tables into
smaller but relevant tables in order to eliminate data
redundancy in the database - table below is in 0NF
Order_No Customer_id Customer_Name
Products-
Purchased Purshase_date Shop_details Local_Tax Total_Amount (£) Discount
Total_amount_Paid
(£)

Cheese £3 ,
Tomatos £1.5, Toast
£0.45, Tooth Brush
502-65247 52435 Mark Smith £3.99 25/10/2016 502 London 7.00% 9.5658 0.00% 9.6

Prefum £80, Katel

502-21460 52438 Lisa Sudgon £20, Gift Box £15 22/10/2016 502 London 7.00% 123.05 0.00% 123.1

Mobile Phone
514-42695 52435 Mark Smith £200, Drill £59.99 31/10/2016 514 Manchester 5.00% 272.9895 0.00% 273.0

T-Shirt £19.99, Belt

523-42134 52477 Peter Alison £2.99, Cola £1.18 2/12/2016 523 Manchester 5.00% 25.368 0.00% 25.4

Kitchen
Appliances£99.99,
KitKat £0.99, Cola
593-55426 52791 Steven Luois £1.18 26/12/2016 593 Newcastle 3.00% 105.2557 3.00% 102.1

Coat £199.99, hand

watch £80.99, Gift
502-21460 52438 Lisa Sudgon card £2.99 26/12/2016 502 London 7.00% 303.8479 3.00% 294.7
 Normal Forms (NFs)
• 1st Normal Form
• Each cell contains a single value
• Data in columns are of same type
• No repetitive columns ( e.g. employee1, employee2 .. Etc)
• Rows can be uniquely identified (PK) – might need to add new column for this!
• 2nd Normal Form
• Is in 1NF
• Can introduce FK for relationships
• 3rd Normal Form
• Is in 2NF
• Transitive dependencies are eliminated – columns those are dependent on non-
key column are moved to a new table
 SQL Injection
• Is making the query condition always true to reveal data of the table! But
How?!!
• Assume you have the java code:
String sql = “ select * from password_Table where password= ‘“ + pass + “’”;
sql.execute();

• You will read password from login page then validate it with what is stored
in the database , if user can be authenticated you will let him access the
system otherwise will stop him/her.

• What if a someone typed in the password field the following:

‘ or 1=1 ––

How SQL will process with this?

79
Sub-Query – advanced SQL
• It’s a query within a query!
• select (SQ1) {instead of column} from (SQ2) { instead of table name}
where column = (SQ3) {instead of value} / in (SQ4) {instead of values}
• Count the number of employees those joined the company in 2007 or
January 2005
select count(*) from (Select * from employees where hire_date like '%-07'
or hire_date like '%JAN-05' order by 6 ) T1;

• List all employees details those salary is greater than the average
salary of the company
select * from employees where salary > (select avg(salary) from
employees);

80
Sub-Query
SELECT
MAX( list_price )
FROM
products;

To select the detailed information of the most expensive products,

you use the list price above (8867.99) in the following query:

SELECT product_id, product_name, list_price FROM

products WHERE list_price = 8867.99;

81

OK, but what can be the problem?

 Sub-Query
What if price changed!
Better way is to dynamically query price, use sub-query to retrieve max
price every time you run the query, so you won't need to be concerned
about price changes any more 

Outer Query

SELECT product_id, product_name, list_price FROM products

WHERE list_price =
( SELECT MAX( list_price ) FROM products ) ;

Sub-Query

82
 Sub-Query

• A subquery which is nested within the FROM clause of

the SELECT statement is called an inline view. Note that other RDBMS
such as MySQL and PostgreSQL use the term derived table instead of
the inline view.
• A subquery nested in the WHERE clause of the SELECT statement is
called a nested subquery.
• A subquery can contain another subquery. Oracle allows you to have an
unlimited number of subquery levels in the FROM clause of the top-level
query and up to 255 subquery levels in the WHERE clause.

83
Sample Database Schema:

https://www.oracletutorial.com/getting-started/oracle-sample-database/

84
Oracle Subquery examples
• A) Oracle subquery in the SELECT clause example
SELECT
product_name,
list_price,
ROUND(
(
SELECT
AVG( list_price )
FROM
products p1
WHERE
p1. category_id = p2.category_id
),
2
) avg_list_price
FROM
products p2
ORDER BY
85
product_name;
Oracle Subquery examples
• B) Oracle subquery in the FROM clause example
SELECT
order_id,
order_value
FROM
(
SELECT
order_id,
SUM( quantity * unit_price ) order_value
FROM
order_items
GROUP BY
order_id
ORDER BY
order_value DESC
)
FETCH FIRST 10 ROWS ONLY;
86
Oracle Subquery examples
• C) Oracle subquery with comparison operators example
SELECT
product_id,
product_name,
list_price
FROM
products
WHERE
list_price > (
SELECT
AVG( list_price )
FROM
products
)
ORDER BY
product_name;

87
Oracle Subquery examples
• D) Oracle subquery with IN and NOT IN operators
SELECT
employee_id,
first_name,
last_name
FROM
employees
WHERE
employee_id IN(
SELECT
salesman_id
FROM
orders
INNER JOIN order_items
USING(order_id)
WHERE
status = 'Shipped'
GROUP BY
salesman_id,
EXTRACT(
YEAR
FROM
order_date
)
HAVING
SUM( quantity * unit_price ) >= 1000000
AND EXTRACT(
YEAR
FROM
order_date) = 2017
AND salesman_id IS NOT NULL
)
ORDER BY
first_name,
last_name;
88
 Correlated Subquery
• A correlated subquery is a way of reading values in each row and comparing those
values in each row with related data.
• A correlated subquery is evaluated once for each row processed by the parent
query. The subquery returns a value for each row processed by the parent query
unlike the normal subquery which executes only once and returns the data to the
parent query for processing.

SELECT last_name, salary, department_id

FROM employees outer
WHERE salary > (SELECT AVG(salary)
FROM employees
WHERE department_id = outer.department_id);

The inner query (correlated subquery) generates the avg salary for each department_id
processed by the outer query.

89
Oracle Subquery

90
Exercise
Using the HR Schema,
• 1. List employees those are not managers.
• 2. List employees those getting salary less than average salary
given to finance department.
• 3. List employees those getting salary less than the average
salary given in the department they are working for.
• 4. List the departments names that the sum of their employee's
salary is greater than the average salary of the company.

91
Quiz #1
Count the number of departments that the sum of their
employee's salary is greater than the average salary of the
employees in the sales department.

92
With Keyword
• A more organized way to represent some sub-queries

With
T1 as (select * from table_a),
T2 as (select * from table_b)
Select * from T1, T2 where T1.column =
T2.column;

93
 Views
• An Oracle VIEW, in essence, is a virtual table that does not physically exist.
• It can be created out of multiple tables
• View is a reference to tables
• Can be used to provide an application with controlled access to data including:
• Limited privileges (e,g, read only)
• Hide unnecessary columns
• Data in views can be updated automatically once updated in the corresponding
table.
CREATE VIEW view_name AS
(SELECT columns
FROM tables
[WHERE conditions]);

94
 Views(2)

• Views definition can be updated without the need to

drop and re-create using CREATE OR REPLACE
statement

CREATE OR REPLACE VIEW view_name AS

(SELECT columns FROM table
WHERE conditions);

EX:
Create a view to list employee_id, last_name from table employees and
department_name, manager_id from departments table.

95
 Indexing
• Indexing is important to improve the performance of data retrieval
and operations on data.
• B-Tree is the default data structure used in oracle indexing

CREATE INDEX new_index_name ON table_name (column_name)

Example:
CREATE TABLE members(
member_id INT,
first_name VARCHAR2(100) NOT NULL,
last_name VARCHAR2(100) NOT NULL,
gender CHAR(1) NOT NULL,
dob DATE NOT NULL,
email VARCHAR2(255) NOT NULL,
PRIMARY KEY(member_id)
);

96
• To view all the indexes on a table can query
user_indexes or all_indexes
SELECT
index_name,
index_type,
visibility,
status
FROM
all_indexes
WHERE
table_name = 'MEMBERS';

97
 PL/SQL
• PL/SQL stands for “Procedural Language extensions to the Structured
Query Language.”
• PL/SQL is Oracle Corporation’s procedural extension for SQL and the
Oracle relational database. It is a high-performance, highly integrated
database language.
• SQL is declarative language while PL/SQL is procedure language

98
99
Variables Initialization
Declare
message varchar(255) := 'Welcome to my Calculator';
num1 number(2) := 5;
num2 number(2) ;
res number(2);
Begin
num2 := :please_enter_second_number ;
res := num1 + num2;
dbms_output.put_line(message);
dbms_output.put_line('the output is:' || res);
End;

100
 Variables Initialization from a Table
declare
emp_lname varchar(100);
salary number(5);

begin
select last_name,salary into emp_lname,salary from employees
where employee_id = 100;
dbms_output.put_line('Employees last name is: ' ||
emp_lname);
dbms_output.put_line('Employees salary = ' || salary);

end

101
Exercise:
Create a pl/sql block to calculate and print out the
average salary of all employees in the company.

102
Variables Initialization from a Table (2)
declare
emp_lname employees.last_name%Type;
salary employees.salary%type;

begin
select last_name,salary into emp_lname,salary from
employees where employee_id = 100;
dbms_output.put_line('Employees last name is: ' ||
emp_lname);
dbms_output.put_line('Employees salary = ' || salary);

end
103
IF .. THEN .. ELSE
IF condition THEN
statements;
ELSE
else_statements;
END IF;

104
IF .. THEN .. ELSE (2)
Example:
….
If Salary > 40000 Then
dbms_output.put_line(‘Rich’);
ELSE
dbms_output.put_line(‘Normal’);
END IF;
…

105
Exercise:
Create a pl/sql block to calculate the average
salary of all employees in the company and print
out ‘Good’ if average was over 5k and ‘Bad’ if
otherwise.

106
 FOR … LOOP

FOR loop_counter IN [REVERSE] lowest_number..highest_number

LOOP
{...statements...}
END LOOP;

107
FOR … LOOP (2)
Example:
….
FOR x in 1..10
LOOP
dbms_output.put_line(x);
END LOOP;
…

108
Exercise:
Create a pl/sql block to check the salary of every
employee in the company and print out ‘Good’ if
salary was over 5k and ‘Bad’ if otherwise.

109
 LOOP .. EXIT WHEN

LOOP
{...statements...}
EXIT WHEN (condition fail);
END LOOP;

110
 LOOP .. EXIT WHEN(2)

Example:
….
LOOP
EXIT WHEN x> 10;
dbms_output.put_line(x);
END LOOP;
…

111
 Quiz(2)
• Write a PL/SQL block to calculate the minimum salary of
every department and print out
‘the salary of department: {department_name} is high’
if the minimum salary was equal to or greater than the
total average salary of the salaries given to all employees in
the company, and print out
‘the salary of department: {department_name} is low’
otherwise.

112
 PL/SQL Exception handling
• Exception is a change in the program flow that happens due to
violating any rule in the database. It interrupts the flow of the
main program.
• for example, if a user has queried a table but no data was
returned. So no_data_found exception is raised.
• There are two types of exceptions:
• System defined: those are predefined exceptions by the
system
• User defined: user can define their own exception if needed

113
 PL/SQL Exception handling(2)
DECLARE

<declarations section>

BEGIN Example:
<executable command(s)>

EXCEPTION
….
WHEN exception1 THEN begin
exception1-handling-statements
Select salary into sal from employees where
WHEN exception2 THEN employee_id = 99;
exception2-handling-statements
….
WHEN exception3 THEN
exception3-handling-statements Exception
........ when no_data_found then
WHEN others THEN
exception3-handling-statements
dbms_output.put_line(‘No data found’)
END; End;

114
 PL/SQL Exception handling(3)
• Predefined exceptions:
https://docs.oracle.com/cd/B10501_01/appdev.920/a96624/07_errs.htm

115
Exercise:
Create a pl/sql block to handle the exception
raised by retrieving multiple values of salaries in a
select statement (i.e. more than one record)

116
 PL/SQL Exception handling(4)
• User defined exception might be used to raise an exception at any point in
the program as needed.
• User defined exceptions might be used to handle logical errors!
• Need to use RAISE keyword to throw an exception.

DECLARE
exception_name EXCEPTION;
BEGIN
IF condition THEN
RAISE exception_name;
END IF;
EXCEPTION
WHEN exception_name THEN
statement;
END; 117
Exercise:
Create a pl/sql block to handle the exception
raised by retrieving a value of salary of an
employee that is greater than 20k and print out
the message: ‘salary is out of range’

118
GOTO
statement
• When you need to transfer
the control flow to a specific
statement that is out of the
sequence of execution you
might use GOTO statement to
do that.
• Must use label to point out
the target statement
• Cant goto a statement inside
if block
• But! Be carful of spaghetti
code!!
119
 CURSOR
• A cursor is a SELECT statement that is defined within
the declaration section of your PLSQL code.
• It can come in three forms:
• Simple cursor without parameters
• Cursor with parameters
• Cursor with return clause

Syntax:
Cursor cursor_name {(optional parameters)}
{RETURN member_Variable%ROWTYPE}
IS
Select_statement;

120
CURSOR(2)
• Cursor can be either:
• Implicit: generated and managed by oracle engine for every SQL statement
• Explicit: defined and managed by user
• includes 4 steps:
• Declare cursor
• Open cursor
• Fetch data
• Close cursor

121
EXPLICIT CURSOR STEPS
DECLARE
CURSOR <cursor_name> IS <SELECT statement>
<cursor_variable declaration>
BEGIN
OPEN <cursor_name>;
FETCH <cursor_name> INTO <cursor_variable>;
.
.
CLOSE <cursor_name>;
END;

122
CURSOR ATTRIBUTES

123
 EXPLICIT CURSOR EXAMPLE
DECLARE
c_id customers.id%type;
c_name customers.name%type;
c_addr customers.address%type;
CURSOR c_customers is
SELECT id, name, address FROM customers;
BEGIN
OPEN c_customers;
LOOP
FETCH c_customers into c_id, c_name, c_addr;
EXIT WHEN c_customers%notfound;
dbms_output.put_line(c_id || ' ' || c_name || ' ' || c_addr);
END LOOP;
CLOSE c_customers;
END;

124
Exercise:
Use Explicit Cursor to Write a PL/SQL block to calculate the average salary
of every department and print out
‘the salary of department: {department_id} is high’
if the average salary was equal to or greater than the total average salary
of the salaries given to all employees in the company, and print out
‘the salary of department: {department_id} is low’

otherwise.

125
PL/SQL Procedure
• Is a subprogram/unit/module that perform a specific task.
• Syntax:
CREATE [OR REPLACE] PROCEDURE procedure_name
[(parameter_name [IN | OUT | IN OUT] type [, ...])]
{IS | AS}
BEGIN
< procedure_body >
END procedure_name;

126
PL/SQL Procedure (2)
• Example:

Create or replace procedure greetings As

Begin
Dbms_output.put_line(‘hello world!’);
End greetings;

127
PL/SQL Procedure (3)
• Example – with parameters:

Create or replace procedure sum(a in number, b in

number, c out number) As
Begin
c:= a + b;
dbms_output.put_line(‘result = ‘ || c);
End sum;

128
PL/SQL Procedure (4)
• Example – with parameters:

Create or replace procedure sum(a in number, b in

number, c in out number) As
Begin
c:= a + b + c;
dbms_output.put_line(‘result = ‘ || c);
End sum;

129
Implicit Cursor
• Implicit cursors are automatically created and destroyed by the Oracle
server whenever we execute an SQL statement inside a PL/SQL block.
• The Oracle server by default opens, fetches, processes, and closes the
implicit cursor automatically without the need of a programmer
intervention.
• So it is faster than explicit cursor:
• no open, fetch, close!
• How to use it?!

130
Implicit Cursor(2)
• Cursor FOR LOOP: might use for loop to move in implicit cursor
records one by one.
• Is an extension to the numeric for loop.
• The cursor FOR LOOP implicitly creates its loop index as a record
variable with the row type in which the cursor returns and then opens
the cursor.
FOR record IN cursor_name LOOP
process_record_statements;
END LOOP;

131
• Example – using cursor for loop:

declare
cursor im_cur is select * from emp_001 where salary > 10000;
begin
for emp_sal in im_cur loop
dbms_output.put_line(emp_sal.last_name || ' ' ||
emp_sal.salary);

end loop;

end;

132
Implicit Cursor(3)
• Can even use select statement in for loop without declaring a cursor!
• BUT…Better to declare cursor if the select statement is long!

FOR record IN (select_statement) LOOP

process_record_statements;
END LOOP;

133
• Example –for loop:

begin
for emp_sal in (select * from emp_001 where salary >
10000) loop
dbms_output.put_line(emp_sal.last_name || ' ' ||
emp_sal.salary);

end loop;

end;

134
PL/SQL procedure –
UPDATE/INSERT/DELETE
• A stored procedure is usually used to perform some transactions on
the named tables in terms of:
• Updating records
• Inserting new records
• Deleting records
• The OUT/ INOUT parameter is used to display count, usually!
• So don’t use it for returning result out of the procedure.

135
Example:
create or replace procedure proc3(emp_id in emp_001.employee_id%type, sal in
emp_001.salary%type, r out number) as
begin
update emp_001 set salary = sal where employee_id = emp_id;

if sql%notfound then
r := 0;
else
r := sql%rowcount;
end if;
end proc3;

--/////////////////////// Main////////////////////
declare
r number;
begin
proc3(100,24000, r);
dbms_output.put_line(r || ' row(s) updated');
end;
136
Exercise:
Write a PL/SQL procedure to update the salary of
employee(s) in company those original salary is less
than 8K by 1.2 and print out the number of records
affected by the update statement.

137
PL/SQL Function
• A function in PL/SQL is the same as procedure, however, it has a return value!
• Use it to perform computation, unlike procedure that can be used to perform
an action.
CREATE [OR REPLACE] FUNCTION function_name
[(parameter_name [IN | OUT | IN OUT] type [, ...])]
RETURN return_datatype
{IS | AS}
BEGIN
< function_body >
END [function_name];

138
Example:
CREATE OR REPLACE FUNCTION totalCustomers
RETURN number as
total number(3) := 0;
BEGIN
SELECT count(*) into total
FROM emp_001;

RETURN total;
END;

begin
dbms_output.put_line(totalCustomers());
end;
139
Triggers
• Triggers are stored programs, which are automatically fired when some events
occur.
• Can be executed in response to:
• DML
• DDL
• DB Operations
• Trigger can be created, dropped, enabled and disabled
• Triggers can be fired BEFORE or AFTER an event occurrences
• Trigger can be a table level or row level trigger
• Table level (statement trigger) means it is fired before or after a query is executed
• Row level(row trigger) means it is fired for each row (before or after the action is performed)

140
Triggers(2)
Benefits of Triggers
• Generating some derived column values automatically
• Enforcing referential integrity
• Event logging and storing information on table access
• Auditing
• Synchronous replication of tables
• Imposing security authorizations
• Preventing invalid transactions

141
Triggers(3)
• To check triggers defined on a table you can query ‘all_triggers’ table
and specify table name.
• Ex:
select * from all_triggers where table_name = 'EMPLOYEES';

142
 Triggers(4)
Syntax
CREATE [OR REPLACE ] TRIGGER trigger_name
{BEFORE | AFTER | INSTEAD OF }
{INSERT [OR] | UPDATE [OR] | DELETE}
[OF col_name]
ON table_name
[REFERENCING OLD AS o NEW AS n]
[FOR EACH ROW]
WHEN (condition)
DECLARE
Declaration-statements
BEGIN
Executable-statements
EXCEPTION
Exception-handling-statements
END;

143
Triggers(5) - EXAMPLE
CREATE OR REPLACE TRIGGER TRIG1
BEFORE DELETE
ON EMP_001
FOR EACH ROW
DECLARE
DEL_ERR EXCEPTION;
BEGIN
IF(:OLD.JOB_ID = 'AD_PRES' OR :OLD.JOB_ID = 'AD_VP') THEN
RAISE DEL_ERR;
ELSE
DBMS_OUTPUT.PUT_LINE('EMPLOYEE :' || :OLD.EMPLOYEE_ID || ' IS DELETED SUCCESSFULLY!');
END IF;
EXCEPTION
WHEN DEL_ERR THEN
RAISE_APPLICATION_ERROR (-20001, 'EMPLOYEE CANNOT BE DLETED!', FALSE);
END;
144
Triggers(6)
• :OLD, :NEW : are special variables that you can use with PL/SQL
triggers without explicitly defining them
• :NEW : represent new table row.
• :OLD : represents old table row.
• They represent values in a specific field
• Insert: :OLD = null, :NEW= [new_value]
• Update: :OLD=[old_vale], :NEW=[new_value]
• Delete: :OLD=[old_value], :NEW= null

145
Triggers(7)
• RAISE_APPLICATION_ERROR: is a built in procedure in the
DBMS_STANDARD package that is used to halt the execution of a
program.
• raise_application_error( error_number, message [, {TRUE | FALSE}] );
• Error_number: negative integer range between -20999 to -20000
• Message: your defined error message to display
• TRUE: added to all previous errors
• FALSE: replaces all previous errors

146
Triggers(8)
CREATE OR REPLACE TRIGGER TRIG1
BEFORE DELETE
ON EMP_001
FOR EACH ROW
WHEN(OLD.JOB_ID = 'AD_PRES' OR OLD.JOB_ID = 'AD_VP')

BEGIN
RAISE_APPLICATION_ERROR (-20001, 'EMPLOYEE CANNOT BE DLETED!', FALSE);

END;

147
Triggers(9)
• To Enable a trigger:
• ALTER TRIGGER TRIG1 ENABLE;
• To disable a trigger:
• ALTER TRIGGER TRIG1 DISABLE;
• To drop a trigger
• DROP TRIGGER TRIG1;

148