## Program 1 : Customer Table

### QUERIES

create table customer(custom varchar(5), customer varchar(15), age numeric, phone varchar(10));

insert into customer values(1,'Raju',23,9495823456);

insert into customer values(2,'Zara',23,9447132324);

insert into customer values(3,'Adam',34,8089123456);

insert into customer values(4,'Sheena',23,7259123456);

insert into customer values(5,'Lisa',23,8891223344);

select *from customer;

alter table customer add d_birth date;

create table cust_phone as select cust_name,phone from customer;

alter table customer drop age;

ALTER TABLE customer ALTER COLUMN cust_name TYPE varchar(25);

TRUNCATE customer;

ALTER TABLE customer RENAME TO cust;

drop table cust;

## Program 2 : Constraints

### QUERIES
create table sales_man(salesman_no int primary key,s_name varchar(10) not null,place
varchar(10),phone numeric(10) unique);

create table sales_order(order_no int primary key,order_date date NOT NULL,order_status char(10)
NOT NULL check(order_status IN('Improcess', 'Fulfilled', 'Cancelled', 'Backorder')), salesman_no int
references sales_man (salesman_no), del_type char(1) check (del_type='F' or del_type='P'));

insert into sales_man values (101,'ananthu','feroke',8137036211);

insert into sales_man values (102,'fariz','chungan',8137036231);

insert into sales_man values (103,'sheena','chelari',9997036231);

insert into sales_man values (104,'asla','chelari',9687036231);

insert into sales_man values (105,'jithin','tanur',9687035671);

insert into sales_order values(1,'01-01-17','Improcess',101,'F');

insert into sales_order values(2,'03-02-17','Fullfilled',102,'F');

insert into sales_order values(3,'03-03-17','Fullfilled',103,'P');

insert into sales_order values(4,'03-03-17','Cancelled',104,'F');

insert into sales_order values(5,'05-03-17','Backorder',105,'P');

ALTER TABLE sales_man DROP constraint sales_man_pkey cascade;

ALTER TABLE sales_order DROP constraint sales_order_del_type_check;

ALTER TABLE sales_order DROP constraint sales_order_order_status_check;

ALTER TABLE sales_man ADD primary key(salesman_no);

ALTER TABLE sales_order ADD FOREIGN KEY (salesman_no) REFERENCES sales_man(salesman_no);

ALTER TABLE sales_order ADD CHECK (del_type = 'F' OR del_type = 'P');

ALTER TABLE sales_order ADD check(order_status IN('Improcess', 'Fullfilled', 'Cancelled',

'Backorder'));

## Program 3 : Hospital Table

### QUERIES

create table hospital(doctorid char(4),doctorname varchar(10),department varchar(25),qualification

varchar(25),experience int);

insert into hospital values('d001','miya','cardiologist','mbbs',5);

insert into hospital values('d002','john','orthologist','md',4);

insert into hospital values('d003','ramesh','skin','mbbs',3);

insert into hospital values('d004','madona','dentist','bds',6);

insert into hospital values('d005','manoj','optometry','md',1);

select * from hospital;

select doctorname from hospital where qualification='md';

select doctorname from hospital where experience>5 and qualification!='md';

select doctorname from hospital where department='skin';

update hospital set experience=5 where doctorid='d003';

delete from hospital where doctorid='d005';

## Program 4 : implementing sql join and set operations

### QUERIES

create table Bank_customer(accno int primary key,cust_name varchar(25),place varchar(25));

create table Deposit(accno int references Bank_customer(accno),deposit_no int, damount numeric);

create table loan(accno int references bank_customer(accno), loan_no int,lamount numeric);

insert into bank_customer values(101,'Ravi','elt');

insert into bank_customer values(102,'Adam','tvm');

insert into bank_customer values(103,'Aysha','mlprm');

insert into bank_customer values(104,'Lisa','kmr');

insert into bank_customer values(105,'Shaju','klm');

insert into bank_customer values(106,'Razeen','kch');

insert into bank_customer values(107,'Radha','tvm');

insert into bank_customer values(108,'Jose','kmr');

insert into deposit values(101,15,400000);

insert into deposit values(102,13,75000);

insert into deposit values(105,12,55000);

insert into deposit values(108,16,750000);

insert into loan values(103,4,500000);

insert into loan values(104,2,200000);

insert into loan values(106,6,300000);

insert into loan values(108,8,600000);

select * from bank_customer;

select b.accno,cust_name,damount from bank_customer b join deposit d on b.accno=d.accno where

b.accno not in(select accno from loan);

select b.accno,cust_name,lamount from bank_customer b join loan l on b.accno=l.accno where

b.accno not in (select accno from deposit);

select cust_name from bank_customer where accno in ((select accno from loan)intersect(select
accno from deposit));

select cust_name from bank_customer where accno not in ((select accno from loan)union(select
accno from deposit));
## Program 5 : Aggregate Functions

### QUERIES

create table employee(empid int PRIMARY KEY,ename varchar(10),salary numeric, department

varchar(20) , age int);

Insert into employee values(101,'Adam', 20000,'Purchase', 25);

Insert into employee values(102,'Lisa', 15000,'Sales', 45);

Insert into employee values(103,'Arun', 18000,'Sales', 34);

Insert into employee values(104,'Aysha', 25000,'Purchase', 25);

Insert into employee values(105,'Sheeja', 30000,'Finance', 36);

Insert into employee values(106,'Sagar', 28000,'Finance', 42);

select count(empid)from employee;

select ename,department from employee a where age in(select max(age) from employee b group by
department having a.department=b.department);

select department,avg(age)from employee group by department;

select department,avg (salary)from employee group by department;

select min(salary) as min_salary from employee;

select count(ename) from employee where department='Purchase';

select max(salary)from employee where department='Sales';

select max(salary) - min(salary) as sal_difference from employee;

## Program 6 : Logical Operators

### QUERIES

create table product(product_code int primary key, product_name varchar(20),category

varchar(20),quantity int,price numeric(10,2));

insert into product values(1,'colgate','paste',10,100);

insert into product values(2,'close up','paste',9,90);

insert into product values(3,'nirma','bath soap',10,600);

insert into product values(4,'sunlight','washing powder',10,700);

insert into product values(5,'toy','car',1,200);

insert into product values(6,'toy','bike',3,300);

insert into product values(7,'lux','bath soap',1,20);

insert into product values(8,'lux','bath liquid',600,2000);

insert into product values(9,'nirma','bath liquid',300,1000);

select * from product order by product_name desc;

select product_code,product_name from product where price between 20 and 50;

select product_name,price from product where category in ('bath soap','paste','washing powder');

select * from product where quantity<100 or quantity>500;

select product_name from product where product_name like 's%';

select product_name from product where category != 'paste';

select product_name from product where product_name like '_u%' and category='washing powder';

## Program 7 : Employee Table

### QUERIES
create table employee(empname varchar(10) primary key,city varchar(10));

create table company(company_name varchar(10) primary key ,city varchar(10));

create table works(empname varchar(10) primary key references employee(empname),cname

varchar(10) references company(company_name),salary int);

create table manages(empname varchar(10) references employee(empname),manager_name

varchar(10) references employee(empname),primary key(empname,manager_name));

insert into employee values('swathi','kzkd');

insert into employee values('vishnu','tvm');

insert into employee values('shreya','usa');

insert into employee values('adam','dubai');

insert into employee values('sajid','malappuram');

insert into company values('infosys','tvm');

insert into company values('chandrika','trissur');

insert into company values('wipro','kochi');

insert into company values('tata','munbai');

insert into company values('bajaj','delhi');

insert into works values('swathi','infosys',10000);

insert into works values('vishnu','infosys',15000);

insert into works values('shreya','wipro',71502);

insert into works values('adam','infosys',8000);

insert into works values('sajid','wipro',18000);

insert into manages values('swathi','adam');

insert into manages values('vishnu','adam');

insert into manages values('sajid','shreya');

select empname from works where cname='infosys';

select employee.empname,employee.city from employee,works where employee.empname =

works.empname and works.cname = 'wipro';

select employee.empname,city from employee,works where employee.empname= works.empname

and cname='infosys' and salary>10000;

select employee.empname from employee,works,company where employee.empname =

works.empname and employee.city = company.city and works.cname = company.company_name;

select empname from works where cname!='wipro';

select cname from works group by cname order by count(*) desc limit 1;

## Program 8 :

### QUERIES

create table supplier(supcode char(3) primary key,sname varchar(10), city varchar(10));

create table product(pcode char(3) primary key, pname varchar(10));

Create table supl_product(supcode char(3) references supplier(supcode),pcode char(3) references

product(pcode),qty int);

insert into supplier values ('s1','raju','calicut');

insert into supplier values ('s2','sheela','thrissur');

insert into supplier values ('s3','aysha','kochi');

insert into supplier values ('s4','anees','tirur');

insert into supplier values ('s5','lisa','calicut');

insert into supplier values ('s6','zara','tirur');

insert into product values('p1','soap');

insert into product values('p2','rice');

insert into product values('p3','salt');

insert into product values('p4','sugar');

insert into product values('p5','wheat');

insert into product values('p6','colgate');

insert into product values('p7','chilly');

insert into supl_product values('s1','p1',34);

insert into supl_product values('s1','p2',20);

insert into supl_product values('s2','p2',20);

insert into supl_product values('s6','p4',5);

insert into supl_product values('s3','p3',10);

insert into supl_product values('s2','p7',10);

insert into supl_product values('s3','p1',12);

select a.supcode,b.supcode,b.city from supplier a,supplier b where a.city=b.city and

a.supcode<b.supcode;

select sname from supplier where supcode in(select supcode from supl_product where pcode='p2');

select pcode from supl_product group by pcode having count(pcode)>1;

select supcode from supplier where city=(select city from supplier where supcode='s1');

select sname from supplier where supcode in(select supcode from supl_product where pcode='p1');

select count(distinct supcode) from supl_product;

select pcode,sum(qty) from supl_product group by pcode;

## Part 2 PostgreSql

## Program 9 : Salary Report of Employees

### QUERIES

create table salaries(EmpNo char(3), Name char(10), Basic numeric(6));

Insert into salaries values('101', 'Adam', 20000);

Insert into salaries values('102', 'Lisa', 15000);

Insert into salaries values('103', 'Arun', 18000);

Insert into salaries values('104', 'Aysha', 25000);

Insert into salaries values('105', 'Sheeja', 30000);

Insert into salaries values('106', 'Sagar', 28000);

Insert into salaries values('111','Muthu', 10000);

Insert into salaries values('109','Hari', 2500);

Insert into salaries values('108','Raju', 5000);

Insert into salaries values('107','Sabi', 8000);

do $$

declare

dav numeric(10,2);

pfv numeric(10,2);

grossv numeric(10,2);

anv numeric(10,2);

netv numeric(10,2);

taxv numeric(10,2);

rec record;

begin

alter table salaries add column da numeric(10,2),add column pf numeric(10,2),add column gross
numeric(10,2),add column net numeric(10,2),add column annual numeric(10,2),add column tax
numeric(10,2);

for rec in select * from salaries

loop

dav:=rec.basic*0.4;

pfv:=rec.basic*0.1;

grossv:=rec.basic+dav;

netv:=grossv-pfv;

anv:=12*netv;

if anv>250000 then

taxv:=(anv-250000)*0.3+(250000-150000)*0.2+(150000-100000)*0.1;

elsif anv>150000 then

taxv:=(anv-150000)*0.2+(150000-100000)*0.1;

elsif anv>100000 then

taxv:=(anv-100000)*0.1;

else

taxv:=0;

end if;

update salaries set da=day, pf=pfy, gross=grossv, net=netv, annual=any, tax=taxv where
empno=rec.empno;

end loop;

end $$;

## Program 10 : Calculating Grade From Average Score

### QUERIES

Create table exam_result(rollno integer, avg_score numeric(5,2), grade char(1));

insert into exam_result values (4, 67), (3, 35), (2, 91), (6, 45), (11, 86), (7, 95), (8, 75), (9, 68), (5, 55),
(12, 97), (13, 30);

do $$

declare

rec record;

grd char[1];
begin

for rec in select * from exam_result

loop

if rec.avg_score between 90 and 100 then

grd:= 'A';

elsif rec.avg_score between 75 and 89 then

grd= 'B';

elsif rec.avg_score between 60 and 74 then

grd:= 'C';

elsif rec.avg_score between 50 and 59 then

grd:= 'D';

else

grd:= 'E';

end if;

update exam_result set grade=grd where rollno=rec.rollno;

end loop;

end;

$$ language plpgsql;

## Program 11 : Area of a Circle

### QUERIES

do $$

declare

r integer;

ar numeric(10,2);

begin

create table areas(radius integer,area numeric(5,2));

r:=3;

for r in 3..7 loop

ar:=3.14*r*r;

insert into areas values(r,ar);

end loop;

end $$;

## Program 12 : Electricity Bill Calculation

### PROGRAM CODE

create or replace function electricity_bill(c int, u int) returns text as $$

declare

rate int;

amt int;

begin

if u<=100 then

rate:=3;

elsif u<=250 then

rate:=4;

elsif u<=500 then

rate:=5;

else

rate:=6;

end if;

amt:=rate*u;

return 'Customer No :' || c || E'\nUnits Consumed :' || u || E'\nBill Amount :' || amt;

end;

$$ language plpgsql;

## Program 13 : Fibonacci Numbers upto a Limit

### PROGRAM CODE

create or replace function fibonacci(n int) returns setof int as $$

declare

a int:=1;

b int:=0;

c int:=0;

begin

loop

exit when n < c;

return next c;

c:=a+b;

a:=b;

b:=c;

end loop;

end;

$$ language plpgsql;

## Program 14 : Check Prime or Not

### PROGRAM CODE

CREATE FUNCTION check_prime(n int) returns varchar(25) AS $$

DECLARE

i int;

BEGIN

if n<2 then

return n || ' is not a prime number';

end if;

for i in 2..n/2

loop

if mod(n,i)=0 then

return n || ' is not a prime number';

end if;

end loop;
return n || ' is a prime number';

end;

$$ language plpgsql;

## Program 15 : Student Mark List

### QUERIES

Create table stud_mark(regno char(5), sname varchar(20), avg_mark numeric(5,2));

insert into stud_mark values ('S01', 'Viji', 35);

insert into stud_mark values ('S02', 'Adam', 91);

insert into stud_mark values ('S03', 'Zara', 45);

insert into stud_mark values ('S04', 'Lisa', 86);

insert into stud_mark values ('S05', 'Ishan', 95);

insert into stud_mark values ('S06', 'Hari', 75);

insert into stud_mark values ('S07', 'Haya', 68);

insert into stud_mark values ('S08', 'Jisha', 55);

insert into stud_mark values ('S09', 'Jasi', 97);

insert into stud_mark values ('S10', 'Pranav', 30);

insert into stud_mark values ('S11', 'Neeraj', 67);

do $$

declare

dist int;

first int;

second int;

third int;

fail int;

begin

select count(*) into dist from stud_mark where avg_mark between 90 and 100;

select count(*) into first from stud_mark where avg_mark between 75 and 89;
select count(*) into second from stud_mark where avg_mark between 60 and 74;

select count(*) into third from stud_mark where avg_mark between 50 and 59;

select count(*) into fail from stud_mark where avg_mark < 50;

raise notice '

No of Distintions : %

No of First Classes : %

No of Second Classes : %

No of Third Classes : %

No of Failures : %',dist,first,second,third,fail;

end $$;

## Program 16 Display Average Salary of a Department

### QUERIES

Create table emp_salary(empno int, ename varchar(15), dept varchar(15), salary int);

Insert into emp_salary values(101, 'Adam', 'Production', 20000);

Insert into emp_salary values(102, 'Lisa', 'Marketing', 15000);

Insert into emp_salary values(103, 'Arun', 'Marketing', 18000);

Insert into emp_salary values(104, 'Aysha', 'Production', 25000);

Insert into emp_salary values(105, 'Sheeja', 'Finance', 30000);

Insert into emp_salary values(106, 'Sagar', 'Finance', 28000);

create function avg_salary(dept_name varchar(10)) returns numeric(10,2) as $$

declare

avg_sal numeric(10,2);

begin

select avg(salary) into avg_sal from emp_salary group by dept having dept=dept_name;

return avg_sal;

end;

$$ language plpgsql;
## Program 17. Implementation of Trigger Before Insert

### QUERIES

Create table student(regno char(5), sname varchar(15), sub1 numeric(3), sub2 numeric(3), sub3
numeric(3), sub4 numeric(3), sub5 numeric(3), mark_total numeric(3), avg_mark numeric(5,2));

create or replace function fun() returns trigger as $$

declare

begin

new.mark_total=new.sub1+new.sub2+new.sub3+new.sub4+new.sub5;

new.avg_mark=new.mark_total/5.0;

return new;

end;

$$ language plpgsql;

create trigger trig before insert on student for each row execute procedure fun();

insert into student values ('s101','adam',23,45,67,23,45);

insert into student values ('s102','sheena',96,97,89,95,67);

insert into student values ('s103','bobby',67,52,83,91,34);

insert into student values ('s104','radha',34,54,23,12,25);

insert into student values ('s105','zara',86,76,82,85,34);

## Program 18. Implementation of Trigger After Delete or Update

### QUERIES

create table phonebook(pname varchar(20), mobno char(10));

create table del_phonebook(pname varchar(20), mobno char(10), modify_date timestamp);

insert into phonebook values('Raju','9895324212');

insert into phonebook values('Aravind', '9435111222');

insert into phonebook values('Sheeja','9277123435');

insert into phonebook values('Arunthadi', '9867111333');

insert into phonebook values('Fida', '9234555777');

create or replace function fun() returns trigger as $$

begin

insert into del_phonebook values (old.pname, old.mobno, now());

return new;

end;

$$ language plpgsql;

create trigger trig after delete or update on phonebook for each row execute procedure fun();

delete from phonebook where pname='Sheeja';

update phonebook set mobno='9292777888' where pname='Raju';

```