Skip to content

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

Paper Details

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

Max Marks: 10

Create following tables with appropriate constraints and primary key. Insert at least 10 records in each table. - Stud_Member (Rollno, FirstName, MiddleName, LastName, DeptID, Semester, ContactNo, Gender) - Department (DeptID, DeptName)

1. Department Table Creation

Hint

Create the Department table first. DeptID will be referenced by Stud_Member.

View Solution & Output 
-- Create DEPARTMENT table
CREATE TABLE Department (
    DeptID NUMBER(5) PRIMARY KEY,
    DeptName VARCHAR2(50) NOT NULL
);

-- Output: Table 'Department' created.

2. Stud_Member Table with Foreign Key

View Solution & Output 
-- Create STUD_MEMBER table with Foreign Key
CREATE TABLE Stud_Member (
    Rollno NUMBER(5) PRIMARY KEY,
    FirstName VARCHAR2(30) NOT NULL,
    MiddleName VARCHAR2(30),
    LastName VARCHAR2(30) NOT NULL,
    DeptID NUMBER(5),
    Semester NUMBER(1) CHECK (Semester BETWEEN 1 AND 8),
    ContactNo VARCHAR2(15),
    Gender VARCHAR2(10) CHECK (Gender IN ('Male', 'Female', 'Other')),
    CONSTRAINT fk_dept_student 
        FOREIGN KEY (DeptID) 
        REFERENCES Department(DeptID)
);

-- Output: Table 'Stud_Member' created with FK to 'Department'.

3. Inserting Data

View Solution & Output 
-- Insert records into DEPARTMENT
INSERT INTO Department VALUES (10, 'Computer Science');
INSERT INTO Department VALUES (20, 'Information Technology');
INSERT INTO Department VALUES (30, 'Electronics');
INSERT INTO Department VALUES (40, 'Mechanical');
INSERT INTO Department VALUES (50, 'Civil');

-- Insert records into STUD_MEMBER
INSERT INTO Stud_Member VALUES (101, 'Rahul', 'Kumar', 'Sharma', 10, 4, '9876543210', 'Male');
INSERT INTO Stud_Member VALUES (102, 'Priya', '', 'Patel', 20, 2, '9876543211', 'Female');
INSERT INTO Stud_Member VALUES (103, 'Amit', 'Singh', 'Verma', 10, 4, '9876543212', 'Male');
INSERT INTO Stud_Member VALUES (104, 'Sita', 'Devi', 'Gupta', 30, 6, '9876543213', 'Female');
INSERT INTO Stud_Member VALUES (105, 'Ram', '', 'Joshi', 20, 2, '9876543214', 'Male');
INSERT INTO Stud_Member VALUES (106, 'Neha', 'Rani', 'Shah', 10, 4, '9876543215', 'Female');
INSERT INTO Stud_Member VALUES (107, 'Vikram', 'Pratap', 'Mehta', 40, 6, '9876543216', 'Male');
INSERT INTO Stud_Member VALUES (108, 'Anita', '', 'Desai', 50, 2, '9876543217', 'Female');
INSERT INTO Stud_Member VALUES (109, 'Sunil', 'Kumar', 'Rao', 30, 6, '9876543218', 'Male');
INSERT INTO Stud_Member VALUES (110, 'Mona', 'Ben', 'Patel', 10, 4, '9876543219', 'Female');

COMMIT;

-- Verify data
SELECT * FROM Department;
SELECT * FROM Stud_Member;

Q1B: Count Female Students by Department

Max Marks: 10

Write a PL/SQL block to display total number of female student in each department.

Hint

Use cursor or GROUP BY query to count female students per department. Use JOIN to get department names.

View Solution & Output 
SET SERVEROUTPUT ON;

DECLARE
    -- Cursor to count female students per department
    CURSOR c_female_count IS
        SELECT d.DeptName, COUNT(*) as female_count
        FROM Department d
        JOIN Stud_Member s ON d.DeptID = s.DeptID
        WHERE s.Gender = 'Female'
        GROUP BY d.DeptID, d.DeptName
        ORDER BY d.DeptName;
BEGIN
    DBMS_OUTPUT.PUT_LINE('========================================');
    DBMS_OUTPUT.PUT_LINE('FEMALE STUDENTS BY DEPARTMENT');
    DBMS_OUTPUT.PUT_LINE('========================================');
    DBMS_OUTPUT.PUT_LINE('Department | Female Count');
    DBMS_OUTPUT.PUT_LINE('----------------------------------------');

    FOR rec IN c_female_count LOOP
        DBMS_OUTPUT.PUT_LINE(RPAD(rec.DeptName, 25) || ' | ' || rec.female_count);
    END LOOP;

    DBMS_OUTPUT.PUT_LINE('========================================');

EXCEPTION
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('Error: ' || SQLERRM);
END;
/
Alternative: Using Explicit Cursor with OPEN/FETCH/CLOSE 
SET SERVEROUTPUT ON;

DECLARE
    CURSOR c_female_count IS
        SELECT d.DeptName, COUNT(*) as female_count
        FROM Department d
        JOIN Stud_Member s ON d.DeptID = s.DeptID
        WHERE s.Gender = 'Female'
        GROUP BY d.DeptID, d.DeptName
        ORDER BY d.DeptName;

    v_deptname Department.DeptName%TYPE;
    v_count NUMBER;
BEGIN
    OPEN c_female_count;

    DBMS_OUTPUT.PUT_LINE('Female Students by Department:');

    LOOP
        FETCH c_female_count INTO v_deptname, v_count;
        EXIT WHEN c_female_count%NOTFOUND;

        DBMS_OUTPUT.PUT_LINE(v_deptname || ': ' || v_count);
    END LOOP;

    CLOSE c_female_count;
END;
/
Alternative: Using Simple SQL with DBMS_OUTPUT 
BEGIN
    FOR rec IN (
        SELECT d.DeptName, COUNT(*) as cnt
        FROM Department d, Stud_Member s
        WHERE d.DeptID = s.DeptID AND s.Gender = 'Female'
        GROUP BY d.DeptName
    ) LOOP
        DBMS_OUTPUT.PUT_LINE(rec.DeptName || ' has ' || rec.cnt || ' female students');
    END LOOP;
END;
/

Q2: Viva Preparation

Max Marks: 5

Potential Viva Questions
  1. Q: What is the purpose of GROUP BY clause?
  2. A: To group rows with same values in specified columns for aggregate calculations.
  3. Q: What is the difference between COUNT(*) and COUNT(column)?
  4. A: COUNT() counts all rows, COUNT(column) counts non-NULL values in that column.*
  5. Q: What is RPAD function?
  6. A: It right-pads a string with spaces to achieve a specified total length.
  7. Q: Can we use aggregate functions without GROUP BY?
  8. A: Yes, but it returns a single value for the entire table.
  9. Q: What is the purpose of CHECK constraint on Gender?
  10. A: To ensure only valid gender values are inserted.

Common Pitfalls

  • GROUP BY: Include all non-aggregated columns in GROUP BY.
  • JOIN Condition: Ensure proper link between Department and Stud_Member.
  • Case Sensitivity: Gender comparison should match stored values.

Quick Navigation

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

Last Updated: April 2024