BCSL-034 Database Management System LabBACHELOR OF COMPUTER APPLICATIONS (BCA)
Term-End Practical Examination
December , 2024 Time : 1 Hour Maximum Marks:50
Note: There are two questions in this paper carrying 20 marks each. Answer them both. Rest 10 marks are for viva-voce.
1. Create a database name 'StudentInfoDB'. Design tables STUDENT(StudentID, Name, Age, Gender, email address,mobile) and ENROLS_IN(EnrolID,StudentID,CourseID,Semester). Select approproate data types for each of hte fields. Identify the primary key etc. Input meaningful data for at least 10 records for each table. (20 marks) The End
Note: There are two questions in this paper carrying 20 marks each. Answer them both. Rest 10 marks are for viva-voce.
1. Create a database name 'StudentInfoDB'. Design tables STUDENT(StudentID, Name, Age, Gender, email address,mobile) and ENROLS_IN(EnrolID,StudentID,CourseID,Semester). Select approproate data types for each of hte fields. Identify the primary key etc. Input meaningful data for at least 10 records for each table. (20 marks)
-- Create the database
CREATE DATABASE StudentInfoDB;
USE StudentInfoDB;
-- Create STUDENT table
CREATE TABLE STUDENT (
StudentID INT PRIMARY KEY AUTO_INCREMENT,
Name VARCHAR(100) NOT NULL,
Age INT CHECK (Age >= 10 AND Age <= 25),
Gender ENUM('Male', 'Female', 'Other') NOT NULL,
EmailAddress VARCHAR(100) UNIQUE NOT NULL,
Mobile VARCHAR(15) UNIQUE NOT NULL
);
-- Create ENROLS_IN table
CREATE TABLE ENROLS_IN (
EnrolID INT PRIMARY KEY AUTO_INCREMENT,
StudentID INT,
CourseID VARCHAR(10),
Semester VARCHAR(10),
FOREIGN KEY (StudentID) REFERENCES STUDENT(StudentID)
);
-- Insert sample data into STUDENT table
INSERT INTO STUDENT (Name, Age, Gender, EmailAddress, Mobile) VALUES
('Ram Sharma', 16, 'Male', 'ram.sharma@example.com', '9801234567'),
('Sita Thapa', 17, 'Female', 'sita.thapa@example.com', '9812345678'),
('Krishna Bhandari', 18, 'Male', 'krishna.b@example.com', '9823456789'),
('Gita Koirala', 19, 'Female', 'gita.k@example.com', '9834567890'),
('Hari Magar', 20, 'Male', 'hari.m@example.com', '9845678901'),
('Pooja Tamang', 18, 'Female', 'pooja.t@example.com', '9856789012'),
('Sunil Gurung', 21, 'Male', 'sunil.g@example.com', '9867890123'),
('Anjali Rai', 22, 'Female', 'anjali.r@example.com', '9878901234'),
('Ramesh Lama', 19, 'Male', 'ramesh.l@example.com', '9889012345'),
('Sujata Shrestha', 20, 'Female', 'sujata.s@example.com', '9890123456');
-- Insert sample data into ENROLS_IN table
INSERT INTO ENROLS_IN (StudentID, CourseID, Semester) VALUES
(1, 'CS101', 'Spring2025'),
(2, 'CS102', 'Spring2025'),
(3, 'CS103', 'Fall2024'),
(4, 'CS104', 'Fall2024'),
(5, 'CS105', 'Spring2025'),
(6, 'CS101', 'Fall2024'),
(7, 'CS102', 'Spring2025'),
(8, 'CS103', 'Fall2024'),
(9, 'CS104', 'Spring2025'),
(10, 'CS105', 'Fall2024');
-- View the tables
SELECT * FROM STUDENT;
SELECT * FROM ENROLS_IN;
2. For the above tables created in Q1, answer the following queries using SQL: (4×5=20 marks)
(a) List all the students along with their details.
(b) Display students by age group
(c) Identify all students with CourseID=C012
(d) List all the 'Female" students.