Query Structure: Beginner

A query is a question written for the database, and the smallest useful one has 2 parts. FROM names the table to read. SELECT lists the columns to bring back, separated by commas. The database finds the table and walks every row, keeping from each one only the columns on the list. What comes back is a new table that lives for the length of the query: the same rows, only the chosen columns.

Run it and 5 rows come back, 1 per product, each with 2 values: Desk Lamp beside 24.0, Standing Desk beside 349.0, and so on down to USB Hub beside 18.75. The query did not name product_id or category, so they were read off the table and left out before the result was assembled, and in_stock went the same way. The products table is the same afterward as before, because a SELECT reads the table and never edits it, however many times it runs.

You write SELECT first and FROM second, and the database works the other way around: it has to locate the table before it can know which columns exist. Read your own queries in the database's order. Start at FROM and picture the table with its column card. Then move up to SELECT and check that every name on the list belongs to that table. A name that does not, such as a misspelled pirce, stops the query with an error saying the column cannot be resolved, before a single row is returned.

The result's columns appear in the order the SELECT list gives them, so price comes first, even though product_id is the first column in the table. The table's own column order has no hold over the result. A column can also be listed twice, which returns it twice side by side; that reads as a mistake today and becomes useful in the last section of this lesson, where 1 copy is turned into a calculation and the other is kept as it is.

Naming 1 column still returns 5 rows: lighting, furniture, accessories, furniture, accessories. furniture appears twice because 2 products belong to it, and the database does not collapse repeats on its own. SELECT narrows the columns, never the rows; the result has as many rows as the table until the filtering lesson adds the tool that changes that. A count of rows in a result is therefore a count of rows in the table, which is a quick sanity check when a result looks too short or too long.

A comma separates every pair of columns, and the last column carries no comma before FROM; a stray comma there is a syntax error. The keywords SELECT and FROM can be typed in any mixture of upper and lower case, so select works exactly like SELECT. Upper case is the convention because it makes the skeleton of a long query visible at a glance: keywords stand out, column names stay quiet. A query may end with a semicolon; tools that run several statements in a row use it as a separator, and a single query runs the same with or without it.

Writing a query from a request follows the same steps every time. Turn each thing the request asks for into a column name from the table's card, spelled exactly as the card spells it. List those names after SELECT in the order the request wants them, with a comma between each pair and none after the last. Put the table's name after FROM. Then read the query back in the database's order: the table first, then each name on the list checked against that table's columns.

An asterisk in place of the column list means every column of the table, in the order the table defines them. SELECT * FROM products returns all 5 columns and all 5 rows, the entire table. It is the quickest way to see what a table you have never opened holds, before you know a single column name, and the result doubles as the column card from the first section with real values filled in.

The star replaces the column list rather than 1 column on it. Writing SELECT * gives every column once; adding named columns beside it, as in SELECT *, price, returns price a second time and clutters the result. Keep the 2 styles apart: the star alone while exploring, a named list alone for the query you keep.

A wide star result is also hard to read where you are likely to read it: 80 columns wrap into unreadable lines in a terminal or on a phone, while 3 named columns fit on 1 line.

A result column takes its name from the table column it came from, and that name is often wrong for whoever reads the result. It may be too technical or too abbreviated, or the result may be headed for a tool that expects a different header. AS gives the result column a new name without touching the table. Write the column, then AS, then the name you want it to carry in the result.

An alias written as a bare word follows the same rules as a column name. It must start with a letter and may contain digits and the _ character after that. The database folds it to lower case, so AS Unit_Price comes back as unit_price. To give a result column a name with a space or a capital letter in it, wrap the alias in double quotes. The quotes make the name exactly what sits between them, spaces and capitals included, and any symbols as well, which is what a spreadsheet or a chart wants to display as a header.

Single quotes mean something different: 'Product' is a piece of text, a value, while "Product" is a name. Writing price AS 'Unit Price' fails with a syntax error because the database expects a name after AS and finds a value instead. Double quotes go around names, and single quotes go around text.

The database accepts an alias with the keyword left out, so price unit_price means the same as price AS unit_price. The cost shows up when a comma goes missing: SELECT product_name price FROM products returns 1 column holding the product names under the header price, and raises no error at all, because the database read price as an alias for product_name. Typing AS every time makes the intent visible, and when a comma is lost the missing AS makes the mistake stand out on the page.

Adding an alias to a working query follows the same path every time. Find the item on the SELECT list whose header needs to change, type AS after it, then the new header. Use a bare lowercase name, with words joined by _, when the header has no spaces, and double quotes when it has spaces or capitals. A header wrapped in single quotes is a piece of text, so it has no place after AS.

An item on the SELECT list does not have to be a column. It can be an expression: a column combined with numbers and other columns using +, -, * and /. The database evaluates the expression once per row, using that row's own values, and the results become a new column that exists only in the result. The table gains nothing and loses nothing; the query answers a question the table never stored.

Each row's price has 5 taken from it: 24.0 becomes 19.0, 349.0 becomes 344.0, 89.5 becomes 84.5, 199.0 becomes 194.0 and 18.75 becomes 13.75. The alias matters here more than anywhere else. Without it the database has to invent a header for the computed column, and the one it invents, _col1 for the second item on the list, tells the reader nothing about what the number means. Every computed column gets an AS, with a name that says what was computed.

The query reads price in 2 expressions. retail_price multiplies it by 1.5, so the Office Chair at 199.0 shows 298.5. markup repeats that product and subtracts the original price, giving 99.5 on the same row. Each expression is computed on its own from the table's columns, which is why the multiplication is spelled out twice: markup cannot refer to retail_price by its alias, and price * 1.5 AS retail_price, retail_price - price AS markup stops with an error that retail_price cannot be resolved. The reason sits in the order the database works through a query, which a later lesson lays out.

Multiplication and division happen before addition and subtraction, as in arithmetic class. price - 5 * 2 takes 10 from the price. (price - 5) * 2 takes 5 and then doubles what is left. Parentheses cost nothing, and a reader should never have to recall precedence to know what a query computes, so wrap any expression that mixes operators even when the default order happens to be the one you want.