PL/SQL

Course Code: CSE201

LAB
Dr.Anuradha Misra
Asst. Professor
Dept Of CSE/IT ASET
AUUP Lucknow
 INTRODUCTION
• SQL (Structured Query Language) is a computer
language aimed to store, manipulate, and retrieve data
stored in relational databases.
• IBM implemented the language, originally called Sequel,
as part of the System R project in the early 1970s..
• The first commercial relational database was released by
Relational Software later becoming oracle

3
 Standard SQL and Language Extensions

• • The RDBMS model was implemented by many vendors

– • Microsoft (SQL Server), Oracle, IBM (DB2), MySQL,
Postgres, etc.
• SQL is the language each of those vendors use in order
to access and manipulate their databases
• In most cases all vendors use the same SQL command in
order to achieve the same goal.
• In some cases vendors will use specific SQL extension
that will only work under their platform.
4
5
 Parts of SQL language
• Data-definition language (DDL).
– provides commands for defining relation schemas, deleting
relations, and modifying relation schemas.
• Interactive data-manipulation language (DML).
– It includes also commands to insert tuples into, delete tuples
from, and modify tuples in the database.
• View definition.
– includes commands for defining views.
• Transaction control.
– includes commands for specifying the beginning and ending of
transactions. 6
 DDL - Data Definition Language
• statements are used to alter/modify a database or table
structure and schema.
• These statements handle the design and storage of
database objects.
• CREATE - to create objects(table,schema,database) in
the database
• ALTER - alters the structure(table, column) of the
database
• DROP - delete objects from the database
• RENAME - rename an object
7
• TRUNCATE - remove all records from a table, including
all spaces allocated for the records are removed
• COMMENT - add comments to the data dictionary

8
 Basic column data types

• Numeric columns
• INT - Integer (whole number) data value
• Money - Numeric data types with fixed precision and
scale

9
Numeric columns
• INT - Integer (whole number) data value
• Money - Monetary or currency values String columns
• VARCHAR(N) – String data
• Date columns • DATE – date data

10
 Schema Definition in SQL
• Create table : Example :
– create table branch (branch-name char(15), branch-city
char(30),assets integer, primary key (branch-name), check
(assets >= 0))
• Delete :
• Delete table : drop table r
• Delete tuples : Delete from r
• Alter table :
• Add Attribute : alter table r add AD
• Drop attribute : alter table r drop A 11
 DML- Data Manipulation Language
• Statements used for managing data within schema
objects.
• statements affect records in a table.
• These are basic operations we perform on data such as
selecting a few records from a table, inserting new
records, deleting unnecessary records, and
updating/modifying existing records.
• SELECT - retrieve data from the a database
• INSERT - insert data into a table
12
• UPDATE - updates existing data within a table
• DELETE - deletes all records from a table, the space for
the records remain
• CALL - call a PL/SQL or Java subprogram
• LOCKTABLE - control concurrency

13
 Example
• Setting Schema & Attributes
• Branch-schema = (branch-name, branch-city, assets)
• Customer-schema = (customer-name, customer- street,
customer-city)
• Loan-schema = (loan-number, branch-name, amount)
• Borrower-schema = (customer-name, loan-number)
• Account-schema = (account-number, branch-name,
balance)
• Depositor-schema = (customer-name, account- number)
14
 Basic SQL Query
• Select Clause :
1. select branch-name from loan Retain Duplicates
2. select distinct branch-name from loan Remove duplicates
3. select all branch-name from loan Retain Duplicate
• Where Clause :
1. select loan-number from loan where branch-name =
’Perryridge’ and amount > 1200
• From Clause : select customer-name, borrower.loan-
number, amount from borrower, loan where
borrower.loan-number = loan.loan-number
15
• Rename Operation :
– select customer-name, borrower.loan-number as loan-id,
amount from borrower, loan where borrower.loan- number =
loan.loan-number
– Tuple variables : select customer-name, T.loan-number,
S.amount from borrower as T, loan as S where T.loan-number
= S.loan- number Tuple variables are most useful for
comparing two tuples in the same relation.
– String Operations : select customer-name from customer
where customer- street like ’%Main%’
16
 DCL
• DCL statements control the level of access that users
have on database objects.
• GRANT - gives user's access privileges to database
GRANT – allows users to read/write on certain database
objects
• REVOKE - withdraw access privileges given with the
GRANT command
• REVOKE – keeps users from read/write permission on
database objects
17
 TCL

• Transaction Control Language(TCL) commands are used

to manage transactions in database.
• These are used to manage the changes made by DML
statements.
• It also allows statements to be grouped together into
logical transactions.

18
 TCL
• TCL statements allow you to control and manage
transactions to maintain the integrity of data within SQL
statements.
• BEGINTransaction – opens a transaction
• COMMITTransaction – commits a transaction
• ROLLBACKTransaction – ROLLBACK a transaction in
case of any error

19
• COMMIT - save work done
• SAVEPOINT - identify a point in a transaction to which
you can later roll back
• ROLLBACK - restore database to original since the last
COMMIT
• SETTRANSACTION - Change transaction options like
isolation level and what rollback segment to use

20
• Commit command
• Commit command is used to permanently save any
transaction into database.
• Following is Commit command's syntax,
– commit;

21
• Rollback command
• This command restores the database to last committed
state. It is also use with savepoint command to jump to a
savepoint in a transaction.
• Following is Rollback command's syntax,
– rollback to savepoint-name;

22
• Savepoint command
• savepoint command is used to temporarily save a
transaction so that you can rollback to that point
whenever necessary.
• Following is savepoint command's syntax,
– savepoint savepoint-name;

23
 Practical No.1.Create a table

• Objective : To create a Table

• Create Table Table_name (column name1 data
type(size), column name 2 data type(size)……);
• Description: The create table is used to create a new
table in the data base. The column parameter specifies
the names of the column of the table. The datatype
parameter specifies the type of data the column can hold

24
• CREATE TABLE client_master (client_no varchar2(6),
Name varchar2(20), Address varchar2(30), State
varchar2(15));

• Result: Table created

• Client_master
Client_no name address state

25
 Practical No.2.Inserting values
• Objective : To insert values into a Table
• Syntax:
• INSERT INTO Table_name (column name1 , column
name2) VALUES (val1,val2,val3……);
• Description: The insert statement is used to insert a single
record or multiple records in a table.
• Example: INSERT INTO client_master
(client_no,name,address,state)
values(‘c001’,’Ivan’,’AA/12’,’Delhi’);
26
• Result: Table name : Client_master
• 1 row created
Client_no name address state

C001 Ivan AA/12 Delhi

27
 Oracle Data Type
• Char(size)-
– this is used to store character string values of fixed length.
– Size is the no. of characters the column can hold.
– Maximum is 255.
– Data held is right space padded.
• Varchar/varchar2(size)-
– variable length alpha numeric data.
– More flexible form of char.
– Maximum 4000 chars.
– Inserted values not padded with space.
– Char is faster than varchar
28
• Date-
– Represent date and time.
– Format is DD-MON-YY.
– Stores time in 24 hour format.
– Default is 12:00:00 AM, first day of the current month.
• Number(P,S)-
– Numbers fixed or floating.
– Upto 38 digits of precisions.
– P-Precision - maximum length of the data
– S- scale- no. of places to the right of decimal.
29
• Long-
– Stores variable length character string upto 2 GB.
– Stores array of binary data in ASCII format.
– Only 1 long data can be defined per table.
– Long cannot be used in
• subqueries.
• Functions
• Expressions
• Where clause
• Table containing long cannot be clusterred
30
• RAW/Long RAW-
– Stores binary data(picture or image).
– Data loaded in these columns are stored without any further
conversions.
– Maximum length 255 bytes.
– Long RAW -Can contain upto 2 GB

31
 Practical No.3.Viewing data in a table
• The SELECT SQL verb is used to achieve this.
• It is used to select the data from the table.
• * is used to select all the columns from the table and
column name can be specified if selected columns data is
to be displayed.
• All Rows and All Columns-
– SELECT <column1> to <column N> From TableName;
Syntax-
SELECT *from <TableName>;
Select * from Customer;
32
 Filtering Table Data
• Selected columns and all rows
• Syntax
– SELECT <coulmn1>,<column2> from <TableName>;
– Example
– SELECT FNAME,LNAME FROM EMP_MSTR;

33
• Selected Rows and All columns
• Select * From <tablename> WHERE <condition>;
• Condition qualified as <column name=value>
• Example
• SELECT * from Branch_MSTR where NAME=‘vile
Parle(HO)’;

34
• Selected rows and Selected Columns
• SELECT <coulmn1>,<coulmn2> FROM <Tablename>
WHERE <condition>;
• Example:
• Select acct_no,branch_no from acct_mstr where
type=‘SB’;

35
• Eliminating Duplicates
• Distinct
• Syntax:
• SELECT DISTINCT <Column1>,<column2> from
<TableName>;
• SELECT DISTINCT * from <Tablename>;
• Example
• Select Distinct * from cust_mstr;
• Select Distinct Name from cust_mstr; 36
 Program 4
• Objective: Sorting the content of the table
• Syntax: SELECT * from <TableName> Order By
<column1>,<column2><[sort order]>;
• Description:
• The Order By clause is used to select the data from the
table in a sorted order. The rows retrieved will be stored in
either ascending or descending depending upon the order
specified.
• Example:
• Select * from Customer order by Name; 37
• To view the data in descending order the word DESC
must be mentioned after the column name and before the
semicolon in the order by clause.
• In case there is no mention of the sort order by default it is
ascending.
• Select * from branch_mstr order by name desc;

38
 Describe

• Describe is used to describe the structure of the table

• Syntax:
• DESCRIBE <tablename>;
• Example
• Describe cust_mstr;

39
 Creating a table from another table

• Create table <tablename>

(<columnname>,<columnname>) as select
<columnname>,<columnname> from <tablename>;

40
 Practical 5.Updating the contents of the table

• The UPDATE statement is used to update columns in the

existing tables row with new values.
• Update all rows
• UPDATE <Tablename> SET
<Columnnme1>=<Expression1>,
<columnname2>=<expression2>;
• Example:
• UPDATE client_master SET city=‘Bombay’;

41
• Updating a select set of rows
• Syntax:
• UPDATE <tablename> SET <Column1>=<Expression1>,
<cloumn2>=<expression2> WHERE <condition>;
• Example:
• UPDATE Branch_mstr SET name=‘head office’ WHERE
Name=‘Vile Parle(HO)’;
• UPDATE Client_master SET City=‘Bombay’ WHERE
Cliennt_no=‘c005’; 42
Program 6. TO MODIFY OR ALTER THE
STRUCTURE OF A TABLE
• Objective: To use the command Alter
• 1. ALTER TABLE ADD
• 2. ALTER TABLE MODIFY
• Syntax:
• ALTER TABLE<Tablename> ADD
<Columnnme1><datatype>;
• ALTER TABLE<Tablename> MODIFY
<Columnnme1><datatype>;
43
• Description:
• The alter table statement is used to add, delete or modify
columns in an existing table.
• The alter table is used to add and drop the various
constraints on an existing table.
• Example:
• ALTER table Client_master ADD (telephone number(10));
• ALTER TABLE Client_master MODIFY (Pincode
number(12));
44
• ALTER table <tablename> rename column <old
columnname> to <newcolumn name>;

45
 Program 7
• Objective: To rename a table
• Syntax:
• ALTER TABLE<Tablename> RENAME <newtablename>
• Description:
• The ALTER table statement is used to rename a table
(Rename to is further used) of database.
• Example:
• ALTER TABLE Client_master RENAME TO Cman_Mast;

46
 Program 8
• Objective: To delete rows from a table
• Syntax:
• DELETE FROM <Tablename> WHERE [Condition];
• Description:
• The Delete statement is used to delete a single record or
multiple records from a table in Oracle database.
• Example:
• DELETE FROM Client_master WHERE
State=‘Maharashtra’;
47
 Program 09

• Objective: To destroy OR DROP a table.

• Syntax: DROP table table_name;
• Description: The drop table statement allows us to
remove a table from the database.
• Example: DROP table client_master;
• RESULT: Table dropped.

48
• Create the table : Customer with following columns:
create table customer( Cust_no number( 4)
Last_name varchar2(20),
first_name varchar2(20),
Add1 varchar2(20),
Add2 varchar2(20),
City varchar2(20),
State varchar2(20),
Birth_date date,
Status varchar2(10)
);

49
 Add fields interactively

• Insert data in the table:

• Insert into customer values(1001, ‘Udupi’,
‘Raj’,’Upendrabaug’,’near Kalpana’,’udp’,’Karnataka’,’12-
dec-92’,’A’);
• Insert into customer values
(&cust_no,’&last_name’,’&first_name’,’&add1’,’&add2’,’&ci
ty’,’&state’,’&birth_date’,’&status’);
• Note: the names need not match the column name but
the order and the type must match.
50
51
• Changing the case of the string:
• UPPER: a function to convert the data in the UPPER
case.
• Syntax: UPPER(Column_name)
• LOWER: a function to convert the data in the UPPER
case.
• Syntax: LOWER(Column_name)

52
 Program 10

• Objective : Using Additional Operators with WHERE

• Description: In select statements various operators can be
made use of:
=(equal to) STATE=‘Karnataka’
>(greater than) Age>30
>=(greater than or equal to) Age>=30
< (less than) Age<30
<=(less than or equal to) Age<=30
<>(not equal to) Age<>30

53
• Select statements can have complex conditions(two or more
conditions) specified by the use of the words :AND ,OR ,NOT
• AND : ensures that rows satisfying both the conditions are
selected.
• Example: Select * from customer where
UPPER(State)=‘KARNATAKA’ AND UPPER (Status)=‘VALID’
• OR: ensures that rows satisfying any one of the conditions is
retrieved.
• Example: Select * from customer where
(UPPER(State)=‘KARNATAKA’ OR UPPER(State)=‘KERELA’)
AND UPPER (Status)=‘VALID’
54
• NOT: it is used to retrieve the rows that does not has the
value written with not.
• Example: Select * from customer where NOT
Cust_no=1005;
• LIKE ‘pattern’: the pattern can contain wild card
characters-%(percentage) and _(underscore).
• % is used to denote any number of unknown characters
and _ denotes one unknown character. To denote more
than one unknown characters the no. of _ must be equal
to the no. of unknown characters.
55
• Example:
1. to retrieve rows where state name begins with K and followed by
any other character. Like Karnataka or kerela
– Select * from customer where state LIKE ‘K%’;
2. Retrieve rows where the first name contains the word RAJ
embedded in it.
Select * from customer where first_name LIKE ‘%Raj%’;
3. Retrieve rows where the address contains the word UDUPI or
UDIPI in which the 3rd character can be anything. This is done using
the underscore.
Select * from customer where address LIKE ‘UD_PI’;
56
• BETWEEN n1 and n2
• This operator can be used to retrieve the rows which fall
within a range.
• Example: Select * from customer where cust_no
BETWEEN 1003 AND 1005;(both 1003 and 1005
included)
• OR
• Select * from customer where cust_no >=1003 AND
cust_no<=1005;
57
• IN operator: is used to specify a list of possible values for
a column.
• Example: select * from customer where CITY
IN(‘Udp’,’Mng’,’pjm’,’Mar’);
• It is same as :
• select * from customer where CITY=‘udp’ or CITY =‘Mng’
Or CITY =‘Pjm’ or CITY=‘Mar’;

58
 Program 11
• Objective: To rename a column while displaying a result.
• Syntax:
• SELECT Column_name1 NEW_Column_Name1,
column_name2 NEW_Column_name2…….. From
table_name;
• Description:
• This type of select statements are used to change the
column names while fetching the result from the tables..
• Example:
59
• SELECT Client_no Client_Identity, Name Client_name,
City Client_city from client_master;
• Result:
• 6 rows selected
Client_identity Client_name Client_city

60
 Program 12
• Objective: To learn about dual(use mathematical
operations)
• Syntax:
SELECT statement1, statement2 from Dual;
• Description:
• The DUAL table is a special one-row, one-column table
present by default in oracle and other database
installations. Oracle’s SQL syntax requires the FROM
clause but some queries don’t require any tables. DUAL
can be readily used in these conditions. 61
• Example:
• SELECT power(2,3) from dual;
• SELECT 43+23,56-23 from dual;
Power(2,3) 43+2 56-23
8 45 33

62
• Select sysdate from dual;
• Output:
• Sysdate
• 05-Apr-21

63
 Program 13
• Objective: To create various functions:
• Avg()
• Sum()
• Max()
• Min()
• Count(attributes)
• Count(*)
• Power(m,n)

64
• Syntax:
• SELECT avg(column_name) “title” from Table_Name;
• SELECT sum(column_name) “title” from Table_Name;
• SELECT max(column_name) “title” from Table_Name;
• SELECT min(column_name) “title” from Table_Name;
• SELECT count(column_name) “title” from Table_Name
where condition;
• SELECT count(*) “title” from Table_Name where
condition;
• Select Power(m,n) from Dual; 65
• Description:
• Avg(): to calculate the average of the values of the given
column, Applicable for only numeric types.
• Sum(): to calculate the sum of the values of given column
• Max(): to find the maximum value from the given column.
• Min(): to find the minimum value from the given column
• Count(attribute): count the number of values present in a
column for a given condition.
• Count(*): count the number of rows of a given condition
with or without null. 66
• Power(m,n): to find the nth power of a number m.
• ABS(n): returns absolute value of ‘n’
• Example:
• SELECT avg(sell_price) “average_sell_price” from
Product_Master;
• SELECT max(sell_price) “maximum_price” from
Product_Master;
• SELECT min(sell_price) “minimum_price” from
Product_Master; 67
• SELECT sum(sell_price) “sum_price” from
Product_Master;
• SELECT count(sell_price) “count” from Product_Master
where sell_price>3000;
• SELECT avg(sell_price) “average_sell_price” from
Product_Master;
• SELECT count(*) “all_rows” from Product_Master;
• SELECT power(2,3) from dual;
• Select abs(-15) “absolute” from dual;
68
 Program 14
• Objective: To enforce various data constraints
– NOT NULL
– UNIQUE
– PRIMARY KEY
– FOREIGN KEY
• Syntax: NOT NULL
create table table_name(column1 datatype (size),column2 datatype (size)
NOT NULL, …..) ;
• Description:
• NOT NULL when specified for a column never takes a null value for that
column and if specified the whole tuple is either dropped or being removed
from the table.

69
• Example: create table Engineer(enrolment
varchar2(10),Name varchar2(20), course varchar2(6),
Semester varchar2(6), department varchar2(15) NOT
NULL));
• Result: 1 table created
• UNIQUE:
• Syntax: create table table_name(column1 datatype(size),
column2 datatype(size), …..column datatype(size)
Unique);
70
• Description: A Unique constraint is a single field or
combination of fields that uniquely defines a record. Some
of the fields can be containing null values as long as the
combination of values is unique.
• Example: create table Engineer(enrolment varchar2(10)
UNIQUE,Name varchar2(20), course varchar2(6),
Semester varchar2(6), department varchar2(15) NOT
NULL));

71
• PRIMARY KEY:
• SYNTAX: create table table_name(column1
datatype(size) Primary key, column2 datatype(size),
…..column datatype(size) );
• Description: A primary key is a single field or combination
of fields that are part of the primary key . It cannot contain
a null value, and a table can have only one Primary key.
• Example: create table Engineer(enrolment varchar2(10)
PRIMARY KEY,Name varchar2(20), course varchar2(6),
Semester varchar2(6), department varchar2(15));
72
• Foreign key:
• Syntax: create table table_name1(column1 datatype(size)
Primary key, column2 datatype(size), …..column
datatype(size));
• create table table_name2(column1 datatype(size),
column2 datatype(size) Foreign key REFERENCES
table_name1, …..column datatype(size));

73
• Description: A foreign key is a way to enforce referential
integrity within your oracle database. A foreign key means
that values in one table must also appear in another table.
• The referenced table is called the parent table while the
table with foreign key is called the child table. The foreign
key in the child table generally references the primary key
in the parent table.

74
• Example:
• create table Engineer1(enrolment varchar2(10) PRIMARY
KEY,Name varchar2(20), course varchar2(6), Semester
varchar2(6), department varchar2(15) );
• create table Eng_student(enrolment varchar2(10),Name
varchar2(20), class varchar2(6), Division
varchar2(20),stream varchar2(15), FOREIGN KEY
(enrolment) REFERENCES Engineer1);

75
 Program 15

Objective: To use GROUP BY and HAVING Clause

• The GROUP BY clause in SQL groups rows that have the
same values in specified columns into summary rows,
allowing aggregate functions to be applied to each group.
• The HAVING clause then filters these grouped results
based on a specified condition, typically involving
aggregate functions.

76
• Syntax:
• SELECT column1, aggregate_function(column2)
FROM table_name
WHERE condition_on_individual_rows -- Optional: Filters
rows before grouping
GROUP BY column1, column3, ... -- Columns to group by
HAVING condition_on_grouped_rows -- Filters groups
based on aggregate results
ORDER BY column_or_aggregate_function; -- Optional:
Sorts the final result 77
Example
• SELECT CustomerID, SUM(OrderTotal) AS
TotalOrderValue FROM Orders GROUP BY CustomerID
HAVING SUM(OrderTotal) > 500;

78
79
 Program 16

• Objective: To use alter table for adding and dropping

constraints.
• Syntax: create table table_name (column1 datatype(size),
column2 datatype(size));
• Alter table table_name add constraint constraint_name
primary_key/foreign_key(column_name);
• Alter table table_name drop constraint constraint_name;

80
• Description: Alter table statement with add constraint are
used to add a constraint to a column of a given table.
• Alter table statement with drop constraint are used to
remove a constraint from a column of a given table.
• Example: create table branch(branch_id varchar2(10),
branch_name varchar2(20), branch_add varchar2(20));
• Alter table branch add constraint pk primary key
(branch_id);
Alter table branch drop constraint pk;

81
 Program 17

• Objective: Consider the following relation:

Emp(ename,company_name,salary)
• Write SQL Queries:
• Find the total salary of each company.
• Find the company name whose salary is less than lowest
averag
• Find the employee name whose salary is higer than
highest ave
82