RATHINAM TECHNICAL

CAMPUS
RATHINAM TECHZONE
POLLACHI MAIN ROAD, EACHANARI, COIMBATORE-641021.

DEPARTMENT OF ARTIFICIAL INTELLIGENCE AND DATA SCIENCE

RECORD NOTE BOOK

22CS403 DATABASE MANAGEMENT SYSTEM LABORATORY

NAME :

REGISTER NUMBER :

YEAR/SEMESTER :

ACADEMIC YEAR :
 RATHINAM TECHNICAL CAMPUS
RATHINAM TECHZONE
POLLACHI MAIN ROAD, EACHANARI, COIMBATORE-641021.

BONAFIDE CERTIFICATE
NAME :

ACADEMIC YEAR :

YEAR/SEMESTER :

BRANCH :

UNIVERSITY REGISTER NUMBER: ……………………………….

Certified that this is the bonafide record of work done by the above student in the

Laboratory during the year 2023-2024.

Head of the Department Staff-in-Charge

Submitted for the Practical Examination held on

Internal Examiner External Examiner

 INDEX
S.No Date Experiment Name Page No: Marks Staff Sign
 INDEX
S.No Date Experiment Name Page No: Marks Staff Sign
 Database design using Conceptual modeling (ER-EER)
Aim:

To design a database for the movie management using Conceptual modeling

(ER-EER).

ER diagram for a

The entities to be included in our ER diagram are,

 Members - this entity will hold member information.

 Movies - this entity will hold information regarding movies
 Categories - this entity will hold information that places movies into
different categories such as "Drama", "Action", and "Epic" etc.
 Movie Rentals - this entity will hold information about movies rented out to members. 
 Payments - this entity will hold information about the payments made by members. 

Relationship involved in ER Diagram:


Entity Relationship Diagram 


ER Diagram using Crow foot notations

 membership_id
transaction_date Movie_id

reference_no MovieRentals Return date

Movie_id
membership_id
full_names
title

gender
director
Members Movies

date_of_birth
Year_released

physical_ Has
address category
postal_
Address

Do Has

membership_id
Category_id

payment_id

Members Movies Category_name

paymentd
ate

reference_no remarks

description
Amt_paid
Result:

Thus the Database has been designed for the movie management using
Conceptual modeling (ER-EER).
 EX.NO:2 Database Implementation using SQL Data definition and
Data manipulation Language with constraints
DATE:

Aim:
Create a database table, add constraints (primary key, unique, check, Not null), insert rows,
update and delete rows using SQL DDL and DML commands.

SQL – Create Command:

Create database ex2_36;

Create table student_36(Name varchar(30) NOT NULL,Roll_no int primary key,Age int
CHECK(Age>18),stud_id varchar(20) UNIQUE,Marks int);

SQL – Alter Command:

i) Add a new column to the student table.

Alter table student_36 add column dept varchar(20);

ii) Adding multiple columns to the table

 Alter table student_36 add column Average int,add column Gender char(1),add
column email varchar(20);

iii) Modify the data type of column average to decimal

Alter table student_36 alter column Average type float;

iv) Remove a column from the table.

Alter table student_36 drop column age;

v) Renaming a column name

Alter table student_36 rename column dept to Department;

SQL – Truncate Command:

i) Removing data from the table.
Truncate table student_36;
 SQL – Drop Command:

i) Removing the table from DB.

Drop table student_36;

SQL – Insert Command:

Insert into student_36
values(‘niranjan’,22101902,20,’BCA02’,’78’),(‘nancy’,22101920,19,’BBA20’,’84’),
(‘noel’,22101935,20,’IT35’,’70’),(‘nivetha’,22101928,20,’AIDS35’,’88’),
(‘naveen’,22101943,19,’CSE43’,’74’);

SQL – Update Command:

Update student_36 set Average=245,Gender=’f’,email=’niranjan34@gmail.com’,
dept=’Arts’ where name=’ niranjan’;
Update student_36 set Average=334,Gender=’m’,email=’just136@gmail.com’,
dept=’computer’ where name=’nancy’;
Update student_36 set Average=445,Gender=’m’,email=’noel34@gmail.com’ ,dept=’Infotech’
where name=’noel’;
Update student_36 set Average=545,Gender=’m’,email=’nivetha28@gmail.com’
,dept=’AI&DS’ where name=’nivetha’;
SQL – Delete Command:

Delete from student_36 where average=545;

Result:
Thus the Database has been implemented using SQL DDL and DML commands with constraints.
 EX.NO: 03 Implementing referential integrity
constraints on tables
DATE:

Aim:
To create a set of tables, add foreign key constraints and incorporate referential integrity

Creating tables to implement Referential integrity constraints:

 create table Department_36(dept_id int primary key,dept_name varchar(40));

 create table Employee_36(employee_id int primary key,employee_name

varchar(40),dept_id int,foreign key (dept_id) references Department_36(dept_id));

 \d Employee_36;

Result:
Thus, the tables with referential integrity constraints has been implemented.
EX.NO:4
Query the database using SQL Where Clause

DATE:

Aim:
To query the database using where clause.

1.Create customer , Branch , Account and Loan table with appropriate

fields and perform the following

CUSTOMER:
create table customer_36 ( custid varchar(20) primary key, fname varchar(20),mname
varchar(20), ltname varchar(20), city varchar(20),mobileno bigint , occupation varchar(20),
dob date);

BRANCH:
create table branch_36(bid int primary key,bname varchar(30),bcity varchar(30));

ACCOUNT:
Create table account_36(acnumber varchar(6) primary key,custid varchar(10), foreign
key(custid) references customer_36(custid),bid int, foreign key(bid) references
branch_23(bid),opening_balance int,aod date,atype varchar(10),astatus varchar(10));

LOAN:
Create table loan_36(loan_number varchar(10) primary key, custid varchar(10),bid int,
loan_amount int, foreign key (custid) references customer_36(custid),foreign key(bid)
references branch_36(bid));

a) Write a query to display the customer number, firstname, customer’s date of birth.
Display in sorted order of date of birth year and within that sort by firstname.

select custid, fname, dob from customer_36 order by dob,fname;

b) Write a query to display the customer number, customer firstname,account number

for the customer’s whose accounts were created after 15th of any month

Select c.fname , c.custid , a.acnumber from customer_36 c join account_36 a on

c.custid = a.custid;

c) Display the Customer_id and Customer_name who have taken less than two loans.

select c.custid , concat(c.fname,' ',c.mname,' ',c.ltname) as customer_name from

customer_36 c join loan_36 l on c.custid = l.custid group by c.custid , customer_name
having count(l.loan_number) < 2;

1. Update, Delete & Retrieval operation using SQL

a) Modify the balance attribute alone such that it decreases the amount by 10% for the
account table

Update Account_36 set opening_balance = opening_balance*0.9;

b) Delete all the account tuples in the ‘Delhi’ branch.

Delete from account_36 where bid=(select bid from branch_36 where bcity =
'Delhi');

c) Delete all loans with loan amounts between 10000 to 20000.

delete from loan_36 where loan_amount between 10000 and 20000;

d) Find the names of all branches in the loan relation.

select distinct b.bname from branch_36 b join loan_36 l on b.bid= l.bid;

e) Display all the Customer names whose come from either Mumbai or Hydrabad

select concat(fname,' ',mname,' ',ltname) as customer_name from customer_36 where

city = 'mumbai';

f) Find all loan (numbers) for loans made at the ‘Adyar’ branch with loan amount
greater than 50000.

Select loan_number from loan_36 where loan_amount>50000 and bid = (select bid
from branch_36 where bname = ‘Adyar’);

g) Find loan( numbers )of those loans with loan amount between 10000 and 20000.
 select loan_number from loan_36 where loan_amount between 10000 and 20000;

h) Display the customer name in alphabetical order

Select concat(fname,' ',mname,' ',ltname) as customer_name from customer_36 order by

fname;

i) Display all the customer names ordered by customer city

select concat(fname,' ',mname,' ',ltname) as customer_name from customer_36 order by city;

j) Give a count of how many account holds are in each branch

select count(a.acnumber) , b.bname from branch_36 b join account_36 a on a.bid =

b.bid group by b.bname;

2. Create a table ‘Employee’ with the following details(Column level

constraints)
Empno Number(5) Primary key
EmpFname Varchar2(20)
EmpLname Varchar2(20)
Designation Varchar2(10)
Date_of_join Date
JobID Varchar2(15)
Salary Number(9,2) NOT NULL
Depno Number(2) Foreign key(Reference ‘Department’ table)

create table employee_36( empno int primary key, empfname varchar(20), emplname
varchar(20), designation varchar(10), date_of_join date, jobid varchar(20), salary int not null,
depno int, foreign key(depno) references department_36(depno));

3. Create Employee table and perform the following operations

a) Create a query to display the last name, hire date, and employee number for each
employee, with employee number appearing first.

Select emplname, empno, date_of_join from employee_36;

b) Display employees records whose salary is greater than 20000.

select * from employee_36 where salary > 20000;

c) Create a query to display the last name and salary of employees whose salary is not in
the range of 2000 and 80000. (hints: not between )
Select emplname,salary from employee_36 where salary not between 2000 and
80000;

d) Display the employee last name, job ID, and start date of employees hired between
01-MAR-1995 and May 1,2010. (hints: between)
 select emplname , JobId,date_of_join from employee_36 where date_of_join between
('01-03-1995') and ('01-05-2010');

e) Display the last name and hire date of every employee who was hired in 1994.(hints:
like)

select emplname, date_of_join from employee_36 where extract (year from

date_of_join) = 1994;

4. Create another table called ‘Department’ with the following

structure(Column level constraints)
Depno Number(2) Primary key
Depname Varchar2(15)
Deplocation Varchar2(10)

Create table department_36(depno int primary key, depname varchar(15),deplocation

varchar(10));

a) Display the number of employees in each department with the department numbers in
descending order

select depno,count(*) as num_of_employees from employee_36 group by depno order

by depno desc;

b) List the departments where there are employees functioning

select d.depname from department_36 d join employee_36 e on d.depno = e.depno;

c) List the employees whose depno is not equal to ‘01’

select * from employee_36 where depno not in (1);

d) Add the field total to table student

ALTER TABLE student_36 ADD COLUMN total NUMERIC(9,2);

e) Insert 5 records into the table. Don’t input values for total field and calculate total
field.

create table sales_36(name varchar(20), quantity int, price int);

INSERT INTO sales_36(name, quantity, price) VALUES ('Product 1',10, 5.99),

('Product 2',5, 9.99),('Product 3',8, 3.49),('Product 4',15, 7.25),('Product 5',20,2.99);

alter table sales_36 add column total int;

update sales_36 set total = quantity*price;

f) List the employee names and their department names

 Select e.emplname, d.depname from department_36 d join employee_36 e on
d.depno = e.depno;

Result:
Thus the Database has been queried using SQL Where Clause.
EX.NO:5
Implementing Aggregate functions in database tables
DATE:

Aim:
To implement aggregate functions in database tables.

Customers:

cust_id cust_nam cust_address cust_city cust_stat cust_zip cust_c cust_contac cust_

e e ountry t email
Village 200 Maple sales
Toys Lane @vill
ageto
100000 ys.co
0001 Detroit MI 44444 USA John Smith m
100000 Kids 333 South Michelle
0002 Place Lake Drive Columbus OH 43333 USA Green NULL
1 Sunny
Place jjones
@fun4
100000 all.co
0003 Fun4All Muncie IN 42222 USA Jim Jones m
829
Riverside dsteph
Drive ens@f
100000 Denise L. un4all.
0004 Fun4All Phoenix AZ 88888 USA Stephens com
100000 The Toy 4545 53rd Kim NUL
0005 Store Street Chicago IL 54545 USA Howard L

Vendors:
vend_id vend_name vend_address vend_city vend_state vend_zip vend_country
123 Main Bear
BRS01 Bears R Us Street Town MI 44444 USA
Bear 500 Park
BRE02 Emporium Street Anytown OH 44333 USA
Doll House 555 High
DLL01 Inc. Street Dollsville CA 99999 USA
 1000 5th New
FRB01 Furball Inc. Avenue York NY 11111 USA
Fun and 42 Galaxy
FNG01 Games Road London NULL N16 6PS England
1 Rue
JTS01 Jouets et ours Amusement Paris NULL 45678 France

OrderItems :

order_num order_item prod_id quantity item_price

20005 1 BR01 100 5.49
20005 2 BR03 100 10.99
20006 1 BR01 20 5.99
20006 2 BR02 10 8.99
20006 3 BR03 10 11.99
20007 1 BR03 50 11.49
20007 2 BNBG01 100 2.99
20007 3 BNBG02 100 2.99
20007 4 BNBG03 100 2.99
20007 5 RGAN01 50 4.49
20008 1 RGAN01 5 4.99
20008 2 BR03 5 11.99
20008 3 BNBG01 10 3.49
20008 4 BNBG02 10 3.49
20008 5 BNBG03 10 3.49
20009 1 BNBG01 250 2.49
20009 2 BNBG02 250 2.49
20009 3 BNBG03 250 2.49

Orders :
order_num order_date cust_id
20005 01-05-2004 00:00 1000000001
20006 12-01-2004 00:00 1000000003
20007 30-01-2004 00:00 1000000004
20008 03-02-2004 00:00 1000000005
20009 08-02-2004 00:00 1000000001

Products:
prod_id vend_id prod_name prod_price prod_desc
BR01 BRS01 8 inch teddy bear 5.99 8 inch teddy bear, comes with cap and
jacket
12 inch teddy 12 inch teddy bear, comes with cap and
BR02 BRS01 bear 8.99 jacket
18 inch teddy 18 inch teddy bear, comes with cap and
BR03 BRS01 bear 11.99 jacket
Fish bean bag Fish bean bag toy, complete with bean
BNBG01 DLL01 toy 3.49 bag worms with which to feed it
Bird bean bag
BNBG02 DLL01 toy 3.49 Bird bean bag toy, eggs are not included
Rabbit bean bag Rabbit bean bag toy, comes with bean
BNBG03 DLL01 toy 3.49 bag carrots
RGAN01 DLL01 Raggedy Ann 4.99 18 inch Raggedy Ann doll
RYL01 FNG01 King doll 9.49 12 inch king doll with royal garments and
crown
RYL02 FNG01 Queen doll 9.49 12 inch queen doll with royal garments and
crown

Problems:
1. How many bean bag toys are in the Products table? select count(*) from products_36
where prod_desc like '%bean bag%';

2. How many customers have yet to provide their email?

select count(*) from customer_36 where cust_email is null;

3. What is the average price of products that are 12 inches long?

select avg(prod_price) from products_36 where prod_name like '%12 inch%';

4. How much would it cost to order 100 units of the most expensive product?

select max(prod_price)*100 as cost from products_36;

5. How much would it cost to order 10 quantities of the 3 most expensive products?

Select sum(prod_price * 10) as cost from products_36 where prod_price in

(select prod_price from products_36 order by prod_price desc limit 3);
 6. How many vendors are incorporated?

Select count(80 from vendors_36;

Result:
Thus, the aggregate functions has been implemented in database tables.
EX.NO:6

DATE: SEQUENCES AND IDENTITY

AIM:

To create a database and apply Sequences and Identity.

SQL – Sequence:
Syntax:
CREATE SEQUENCE sequence_name
[ AS { data_type | DEFAULT integer_literal } ]
[ INCREMENT BY increment ]
[ START WITH start ]
[ MINVALUE minvalue | NOMINVALUE ]
[ MAXVALUE maxvalue | NOMAXVALUE ]
[ CYCLE | NOCYCLE ]

[ CACHE cache | NOCACHE ];

Queries:
1. Query to create a sequence in ascending order.
CREATE SEQUENCE ascending_36 START WITH 1 INCREMENT BY 1;

2. Query to create a sequence in descending order.

CREATE SEQUENCE sample2_36 START WITH 100 INCREMENT BY -1 MINVALUE -999999
MAXVALUE 100;

3. Using sequence in the table.

CREATE TABLE sample_36( id varchar(20) PRIMARY KEY,name VARCHAR(50));
 INSERT INTO sample_36(id, name)VALUES ('22102036', 'Naveen');

4. Altering a Sequence.
ALTER SEQUENCE ascending_36 INCREMENT BY 2;

5. Dropping a Sequence.
drop sequence ascending_36;

SQL – Identity:
SQL, an identity column is a column in a table that automatically generates numeric values. It's
commonly used for primary key columns to ensure each row has a unique identifier. Different
database systems have slightly different syntax for defining an identity column.

Syntax:
CREATE TABLE table_name(column_name data_type PRIMARY KEY GENERATED
ALWAYS AS IDENTITY (start_value, increment_value));

Queries:
1. Creating a table with the Identity generated always.
CREATE TABLE Tab1_36(ID INT GENERATED ALWAYS AS IDENTITY,Name VARCHAR(50));

2. Creating a table with the Identity generated by default.

CREATE TABLE Tab2_36( ID INT GENERATED BY DEFAULT AS IDENTITY, Name VARCHAR(50));

3. Inserting values into the tables with identity.

INSERT INTO Tab1_36(Name) VALUES ('NAVEEN'), ('NETHAJI'), ('NETHRA');
 4. Creating a table with Identity using sequence options.
CREATE TABLE Tab3_36( ID SERIAL PRIMARY KEY, Name VARCHAR(50));

5. Adding an Identity column to the existing table.

ALTER TABLE tab1_36 ADD identity INT GENERATED ALWAYS AS IDENTITY;

6. Changing an Identity Column.

ALTER TABLE Tab1_36 ALTER COLUMN ID RESTART WITH 50;

7. Removing the Identity constraint from the table.

ALTER TABLE Tab1_36 ALTER COLUMN ID DROP IDENTITY;

Result:
Thus the database created, Sequences and Identity has been applied successfully.
 EX.NO:7 QUERYING THE DATABASE USING SQL SUB
DATE: QUERIES AND JOINS

Aim:
To query or manage the database using SQL Sub queries and Joins.

Table: Employees:
EMP_I EMP_NA JOB_NA MANAGER HIRE_DA SALA COMMISSI DEP_I
D
D ME ME _ID TE RY ON

68319 KAYLING PRESIDE 1991-11-18 60000 1001

NT
66928 BLAZE MANAGE 68319 1991-05-01 27500 3001
R
67832 CLARE MANAGE 68319 1991-06-09 25500 1001
R
65646 JONAS MANAGE 68319 1991-04-02 29570 2001
R
67858 SCARLET ANALYS 65646 1997-04-19 31000 2001
T
69062 FRANK ANALYS 65646 1991-12-03 31000 2001
T
63679 SANDRIN CLERK 69062 1990-12-18 9000 2001
E
64989 ADELYN SALESM 66928 1991-02-20 17000 400 3001
AN
65271 WADE SALESM 66928 1991-02-22 13500 600 3001
AN
66564 MADDEN SALESM 66928 1991-09-28 13500 1500 3001
AN
68454 TUCKER SALESM 66928 1991-09-08 16000 0 3001
AN
68736 ADNRES CLERK 67858 1997-05-23 12000 2001
69000 JULIUS CLERK 66928 1991-12-03 10500 3001
69324 MARKER CLERK 67832 1992-01-23 14000 1001

Table: Department:
DEP_ID DEP_NAME DEP_LOCATION

1001 FINANCE SYDNEY

2001 AUDIT MELBOURNE
3001 MARKETING PERTH
4001 PRODUCTION BRISBANE

Table: Salary Grade:

SALARY MIN_SAL MAX_SAL
1 800 1300
2 1301 1500
3 1501 2100
4 2101 3100
5 3101 9999

Queries:
1. write a SQL query to find the managers. Return complete information about the
managers.
select * from employees_36 where job_name= 'MANAGER';

2. write a SQL query to compute the experience of all the managers. Return employee ID,
employee name, job name, joining date, and experience.
Select emp_id, emp_name, job_name, hire_date, (current_date – hire_date)/365 as
experience from employees_36 where job_name = 'MANAGER';
3. write a SQL query to find those employees who work as 'MANAGERS' and 'ANALYST'
and working in ‘SYDNEY’ or ‘PERTH’ with an experience more than 5 years without
receiving the commission. Sort the result-set in ascending order by department location.
Return employee ID, employee name, salary, and department name.
select e.emp_id, e.emp_name, e.salary, d.dep_name from employees_36 e join
departments_36 d on e.dep_id = d.dep_id where e.job_name in
(‘MANAGER’,’ANALYST’) AND d.dep_location in (‘SYDNEY’,’PERTH’) AND
(current_date-e.hire_date)/365 >5 and e.commission is null order by d.dep_location asc ;

4. write a SQL query to find those employees of department 1001 and whose salary is more
than the average salary of employees in department 2001. Return complete information
about the employees.
select * from employees_36 where dep_id = 1001 and salary > (select
avg(salary) from employees_36 where dep_id = 2001);

5. write a SQL query to find the employees of grade 2 and 3.Return all the information of
employees and salary details.
Select e.*, s.min_salary, s.max_salary from employees_36 e join salarygrades_36 s on e.salary
between s.min_salary and s.max_salary where s.salary_grades in (2,3);
 a. Write a SQL query to find those employees who work as same designation of
FRANK. Return complete information about the employees.
select * from employees_36 where job_name = (select job_name from employees_36
where emp_name = 'FRANK');

6. write a SQL query to find those employees of department ID 2001 and whose designation
is same as of the designation of department ID 1001. Return complete information about
the employees.
Select * from employees_36 where dep_id = 2001 and job_name in (select
job_name from employees_36 where dep_id = 1001);

7. write a SQL query to find those employees whose salary is the same as the salary of
FRANK or SANDRINE. Sort the result-set in descending order by salary. Return
complete information about the employees.
Select * from Employees_36 where salary in (select salary from Employees_36
where emp_name in ('FRANK','SANDRINE')) order by salary desc;
8. write a SQL query to find those employees whose salary is more than the total
remuneration (salary + commission) of the designation SALESMAN. Return complete
information about the employees.
Select * from Employees_36 where salary > (SELECT MAX(salary + commission)
from Employees_36 where job_name in ('SALESMAN'));

9. write a SQL query to find those employees who are senior to BLAZE and working at
PERTH or BRISBANE. Return complete information about the employees.
select * from Employees_36 where hire_date < (select hire_date from Employees_36
where emp_name = 'BLAZE') and dep_id in (select dep_id from Departments_36 where
dep_location in('PERTH','BRISBANE'));

10. write a SQL query to list any job of department ID 1001 which are not found in
department ID 2001. Return job name.
select distinct job_name from employees_36 where dep_id = 1001 and job_name not
in (select job_name from employees_36 where dep_id = 2001);

11. From the following table, write a SQL query to find the highest paid employee. Return
complete information about the employees.
Select * from employees_36 where salary = (select max(salary) from employees_36);
 12. write a SQL query to find the highest paid employees in the department MARKETING.
Select * from Employees_36 where dep_id = (select dep_id from Departments_36
where dep_name = 'MARKETING') order by salary desc;

13. write a SQL query to find those employees who are senior employees as of year 1991.
Return complete information about the employees.
select * from employees_36 where hire_date < '1992-01-01';

Result:
Thus the Database has been queried using SQL Sub Queries and Joins.
 EX.NO:8 QUERYING THE DATABASE USING SQL
DATE: PROGRAMMING

AIM:
To query or manage the database using SQL Stored Procedures/Functions.
SQL PROGRAMMING – STORED PROCEDURES/FUNCTIONS:
1. Write a PL/PGSQL code block to find sum and average of three numbers
CREATE PROCEDURE calculation_36 (a int, b int, c int)

AS $$
BEGIN
RAISE NOTICE 'SUM:%',(a+b+c);
RAISE NOTICE 'AVERAGE:%',(a+b+c)/3;
END;
$$ LANGUAGE plpgsql;

2. Write a PL/PGSQL code block to find Simple Interest.

CREATE PROCEDURE simpleinterest_36(principal int, rate int, time_period int)

AS $$
DECLARE
interest int;
BEGIN
interest := (principal * rate * time_period) / 100;

RAISE NOTICE 'Simple Interest: %', interest::text;

END;
 $$ LANGUAGE plpgsql;

3. Write a PL/PGSQL code block to find area of circles with radius 7.

CREATE PROCEDURE areaofcircle_36()
AS $$
DECLARE
radius INTEGER := 7;
area FLOAT;

BEGIN
area := PI() * radius * radius;
RAISE NOTICE 'Area of circle with radius %: %', radius, area;
END;
$$ LANGUAGE plpgsql;

4. Write a PL/PGSQL code block to find factorial of a number.

CREATE PROCEDURE factorial_36(n INT)

AS $$
DECLARE
result int;
BEGIN
IF n = 0 THEN

RAISE NOTICE 'Cannot provide factorial';

ELSE
result := n * factorial(n - 1);
RAISE NOTICE 'Factorial:%',result;
END IF;
END;

$$ LANGUAGE plpgsql;

5. Write a PL/ PGSQL code block to find reverse of a number.

CREATE PROCEDURE reversenumber_36(n INTEGER)
AS $$
DECLARE
reversed INTEGER = 0;

remainder INTEGER;
BEGIN
 WHILE n > 0 LOOP
remainder = n % 10;
reversed = reversed * 10 + remainder;
n = n / 10;

END LOOP;
RAISE NOTICE 'Reversed number:%',reversed;
END;
$$ LANGUAGE plpgsql;

6. Write a PL/ PGSQL code block to find greatest of three numbers.

CREATE PROCEDURE find_greatest_36(a INTEGER, b INTEGER, c INTEGER)
AS $$
DECLARE

greatest INTEGER;
BEGIN
IF a >= b AND a >= c THEN
greatest = a;
ELSIF b >= a AND b >= c THEN
greatest = b;
 ELSE
greatest = c;
END IF;
RAISE NOTICE 'Greatest:%',greatest;

END;
$$ LANGUAGE plpgsql;

7. Write a PL/ PGSQL code block to generate Fibonacci series.

CREATE PROCEDURE fibonacciseries_36 (n INTEGER)
AS $$
DECLARE
a INTEGER = 0;
b INTEGER = 1;
temp INTEGER;
i INTEGER = 0;
BEGIN

IF n <= 0 THEN
RAISE NOTICE '0';
END IF;
 IF n = 1 THEN
RAISE NOTICE '1';
END IF;
FOR i IN 3..n LOOP

temp = a + b;
a = b;
b = temp;
END LOOP;
RAISE NOTICE 'fibonacci:%',temp;
END;

$$ LANGUAGE plpgsql;

8. Write a PL/PGSQL function to calculate the income tax for the given employee:
Conditions:
If pay < 2,50,000, no tax is charged.
If pay is >= 2,50,001 and <= 5,00,000, 5% of pay is charged as tax.
If pay is >= 5,00,001 and <= 10,00,000, 20% of pay is charged as tax.
If pay is >= 10,00,001, 30% of pay is charged as tax.

CREATE PROCEDURE calculate_income_tax_36(pay numeric)

AS $$
DECLARE
 tax_amount numeric;
BEGIN
IF pay <= 250000 THEN
tax_amount := 0;
ELSIF pay >= 250001 AND pay <= 500000 THEN
tax_amount := (pay - 250000) * 0.05;
ELSIF pay >= 500001 AND pay <= 1000000 THEN
tax_amount := 12500 + (pay - 500000) * 0.20;
ELSE
tax_amount := 112500 + (pay - 1000000) * 0.30;
END IF;
RAISE NOTICE 'Tax amount:%',tax_amount;
END;
$$ LANGUAGE plpgsql;

9. An Electricity Board charges the following rates to domestic users to discourage large
consumption of energy:
For the first 1 - 200 units { 1 - 100 units = Rs. 1.00, 101 - 200 units = Rs.1.50 }
From 201 - 500 units{ 1 - 200 units = Rs. 2.00 and 201 - 500 units = Rs. 3.00 }
Above 500 units{ 1 - 200 = Rs. 3.50, 201- 500 unit = Rs. 4.60 &above 500 unit
Rs.6.60}
Write a PL/PGSQL program to read the connection_id of users and number of units
consumed, display the charges with names.

Create table users_36(id SERIAL PRIMARY KEY,name varchar(100));

insert into users_36 (name) values('John'),('Alice'),('Bob');

CREATE OR REPLACE FUNCTION

calculate_electricity_charges_36(connection_id INT, units_consumed INT)
RETURNS TABLE(name1 VARCHAR(100), charges NUMERIC)
 AS $$
DECLARE
name_var VARCHAR(100);
charge_per_unit NUMERIC;
BEGIN
SELECT name INTO name_var FROM users_36 WHERE id = connection_id;
IF units_consumed <= 100 THEN
charge_per_unit := 1.00;
ELSIF units_consumed <= 200 THEN
charge_per_unit := 1.50;
ELSIF units_consumed <= 500 THEN
charge_per_unit := 2.00;
ELSIF units_consumed <= 1000 THEN
charge_per_unit := 3.50;
ELSE
charge_per_unit := 6.60;
END IF;
RETURN QUERY SELECT name_var, units_consumed * charge_per_unit;
END;
$$ LANGUAGE plpgsql;

10. Write a PL/PGSQL Procedure to print the grade of the Student. Obtain student marks as
input from the main program.
Check for the following condition:
if the input mark ranges 91-100 then Grade A,
81-90 then Grade B,
 71-80 then Grade C,
60-70 then Grade D and <60 Grade E.

CREATE OR REPLACE PROCEDURE calculate_student_grade_36(marks INT)

AS $$
DECLARE
grade_char CHAR;
BEGIN
IF marks >= 91 THEN
grade_char := 'A';
ELSIF marks >= 81 THEN
grade_char := 'B';
ELSIF marks >= 71 THEN
grade_char := 'C';
ELSIF marks >= 61 THEN
grade_char := 'D';
ELSE
grade_char := 'E';
END IF;
RAISE NOTICE 'Grade: %', grade_char;
END;
$$ LANGUAGE plpgsql;

11. Write a PL/PGSQL program to display the salary of an employee when an empId is given.

CREATE TABLE employees11_36(empId INT PRIMARY KEY,name

VARCHAR(100),salary NUMERIC);

INSERT INTO employees11_36(empId, name, salary) VALUES (101, 'John Doe',

50000),(102, 'Alice Smith', 60000),(103, 'Bob Johnson', 55000);
 CREATE OR REPLACE FUNCTION display_employee_salary_36(emp_id INT)
RETURNS NUMERIC
AS $$
DECLARE
emp_salary NUMERIC;
BEGIN
SELECT salary INTO emp_salary FROM employees11_36 WHERE empId = emp_id;
IF FOUND THEN
RETURN emp_salary;
ELSE
RETURN NULL;
END IF;
END;
$$ LANGUAGE plpgsql;

SELECT display_employee_salary_36(101);

12. Write a PL/PGSQL function to count the number of employees in a particular

department.

CREATE TABLE employees12_36(emp_id SERIAL PRIMARY KEY,name1

VARCHAR(100),department_id INT);
INSERT INTO employees12_36(name1, department_id) VALUES ('John Doe', 1),('Alice
Smith', 1),('Bob Johnson', 2),('Emily Williams', 1),('Michael Brown', 2);

CREATE OR REPLACE FUNCTION

count_employees_in_department_36(p_department_id INT)
RETURNS INT AS $$
DECLARE
 employee_count INT;
BEGIN
SELECT COUNT(*) INTO employee_count FROM employees12_36 WHERE
department_id = p_department_id;

RETURN employee_count;
END;
$$ LANGUAGE plpgsql;

SELECT count_employees_in_department_36(1);
Result:
Thus the database has been queried using the stored procedures and functions.
EX.NO:9

DATE: VIEWS

Aim:

To create a database and apply views using DDL and DML commands.

StudentDetails:
SID NAME ADDRESS CONTACTNO
1 Harsh Kolkata 9876543891
2 Ashish Durgapur 7890654321
3 Pratik Delhi 9977665541
4 Dhanraj Bihar 9087654321
5 Ram Rajasthan 9876543210

StudentMarks

ID Name Marks Age

1 Harsh 90 19

2 Suresh 50 20

3 Pratik 80 19

4 Dhanraj 95 21

5 Ram 85 18
SQL – Views:
Creating View from a single table:

1. Create a View named DetailsView from the table StudentDetails with the details of
students whose ID is less than 4.

Create view detailsview_36 as select * from studentdetails_36 where sid<4;

 2. create a view named StudentNames from the table StudentDetails by arranging the
names in descending order.Return names.

Create view StudentNames_36 as select name from studentdetails_36 order by name

desc;

Creating View from multiple tables:

1. create a View named MarksView from two tables StudentDetails and StudentMarks.
Return ID,Name ,Address, ContactNo,Marks.

Create view marksview_36 as select d.sid, d.name, d.address,d.contactno, m.marks from

studentdetails_36 d join studentmarks_36 m on d.sid =m.id;
 2. create a View named GoodPerfomerView from two tables StudentDetails and
StudentMarks and return the student name, ID, Marks of students who scored more than
80 and order the result by ID in ascending order .

create view good_performer_view_36 as select d.name, d.sid, m.marks from

studentdetails_36 d join studentmarks_36 m on d.sid = m.id where marks<80 order by m.id
asc;

Inserting a row in a view:

1. Insert 5 new records in to the table using the views DetailsView.

Insert into detailsview_36 values (6,'Sachin','Hyderabad','9874973210'),

(7,'Virat','Banglore','9876544356'),(8,'Rahul','Delhi','9876543212'),(9,'Rohit','Mumbai','987654
3213'),(10,'Dhoni','Ranchi','9876543214');

Deleting a row from a View:

1. Delete the row with ID 2 from the view DetailsView.

delete from detailsview_36 where sid=2;

Updating the rows of views:

 1. Update the contactNo of student with ID 3 using DetailsView.

update detailsview_36 set contactno = 1254746758 where sid=3;

Dropping the views:

1. Drop the DetailsView and StudentNames.

Drop view detailsview_36;

Drop view studentnames_36;

Creating a View with check options:

1. Create a View SampleView from StudentDetails Table in which student name should not
be null
 Create view sampleviews_36 as select * from studentdetails_36 where name is not null;

Inserting data into view with check options:

2. Insert 5 records into the SampleView.

Insert into sampleviews_36 values (11,'john','new york','6793575468'),(12,null,'los

angeles','0987654321'),(13,'charles','london','9876543210'),(14,'bob','paris','8765432109'),(15,
'eve','berlin','7654321098');

Result:

Thus, the database has been created using views and DDL, DML commands has been
applied successfully.
EX.NO:10
QUERYING/MANAGING THE DATABASE USING SQL
DATE: PROGRAMMING - TRIGGERS

AIM:
To query or manage the database using SQL Triggers.
SQL – TRIGGERS:
1. Create the following tables with given attributes having appropriate data type and specify
the necessary primary and foreign key constraints:
Customer (Custid, Custname, Addr, phno,panno)
Create table customer_36 (custid int primary key, custname varchar(30), addr
varchar(100), phno bigint, panno bigint);

Loan (Loanid, Amount, Interest, Custid)

create table loan_36(loanid int primary key,amount decimal(15,2),interest
decimal(5,2),custid int, foreign key (custid) references customer_36(custid));

Account (Acctno, Accbal, Custid)

create table account_\36(acctno int primary key,accbal decimal(15,2),custid int, foreign
key (custid) references customer_36(custid));

Create a trigger that checks for the minimum balance in the account.
 CREATE OR REPLACE FUNCTION check_min_balance_36() RETURNS
TRIGGER
AS $$
BEGIN
IF NEW.Accbal < 100 THEN -- Assuming the minimum balance is 100
RAISE EXCEPTION 'Minimum balance constraint violated';
END IF;
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER check_min_balance_trigger_36
BEFORE INSERT OR UPDATE ON Account_36
FOR EACH ROW
EXECUTE FUNCTION check_min_balance_36();

2. Create the student database with the following tables and do the following:
Create database student10a_36;

assessment(reg_no,name, mark1, mark2, mark3, total)

create table assessment_36(reg_no int primary key, name varchar(50), mark1 int, mark2 int,
mark3 int, total int);
 dept_details (dept_no, dept_name, location)
Create table dept_details_36(dept_no int primary key,dept_name varchar(50),location
varchar(50));

Write a PL/SQL Trigger to verify the data before insertion on the assessment table.

CREATE OR REPLACE FUNCTION verify_assessment_data_36() RETURNS TRIGGER

AS $$
DECLARE
total_marks INT;
BEGIN
total_marks := NEW.mark1 + NEW.mark2 + NEW.mark3;
IF total_marks < 0 OR total_marks > 300 THEN
RAISE EXCEPTION 'Total marks should be between 0 and 300.';
END IF;
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER assessment_verify_trigger_36
BEFORE INSERT ON assessment_36
FOR EACH ROW
EXECUTE FUNCTION verify_assessment_data_36();

3. Consider the following relations for an order processing application:

 CUSTOMER (CID, NAME)
PRODUCT (PCODE, PNAME, UNIT_PRICE)
CUST_ORDER (OCODE, ODATE, CID)
ORDER_PRODUCT (OCODE, PCODE, QTY)
Develop a Trigger to ensure the product is Pen, Eraser, or Pencil during insertion.
CREATE OR REPLACE FUNCTION check_valid_product_36() RETURNS TRIGGER
AS $$
DECLARE
product_name VARCHAR(50);
BEGIN
SELECT PNAME INTO product_name FROM PRODUCT WHERE PCODE =
NEW.PCODE;
IF UPPER(product_name) NOT IN ('PEN', 'ERASER', 'PENCIL') THEN
RAISE EXCEPTION 'Only Pen, Eraser, or Pencil products are allowed.';
END IF;
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER check_valid_product_trigger_36
BEFORE INSERT ON ORDER_PRODUCT_36
FOR EACH ROW
EXECUTE FUNCTION check_valid_product_36();

4. Create a trigger to maintain an employee audit whenever an employee’s first name changes.
CREATE OR REPLACE FUNCTION employee_first_name_audit_36() RETURNS
TRIGGER
 AS $$
BEGIN
IF OLD.first_name IS DISTINCT FROM NEW.first_name THEN
INSERT INTO employee_audit(audit_id, employee_id, old_first_name, new_first_name,
change_date)
VALUES (DEFAULT, OLD.employee_id, OLD.first_name, NEW.first_name,
CURRENT_TIMESTAMP);
END IF;
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER employee_first_name_audit_trigger
BEFORE UPDATE OF empname ON employee_36
FOR EACH ROW
EXECUTE FUNCTION employee_first_name_audit_36();

5. Given Student_Report table, in which student marks assessment is recorded. In such

schema, create a trigger so that the total and average of specified marks is automatically
inserted whenever a record is insert.
Student_Report (reg_no,name, mark1, mark2, mark3, total,avg)
CREATE OR REPLACE FUNCTION calculate_marks_36()
RETURNS TRIGGER AS $$
BEGIN
NEW.total := NEW.mark1 + NEW.mark2 + NEW.mark3;
NEW.avg := (NEW.mark1 + NEW.mark2 + NEW.mark3) / 3.0;
RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER calculate_marks_trigger_36

BEFORE INSERT ON Student_Report_36
FOR EACH ROW
 EXECUTE FUNCTION calculate_marks_36();

6. Write a database trigger before delete for each row on table course, delete corresponding
course students from Student table
1.Create a table ‘Student’ with the following details (table level constraints)

Rollno number(5) Primary key

Name varchar2(10)
Coursecode number(2) Foreign key (Reference ‘Course’ table)
Mark1 number(3) Check whether values range from 0 to 100
Mark2 number(3) Check whether values range from 0 to 100

create table student_36(rollno int primary key, name varchar(20), coursecode int,
mark1 int check (mark1 between 0 and 100),mark2 int check(mark2 between 0 and 100), constraint
fk_course foreign key(coursecode) references course_36(ccode));

2. Create a table ‘Course’ with the following details (table level constraints)
Ccode number(2) Primary key
Course varchar2(10)

create table course_36(ccode int primary key, course varchar(20));

CREATE OR REPLACE FUNCTION delete_course_students_36()

RETURNS TRIGGER
AS $$
BEGIN
DELETE FROM Student WHERE Coursecode = OLD.Ccode;
RETURN OLD;
 END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER delete_course_students_trigger_36
BEFORE DELETE ON Course_36
FOR EACH ROW
EXECUTE FUNCTION delete_course_students_36();

7. Create the following tables with given attributes having appropriate data type and specify
the necessary primary and foreign key constraints:
Customer (Custid, Custname, phno,pan,DOB)
create table customer5_36(custid int primary key, custname varchar(30), phno bigint, pan bigint,
dob date);

HomeLoan (HLoanid, Amount, Custid)

create table homeloan_36(Hloanid int primary key, amount decimal(15,2),custid int,foreign
key (custid) references customer5_36(custid));

VehicleLoan (VLoanid, Amount, Custid)

create table vehicleloan_36(Vloanid int primary key, amount decimal(15,2),custid
int,foreign key (custid) references customer5_36(custid));
Write a trigger which displays the Homeloan details whenever the values are inserted in the
respective table.
CREATE OR REPLACE FUNCTION display_homeloan_details_36() RETURNS
TRIGGER AS $$
BEGIN
RAISE NOTICE 'HomeLoan details:';
RAISE NOTICE 'HomeLoan ID: %', NEW.HLoanid;
RAISE NOTICE 'Amount: %', NEW.Amount;
RAISE NOTICE 'Customer ID: %', NEW.Custid;
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER display_homeloan_details_trigger_36
AFTER INSERT ON HomeLoan_36
FOR EACH ROW
EXECUTE FUNCTION display_homeloan_details_36()

8. Consider the following relational schema for a banking database application:

CUSTOMER (CID, CNAME)
BRANCH (BCODE, BNAME)
ACCOUNT (ANO, ATYPE, BALANCE, CID, BCODE)
 TRANSACTION (TID, ANO, TTYPE, TDATE, TAMOUNT)
Develop a database trigger that will update the value of BALANCE in ACCOUNT table
when a record is inserted in the transaction table.
CREATE OR REPLACE FUNCTION update_balance_36() RETURNS TRIGGER AS
$$
DECLARE
new_balance NUMERIC;
BEGIN
IF NEW.TTYPE = 'DEPOSIT' THEN
SELECT BALANCE + NEW.TAMOUNT INTO new_balance
FROM ACCOUNT10_36
WHERE ANO = NEW.ANO;
ELSIF NEW.TTYPE = 'WITHDRAWAL' THEN
SELECT BALANCE - NEW.TAMOUNT INTO new_balance
FROM ACCOUNT10_36
WHERE ANO = NEW.ANO;
END IF;
UPDATE ACCOUNT10_36
SET BALANCE = new_balance
WHERE ANO = NEW.ANO;
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER update_balance_36
AFTER INSERT ON TRANSACTION_36
FOR EACH ROW
EXECUTE FUNCTION update_balance_36();
9. Create the employee database with the following tables and do the following:

Create a table ‘Employee’ with the following details(Column level constraints)

Empno Number(5) Primary key
Empname Varchar2(20) Designation Varchar2(10)
Date_of_join Date
Salary Number(9,2) NOT NULL
Depno Number(2) Foreign key(Reference ‘Department’ table)

create table employee_36(empno int primary key,empname varchar(20),designation

varchar(30),date_of_join date,salary bigint not null check(salary<=25000),depno int ,
constraint fk_department foreign key (depno) references department_36(depno));

Create another table called ‘Department’ with the following structure(Column level
constraints)
Depno Number(2) Primary key
Depname Varchar2(15)
Deplocation Varchar2(10)

create table department_36(depno int primary key,depname varchar(20),deplocation

varchar(20));
 Write a database trigger before insert or update for each row on the table employee not
allowing to enter salary > 25,000.
CREATE OR REPLACE FUNCTION check_salary_limit_36() RETURNS TRIGGER
AS $$
BEGIN
IF NEW.Salary > 25000 THEN
RAISE EXCEPTION 'Salary cannot exceed 25,000.';
END IF;
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER check_salary_limit_trigger_36
BEFORE INSERT OR UPDATE OF Salary ON Employee_36
FOR EACH ROW
EXECUTE FUNCTION check_salary_limit_36();

10. Create a table itemfile with the following fields

Itemcode varchar2(5) Itemdesc varchar2(30)
P_category varchar2(30) (Spares, Accessories)
Qty_hand number(5) Re_level number(5)
Maxlevel number(5) Itemrate number(9,2)

create table itemfile_36(itemcode varchar(10),itemdesc varchar(20),p_category

varchar(30),qty_hand int,re_level int,maxlevel int,itemrate int);
 Write a database trigger to display the total number of records after inserting a record into
the table
CREATE OR REPLACE FUNCTION display_total_records_36() RETURNS TRIGGER
AS $$
DECLARE
total_records INTEGER;
BEGIN
SELECT COUNT(*) INTO total_records FROM itemfile;
RAISE NOTICE 'Total number of records in itemfile: %', total_records;
RETURN NULL;
END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER display_total_records_trigger_36
AFTER INSERT ON itemfile_36
FOR EACH ROW
EXECUTE FUNCTION display_total_records_36();

RESULT:

Thus the triggers has been applied on the database successfully.