FINAL PROJECT__University Course Management System.txt

==============================
     1️⃣ Students Table
==============================

CREATE TABLE Students (
    StudentID INT PRIMARY KEY,
    FirstName VARCHAR(50),
    LastName VARCHAR(50),
    Email VARCHAR(100),
    BirthDate DATE,
    EnrollmentDate DATE
);

INSERT INTO Students (StudentID,FirstName,LastName,Email,BirthDate,EnrollmentDate)  VALUES 
(1,'John','Doe','john.doe@email.com','2000-01-15','2022-08-01'),
(2, 'Jane', 'Smith', 'jane.smith@email.com', '1999-05-25', '2021-08-01');

==============================
     2️⃣ Courses Table
==============================

CREATE TABLE Courses (
    CourseID INT PRIMARY KEY,
    CourseName VARCHAR(100),
    DepartmentID INT,
    Credits INT
);

INSERT INTO Courses (CourseID,CourseName,Department,Credits) VALUES
(101, 'Introduction to SQL', 1, 3),
(102, 'Data Structures', 2, 4);

==============================
    3️⃣ Instructors Table
==============================

CREATE TABLE Instructors (
    InstructorID INT PRIMARY KEY,
    FirstName VARCHAR(50),
    LastName VARCHAR(50),
    Email VARCHAR(100),
    DepartmentID INT,
    Salary DECIMAL(10,2)
);


INSERT INTO Instructors (InstructorID, FirstName,LastName,Email,DepartmentID,Salary) VALUES
(1, 'Alice', 'Johnson', 'alice.johnson@univ.com', 1, 80000),
(2, 'Bob', 'Lee', 'bob.lee@univ.com', 2, 75000);

==============================
   4️⃣ Enrollments Table
==============================

CREATE TABLE Enrollments (
    EnrollmentID INT PRIMARY KEY,
    StudentID INT,
    CourseID INT,
    EnrollmentDate DATE
);


INSERT INTO Enrollments(EnrollmentID,StudentID,CourseID,EnrollmentDate) VALUES
(1, 1, 101, '2022-08-01'),
(2, 2, 102, '2021-08-01');

==============================
   5️⃣ Departments Table
==============================

CREATE TABLE Departments (
    DepartmentID INT PRIMARY KEY,
    DepartmentName VARCHAR(100)
);

INSERT INTO Departments (DepartmentID,DepartmentName) VALUES
(1, 'Computer Science'),
(2, 'Mathematics');


==============================
         Queries
==============================
 
1️⃣ Perform CRUD Operations :-

INSERT INTO Students VALUES
(3, 'Ali', 'Khan', 'ali@email.com', '2001-03-10', '2023-08-01');

SELECT * FROM Students;

UPDATE Students
SET Email = 'ali.khan@email.com'
WHERE StudentID = 3;

DELETE FROM Students
WHERE StudentID = 3;

2️⃣ Retrieve students who enrolled after 2022 :-

SELECT *
FROM Students
WHERE EnrollmentDate > '2022-12-31';

3️⃣ Courses offered by Mathematics department (limit 5) :-

SELECT *
FROM Courses
WHERE DepartmentID = 2
LIMIT 5;

4️⃣ Number of students in each course (more than 5) :-

SELECT CourseID, COUNT(StudentID) AS TotalStudents
FROM Enrollments
GROUP BY CourseID
HAVING COUNT(StudentID) > 5;

5️⃣ Students enrolled in both Introduction to SQL and Data Structures :-

SELECT s.StudentID, s.FirstName
FROM Students s
JOIN Enrollments e ON s.StudentID = e.StudentID
WHERE e.CourseID IN (101,102)
GROUP BY s.StudentID, s.FirstName
HAVING COUNT(DISTINCT e.CourseID) = 2;

6️⃣ Students enrolled in either Introduction to SQL OR Data Structures :-

SELECT DISTINCT s.StudentID, s.FirstName
FROM Students s
JOIN Enrollments e ON s.StudentID = e.StudentID
WHERE e.CourseID IN (101,102);

7️⃣ Average credits of all courses :-

SELECT AVG(Credits) AS AverageCredits
FROM Courses;

8️⃣ Maximum salary of instructors in Computer Science department :-

SELECT MAX(Salary) AS MaxSalary
FROM Instructors
WHERE DepartmentID = 1;

9️⃣ Count students enrolled in each department :-

SELECT d.DepartmentName, COUNT(e.StudentID) AS TotalStudents
FROM Departments d
JOIN Courses c ON d.DepartmentID = c.DepartmentID
JOIN Enrollments e ON c.CourseID = e.CourseID
GROUP BY d.DepartmentName;

🔟 INNER JOIN – Students and their courses :-

SELECT s.FirstName, c.CourseName
FROM Students s
INNER JOIN Enrollments e ON s.StudentID = e.StudentID
INNER JOIN Courses c ON e.CourseID = c.CourseID;

1️⃣1️⃣ LEFT JOIN – All students and courses (if any) :-

SELECT s.FirstName, c.CourseName
FROM Students s
LEFT JOIN Enrollments e ON s.StudentID = e.StudentID
LEFT JOIN Courses c ON e.CourseID = c.CourseID;

1️⃣2️⃣ Subquery – Students in courses with more than 10 students :-

SELECT *
FROM Students
WHERE StudentID IN (
    SELECT StudentID
    FROM Enrollments
    WHERE CourseID IN (
        SELECT CourseID
        FROM Enrollments
        GROUP BY CourseID
        HAVING COUNT(StudentID) > 10
    )
);

1️⃣3️⃣ Extract Year from EnrollmentDate :-

SELECT StudentID, YEAR(EnrollmentDate) AS EnrollmentYear
FROM Students;


1️⃣4️⃣ Concatenate instructor first and last name :-

SELECT CONCAT(FirstName, ' ', LastName) AS FullName
FROM Instructors;

1️⃣5️⃣ Running total of students in courses  :-

SELECT CourseID,
       COUNT(StudentID) AS TotalStudents,
       SUM(COUNT(StudentID)) OVER (ORDER BY CourseID) AS RunningTotal
FROM Enrollments
GROUP BY CourseID;


1️⃣6️⃣ Label students as Senior or Junior :-

SELECT StudentID,
CASE
    WHEN TIMESTAMPDIFF(YEAR, EnrollmentDate, CURDATE()) > 4 THEN 'Senior'
    ELSE 'Junior'
END AS Status
FROM Students;