Databases and SELECT
HandoutDatabases and tables
A database stores information in tables. A table is a grid, a bit like a spreadsheet:
- each row is one record — for example, one student;
- each column is one field — for example, the student's name or score.
This course uses a small table called student. It has 5 records and 4 fields: id, name, form, and score.
Asking for data with SELECT
To read data from a table you write a query with SELECT.
The simplest query asks for every column of every row. The star * means "all columns":
SELECT * FROM student;
SELECTchooses the columns —*means all of them.FROM studentsays which table to read.
The database replies with a result table (also called a result set): the rows your query asked for.
A few rules
- Keywords like
SELECTandFROMare often written in capitals so they stand out. - End each statement with a semicolon
;. - An empty cell is shown as
NULL, which means "no value".
Now try it. Click Run to see the output, then Check answer.
Common mistakes
- End a query with a semicolon:
SELECT ... FROM table;. SELECT *returns every column — name only the ones you need.
SELECT … FROM … WHERE
WHERE keeps rows, then SELECT keeps columns — step through it.
Show the whole student table — every column and every row. Use SELECT * FROM ...;.
Click Run to see the output here.
Now keep only some columns and some rows. Show just the name and score of students in form 11A. Pick the two columns and add a WHERE form = '11A'.
Click Run to see the output here.