First Queries: SELECT and FROM

First lesson of the SQL Fundamentals (SELECT, Filters, Aggregates) course, free to read: about 15 minutes, 3 exercises and 3 quiz questions. This is the catalogue's base lesson; in the student space, it is rewritten for your profile.

Objectives

Understand what a relational table is (rows, columns, types)
Write a SELECT ... FROM query to read chosen columns
Create calculated columns and rename them with AS
Limit the number of rows displayed with LIMIT

SELECT and FROM Column aliases (AS)Calculated columns LIMIT

SQL (Structured Query Language) is the standard language for querying relational databases: SQLite, PostgreSQL, MySQL, SQL Server… In a relational database, data is stored in tables. A table looks like a spreadsheet sheet, but stricter: each column has a name and a type (text, integer, date…), and each row represents a record, for example a product.

Here is the produits table used in this lesson:

id	nom	categorie	prix	stock
1	Clavier	Informatique	50	12
2	Souris	Informatique	20	40
…	…	…	…	…

Reading Columns

The simplest query combines two keywords: SELECT (which columns?) and FROM (which table?).

-- The name and price of each product
SELECT nom, prix
FROM produits;

Columns are separated by commas and appear in the order you write them. To display everything, use the star:

SELECT *          -- all the columns
FROM produits;

SELECT * is handy for discovering a table, but in a real program, prefer naming the columns: the query is more readable, faster, and does not break if someone adds a column.

Calculating and Renaming

SELECT also accepts expressions. You can calculate the value of each product’s stock:

SELECT nom,
       prix * stock AS valeur_stock   -- calculated column + alias
FROM produits;

The AS keyword gives a name (an alias) to the result column. The produits table is not modified: a SELECT query only reads.

Watch out for the special value NULL, which means “unknown” or “missing”. The mouse pad’s stock is NULL, so prix * stock is also NULL for that product: any calculation with an unknown value gives an unknown result.

Limiting the Number of Rows

On a table with several million rows, you do not display everything. LIMIT restricts the result:

SELECT nom, categorie
FROM produits
LIMIT 3;   -- at most 3 rows

Without an explicit sort (covered in a later lesson), the database does not guarantee which rows come out first. LIMIT alone is therefore mainly for taking a peek at the data.

A Few Syntax Rules

Keywords (SELECT, FROM) are not case-sensitive: select works too, but the convention is to write them in uppercase.
Text is written between single quotes: 'Informatique'.
-- starts a comment up to the end of the line.
The semicolon ends a query; it is essential when you chain several.

You now know how to read a table, choose its columns and create calculated columns: this is the basis of all the queries you will write next.

Exercises

Solutions and tests stay on the server. In the student space, your code is run and checked, and the tutor gives hints before the solution.

CodeDifficulty 1SQL (SQLite) in the browser2 hints

Display the name and price

Write a query that displays the nom and the prix of all products in the produits table, in that column order.

Exercise data (SQL)

CREATE TABLE produits (
  id INTEGER PRIMARY KEY,
  nom TEXT NOT NULL,
  categorie TEXT NOT NULL,
  prix INTEGER NOT NULL,   -- prix en euros
  stock INTEGER            -- NULL = stock inconnu
);
INSERT INTO produits VALUES
 (1, 'Clavier', 'Informatique', 50, 12),
 (2, 'Souris', 'Informatique', 20, 40),
 (3, 'Écran 27 pouces', 'Informatique', 229, 5),
 (4, 'Lampe de bureau', 'Maison', 35, 0),
 (5, 'Chaise ergonomique', 'Mobilier', 189, 3),
 (6, 'Bureau assis-debout', 'Mobilier', 399, 2),
 (7, 'Tapis de souris', 'Informatique', 10, NULL),
 (8, 'Plante verte', 'Maison', 15, 25);

-- Votre requête ici
SELECT ...
FROM produits;

CodeDifficulty 2SQL (SQLite) in the browser2 hints

Stock value

Display the nom of each product and a calculated column named valeur_stock equal to prix * stock.

Exercise data (SQL)

CREATE TABLE produits (
  id INTEGER PRIMARY KEY,
  nom TEXT NOT NULL,
  categorie TEXT NOT NULL,
  prix INTEGER NOT NULL,   -- prix en euros
  stock INTEGER            -- NULL = stock inconnu
);
INSERT INTO produits VALUES
 (1, 'Clavier', 'Informatique', 50, 12),
 (2, 'Souris', 'Informatique', 20, 40),
 (3, 'Écran 27 pouces', 'Informatique', 229, 5),
 (4, 'Lampe de bureau', 'Maison', 35, 0),
 (5, 'Chaise ergonomique', 'Mobilier', 189, 3),
 (6, 'Bureau assis-debout', 'Mobilier', 399, 2),
 (7, 'Tapis de souris', 'Informatique', 10, NULL),
 (8, 'Plante verte', 'Maison', 15, 25);

SELECT nom, ... AS valeur_stock
FROM produits;

Multiple choiceDifficulty 11 hint

Reading a query

What does SELECT nom prix FROM produits; (with no comma) return?

Two columns: nom and prix
A single column, containing the names, titled prix
A syntax error in every database