Tables and SELECT

Lesson 1 of 21 · Part 1 · Querying data · about 15 minutes

A database keeps its data in tables. This course uses the data of a small online shop: customers, products, orders. Every query on this page runs right in your browser: press Run, change something, and run it again.

main.sql

1SELECT * FROM products;

Output

id	name	category	price	stock
1	Notebook	Stationery	4.50	120
2	Pens (10)	Stationery	6.00	80
3	Backpack	Bags	39.90	15
4	Water bottle	Kitchen	12.00	40
5	Headphones	Electronics	59.00	10
6	Desk lamp	Electronics	25.00	0
7	USB cable	Electronics	8.50	200
8	Coffee mug	Kitchen	9.90	60
9	Phone case	Electronics	15.00	35
10	Tote bag	Bags	11.00	25

10 rows

This is the shop's products table. Each row is one product, and each column is one piece of information about it:

Column	What it holds
`id`	the product's number, different for every product
`name`	the product's name
`category`	its category
`price`	its price
`stock`	how many are left

SELECT and FROM

A query says: from (FROM) this table, select (SELECT) these columns. * means "every column". To see only the name and the price, write the column names, separated by commas:

main.sql

1SELECT name, price FROM products;

Output

name	price
Notebook	4.50
Pens (10)	6.00
Backpack	39.90
Water bottle	12.00
Headphones	59.00
Desk lamp	25.00
USB cable	8.50
Coffee mug	9.90
Phone case	15.00
Tote bag	11.00

10 rows

The result is a table too: only the two columns you asked for, with the same rows. The columns come in the order you wrote them:

main.sql

1SELECT price, name FROM products;

Output

price	name
4.50	Notebook
6.00	Pens (10)
39.90	Backpack
12.00	Water bottle
59.00	Headphones
25.00	Desk lamp
8.50	USB cable
9.90	Coffee mug
15.00	Phone case
11.00	Tote bag

10 rows

The shop's other tables

The shop also has a customers table, an orders table, an order_items table (what is in each order) and an employees table. Later lessons use them one by one. First, a look at the customers:

main.sql

1SELECT id, name, city FROM customers;

Output

id	name	city
1	Ana Silva	London
2	Ben Okafor	Paris
3	Chloe Martin	London
4	David Kim	Tokyo
5	Emma Novak	Berlin
6	Farah Khan	Paris
7	George Brown	Tokyo
8	Hana Sato	London
9	Ivan Petrov	Berlin
10	Julia Costa	Sydney

10 rows

Tip: When you don't know which columns a table has, take a look with SELECT * first.

Several at once

A query ends with a semicolon ;. You can write several at once, and each one gives its own result:

main.sql

1SELECT name FROM products;2SELECT name FROM customers;

Output

Statement on line 1

name
Notebook
Pens (10)
Backpack
Water bottle
Headphones
Desk lamp
USB cable
Coffee mug
Phone case
Tote bag

10 rows

Statement on line 2

name
Ana Silva
Ben Okafor
Chloe Martin
David Kim
Emma Novak
Farah Khan
George Brown
Hana Sato
Ivan Petrov
Julia Costa

10 rows

Keywords in capitals, names in lowercase

Words like SELECT and FROM are keywords; capitals or not, both work. By habit, keywords go in capitals and table and column names in lowercase, so you can tell them apart at a glance. Line breaks and spaces are up to you, so a long query can take several lines:

main.sql

1select name,2    stock3from products;

Output

name	stock
Notebook	120
Pens (10)	80
Backpack	15
Water bottle	40
Headphones	10
Desk lamp	0
USB cable	200
Coffee mug	60
Phone case	35
Tote bag	25

10 rows

This is exactly the same as SELECT name, stock FROM products;.

Guess first

How many columns does the result have?

1SELECT name, city FROM customers;

SELECT names two columns, name and city, so the result has only those two. The customers table itself has 5 columns, but only 2 are selected here.

Your first error

Misspell a column name and the database tells you which name it doesn't know:

main.sql

1SELECT name, prise FROM products;

Output

ERROR 1054 (42S22): Unknown column 'prise' in 'field list'

ERROR 1054 is MySQL's error number; after it, it says there is no column called prise. Change it to price and run it again.

Practice

Write the query, then press Check. A hint costs nothing; the answer is there when you need it.

Exercise 1

Products and stock

From the products table, get two columns for every product: name and stock.

It should print

name	stock
Notebook	120
Pens (10)	80
Backpack	15
Water bottle	40
Headphones	10
Desk lamp	0
USB cable	200
Coffee mug	60
Phone case	35
Tote bag	25

10 rows

exercise1.sql

1SELECT * FROM products;

Output

Press Run or Ctrl+Enter

Exercise 2

Employees

From the employees table, get the name and title columns, title first.

It should print

title	name
Manager	Grace Lee
Sales lead	Omar Haddad
Warehouse lead	Lucia Romero
Sales	Tom Becker
Sales	Mei Tanaka
Packer	Sam Wilson

6 rows

exercise2.sql

1SELECT name FROM employees;

Output

Press Run or Ctrl+Enter

Exercise 3

Fix it: the column name

This query gives an error. Fix it so it gets the customers' name and email.

It should print

name	email
Ana Silva	[email protected]
Ben Okafor	[email protected]
Chloe Martin	NULL
David Kim	[email protected]
Emma Novak	[email protected]
Farah Khan	NULL
George Brown	[email protected]
Hana Sato	[email protected]
Ivan Petrov	NULL
Julia Costa	[email protected]

10 rows

exercise3.sql

1SELECT name, mail FROM customers;

Output

Press Run or Ctrl+Enter

Some customers left no email: that cell shows NULL, which means "no value". Lesson 5 is all about it.

What you learned

Data lives in tables: each row is one record, each column one piece of information.
SELECT column, column FROM table; gets the columns you want; * is every column.
Every query ends with a semicolon, and you can run several at once.
Keywords by habit in capitals, names in lowercase; line breaks and spaces as you like.
A misspelled column name gives ERROR 1054: first see which name it doesn't know.