Student Performace & Attendance Tracker.txt

CREATE DATABASE 
student_performance_tracker;

CREATE TABLE Departments(
    department_id INT PRIMARY KEY,
    department_name VARCHAR(100)
    );

    CREATE TABLE Students(
    student_id INT PRIMARY KEY,
    name VARCHAR(100),
    dob DATE,
    gender VARCHAR(10),
    email VARCHAR(100),
    phone_number VARCHAR(15),
    address VARCHAR(200),
    admission_date DATE,
    department_id INT,
    FOREIGN KEY(department_id)REFERENCES Departments(department_id)
    );
    
    CREATE TABLE Faculty(
     faculty_id INT PRIMARY KEY,
     name VARCHAR(100),
     email VARCHAR(100),
     phone_number VARCHAR(15),
    experience_years INT,
    department_id INT,
    FOREIGN KEY(department_id)REFERENCES Departments(department_id)
);

CREATE TABLE Course(
     course_id INT PRIMARY KEY,
     course_name VARCHAR(100),
     faculty_id INT,
     FOREIGN KEY(faculty_id)REFERENCES Faculty(faculty_id)
);

CREATE TABLE Enrollments(
    enrollment_id INT PRIMARY KEY,
    student_id INT,
    course_id INT,
    enrollment_date DATE,
    UNIQUE(student_id,course_id),
    FOREIGN KEY (student_id) REFERENCES  Students(student_id) 
    FOREIGN KEY (course_id) REFERENCES  Course(course_id)  
);
    
    CREATE TABLE Attendance(
    attendance_id INT PRIMARY KEY,
    student_id INT,
    course_id INT,
    attendance_date DATE,
    UNIQUE(student_id,course_id),
    FOREIGN KEY (student_id) REFERENCES  Students(student_id),
    FOREIGN KEY (course_id) REFERENCES  Course(course_id)                   
);
    
CREATE TABLE Grade(
   grade_id INT PRIMARY KEY,
    student_id INT,
    course_id INT,
    marks_obtained INT,
    grade VARCHAR(5),
    FOREIGN KEY (student_id) REFERENCES Students(student_id),
    FOREIGN KEY (course_id) REFERENCES Course(course_id)                   
);


INSERT INTO Departments (department_id, department_name) VALUES
(1, 'Human Resources'),
(2, 'Engineering'),
(3, 'Sales'),
(4, 'Marketing'),
(5, 'Finance'),
(6, 'Customer Service'),
(7, 'Information Technology'),
(8, 'Operations');

INSERT INTO Students (student_id, name, dob, gender, email, phone_number, address, admission_date, department_id) VALUES
(1, 'Alice Johnson', '2005-03-15', 'Female', 'alice.j@example.com', '555-0101', '123 Oak St, Cityville', '2023-09-01', 1),
(2, 'Bob Smith', '2004-11-20', 'Male', 'bob.s@example.com', '555-0202', '456 Pine St, Townsville', '2023-09-01', 2),
(3, 'Charlie Brown', '2006-01-25', 'Male', 'charlie.b@example.com', '555-0303', '789 Maple Ave, Villageton', '2023-09-05', 1),
(4, 'Diana Prince', '2005-07-04', 'Female', 'diana.p@example.com', '555-0404', '101 Elm St, Metropolis', '2023-09-05', 3),
(5, 'Ethan Hunt', '2004-09-12', 'Male', 'ethan.h@example.com', '555-0505', '202 Mission St, Spyburg', '2023-09-10', 2),
(6, 'Fiona Glenanne', '2005-12-30', 'Female', 'fiona.g@example.com', '555-0606', '303 Covert Ln, Burn Notice', '2023-09-10', 3),
(7, 'George Jetson', '2006-06-14', 'Male', 'george.j@example.com', '555-0707', '404 Spacely St, Orbit City', '2023-09-15', 1),
(8, 'Hannah Abbott', '2004-02-28', 'Female', 'hannah.a@example.com', '555-0808', '505 Potion Ave, Hogwarts', '2023-09-15', 2);



INSERT INTO Faculty (faculty_id, name, email, phone_number, department_id,  experience_years ,) VALUES
(101, 'Dr. Alice Johnson', 'alice.j@university.edu', '555-1234', 1,6),
(102, 'Dr. Bob Smith', 'bob.s@university.edu', '555-5678', 1,4),
(103, 'Dr. Cathy Lee', 'cathy.l@university.edu', '555-8765', 2,5),
(104, 'Dr. David Kim', 'david.k@university.edu', '555-4321', 2,4),
(105, 'Dr. Eva Williams', 'eva.w@university.edu', '555-9876', 3,6),
(106, 'Dr. Frank Jones', 'frank.j@university.edu', '555-3456', 3,3),
(107, 'Dr. Grace Davis', 'grace.d@university.edu', '555-6543', 4,5),
(108, 'Dr. Henry White', 'henry.w@university.edu', '555-2109', 4,4);


INSERT INTO Course (course_id, course_name, faculty_id) VALUES
(1, 'Introduction to Computer Science', 101),
(2, 'Data Structures and Algorithms', 102),
(3, 'Database Management Systems', 101),
(4, 'Operating Systems', 103),
(5, 'Computer Networks', 102),
(6, 'Software Engineering', 104),
(7, 'Artificial Intelligence', 103),
(8, 'Machine Learning', 104),
(9, 'Web Development', 101),
(10, 'Mobile App Development', 102);


INSERT INTO Enrollments (enrollment_id, student_id, course_id, enrollment_date) VALUES
(1, 1, 1, '2025-01-15'),
(2, 1, 2, '2025-02-01'),
(3, 2, 1, '2025-01-20'),
(4, 2, 3, '2025-03-10'),
(5, 3, 2, '2025-01-25'),
(6, 3, 4, '2025-04-05'),
(7, 1, 3, '2025-03-12'),
(8, 2, 4, '2025-04-15');

INSERT INTO Attendance (attendance_id, student_id, course_id, attendance_date,status) VALUES
(1, 1, 1, '2025-10-26','present'),
(2, 1, 2, '2025-10-27','absent'),
(3, 2, 1, '2025-10-26','late'),
(4, 2, 3, '2025-10-28','absent'),
(5, 3, 1, '2025-10-27','late'),
(6, 3, 2, '2025-10-28','present'),
(7, 1, 3, '2025-10-29','late'),
(8, 2, 2, '2025-10-29','present');

INSERT INTO Grade (grade_id, student_id, course_id, marks_obtained, grade) VALUES
(1, 1, 1, 85, 'A'),
(2, 2, 1, 72, 'B'),
(3, 3, 2, 91, 'A+'),
(4, 4, 3, 68, 'B-'),
(5, 1, 2, 78, 'B+'),
(6, 2, 3, 55, 'C'),
(7, 3, 1, 89, 'A'),
(8, 4, 2, 45, 'D');


================================
TASKS & FUNCTIONALITIES 
================================

-----------------------------------
QUESTION :- 1
-----------------------------------

INSERT INTO Student 
(student_id, name, dob, gender, email, phone_number, address, admission_date, department_id)
VALUES (9, 'priya patel', '2007-09-02', 'Female', 'priya@gmail.com', 963258741, 'delhi', '2025-06-18', 101);

UPDATE Students
SET phone_number = '900000000000'
WHERE student_id = 1;

DELETE FROM Students 
WHERE student_id = 6;
 
-----------------------------------
QUESTION :- 2
-----------------------------------

SELECT * FROM Students s
JOIN Departments d 
ON s.department_id = d.department_id
WHERE
d.department_name = 'Computer Science Department';


SELECT * FROM Grade
ORDER BT marks_obtained DESC
LIMIT 10;


SELECT student_id, SUM(status = 'present') / count(*)*100
AS attendance_precent
FROM Attendance
GROUP BY student_id
HAVING attendance_precent < 75;

-----------------------------------
QUESTION :- 3
-----------------------------------

SELECT * FROM Grade
WHERE marks_obtained < 40
AND student_id IN(
    SELECT student_id
    FROM Attendance
    GROUP BY student_id
    HAVING SUM(status = 'present')/
    COUNT(*)*100 < 50
);

SELECT student_id
FROM Grade
WHERE marks_obtained > 90
OR student_id IN(
    SELECT student_id 
    FROM Attendance
    GROUP BY student_id 
    HAVING SUM (status = 'present')/COUNT(*)
);

SELECT * FROM Faculty
WHERE faculty_id NOT IN (
    SELECT faculty_id FROM Course 
);

-----------------------------------
QUESTION :- 4
-----------------------------------

SELECT * FROM Students
ORDER BY name;

SELECT department_id, COUNT(*) total_students
FROM Students
GROUP BY course_id;

SELECT course_id , AVG (marks_obtained)
average_marks
FROM Grade
GROUP BY course_id;

-----------------------------------
QUESTION :- 5
-----------------------------------

SELECT student_id,
SUM (status = 'present')/COUNT(*)*100
AS attendance_precent
FROM Attendance
GROUP BY student_id ;

SELECT MAX(marks_obtained)highest,
MIN(marks_obtained)lowest
FROM Grades;

SELECT department_id,COUNT(*) total_students
FROM Students
GROUP BY department_id;

-----------------------------------
QUESTION :- 7
-----------------------------------

SELECT s.name,d.department_name
FROM Students s 
INNER JOIN Departments d
ON s.department_id = d.department_id;

SELECT s.name
FROM Students s
LEFT JOIN Enrollments e
ON s.student_id = e.student_id
WHERE e.enrollment_id IS NULL;

SELECT c.course_name
FROM Faculty f
RIGHT JOIN Courses c
ON f.faculty_id = c.faculty_id
WHERE f.faculty_id IS NULL;

SELECT s.student_id, s.name
FROM Student s
FULL OUTER JOIN Grade g
ON s.student_id = g.student_id
WHERE g.grade_id IS NULL;


-----------------------------------
QUESTION :- 8
-----------------------------------

SELECT *
FROM Grade
WHERE marks_obtained >
(SELECT AVG(marks_obtained) FROM Grade);


SELECT *
FROM Faculty
WHERE experience_years >= 5;

SELECT student_id
FROM Attendance
WHERE status ='Absent'
GROUP BY student_id
HAVING COUNT(*)> 10;

-----------------------------------
QUESTION :- 9
-----------------------------------

SELECT MONTH(attendance_date) AS month_no
FROM Attendance;


SELECT name,
TIMESTAMPDIFF(YEAR,admission_date,CURDATE()) AS years_since_admission
FROM Students;

SELECT DATE_FORMAT(attendance_date,'%d-%m-%Y')
FROM Attendance;

-----------------------------------
QUESTION :- 10
-----------------------------------

SELECT LENGTH(name) FROM Students;

SELECT TRIM(name) FROM Students;

SELECT IFNULL(email,'Email Not Provided') FROM Students;

-----------------------------------
QUESTION :- 11
-----------------------------------

SELECT student_id,marks_obtained,
RANK() OVER(ORDER BY marks_obtained DESC) AS rank_no
FROM Grade;

SELECT student_id,
COUNT(*) OVER(PARTITION BY student_id ORDER BY attendance_date)
AS cumulative_attendance
FROM Attendance;


SELECT 
MONTH(enrollment_date) AS month,
COUNT(student_id) OVER (ORDER BY MONTH(enrollment_date)) AS running_total
FROM enrollment;


-----------------------------------
QUESTION :- 12
-----------------------------------

SELECT student_id,marks_obtained,
CASE
 WHEN marks_obtained > 90 THEN 'Excellent'
 WHEN marks_obtained BETWEEN 75 AND 90 THEN 'Good'
 ELSE 'Needs Improvement'
END AS performance
FROM Grade;

SELECT student_id,
CASE
 WHEN SUM(status='Present')/COUNT(*)*100 > 80 THEN 'Regular'
 WHEN SUM(status='Present')/COUNT(*)*100 BETWEEN 50 AND 80 THEN 'Irregular'
 ELSE 'Defaulter'
END AS attendance_type
FROM Attendance
GROUP BY student_id;