The SQL language and your tools

One language, many dialects

SQL (Structured Query Language, said "S-Q-L" or "sequel") became an ANSI standard in 1986 and an ISO standard in 1987. The standard is still updated; the latest version is SQL:2023. Every product follows the standard for the core (SELECT, WHERE, JOIN, GROUP BY) and adds its own extras. These versions are called dialects.

SQL is declarative: you describe the result you want ("customers in Pune"), not the steps to get it. The database works out how to find the rows. That is why the same short query works on 10 rows and on 10 crore rows.

The five statement families

Some books put SELECT inside DML instead of giving it its own family; both groupings are common in interviews. SQLite has no users or passwords, so it has no GRANT or REVOKE. You will meet DCL with PostgreSQL in module 11 and TCL in module 8.

The small rules of writing SQL

• A semicolon ; ends a statement. With one statement it is often optional, but with several statements in a row it is required. Always add it.
• Keywords are not case-sensitive: select, SELECT and Select are the same. The common style is keywords in CAPITALS and names in lower_case.
• Data is case-sensitive: WHERE city = 'pune' finds nothing when the value is stored as 'Pune'.
• Text values go in single quotes: 'Pune'. A single quote inside text is doubled: 'Raj''s Cafe'.
• Double quotes are for identifiers, which are names of tables and columns: "order date" (a name with a space). MySQL uses backticks by default.
• Spaces and new lines do not matter. Put each clause on its own line so queries are easy to read.

Comments and aliases

-- A comment runs to the end of the line
/* A block comment
   can span many lines */
SELECT name  AS product_name,   -- AS gives the column a new label
       stock AS units_left
FROM products;

An alias is a temporary name given with AS. It changes the heading in the result, not the table. Module 2 uses aliases a lot.

Your tools

• This site: SQLite runs inside your browser. Nothing to install, and every task in this course runs here.
• SQLite on your computer: the sqlite3 command-line program, or DB Browser for SQLite, a free window-based app.
• PostgreSQL: a free, powerful server database. psql is its command-line tool; pgAdmin is its free graphical tool.
• MySQL: very widely used, for example by WordPress. MySQL Workbench is its free graphical tool.
• DBeaver Community: one free app that connects to SQLite, PostgreSQL, MySQL, SQL Server and many more.

Common mistakes

• Missing semicolons between statements, so the second statement becomes part of the first and you get "near ...: syntax error". End every statement with ;.
• Using double quotes for text. Use 'single quotes' for values.
• Writing -- comments in MySQL with no space after the dashes. MySQL needs "-- " (dash, dash, space).
• Copying a query from a SQL Server tutorial (TOP 3) into SQLite. Check which dialect a tutorial uses.