If we wanted to query our superhero database that has a table called SUPERHEROS. With a query that should return the ID, FirstName, and Surname in reverse alphabetical order by Surname of all heros whose first name begins with A or who have a cape, we would create the query by design in this way:

The style of query that you can see above is called a Query by Design. This is the style of database query that you will be sing in your exam if you sit the exam on or before January 2023.

Activity

Download and complete the Query By Design scribl.it notes below. If you are sitting the exam from Summer 2023, this is still a useful exercise as it helps you understand the “human” version of the SQL you will be using.

Introducing SQL

Using a Query By Design method will often take longer to create than simply writing out the SQL query. However, because of the use of the table this visual way of designing a query can seem more logical. This same query from above in SQL would look like:

SELECT ID, FirstName, LastName
FROM SUPERHEROS
WHERE FirstName LIKE 'A%'
AND HasCape = 'Yes'
ORDER BY LastName DESC

SQL, like query by design has four commands:

INSERT – Creates new records in the database

SELECT – Reads the data from the database records

UPDATE – Updates existing data in a record

DELETE – Deletes entire records (not tables)

Activity

Download and complete the SQL scribl.it notes below. If you are sitting the exam before Summer 2023, this activity is optional.