Sem-2 Practical Practice Question3
Q.3 Create following tables with proper constraints and insert at least 5 records in each.
dealer(dno, dname)
item(item_id, item_name)
order(ono, item_id, dno, order_date, amount)
Perform following tasks:
1. Display order detail with dealer name.
2. Display total amount of each dealer.
3. Display order date, dealer name whose amount is more than 600000.
4. Display ordered detail item wise in descending order.
5. Display order detail of “01/01/2020” date.
Write PL/SQL block to display the order and item detail of given order number if dealer no is greater than 105.
SOLUTION .
✅ 1. Create Tables with Constraints.
-- Dealer Table
CREATE TABLE dealer (
dno INT PRIMARY KEY,
dname VARCHAR(50) NOT NULL
);
-- Item Table
CREATE TABLE item (
item_id INT PRIMARY KEY,
item_name VARCHAR(50) NOT NULL
);
-- Order Table
CREATE TABLE orders (
ono INT PRIMARY KEY,
item_id INT,
dno INT,
order_date DATE,
amount DECIMAL(10,2),
FOREIGN KEY (item_id) REFERENCES item(item_id),
FOREIGN KEY (dno) REFERENCES dealer(dno)
);
✅ 2. Insert Sample Data.
INSERT INTO dealer VALUES(1, 'Alpha Traders');
INSERT INTO dealer VALUES(2, 'Beta Distributors');
INSERT INTO dealer VALUES(3, 'Gamma Supplies');
INSERT INTO dealer VALUES(4, 'Delta Corp');
INSERT INTO dealer VALUES(5, 'Omega Dealers');
INSERT INTO item VALUES(101, 'Laptop');
INSERT INTO item VALUES(102, 'Printer');
INSERT INTO item VALUES(103, 'Monitor');
INSERT INTO item VALUES(104, 'Keyboard');
INSERT INTO item VALUES(105, 'Mouse');
INSERT INTO orders VALUES (1001, 101, 1, '01-Jan-2020', 750000);
INSERT INTO orders VALUES (1002, 102, 2, '03-Nov-2020', 250000);
INSERT INTO orders VALUES (1003, 103, 3, '04-Dec-2020', 120000);
INSERT INTO orders VALUES (1004, 104, 1, '10-Aug-2020', 300000);
INSERT INTO orders VALUES (1005, 105, 4, '06-Jan-2020', 800000);
✅ 3. Queries.
1️⃣ Display order detail with dealer name
SELECT o.ono, o.item_id, o.order_date, o.amount, d.dname
FROM orders o
JOIN dealer d ON o.dno = d.dno;
2️⃣ Display total amount of each dealer.
SELECT d.dname, SUM(o.amount) AS total_amount
FROM orders o
JOIN dealer d ON o.dno = d.dno
GROUP BY d.dname;
3️⃣ Display order date, dealer name whose amount is more than 600000.
SELECT o.order_date, d.dname, o.amount
FROM orders o
JOIN dealer d ON o.dno = d.dno
WHERE o.amount > 600000;
4️⃣ Display ordered detail item-wise in descending order.
SELECT i.item_name, o.ono, o.order_date, o.amount
FROM orders o
JOIN item i ON o.item_id = i.item_id
ORDER BY i.item_name DESC;
5️⃣ Display order detail of “01/01/2020” date.
SELECT * FROM orders
WHERE order_date = '01-Jan-2020';
Write PL/SQL block to display the order and item detail of given order number if dealer no is greater than 105.
Solution .
DECLARE
v_ono orders.ono%TYPE := &Enter_Order_No; -- Take order number as input
v_item_id orders.item_id%TYPE;
v_dno orders.dno%TYPE;
v_order_date orders.order_date%TYPE;
v_amount orders.amount%TYPE;
v_item_name item.item_name%TYPE;
BEGIN
-- Fetch order details
SELECT item_id, dno, order_date, amount
INTO v_item_id, v_dno, v_order_date, v_amount
FROM orders
WHERE ono = v_ono;
-- Check dealer number condition
IF v_dno > 105 THEN
-- Fetch item name
SELECT item_name INTO v_item_name
FROM item
WHERE item_id = v_item_id;
-- Display result
DBMS_OUTPUT.PUT_LINE('Order No : ' || v_ono);
DBMS_OUTPUT.PUT_LINE('Dealer No : ' || v_dno);
DBMS_OUTPUT.PUT_LINE('Item ID : ' || v_item_id);
DBMS_OUTPUT.PUT_LINE('Item Name : ' || v_item_name);
DBMS_OUTPUT.PUT_LINE('Order Date : ' || TO_CHAR(v_order_date, 'DD-MM-YYYY'));
DBMS_OUTPUT.PUT_LINE('Amount : ' || v_amount);
ELSE
DBMS_OUTPUT.PUT_LINE('Dealer number is not greater than 105. No output.');
END IF;
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('No order found with the given order number.');
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('An error occurred: ' || SQLERRM);
END;
