SQLASSIGNMENTT4.sql

use JANUARY2026

-- Creating Student_Assessments table

CREATE TABLE Student_Assessments
(
    assessment_id INT PRIMARY KEY,
    student_id INT NOT NULL,
    student_name VARCHAR(50) NOT NULL,
    course_name VARCHAR(50) NOT NULL,

    assessment_type VARCHAR(20)
    CHECK (assessment_type IN ('Quiz', 'Assignment', 'Exam')),

    assessment_date DATE NOT NULL,

    total_marks INT CHECK (total_marks > 0),
    obtained_marks INT CHECK (obtained_marks >= 0),

    attendance_status VARCHAR(10)
    CHECK (attendance_status IN ('Present', 'Absent')),

    trainer_name VARCHAR(50) NOT NULL,
    remarks VARCHAR(100),

    CHECK (obtained_marks <= total_marks)
);

-- inserting data 
INSERT INTO Student_Assessments VALUES
(1, 101, 'Aman',   'SQL',      'Quiz',       '2024-01-10', 100, 80, 'Present', 'Vidhi dhandre', NULL),
(2, 101, 'Aman',   'SQL',      'Exam',       '2024-02-20', 100, 75, 'Present', 'Vidhi dhandre', NULL),

(3, 102, 'Anjali', 'SQL',      'Assignment', '2024-01-18', 100, 35, 'Present', 'Vidhi dhandre', 'Needs improvement'),
(4, 102, 'Anjali', 'Power BI', 'Quiz',       '2024-02-10', 50,  0,  'Absent',  'Ronit pal', 'Absent'),

(5, 103, 'Ronak',  'Power BI', 'Assignment', '2024-02-05', 100, 60, 'Present', 'Ronit pal', NULL),
(6, 103, 'Ronak',  'Power BI', 'Exam',       '2024-03-01', 100, 65, 'Present', 'Ronit pal', NULL),

(7, 104, 'Sana',  'Azure',    'Quiz',       '2024-01-25', 50,  40, 'Present', 'Neha Singh', NULL),
(8, 104, 'Sana',  'Azure',    'Exam',       '2024-03-05', 100, 0,  'Absent',  'Neha Singh', 'Absent'),

(9, 105, 'Arav',  'SQL',      'Quiz',       '2024-01-15', 50,  45, 'Present', 'Vidhi dhandre', NULL),
(10,105, 'Arav',  'SQL',      'Assignment', '2024-02-18', 100, 55, 'Present', 'Vidhi dhandre', NULL),

(11,106, 'Amin',   'Azure',    'Assignment', '2024-02-12', 100, 48, 'Present', 'Neha Singh', NULL),
(12,106, 'Amin',   'Azure',    'Exam',       '2024-03-10', 100, 52, 'Present', 'Neha Singh', NULL),

(13,107, 'Niya',   'Power BI', 'Quiz',       '2024-01-28', 50,  42, 'Present', 'Ronit pal', NULL),
(14,107, 'Niya',   'Power BI', 'Assignment', '2024-02-22', 100, 58, 'Present', 'Ronit pal', NULL),

(15,108, 'Punjan',  'SQL',      'Exam',       '2024-03-02', 100, 85, 'Present', 'Vidhi dhandre', 'Good'),

(16,109, 'Rahi',   'Azure',    'Quiz',       '2023-12-15', 50,  25, 'Present', 'Neha Singh', 'Weak'),
(17,109, 'Rahi',   'Azure',    'Assignment', '2024-01-30', 100, 38, 'Present', 'Neha Singh', NULL),

(18,110, 'Adarsh',  'Power BI', 'Exam',       '2024-03-08', 100, 90, 'Present', 'Ronit pal', 'Excellent'),

(19,111, 'Alina',  'SQL',      'Quiz',       '2024-02-05', 50,  0,  'Absent',  'Vidhi dhandre', 'Absent'),

(20,112, 'Niren',  'Azure',    'Exam',       '2024-03-12', 100, 70, 'Present', 'Neha Singh', NULL);

Select * from Student_Assessments 

---1. Records with obtained marks > 70%
SELECT * FROM Student_Assessments
WHERE obtained_marks > (0.7 * total_marks);

---2. Students scoring full marks
SELECT * FROM Student_Assessments
WHERE obtained_marks = total_marks;

---3. Absent students
SELECT * FROM Student_Assessments
WHERE attendance_status = 'Absent';

---4. Marks between 40 and 60
SELECT * FROM Student_Assessments
WHERE obtained_marks BETWEEN 40 AND 60;

---5. Assessments after 1 Jan 2024
SELECT * FROM Student_Assessments
WHERE assessment_date > '2024-01-01';

---6. Assessments not by Rahul Sharma
SELECT * FROM Student_Assessments
WHERE trainer_name <> 'Rahul Sharma';

---7. Power BI Project assessments
SELECT * FROM Student_Assessments
WHERE course_name = 'Power BI'
AND assessment_type = 'Assignment';

---8. SQL or Azure course records
SELECT * FROM Student_Assessments
WHERE course_name IN ('SQL', 'Azure');

---9. Students not absent
SELECT * FROM Student_Assessments
WHERE attendance_status = 'Present';

---10. Marks < 50 and present
SELECT * FROM Student_Assessments
WHERE obtained_marks < 50
AND attendance_status = 'Present';

---11. Names starting with A
SELECT * FROM Student_Assessments
WHERE student_name LIKE 'A%';

---12. Trainers containing 'an'
SELECT * FROM Student_Assessments
WHERE trainer_name LIKE '%an%';

---13. Courses ending with BI
SELECT * FROM Student_Assessments
WHERE course_name LIKE '%BI';

---14. Remarks containing 'late'
SELECT * FROM Student_Assessments
WHERE remarks LIKE '%late%';

---15. Calculate percentage
SELECT student_name, 
       (obtained_marks * 100.0 / total_marks) AS Percentage
FROM Student_Assessments;

---16. Failed students (<40%)
SELECT * FROM Student_Assessments
WHERE (obtained_marks * 100.0 / total_marks) < 40;

---17. Grace marks for 35–39
SELECT student_name, obtained_marks, obtained_marks + 5 AS Grace_Marks
FROM Student_Assessments
WHERE obtained_marks BETWEEN 35 AND 39;

---18. Add 5% marks to quizzes (display only)
SELECT student_name, obtained_marks,
       obtained_marks * 1.05 AS Increased_Marks
FROM Student_Assessments
WHERE assessment_type = 'Quiz';

---19. Average marks per course
SELECT course_name, AVG(obtained_marks) AS Avg_Marks
FROM Student_Assessments
GROUP BY course_name;

---20. Max & Min per assessment type
SELECT assessment_type,
       MAX(obtained_marks) AS Max_Marks,
       MIN(obtained_marks) AS Min_Marks
FROM Student_Assessments
GROUP BY assessment_type;

---21. Absent count per course
SELECT course_name, COUNT(*) AS Absent_Count
FROM Student_Assessments
WHERE attendance_status = 'Absent'
GROUP BY course_name;

---22. Total assessments per trainer
SELECT trainer_name, COUNT(*) AS Total_Assessments
FROM Student_Assessments
GROUP BY trainer_name;

---23. Pass count per course (>=40%)
SELECT course_name, COUNT(*) AS Pass_Count
FROM Student_Assessments
WHERE (obtained_marks * 100.0 / total_marks) >= 40
GROUP BY course_name;

---24. Trainers with more than 5 assessments
SELECT trainer_name FROM Student_Assessments
GROUP BY trainer_name
HAVING COUNT(*) > 5;

---25. Students with more than 3 assessments
SELECT student_name FROM Student_Assessments
GROUP BY student_name
HAVING COUNT(*) > 3;

---26. Courses with average marks > 65
SELECT course_name FROM Student_Assessments
GROUP BY course_name
HAVING AVG(obtained_marks) > 65;

---27. Assessment types with >20% absentees
SELECT assessment_type FROM Student_Assessments
GROUP BY assessment_type
HAVING 
COUNT(CASE WHEN attendance_status = 'Absent' THEN 1 END) * 100.0 / COUNT(*) > 20;

---28. Students above overall average
SELECT * FROM Student_Assessments
WHERE obtained_marks >
(
    SELECT AVG(obtained_marks)
    FROM Student_Assessments
);

---29. Top scorer per course
SELECT course_name, MAX(obtained_marks) AS Top_Marks
FROM Student_Assessments
GROUP BY course_name;

---30. Below course average
SELECT s.*
FROM Student_Assessments s
WHERE obtained_marks <
(
    SELECT AVG(obtained_marks)
    FROM Student_Assessments
    WHERE course_name = s.course_name
);

---31. Never absent students
SELECT student_name FROM Student_Assessments
GROUP BY student_name
HAVING COUNT(CASE WHEN attendance_status = 'Absent' THEN 1 END) = 0;

---32. Trainers with average marks < 50
SELECT trainer_name FROM Student_Assessments
GROUP BY trainer_name
HAVING AVG(obtained_marks) < 50;

---33. Update remarks for marks < 40
UPDATE Student_Assessments
SET remarks = 'Fail'
WHERE (obtained_marks * 100.0 / total_marks) < 40;

---34. Delete records older than 2 years
DELETE FROM Student_Assessments
WHERE assessment_date < DATEADD(YEAR, -2, GETDATE());

---35. Update marks to 0 where absent
UPDATE Student_Assessments
SET obtained_marks = 0
WHERE attendance_status = 'Absent';