-- Create students table
CREATE TABLE students (
    id INT PRIMARY KEY,
    name VARCHAR(255),
    marks INT
);

-- Insert some sample data into students table
INSERT INTO students (id, name, marks) VALUES
(1, 'John', 75),
(2, 'Alice', 92),
(3, 'Bob', 60),
(4, 'Eva', 85),
(5, 'Charlie', 45);

-- Create grades table
CREATE TABLE grades (
    grade INT PRIMARY KEY,
    min_mark INT,
    max_mark INT
);

-- Insert predefined grades into grades table
INSERT INTO grades (grade, min_mark, max_mark) VALUES
(1, 0, 9),
(2, 10, 19),
(3, 20, 29),
(4, 30, 39),
(5, 40, 49),
(6, 50, 59),
(7, 60, 69),
(8, 70, 79),
(9, 80, 89),
(10, 90, 100);

/* ALL Queries Here  */

-- Print Grades Table
-- select * from grades;

-- print Students Table
-- select * from students;

-- Assign respective grades to all students
select id, name , marks, grade from students,grades where marks>=min_mark and marks<=max_mark;


/*
Ketty gives Eve a task to generate a report containing three columns: Name, Grade and Mark. Ketty doesn't want the NAMES of those students who received a grade lower than 8. The report must be in descending order by grade -- i.e. higher grades are entered first. If there is more than one student with the same grade (8-10) assigned to them, order those particular students by their name alphabetically. Finally, if the grade is lower than 8, use "NULL" as their name and list them by their grades in descending order. If there is more than one student with the same grade (1-7) assigned to them, order those particular students by their marks in ascending order.
*/
-- select 
-- CASE WHEN grade < 8  THEN NULL ELSE name END as Name,
-- grade,marks from students,grades 
-- WHERE 
--     marks>=min_mark and marks<=max_mark 
-- ORDER BY
--     CASE WHEN name is NULL THEN marks END ASC,
--     grade DESC,
--     name ASC;