Practice SQL

Write a query that returns the name and city of every student, ordered alphabetically by city and then by name within each city.

SELECT -- your columns
FROM students
ORDER BY -- your sort columns;

SELECT name, city
FROM students
ORDER BY city ASC, name ASC;

Return the id and name of all students whose name contains the substring 'an' (case-insensitive match is not required — just use LIKE). Limit the results to the first 5 rows.

SELECT id, name
FROM students
WHERE name LIKE '%an%'
LIMIT 5;

Write a query that shows each city and the number of students in that city. Name the count column total. Only include cities that have more than 2 students.

SELECT city, COUNT(*) AS total
FROM students
GROUP BY city
-- filter groups here

SELECT city, COUNT(*) AS total
FROM students
GROUP BY city
HAVING COUNT(*) > 2;

Using the students and enrolments tables, write a query that returns each student's name and the course_id they are enrolled in. Include all students even if they have no enrolments (show NULL for course_id in that case). Order by student name.

SELECT s.name, e.course_id
FROM students s
LEFT JOIN enrolments e ON s.id = e.student_id
ORDER BY s.name;

Some students have a NULL value in the level column. Write two separate statements: 1. An UPDATE that sets level = 1 for every student where level is NULL. 2. A SELECT that returns the name and level of all students where level is still NULL after the update (should return no rows, but the query must be valid).

UPDATE students
SET level = 1
WHERE level IS NULL;

SELECT name, level
FROM students
WHERE level IS NULL;

Write a single query (using a subquery) that returns the name and level of every student whose level is strictly above the average level across all students. Order results by level descending.

SELECT name, level
FROM students
WHERE level > (
  SELECT AVG(level)
  FROM students
)
ORDER BY level DESC;

Using a window function, write a query that returns student_id, course_id, score, and a rank column that ranks students within each course from highest score (rank 1) to lowest. Use RANK() so that tied scores receive the same rank. Only include rows where score is not NULL.

SELECT
  student_id,
  course_id,
  score,
  RANK() OVER (
    -- partition and order here
  ) AS rank
FROM enrolments
WHERE score IS NOT NULL;

SELECT
  student_id,
  course_id,
  score,
  RANK() OVER (
    PARTITION BY course_id
    ORDER BY score DESC
  ) AS rank
FROM enrolments
WHERE score IS NOT NULL;

Write a query using a CTE named course_counts that counts the number of enrolments per course. Then in the main query, return the course_id and enrolment_count for courses that have fewer enrolments than the average enrolment count across all courses.

WITH course_counts AS (
  SELECT course_id, COUNT(*) AS enrolment_count
  FROM enrolments
  GROUP BY course_id
)
SELECT course_id, enrolment_count
FROM course_counts
WHERE enrolment_count < (
  SELECT AVG(enrolment_count) FROM course_counts
)
ORDER BY enrolment_count ASC;

Write a query that returns student_id, course_id, score, and a grade column computed with a CASE expression: scores 90 and above are 'A', 75-89 are 'B', 60-74 are 'C', any other non-NULL score is 'F', and NULL scores should show 'Not graded'. Order by student_id, then course_id.

SELECT
  student_id,
  course_id,
  score,
  CASE
    -- your conditions here
  END AS grade
FROM enrolments
ORDER BY student_id, course_id;

SELECT
  student_id,
  course_id,
  score,
  CASE
    WHEN score >= 90 THEN 'A'
    WHEN score >= 75 THEN 'B'
    WHEN score >= 60 THEN 'C'
    WHEN score IS NOT NULL THEN 'F'
    ELSE 'Not graded'
  END AS grade
FROM enrolments
ORDER BY student_id, course_id;