SQL Database Investigation

Learn how to search and investigate a school technology database using SQL.

How to approach this task

SQL Query Editor Ctrl + Enter to run

Query Results Results appear here

Run your SQL query to see the results here.
Database Browser — view the data

Look at the available data before writing your query. Choose a table to view its records.

SQL Tutor — help with writing SQL

If you are unsure what to type, use this section before trying the task. SQL is a language used to ask questions of databases.

1. The basic SQL pattern

Most of the SQL you will use follows this pattern:

SELECT fields FROM table WHERE condition ORDER BY field;

Think of it as:

  1. SELECT – What information do I want?
  2. FROM – Which table contains it?
  3. WHERE – Which records do I want?
  4. ORDER BY – How should the results be arranged?

2. SQL commands

Clicking a command puts an example into the SQL editor.

3. Choosing fields

Use * when you want every field.

SELECT * FROM Computers;

Or name the fields you want:

SELECT Model, RAM FROM Computers;

4. Searching with WHERE

WHERE lets you choose only records that meet a condition.

SELECT * FROM Computers WHERE RAM = 16;

Text values normally need quotation marks:

SELECT * FROM Computers WHERE OperatingSystem = 'Windows 11';

Numbers do not need quotation marks:

WHERE RAM = 16

5. AND and OR

AND means both conditions must be true.

SELECT * FROM Computers WHERE RAM = 16 AND OperatingSystem = 'Windows 11';

OR means either condition can be true.

SELECT * FROM Computers WHERE Model = 'Apple iMac' OR Model = 'HP ProDesk';

6. Searching text with LIKE

LIKE is useful when you only know part of some text value.

SELECT * FROM Students WHERE LastName LIKE 'W%';

The % means "anything can come after this".

7. Sorting results

ORDER BY sorts your results.

SELECT * FROM Computers ORDER BY RAM ASC;

ASC = smallest to largest / A to Z
DESC = largest to smallest / Z to A

8. A useful way to build SQL

  1. Find the table.
  2. Decide which fields you need.
  3. Add SELECT.
  4. Add FROM.
  5. Ask yourself whether you need WHERE.
  6. Add AND or OR if you have more than one condition.
  7. Add ORDER BY if the question asks you to sort.
Database Structure — fields available in each table