Ga naar inhoud

Header

Retrieving Data with SELECT

Introduction

We are going to work with SQL queries.

With these, we can retrieve data from tables in databases.

We start with the SELECT statement.



1. Retrieving all data from a table

With the SELECT statement, you can retrieve data.

Below you see the syntax to retrieve all data from a table.

SELECT
  * 
FROM
  <table_name>;
  • With SELECT, you indicate that you are retrieving data.
    • With *, you indicate that you are retrieving all rows and columns from a table.
  • With FROM, you indicate where you are selecting data from.
    • Here, you can specify a table name.
  • The semicolon ; indicates the end of the query.



Header

The example below shows how to retrieve all rows from the movies table:

SELECT
  *
FROM
  movies;
id name year rank
10920 Aliens 1986 8.2
17173 Animal House 1978 7.5
18979 Apollo 13 1995 7.5
... ... ... ... 
Columns: 4
Rows: 36



1.1. Styling

In this training, we apply the following (styling) rules:

  • Statements like SELECT and FROM on separate lines.
    • After this, we indent by 2 spaces.
  • We write statements in uppercase, such as SELECT.
    • This is not mandatory.
    • We do this to clearly distinguish.
      • Between SQL statements and names of columns and tables.
  • We end a query with a semicolon ;



2. Data from specific columns

With *, you select all columns from a table.

You can also specify specific columns.

The syntax for this is as follows:

SELECT
  <column_1, ... column_n>
FROM
  <table_name>;

See the example below:

SELECT
  name,
  rank
FROM
  movies;
name rank
Aliens 8.2
Animal House 7.5
Apollo 13 7.5
... ... 
Columns: 2
Rows: 36
  • Here, we only select data from columns name and rank.



3. Unique values with DISTINCT

So far, we have retrieved all rows from columns.

With DISTINCT, we can retrieve only the unique values.

See the example below:

SELECT
  DISTINCT rank
FROM
  movies;
rank
(NULL)
5.8
8.7
5.3
7.8
... 
Columns: 1
Rows: 21
  • Here, we only retrieve unique values from column rank.



4. The number of rows with COUNT()

With the function COUNT(), we can obtain the number of rows in a selection.

See the example below:

SELECT
  COUNT(*)
FROM
  movies;
count
36
  • Here, the number of rows in the movies table is counted.



5. The first number of rows with LIMIT

So far, we have always obtained all rows from a selection.

By using LIMIT, you can limit this.

You then only get the number of rows you specify.

See the example below:

SELECT
  *
FROM
  movies
LIMIT
  5;
id name year rank
10920 Aliens 1986 8.2
17173 Animal House 1978 7.5
18979 Apollo 13 1995 7.5
30959 Batman Begins 2005 (NULL)
46169 Braveheart 1995 8.3 
Columns: 4
Rows: 5
  • Here, we select the first 5 rows from the movies table.


Note: LIMIT does not work in all SQL dialects:

  • LIMIT <n>: PostgreSQL, MySQL

  • SELECT TOP <n>: MSSQL

  • FETCH FIRST <n> ROWS ONLY: Oracle



Summary

  • You select all rows and columns from a table as follows:
SELECT
  *
FROM
  movies;
  • You retrieve specific columns as follows:
SELECT
  name,
  rank
FROM
  movies;
  • You retrieve unique values with DISTINCT:
SELECT
  DISTINCT rank
FROM
  movies;
  • You calculate the number of rows in a selection with the function COUNT():
SELECT
  COUNT(*)
FROM
  movies;
  • You retrieve the first number of rows with LIMIT:
SELECT
  *
FROM
  movies
LIMIT
  10;
  • Note: this varies per SQL dialect.