A). Create SQL table with primary key and foreign key with insert 5 records. Distributor : ( Dno,d_name,phno) Item : ( Item_no,Dno,item_name,qty,rate )

B). Pl/sql block that accept distributor no and display its details with item details be distributes.

A) Create tables with PK, FK and insert records

Create Distributor table

CREATE TABLE Distributor (
    Dno NUMBER PRIMARY KEY,
    d_name VARCHAR2(50),
    phno VARCHAR2(15)
);

Create Item table (with Foreign Key)

CREATE TABLE Item (
    Item_no NUMBER PRIMARY KEY,
    Dno NUMBER,
    item_name VARCHAR2(50),
    qty NUMBER,
    rate NUMBER,
    CONSTRAINT fk_distributor
    FOREIGN KEY (Dno)
    REFERENCES Distributor(Dno)
);

Insert 5 records into Distributor

INSERT INTO Distributor VALUES (1, 'Raj Traders', '9876543210');
INSERT INTO Distributor VALUES (2, 'Shiv Supplies', '9123456780');
INSERT INTO Distributor VALUES (3, 'Om Distributors', '9988776655');
INSERT INTO Distributor VALUES (4, 'Krishna Agency', '9090909090');
INSERT INTO Distributor VALUES (5, 'Ganesh Traders', '9111222333');

Insert 5 records into Item

INSERT INTO Item VALUES (101, 1, 'Rice', 50, 60);
INSERT INTO Item VALUES (102, 2, 'Wheat', 40, 55);
INSERT INTO Item VALUES (103, 1, 'Sugar', 30, 45);
INSERT INTO Item VALUES (104, 3, 'Oil', 20, 120);
INSERT INTO Item VALUES (105, 4, 'Salt', 25, 20);

B) PL/SQL Block

Accept distributor number and display distributor and item details

SET SERVEROUTPUT ON;DECLARE
    v_dno Distributor.Dno%TYPE := &Enter_Distributor_No;
    
    -- Distributor details
    v_name Distributor.d_name%TYPE;
    v_phno Distributor.phno%TYPE;BEGIN
    -- Fetch distributor details
    SELECT d_name, phno
    INTO v_name, v_phno
    FROM Distributor
    WHERE Dno = v_dno;    DBMS_OUTPUT.PUT_LINE('Distributor Details:');
    DBMS_OUTPUT.PUT_LINE('Dno: ' || v_dno);
    DBMS_OUTPUT.PUT_LINE('Name: ' || v_name);
    DBMS_OUTPUT.PUT_LINE('Phone: ' || v_phno);    DBMS_OUTPUT.PUT_LINE(' ');
    DBMS_OUTPUT.PUT_LINE('Items Supplied:');    -- Loop through items
    FOR rec IN (
        SELECT Item_no, item_name, qty, rate
        FROM Item
        WHERE Dno = v_dno
    )
    LOOP
        DBMS_OUTPUT.PUT_LINE(
            'Item No: ' || rec.Item_no ||
            ', Name: ' || rec.item_name ||
            ', Qty: ' || rec.qty ||
            ', Rate: ' || rec.rate
        );
    END LOOP;EXCEPTION
    WHEN NO_DATA_FOUND THEN
        DBMS_OUTPUT.PUT_LINE('No distributor found with given number.');
END;
/