Set 1

Create Client_Master table

CREATE TABLE Client_Master (

Client_No VARCHAR(6) PRIMARY KEY,

Name VARCHAR(50),

City VARCHAR(50),

Pincode VARCHAR(6),

State VARCHAR(50),

Bal_Due DECIMAL(10, 2)

);

Insert data into Client_Master

INSERT INTO Client_Master VALUES

('C00001', 'IVAN BAYROSS', 'BOMBAY', '400054', 'MAHARASHTRA', 15000),

('C00002', 'VANDANA SAITWAL', 'MADRAS', '780001', 'TAMIL NADU', 0),

('C00003', 'PRAMADA JAGUSTE', 'BOMBAY', '400057', 'MAHARASHTRA', 5000),

('C00004', 'BASU NAVINDGI', 'BOMBAY', '400056', 'MAHARASHTRA', 0),

('C00005', 'RAVI SREEDHARAN', 'DELHI', '100001', 'DELHI', 2000),

('C00006', 'RUKMINI', 'BOMBAY', '400050', 'MAHARASHTRA', 0);

Retrieve names and cities

SELECT Name, City FROM Client_Master;

Clients in cities with second letter 'A'

SELECT * FROM Client_Master WHERE City LIKE '_A%';

Update city to CHENNAI

UPDATE Client_Master SET City = 'CHENNAI' WHERE Client_No = 'C00004';

Clients in CHENNAI or DELHI

SELECT * FROM Client_Master WHERE City IN ('CHENNAI', 'DELHI');

Set 2

Create Emp table

CREATE TABLE Emp (

Empno INT PRIMARY KEY,

Job VARCHAR(50),

Mgr INT,

Hiredate DATE,

Sal DECIMAL(10, 2),

Comm DECIMAL(10, 2),

Deptno INT

);

Insert data

INSERT INTO Emp VALUES

(7369, 'SMITH', 'CLERK', 7902, '1980-12-17', 800, NULL, 20),

(7499, 'ALLEN', 'SALESMAN', 7698, '1981-02-20', 1600, 300, 30),

(7521, 'WARD', 'SALESMAN', 7698, '1981-02-22', 1250, 500, 30),

(7566, 'JONES', 'MANAGER', 7839, '1981-04-02', 2975, NULL, 20),

(7698, 'BLAKE', 'MANAGER', 7839, '1981-05-01', 2850, NULL, 30),

(7844, 'TURNER', 'SALESMAN', 7698, '1981-09-08', 1500, 0, 30);

Average salary per department

SELECT Deptno, AVG(Sal) AS Avg_Salary FROM Emp GROUP BY Deptno;

Max salary per department

SELECT Deptno, MAX(Sal) AS Max_Salary FROM Emp GROUP BY Deptno ORDER BY Max_Salary DESC;

Departments with total salary > 5000

SELECT Deptno FROM Emp GROUP BY Deptno HAVING SUM(Sal) > 5000;

Function to count employees

CREATE OR REPLACE FUNCTION Count_Employees() RETURNS INT AS $$

BEGIN

RETURN (SELECT COUNT(*) FROM Emp);

END;

$$ LANGUAGE plpgsql;

Set 3

Create Supplier table

SNO VARCHAR(3) PRIMARY KEY,

SNAME VARCHAR(50),

CITY VARCHAR(50),

STATUS INT

);

Insert data into Supplier

INSERT INTO Supplier VALUES

('S1', 'Smith', 'London', 20),

('S2', 'Jones', 'Paris', 10),

('S3', 'Blake', 'Paris', 30),

('S4', 'Clark', 'London', 20),

('S5', 'Adams', 'Athens', 30);

Total number of suppliers

SELECT COUNT(*) FROM Supplier; -- Output: 5

Suppliers in London

SELECT SNAME FROM Supplier WHERE CITY = 'London'; -- Output: Smith, Clark

Update status of S3

UPDATE Supplier SET STATUS = 20 WHERE SNO = 'S3';

Indexing Example

-- Table

CREATE TABLE Products (

PID INT PRIMARY KEY,

PNAME VARCHAR(50),

CATEGORY VARCHAR(50)

);

-- Index

CREATE INDEX idx_category ON Products(CATEGORY);

-- Indexed search
SELECT * FROM Products WHERE CATEGORY = 'Electronics';

-- Non-indexed search

SELECT * FROM Products WHERE PNAME = 'Laptop';

Set 4

Create Parts table

CREATE TABLE Parts (

PNO VARCHAR(3) PRIMARY KEY,

PNAME VARCHAR(50),

COLOR VARCHAR(20),

WEIGHT DECIMAL(5, 2),

CITY VARCHAR(50),

COST DECIMAL(6, 2)

);

Insert data into Parts

INSERT INTO Parts VALUES

('P1', 'Nut', 'Red', 12.0, 'London', 0.50),

('P2', 'Bolt', 'Green', 17.0, 'Paris', 0.75),

('P3', 'Screw', 'Blue', 17.0, 'Oslo', 0.25),

('P4', 'Screw', 'Red', 14.0, 'London', 0.35),

('P5', 'Cam', 'Blue', 12.0, 'Paris', 1.50),

('P6', 'Cog', 'Red', 19.0, 'London', 0.90);

Parts with color Red or Blue

SELECT PNO, PNAME FROM Parts WHERE COLOR IN ('Red', 'Blue'); -- Output: P1, P3, P4, P5,

P6

Total cost of parts

SELECT SUM(COST) FROM Parts; -- Output: 4.25

Parts cost > any in London

SELECT * FROM Parts WHERE COST > ALL (SELECT COST FROM Parts WHERE CITY = 'London'); --

Output: P2, P5
Procedure for min of two numbers

CREATE OR REPLACE FUNCTION findMin(a INT, b INT) RETURNS INT AS $$

BEGIN

IF a < b THEN

RETURN a;

ELSE

RETURN b;

END IF;

END;

$$ LANGUAGE plpgsql;

Set 5

Create Flights table

CREATE TABLE Flights (

flno INT PRIMARY KEY,

"from" VARCHAR(50),

"to" VARCHAR(50),

distance INT,

price INT

);

Insert data into Flights

INSERT INTO Flights VALUES

(101, 'HYD', 'DEL', 1500, 3500),

(102, 'DEL', 'HYD', 1500, 3200),

(205, 'BOM', 'BLR', 850, 2800),

(206, 'BLR', 'BOM', 850, 2500),

(310, 'CCU', 'MAA', 1650, 4000);

Average price of flights

SELECT AVG(Price) FROM Flights; -- Output: 3200

Flights with price > average

SELECT flno, "from", "to", distance, Price FROM Flights WHERE Price > (SELECT AVG(Price)

FROM Flights); -- Output: 101, 310

SELECT flno, distance, price FROM Flights;

Function to count flights

CREATE OR REPLACE FUNCTION Count_Flights() RETURNS INT AS $$

BEGIN

RETURN (SELECT COUNT(*) FROM Flights);

END;

$$ LANGUAGE plpgsql;

Set 6

Create Aircraft table

CREATE TABLE Aircraft (

aid INT PRIMARY KEY,

aname VARCHAR(50),

cruising_range INT

);

Insert data

INSERT INTO Aircraft VALUES

(1, 'Boeing 737', 2800),

(2, 'Airbus A320', 2500),

(3, 'Boeing 777', 5000),

(4, 'Embraer E190', 1800);

Max and Min cruising range

SELECT MAX(cruising_range), MIN(cruising_range) FROM Aircraft; -- Output: 5000, 1800

Aircrafts NOT certified

SELECT aid, aname, cruising_range FROM Aircraft WHERE aid NOT IN (SELECT DISTINCT aid

FROM Certified);

Cruising range > 2000

SELECT aname FROM Aircraft WHERE cruising_range > 2000; -- Output: Boeing 737, Airbus

A320, Boeing 777

ALTER TABLE Emp ADD COLUMN last_updated TEXT;

CREATE OR REPLACE FUNCTION update_last_updated() RETURNS TRIGGER AS $$

BEGIN

NEW.last_updated := 'Salary Updated';

RETURN NEW;

END;

$$ LANGUAGE plpgsql;

CREATE TRIGGER salary_update_trigger

BEFORE UPDATE OF Sal ON Emp

FOR EACH ROW

EXECUTE FUNCTION update_last_updated();

-- Test update

UPDATE Emp SET Sal = 55000.00 WHERE Empno = 1;

-- Verify

SELECT * FROM Emp WHERE Empno = 1;