Structured Query Language (SQL, usually pronounced S-Q-L or “sequel”) is a programming language designed for managing data within a Database Management System (DBMS) — a software that stores, manipulates, and retrieves data efficiently.
The types of operations SQL can perform are often referred to as CRUD, or “create, read, update, delete.”
- * is a special character meaning “all.”
- FROM returns tells SQL which table to read data from
- LIMIT 5 returns the number of rows returned to 5
We need to familiarize ourselves with all the tables in the database.
There are many different versions of SQL (also called SQL flavors or dialects). Examples:
- SQLite
- T-SQL
- MySQL
- PostgreSQL
- Oracle
ANSI SQL is a standardized version of SQL that serves as a common ground for all SQL dialects. It ensures that the core functionality of SQL remains consistent across different SQL implementations, making it easier for developers to work with various database systems. When working with different SQL dialects, it’s important to be aware of their specific syntax and features. However, thanks to the ANSI SQL standard, the majority of the concepts and techniques we learn in SQLite will still be applicable across various SQL implementations.
Row = record, Column = field
A database can store large amounts of data more securely and efficiently than a spreadsheet or a text file. However, unlike simply opening a spreadsheet, we actually have to “ask” for data from the database. This is what we do when we write queries in SQL.
AS will let you temporarily rename or alias these fields
Aliasing fields does not permanently change their name in the database. Aliases only exist for as long as the query does.
When using aliases, quotes are optional for simple, single-word names without spaces or special characters. However, they are recommended—or sometimes required—when the alias:
- Contains spaces (e.g., order_id AS ‘Order ID’)
- Contains special characters or reserved words (e.g., usd AS ‘Dollars ($)’)
- Is case-sensitive (e.g., order_date AS ‘OrderDate’)
Using AS is preferred for a few reasons:
- Readability:
Including AS in the query explicitly indicates that an alias is being used, making it easier for others (or yourself) to understand the query when reading it later - Consistency:
Different databases may have varying support for aliasing without the AS keyword. To ensure queries are compatible across database systems, it’s a good idea to use AS - Avoiding Errors:
Using AS can prevent potential errors caused by ambiguity, especially when column names are similar to keywords in SQL
Adding Comments – it’s often a good idea to include comments explaining the reasoning behind our code. Single-line comments in SQL are started with two dashes, like so:
— This is a single line comment.
— Each single line comment must start with two dashes.
We can also write multi-line comments by using the /* and */ characters at the start and end of a comment. This is often useful when we want to “comment out” a whole query:
/*
This is a multi-line comment.
The query below will not run because it is inside of this comment:
SELECT product_name
FROM orders
LIMIT 10;
*/
