SQL for FP&A and CFOs
Introduction to SQL
What is SQL?
Structured Query Language, or SQL, is the language used to communicate with databases. Think of a database as a massive, highly organized collection of spreadsheets, called tables. To get information out of these tables, you need to ask for it in a way the database understands. That's where SQL comes in.
SQL, or Structured Query Language, is a language to talk to databases.
An SQL command is called a query. Writing a query is like forming a specific question. You tell the database exactly what you want to see, which tables to look in, and how to present the information. The syntax is designed to be readable, almost like a sentence. Most SQL statements end with a semicolon (;). While not always required, it’s a good habit to clearly separate your commands.
Common Data Types
Every column in a database table is designed to hold a specific kind of information. This is called its data type. You can't store a name in a column meant for numbers, just as you can't park a car in a bike lane. Defining data types ensures that the information stored is consistent and reliable.
While there are many data types, a few basics cover most situations.
| Data Type | Description | Example |
|---|---|---|
INT | Whole numbers, positive or negative. | 42, -150 |
VARCHAR(n) | A string of text with a max length of n characters. | 'Hello', 'New York' |
DECIMAL(p, s) | Exact numbers, great for money. p is total digits, s is digits after the decimal. | 123.45 |
DATE | A date, like a birthday or an event. | '2024-08-23' |
BOOLEAN | Represents true or false values. | TRUE, FALSE |
Querying Data with SELECT
The most common task in SQL is retrieving data. The SELECT statement is your tool for this job. You use it to pick the columns you want to see from a table.
Imagine we have a table named customers that stores information about users.
| customer_id | first_name | last_name | city | signup_date |
|---|---|---|---|---|
| 1 | Maria | Garcia | Houston | 2023-01-15 |
| 2 | John | Smith | New York | 2023-02-20 |
| 3 | Chen | Wang | New York | 2023-03-10 |
| 4 | David | Jones | Chicago | 2023-04-05 |
To get a list of just the first and last names of all customers, you would write this query:
SELECT first_name, last_name
FROM customers;
Here, SELECT specifies the columns we want, and FROM tells the database which table to look in. If you want to see every column without typing them all out, you can use an asterisk (*) as a shortcut.
SELECT *
FROM customers;
Filtering and Sorting
Often, you don't need every single row from a table. You might only want customers from a specific city or those who signed up after a certain date. The WHERE clause lets you filter data based on conditions you set.
For example, to find all customers who live in New York, you would add a WHERE clause to your query.
SELECT first_name, last_name, city
FROM customers
WHERE city = 'New York';
This query would return only the rows for John Smith and Chen Wang. The condition city = 'New York' acts as a filter, keeping only the rows that match.
After you've selected and filtered your data, you may want to sort it. The ORDER BY clause arranges your results. By default, it sorts in ascending order (ASC), from A to Z or smallest to largest. To reverse this, you can specify descending order (DESC).
SELECT first_name, last_name, signup_date
FROM customers
ORDER BY signup_date DESC;
This query lists all customers, starting with the one who signed up most recently. You can also combine these clauses. To get customers from New York, sorted alphabetically by last name:
SELECT first_name, last_name, city
FROM customers
WHERE city = 'New York'
ORDER BY last_name;
Notice the order: SELECT, FROM, WHERE, then ORDER BY. This structure is fundamental to writing SQL queries.
What is the primary purpose of the SELECT statement in SQL?
Which query correctly retrieves all columns for customers living in 'London' from a table named customers?
With these basic commands, you have the building blocks to start exploring and retrieving data from any relational database.