The Select Statement

Simple SQL queries useing the Select statement

For the rest of this exercise we will be looking at the SELECT statement.

To follow along, you should open the DB Browser application and connect to the 'staff' database you saved earlier.

If you haven't already done so this can be downloaded from this link

In SQL, querying data is performed by a SELECT statement. A select statement can have 6 key components;

SELECT colnames
FROM tablename
GROUP BY colnames
WHERE conditions
HAVING conditions
ORDER BY colnames

In practice very few queries will have all of these clauses in them simplifying many queries. On the other hand, conditions in the WHERE clause can be very complex and if you need to JOIN two or more tables together then more clauses (JOIN and ON) are needed.

All of the clause names above have been written in uppercase for clarity. SQL is not case sensitive. Neither do you need to write each clause on a new line, but it is often clearer to do so for all but the simplest of queries.

In this exercise we will start with the very simple and work our way up to the more complex.

The simplest query is effectively one which returns the contents of the whole table.

With staff database open in DB Browser select the 'Execute SQL Tab'.

In the SQL pane type type following:

Select * from employees;

The '*' character acts as a wildcard meaning all of the columns

Click on the 'Play' arrow above the SQL pane to see the results of your query.

It is better practice and generally more efficient to explicitly list the column names that you want returned. For example:

Select first_name, last_name from employees;

In addition to limiting the columns returned by a query, you can also limit the rows returned. The simplest case is to say how many rows are wanted using the Limit clause. In the example below only the first ten rows of the result of the query will be returned. This is useful if you just want to get a feel for what the data looks like.

Select *
From employees
Limit 10;

Exercise 1

Write a query which returns the first 5 rows from the employees table with only the columns first_name, last_name, and gender.

Solution to Exercise 1