Skip to content

VNSGU BCA Sem 2: Core RDBMS (205) Practical Solutions - Set D

Paper Details

  • Subject: Core of Relational Database Management System (CRDMS)
  • Subject Code: 205
  • Set: D
  • Semester: 2
  • Month/Year: April 2024
  • Max Marks: 25
  • Time Recommendation: 2 Hours
  • Paper: View Paper | Download PDF

Questions & Solutions

All questions are compulsory

Q1A: Doctormaster & Patientmaster Schema

Max Marks: 10

Create following tables with appropriate constraints and primary key. Insert at least 10 records in each table. - Doctormaster (doct_id, name, address, specialization) - Patientmaster (Pat_id, pat_name, DOB, address, Phoneno, doct_id)

1. Doctormaster Table Creation

Hint

Create the doctor master table first. doct_id will be referenced by Patientmaster.

View Solution & Output 
-- Create DOCTORMASTER table
CREATE TABLE Doctormaster (
    doct_id NUMBER(5) PRIMARY KEY,
    name VARCHAR2(50) NOT NULL,
    address VARCHAR2(100),
    specialization VARCHAR2(30)
);

-- Output: Table 'Doctormaster' created.

2. Patientmaster Table with Foreign Key

View Solution & Output 
-- Create PATIENTMASTER table with Foreign Key
CREATE TABLE Patientmaster (
    Pat_id NUMBER(5) PRIMARY KEY,
    pat_name VARCHAR2(50) NOT NULL,
    DOB DATE,
    address VARCHAR2(100),
    Phoneno VARCHAR2(15),
    doct_id NUMBER(5),
    CONSTRAINT fk_doctor_patient 
        FOREIGN KEY (doct_id) 
        REFERENCES Doctormaster(doct_id)
);

-- Output: Table 'Patientmaster' created with FK to 'Doctormaster'.

3. Inserting Data

View Solution & Output 
-- Insert records into DOCTORMASTER
INSERT INTO Doctormaster VALUES (101, 'Dr. Sharma', 'Mumbai', 'Cardiology');
INSERT INTO Doctormaster VALUES (102, 'Dr. Patel', 'Ahmedabad', 'Neurology');
INSERT INTO Doctormaster VALUES (103, 'Dr. Gupta', 'Surat', 'Orthopedics');
INSERT INTO Doctormaster VALUES (104, 'Dr. Mehta', 'Delhi', 'Pediatrics');
INSERT INTO Doctormaster VALUES (105, 'Dr. Joshi', 'Pune', 'Dermatology');

-- Insert records into PATIENTMASTER
INSERT INTO Patientmaster VALUES (1, 'Rahul Kumar', '15-AUG-1990', 'Mumbai', '9876543210', 101);
INSERT INTO Patientmaster VALUES (2, 'Priya Shah', '20-JUL-1985', 'Ahmedabad', '9876543211', 102);
INSERT INTO Patientmaster VALUES (3, 'Amit Verma', '10-JUN-1992', 'Surat', '9876543212', 103);
INSERT INTO Patientmaster VALUES (4, 'Sita Patel', '05-MAY-1988', 'Delhi', '9876543213', 104);
INSERT INTO Patientmaster VALUES (5, 'Ram Gupta', '25-APR-1995', 'Pune', '9876543214', 105);
INSERT INTO Patientmaster VALUES (6, 'Neha Desai', '12-SEP-1991', 'Mumbai', '9876543215', 101);
INSERT INTO Patientmaster VALUES (7, 'Vikram Rao', '08-NOV-1986', 'Ahmedabad', '9876543216', 102);
INSERT INTO Patientmaster VALUES (8, 'Anita Sharma', '03-JAN-1993', 'Surat', '9876543217', 103);
INSERT INTO Patientmaster VALUES (9, 'Sunil Mehta', '18-MAR-1984', 'Delhi', '9876543218', 104);
INSERT INTO Patientmaster VALUES (10, 'Mona Joshi', '22-JUL-1990', 'Pune', '9876543219', 105);

COMMIT;

-- Verify data
SELECT * FROM Doctormaster;
SELECT * FROM Patientmaster;

Q1B: Display Patient Information PL/SQL

Max Marks: 10

Write a PL/SQL block to display patient information if available. If patient information is not available then print appropriate message.

Hint

Accept Pat_id as input. Use exception handling to catch NO_DATA_FOUND and display appropriate message.

View Solution & Output 
SET SERVEROUTPUT ON;

DECLARE
    v_pat_id NUMBER := &pat_id;
    v_pat_name Patientmaster.pat_name%TYPE;
    v_dob Patientmaster.DOB%TYPE;
    v_address Patientmaster.address%TYPE;
    v_phoneno Patientmaster.Phoneno%TYPE;
    v_doc_name Doctormaster.name%TYPE;
    v_specialization Doctormaster.specialization%TYPE;
BEGIN
    -- Get patient and doctor details
    SELECT p.pat_name, p.DOB, p.address, p.Phoneno,
           d.name, d.specialization
    INTO v_pat_name, v_dob, v_address, v_phoneno,
         v_doc_name, v_specialization
    FROM Patientmaster p
    JOIN Doctormaster d ON p.doct_id = d.doct_id
    WHERE p.Pat_id = v_pat_id;

    DBMS_OUTPUT.PUT_LINE('========================================');
    DBMS_OUTPUT.PUT_LINE('PATIENT INFORMATION FOUND');
    DBMS_OUTPUT.PUT_LINE('========================================');
    DBMS_OUTPUT.PUT_LINE('Patient ID: ' || v_pat_id);
    DBMS_OUTPUT.PUT_LINE('Patient Name: ' || v_pat_name);
    DBMS_OUTPUT.PUT_LINE('Date of Birth: ' || v_dob);
    DBMS_OUTPUT.PUT_LINE('Address: ' || v_address);
    DBMS_OUTPUT.PUT_LINE('Phone: ' || v_phoneno);
    DBMS_OUTPUT.PUT_LINE('----------------------------------------');
    DBMS_OUTPUT.PUT_LINE('Doctor: ' || v_doc_name);
    DBMS_OUTPUT.PUT_LINE('Specialization: ' || v_specialization);
    DBMS_OUTPUT.PUT_LINE('========================================');

EXCEPTION
    WHEN NO_DATA_FOUND THEN
        DBMS_OUTPUT.PUT_LINE('========================================');
        DBMS_OUTPUT.PUT_LINE('PATIENT INFORMATION NOT AVAILABLE');
        DBMS_OUTPUT.PUT_LINE('========================================');
        DBMS_OUTPUT.PUT_LINE('No patient found with ID: ' || v_pat_id);
        DBMS_OUTPUT.PUT_LINE('Please check the Patient ID and try again.');
        DBMS_OUTPUT.PUT_LINE('========================================');
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('Error: ' || SQLERRM);
END;
/
Alternative: Using Cursor to Check Existence First 
SET SERVEROUTPUT ON;

DECLARE
    v_pat_id NUMBER := &pat_id;

    CURSOR c_patient(p_id NUMBER) IS
        SELECT p.pat_name, p.DOB, p.address, p.Phoneno,
               d.name, d.specialization
        FROM Patientmaster p
        JOIN Doctormaster d ON p.doct_id = d.doct_id
        WHERE p.Pat_id = p_id;

    v_pat_name Patientmaster.pat_name%TYPE;
    v_dob Patientmaster.DOB%TYPE;
    v_address Patientmaster.address%TYPE;
    v_phoneno Patientmaster.Phoneno%TYPE;
    v_doc_name Doctormaster.name%TYPE;
    v_specialization Doctormaster.specialization%TYPE;
BEGIN
    OPEN c_patient(v_pat_id);
    FETCH c_patient INTO v_pat_name, v_dob, v_address, v_phoneno, v_doc_name, v_specialization;

    IF c_patient%FOUND THEN
        DBMS_OUTPUT.PUT_LINE('Patient Found: ' || v_pat_name);
        DBMS_OUTPUT.PUT_LINE('DOB: ' || v_dob);
        DBMS_OUTPUT.PUT_LINE('Doctor: ' || v_doc_name);
    ELSE
        DBMS_OUTPUT.PUT_LINE('Patient information not available for ID: ' || v_pat_id);
    END IF;

    CLOSE c_patient;
END;
/

Q2: Viva Preparation

Max Marks: 5

Potential Viva Questions
  1. Q: What is the purpose of exception handling in PL/SQL?
  2. A: To gracefully handle errors and prevent program termination.
  3. Q: What is NO_DATA_FOUND exception?
  4. A: A predefined exception raised when SELECT INTO returns no rows.
  5. Q: What is the difference between %FOUND and %NOTFOUND cursor attributes?
  6. A: %FOUND returns TRUE if a row was fetched, %NOTFOUND returns TRUE if no row was fetched.
  7. Q: Why use parameterized cursor?
  8. A: To pass parameters to the cursor query at runtime.
  9. Q: What is the advantage of using JOIN in this scenario?
  10. A: It retrieves patient and doctor information in a single query.

Common Pitfalls

  • NO_DATA_FOUND: Must handle this exception to display custom message.
  • Cursor Attribute: Use %FOUND to check if data exists before accessing.
  • Join Condition: Ensure proper ON clause to link patient with correct doctor.

Quick Navigation

Set Link
Set A Solutions
Set B Solutions
Set C Solutions
Set D Current Page
Set E Solutions
Set F Solutions

Last Updated: April 2024