Transcript Introduction to SQL Exercises - Australian Defence Force

Introduction to SQL
Exercises
Exercise 1
• Suppose we have data on:
– Animal name
– Type (eg: bird, fish, mammal)
– Speed (kph/mph)
– Speed types(eg running, swimming, flying, diving
etc)
• Give a relational database schema for a
database with this information
Exercise 2
• Write the SQL to create tables for:
– animalType(typeID, description)
– speedType(typeID, description)
– animal(name, animalType)
– animalSpeed(animalName, speedType, topSpeed)
Exercise 3
• Write the SQL to:
– Insert a speedType with typeID of 1 and description
running
– Insert a speedType with a typeID of 2 and description
diving
– Insert an animalSpeed for a running cheetah of 112kph
– Insert an animalSpeed for a diving peregrine falcon of
387.2kph
Exercise 4
• Write the SQL to:
– Update the animalSpeed of the cheetah to
113kph
– Delete the animalSpeed entry for the peregrine
falcon
Exercise 5
• Write the SQL to:
– Select all records and fields from the speedType table
– Select all animalNames from the animal table
– Select all entries for peregrine falcons from the
animalSpeed table
– Select all entries for diving peregrine falcons from the
animalSpeed table