SQLASSIGNMENT4.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 sample student assessment data

INSERT INTO Student_Assessments VALUES
(1, 101, 'Aarv',   'SQL',      'Quiz',       '2024-01-10', 100, 80, 'Present', 'Vidhi Sharma', NULL),
(2, 102, 'Anjali', 'SQL',      'Exam',       '2024-01-20', 100, 35, 'Present', 'Vidhi Sharma', 'Needs improvement'),
(3, 103, 'Riya',  'Power BI', 'Assignment', '2024-02-05', 100, 60, 'Present', 'Ankit Jain', NULL),
(4, 104, 'Siya',  'Power BI', 'Quiz',       '2024-02-10', 50,  0,  'Absent',  'Ankit Jain', 'Absent'),
(5, 105, 'Arjun',  'Azure',    'Exam',       '2024-03-01', 100, 72, 'Present', 'Neha Singh', NULL),
(6, 106, 'Aman',   'SQL',      'Assignment', '2024-02-15', 100, 45, 'Present', 'Vidhi Sharma', NULL),
(7, 107, 'Niya',   'Azure',    'Quiz',       '2024-01-25', 50,  40, 'Present', 'Neha Singh', NULL),
(8, 108, 'Pooja',  'Power BI', 'Exam',       '2024-03-10', 100, 85, 'Present', 'Ankit Jain', 'Good'),
(9, 109, 'Ravi',   'SQL',      'Quiz',       '2023-12-15', 50,  25, 'Present', 'Vidhi Sharma', 'Weak'),
(10,110, 'Akash',  'Azure',    'Assignment', '2024-03-05', 100, 0,  'Absent',  'Neha Singh', 'Absent');

Select * from Student_Assessments

-- 1. 10% bonus marks
SELECT student_name, obtained_marks * 0.10 AS Bonus
FROM Student_Assessments;
-- 2. New salary logic → Flat 5000 not applicable here
-- (Skipped as this is student marks based system)

-- 3. Projected age (assume age = 20 for understanding)
SELECT student_name, 20 + 2 AS ProjectedAge
FROM Student_Assessments;

-- 4. 2% deduction
SELECT student_name, obtained_marks * 0.02 AS Deduction
FROM Student_Assessments;

-- 6. Exactly 20 years old (assumed age logic)
-- Not applicable (no age column)

-- 7. Marks greater than 20
SELECT * FROM Student_Assessments
WHERE obtained_marks > 20;

-- 8. Not from Power BI
SELECT * FROM Student_Assessments
WHERE course_name <> 'Power BI';

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

-- 10. Marks <= 50
SELECT * FROM Student_Assessments
WHERE obtained_marks <= 50;

-- 11. SQL course AND marks > 50
SELECT * FROM Student_Assessments
WHERE course_name = 'SQL'
AND obtained_marks > 50;

-- 12. SQL OR Azure course
SELECT * FROM Student_Assessments
WHERE course_name = 'SQL'
OR course_name = 'Azure';

-- 13. Present but not Azure
SELECT * FROM Student_Assessments
WHERE attendance_status = 'Present'
AND course_name <> 'Azure';

-- 14. Marks = 35 AND Present
SELECT * FROM Student_Assessments
WHERE obtained_marks = 35
AND attendance_status = 'Present';

-- 15. Not SQL and marks > 40
SELECT * FROM Student_Assessments
WHERE course_name <> 'SQL'
AND obtained_marks > 40;

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

-- 17. Course IN SQL, Azure
SELECT * FROM Student_Assessments
WHERE course_name IN ('SQL', 'Azure');

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

-- 19. Marks between 25k–35k equivalent → marks range
SELECT * FROM Student_Assessments
WHERE obtained_marks BETWEEN 25 AND 35;

-- 20. Missing attendance
SELECT * FROM Student_Assessments
WHERE attendance_status IS NULL;

-- 21. Names ending with 'n'
SELECT * FROM Student_Assessments
WHERE student_name LIKE '%n';

-- 22. Marks NOT between 40 and 80
SELECT * FROM Student_Assessments
WHERE obtained_marks NOT BETWEEN 40 AND 80;

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

-- Count absent students
SELECT COUNT(*) AS Absent_Count
FROM Student_Assessments
WHERE attendance_status = 'Absent';