Strata Scratch Problem Sets

Problem sets, exercises, and educational guides for SQL and python. These exercises are specifically tailored for business and marketing analytics students and novices.

Back to the Strata Scratch website
Login to Strata Scratch

Basic SQL Exercises (with Solutions)

Instructions

Questions

1. How many people embarked on the Titanic?

Schema: datasets

Table: titanic

Hint:

SELECT
    count(passengerid) AS n_passengers_embarked
FROM datasets.titanic

2. How many of survirors were women first class passengers?

Schema: datasets

Table: titanic

Hints:

SELECT
   count(passengerid) AS n_women_first_class_survivors
FROM datasets.titanic
WHERE
   pclass = 1 AND 
   sex = 'female' AND
   survived = 1

3. What is the average fare paid by those who died in the accident?

Schema: datasets

Table: titanic

Hints:

SELECT 
    avg(fare) AS non_survivor_average_fare
FROM datasets.titanic
WHERE 
    survived = 0

4. How many people survived per passenger class?

Schema: datasets

Table: titanic

Hints:

SELECT 
    pclass,
    SUM(survived) AS n_survived
FROM datasets.titanic
GROUP BY 
    pclass

5. What is the average height of quarterbacks?

Schema: datasets

Table: nfl_combine

Hints:

SELECT 
   avg(heightinchestotal) AS avg_height_inches
FROM datasets.nfl_combine
WHERE 
    position = 'QB'

6. Which year had the highest number of players?

Schema: datasets

Table: nfl_combine

Hints:

SELECT
    year,
    count(*) AS n_players
FROM
    datasets.nfl_combine
GROUP BY 
    year
ORDER BY 
   n_players DESC
LIMIT 1

7. How many players with weight <= 50 or weight >= 200 are there for each college?

Schema: datasets

Table: nfl_combine

Hints:

SELECT
    college,
    count(DISTINCT name) AS n_players
FROM datasets.nfl_combine
WHERE
    weight >= 200 OR
    weight <= 50
GROUP BY 
    college

8. What are the best and worst total SAT scores per school?

Schema: datasets

Table: sat_scores

Hints:

SELECT
    school,
    
    max(sat_writing + sat_verbal + sat_math) AS best_score,
    min(sat_writing + sat_verbal + sat_math) AS worst_score
FROM datasets.sat_scores
GROUP BY
    school

9. How many students each teacher lectured to?

Schema: datasets

Table: sat_scores

Hints:

SELECT
    teacher,
    
    COUNT(student_id) AS n_students
FROM datasets.sat_scores
GROUP BY
    teacher

10. Who is the student who has highest efficency for mathematics? Efficency is defined as number of points obtained divided by hours studied.

Schema: datasets

Table: sat_scores

Hints

SELECT
    student_id,
    hrs_studied,
    sat_math,
    sat_math / hrs_studied AS points_per_hour
FROM datasets.sat_scores
WHERE hrs_studied > 0
ORDER BY 
    points_per_hour DESC
LIMIT 1