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,
Ename VARCHAR(50),
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
CREATE TABLE Supplier (
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
List flno, distance, price
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
Trigger for salary update
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;
