SQL

Learning Objectives

In this session you will

  • Learn what is meant by an “SQL database” and understand why we use them
  • Learn ways in which we can use SQL statements (code) to search (or query) a database table
  • Be able to write SQL code using the SELECT / FROM / WHERE / ORDER BY statements.

Getting Started: What is SQL?

Watch this short video to introduce you to the topic.

Keywords

Before you start writing SQL code, you need to understand the keywords listed below. Click here to find out more!
  • We can search these database tables by using a query
  • SQL queries use the following statements to describe what we are searching for
    • SELECT – Which fields do you want to show in your search results?
    • FROM – From which table?
    • WHERE – What is your search criteria?
    • ORDER BY – Which field would you like to sort in ascending or descending order?
  • SQL WHERE statements can use different operators such as
    • Comparison operators
      • equals to =
      • not equals to !=
      • Greater than >
      • Greater than or equal to >=
      • Less than <
      • Less than or equal to <=
    • Boolean operators when needing to search more than 1 search criteria:
      • AND – Where all search criteria must be met
      • OR – Where at least 1 of the search criteria must be met.

Writing SQL Statements: Your Challenge!

Watch each video and then have a go at writing the SQL statements for each question. The challenges have been organised into “bronze”, “silver” and “gold”.

The Scenario

A car dealer stores data about the cars in their showroom. Figure 1 shows the data returned from the database when the SQL statement SELECT * FROM Cars is executed.

You can download each challenge, or continue working through the videos and questions below.

Here are the answers once you have finished each challenge – do not cheat!

Bronze Challenge: Using SELECT, FROM and WHERE

Your Turn! Click here to see the questions
  1. Give the SQL statement that would be needed to show all red cars. Only the Make, Model and Colour fields should be displayed.
  2. Give the SQL statement that would be needed to show all cars made by BMW. All fields must be displayed in your results.
  3. Give the SQL statement that would be needed to show all cars made in the year 2010. Only the Make and Model should be shown.
  4. Give the SQL statement that would be needed to show all cars with a valid MOT. All fields must be displayed in your results.

Bronze Challenge: Using the Not Equals to Comparison Operator

Your Turn! Click here to see the questions
  1. Give the SQL statement that would be needed to show all cars not made by Nissan. All fields must be displayed in your results.
  2. Give the SQL statement that would be needed to show all cars except ones which are red. All fields must be displayed in your results.

Bronze Challenge: Using Greater Than and Less Than Comparison Operators

Your Turn! Click here to see the questions
  1. Give the SQL statement that would be needed to show all cars over £10,000. Only the Make, Model and Price should be displayed.
  2. Give the SQL statement that would be needed to show all cars before 2012. All fields must be displayed in your results.
  3. Give the SQL statement that would be needed to show all cars costing £7000 or less. Only the Make, Model and Price should be displayed.
  4. Give the SQL statement that would be needed to show all cars made in the year 2004 or after. Only the Model and Price should be displayed.

Silver Challenge: Using Boolean Operators

Your Turn! Click here to see the questions
  1. Give the SQL statement that would be needed to show all cars made by BMW or Audi. Only the Make, Model and Colour needs to be displayed.
  2. Give the SQL statement that would be needed to show all red cars over £3000. Only the Make, Model and Price fields should be displayed.
  3. Give the SQL statement that would be needed to show all cars which are a Mini or have a valid MOT . Only the Make and Model should be shown.
  4. Give the SQL statement that would be needed to show all cars with a valid MOT and were made before 2010. All fields must be displayed in your results.
  5. Give the SQL statement that would be needed to show all cars before 2012 and are red in colour. All fields must be displayed in your results.
  6. Give the SQL statement that would be needed to show all cars not made by Nissan or Ford. The Make, Model, Colour and Price must be displayed in the results.

Silver Challenge: Sorting Query Results

Your Turn! Click here to see the questions
  1. Give the SQL statement that would be needed to show all cars between £10,000 and £15,000. All fields must be displayed in your results and the models should be sorted in alphabetical order.
  2. Give the SQL statement that would be needed to show all cars costing £7000 or less and are black. Only the Make, Model and Price should be displayed and the cheapest cars should appear at the top.
  3. Give the SQL statement that would be needed to show all cars made in between the years 2012 and 2017. Only the Model and Price should be display and the newest cars should be displayed at the top.
  4. Give the SQL statement that would be needed to show all cars not made by Nissan and are either black or blue in colour. All fields must be displayed in your results and should be grouped by Make.

Gold Challenge: LIKE Wildcard + Create Your Own!

Your Turn! Click here to see the questions
  1. Give the SQL command that would be needed to show all cars where the model name begins with the letter “Q”. Only the Make, Model and Colour needs to be displayed.
  2. Give the SQL command that would be needed to show all cars with a colour ending in the letter “e”. Only the Make, Model and Price fields should be displayed.
  3. Give the SQL statement that would be needed to show all cars where the make contains the letter “u”. Only the Make should be shown.
  4. Give the SQL statement that would be needed to show all cars where the make starts with the letter “a” or “b”. All fields must be displayed in your results.
  5. Give the SQL statement that would be needed to show all cars where the colour starts with the letter “B” and are under £10,000. The Make, Model, Colour and Price must be displayed in the results.
  6. Give the SQL statement that would be needed to show all car brands which contain the word “wagen”. All fields must be displayed and grouped by model.
  7. Give the SQL statement that would be needed to show all cars which contain a colour starting with “bl” and are either made by Audi or Volkswagen. All fields must be shown and should be displayed from most expensive to cheapest.

Gold Challenge: Create your own!

Make up three of your own questions using as many of the operators and statements as possible. Try to make it as tricky as you can!