Selecting data

Objective

  • Write a query to select all values for specific fields from a single table.

Key Points

  • A relational database stores information in tables, each of which has a fixed set of columns and a variable number of records.

  • A database manager is a program that manipulates information stored in a database.

  • We write queries in a specialized language called SQL to extract information from databases.

  • Use SELECT… FROM… to get values from a database table.

  • SQL is case-insensitive (but data is case-sensitive).

Introductory jargon

  • A relational database is arranged as tables.

  • Columns are called fields.

  • Rows are called records.

  • Commands are called queries.

Selecting fields

We will use SELECT ... FROM ... to get values from a database table.

From our data set, we will use this syntax to display scientists’ names using SELECT and giving names of columns we want.

SELECT family, personal FROM Person;

family    personal
--------  ---------
Dyer      William
Pabodie   Frank
Lake      Anderson
Roerich   Valentina
Danforth  Frank

SQL is case insensitive.

SeleCT family, PERSONAL from person;

id        id        id
--------  --------  --------
dyer      dyer      dyer
pb        pb        pb
lake      lake      lake
roe       roe       roe
danforth  danforth  danforth

We specify what order columns are displayed in.

SELECT id, id, id FROM Person;

id        id        id
--------  --------  --------
dyer      dyer      dyer
pb        pb        pb
lake      lake      lake
roe       roe       roe
danforth  danforth  danforth

We can select all the columns in a table using *.

SELECT * FROM Person;

id        personal   family
--------  ---------  --------
dyer      William    Dyer
pb        Frank      Pabodie
lake      Anderson   Lake
roe       Valentina  Roerich
danforth  Frank      Danforth

Practice: Selecting Site names

Write a query that selects only the name column from the Site table.

Solution
SELECT name FROM Site;

name
-----
DR-1
DR-3
MSK-4