SQL Tutorial

Tutorial Instructions

Do Tutorials 0–3 from the SQLZoo Tutorial; you can do the quizzes if you'd like to reinforce your knowledge. Optionally, do Tutorials 4–7, if you want to try the optional problems below. (Any substantive knowledge of SQL demands the techniques used for the optional problems, so you should do them unless you really struggle with the required problems.)

Problem Set Exercises

Using this SQL Fiddle practice database on Roman gladiators, answer the questions. You'll need to type your SELECT queries at the bottom of the script, where it says 'CREATE YOUR OWN QUERIES BELOW'. If you decide to run more than one query at a time, be sure to finish each query with a semicolon! (N.B. Database schema and sample data created with the rather entertaining assistance of ChatGPT; I've copied our conversation into a document since evidently talking about killing gladiators is a potential violation of terms of service and thus the chat can't be shared...)

Note: You may find it convenient to use short aliases for the tables, to avoid having to type long table names. You can supply any alias you like directly after the name of the table in your SELECT statement and then reuse that alias elsewhere, even earlier in the statement. For example, here I use ts as shorthand for TrainingSchools and g as shorthand for Gladiators (try running this query in the SQL Fiddle and see what happens!):

 SELECT g.Name AS GladiatorName, 
    ts.Name AS LudusName
    FROM Gladiators g
        JOIN TrainingSchools ts 
            ON g.TrainingSchoolID = ts.SchoolID;

Hand in: A Word file (.doc or .docx) with the SQL query and result table for each question. You can download a Word document with just the questions here. You can copy and paste your query and the result table from the SQL Fiddle into the Word document.

Required (Straightforward)
These use the tables gladiator_list and match_list. Before starting to write your queries, you'll want to see what's in each table by running SELECT * FROM gladiator_list; and SELECT * FROM match_list;.

  1. What date did each match take place, and who was the winner? (Just return the relevant columns.)
  2. Who lost the match in 65 BCE? (Just return their name.)
  3. Which gladiators' names begin with V? (Just return their names.)
  4. List the gladiators' names, owners, and ludus (training school).

Required (Slightly Harder)
These use the tables gladiator_list and match_list.

  1. Which gladiator died the latest, what year did he die, what type of gladiator was he, and what ludus (training school) did he belong to? (Remember that BCE years go backwards, so you'll want the smallest number. You can choose just the first row of results by adding LIMIT 1 at the end of the query.)
  2. Which matches took place after 100 BCE but before 50 BCE? Include everything except for MatchID, WinnerID, and LoserID; return your results in chronological order. (To start with the largest number, add DESC at the end of the ORDER BY clause.)
  3. List the names of the losers of matches who were spared (note that 0 is the same as FALSE and 1 is the same as TRUE).
  4. Which gladiators reached the ripe old age of 30? Return just the gladiators' names and their age at death, calculated from DeathDate and DateOfBirth. (You can use ColumnA - ColumnB both in the SELECT statement [give it the alias 'Age' using AS Age right after the formula] and in the WHERE clause.)

Optional (More Challenging)
These exercises may use GROUP BY, JOIN, and the aggregate function COUNT(). You should use (some of) the tables Owners, Gladiators, TrainingSchools, TypesOfGladiators, and Matches. Remember to check the column names of each table by doing SELECT * FROM Table before you try working with that table.

  1. Using JOIN, list just the names of the gladiators and the names of their owners.
  2. How many types of gladiator are included in the database (whether or not they're represented by an actual gladiator)?
  3. How many gladiators are at each ludus (training school)?

Optional (Most Challenging)
These exercises may use GROUP BY, JOIN, and HAVING, as well as some aggregate functions such as COUNT() or MAX()/MIN(). You should use the tables Owners, Gladiators, TrainingSchools, TypesOfGladiators, and Matches. Remember to check the column names of each table by doing SELECT * FROM Table before you try working with that table.

  1. What is the only type of gladiator that is represented more than once in the database? (Return the TypeName and the count of that TypeName. Do not use LIMIT.)
  2. Which gladiator died at the youngest age, and how old was he? Do not use LIMIT; you will need to use a subquery (i.e., SELECT within SELECT) for this.
  3. How many times did each gladiator owner/enslaver have a victorious gladiator? (Return the Owners.Name column [you may rename it if you like] and the count of their gladiators' wins, as NumWins.)
  4. How many gladiators does each gladiator owner/enslaver have training at each ludus (training school) location? Order by the owners' names. (Return the owner's name, the location, and the count of that owner's gladiators at the location.)