A SELECT statement creates a table. The following statement creates a single-row table with columns named first and last.

sqlite> SELECT "Ben" AS first, "Bitdiddle" AS last;
Ben|Bitdiddle

A UNION statement creates a table that consists of the rows of two tables with the same number of columns.

sqlite> SELECT "Ben" AS first, "Bitdiddle" AS last UNION
...> SELECT "Louis", "Reasoner";
Ben|Bitdiddle
Louis|Reasoner

A FROM clause specifies an existing table from which information can be drawn. The following statement creates a table that consists of the name and division columns from an existing table records.

sqlite> SELECT name, division FROM records;
Alyssa P Hacker|Computer
...
Robert Cratchet|Accounting

The special syntax SELECT * selects all columns from an existing table. It’s an easy way to display the contents of a table.

sqlite> SELECT * FROM records;
Alyssa P Hacker|Computer|Programmer|40000|Ben Bitdiddle
...
Robert Cratchet|Accounting|Scrivener|18000|Eben Scrooge

The general syntax of a SELECT statement is as follows:

SELECT [columns] FROM [tables]
WHERE [condition] ORDER BY [columns] LIMIT [limit];

• SELECT [columns]

  • Specifies the columns of our output table; [columns] is a comma-separated list of column names, and * selects all columns

• FROM [tables]

  • Specifies the existing tables from which we select columns; [tables] is a comma-separated list of table names

• WHERE [condition]

  • Filters the output table to include only rows which satisfy the [condition], a boolean expression

• ORDER BY [columns]

  • Orders the rows of the output table by the given comma-separated list of columns

• LIMIT [limit]

  • Limits the number of rows in the output table to the integer [limit]

The following SELECT statement lists all information about employees with the "Programmer" title.

sqlite> SELECT * FROM records WHERE title = "Programmer";
Alyssa P Hacker|Computer|Programmer|40000|Ben Bitdiddle
Cy D Fect|Computer|Programmer|35000|Ben Bitdiddle

The following SELECT statement lists the names and salaries of each employee in the accounting division, sorted in descending order by their salaries.

sqlite> SELECT name, salary FROM records
...> WHERE division = "Accounting" ORDER BY salary DESC;
Eben Scrooge|75000
Robert Cratchet|18000

All valid SQL statements are terminated by a semicolon.

An SQL statement may have any number of line breaks and any amount of whitespace. But keep in mind that consistent line-breaking and indentation make your code a lot easier to read!

Joining tables is a fundamental database operation.

So far, we've been able to examine and filter through the information in individual rows. But what if we want to reveal relationships between rows of the same table or with information in a different table? The tool we use for this is called a join, which involves considering every possible combination of rows from multiple tables. In SQL, a join is specified by a comma-separated list of input tables in the FROM clause of a SELECT statement.

For example, suppose we have a meetings table that records divisional meetings.

The following statement joins the records table and the meetings table:

sqlite> SELECT * FROM records, meetings;
Alyssa P Hacker|Computer|Programmer|40000|Ben Bitdiddle|Accounting|Monday|9am
Alyssa P Hacker|Computer|Programmer|40000|Ben Bitdiddle|Computer|Wednesday|4pm
Alyssa P Hacker|Computer|Programmer|40000|Ben Bitdiddle|Administration|Monday|11am
...
Robert Cratchet|Accounting|Scrivener|18000|Eben Scrooge|Administration|Monday|11am
Robert Cratchet|Accounting|Scrivener|18000|Eben Scrooge|Administration|Wednesday|4pm

There is one row in the joined table for each possible pair of a row from records and a row from meetings. Because records has 5 columns and meetings has 3 columns, the joined table has 5 + 3 = 8 columns. The columns are named Name, Division, Title, Salary, Supervisor, Division, Day, Time. Because records has 9 rows and meetings has 4 rows, there are 9 * 4 = 36 possible pairs of rows between the two tables; uncoincidentally, the joined table has 36 rows.

Sometimes, we join tables with overlapping column names. When this happens, we need to be able to disambiguate column names. The AS keyword gives an alias to a table listed in a FROM clause. Then we can use a dot expression to refer to a column in that table.

The following statement finds the name and title of Louis Reasoner's supervisor.

sqlite> SELECT b.name, b.title FROM records AS a, records AS b
...> WHERE a.name = "Louis Reasoner" AND
...> a.supervisor = b.name;
Alyssa P Hacker|Programmer

An aggregate function condenses information from multiple rows of a table into a single row. Some aggregate functions are MAX, MIN, COUNT, and SUM.

If we wanted to find the name and salary of the employee who makes the most money, we might type:

sqlite> SELECT name, MAX(salary) FROM records;
Lana Lambda|610000

The special syntax COUNT(*) counts the number of rows in a table. We can count the number of rows in records (which is the number of employees at our company).

sqlite> SELECT COUNT(*) FROM records;
9

The GROUP BY [column name] clause groups together all rows that have the same value in [column name]. Then aggregation is performed on each group so that there is exactly one row in the output table for each group.

The following statement finds the minimum salary earned in each division of our company.

sqlite> SELECT division, MIN(salary) FROM records GROUP BY division;
Computer|25000
Administration|25000
Accounting|18000

The HAVING [condition] clause filters the output table to include only the groups that satisfy the [condition].

If we wanted to find all titles that are held by more than one person, we might type:

sqlite> SELECT title FROM records GROUP BY title HAVING COUNT(*) > 1;
Programmer