$ sudo -i -u postgres $ psql postgres=# \c dvdrsh You are now connected to database "dvdrsh"

-- 1. Create Table CUSTOMER dvdrsh=# CREATE TABLE customer( cus_no varchar(5), cus_name varchar(15), age int, phone varchar(10) ); CREATE TABLE -- a) Insert 5 Records dvdrsh=# INSERT INTO customer VALUES('101','nila',23,'98023452'); INSERT 0 1 dvdrsh=# INSERT INTO customer VALUES('102','arun',21,'98452211'); INSERT 0 1 dvdrsh=# INSERT INTO customer VALUES('103','maya',24,'97452211'); INSERT 0 1 dvdrsh=# INSERT INTO customer VALUES('104','ravi',22,'96452211'); INSERT 0 1 dvdrsh=# INSERT INTO customer VALUES('105','asha',23,'95452211'); INSERT 0 1 -- Display Records dvdrsh=# SELECT * FROM customer; cus_no | cus_name | age | phone --------+----------+-----+----------- 101 | nila | 23 | 98023452 102 | arun | 21 | 98452211 103 | maya | 24 | 97452211 104 | ravi | 22 | 96452211 105 | asha | 23 | 95452211 (5 rows) -- b) Add new field d_birth dvdrsh=# ALTER TABLE customer ADD COLUMN d_birth DATE; ALTER TABLE -- c) Create Table cust_phone from customer dvdrsh=# CREATE TABLE cust_phone AS SELECT cus_name,phone FROM customer; SELECT 5 dvdrsh=# SELECT * FROM cust_phone; cus_name | phone ----------+----------- nila | 98023452 arun | 98452211 maya | 97452211 ravi | 96452211 asha | 95452211 (5 rows) -- d) Remove column age dvdrsh=# ALTER TABLE customer DROP COLUMN age; ALTER TABLE -- e) Change size of cus_name dvdrsh=# ALTER TABLE customer ALTER COLUMN cus_name TYPE varchar(25); ALTER TABLE -- f) Delete all records dvdrsh=# DELETE FROM customer; DELETE 5 -- g) Rename table customer to cust dvdrsh=# ALTER TABLE customer RENAME TO cust; ALTER TABLE -- h) Drop table dvdrsh=# DROP TABLE cust; DROP TABLE

dvdrsh=# CREATE TABLE hospital( doctor_id varchar(15), doctor_name varchar(20), department varchar(15), qualification varchar(10), experience int ); CREATE TABLE -- a) Insert 5 Records dvdrsh=# INSERT INTO hospital VALUES('D001','rakesh','Skin','MD',6); INSERT 0 1 dvdrsh=# INSERT INTO hospital VALUES('D002','shiya','Cardiology','MBBS',4); INSERT 0 1 dvdrsh=# INSERT INTO hospital VALUES('D003','rahul','Emergency','MBBS',3); INSERT 0 1 dvdrsh=# INSERT INTO hospital VALUES('D004','meera','Skin','MD',8); INSERT 0 1 dvdrsh=# INSERT INTO hospital VALUES('D005','akhil','Ortho','MBBS',7); INSERT 0 1 -- b) Display all Doctors dvdrsh=# SELECT * FROM hospital; doctor_id | doctor_name | department | qualification | experience -----------+-------------+------------+---------------+----------- D001 | rakesh | Skin | MD | 6 D002 | shiya | Cardiology | MBBS | 4 D003 | rahul | Emergency | MBBS | 3 D004 | meera | Skin | MD | 8 D005 | akhil | Ortho | MBBS | 7 (5 rows) -- c) Doctors with qualification MD dvdrsh=# SELECT * FROM hospital WHERE qualification='MD'; doctor_id | doctor_name | department | qualification | experience -----------+-------------+------------+---------------+----------- D001 | rakesh | Skin | MD | 6 D004 | meera | Skin | MD | 8 (2 rows) -- d) Doctors with experience >5 and not MD dvdrsh=# SELECT * FROM hospital WHERE experience>5 AND qualification!='MD'; doctor_id | doctor_name | department | qualification | experience -----------+-------------+------------+---------------+----------- D005 | akhil | Ortho | MBBS | 7 (1 row) -- e) Doctors in Skin department dvdrsh=# SELECT * FROM hospital WHERE department='Skin'; doctor_id | doctor_name | department | qualification | experience -----------+-------------+------------+---------------+----------- D001 | rakesh | Skin | MD | 6 D004 | meera | Skin | MD | 8 (2 rows) -- f) Update experience of doctor D003 to 5 dvdrsh=# UPDATE hospital SET experience=5 WHERE doctor_id='D003'; UPDATE 1 dvdrsh=# SELECT * FROM hospital WHERE doctor_id='D003'; doctor_id | doctor_name | department | qualification | experience -----------+-------------+------------+---------------+----------- D003 | rahul | Emergency | MBBS | 5 (1 row) -- g) Delete doctor with ID D005 dvdrsh=# DELETE FROM hospital WHERE doctor_id='D005'; DELETE 1 dvdrsh=# SELECT * FROM hospital; doctor_id | doctor_name | department | qualification | experience -----------+-------------+------------+---------------+----------- D001 | rakesh | Skin | MD | 6 D002 | shiya | Cardiology | MBBS | 4 D003 | rahul | Emergency | MBBS | 5 D004 | meera | Skin | MD | 8 (4 rows)

dvdrsh=# CREATE TABLE bank_customer( accno int PRIMARY KEY, cust_name varchar(20), place varchar(20) ); CREATE TABLE dvdrsh=# CREATE TABLE deposit( accno int REFERENCES bank_customer(accno), deposit_no int, damount int ); CREATE TABLE dvdrsh=# CREATE TABLE loan( accno int REFERENCES bank_customer(accno), loan_no int, lamount int ); CREATE TABLE -- Insert Records dvdrsh=# INSERT INTO bank_customer VALUES(2001,'roy','calicut'); INSERT 0 1 dvdrsh=# INSERT INTO bank_customer VALUES(2002,'anu','kochi'); INSERT 0 1 dvdrsh=# INSERT INTO bank_customer VALUES(2003,'hari','trissur'); INSERT 0 1 dvdrsh=# INSERT INTO bank_customer VALUES(2004,'megha','kannur'); INSERT 0 1 dvdrsh=# INSERT INTO bank_customer VALUES(2005,'siva','palakkad'); INSERT 0 1 dvdrsh=# INSERT INTO deposit VALUES(2001,101,20000); INSERT 0 1 dvdrsh=# INSERT INTO deposit VALUES(2002,102,15000); INSERT 0 1 dvdrsh=# INSERT INTO loan VALUES(2003,201,200000); INSERT 0 1 dvdrsh=# INSERT INTO loan VALUES(2001,202,50000); INSERT 0 1 -- a) Display customer details dvdrsh=# SELECT * FROM bank_customer; accno | cust_name | place -------+-----------+----------- 2001 | roy | calicut 2002 | anu | kochi 2003 | hari | trissur 2004 | megha | kannur 2005 | siva | palakkad (5 rows) -- b) Customers with deposit dvdrsh=# SELECT bank_customer.cust_name,deposit.damount FROM bank_customer INNER JOIN deposit ON bank_customer.accno=deposit.accno; cust_name | damount -----------+--------- roy | 20000 anu | 15000 (2 rows) -- c) Customers with loan dvdrsh=# SELECT bank_customer.cust_name,loan.lamount FROM bank_customer INNER JOIN loan ON bank_customer.accno=loan.accno; cust_name | lamount -----------+--------- hari | 200000 roy | 50000 (2 rows) -- d) Customers having both loan and deposit dvdrsh=# SELECT bank_customer.cust_name FROM bank_customer INNER JOIN deposit ON bank_customer.accno=deposit.accno INTERSECT SELECT bank_customer.cust_name FROM bank_customer INNER JOIN loan ON bank_customer.accno=loan.accno; cust_name ----------- roy (1 row) -- e) Customers with neither loan nor deposit dvdrsh=# SELECT cust_name FROM bank_customer WHERE accno NOT IN (SELECT accno FROM deposit) AND accno NOT IN (SELECT accno FROM loan); cust_name ----------- megha siva (2 rows)

dvdrsh=# CREATE TABLE product( product_code int PRIMARY KEY, product_name varchar(20), category varchar(20), quantity int, price int ); CREATE TABLE -- Insert Records dvdrsh=# INSERT INTO product VALUES(101,'soap','bath soap',300,25); INSERT 0 1 dvdrsh=# INSERT INTO product VALUES(102,'sunsilk','shampoo',80,45); INSERT 0 1 dvdrsh=# INSERT INTO product VALUES(103,'surf','washing powder',600,55); INSERT 0 1 dvdrsh=# INSERT INTO product VALUES(104,'paste','paste',120,30); INSERT 0 1 dvdrsh=# INSERT INTO product VALUES(105,'salt','food',50,20); INSERT 0 1 -- a) Records in descending order of Product_Name dvdrsh=# SELECT * FROM product ORDER BY product_name DESC; product_code | product_name | category | quantity | price --------------+--------------+----------------+----------+------ 102 | sunsilk | shampoo | 80 | 45 103 | surf | washing powder | 600 | 55 101 | soap | bath soap | 300 | 25 105 | salt | food | 50 | 20 104 | paste | paste | 120 | 30 (5 rows) -- b) Product_Code and Product_Name with price between 20 and 50 dvdrsh=# SELECT product_code,product_name FROM product WHERE price BETWEEN 20 AND 50; product_code | product_name --------------+-------------- 101 | soap 102 | sunsilk 104 | paste 105 | salt (4 rows) -- c) Products belonging to bath soap, paste, or washing powder dvdrsh=# SELECT * FROM product WHERE category IN ('bath soap','paste','washing powder'); product_code | product_name | category | quantity | price --------------+--------------+----------------+----------+------ 101 | soap | bath soap | 300 | 25 103 | surf | washing powder | 600 | 55 104 | paste | paste | 120 | 30 (3 rows) -- d) Products with quantity <100 or >500 dvdrsh=# SELECT * FROM product WHERE quantity<100 OR quantity>500; product_code | product_name | category | quantity | price --------------+--------------+----------------+----------+------ 102 | sunsilk | shampoo | 80 | 45 103 | surf | washing powder | 600 | 55 105 | salt | food | 50 | 20 (3 rows) -- e) Products whose name starts with 's' dvdrsh=# SELECT product_name FROM product WHERE product_name LIKE 's%'; product_name -------------- soap sunsilk surf salt (4 rows) -- f) Products not belonging to category 'paste' dvdrsh=# SELECT * FROM product WHERE category!='paste'; product_code | product_name | category | quantity | price --------------+--------------+----------------+----------+------ 101 | soap | bath soap | 300 | 25 102 | sunsilk | shampoo | 80 | 45 103 | surf | washing powder | 600 | 55 105 | salt | food | 50 | 20 (4 rows) -- g) Products whose second letter is 'u' and category is washing powder dvdrsh=# SELECT product_name FROM product WHERE product_name LIKE '_u%' AND category='washing powder'; product_name -------------- surf (1 row)

dvdrsh=# CREATE TABLE employee( employee_name varchar(20), city varchar(20) ); CREATE TABLE dvdrsh=# CREATE TABLE works( employee_name varchar(20), company_name varchar(20), salary int ); CREATE TABLE dvdrsh=# CREATE TABLE company( company_name varchar(20), city varchar(20) ); CREATE TABLE dvdrsh=# CREATE TABLE manages( employee_name varchar(20), manager_name varchar(20) ); CREATE TABLE -- Insert Records dvdrsh=# INSERT INTO employee VALUES('akash','chennai'); INSERT 0 1 dvdrsh=# INSERT INTO employee VALUES('rahul','bangalore'); INSERT 0 1 dvdrsh=# INSERT INTO employee VALUES('meera','kochi'); INSERT 0 1 dvdrsh=# INSERT INTO employee VALUES('anil','bangalore'); INSERT 0 1 dvdrsh=# INSERT INTO employee VALUES('sita','chennai'); INSERT 0 1 dvdrsh=# INSERT INTO works VALUES('akash','Infosys',20000); INSERT 0 1 dvdrsh=# INSERT INTO works VALUES('rahul','Wipro',15000); INSERT 0 1 dvdrsh=# INSERT INTO works VALUES('meera','Infosys',12000); INSERT 0 1 dvdrsh=# INSERT INTO works VALUES('anil','TCS',9000); INSERT 0 1 dvdrsh=# INSERT INTO works VALUES('sita','Wipro',18000); INSERT 0 1 dvdrsh=# INSERT INTO company VALUES('Infosys','bangalore'); INSERT 0 1 dvdrsh=# INSERT INTO company VALUES('Wipro','bangalore'); INSERT 0 1 dvdrsh=# INSERT INTO company VALUES('TCS','chennai'); INSERT 0 1 -- A) Employees who work in Infosys dvdrsh=# SELECT employee_name FROM works WHERE company_name='Infosys'; employee_name --------------- akash meera (2 rows) -- B) Names and cities of employees who work in Wipro dvdrsh=# SELECT works.employee_name,employee.city FROM works JOIN employee ON works.employee_name=employee.employee_name WHERE company_name='Wipro'; employee_name | city ---------------+----------- rahul | bangalore sita | chennai (2 rows) -- C) Employees working in Infosys with salary > 10000 dvdrsh=# SELECT works.employee_name,employee.city FROM works JOIN employee ON works.employee_name=employee.employee_name WHERE company_name='Infosys' AND salary>10000; employee_name | city ---------------+----------- akash | chennai meera | kochi (2 rows) -- D) Employees living in same city as their company dvdrsh=# SELECT DISTINCT employee.employee_name FROM employee JOIN works ON employee.employee_name=works.employee_name JOIN company ON works.company_name=company.company_name WHERE employee.city=company.city; employee_name --------------- rahul (1 row) -- E) Employees not working in Wipro dvdrsh=# SELECT employee_name FROM works WHERE company_name!='Wipro'; employee_name --------------- akash meera anil (3 rows) -- F) Company with most employees dvdrsh=# SELECT company_name,COUNT(*) FROM works GROUP BY company_name ORDER BY COUNT(*) DESC LIMIT 1; company_name | count --------------+------- Infosys | 2 (1 row)

dvdrsh=# CREATE TABLE employee2( empid varchar(5), ename varchar(20), salary int, department varchar(15), age int ); CREATE TABLE -- Insert Records dvdrsh=# INSERT INTO employee2 VALUES('E01','arun',25000,'sales',30); INSERT 0 1 dvdrsh=# INSERT INTO employee2 VALUES('E02','meera',30000,'sales',35); INSERT 0 1 dvdrsh=# INSERT INTO employee2 VALUES('E03','rahul',20000,'purchase',28); INSERT 0 1 dvdrsh=# INSERT INTO employee2 VALUES('E04','sita',22000,'purchase',32); INSERT 0 1 dvdrsh=# INSERT INTO employee2 VALUES('E05','anil',40000,'accounts',45); INSERT 0 1 -- B) Total number of employees dvdrsh=# SELECT COUNT(*) FROM employee2; count ------- 5 (1 row) -- C) Oldest employee of each department dvdrsh=# SELECT department,MAX(age) FROM employee2 GROUP BY department; department | max ------------+----- sales | 35 purchase | 32 accounts | 45 (3 rows) -- D) Average age of employees in each department dvdrsh=# SELECT department,AVG(age) FROM employee2 GROUP BY department; department | avg ------------+---------------- sales | 32.5 purchase | 30 accounts | 45 (3 rows) -- E) Department and average salary dvdrsh=# SELECT department,AVG(salary) FROM employee2 GROUP BY department; department | avg ------------+------- sales | 27500 purchase | 21000 accounts | 40000 (3 rows) -- F) Lowest salary dvdrsh=# SELECT MIN(salary) FROM employee2; min ------ 20000 (1 row) -- G) Employees in purchase department dvdrsh=# SELECT COUNT(*) FROM employee2 WHERE department='purchase'; count ------- 2 (1 row) -- H) Highest salary in sales department dvdrsh=# SELECT MAX(salary) FROM employee2 WHERE department='sales'; max ------ 30000 (1 row) -- I) Difference between highest and lowest salary dvdrsh=# SELECT MAX(salary)-MIN(salary) AS salary_difference FROM employee2; salary_difference ------------------ 20000 (1 row)

dvdrsh=# CREATE TABLE areas(radius numeric, area numeric); CREATE TABLE dvdrsh=# DO $$ DECLARE r int; BEGIN FOR r IN 3..7 LOOP INSERT INTO areas VALUES(r,3.14*r*r); END LOOP; END $$; DO dvdrsh=# SELECT * FROM areas; radius | area --------+--------- 3 | 28.26 4 | 50.24 5 | 78.50 6 | 113.04 7 | 153.86 (5 rows)

dvdrsh=# CREATE OR REPLACE FUNCTION is_prime(n int) RETURNS text AS $$ DECLARE i int := 2; BEGIN IF n <= 1 THEN RETURN 'not prime'; END IF; WHILE i <= n/2 LOOP IF n % i = 0 THEN RETURN 'not prime'; END IF; i := i + 1; END LOOP; RETURN 'prime'; END; $$ LANGUAGE plpgsql; CREATE FUNCTION dvdrsh=# SELECT is_prime(7); is_prime ---------- prime (1 row) dvdrsh=# SELECT is_prime(8); is_prime ---------- not prime (1 row)

dvdrsh=# DO $$ DECLARE cust_no int := 101; unit int := 350; charge numeric; BEGIN IF unit >= 600 THEN charge := unit * 3.5; ELSIF unit >= 400 THEN charge := unit * 3.0; ELSIF unit >= 200 THEN charge := unit * 2.5; ELSIF unit >= 100 THEN charge := unit * 2.0; ELSE charge := unit * 1.5; END IF; RAISE NOTICE 'Consumer Number: %', cust_no; RAISE NOTICE 'Unit Consumed: %', unit; RAISE NOTICE 'Bill Amount: %', charge; END $$; NOTICE: Consumer Number: 101 NOTICE: Unit Consumed: 350 NOTICE: Bill Amount: 875.0 DO

dvdrsh=# CREATE TABLE exam_result( rollno int, avg_score int, grade varchar(5) ); CREATE TABLE dvdrsh=# INSERT INTO exam_result VALUES (1,95,NULL), (2,82,NULL), (3,70,NULL), (4,55,NULL), (5,40,NULL), (6,91,NULL), (7,76,NULL), (8,62,NULL), (9,58,NULL), (10,30,NULL); INSERT 0 10 dvdrsh=# UPDATE exam_result SET grade='A' WHERE avg_score BETWEEN 90 AND 100; UPDATE 2 dvdrsh=# UPDATE exam_result SET grade='B' WHERE avg_score BETWEEN 75 AND 89; UPDATE 2 dvdrsh=# UPDATE exam_result SET grade='C' WHERE avg_score BETWEEN 60 AND 74; UPDATE 2 dvdrsh=# UPDATE exam_result SET grade='D' WHERE avg_score BETWEEN 50 AND 59; UPDATE 2 dvdrsh=# UPDATE exam_result SET grade='E' WHERE avg_score < 50; UPDATE 2 dvdrsh=# SELECT * FROM exam_result; rollno | avg_score | grade --------+-----------+------ 1 | 95 | A 2 | 82 | B 3 | 70 | C 4 | 55 | D 5 | 40 | E 6 | 91 | A 7 | 76 | B 8 | 62 | C 9 | 58 | D 10 | 30 | E (10 rows)

dvdrsh=# CREATE OR REPLACE PROCEDURE fibonacci(n int) LANGUAGE plpgsql AS $$ DECLARE first int := 0; second int := 1; third int; i int; BEGIN RAISE NOTICE 'Fibonacci series:'; RAISE NOTICE '%', first; RAISE NOTICE '%', second; FOR i IN 2..n LOOP third := first + second; first := second; second := third; RAISE NOTICE '%', third; END LOOP; END; $$; CREATE PROCEDURE dvdrsh=# CALL fibonacci(7); NOTICE: Fibonacci series: NOTICE: 0 NOTICE: 1 NOTICE: 1 NOTICE: 2 NOTICE: 3 NOTICE: 5 NOTICE: 8 NOTICE: 13 CALL