Week 4

For this week, we will be extracting actual data out of databases so you will be required to have SQLite in order to complete the lab. Additionally, you may find the SQLite Browser helpful to use. If you are struggling to install either program, this tutorial may be of assistance to you

It is recommended that you become familiar with the command-line program and not the GUI (graphical user interface – i.e. SQLite Browser). It will help you in the long-run to not become overly reliant on using the GUI (as you will be required to write queries for assignments / midterm / final)

For the following problems, we will be using the following database schema. The SQLite database file can be found here. For more information, consult the A1 handout

Classes(class, type, country, numGuns, bore, displacement)
Ships(name, class, launched)
Battles(name, date)
Outcomes(ship, battle, result)

Write the following queries based on the database schema. Additionally, write out the results that the queries return

  1. Find the class name and country for all classes with at least 10 guns
  2. Find the names of all ships launched prior to 1918 but call the resulting column shipName
  3. Find the names of ships sunk in battle and the name of the battle in which they were sunk
  4. Find all ships that have the same name as their class
  5. Find the name of all ships that begin with the letter “R”
  6. Find the ships heavier than 35,000 tons
  7. List the name, displacement, and number of guns of the ships engaged in the battle of Guadalcanal
  8. Find all the ships mentioned in the database (remember that all these ships may not appear in the Ship relation)
  9. Find the countries that have both battleships and battlecruisers
  10. Find those ships that were damaged in one battle but later fought in another
  11. Find those battles with at least three ships of the same country

Practice writing SQL queries (as opposed to relational algebra) on the problems from last week’s lab

Resources