Undergraduate
Database Management
Systems
Lecture 13
Hamza Shaukat
Hamza.Shaukat@itu.edu.pk
Information Technology University (ITU)
Faculty of Engineering
Full Outer Join
The full outer join combines the results of both the left join and the right join.
If the rows in the joined table do not match, the full outer join sets NULL
values for every column of the table that does not have the matching
row.If a row from one table matches a row in another table, the result
row will contain columns populated from columns of rows from both
tables.
Syntax: SELECT * FROM A
FULL [OUTER] JOIN B on A.id = B.id;
Information Technology University (ITU)
Faculty of Engineering
Example
Department
Employees
Information Technology University (ITU)
Faculty of Engineering
Example
SELECT
employee_name,
department_name
FROM
employees e
FULL OUTER JOIN departments d
ON d.department_id = e.department_id;
Information Technology University (ITU)
Faculty of Engineering
Introduction
PL/SQL stands for Procedural Language/SQL.
PL/SQL extends SQL by adding constructs found in procedural
languages, resulting in a structural language that is more powerful
than SQL.
The basic unit in PL/SQL is a block. All PL/SQL programs are made up
of blocks, which can be nested within each other.
A block has the following structure:
Information Technology University (ITU)
Faculty of Engineering
Structure of a Block
DECLARE
/* Declarative section */
BEGIN
/* Executable section: procedural and SQL statements
go here. This is the only section of the block that
is required. */
EXCEPTION
/* Exception handling section: error handling
statements go here. */
Information Technology University (ITU) END;
Faculty of Engineering
Coding a Block
Only the executable section is required. The other sections are
optional.
The only SQL statements allowed in a PL/SQL program DML
statements such as SELECT, INSERT, UPDATE, DELETE plus some
transaction control.
Data definition statements like CREATE, DROP, or ALTER are not
allowed.
The executable section also contains constructs such as assignments,
branches, loops, procedure calls, and triggers.
PL/SQL is not case sensitive.
C style comments (/* ... */) may be used.
Information Technology University (ITU)
Faculty of Engineering
Variables and Types
Information is transmitted between a PL/SQL program and the
database through variables.
Every variable has a specific type associated with it.
Types in PL/SQL can be tricky. In many cases, a PL/SQL variable will
be used to manipulate data stored in an existing relation. In this
case, it is essential that the variable have the same type as the
relation column. If there is any type mismatch, variable
assignments and comparisons may not work the way you expect.
To be safe, instead of hard coding the type of a variable, you should
use the %TYPE operator. For example:
id emp.empno%TYPE;
gives PL/SQL variable id whatever type was declared for the empno
column in relation emp.
Information Technology University (ITU)
Faculty of Engineering
Initializing a Variable
The initial value of any variable, regardless of its type, is NULL.
We can assign values to variables, using the ":=" operator.
The assignment can occur either immediately after the type of the
variable is declared, or anywhere in the executable portion of the
program.
Example
DECLARE
X NUMBER := 10;
Information Technology University (ITU)
Faculty of Engineering
Structure
create [or replace] procedure procedure_name(parameter_list)
language plpgsql
as $$
declare
-- variable declaration
begin
-- stored procedure body
end; $$
10
Information Technology University (ITU)
Faculty of Engineering
Example
CREATE PROCEDURE hello_world()
LANGUAGE plpgsql
AS $$
BEGIN
RAISE NOTICE ‘Hello world’;
END
$$;
For Execution: call hello_world();
11
Information Technology University (ITU)
Faculty of Engineering
Example
CREATE PROCEDURE hello(name varchar(30))
LANGUAGE plpgsql
AS $$
BEGIN
RAISE NOTICE ‘Hello %’,name;
END
$$;
Execution: call hello(‘Sqlshack’);
12
Information Technology University (ITU)
Faculty of Engineering
Example
CREATE TABLE accounts (
user_id integer PRIMARY KEY,
firstname VARCHAR ( 50 ) NOT NULL,
lastname VARCHAR ( 50 ) NOT NULL,
email VARCHAR ( 255 ) UNIQUE NOT NULL
);
CREATE PROCEDURE add_data(id integer,firstname
varchar(50),lastname varchar(50), email varchar(255) )
LANGUAGE SQL
AS $$
INSERT INTO accounts VALUES (id,firstname,lastname,email);
$$;
CALL add_data(1, ‘Daniel’,’Smith’,’dsmith@gmail.com’);
CALL add_data(2, ‘John’,’Rambo’,’jrambo@gmail.com’);
13
Information Technology University (ITU)
Faculty of Engineering
14
Information Technology University (ITU)
Faculty of Engineering
Looping Syntax
[ <<label>> ]
for loop_counter in [ reverse ] from.. to [ by
step ] loop
statements
end loop [ label ];
Example
$$
begin Output:
for counter in 1..5 loop
raise notice 'counter: %', counter; NOTICE: Counter: 1
end loop; NOTICE: Counter: 2
NOTICE: Counter: 3
end; $$
NOTICE: Counter: 4
NOTICE: Counter: 5
15
Information Technology University (ITU)
Faculty of Engineering
Example
CREATE OR REPLACE PROCEDURE DeleteAccounts(MyId integer)
LANGUAGE plpgsql
AS $$
BEGIN
DELETE from accounts WHERE user_id=MyId;
END $$;
CREATE OR REPLACE PROCEDURE DeleteAccounts(MyId integer)
CALL DeleteAccounts(2);
16
Information Technology University (ITU)
Faculty of Engineering
If Condition
if condition then
statements;
end if;
17
Information Technology University (ITU)
Faculty of Engineering
Example
$$
DECLARE
a integer := 10;
b integer := 20;
BEGIN
IF a > b THEN
RAISE NOTICE 'a is greater than b';
END IF;
IF a < b THEN
RAISE NOTICE 'a is less than b';
END IF;
IF a = b THEN
RAISE NOTICE 'a is equal to b';
END IF;
END $$;
18
Information Technology University (ITU)
Faculty of Engineering
Example
CREATE OR REPLACE PROCEDURE genre_traverse() LANGUAGE plpgsql AS $$
# DECLARE
# genre_rec record;
# BEGIN
# for genre_rec in (select "GenreId","Name" from public."Genre" order by "GenreId")
# loop
# RAISE NOTICE 'Genre Id is : % , Name is : %', genre_rec."GenreId",genre_rec."Name";
end loop;
$# END;
$$ ;
19
Information Technology University (ITU)
Faculty of Engineering
Example
procedure_demo=# call genre_traverse();
NOTICE: Genre Id is : 1 , Name is : test
NOTICE: Genre Id is : 2 , Name is : Jazz
NOTICE: Genre Id is : 3 , Name is : Metal
NOTICE: Genre Id is : 4 , Name is : Alternative & Punk
NOTICE: Genre Id is : 5 , Name is : Rock And Roll
NOTICE: Genre Id is : 6 , Name is : Blues
NOTICE: Genre Id is : 7 , Name is : Latin
NOTICE: Genre Id is : 8 , Name is : Reggae
NOTICE: Genre Id is : 9 , Name is : Pop
NOTICE: Genre Id is : 10 , Name is : Soundtrack
NOTICE: Genre Id is : 11 , Name is : Bossa Nova
NOTICE: Genre Id is : 12 , Name is : Easy Listening
NOTICE: Genre Id is : 13 , Name is : Heavy Metal
NOTICE: Genre Id is : 14 , Name is : R&B/Soul
20
Information Technology University (ITU)
Faculty of Engineering
Question
Write a procedure to insert data in the Employee name and raise a note when
inserted
21
Information Technology University (ITU)
Faculty of Engineering
Functions
A PL/SQL function declaration is similar to a procedure declaration--;except that the
function returns a value of a predefined data type.
Syntax:
create [or replace] function function_name(param_list)
returns return_type
language plpgsql
as
$$
declare
-- variable declaration
begin
-- logic
end;
$$
22
Information Technology University (ITU)
Faculty of Engineering
Example
testdb# select * from COMPANY;
23
Information Technology University (ITU)
Faculty of Engineering
Example
CREATE OR REPLACE FUNCTION totalRecords ()
LANGUAGE plpgsql
RETURNS integer
as $$
declare
total integer;
BEGIN
SELECT count(*) into total FROM COMPANY;
RETURN total;
END;
$$
Execution:
testdb=# select totalRecords();
24
Information Technology University (ITU)
Faculty of Engineering
Example
Create function get_car_Price1(Price_from int, Price_to int)
returns int
language plpgsql
as
$$
Declare
Car_count integer;
# Begin
select count(*)
into Car_count
from Car
where car_price between Price_from and Price_to;
return Car_count;
End;
$$;
25
Information Technology University (ITU)
Faculty of Engineering
Example
Select get_car_Price(26000,70000);
26
Information Technology University (ITU)
Faculty of Engineering
Question
Write a function to return an average value of the prople who’s deaprartment is between 30 to
90
27
Information Technology University (ITU)
Faculty of Engineering
Union
Table 1
Table 2
28
Information Technology University (ITU)
Faculty of Engineering
Example
SELECT * FROM top_rated_films
UNION
SELECT * FROM most_popular_films;
29
Information Technology University (ITU)
Faculty of Engineering
Example
SELECT * FROM top_rated_films
UNION ALL
SELECT * FROM most_popular_films;
30
Information Technology University (ITU)
Faculty of Engineering
Intersect
SELECT select_list
FROM A
INTERSECT
SELECT select_list
FROM B;
31
Information Technology University (ITU)
Faculty of Engineering
Example
SELECT * FROM most_popular_films
INTERSECT
SELECT * FROM top_rated_films;
32
Information Technology University (ITU)
Faculty of Engineering
Excerpt
SELECT select_list
FROM A
EXCEPT
SELECT select_list
FROM B;
33
Information Technology University (ITU)
Faculty of Engineering
SELECT * FROM top_rated_films
EXCEPT
SELECT * FROM most_popular_films;
34
Information Technology University (ITU)
Faculty of Engineering
