Language Structure

When writing SQL, you write queries which you can think of as questions or requests that you make to your database to answer these requests. With SQL, you can answer any conceivable question about your data's contents. For example, a developer for a forum site might use SQL to ask things like:

  1. Do I have a registered user named xx_1wind3ddragon_xx?
  2. Give me all the comments made by xx_1wind3ddragon_xx.
  3. What is the average number of comments each user makes?
  4. How many logins were there today?
  5. Give me all the users.

Those are just some examples.

Statements and Clauses

Here is a simple SQL query on the daters table we introduced in the previous lesson.

SELECT age
FROM daters
WHERE name = 'Ben';

The intent of the query is obvious and intuitive, almost an English sentence. Don't be fooled. SQL follows a very strict grammar. Let's break it down.

Clause Expression height_cm Keyword SELECT Statement Clause Expression daters Keyword FROM Clause Expression name = ‘Ben’ Keyword WHERE

A query is a statement with several clauses, in this example 3 clauses. Each clause is denoted by a keyword, and has an expression (sometimes multiple expressions). The keywords are a reserved set of words that you don't need to memorize. Look them up again when you need them, and you'll naturally start to remember them.

What are all the keywords?

There are a ton of keywords, far too many to memorize. You can find them all listed here: Postgres Keywords Appendix.

The clause with the SELECT keyword is called the "select clause". We also have a "from clause" and a "where clause". Statements are a sequence of clauses in a specific order, e.g. the FROM clause must be after the SELECT clause. Changing the order of these clauses would make the statement invalid. The query must end with a semicolon. This strict structure is what puts the "structured" in "Structured Query Language." Roughly, each clause represents an operation in relational algebra, i.e. each one alters the input table in some way.

We'll dive into each clause and what it does in the next chapter. For now, pat yourself on the back! You know the basic structure of SQL, which is the language for expressing relational algebra operations on tables. Tables and relational algebra are part of the relational model, which is the most popular way to structure data in a database.

© 2022 Andrew Carlson. All rights reserved.