Skip to content

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

Paper Details

  • Subject: Core of Relational Database Management System (CRDMS)
  • Subject Code: 205
  • Set: A
  • 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: Course & Student Schema

Max Marks: 10

Create following tables with proper constraints, primary key. Insert at least 5 records in each table. - Course (course_id, course_name) - Student (sno, sname, city, mobile, birth_date, course_id)

1. Course Table Creation

Hint

Create the parent table first. course_id will be referenced by Student table.

View Solution & Output 
-- Create COURSE table
CREATE TABLE Course (
    course_id NUMBER(5) PRIMARY KEY,
    course_name VARCHAR2(50) NOT NULL
);

-- Output: Table 'Course' created.

2. Student Table with Foreign Key

View Solution & Output 
-- Create STUDENT table with Foreign Key
CREATE TABLE Student (
    sno NUMBER(5) PRIMARY KEY,
    sname VARCHAR2(50) NOT NULL,
    city VARCHAR2(30),
    mobile VARCHAR2(15),
    birth_date DATE,
    course_id NUMBER(5),
    CONSTRAINT fk_course_student 
        FOREIGN KEY (course_id) 
        REFERENCES Course(course_id)
);

-- Output: Table 'Student' created with FK to 'Course'.

3. Inserting Data

View Solution & Output 
-- Insert records into COURSE
INSERT INTO Course VALUES (101, 'BCA');
INSERT INTO Course VALUES (102, 'BBA');
INSERT INTO Course VALUES (103, 'MCA');
INSERT INTO Course VALUES (104, 'MBA');
INSERT INTO Course VALUES (105, 'B.Com');

-- Insert records into STUDENT
INSERT INTO Student VALUES (1, 'Rahul Sharma', 'Surat', '9876543210', '15-AUG-2000', 101);
INSERT INTO Student VALUES (2, 'Priya Patel', 'Mumbai', '9876543211', '20-JUL-2001', 102);
INSERT INTO Student VALUES (3, 'Amit Kumar', 'Surat', '9876543212', '10-JUN-2000', 101);
INSERT INTO Student VALUES (4, 'Sneha Gupta', 'Delhi', '9876543213', '05-MAY-2001', 103);
INSERT INTO Student VALUES (5, 'Vikram Singh', 'Surat', '9876543214', '25-APR-2000', 101);

COMMIT;

-- Verify data
SELECT * FROM Course;
SELECT * FROM Student;

Q1B: Display Student from Surat

Max Marks: 10

Write a PL/SQL block to display the student detail of given sno if student belongs to "Surat" city.

Hint

Accept sno as input, check if student's city is 'Surat', then display details.

View Solution & Output 
SET SERVEROUTPUT ON;

DECLARE
    v_sno NUMBER := &sno;
    v_sname Student.sname%TYPE;
    v_city Student.city%TYPE;
    v_mobile Student.mobile%TYPE;
    v_birth_date Student.birth_date%TYPE;
    v_course_name Course.course_name%TYPE;
BEGIN
    -- Get student details with course name
    SELECT s.sname, s.city, s.mobile, s.birth_date, c.course_name
    INTO v_sname, v_city, v_mobile, v_birth_date, v_course_name
    FROM Student s
    JOIN Course c ON s.course_id = c.course_id
    WHERE s.sno = v_sno;

    -- Check if student belongs to Surat
    IF UPPER(v_city) = 'SURAT' THEN
        DBMS_OUTPUT.PUT_LINE('========================================');
        DBMS_OUTPUT.PUT_LINE('STUDENT DETAILS - SURAT CITY');
        DBMS_OUTPUT.PUT_LINE('========================================');
        DBMS_OUTPUT.PUT_LINE('Student No: ' || v_sno);
        DBMS_OUTPUT.PUT_LINE('Name: ' || v_sname);
        DBMS_OUTPUT.PUT_LINE('City: ' || v_city);
        DBMS_OUTPUT.PUT_LINE('Mobile: ' || v_mobile);
        DBMS_OUTPUT.PUT_LINE('Birth Date: ' || v_birth_date);
        DBMS_OUTPUT.PUT_LINE('Course: ' || v_course_name);
        DBMS_OUTPUT.PUT_LINE('========================================');
    ELSE
        DBMS_OUTPUT.PUT_LINE('Student ' || v_sno || ' does not belong to Surat city.');
        DBMS_OUTPUT.PUT_LINE('Student city: ' || v_city);
    END IF;

EXCEPTION
    WHEN NO_DATA_FOUND THEN
        DBMS_OUTPUT.PUT_LINE('Error: Student with sno ' || v_sno || ' not found.');
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('Error: ' || SQLERRM);
END;
/
Alternative: Using Explicit Cursor with Parameter

You can also use a parameterized cursor to check student city:

SET SERVEROUTPUT ON;

DECLARE
    v_sno NUMBER := &sno;

    -- Parameterized cursor
    CURSOR c_student(p_sno NUMBER) IS
        SELECT s.sname, s.city, s.mobile, s.birth_date, c.course_name
        FROM Student s
        JOIN Course c ON s.course_id = c.course_id
        WHERE s.sno = p_sno;

    v_sname Student.sname%TYPE;
    v_city Student.city%TYPE;
    v_mobile Student.mobile%TYPE;
    v_birth_date Student.birth_date%TYPE;
    v_course_name Course.course_name%TYPE;
BEGIN
    -- Open cursor with parameter
    OPEN c_student(v_sno);
    FETCH c_student INTO v_sname, v_city, v_mobile, v_birth_date, v_course_name;

    IF c_student%FOUND THEN
        IF UPPER(v_city) = 'SURAT' THEN
            DBMS_OUTPUT.PUT_LINE('Student No: ' || v_sno);
            DBMS_OUTPUT.PUT_LINE('Name: ' || v_sname);
            DBMS_OUTPUT.PUT_LINE('City: ' || v_city);
            DBMS_OUTPUT.PUT_LINE('Mobile: ' || v_mobile);
            DBMS_OUTPUT.PUT_LINE('Course: ' || v_course_name);
        ELSE
            DBMS_OUTPUT.PUT_LINE('Student does not belong to Surat city.');
        END IF;
    ELSE
        DBMS_OUTPUT.PUT_LINE('Student not found.');
    END IF;

    CLOSE c_student;
END;
/

Q2: Viva Preparation

Max Marks: 5

Potential Viva Questions
  1. Q: What is a FOREIGN KEY constraint?
  2. A: A constraint that establishes a link between two tables by referencing the primary key of another table.
  3. Q: What is the purpose of UPPER() function?
  4. A: It converts a string to uppercase for case-insensitive comparison.
  5. Q: What is the difference between %TYPE and %ROWTYPE?
  6. A: %TYPE references a single column's data type, while %ROWTYPE references an entire row structure.
  7. Q: What is the purpose of parameterized cursor?
  8. A: It allows passing values to the cursor at runtime to filter results dynamically.
  9. Q: What is an implicit cursor?
  10. A: A cursor automatically created by Oracle for DML operations like INSERT, UPDATE, DELETE.

Common Pitfalls

  • Case Sensitivity: Always use UPPER() or LOWER() for city comparison to handle different cases.
  • Insert Order: Insert into Course first, then Student to maintain referential integrity.
  • Date Format: Ensure proper date format when inserting birth_date.

Quick Navigation

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

Last Updated: April 2024