Sem-2 Practical Practice Question3
Q.3 Create following tables with proper constraints and insert atleast 5 records in each. Table
Course (course_id, course_ name)
Student(sno,sname,city,mobile,birth_date,course_id)
Perform following tasks:
1. Display student’s detail with course name.
2. Display student’s detail who belongs to “ BCA” course.
3. Display total numbers of student’s course_name wise.
4. Display student’s detail as per city in ascending order along with course detail.
5. Display student’s detail whose birth_ date is before 2000.
Write PL/SQL block to display the student detail of given Sno(student no) if student belongs to “Surat city.
SOLUTION .
✅ Step 1: Create Tables.
-- Course Table
CREATE TABLE Course (
course_id INT PRIMARY KEY,
course_name VARCHAR(50) NOT NULL
);
-- Student Table
CREATE TABLE Student1 (
sno INT PRIMARY KEY,
sname VARCHAR(50),
city VARCHAR(50),
mobile VARCHAR(15),
birth_date DATE,
course_id INT,
FOREIGN KEY (course_id) REFERENCES Course(course_id)
);
✅ Step 2: Insert Records
-- Insert into Course
INSERT INTO Course VALUES(1, 'BCA');
INSERT INTO Course VALUES(2, 'MCA');
INSERT INTO Course VALUES(3, 'BBA');
INSERT INTO Course VALUES(4, 'MBA');
INSERT INTO Course VALUES(5, 'B.Sc');
-- Insert into Student
INSERT INTO Student1 VALUES(101, 'Riya Shah', 'Surat', '9998844777', '14-May-1999', 1);
INSERT INTO Student1 VALUES(102, 'Amit Patel', 'Ahmedabad', '9876543210', '03-Jun-2001', 2);
INSERT INTO Student1 VALUES(103, 'Sneha Mehta', 'Vadodara', '9876554321', '25-May-1998', 1);
INSERT INTO Student1 VALUES(104, 'Ravi Desai', 'Rajkot', '9123456789', '07-Sep-2002', 3);
INSERT INTO Student1 VALUES(105, 'Neha Joshi', 'Surat', '9000011111', '01-Jan-1997', 4);
✅ Step 3: Required Queries.
1️⃣ Student detail with course name
SELECT s.*, c.course_name
FROM Student1 s
JOIN Course c ON s.course_id = c.course_id;
2️⃣ Student detail for “BCA” course.
SELECT s.*
FROM Student1 s
JOIN Course c ON s.course_id = c.course_id
WHERE c.course_name = 'BCA';
3️⃣ Total number of students course-wise.
SELECT c.course_name, COUNT(*) AS total_students
FROM Student1 s
JOIN Course c ON s.course_id = c.course_id
GROUP BY c.course_name;
4️⃣ Student detail city-wise in ascending order
SELECT s.*, c.course_name
FROM Student1 s
JOIN Course c ON s.course_id = c.course_id
ORDER BY s.city ASC;
5️⃣ Student detail with birth date before 2000
SELECT *
FROM Student1
WHERE birth_date < '01-Jan-2000';
✅ Step 4: PL/SQL Block for Given Student No (If city = ‘Surat’).
DECLARE
v_sno Student1.sno%TYPE := &Enter_Student_No;
v_sname Student1.sname%TYPE;
v_city Student1.city%TYPE;
v_mobile Student1.mobile%TYPE;
v_birthdate Student1.birth_date%TYPE;
v_course_id Student1.course_id%TYPE;
BEGIN
-- Fetch student detail
SELECT sname, city, mobile, birth_date, course_id
INTO v_sname, v_city, v_mobile, v_birthdate, v_course_id
FROM Student
WHERE sno = v_sno;
IF LOWER(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('Birth Date : ' || TO_CHAR(v_birthdate, 'DD-MM-YYYY'));
DBMS_OUTPUT.PUT_LINE('Course ID : ' || v_course_id);
ELSE
DBMS_OUTPUT.PUT_LINE('Student does not belong to Surat.');
END IF;
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('No student found with the given Student No.');
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Error: ' || SQLERRM);
END;
