DBMS Practical Questions

Problem 1


-- client_master table

CREATE TABLE client_master (

    client_no VARCHAR(6),

    name VARCHAR(20),

    address VARCHAR(30),

    city VARCHAR(15),

    pincode NUMERIC(8),

    state VARCHAR(15),

    bal_due NUMERIC(10,2)

);


-- Product_master table

CREATE TABLE Product_master (

    product_no VARCHAR(6),

    description VARCHAR(15),

    Profit_percent NUMERIC(4,2),

    Unit_measure VARCHAR(10),

    Qty_on_hand NUMERIC(8),

    Reorder_level NUMERIC(8),

    Sell_price NUMERIC(8,2),

    Cost_price NUMERIC(8,2)

);

-- . Insert 5-6 records in each table.

-- Inserting records into the client_master table

INSERT INTO client_master VALUES ('C001', 'John Doe', '123 Main St', 'Mumbai', 123456, 'StateA', 5000.00);

INSERT INTO client_master VALUES ('C002', 'Jane Smith', '456 Elm St', 'Pune', 789012, 'StateB', 10000.00);

INSERT INTO client_master VALUES ('C003', 'David Johnson', '789 Oak St', 'Tamil Nadu', 345678, 'StateC', 15000.00);

INSERT INTO client_master VALUES ('C004', 'Emily Brown', '321 Maple St', 'Delhi', 901234, 'StateD', 20000.00);


-- Inserting records into the Product_master table

INSERT INTO Product_master VALUES ('P001', 'ProductA', 0.05, 'UnitA', 100, 20, 100.00, 80.00);

INSERT INTO Product_master VALUES ('P002', 'ProductB', 0.08, 'UnitB', 150, 25, 150.00, 120.00);

INSERT INTO Product_master VALUES ('P003', 'ProductC', 0.10, 'UnitC', 200, 30, 200.00, 160.00);

INSERT INTO Product_master VALUES ('P004', 'ProductD', 0.12, 'UnitD', 120, 15, 180.00, 140.00);


-- Find out the names of all clients

SELECT name FROM client_master;


--  Retrieve the entire contents of the client_master table.

SELECT * from client_master;


--  Retrieve the list of names and cities of all the clients

SELECT name, city FROM client_master;


-- List the various products available from the product_master table

SELECT description from Product_master;


-- List all the clients who are located in mumbai

SELECT name, city 

FROM client_master 

WHERE city = 'Mumbai';


-- Change the city of client_no C001 to mumbai

UPDATE client_master

SET city = 'Mumbai'

WHERE client_no = 'C001';


-- Change the bal_due of client_no C005 to Rs. 1000

UPDATE client_master

SET bal_due = 1000.00

WHERE client_no = 'C005';


-- Change the cost price of 'hard disk' to Rs. 3000

-- Here instead of 'Hard Disk' we take 'Product A'

UPDATE Product_master

SET Cost_price = 3000.00

WHERE description = 'ProductA';


-- Delete all the products from product_master where the qty_on_hand is less than 100 

DELETE FROM Product_master

WHERE Qty_on_hand < 100;


-- . Delete from client_master where the column state holds the value 'Tamil Nadu'

-- Here instead of 'Tamil Nadu' we take 'Mumbai'

DELETE FROM client_master

WHERE state = 'Tamil Nadu';



Problem 3 & 2


-- client_master table

CREATE TABLE client_master (

    client_no VARCHAR(6),

    name VARCHAR(20),

    address VARCHAR(30),

    city VARCHAR(15),

    pincode NUMERIC(8),

    state VARCHAR(15),

    bal_due NUMERIC(10,2)

);


-- Product_master table

CREATE TABLE Product_master (

    product_no VARCHAR(6),

    description VARCHAR(15),

    Profit_percent NUMERIC(4,2),

    Unit_measure VARCHAR(10),

    Qty_on_hand NUMERIC(8),

    Reorder_level NUMERIC(8),

    Sell_price NUMERIC(8,2),

    Cost_price NUMERIC(8,2)

);


-- sales_order table

CREATE TABLE sales_order (

    order_no VARCHAR(6),

    Order_date DATE,

    Client_no VARCHAR(6),

    Dely_addr VARCHAR(25),

    Salesman_no VARCHAR(6),

    Dely_type CHAR(1),

    Billed_yn CHAR(1),

    Dely_date DATE,

    Order_status VARCHAR(20)

);


-- Inserting records into the client_master table

INSERT INTO client_master VALUES ('C001', 'John Doe', '123 Main St', 'Mumbai', 123456, 'StateA', 5000.00);

INSERT INTO client_master VALUES ('C002', 'Jane Smith', '456 Elm St', 'Pune', 789012, 'StateB', 10000.00);

INSERT INTO client_master VALUES ('C003', 'David Johnson', '789 Oak St', 'Bangalore', 345678, 'StateC', 15000.00);

INSERT INTO client_master VALUES ('C004', 'Emily Brown', '321 Maple St', 'Delhi', 901234, 'StateD', 20000.00);


-- Inserting records into the Product_master table

INSERT INTO Product_master VALUES ('P001', 'ProductA', 0.05, 'UnitA', 100, 20, 100.00, 80.00);

INSERT INTO Product_master VALUES ('P002', 'ProductB', 0.08, 'UnitB', 150, 25, 150.00, 120.00);

INSERT INTO Product_master VALUES ('P003', 'ProductC', 0.10, 'UnitC', 200, 30, 200.00, 160.00);

INSERT INTO Product_master VALUES ('P004', 'ProductD', 0.12, 'UnitD', 120, 15, 180.00, 140.00);


-- Inserting records into the sales_order table

INSERT INTO sales_order VALUES ('SO001', '2024-01-05', 'C001', '789 Sunset Blvd', 'S001', 'X', 'Y', '2024-01-10', 'Completed');

INSERT INTO sales_order VALUES ('SO002', '2024-02-10', 'C002', '456 Sunrise Ave', 'S002', 'Y', 'N', '2024-02-15', 'In Progress');

INSERT INTO sales_order VALUES ('SO003', '2024-03-15', 'C003', '123 Moonlight Rd', 'S003', 'Z', 'Y', '2024-03-20', 'Completed');


SELECT name

FROM client_master

WHERE SUBSTRING(name, 2, 1) = 'a';


SELECT *

FROM client_master

WHERE SUBSTRING(city, 2, 1) = 'a';


SELECT *

FROM client_master

WHERE city = 'Mumbai'

ORDER BY name;


SELECT *

FROM client_master

WHERE bal_due > 10000;


SELECT *

FROM sales_order

WHERE EXTRACT(MONTH FROM Order_date) = 1;


SELECT *

FROM sales_order

WHERE Client_no IN ('C001', 'C002');



SELECT *

FROM Product_master

WHERE Sell_price > 2000 AND Sell_price <= 5000;



SELECT product_no, description, Sell_price AS original_sell_price, Sell_price * 1.5 AS new_price

FROM Product_master

WHERE Sell_price > 1500;


SELECT DATE_FORMAT(Order_date, '%d-%b-%y') AS formatted_order_date

FROM sales_order;


SELECT DATE_FORMAT(Dely_date, '%M %e') AS delivery_month_date

FROM sales_order;


SELECT DATE_ADD(CURRENT_DATE, INTERVAL 15 DAY);


SELECT DATEDIFF(CURRENT_DATE, Dely_date) AS days_elapsed

FROM sales_order;


SELECT MIN(Sell_price) AS min_price, MAX(Sell_price) AS max_price

FROM Product_master;



Problem 4


-- Write a query to create a table employee with empno, ename, designation, and salary. 

-- Emp (empno number (4), ename varchar2 (10), designatin varchar2 (10), salary number (8,2));

CREATE TABLE employee1 (

  empno numeric,

  ename varchar(10),

  designation varchar(10),

  salary_no decimal(8,2)

);


--Write a Query to Alter the column empno number (4) to empno number (6).

ALTER TABLE employee1

ALTER COLUMN empno TYPE numeric(6);


-- Write a Query to Alter the table employee with multiple columns (empno, ename.)

ALTER TABLE employee1

ALTER COLUMN ename TYPE varchar(20);


ALTER TABLE employee1

ALTER COLUMN empno TYPE numeric(10);


-- Write a Query to add multple coluns in to employee dob date, doj date

ALTER TABLE employee1

ADD COLUMN dojdate DATE;


ALTER TABLE employee1

ADD COLUMN dobdate DATE;


ALTER TABLE employee1

ADD COLUMN qualification INT;


-- Write a query to drop a column 'doj' from an existing table employee 

ALTER TABLE employee1 

DROP COLUMN dojdate;


-- Write a query to drop multiple columns 'dob' and 'qualification' from employee 

ALTER TABLE employee1

DROP COLUMN dobdate;


ALTER TABLE employee1

DROP COLUMN qualification;


-- Truncate tablle EMP 

TRUNCATE TABLE employee1;


-- Drop Table Emp 

DROP TABLE employee1;


Problem 5


-- 1. Create table company1

CREATE TABLE company1 (

    id INT,

    name TEXT NOT NULL,

    age INT NOT NULL,

    address VARCHAR(50),

    salary REAL

);


-- 2. Insert rows with name or age as NULL values

INSERT INTO company1 (id, name, age, address, salary) VALUES

(1, 'Christopher', 25, 'Address 1', 50000),

(2, 'John', 30, 'Address 2', 60000),

(3, 'Jane', 30, 'Address 3', 70000),

(4, 'Doe', 45, 'Address 4', 80000);


-- 3. Create table company3

CREATE TABLE company3 (

    id INT NOT NULL,

    name TEXT NOT NULL,

    age INT UNIQUE,

    address VARCHAR(50),

    salary REAL

);


-- 4. Insert 2 rows with the same age values in two rows

INSERT INTO company3 (id, name, age, address, salary) VALUES

(1, 'John', 25, 'Address 1', 50000),

(2, 'Doe', 30, 'Address 2', 60000),

(3, 'Jane', 31, 'Address 3', 70000);


-- 5. Create table company4

CREATE TABLE company4 (

    id INT PRIMARY KEY,

    name TEXT,

    age INT,

    address VARCHAR(50),

    salary REAL

);


-- 6. Insert 2 rows with the same id values in two rows

INSERT INTO company4 (id, name, age, address, salary) VALUES

(1, 'John', 25, 'Address 1', 50000),

(3, 'Doe', 30, 'Address 2', 60000);


-- 7. Create tables employee and department

CREATE TABLE employee (

    id INT PRIMARY KEY,

    name TEXT,

    age INT,

    address VARCHAR(50),

    salary REAL

);


CREATE TABLE department (

    id INT PRIMARY KEY,

    name VARCHAR(50) NOT NULL,

    emp_id INT REFERENCES employee(id)

);


-- 8. Insert 2 rows in employee table with id 1 and 2

INSERT INTO employee (id, name, age, address, salary) VALUES

(1, 'John', 25, 'Address 1', 50000),

(2, 'Doe', 30, 'Address 2', 60000);


-- 9. Insert row in department table with different empid

INSERT INTO department (id, name, emp_id) VALUES

(1, 'HR', 1);


-- 10. Create table company5 with salary check constraint

CREATE TABLE company5 (

    id INT,

    name TEXT,

    age INT,

    address VARCHAR(50),

    salary REAL CHECK (salary > 0)

);

-- View the content of the tables


-- View company1 table

SELECT * FROM company1;


-- View company3 table

SELECT * FROM company3;


-- View company4 table

SELECT * FROM company4;


-- View employee table

SELECT * FROM employee;


-- View department table

SELECT * FROM department;


-- View company5 table

SELECT * FROM company5;



-- 11. Insert row with salary value less than 0

-- INSERT INTO company5 (id, name, age, address, salary) VALUES

-- (1, 'John', 25, 'Address 1', -50000);





Problem 6


CREATE TABLE client_master (

client_no VARCHAR(6) PRIMARY KEY,

name VARCHAR(20) NOT NULL,

address VARCHAR(30),

city VARCHAR(15),

pincode NUMERIC(8),

state VARCHAR(15),

bal_due NUMERIC(10,2)

);


CREATE TABLE product_master (

product_no VARCHAR(6) PRIMARY KEY,

description VARCHAR(15) NOT NULL,

profit_percent NUMERIC(4,2),

unit_measure VARCHAR(10),

qty_on_hand NUMERIC(8),

reorder_lvl NUMERIC(8),

sell_price NUMERIC(8,2),

cost_price NUMERIC(8,2)

);


CREATE TABLE sales_order (

    order_no VARCHAR(6) PRIMARY KEY,

    order_date DATE NOT NULL,

    client_no VARCHAR(6),

    dely_addr VARCHAR(25),

    salesman_no VARCHAR(6),

    dely_type CHAR(1),

    billed_yn CHAR(1),

    dely_date DATE,

    order_status VARCHAR(10),

    CONSTRAINT fk_client_master

        FOREIGN KEY (client_no)

            REFERENCES client_master(client_no)

);


CREATE TABLE sales_order_details (

order_no VARCHAR(6),

product_no VARCHAR(6),

qty_ordered NUMERIC(8),

qty_disp NUMERIC(8),

product_rate NUMERIC(10,2),

CONSTRAINT fk_sales_order

FOREIGN KEY (order_no)

REFERENCES sales_order(order_no),

CONSTRAINT fk_product_master

FOREIGN KEY (product_no)

REFERENCES product_master(product_no)

);


INSERT INTO client_master(client_no,name,address,city,pincode,state,bal_due) VALUES

('C0001','Atharva','xyz Str','Mumbai',400021,'Maharashtra',1200),

('C0002','John','hdj Str','Delhi',100021,'Delhi',100),

('C0003','Jack','abc Str','Mumbai',400021,'Maharashtra',1500),

('C0004','Jill','jdk Str','Bangalore',600021,'Karnataka',20000),

('C0005','Joey','djd Str','Chennai',700021,'Tamil Nadu',3000);


INSERT INTO product_master (product_no,description,profit_percent,unit_measure,qty_on_hand,reorder_lvl,sell_price,cost_price) VALUES

('P0001','Floppies',20,'abc',1000,2,1250,750),

('P0002','CD Drive',30,'abf',500,1,500,750),

('P0003','Monitor',25,'hdj',57,3,13000,8000),

('P0004','Mouse',10,'jcjc',250,1,1500,1000),

('P0005','Keyboard',27,'jdm',100,3,7500,4500);


INSERT INTO sales_order (order_no,order_date,client_no,dely_addr,salesman_no,dely_type,billed_yn,dely_date,order_status) VALUES

('O0001','2024-04-01','C0001','abc','S234','A','Y','2024-04-08','Delivered'),

('O0002','2024-04-12','C0002','hdh','S204','B','Y','2024-04-19','Delivered'),

('O0003','2024-04-23','C0001','shd','S454','C','Y','2024-04-25','Delivered'),

('O0004','2024-04-23','C0003','hdj','S123','A','Y','2024-04-30','Shipped'),

('O0005','2024-04-24','C0004','jfj','S897','B','Y','2024-05-01','Shipped'),

('O0006','2024-04-25','C0005','hjf','S364','A','Y','2024-05-02','Shipped'),

('O0007','2024-04-25','C0002','sle','S867','C','Y','2024-05-01','Shipped');


INSERT INTO sales_order_details (order_no,product_no,qty_ordered,qty_disp,product_rate) VALUES

('O0001','P0001',4,25,1250),

('O0002','P0005',10,100,4500),

('O0003','P0002',8,25,500),

('O0004','P0001',7,25,1250),

('O0005','P0004',15,85,1000),

('O0006','P0003',13,205,13000),

('O0007','P0001',2,25,1250);


SELECT pm.product_no, pm.description

FROM client_master cm

JOIN sales_order so ON cm.client_no = so.client_no

JOIN sales_order_details sod ON so.order_no = sod.order_no

JOIN product_master pm ON sod.product_no = pm.product_no

WHERE cm.name = 'Atharva';


SELECT pm.product_no, pm.description

FROM sales_order so

JOIN sales_order_details sod ON so.order_no = sod.order_no

JOIN product_master pm ON sod.product_no = pm.product_no

WHERE so.order_status <> 'Delivered';


SELECT cm.name 

FROM product_master pm

JOIN sales_order_details sod ON pm.product_no = sod.product_no

JOIN sales_order so ON sod.order_no = so.order_no

JOIN client_master cm ON so.client_no = cm.client_no

WHERE pm.description = 'CD Drive';


SELECT sod.product_no, sod.order_no

FROM sales_order_details sod

JOIN product_master pm ON sod.product_no = pm.product_no

WHERE pm.description = 'Floppies' AND sod.qty_ordered < 5;


SELECT sod.product_no, pm.description, sod.qty_ordered

FROM sales_order_details sod

JOIN product_master pm ON sod.product_no = pm.product_no

JOIN sales_order so ON sod.order_no = so.order_no

JOIN client_master cm ON so.client_no = cm.client_no

WHERE cm.client_no IN ('C0001','C0002');


SELECT pm.description, SUM(sod.qty_ordered) AS total_qty_sold

FROM sales_order_details sod

JOIN product_master pm ON sod.product_no = pm.product_no

GROUP BY pm.description;


SELECT sod.product_no, pm.description, sod.qty_disp * sod.product_rate AS value_sold

FROM sales_order_details sod

JOIN product_master pm ON sod.product_no = pm.product_no;


SELECT cm.name 

FROM sales_order_details sod

JOIN sales_order so ON sod.order_no = so.order_no

JOIN client_master cm ON so.client_no = cm.client_no

WHERE sod.qty_ordered > 10;


SELECT order_no, COUNT(*) AS order_count

FROM sales_order_details

GROUP BY order_no

ORDER BY order_count DESC;





Problem 7

Perform the following queries using nested sub-queries


CREATE TABLE client_master (

    client_no INT PRIMARY KEY,

    name VARCHAR(255),

    address VARCHAR(255),

    city VARCHAR(100),

    pincode VARCHAR(20),

    state VARCHAR(100),

    bal_due DECIMAL(10, 2)

);


CREATE TABLE Product_master (

    product_no INT PRIMARY KEY,

    description VARCHAR(255),

    profit_percentage DECIMAL(5, 2),

    unit_measure VARCHAR(50),

    qty_on_hand INT,

    reorder_level INT,

    sell_price DECIMAL(10, 2),

    cost_price DECIMAL(10, 2)

);


CREATE TABLE Sales_order (

    order_no INT PRIMARY KEY,

    order_date DATE,

    client_no INT,

    dely_addr VARCHAR(255),

    salesman_no INT,

    dely_type VARCHAR(50),

    billed_yn CHAR(1),

    dely_date DATE,

    order_status VARCHAR(50)

);


CREATE TABLE Sales_order_details (

    order_no INT,

    product_no INT,

    qty_ordered INT,

    qty_disp INT,

    product_rate DECIMAL(10, 2),

    PRIMARY KEY (order_no, product_no),

    FOREIGN KEY (order_no) REFERENCES Sales_order (order_no),

    FOREIGN KEY (product_no) REFERENCES Product_master (product_no)

);


-- Inserting into client_master table

INSERT INTO client_master (client_no, name, address, city, pincode, state, bal_due)

VALUES

    (1, 'Joel', 'Bhandup', 'Mumbai', '12345', 'Maharashtra', 1000.00),

    (2, 'Christopher', 'Nallasopara', 'Thane', '23456', 'Maharashtra', 1500.00),

    (3, 'Vasco', 'Jamnagar', 'Ahmedabad', '34567', 'Gujarat', 2000.00),

    (4, 'Prajapati', 'Andheri', 'Mumbai', '45678', 'Maharashtra', 500.00);


-- Inserting into Product_master table

INSERT INTO Product_master (product_no, description, profit_percentage, unit_measure, qty_on_hand, reorder_level, sell_price, cost_price)

VALUES

    (101, 'Laptop', 0.15, 'Unit', 100, 20, 50000.00, 40000.00),

    (102, 'Smartphone', 0.12, 'Unit', 80, 15, 70000.00, 55000.00),

    (103, 'RAM', 0.18, 'Unit', 120, 25, 3000.00, 2000.00),

    (104, 'ROM', 0.10, 'Unit', 90, 18, 2000.00, 1500.00),

    (105, 'TV', 0.12, 'Unit', 50, 20, 10000.00, 8000.00);


-- Inserting into Sales_order table

INSERT INTO Sales_order (order_no, order_date, client_no, dely_addr, salesman_no, dely_type, billed_yn, dely_date, order_status)

VALUES

    (1001, '2024-03-10', 1, 'Bhandup', 101, 'Express', 'Y', '2024-03-12', 'Completed'),

    (1002, '2024-03-11', 2, 'Nallasopara', 102, 'Standard', 'N', NULL, 'Pending'),

    (1003, '2024-03-12', 3, 'Jamnagar', 103, 'Express', 'Y', '2024-03-14', 'Completed'),

    (1004, '2024-03-13', 4, 'Andheri', 104, 'Standard', 'Y', '2024-03-15', 'Completed');


-- Inserting into Sales_order_details table

INSERT INTO Sales_order_details (order_no, product_no, qty_ordered, qty_disp, product_rate)

VALUES

    (1001, 101, 5, 5, 50000.00),

    (1001, 102, 3, 3, 70000.00),

    (1002, 103, 2, 0, 3000.00),

    (1003, 104, 4, 4, 2000.00),

    (1004, 101, 6, 6, 50000.00);

    

-- Find the product no. and description of non-moving products i.e. products not being sold.    

SELECT product_no, description

FROM Product_master

WHERE product_no NOT IN (

  SELECT DISTINCT product_no 

  FROM Sales_order_details

);


-- Find the customer name, address for the client who has placed order no ‘O191’

-- Here we take order no = 1001

SELECT cm.name, cm.address

FROM Client_master cm

INNER JOIN Sales_order so ON cm.client_no = so.client_no

WHERE so.order_no = 1001;


-- Find the clients names who have placed orders before the month of May’96

-- Here, we display orders that is placed before March 12

SELECT DISTINCT cm.name 

FROM Client_master cm

INNER JOIN Sales_order so ON cm.client_no = so.client_no

WHERE so.order_date < '2024-03-12';


-- Find out if the product ‘1.44 Drive’ has been ordered by any client and print the client_no, name to whom it was sold

-- Here we display product 'Smartphone'

SELECT DISTINCT c.client_no, c.name

FROM Client_master c 

JOIN Sales_order so ON c.client_no = so.client_no

JOIN Sales_order_details sod ON so.order_no = sod.order_no

JOIN Product_master p ON sod.product_no = p.product_no

WHERE p.description = 'Smartphone';


-- Find the names of clients who have placed orders worth Rs. 10000 or more

SELECT DISTINCT cm.name 

FROM Client_master cm

JOIN Sales_order so ON cm.client_no = so.client_no 

JOIN (

    SELECT order_no, SUM(qty_ordered * product_rate) AS total_order_value

    FROM Sales_order_details

    GROUP BY order_no

) sod ON so.order_no = sod.order_no

WHERE sod.total_order_value >= 10000;


-- Retrieve all the orders placed by a client named ‘Rahul Desai’ from the sales_order table

SELECT * 

FROM Sales_order so

JOIN Client_master cm ON so.client_no = cm.client_no 

WHERE cm.name = 'Prajapati';


-- Find out all the products that are not being sold from the product_master table, based on the

-- Products actually sold as shown in the sales_order_details table.

-- Here we retrieve data through salesman_no '102'

SELECT DISTINCT cm.name, cm.address, cm.city 

FROM Client_master cm

JOIN Sales_order so ON cm.client_no = so.client_no

WHERE so.salesman_no = '102';


-- Retrieve the product numbers, their description and the total quantity ordered for each product

SELECT p.product_no, p.description, SUM(sod.qty_ordered) AS total_quantity_ordered 

FROM Product_master p 

JOIN sales_order_details sod ON p.product_no = sod.product_no

GROUP BY p.product_no, p.description;



Problem 8

 Write a block to display sum of digits of a three digit number


SET SERVEROUTPUT ON;


DECLARE

    v_number NUMBER := 345; -- Change this to your desired three-digit number

    v_sum NUMBER := 0;

BEGIN

    IF v_number < 100 OR v_number > 999 THEN

        DBMS_OUTPUT.PUT_LINE('Invalid input. Please enter a three-digit number.');

    ELSE

        v_sum := TRUNC(MOD(v_number, 10)) + TRUNC(MOD(v_number / 10, 10)) + TRUNC(MOD(v_number / 100, 10));

        DBMS_OUTPUT.PUT_LINE('Sum of digits of ' || v_number || ' is: ' || v_sum);

    END IF;

END;

/


Write a block to display square of 1 to 10 (Problem 8 & 9)


SET SERVEROUTPUT ON;


DECLARE

    v_square NUMBER;

BEGIN

    FOR i IN 1..10 LOOP

        v_square := i * i;

        DBMS_OUTPUT.PUT_LINE('Square of ' || i || ' is: ' || v_square);

    END LOOP;

END;

/




Write a block to display Fibonacci series upto 8th term (start with 0,1)



SET SERVEROUTPUT ON;


DECLARE

    v_fib1 NUMBER := 0;

    v_fib2 NUMBER := 1;

    v_next_term NUMBER;

BEGIN

    DBMS_OUTPUT.PUT_LINE('Fibonacci series up to 8th term:');

    DBMS_OUTPUT.PUT_LINE(v_fib1); -- Display the first term

    DBMS_OUTPUT.PUT_LINE(v_fib2); -- Display the second term

    

    FOR i IN 3..8 LOOP -- Start the loop from the 3rd term up to the 8th term

        v_next_term := v_fib1 + v_fib2;

        DBMS_OUTPUT.PUT_LINE(v_next_term); -- Display the next term

        v_fib1 := v_fib2; -- Update v_fib1 with the value of v_fib2

        v_fib2 := v_next_term; -- Update v_fib2 with the value of the next term

    END LOOP;

END;

/




Problem 10

Write a function to find factorial of a number


CREATE OR REPLACE FUNCTION factorial(n IN NUMBER) RETURN NUMBER IS

    result NUMBER := 1;

BEGIN

    IF n < 0 THEN

        RETURN -1; -- Factorial of negative number is not defined

    ELSIF n = 0 THEN

        RETURN 1; -- Factorial of 0 is 1

    ELSE

        FOR i IN 1..n LOOP

            result := result * i;

        END LOOP;

        RETURN result;

    END IF;

END;

/

-- Call the function with 'n' parameter to get factorial

DECLARE

    num NUMBER := 5;

    fact NUMBER;

BEGIN

    fact := factorial(num);

    DBMS_OUTPUT.PUT_LINE('Factorial of ' || num || ' is: ' || fact);

END;

/







Create table emp (id,name,salary) and insert 3 records in it. 

Write a function find average salary from emp table


CREATE TABLE emp1 (

    emp_id numeric(4),

    emp_name varchar(10),

    salary numeric(8, 2)

);


INSERT INTO emp1

VALUES 

    (1, 'chirag', 20000),

    (2, 'Prajapati', 50000),

    (3, 'Joel', 60000),

    (4, 'Maddy', 80000),

    (5, 'Saad', 80000);


SELECT * FROM emp1;


CREATE FUNCTION get_avg_salary()

RETURNS numeric AS

$$

BEGIN

    RETURN (SELECT AVG(salary) FROM emp1)::numeric(8,2);

END;

$$

LANGUAGE plpgsql;


SELECT get_avg_salary();



Problem 10

1. Create table emp (id,name,salary) and insert 3 records in it. 

2. Write a row level trigger that would fire before insert/ update/delete operations performed on emp table, not allowing these operations and display the appropriate message.

3. Write a row level trigger that would fire after insert/update/delete operations performed on emp table displaying date on which data manipulation performed.


CREATE TABLE emp1 (

    emp_id numeric(4),

    emp_name varchar(10),

    salary numeric(8, 2)

);


INSERT INTO emp1

VALUES 

    (1, 'chirag', 20000),

    (2, 'Prajapati', 50000),

    (3, 'Joel', 60000),

    (4, 'Maddy', 80000),

    (5, 'Saad', 80000);


SELECT * FROM emp1;


-- Create the trigger function to prevent operations and display message

CREATE OR REPLACE FUNCTION prevent_emp1_operations()

RETURNS TRIGGER AS

$$

BEGIN

    RAISE EXCEPTION 'Operation not allowed on emp1 table';

END;

$$

LANGUAGE plpgsql;


-- Create the trigger to fire before insert/update/delete operations

CREATE TRIGGER prevent_emp1_operations_trigger

BEFORE INSERT OR UPDATE OR DELETE ON emp1

FOR EACH ROW

EXECUTE FUNCTION prevent_emp1_operations();


-- Create the trigger function to display manipulation date

CREATE OR REPLACE FUNCTION display_manipulation_date_emp1()

RETURNS TRIGGER AS

$$

BEGIN

    RAISE NOTICE 'Data manipulation performed on emp1 table on %', current_date;

    RETURN NEW;

END;

$$

LANGUAGE plpgsql;


-- Create the trigger to fire after insert/update/delete operations

CREATE TRIGGER display_manipulation_date_emp1_trigger

AFTER INSERT OR UPDATE OR DELETE ON emp1

FOR EACH ROW

EXECUTE FUNCTION display_manipulation_date_emp1();



Problem 11











Problem 12


Problem 13

 

Comments

Post a Comment