Problem 1:

Given 3 tables, students(id, name) , courses(id, name) , grades(id, course_id, student_id, grade),

find the top 100 students based on their

average grades sorted descendingly by the average grade and in case multiple students have the same average grade, sort them lexicographically in ascending order by their names.

Your query should output a table with the following columns (name, average_grade).

Solution:

SELECT DISTINCTstudents.nameAS name, avg(grade) AS average_grade FROM grades
RIGHT JOIN students ONstudents.id=grades.student_idGROUP BYstudents.nameORDER BYavg(grade) DESC, students.nameASCLIMIT100

---

Problem 2:

For the same tables in Problem 1, for each student, get all the courses that he/she is enrolled in along with the grade he/she scored for each course. Order the result by the student name in ascending order and if there is a tie, break it with the course name in ascending order and if there is a tie break it with the grade in ascending order. The final result should have 3 columns with names (name, course, grade).

Solution:

SELECT DISTINCTstudents.nameAS name,courses.nameAS course, grades.gradeAS grade FROM ((grades
INNER JOIN students ONstudents.id=grades.student_idINNER JOIN courses ONcourses.id=grades.course_id))
ORDER BYstudents.nameASC, courses.nameASC, grades.gradeASC

---

Problem 3:

For the same tables in Problem 1, get the name of the most popular course (the one where the most students are enrolled) and if there is a tie, get the course that's lexicographically the smallest.

Solution:

SELECTc.nameFROM courses c
INNER JOIN grades g ONg.course_id=c.idGROUP BYc.id, c.nameORDER BYCOUNT(*) DESC, c.nameLIMIT1;

---

Problem 4:

Given a table called "bugs" with the following columns (id, token, title, category, device, reported_at, created_at, updated_at). Select all distinct bug categories.

Solution:

SELECT DISTINCT category FROM bugs

---

Problem 5:

For the same table in Problem 4, find how many bugs were created on "2019-03-01" or later. Your query should produce a table with one column called "count". This problem is graded partially, 10% on correctness (your query gets the correct count) and 90% on performance (your query makes use of available indexes).

Scored 10% only "Not Optimal solution"

SHOW INDEXES FROM bugs;

"records": [
{
"Table": "bugs",
"Non_unique": 0,
"Key_name": "PRIMARY",
"Seq_in_index": 1,
"Column_name": "id",
"Collation": "A",
"Cardinality": 9791826,
"Sub_part": null,
"Packed": null,
"Null": "",
"Index_type": "BTREE",
"Comment": "",
"Index_comment": ""
},
{
"Table": "bugs",
"Non_unique": 1,
"Key_name": "index_bugs_on_category_and_token_and_reported_at",
"Seq_in_index": 1,
"Column_name": "category",
"Collation": "A",
"Cardinality": 1,
"Sub_part": null,
"Packed": null,
"Null": "YES",
"Index_type": "BTREE",
"Comment": "",
"Index_comment": ""
},
{
"Table": "bugs",
"Non_unique": 1,
"Key_name": "index_bugs_on_category_and_token_and_reported_at",
"Seq_in_index": 2,
"Column_name": "token",
"Collation": "A",
"Cardinality": 29946,
"Sub_part": null,
"Packed": null,
"Null": "YES",
"Index_type": "BTREE",
"Comment": "",
"Index_comment": ""
},
{
"Table": "bugs",
"Non_unique": 1,
"Key_name": "index_bugs_on_category_and_token_and_reported_at",
"Seq_in_index": 3,
"Column_name": "reported_at",
"Collation": "A",
"Cardinality": 6085027,
"Sub_part": null,
"Packed": null,
"Null": "YES",
"Index_type": "BTREE",
"Comment": "",
"Index_comment": ""
}
]
}

SELECTCOUNT(x.created_at) AS count FROM bugs AS y USE INDEX (PRIMARY,index_bugs_on_category_and_token_and_reported_at) INNER JOIN bugs AS x ONx.id=y.idWHEREx.created_at>='2019-03-01

---

Problem 6:

For the same table in Problem 4, find the title of the bug with token = "token660" and reported_at on "2020-08-30". This problem is graded partially, 10% on correctness (your query gets the correct count) and 90% on performance (your query makes use of available indexes).

Scored 10% only "Not Optimal Solution"

SELECTx.titleFROM bugs
AS y USE INDEX (index_bugs_on_category_and_token_and_reported_at)
JOIN bugs AS x ONx.id=y.idWHEREy.token='token660'ANDy.reported_at='2020-08-30'