DBMS SQL Assignment

Select the existing college_DB database and create all three tables.

USE college_DB;

CREATE TABLE IF NOT EXISTS student (
    student_id INT PRIMARY KEY,
    student_name VARCHAR(100) NOT NULL,
    gender VARCHAR(10),
    department VARCHAR(50),
    semester INT
);

CREATE TABLE IF NOT EXISTS course (
    course_id INT PRIMARY KEY,
    course_name VARCHAR(100) NOT NULL,
    credits INT
);

CREATE TABLE IF NOT EXISTS enrollment (
    student_id INT,
    course_id INT,
    marks INT,
    PRIMARY KEY (student_id, course_id),
    FOREIGN KEY (student_id) REFERENCES student(student_id),
    FOREIGN KEY (course_id) REFERENCES course(course_id)
);

Insert at least five records in each table.

INSERT INTO student
(student_id, student_name, gender, department, semester)
VALUES
(101, 'Rahul', 'Male', 'BCA', 3),
(102, 'Priya', 'Female', 'BCA', 3),
(103, 'Amit', 'Male', 'BCA', 3),
(104, 'Sneha', 'Female', 'BBA', 3),
(105, 'Rohan', 'Male', 'BCA', 3);

INSERT INTO course
(course_id, course_name, credits)
VALUES
(201, 'DBMS', 4),
(202, 'Java', 4),
(203, 'Python', 3),
(204, 'Data Structure', 4),
(205, 'Computer Networks', 3);

INSERT INTO enrollment
(student_id, course_id, marks)
VALUES
(101, 201, 85),
(102, 201, 90),
(103, 202, 78),
(104, 204, 88),
(105, 204, 95);

Display the total number of students.

SELECT COUNT(*) AS Total_Students
FROM student;

Display students in descending order of marks.

SELECT
    student.student_id,
    student.student_name,
    enrollment.marks
FROM student
JOIN enrollment
ON student.student_id = enrollment.student_id
ORDER BY enrollment.marks DESC;

Display all courses where students have no enrollment.

SELECT
    course.course_id,
    course.course_name
FROM course
LEFT JOIN enrollment
ON course.course_id = enrollment.course_id
WHERE enrollment.course_id IS NULL;

Find students who got the maximum marks.

SELECT
    student.student_id,
    student.student_name,
    enrollment.marks
FROM student
JOIN enrollment
ON student.student_id = enrollment.student_id
WHERE enrollment.marks = (
    SELECT MAX(marks)
    FROM enrollment
);

Find students enrolled in a particular course.

SELECT
    student.student_id,
    student.student_name,
    course.course_name
FROM student
JOIN enrollment
ON student.student_id = enrollment.student_id
JOIN course
ON enrollment.course_id = course.course_id
WHERE course.course_name = 'DBMS';

Display the number of students in each department.

SELECT
    department,
    COUNT(*) AS Total_Students
FROM student
GROUP BY department;

Display minimum marks of students.

SELECT MIN(marks) AS Minimum_Marks
FROM enrollment;

Display maximum marks obtained in each department.

SELECT
    student.department,
    MAX(enrollment.marks) AS Maximum_Marks
FROM student
JOIN enrollment
ON student.student_id = enrollment.student_id
GROUP BY student.department;

Find students who have obtained the lowest marks in a particular course.

SELECT
    student.student_id,
    student.student_name,
    enrollment.marks,
    course.course_name
FROM student
JOIN enrollment
ON student.student_id = enrollment.student_id
JOIN course
ON enrollment.course_id = course.course_id
WHERE course.course_name = 'DBMS'
AND enrollment.marks = (
    SELECT MIN(e.marks)
    FROM enrollment e
    JOIN course c
    ON e.course_id = c.course_id
    WHERE c.course_name = 'DBMS'
);