Chapter 8

Introduction to SQL

1
Chapter 8

Introduction to SQL

DDL
• Create database Command
• Create table command
• Alter table command

2
Introduction to SQL Language
• The name SQL stands for Structured Query Language.

• It is pronounced “S-Q-L” and can also be pronounced

“sequel.”

• SQL is a standard language for accessing and manipulating

databases.

• SQL is a computer language designed to get information from

data that is stored in a relational database.

3
Introduction to SQL Language(Cont..)
• SQL is different from most other computer languages.
• With SQL, you describe the type of information you want. The
computer then determines the best procedure to use to
obtain it and runs that procedure. This is called a declarative
computer language because the focus is on the result
• You specify what the result should look like. The computer is
allowed to use any method of processing as long as it obtains
the correct result.

4
Introduction to SQL Language(Cont..)
• Most other computer languages are procedural. These are
languages like C, Cobol, Java, Assembler, Fortran, Visual Basic,
and others. In these languages, you describe the procedure
that will be applied to the data; you do not describe the
result. The result is whatever emerges from applying the
procedure to the data.
• SQL is a nonprocedural language, in contrast to the
procedural or third generation languages (3GLs) such as
COBOL and C that had been created up to that time.
Introduction to SQL Language(Cont..)

• SQL describes what data to retrieve, delete, or insert, rather

than how to perform the operation.
• Two standards organizations, the American National
Standards Institute (ANSI) and the International Standards
Organization (ISO), currently promote SQL standards to
industry.

6
What Can SQL do?
• SQL can execute queries against a database
• SQL can retrieve data from a database
• SQL can insert records in a database
• SQL can update records in a database
• SQL can delete records from a database
• SQL can create new databases
What Can SQL do? (Cont..)

• SQL can create new tables in a database

• SQL can create stored procedures in a database
• SQL can create views in a database
• SQL can set permissions on tables, procedures, and views

8
SQL DML and DDL
• SQL can be divided into two parts: The Data Manipulation
Language (DML) and the Data Definition Language (DDL).

DML

• The query and update commands form the DML part of SQL:

– SELECT - extracts data from a database

– UPDATE - updates data in a database

– DELETE - deletes data from a database

DDL
• The DDL part of SQL permits database tables to be created or
deleted. It also defines indexes (keys), specifies links between
tables, and imposes constraints between tables. The most
important DDL statements in SQL are:
– CREATE DATABASE - creates a new database

– ALTER DATABASE - modifies a database

– CREATE TABLE - creates a new table

DDL

– ALTER TABLE - modifies a table

– DROP TABLE - deletes a table
– CREATE INDEX - creates an index (search key)
– DROP INDEX - deletes an index

11
Relational database management system (RDBMS)
• A relational database management system (RDBMS) is a program
used to create, update, and manage relational databases.
• Some of the most well-known RDBMSs include :

– MySQL,

– PostgreSQL,

– Microsoft SQL Server, and

– Oracle Database.

12
PostgreSQL
PostgreSQL
• is an advanced, enterprise-class, and open-source relational
database system.
• PostgreSQL supports SQL (relational)
• PostgreSQL is a highly stable database
• PostgreSQL is used as a primary database for many web
applications as well as mobile and analytics applications.

13
• Language support
• PostgreSQL support most popular
programming languages:
– Python
– Java
– C#
– C/C+
– Ruby
– JavaScript (Node.js)

14
Installing PostgreSQL on Windows

• To use Postgre in your machine, you need to install:

 Postgre Database Server
 A graphical tool to administer and manage the DB.
 pgAdmin is the most popular tool GUI Tool for Postgre

Step 1) Open your browser.

Go to https://www.postgresql.org/download and select
Windows

15
Step 2) Check options.
You are given two options 1) Interactive Installer by EnterpriseDB
and 2) Graphical Installer by BigSQL.
• BigSQL currently installs pgAdmin version 4 which is
deprecated. It’s best to choose

16
Step 3) Select PostgreSQL version.

• You will be prompted to desired PostgreSQL version and

operating system.
• Select the latest PostgreSQL version (15.2) and OS as per your
environment
• Click the Download Button

Step 4) Open exe file.

Once you Download PostgreSQL, open the downloaded exe and
Click next on the install welcome screen.
17
Step 5) Update location.
– Change the Installation directory if required, else leave it
to default
– Click Next

Step 6) Select components.

• You may choose the components you want to install in your

system. You may uncheck Stack Builder
• Click Next

18
Step 7) Check data location.

– You may change the data location

– Click Next

Step 8) Enter password.

– Enter super user password. Make a note of it

– Click Next

Step 9) Check port option.

– Leave the port number default

19
Step 10) Check summary.
– Check the pre-installation summary:
– Click Next

Step 11) Ready to install.

Click the next button.
Step 12) Check stack builder prompt.
Once install is complete you will see the Stack Builder prompt
• Uncheck that option
• Click Finish

20
PostgreSQL Create Database using pgAdmin
1. Launch PostgreSQL.
To launch PostgreSQL go to Start Menu and search
pgAdmin 4
2. Check pgAdmin.
You will see pgAdmin homepage.
3. Find PostgreSQL 15.
Click on Servers > PostgreSQL 15 in the left tree
4. Enter password.
– Enter super user password set during installation
– Click OK
5. In the Object Tree, right click and select create a
database to Postgres create database
21
22
6 In the pop-up,
– Enter Database Name
– Comment if any database – optional
– Click Save
7.DB is created and shown in the Object tree.

23
PostgreSQL Create Table: pgAdmin

Below is a step by step process to create table in

pgAdmin:

Step 1) In the Object Tree,

• Select the Database
• Select the Schema where you want to
create a table in our case public.
• Click Create Table

24
25
Step 2) In the popup, Enter the Table Name

26
Step 4) In the object tree, you will see the table created

27
The CREATE DATABASE Statement
• The syntax for the typical CREATE DATABASE statement looks
like this:

SYNTAX:
CREATE DATABASE database_name
How to create database in PostgreSQL
command line and the GUI

PSQL Create Database Command Line (SQL Shell)

Step 1) Open the SQL Shell

Step 2) Press enter five times to connect to the DB

29
 Step 3) Enter the command
CREATE DATABASE university;

30
Step 4) Enter command \l to get a list of all
databases
Step 5) To connect to a Database use PostgreSQL
database command
\c university

• Prompt changes to university

• which signifies that we are connected to
database university and can perform operations
like create table, trigger, execute SQL on it.

31
Drop Database in PostgreSQL
– The DROP DATABASE in PostgreSQL is a statement
to permanently remove all catalog entries and
data directory.
– The database owner can only execute this
command. It can not be executed while someone
is connected with the target database. You need
to connect to some other database to execute the
DROP DATABASE command.
– So, you should be extra cautious when performing
this operation.
– PostgreSQL DROP DATABASE Syntax:
DROP DATABASE [IF EXISTS) name;
32
PostgreSQL Drop Database Using SQL
Shell(Command Line)

• Below is a step by step process to drop database in

Postgres command line:
Step 1) Use command \l to determine the currently
available database.
Step 2) To drop database in Postgres, enter command
drop database test;

33
The CREATE TABLE Statement
• Here's the basic syntax for the CREATE TABLE
statement:
CREATE TABLE table_name
(field1 datatype [ NOT NULL ],
field2 datatype [ NOT NULL ],
field3 datatype [ NOT NULL ]...)
The CREATE TABLE Statement with primary key

CREATE TABLE table_name(

column1 datatype,
column2 datatype,
column3 datatype,
.....
columnN datatype,
PRIMARY KEY( one or more columns )
);

35
PostgreSQL Data Types
• PostgreSQL supports the following data types:
– Text Types
– Numeric Types
– Dates and Times
Character Data types

Name Description
Allows you to declare variable-
varchar(n) length with a limit
Char(n) Fixed-length, blank padded
Use can use this data type to
Text declare a variable with
unlimited length
36
Numeric Datatypes

• PostgreSQL supports two distinct types of numbers:

 Integers
 Floating-point numbers

Name Store size Range

smallint 2 bytes -32768 to +32767
-2147483648 to
integer 4 bytes
+2147483647
-9223372036854775808 to
bigint 8 bytes
9223372036854775807

37
 If you declared it as
decimal datatype ranges
from 131072 digits
decimal variable
before the decimal point
to 16383 digits after the
decimal point
If you declare it as the
number, you can include
number up to 131072
numeric variable
digits before the decimal
point to 16383 digits
after the decimal point
6 decimal digits
real 4 bytes
precision
15 decimal digits
double 8 bytes precision

38
Example 1:

CREATE TABLE employee(

ID INT PRIMARY KEY NOT NULL,
NAME TEXT NOT NULL,
AGE INT NOT NULL,
ADDRESS CHAR(50),
SALARY REAL
);

Note: primary key and NOT NULL are constraints

39
Example 2:

CREATE TABLE DEPARTMENT(

ID INT PRIMARY KEY NOT NULL,
DEPT CHAR(50) NOT NULL,
EMP_ID INT NOT NULL
);

40
PostgreSQL - DROP Table
• The PostgreSQL DROP TABLE statement is used to
remove a table definition and all associated data,
indexes, rules, triggers, and constraints for that
table.

Syntax
• Basic syntax of DROP TABLE statement is as
follows −

DROP TABLE table_name;

41
• You can verify if your table has been created successfully
using \d command, which will be used to list down all the
tables in an attached database.

• testdb-# \d

Use \d tablename to describe each table as shown below −

testdb-# \d employee

42
More on Data Types
• Name Storage Size Description
small
Range
smallserial 2 bytes autoincrementing 1 to 32767
integer
autoincrementing 1 to
serial 4 bytes integer 2147483647
large 1 to
bigserial 8 bytes autoincrementing 9223372036
integer 854775807

43
Example: Drop department table

Solution:
drop table department;

44
PostgreSQL - CONSTRAINTS
• The following are commonly used constraints available in
PostgreSQL.
– NOT NULL Constraint − Ensures that a column cannot have NULL
value.

– UNIQUE Constraint − Ensures that all values in a column are

different.

– PRIMARY Key − Uniquely identifies each row/record in a database

table.

– FOREIGN Key − Constrains data based on columns in other tables.

– CHECK Constraint − The CHECK constraint ensures that all values

in a column satisfy certain conditions 45
Implementing Constraints

NOT NULL Constraint

• By default, a column can hold NULL values.
Eg:
CREATE TABLE employee(
ID INT PRIMARY KEY NOT NULL,
NAME TEXT NOT NULL,
AGE INT NOT NULL,
ADDRESS CHAR(50),
SALARY REAL
);
46
 Implementing Constraints(Cont..)

Primary Key Constraints

a) Adding a primary key when creating a table

CREATE TABLE dept OR

( CREATE TABLE dept
dno int serial PRIMARY KEY, (
dname varchar(50) NOT NULL dno int serial
) dname ,varchar(50) NOT NULL,
Primary key(dno)
)
b) Adding a primary key after a table created
This section shows how to add a primary key to a table, even
after the table contains many rows of data. The syntax is:
ALTER TABLE table_name

ADD CONSTRAINT name_of_the_constraint

PRIMARY KEY (list_of_columns_in_the_primary_key);

Remark: In postgresql, the column to be used as Pk should

defined as NOT NULL
Example: Adding the primary key of a table using alter

command
CREATE TABLE department

(
dno int ,
dname varchar(50) NOT NULL
)

alter table department

add constraint dno_pk

primary key (dno);

Using more than one columns as primary key

Eg:
create table coures_taken(
sid int ,
cno varchar(10),
constraint pk_sid_cno primary key(sid,cno)
);

Or
create table coures_taken(
sid int ,
cno varchar(10),
primary key(sid,cno)
);
50
Dropping Constraints
• To remove a constraint you need to know its name.

• If system-generated name is used, find the constraint name

using the psql command \d table name

Syntax:
alter table table_name
drop constraint constraintName;

Eg: drop constrain dno_pk

alter table department

drop constraint dno_pk;
UNIQUE Constraint
• The UNIQUE Constraint prevents two records from
having identical values in a particular column.

Eg:
CREATE TABLE COMPANY3(
ID INT PRIMARY KEY NOT NULL,
NAME TEXT NOT NULL,
AGE INT NOT NULL UNIQUE,
ADDRESS CHAR(50),
SALARY REAL DEFAULT 50000.00
52
Foreign Key Constraints
• A foreign key constraint specifies that the
values in a column (or a group of columns)
must match the values appearing in some row
of another table.
• We say this maintains the referential integrity
between two related tables.
Example:
CREATE TABLE department
(
dno int ,
dname varchar(50) NOT NULL
)
CREATE TABLE employee(
ID INT PRIMARY KEY NOT NULL,
NAME TEXT NOT NULL,
AGE INT NOT NULL,
ADDRESS CHAR(50),
SALARY REAL,
dno int references department(dno));
SQL FOREIGN KEY Constraint on ALTER
TABLE
alter table employee

add constraint fk_dno

foreign key(dno) references dept(dno);

CREATE TABLE P55 (
ID int NOT NULL PRIMARY KEY,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Age int
);

CREATE TABLE Orders (

OrderID int NOT NULL,
OrderNumber int NOT NULL,
PersonID int,
PRIMARY KEY (OrderID),
FOREIGN KEY (PersonID) REFERENCES p55(PersonID)
);

56
To DROP a FOREIGN KEY Constraint
• To drop a FOREIGN KEY constraint, use the following SQL:

ALTER TABLE table_name

DROP CONSTRAINT foreign_key_contraint_name

Eg:ALTER TABLE employee

DROP CONSTRAINT fk_dno
3) Default Constraints
– SQL DEFAULT Constraint
– The DEFAULT constraint is used to insert a default value
into a column.
– The default value will be added to all new records, if no

other value is specified.

SQL DEFAULT Constraint on CREATE TABLE

• The following SQL creates a DEFAULT constraint on the "City"

column when the "Persons" table is created:

CREATE TABLE Persons

(
P_Id int NOT NULL,
LastName varchar(255) NOT NULL,
Age int default 18,
City varchar(255) DEFAULT ‘Addis Ababa'
)
SQL DEFAULT Constraint on ALTER TABLE
• To create a DEFAULT constraint on the "City"
column when the table is already created, use
the following SQL:
ALTER TABLE Persons
ALTER COLUMN Age SET DEFAULT 18

• ALTER TABLE Employee ADD CONSTRAINT

DF_SomeName DEFAULT 'SANDNES' FOR City;
Dropping or removing any default value

ALTER TABLE table_name

ALTER COLUMN column_name
DROP DEFAULT;;

Eg:
ALTER TABLE persons
ALTER COLUMN age
DROP DEFAULT;;
SQL CHECK Constraint
• The CHECK constraint is used to limit the value
range that can be placed in a column.
• If you define a CHECK constraint on a single
column it allows only certain values for this
column.
• If you define a CHECK constraint on a table it
can limit the values in certain columns based
• SQL CHECK Constraint on CREATE TABLE

• The following SQL creates a CHECK constraint on the “AGE" column

when the "Persons" table is created. The CHECK constraint specifies that

the column “Age" must only include integers greater than 18.:

CREATE TABLE Persons

Id int NOT NULL PRIMARY KEY,

LastName varchar(255) NOT NULL,

FirstName varchar(255),

Age int check(Age>18)

)
CHECK constraint on multiple columns
• To allow naming of a CHECK constraint, and for
defining a CHECK constraint on multiple
columns, use the following SQL syntax:
CREATE TABLE Persons
(
id int primary key,
Age int,
CONSTRAINT Persons_id_age check(id > 0 and
age > 18)

)
SQL CHECK Constraint on ALTER TABLE
• To create a CHECK constraint on the "P_Id"
column when the table is already created, use
the following SQL:

alter table e3
add constraint persons_age_chk check(age>18);
• To allow naming of a CHECK constraint, and for
defining a CHECK constraint on multiple
columns, use the following SQL syntax:

alter table persons add constraint

persons_id_age_chk check(id>0 and age>18);
To DROP a CHECK Constraint

• To drop a CHECK constraint, use the following

SQL:

• ALTER TABLE Persons

DROP CONSTRAINT persons_id_age_chk
Indexes
• An index can be created in a table to find data more quickly
and efficiently.
• The users cannot see the indexes, they are just used to speed
up searches/queries.

Note: You should only create indexes on columns (and tables)

that will be frequently searched against.
SQL CREATE INDEX Syntax
• Creates an index on a table. Duplicate values are allowed:

CREATE INDEX index_name

ON table_name (column_name)

Eg: create index persons_fname_idx on persons(fname)

Creating an index on a combination of columns

• If you want to create an index on a combination of columns,

you can list the column names within the parentheses,
separated by commas:

CREATE INDEX persons_index

ON Persons (LastName, FirstName)
SQL DROP INDEX

DROP INDEX index_name

Eg:
drop index persons_fname_index;
The DROP TABLE Statement
• The DROP TABLE statement is used to delete a table.

DROP TABLE table_name

Eg: drop table persons

The TRUNCATE TABLE Statement
• What if we only want to delete the data inside the table, and
not the table itself?
• Then, use the TRUNCATE TABLE statement:

TRUNCATE TABLE table_name

Eg: truncate table persons;

SQL ALTER TABLE Statement

• We can change the structure of a table using the ALTER TABLE

command. We can
– Add a column
– Rename a column
– Modify a column
– Delete a column
– Rename a table

74
Add Column in a Table
• We can add columns in a table using the ALTER TABLE
command with the ADD clause. For example,
Syntax:
ALTER TABLE table_name
ADD column_name datatype constraint;

Eg:
ALTER TABLE Persons
ADD phone varchar(10);

75
Add Multiple Columns in a Table
• We can also add multiple columns at once in a
table.

ALTER TABLE table_name

ADD COLUMN column_name1 data_type constraint,
ADD COLUMN column_name2 data_type constraint,
...
ADD COLUMN column_namen data_type constraint;

76
Example:
ALTER TABLE Persons
ADD phone varchar(10),
ADD address varchar(10),
Add Salary numeric(5,2);

77
 Dropping a column from a table

• To delete a column in a table, use the

following syntax (notice that some database
systems don't allow deleting a column):

ALTER TABLE table_name

DROP COLUMN column_name
Eg:
Alter table persons
Drop column address;
Changing a Column's Data Type

• To change the data type of a column in a table,

use the following syntax:

ALTER TABLE table_name

ALTER COLUMN column_name newdatatype
Renaming a Table

ALTER TABLE table_name

RENAME TO new_table_name;

Eg:
ALTER TABLE persons
RENAME TO employees;

80
Renaming a column

ALTER TABLE table_name

RENAME COLUMN column_name TO
new_column_name;

Eg:
ALTER TABLE persons
RENAME column fname TO first_name;

81
Lesson Summary
• The name SQL stands for Structured Query Language.
• SQL is a standard language for accessing and manipulating
databases.
• SQL is a nonprocedural language.
• SQL can execute queries against a database
• SQL can retrieve data from a database
• SQL can insert records in a database
• SQL can update records in a database
• SQL can delete records from a database
• SQL can create new databases
82
Homework

1. create the following tables

a) Department(dno,dname)
b) Student(idNo,fname,lname,age,sex,dnumber)
Constraints:
dno – is pk for department table
idNo-is pk for student table
dnumber is a foreign key in student table

83
2. Write a SQL statement to create countries table with the
following fields and constraints.

Column name datatype length Constraint

Country_id int Primary key
Country_name Varchar 30 Default ‘USA’
Number_of_population int

84
3. Write a SQL statement to create a simple table movies which
should include columns movie_id, movie_name, movie_type and
movie_rating only if it does not exist. The movie_id should be an
unique indentifier of the table. It should store an auto
incremented value. Each field should not be null too

4. Write a SQL statement to rename the movies table to

movies_renamed.

85
5.Write a SQL statement to add a column movie_director to the
movies_renamed table.

6. Write a SQL statement to change the data type of the column

movie_rating to integer.
7. Write a SQL statement to drop the movie_director column.

86
SQL-DML

87
SQL-DML

DML
• Insert Command
• Update command
• Delete command
• Select

88
DML (Data Manipulation Language)

• Inserting Records (INSERT SQL Command)

• Updating Records (UPDATE SQL Command)
• Deleting Records (DELETE Command)
• The SELECT statement

89
Inserting Records (INSERT SQL Command)

• INSERT statement allows you to insert a new row into a table.

Syntax:
INSERT INTO table_name(column1, column2, …)
VALUES (value1, value2, …);

90
INSERT statement examples

CREATE TABLE links (

id SERIAL PRIMARY KEY,
url VARCHAR(255) NOT NULL,
name VARCHAR(255) NOT NULL,
description VARCHAR (255),
last_update DATE
);

91
Inserting a single row into a table
• The following statement inserts a new row into the links table:

INSERT INTO links (url, name)

VALUES('https://www.postgresqltutorial.com','PostgreSQL
Tutorial');

• In the above example, description and last_update columns are

optional columns.

92
Inserting a date value
– To insert a date value into a column with the DATE
type, you use the date in the format 'YYYY-MM-
DD'.

INSERT INTO links (url, name, last_update)

VALUES('https://www.google.com','Google','2013-06-01');

93
Inserting values for all columns

Use the table:

student studID fname lname sex

Method 1:
INSERT INTO student (studID, FirstName, LastName, Sex)
VALUES ('AB101', 'Jemal', 'Abdella', 'Male')

Method 2:
INSERT INTO Student
VALUES ('AB101', 'Jemal', 'Abdella', 'Male')
94
INSERT Multiple Rows

INSERT INTO table_name (column_list)

VALUES
(value_list_1),
(value_list_2),
...
(value_list_n);

95
Example: Inserting multiple records

INSERT INTO
links (url, name)
VALUES
('https://www.google.com','Google'),
('https://www.yahoo.com','Yahoo'),
('https://www.bing.com','Bing');

96
UPDATE statement

• UPDATE statement allows you to modify data in a table.

UPDATE table_name
SET column1 = value1,
column2 = value2,
...
WHERE condition;

97
Example:

UPDATE Student
SET FirstName = 'Merima', Sex = 'Female'

WHERE FirstName = 'Jemal' AND Sex = 'Male'

98
DELETE statement

• DELETE statement allows you to delete one or more rows

from a table

DELETE FROM table_name

WHERE condition

99
Example

DELETE FROM Student

WHERE idNo=2;

DELETE to delete all rows from the

table

DELETE FROM Student;

100
Select statement

• Select statement is used to query data from tables

Syntax:
SELECT [ALL | DISTINCT] column1 [, column2]
FROM table1 [, table2]
[WHERE conditions]
[GROUP BY column-list]
[HAVING conditions]
[ORDER BY column-list [ASC | DESC] ]

101
The SELECT statement has the following clauses:
• Select distinct rows using DISTINCT operator.
• Sort rows using ORDER BY clause.
• Filter rows using WHERE clause.
• Group rows into groups using GROUP BY clause.
• Filter groups using HAVING clause.
• Join with other tables using joins such as INNER JOIN,
LEFT JOIN, FULL OUTER JOIN, CROSS JOIN clauses.
• Perform set operations using UNION, INTERSECT, and
EXCEPT.
102
 Basic form of the SELECT statement

SELECT
select_list
FROM
table_name;

• PostgreSQL evaluates the FROM clause before the SELECT

clause in the SELECT statement:

• Note that the SQL keywords are case-insensitive.

103
• PostgreSQL SELECT examples using Sample
dvdrental Database

104
1. SELECT statement to query data from one column
Write a SELECT statement to find the first names of all customers
from the customer table:

Solution:
select first_name
from customer

105
2. Using SELECT statement to query data from
multiple columns

• Write select query to list first name, last name

and email of customers

Solution:
select first_name,email
from customer;

106
3. Using SQL SELECT statement to query data from all
columns of a table.

• Write a select query that selects data from all

columns of the customer table

Solution:

select *
from customer;
107
4. SELECT statement with expressions

• Write a SELECT statement to return full names and

emails of all customers

Solution
Select first_name || last_name|| ‘ “,email
From customer;

Note: The concatenation operator (||) is used to

concatenate the first name, space, and last name of
every customer.
108
5. SELECT statement with expressions

SELECT 5 * 3;

109
Column Alias
• A column alias allows you to assign a column or an expression
in the select list of a SELECT statement a temporary name.
The column alias exists temporarily during the execution of
the query.
Syntax of using a column alias:
SELECT column_name AS alias_name FROM table_name

Or

SELECT column_name alias_name FROM table_name;

Note: The AS keyword is optional

110
 Column Alias (Cont…)

• If you want to rename the last_name heading, you can assign it a new
name using a column alias like this:

SELECT
first_name, last_name AS surname
FROM customer;
Or you can make it shorter by removing the AS keyword as follows:

SELECT
first_name, last_name surname
FROM customer 111
 Assigning a column alias to an expression
SELECT
first_name || ' ' || last_name Full_Name
FROM
customer;

Column aliases that contain spaces Output

SELECT
first_name || ' ' || last_name “Full Name “
FROM
customer;

112
ORDER BY clause
• The ORDER BY clause allows you to sort rows returned by
a SELECT clause in ascending or descending order based on a
sort expression.
SELECT
select_list
FROM
table_name
ORDER BY
sort_expression1 [ASC | DESC],
...
sort_expressionN [ASC | DESC];

113
ORDER BY clause (Cont..)

• Postgresql evaluates the clauses in the SELECT statment

in the following order: FROM, SELECT, and ORDER BY.

• Due to the order of evaluation, if you have a column alias in

the SELECT clause, you can use it in the ORDER BY clause.

114
1.The following query uses the ORDER BY clause to sort
customers by their first names in ascending order:

SELECT
first_name,
last_name
FROM
customer
ORDER BY
first_name ASC;

Or (ASC is default can be omitted)

SELECT
first_name,
last_name
FROM
customer
ORDER BY
first_name; 115
2.The following query uses the ORDER BY clause to sort
customers by their first names in descending order:

SELECT
first_name,
last_name
FROM
customer
ORDER BY
first_name DESC;

116
3. ORDER BY clause to sort rows by multiple columns

• The following statement selects the first name and last name from the
customer table and sorts the rows by the first name in ascending order
and last name in descending order

SELECT
first_name,
last_name
FROM
customer
ORDER BY
first_name ASC,
last_name DESC;

117
SELECT DISTINCT clause
• The DISTINCT clause is used in the SELECT statement to
remove duplicate rows from a result set.
• The DISTINCT clause keeps one row for each group of
duplicates.
• The DISTINCT clause can be applied to one or more columns
in the select list of the SELECT statement.

SELECT SELECT
DISTINCT column1 DISTINCT column1,column2
FROM FROM
table_name; table_name;
118
SELECT DISTINCT examples
• Let’s create a new table called distinct_demo and insert data
into it for practicing the DISTINCT clause.

CREATE TABLE distinct_demo (

id serial NOT NULL PRIMARY KEY,
bcolor VARCHAR,
fcolor VARCHAR
);

119
INSERT INTO distinct_demo (bcolor, fcolor)
VALUES
('red', 'red'),
('red', 'red'),
('red', NULL),
(NULL, 'red'),
('red', 'green'),
('red', 'blue'),
('green', 'red'),
('green', 'blue'),
('green', 'green'),
('blue', 'red'),
('blue', 'green'),
('blue', 'blue');

120
SELECT
id,
bcolor,
fcolor
FROM
distinct_demo ;

DISTINCT one column example

SELECT
DISTINCT bcolor
FROM
distinct_demo
ORDER BY
bcolor;

121
 DISTINCT multiple columns
SELECT
DISTINCT bcolor,
fcolor
FROM
distinct_demo
ORDER BY
bcolor,
fcolor;

• The query returns the unique combination of bcolor and fcolor from the
distinct_demo table
122
WHERE clause
• The SELECT statement returns all rows from one or more
columns in a table.
• To select rows that satisfy a specified condition, you use a
WHERE clause.
The syntax of the WHERE clause is as follows:

SELECT select_list
FROM table_name
WHERE condition
ORDER BY sort_expression

123
Order of Evaluation:

• PostgreSQL evaluates the WHERE clause after the FROM clause

and before the SELECT and ORDER BY clause:

124
• To form the condition in the WHERE clause, you use
comparison and logical operators:

Operator Description
= Equal
> Greater than
< Less than
>= Greater than or equal
<= Less than or equal
<> or != Not equal
AND Logical operator AND
OR Logical operator OR
IN Return true if a value matches any value in a list
BETWEEN Return true if a value is between a range of values
LIKE Return true if a value matches a pattern
IS NULL Return true if a value is NULL
NOT Negate the result of other operators

125
1) Using WHERE clause with the equal (=) operator example
• The following statement uses the WHERE clause customers
whose first names are Jamie:

SELECT
last_name,
first_name
FROM
customer
WHERE
first_name = 'Jamie';

126
2) Using WHERE clause with the AND operator example

• The following example finds customers whose first name is

Jamie and whose last name is Rice by using the AND logical
operator to combine two Boolean expressions:
SELECT
last_name,
first_name
FROM
customer
WHERE
first_name = 'Jamie' AND
last_name = 'Rice';

127
3) Using the WHERE clause with the OR operator example
• This example finds the customers whose last name is
Rodriguez or first name is Adam by using the OR operator:

SELECT
first_name,
last_name
FROM
customer
WHERE
last_name = 'Rodriguez' OR
first_name = 'Adam';

128
4) Using WHERE clause with the IN operator example
• If you want to match a string with any string in a list, you can
use the IN operator.

• You use IN operator in the WHERE clause to check if a value

matches any value in a list of values.
• The syntax of the IN operator is as follows:

value IN (value1,value2,...)

129
• For example, the following statement returns customers
whose first name is Ann, or Anne, or Annie:

SELECT
first_name,
last_name
FROM
customer
WHERE
first_name IN
('Ann','Anne','Annie');

130
 NOT IN operator

• You can combine the IN operator with the NOT operator to

select rows whose values do not match the values in the list.
SELECT
customer_id,
rental_id,
return_date
FROM
rental
WHERE
customer_id NOT IN (1, 2);

131
 5) Using the WHERE clause with the LIKE operator example

• To find a string that matches a specified pattern, you use the LIKE operator.
The following example returns all customers whose first names start with the
string Ann:

SELECT
first_name,
last_name
FROM
customer
WHERE
first_name LIKE 'Ann%

• The % is called a wildcard that matches any string. The 'Ann%' pattern
matches any string that starts with 'Ann'.
132
Wildcard characters
• PostgreSQL supports two types of wildcards
represented with a percentage sign “%,” and an
underscore sign “_”.
• The percentage wildcard "%" matches sequences of
characters, while the underscore wildcard "_"
matches a single character.
• These wildcards are used with the LIKE operator to
perform the pattern matching.
133
Examples:
_x all those values that start with anything but their second letter
must be “x”.

_x_ you can place anything at the first and third index however
the second index must have the letter “x”.

%, percentage wildcard is used to signify zero, one, or more than one

number/character
%xx% fetch all those values containing a substring “xx”
%xx find all those strings that end with “xx”.
134
xx% find all those strings that start with “xx”.
NOT LIKE
SELECT
first_name,
last_name
FROM
customer
WHERE
first_name NOT LIKE 'Jen%'
ORDER BY
first_name

135
6) Using the WHERE clause with the BETWEEN operator
example
• The following example finds customers whose customer_id is
between 1 and 10
select customer_id,first_name
from customer
where customer_id between 1 and 10;

136
NOT BETWEEN
SELECT
customer_id,
payment_id,
amount
FROM
payment
WHERE
amount NOT BETWEEN 8 AND 9;

137
7) Using the WHERE clause with the not equal operator (<>)
example
• This example finds customers whose first names start with Bra
and last names are not Motley:
SELECT
first_name,
last_name
FROM
customer
WHERE
first_name LIKE 'Bra%' AND
last_name <> 'Motley';

• Note that you can use the != operator and <> operator
interchangeably because they are equivalent.
138
table aliases
• Table aliases temporarily assign tables new names during the
execution of a query.
• he following illustrates the syntax of a table alias:
table_name AS alias_name;

139
Aggregate functions
• Aggregate functions perform a calculation on a set of rows
and return a single row.
– These are:
• SUM()
• MIN()
• MAX()
• AVG()
• COUNT()

140
SUM()
1. Display the total amount of payments using the payment
table.
Solution
SELECT SUM(amount) as “Total Payement”
FROM payment;

141
AVG() function

• The AVG() function allows you to calculate the average value of

a set.

• The syntax of the AVG() function is as follows:

AVG(column)

• Display the average amount that customers paid

SELECT AVG(amount)
FROM payment;

SELECT round(AVG(amount))
FROM payment; 142
COUNT() function

To get the number of films,

SELECT
COUNT(*)
FROM
film;

143
MAX() function

• To get the max replacement_cost

SELECT
max(replacement_cost)
FROM
film;

144
MIN() function

• To get the min replacement_cost

SELECT
min(replacement_cost)
FROM
film;

145
Example:
Using payment table, display the max, min, and average
amount.

Solution
SELECT MAX(salary) as Max , MIN(salary) as MIN , AVG(salary)
as Average
FROM payment

146
GROUP BY clause
• The GROUP BY clause divides the rows returned from the
SELECT statement into groups.
• For each group, you can apply an aggregate function e.g.,
SUM() to calculate the sum of items or COUNT() to get the
number of items in the groups.

147
• The following statement illustrates the basic syntax of the
GROUP BY clause:
SELECT
column_1,
column_2,
...,
aggregate_function(column_3)
FROM
table_name
GROUP BY
column_1,
column_2,
148
...;
Example:
1.Use emp table to display the sum
of the salaries of employees in
each job category.

Solution:

select job,sum(sal) from emp group

by job

149
Example:
2.Use emp table to display the number
employees in
each job category.

Solution:

select job,count(*) from emp group

by job

150
3. Using emp table, display the max and min salaries of
employees for each job category.

SELECT dept, MAX(salary) as Max, MIN(salary) as MIN

FROM emp
GROUP BY job;

151
HAVING clause

• Similar to where condition except that HAVING is used with

group of records instead of single records

152
Example:
1.Use emp table to display the sum
of the salaries of employees in
Each job category with total salary
Greater than 5000.

Solution:

select job,sum(sal) from emp group

by job having sum(sal) > 5000.

153
Lesson Summary
• INSERT statement allows you to insert a new row into a table
• UPDATE statement allows you to modify data in a table.
• DELETE statement allows you to delete one or more rows from
a table

154
Summary

• Select statement is used to query data from tables

Syntax:
SELECT [ALL | DISTINCT] column1 [, column2]
FROM table1 [, table2]
[WHERE conditions]
[GROUP BY column-list]
[HAVING conditions]

[ORDER BY column-list [ASC | DESC] ]

155
Lesson Summary

• Assign a column or an expression a column alias using the

syntax column_name AS alias_name or expression AS
alias_name.
• The AS keyword is optional.
• Use double quotes (“) to surround a column alias that contains
spaces.

156
Lesson Summary
• Use the ORDER BY clause in the SELECT
statement to sort rows.Use the ASC option
to sort rows in ascending order and DESC
option to sort rows in descending order.
SELECT DISTINCT statement to remove duplicate
rows returned by a query

157
Homework
(For the following questions, use the dvdrental sample database)

1. List the records of all customers sorted by first_name then

by last_name in descending order.
2. List the records of all customers whose first_name starting
with the letter ‘B’.
3. Write a query to count the number of payment transactions
that each staff has processed. (Use payment table).

158
4. Write a query that list the film_id,title and length of all films with
film length between 50 and 100 sorted in ascending order by title.

5. List all customers except those customer with first names:

Betty,Karen and Jennifer sorted with first name.

6. List those employees who were hired between '1995-01-01'and

'2000-01-01'; (Use emp table)

159
SQL-DML Part III

160
• Join commands
• The CROSS JOIN
• The INNER JOIN
• The LEFT OUTER JOIN
• The RIGHT OUTER JOIN
• The FULL OUTER JOIN
• Subquery
• View
• Function
• Stored Procedure
• Triggers

161
 Joins
• joins clause is used to combine records from two or more

tables in a database.

• A JOIN is a means for combining fields from two tables by

using values common to each.
• Join Types in PostgreSQL are −
• The CROSS JOIN
• The INNER JOIN
• The LEFT OUTER JOIN
• The RIGHT OUTER JOIN
• The FULL OUTER JOIN
162
Sample tables for examples on joins
CREATE TABLE basket_a (
a INT PRIMARY KEY,
fruit_a VARCHAR (100) NOT NULL
);

CREATE TABLE basket_b (

b INT PRIMARY KEY,
fruit_b VARCHAR (100) NOT NULL
);
163
Sample tables for examples on joins (Cont..)
INSERT INTO basket_a (a, fruit_a)
VALUES
(1, 'Apple'),
(2, 'Orange'),
(3, 'Banana'),
(4, 'Cucumber');

INSERT INTO basket_b (b, fruit_b)

VALUES
(1, 'Orange'),
(2, 'Apple'),
(3, 'Watermelon'),
(4, 'Pear');

164
The CROSS JOIN
• A CROSS JOIN matches every row of the first table with every
row of the second table

• If the input tables have x and y columns, respectively, the

resulting table will have x+y columns

The following is the syntax of CROSS JOIN −

SELECT ... FROM table1 CROSS JOIN table2 ...

165
Sample Tables for CROSS JOIN example

166
Select * from basket_a cross join basket_b

167
The INNER JOIN
• A INNER JOIN creates a new result table by combining column
values of two tables (table1 and table2) based upon the join-
predicate.
• The query compares each row of table1 with each row of table2
to find all pairs of rows, which satisfy the join-predicate.
• When the join-predicate is satisfied, column values for each
matched pair of rows of table1 and table2 are combined into a
result row.

168
INNER JOIN (Cont…)

• An INNER JOIN is the most common type of join and is

the default type of join. You can use INNER keyword
optionally.

• The following is the syntax of INNER JOIN −

SELECT table1.column1, table2.column2...

FROM table1

INNER JOIN table2

ON table1.common_filed = table2.common_field;
169
Example:
select a,basket_a,b,fruit_b
from basket_a inner join basket_b
on fruit_a=fruit_b

170
The LEFT OUTER JOIN
• In LEFT OUTER JOIN, an inner join is performed first. Then, for
each row in table T1 that does not satisfy the join condition
with any row in table T2, a joined row is added with null
values in columns of T2.
• Thus, the joined table always has at least one row for each
row in T1.
• The following is the syntax of LEFT OUTER JOIN −

SELECT ... FROM table1 LEFT OUTER JOIN table2 ON

conditional_expression ...

171
Example:

Select a,fruit_a,b,fruit_b
from basket_a left outer join basket_b on
fruit_a=fruit_b;

172
The RIGHT OUTER JOIN
• In RIGHT OUTER JOIN, an inner join is performed first. Then,
for each row in table T2 that does not satisfy the join
condition with any row in table T1, a joined row is added with
null values in columns of T1.
• Thus, the joined table always has at least one row for each
row in T2.
• The following is the syntax of RIGHT OUTER JOIN −

SELECT ... FROM table1 RIGHT OUTER JOIN table2 ON

conditional_expression ...

173
Example:

Select a,fruit_a,b,fruit_b
from basket_a right outer join basket_b on
fruit_a=fruit_b;

174
FULL OUTER JOIN
• First, an inner join is performed. Then, for each row in
table T1 that does not satisfy the join condition with any
row in table T2, a joined row is added with null values in
columns of T2. In addition, for each row of T2 that does
not satisfy the join condition with any row in T1, a joined
row with null values in the columns of T1 is added.
• The following is the syntax of FULL OUTER JOIN −

SELECT ... FROM table1 FULL OUTER JOIN table2 ON

conditional_expression ..
175
Example:

Select a,fruit_a,b,fruit_b
from basket_a full outer join basket_b on
fruit_a=fruit_b;

176
 Sample Tables for INNER JOIN example

Example :Use INNER JOIN to select customer_id, first_name, last_name, amount,

payment_date from both customer and payment tables.
177
Solution
SELECT
customer.customer_id,first_name,last_name,
amount,payment_date
FROM customer INNER JOIN payment
ON payment.customer_id = customer.customer_id
ORDER BY payment_date;

178
Solution (Using Table alies)
SELECT
c.customer_id,first_name,last_name,
amount,payment_date
FROM customer C INNER JOIN payment p
ON c.customer_id = p.customer_id
ORDER BY payment_date;

179
Keyword using
• Whenever two tables join on the same column name we can
use the using syntax.

Eg:
SELECT
c.customer_id,first_name,last_name,
amount,payment_date
FROM customer C INNER JOIN payment p
USING (customer_id)
ORDER BY payment_date;

180
 INNER JOIN to join three tables
The following diagram illustrates the relationship between three tables: staff,
payment, and customer.
• Each staff handles zero or many payments. And each payment is processed
by one and only one staff.
• Each customer made zero or many payments. Each payment is made by
one customer.

181
Example :Write a query that displays the:
– customer id,first name and last name,
– Staff first name and last name
– Amount and payment date
Solution
SELECT c.customer_id,,c.first_name customer_first_name,
c.last_name customer_last_name,s.first_name
staff_first_name,s.last_name staff_last_name,
amount, payment_date
FROM customer c INNER JOIN payment p
ON p.customer_id = c.customer_id
INNER JOIN staff s ON p.staff_id = s.staff_id
ORDER BY payment_date;
182
OR
SELECT c.customer_id,,c.first_name customer_first_name,
c.last_name customer_last_name,s.first_name
staff_first_name,s.last_name staff_last_name,
amount, payment_date
FROM customer c INNER JOIN payment p
USING(customer_id )
INNER JOIN staff s USING(staff_id)
ORDER BY payment_date;

183
self-join
• A self-join is a regular join that joins a table to itself.

• You typically use a self-join to query hierarchical data or to

compare rows within the same table.

184
self-join (Cont..)
• The following statements create the employee table
and insert some sample data into the table.

CREATE TABLE employee (

employee_id INT PRIMARY KEY,
first_name VARCHAR (255) NOT NULL,
last_name VARCHAR (255) NOT NULL,
manager_id INT,
FOREIGN KEY (manager_id)
REFERENCES employee (employee_id)
ON DELETE CASCADE
);

185
Sample table for self-join example
INSERT INTO employee (
employee_id,
first_name,
last_name,
manager_id
)
VALUES
(1, 'Windy', 'Hays', NULL),
(2, 'Ava', 'Christensen', 1),
(3, 'Hassan', 'Conner', 1),
(4, 'Anna', 'Reeves', 2),
(5, 'Sau', 'Norman', 2),
(6, 'Kelsie', 'Hays', 3),
(7, 'Tory', 'Goff', 3),
(8, 'Salley', 'Lester', 3); 186
Example: Write a query that displays list of all employees with their
respective mangers

SELECT
e.first_name || ' ' || e.last_name employee,
m .first_name || ' ' || m .last_name manager
FROM
employee e
INNER JOIN employee m ON m .employee_id =
e.manager_id
ORDER BY manager;

187
NULL Values
– NULL is the term used to represent a missing value.
– A NULL value in a table is a value in a field that appears to
be blank.
– A field with a NULL value is a field with no value.
– NULL value is different from a zero value or a field that
contains spaces

188
 IS NOT NULL operator
Eg: select * from emp where comm is not null;

189
Subquery
• allows you to construct complex queries

• Suppose we want to find the films whose rental rate is higher than the
average rental rate. We can do it in two steps:

• Find the average rental rate by using the SELECT statement and average
function ( AVG).
• Use the result of the first query in the second SELECT statement to find the
films that we want.

190
• The following query gets the average rental rate:

SELECT SELECT
AVG (rental_rate) film_id,
FROM title,
film; rental_rate
FROM
film
WHERE
rental_rate > 2.98;

Now, we can get films whose rental rate is higher than the
average rental rate:

191
• A subquery is a query nested inside another query
• To construct a subquery, we put the second query in brackets
and use it in the WHERE clause as an expression:
SELECT
film_id,
title,
rental_rate
FROM
film
WHERE
rental_rate > (
SELECT
AVG (rental_rate)
FROM
film 192
• The query inside the brackets is called a subquery or an inner query.

• The query that contains the subquery is known as an outer query.

• A subquery is used to return data that will be used in the main query
as a condition to further restrict the data to be retrieved.
• Subqueries can be used with the SELECT, INSERT, UPDATE and DELETE
statements along with the operators like =, <, >, >=, <=, IN, etc.

193
Using IN with subquery

194
select empno,ename,sal
from emp
where empno
in (select empno from emp where sal > 2000);

195
VIEWS
• Views are virtual tables. That is, they are not real tables;

nevertheless appear as ordinary tables to SELECT.

• A view can represent a subset of a real table, selecting certain

columns or certain rows from an ordinary table.
• A view can even represent joined tables.
• A view can be created from one or many tables,
• a view does not store data physically like a table

196
Creating Views
• views are created using the CREATE VIEW statement
• views can be created from a single table, multiple
tables, or another view.
The basic CREATE VIEW syntax is as follows −

CREATE [TEMP | TEMPORARY] VIEW view_name AS

SELECT column1, column2.....
FROM table_name
WHERE [condition];

197
CREATE VIEW example

1. Create a view called employee_view from employee

table containing empno,ename and salary columns.

Solution:

CREATE VIEW employee_view AS

SELECT empno,ename,salary

FROM employee;

198
Example :create a view called customer_payment_view with
customer_id, first_name, last_name, amount, payment_date
columns from both customer and payment tables.
199
Dropping Views
Syntax:
DROP VIEW view_name;

• Example: Drop the employee_view view.

Solution:
Drop view employee_view;

200
Function
• function or a stored procedure is a set of SQL and procedural
commands such as declarations, assignments, loops, flow-of-
control etc. stored on the database server

• We can create PostgreSQL functions in serval languages, for

example, SQL, PL/pgSQL, C, Python etc.

201
Syntax of PostgreSQL CREATE Function command
CREATE [OR REPLACE] FUNCTION function_name (arguments)
RETURNS return_datatype
LANGUAGE plpgsql
AS $variable_name$
DECLARE
declaration;
[...] -- variable declaration
BEGIN
< function_body >
[...] -- logic
RETURN { variable_name | value }
END;
202
$$
Example:

1. Create a function by the name of count_films1() that takes

two arguments : len_from and len_to and returns the number
of films whose duration length is between len_from and
len_to

(Use film table from sampleDB)

203
Solution:
create or replace function count_films1(len_from integer,len_to
integer)
returns integer
language plpgsql
as
$function$
declare
total integer;
begin

select count(*) into total

from film
where length between len_from and len_to;
return total;
end;
$function$; 204
How to execute the function count_films1()

Calling the function count_films1()

select count_films1(120,200);

205
Example 2:

• Create a function that takes a department number as an

argument and returns the sum of the salaries of all employees
who work in this department number.

206
Solution
create or replace function compute_sum(d_no integer)
returns numeric
language plpgsql
as
$function$
declare
total numeric;
begin
select sum(sal) into total
from emp
where deptno=d_no;
return total;
end;
$function$;

207
Stored PROCEDURE

• Stored procedures in PostgreSQL are a collection of SQL

commands manipulated to achieve a particular operation
• A stored procedure does not return a value
• You cannot use the return statement in a stored procedure

208
Creating a Stored PROCEDURE
Syntax:

create [or replace] procedure

procedure_name(parameter_list)
language plpgsql
as $$
declare
-- variable declaration
begin
-- stored procedure body
end; $$
209
Sample table for creating a stored procedure example
create table accounts (
id serial,
name varchar(100) not null,
balance numeric(15,2) not null,
primary key(id)
);
insert into accounts(name,balance)
values
('Bob',10000),
('Alice',10000);

210
Example:
1.Create a stored procedure that takes two arguments(an id
number and balance for an account holder) and inserts the
record of this account to the accounts table.

See the next slide for the solution

211
Solution
create or replace procedure transfer(from_id integer,to_id
integer,amount numeric)
language plpgsql
as
$$
declare

begin
update accounts
set balance=balance-amount
where id=from_id;

update accounts
set balance=balance+amount
where id=to_id;
end; 212
$$;
How to call a stored procedure?

Syntax:

call the_stored_procedure_name(arguments)

For the example 1 on the previous slide, the call looks:

call transfer(1,2,5000.00)

213
Example:
2. Create a stored procedure that takes three arguments(two id
numbers and amount) and transfers the amount from the
balance of account of the first id to the account of the second id.

See the next slide for the solution

214
Solution

create or replace procedure insert_record(name varchar,balance

numeric)
language plpgsql
as
$$
declare

begin
insert into accounts(name,balance)
values(name,balance);
end;
$$;

215
Solution(Cont..)

• Calling the stored procedure on the previous slide:

call insert_record(‘Kedir’,20000.00);

216
TRIGGERS
• Triggers are database callback functions, which are
automatically performed/invoked when a specified database
event occurs.
• Trigger is a function, which involved automatically whenever
an event linked with a table.
• The event can be described as any of the following INSERT,
UPDATE, DELETE or TRUNCATE.

217
Type of Triggers
– Row-level trigger
– Statement-level trigger

• If we issue an UPDATE command, which affects 10 rows,

the row-level trigger will be invoked 10 times, on the other
hand, the statement level trigger will be invoked 1 time.

218
Creating a trigger in postgresql
Steps:
1. Firstly, we can specify a trigger function.
– Trigger is a function, which invoked automatically
whenever an event linked with a table. The event can be
described as any of the following INSERT, UPDATE, DELETE
or TRUNCATE.

2. Create a trigger and bind the trigger function to a table.

219
Syntax of Create trigger function

CREATE FUNCTION trigger_function()

RETURNS TRIGGER
LANGUAGE PLPGSQL
AS $$
BEGIN
-- trigger logic goes here?
END;
$$

220
Syntax of PostgreSQL CREATE TRIGGER command:

CREATE TRIGGER trigger_name

{BEFORE | AFTER} { event }
ON table_name
[FOR [EACH] { ROW | STATEMENT }]
EXECUTE PROCEDURE trigger_function

• The event parameter is used to define the event which requested the
trigger, and it can be INSERT, UPDATE, DELETE, or TRUNCATE.

221
• A trigger that is marked FOR EACH ROW is called once for every row
that the operation modifies. In contrast, a trigger that is marked FOR
EACH STATEMENT only executes once for any given operation,
regardless of how many rows it modifies.

• NEW.column-name: used to refer to the value of the column after

the column is updated
• OLD.column-name: used to refer to the value of the column after
the column is updated

222
CREATE TABLE Clients(
ID INT GENERATED ALWAYS AS IDENTITY,
First_name VARCHAR(50) NOT NULL,
Last_name VARCHAR(50) NOT NULL,
PRIMARY KEY(ID)
);

CREATE TABLE Client_audits (

ID INT GENERATED ALWAYS AS IDENTITY,
Client_id INT NOT NULL,
First_name VARCHAR(50) NOT NULL,
changed_on TIMESTAMP(5) NOT NULL
);
223
Inserting Sample data into Clients
table

INSERT INTO Clients (First_name, Last_name)

VALUES ('Mike', 'Ross'),
('Olivia', 'Smith');

SELECT * FROM clients ;

SELECT * FROM Client_audits;

224
Creating a trigger function

CREATE OR REPLACE FUNCTION log_first_name_change()

RETURNS TRIGGER
AS
$$
begin
insert into Client_audits(Client_id,First_name,changed_on)
values(OLD.ID,OLD.First_name,now());

return NEW;
end;
$$
language plpgsql;

225
Creating a trigger

CREATE TRIGGER first_name_changes_trg

BEFORE UPDATE
ON clients
FOR EACH ROW
EXECUTE PROCEDURE log_first_name_change();

226
Testing the trigger on Updating clients table

UPDATE clients
SET first_name='Ayele'
WHERE id=1;

SELECT * FROM clients ;

SELECT * FROM Client_audits

227
Drop Trigger command

DROP TRIGGER [IF EXISTS] trigger_name

ON table_name [ CASCADE | RESTRICT ];

Example:

drop trigger first_name_changes_trg on clients;

228
Lesson Summary
• Use the DROP VIEW statement to remove one or more views
from the database.
• Joins clause is used to combine records from two or more
tables in a database.
• Join Types in PostgreSQL are −
• The CROSS JOIN
• The INNER JOIN
• The LEFT OUTER JOIN
• The RIGHT OUTER JOIN
• The FULL OUTER JOIN

229
Lesson Summary(cont..)

• Views are virtual tables. That is, they are not real tables;
• A view can be created from one or many tables,
• a view does not store data physically like a table

The basic CREATE VIEW syntax is as follows −

CREATE [TEMP | TEMPORARY] VIEW

view_name AS
SELECT column1, column2.....
FROM table_name
WHERE [condition];

230
Lesson Summary(cont..)
Syntax for creating a function

CREATE [OR REPLACE] FUNCTION function_name (arguments)

RETURNS return_datatype
LANGUAGE plpgsql
AS $variable_name$
DECLARE
declaration;
[...] -- variable declaration
BEGIN
< function_body >
[...] -- logic
RETURN { variable_name | value }
END;
$$
231
Lesson Summary(Cont...)

• Stored procedures in PostgreSQL are a collection of SQL commands manipulated to

achieve a particular operation
• A stored procedure does not return a value
• You cannot use the return statement in a stored procedure
Syntax for creating a stored procedure :
create [or replace] procedure procedure_name(parameter_list)

language plpgsql

as $$

declare

-- variable declaration

begin

-- stored procedure body

end; $$ 232
Lesson Summary(cont..)

• Triggers are database callback functions, which are automatically

performed/invoked when a specified database event occurs.
• Trigger is a function, which involved automatically whenever an
event linked with a table.

• We have used the CREATE Function command to create a new

function for the particular table where the trigger function is
parallel to the consistent user-defined function.

• The PostgreSQL Create trigger command is used to create a new

trigger
233
Homework

1. Write a query that displays the customer_id, first_name,

last_name, amount and payment_date of the customer whose
customer_id is 2. Use customer table with table structure shown
below from the sample database.

234
Homework (Cont…)

2. Question on comparing the rows with the same table. Use the film
table from the sample database to write a query that finds all pair
of films that have the same length

235
Homework (Cont…)

3. Using customer table of the sample database, write a function

that accepts a customer id and returns the full name of the
customer with this customer id. Test this function by Calling it.

236
Homework (Cont…)

4. Write a stored called add_employee() that takes an

employee’s id , first name and last name as arguments and
inserts values of these arguments as a record to the employee

sample table. Test this procedure by calling it.

237
5. Use the following two tables:
a. Create a trigger
CREATE TABLE employees(
ID INT GENERATED ALWAYS AS IDENTITY, function that inserts
records to the
First_name VARCHAR(50) NOT NULL, log_employees table
Last_name VARCHAR(50) NOT NULL, when records in the
PRIMARY KEY(ID) employees table are
);
CREATE TABLE log_employees ( updated.
ID INT GENERATED ALWAYS AS IDENTITY,
Emp_id INT NOT NULL, b.create a trigger that
Old_First_name VARCHAR(50) NOT NULL,
executes the trigger
New_First_name VARCHAR(50) NOT NULL,
changed_on TIMESTAMP(5) NOT NULL function when a record
); of employees table is
updated.

238
References

https://www.postgresqltutorial.com/postgresql-getting-
started/what-is-postgresql/

239