dbms lab

 

Set 1

1. a) Create Client_Master table with the following columns

Client_Master(Client_No,Name,City,Pincode,State,Bal_Due).

CREATE TABLE Client_Master (

    Client_No VARCHAR(6) PRIMARY KEY,

    Name VARCHAR(50),

    City VARCHAR(30),

    Pincode VARCHAR(10),

    State VARCHAR(30),

    Bal_Due DECIMAL(10, 2)

);

    b) Insert the following data into Client_Master Table.

CLIENT NO	NAME	CITY	PINCODE	STATE	BAL_DUE
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

1.       INSERT INTO Client_Master VALUES

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

2.       INSERT INTO Client_Master VALUES

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

3.       INSERT INTO Client_Master VALUES

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

4.       INSERT INTO Client_Master VALUES

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

5.       INSERT INTO Client_Master VALUES

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

6.       INSERT INTO Client_Master VALUES

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

c) Retrieve the list of names and cities of all the clients.

              SELECT Name, City FROM Client_Master;

d) Find out the clients who stay in a city whose second letter is ‘A’.

        SELECT * FROM Client_Master        WHERE SUBSTRING(City FROM 2 FOR 1) = 'A';

e) Update the city of a Client as CHENNAI whose Client_no is ‘C00004’.

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

2. Write a PL/SQL Program to find Minimum of two numbers using findMin() Procedure using Postgresql?

           

DROP PROCEDURE IF EXISTS findMin;

CREATE OR REPLACE PROCEDURE findMin(IN num1 INT, IN num2 INT, OUT minValue INT)

LANGUAGE plpgsql

AS $$

BEGIN

    minValue := LEAST(num1, num2);

END;

$$;

 

DO $$

DECLARE

    fetched_num1 INTEGER;

    fetched_num2 INTEGER;

    result INTEGER;

BEGIN

    SELECT num1, num2 INTO fetched_num1, fetched_num2 FROM input_values;

 

    CALL findMin(fetched_num1, fetched_num2, result);

 

    INSERT INTO min_value_output (min_value) VALUES (result);

END $$;

 

SELECT * FROM min_value_output;

 

Set 2

1) a) Create Emp table with the following columns

Emp( Empno, Ename, job, Mgr,hiredate, Sal, Comm, Deptno)

 

CREATE TABLE Emp (

    Empno INT PRIMARY KEY,

    Ename VARCHAR(50),

    Job VARCHAR(50),

    Mgr INT,

    Hiredate DATE,

    Sal NUMERIC(10,2),

    Comm NUMERIC(10,2),

    Deptno INT

);

 

    b) Insert the following data into Emp table.

EMPNO	ENAME	JOB	MGR	HIREDATE	SAL	COMM	DEPTNO
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

·        INSERT INTO Emp VALUES

              (7369, 'SMITH', 'CLERK', 7902, TO_DATE('1980-12-17', 'YYYY-MM-DD'), 800, NULL, 20);

 

·        INSERT INTO Emp VALUES

              (7499, 'ALLEN', 'SALESMAN', 7698, TO_DATE('1981-02-20', 'YYYY-MM-DD'), 1600, 300, 30);

 

·        INSERT INTO Emp VALUES

              (7521, 'WARD', 'SALESMAN', 7698, TO_DATE('1981-02-22', 'YYYY-MM-DD'), 1250, 500, 30);

 

·        INSERT INTO Emp VALUES

              (7566, 'JONES', 'MANAGER', 7839, TO_DATE('1981-04-02', 'YYYY-MM-DD'), 2975, NULL, 20);

 

·        INSERT INTO Emp VALUES

              (7698, 'BLAKE', 'MANAGER', 7839, TO_DATE('1981-05-01', 'YYYY-MM-DD'), 2850, NULL, 30);

 

·        INSERT INTO Emp VALUES

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

 c) Find the Average salary for each and every Department.

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

    d) List the ename, job, maximum salary from each department and display in Descending order.

              SELECT Deptno, Ename, Job, Sal AS Max_Salary FROM Emp

                           WHERE (Deptno, Sal) IN ( SELECT Deptno, MAX(Sal) FROM Emp GROUP BY Deptno)

                           ORDER BY Sal DESC;

    e) List Department Number having total salary greater than 5000.

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

 

2) Write a PL/SQL Program to create a Function which counts no.of Employees in Employee table.

CREATE OR REPLACE FUNCTION CountEmployees()

RETURNS INT AS $$

DECLARE

    EmployeeCount INT;

BEGIN

    -- Counting the number of employees

    SELECT COUNT(*) INTO EmployeeCount

    FROM Employee;

 

    RETURN EmployeeCount;

END;

$$ LANGUAGE plpgsql;

 

Set 3

1. a) Create Supplier Table with the following columns

Supplier (SNO,SNAME,CITY,STATUS).

CREATE TABLE Supplier (

    SNO     NUMBER PRIMARY KEY,

    SNAME   VARCHAR2(50),

    CITY    VARCHAR2(50),

    STATUS  NUMBER

);

   b) Insert the following data into Supplier table

SNO	SNAME	CITY	STATUS
S1	Smith	London	20
S2	Jones	Paris	10
S3	Blake	Paris	30
S4	Clark	London	20
S5	Adams	Athens	30

·        INSERT INTO Supplier VALUES ('S1', 'Smith', 'London', 20);

·        INSERT INTO Supplier VALUES ('S2', 'Jones', 'Paris', 10);

·        INSERT INTO Supplier VALUES ('S3', 'Blake', 'Paris', 30);

·        INSERT INTO Supplier VALUES ('S4', 'Clark', 'London', 20);

·        INSERT INTO Supplier VALUES ('S5', 'Adams', 'Athens', 30);

    c) Find out the total number of Suppliers.

                            SELECT COUNT(*) FROM Supplier;

    d) List the Supplier names who are living at London.

                           SELECT SupplierName FROM Supplier WHERE City = 'London';

    e) Update the status of supplier as 20 whose Supplier No is S3.

                           UPDATE Supplier SET Status = 20 WHERE SupplierNo = 'S3';

 

2. Create a table and perform the search operation on table using indexing and non-indexing techniques.

SET SERVEROUTPUT ON;

 

-- Step 1: Create the Employees table

BEGIN

  EXECUTE IMMEDIATE 'DROP TABLE Employees';

EXCEPTION

  WHEN OTHERS THEN

    IF SQLCODE != -942 THEN -- ORA-00942: table or view does not exist

      RAISE;

    END IF;

END;

/

 

SET 4

1. a) Create Parts table with the following columns

Parts (PNO, PNAME,COLOR,WEIGTH,CITY,COST)

CREATE TABLE Parts (

  PNO     NUMBER (10) PRIMARY KEY,

  PNAME   VARCHAR (50),

  COLOR   VARCHAR (20),

  WEIGHT   NUMBER (10),

  CITY    VARCHAR (50),

  COST    NUMBER (10)

);

    b) Insert the following data into Supplier table

PNO	PNAME	COLOR	WEIGHT	CITY	COST
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

   INSERT INTO Supplier (PNO, PNAME, COLOR, WEIGHT, CITY, COST)

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

 

INSERT INTO Supplier (PNO, PNAME, COLOR, WEIGHT, CITY, COST)

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

 

INSERT INTO Supplier (PNO, PNAME, COLOR, WEIGHT, CITY, COST)

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

 

INSERT INTO Supplier (PNO, PNAME, COLOR, WEIGHT, CITY, COST)

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

 

INSERT INTO Supplier (PNO, PNAME, COLOR, WEIGHT, CITY, COST)

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

 

INSERT INTO Supplier (PNO, PNAME, COLOR, WEIGHT, CITY, COST)

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

     c) List the Part no, Part Name from the Parts table whose part color is either in Red or blue.

          SELECT PNO, PNAME FROM Parts WHERE COLOR IN ('Red', 'Blue');

    d) Find the total cost of parts.

         SELECT SUM (COST) AS Total Cost FROM Parts;

    e) Finding parts with a cost higher than the cost of any part in 'London'.

           SELECT PNO, PNAME, COLOR, WEIGHT, CITY, COSTFROM PartsWHERE COST > ANY (

          SELECT COST FROM PartsWHERE CITY = 'London');

 

2. Write a PL/SQL Program to find Minimum of two numbers using findMin() Procedure using Postgresql?

 

DO $$

DECLARE

  result INTEGER;

BEGIN

  CALL findMin(25, 40, result);

  RAISE NOTICE 'Minimum value is: %', result;

END;

$$;

Set 5

1. a) Create Flights table with the following columns

Flights ( flno integer, from string, to string, distance integer, Price integer).

CREATE TABLE Flights (  flno  INTEGER PRIMARY KEY,"from"   VARCHAR(255),  "to"     VARCHAR(255), distance INTEGER, price    INTEGER);

   b) Insert the following data into Flights table

flno	from	to	distance	Price
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

  INSERT INTO Flights (flno, "from", "to", distance, price)

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

 

INSERT INTO Flights (flno, "from", "to", distance, price)

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

 

INSERT INTO Flights (flno, "from", "to", distance, price)

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

 

INSERT INTO Flights (flno, "from", "to", distance, price)

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

 

INSERT INTO Flights (flno, "from", "to", distance, price)

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

c) Find the average price of flights.

SELECT AVG(price) AS average _price FROM Flights;

    d) Find flights with a price higher than the average price.

            SELECT flno, "from", "to", distance, priceFROM FlightsWHERE price > (SELECT AVG(price) FROM Flights);

    e)  List the flight Ids, distance and price from the flights table.

         SELECT flno AS flight_id, distance, priceFROM Flights;

 

2. Create a Function which counts no.of Flights in Flight table

CREATE OR REPLACE FUNCTION count_flights

RETURN INTEGER

IS

  flight_count INTEGER;

BEGIN

  SELECT COUNT(*) INTO flight_count FROM Flights;

  RETURN flight_count;

END;

/

 

Set 6

1. a) Create Aircraft table with the following columns

Aircraft (aid integer, aname string, cruising_range integer)

CREATE TABLE Aircraft (

  aid NUMBER PRIMARY KEY,

  aname VARCHAR(255),

  cruisingrange INTEGER

);

   b) Insert the following data into Aircraft table.

aid	aname	cruising_range
1	Boeing 737	2800
2	Airbus A320	2500
3	Boeing 777	5000
4	Embraer E190	1800

 

INSERT INTO Aircraft (aid, aname, cruisingrange) VALUES

  (1, 'Boeing 737', 2800);

 

INSERT INTO Aircraft (aid, aname, cruisingrange) VALUES

  (2, 'Airbus A320', 2500);

 

INSERT INTO Aircraft (aid, aname, cruisingrange) VALUES

  (3, 'Boeing 777', 5000);

 

INSERT INTO Aircraft (aid, aname, cruisingrange) VALUES

  (4, 'Embraer E190', 1800);

 c) Find the aircraft with the maximum and minimum cruising range.

SELECT aid, aname, cruisingrange FROM Aircraft WHERE cruisingrange = (SELECT MAX (cruising range) FROM Aircraft);

SELECT aid, aname, cruisingrange FROM Aircraft WHERE cruisingrange = (SELECT MIN(cruising range) FROM Aircraft);

   d)  Find aircraft that are NOT certified.

SELECT aid, aname, cruisingrangeFROM AircraftWHERE certification_status IS NULLOR certification_status = 'Not Certified';

   e) List the aircraft names whose cruising range is greater than 2000.

SELECT anameFROM AircraftWHERE cruisingrange > 2000;

2. Write a PL/SQL Program to Create a row-level trigger that updates the last_updated column to 'Salary Updated' whenever the salary of an employee is updated. Demonstrate the functionality by updating the salary of an employee (e.g., update the salary of the employee with id = 1 to 55000.00). View the updated table to verify the last_updated column is correctly updated.

 CREATE TABLE Employee ( id NUMBER PRIMARY KEY, name VARCHAR2(100), salary NUMBER(10,2), last_updated VARCHAR2(50) ); 

INSERT INTO Employee (id, name, salary, last_updated) VALUES (1, 'John Doe', 50000.00, NULL); 

CREATE OR REPLACE TRIGGER trg_salary_update BEFORE UPDATE OF salary ON Employee FOR EACH ROW BEGIN :NEW.last_updated := 'Salary Updated'; END; / 

UPDATE Employee SET salary = 55000.00 WHERE id = 1;  SELECT * FROM Employee;