Section A: Multiple Choice Questions (12 x 0.5 = 6 marks)

Q1. Database & Table Creation (2.5 marks)
Create a database CollegeDB.
Inside it, create a table Students with the following structure:

    student_id (INT, Primary Key, Auto Increment (must start with 11211)
    name (VARCHAR(50), NOT NULL)
    age (INT, must be greater than 16)
    district (VARCHAR(30))
    gender ((‘M’,’F’,’O’))
    marks (DECIMAL(5,2))

-- 1. Create a database CollegeDB. 
CREATE DATABASE IF NOT EXISTS CollegeDB;

USE CollegeDB;

-- Inside it, create a table Students with the following structure:
CREATE TABLE IF NOT EXISTS Students(
	student_id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50) NOT NULL,
    age INT,
    district VARCHAR(30),
    gender ENUM('M', 'F', 'O'),
    marks DECIMAL(5,2),
    
    CHECK (age > 16)
) AUTO_INCREMENT = 11211;

Q2. Data Insertion (1.5 marks)
Insert the below 5 records into the Students table.

student_id name age district gender marks
1 Sita 18 Kathmandu Female 88.50
2 Ram 20 Bhaktapur Female 92.00
4 Bikash 21 Pokhara Male 81.25
5 Prakash 22 Chitwan Male 67.50 
-- 2. Insert the below 5 records into the Students table.
INSERT INTO Students(name, age, district, gender, marks) VALUES
('Sita', 18, 'Kathmandu', 'F', 88.50),
('Ram', 20, 'Lalitpur', 'M', 75.00),
('Asha', 19, 'Bhaktapur', 'F', 92.00),
('Bikash', 21, 'Pokhara', 'M', 81.25),
('Prakash', 22, 'Chitwan', 'M', 67.50);

Q3. Debugging – Table Creation Error (1.5 marks)
The following code is incorrect:

CREATE TABLE Teachers (
teacher-id INT PRIMARY KEY AUTO_INCREMENT,
tname VARCHAR2(30) NOT NULL,
salary FLOAT,
department VCHAR(20)
);

Debugged:

-- 3. Debug: fix the error
CREATE TABLE Teachers(
	teacher_id INT PRIMARY KEY AUTO_INCREMENT, 
	tname VARCHAR(30) NOT NULL, 
	salary FLOAT,
	department VARCHAR(20)
);

Identify and correct the errors.

Q4. Show all students who scored more than 80, sorted by marks (descending). (1.5 marks)

-- 4. Show all students who scored more than 80, sorted by marks (descending).
SELECT *
FROM Students
WHERE marks > 80
ORDER BY marks DESC;

Q5. Find the average marks grouped by gender. (2.5 marks)

-- 5. Find the average marks grouped by gender
SELECT gender, AVG(marks) AS avg_marks
FROM Students
GROUP BY gender;

Q6. Show only the genders with average marks greater than 80. (2 marks)

-- 6. Show only the genders with average marks greater than 80.
SELECT gender, AVG(marks) AS avg_marks
FROM Students
WHERE marks > 80
GROUP BY gender;

Q7. a) Display the top 2 students with highest marks. (1 marks)

-- 7 (a) Display the top 2 students with highest marks
SELECT *
FROM Students
ORDER BY marks DESC
LIMIT 2;

Q7. b) Display students whose marks are greater than the average marks of all students. (2.5 marks)

-- 7 (b) Display students whose marks are greater than the average marks of all students.
WITH average_marks AS(
	SELECT AVG(marks) AS avg_marks
	FROM Students
)
SELECT s.*
FROM Students s, average_marks a
WHERE s.marks > a.avg_marks;

Q8. (4 marks)
Create another table:

CREATE TABLE Courses (
    course_id INT PRIMARY KEY,
    course_name VARCHAR(50),
    student_id INT,
    FOREIGN KEY (student_id) REFERENCES Students(student_id)
);
course_id course_name student_id
101 BSC CSIT 1
102 BBA 2
103 BSC CSIT 1
104 BHM 4

Display student name with course enrolled.

-- 8. Display student name with course enrolled.
CREATE TABLE Courses(
	course_id INT PRIMARY KEY, 
    course_name VARCHAR(50), 
    student_id INT, 
    
    FOREIGN KEY (student_id) REFERENCES Students(student_id) 
);

INSERT INTO Courses(course_id, course_name, student_id) VALUES
(101, 'BSc CSIT', 11211),
(102, 'BBA', 11212),
(103, 'BSc CSIT', 11213),
(104, 'BHM', 11214);

SELECT s.student_id, s.name, c.course_name AS course_enrolled
FROM Students s
RIGHT JOIN Courses c 
	ON s.student_id = c.student_id;

Q9. Debugging – Wrong Query (1 marks)
Given query:

SELECT name, SUM(marks)
FROM Students
WHERE marks > 50
ORDERED BY marks
GROUPED BY name;

Debugged:

-- 9. Debug the wrong query
SELECT name, SUM(marks) AS sum_marks
FROM Students 
WHERE marks > 50
GROUP BY name
ORDER BY sum_marks;

Q10. (5 marks)
Display name and marks of students:

  • Who are enrolled in at least one course,
  • Have marks greater than the average marks of their gender.

Expected Output:

name gender marks
Asha Female 92.00 
WITH gender_avg AS (
    SELECT gender, AVG(marks) AS avg_marks
    FROM Students
    GROUP BY gender
)
SELECT DISTINCT s.name, s.marks
FROM Students s
JOIN gender_avg g ON s.gender = g.gender
JOIN Courses c ON s.student_id = c.student_id
WHERE s.marks > g.avg_marks;