What is SQL and How to Use It

What is SQL?

SQL (Structured Query Language) is the standard language for communicating with relational databases. It allows you to retrieve, filter, sort, aggregate, and combine data stored across one or more tables. Nearly every organisation that stores meaningful data uses a relational database, and SQL is the universal language for accessing it.

For data analysts, SQL is arguably the single most important technical skill. It allows you to go directly to the source of data rather than waiting for someone to extract it for you, gives you access to far larger datasets than Excel can handle, and is the foundation for more advanced analytical work in Python and data visualisation tools.

Where is SQL Used?

SQL is used to interact with relational database management systems (RDBMS). The most common ones you will encounter:

PostgreSQL: Open-source, widely used in tech companies and startups. Feature-rich and well-documented.

MySQL / MariaDB: Open-source, extremely common in web applications.

Microsoft SQL Server: Widely used in enterprise environments, especially in finance, retail, and government.

SQLite: Lightweight, file-based, used in mobile apps and for learning SQL.

BigQuery (Google): Cloud-based SQL for very large datasets (petabyte scale). Uses standard SQL syntax.

Snowflake / Redshift: Cloud data warehouses used by data-focused organisations for analytical SQL.

The SQL syntax you learn is largely transferable across all of these. Minor differences exist (date functions, string functions, some operators), but the core SELECT, FROM, WHERE, GROUP BY, JOIN patterns work the same way.

Setting Up Your SQL Environment

For learning, you have several options:

SQLiteOnline.com: A browser-based SQLite environment. No installation required. Good for beginners.

DBeaver: Free desktop application that connects to any database. Professional-grade tool used by many analysts.

DB Browser for SQLite: Free, simple desktop application for SQLite. Good for learning.

PostgreSQL (local): Install PostgreSQL and pgAdmin on your machine for a more complete environment.

Google BigQuery Sandbox: Free tier of BigQuery. Excellent for learning analytical SQL at scale.

For this course, we will use standard SQL syntax compatible with PostgreSQL and BigQuery.

Database Concepts

Understanding the structure of relational databases makes SQL much easier to learn.

Database: A collection of related tables. An e-commerce business might have a database called "orders_db".

Table: A structured set of data with rows and columns. Similar to an Excel spreadsheet but stored on a server and potentially containing millions of rows.

Row (Record): A single entry in a table. In a customers table, each row is one customer.

Column (Field): An attribute of each record. In a customers table: customer_id, name, email, signup_date.

Primary Key: A column (or combination of columns) that uniquely identifies each row. customer_id is typically a primary key.

Foreign Key: A column in one table that references the primary key of another table. In an orders table, customer_id is a foreign key linking to the customers table.

Schema: The structure definition of a database -- the list of tables and their columns, data types, and relationships.

Your First SQL Query

SQL queries follow a standard structure. The most basic query:

SELECT column1, column2
FROM table_name;

To retrieve all columns:

SELECT *
FROM customers;

To retrieve specific columns:

SELECT customer_id, name, email, signup_date
FROM customers;

SQL keywords (SELECT, FROM, WHERE, etc.) are conventionally written in uppercase, though SQL is not case-sensitive for keywords. Table and column names may be case-sensitive depending on the database system.

Every SQL statement ends with a semicolon (;).

SQL Execution Order

This is important: SQL does not execute in the order you write it. The logical execution order is:

1. FROM (identify the table)
2. WHERE (filter rows)
3. GROUP BY (group filtered rows)
4. HAVING (filter groups)
5. SELECT (choose columns and calculate aggregates)
6. ORDER BY (sort results)
7. LIMIT (limit number of rows returned)

Understanding this order helps you understand why you cannot reference a SELECT alias in a WHERE clause, and why aggregate functions go in HAVING, not WHERE.

Practical SQL Workflow for Analysts

When you receive a data question, here is a practical approach:

1. Understand the question: What is being asked? What time period? What dimensions? What metric?
2. Identify the tables: Which tables contain the relevant data?
3. Sketch the query structure: What goes in SELECT, FROM, WHERE, GROUP BY?
4. Write and run the query: Start simple, then add complexity
5. Validate the results: Does the total match known figures? Are edge cases handled?
6. Document the query: Add comments explaining what the query does

Key Takeaways

• SQL is the standard language for querying relational databases and is one of the most important skills for data analysts.
• SQL syntax is largely transferable across PostgreSQL, MySQL, SQL Server, BigQuery, and Snowflake -- the same core concepts apply everywhere.
• A database is made up of tables; tables contain rows (records) and columns (fields); tables are linked by primary and foreign keys.
• SQL executes in logical order: FROM, WHERE, GROUP BY, HAVING, SELECT, ORDER BY, LIMIT -- not in the order it is written.
• The basic query structure is: SELECT [columns] FROM [table] -- and you will build on this throughout the module.

Practice Exercise

Set up SQLiteOnline.com or a local database environment. Create a simple table and run your first queries:

CREATE TABLE products (
  product_id INTEGER PRIMARY KEY,
  name TEXT,
  category TEXT,
  price DECIMAL(10,2),
  stock INTEGER
);

INSERT INTO products VALUES (1, 'Laptop', 'Electronics', 899.99, 50);
INSERT INTO products VALUES (2, 'Mouse', 'Electronics', 29.99, 200);
INSERT INTO products VALUES (3, 'Desk', 'Furniture', 349.99, 30);
INSERT INTO products VALUES (4, 'Chair', 'Furniture', 199.99, 45);

SELECT * FROM products;
SELECT name, price FROM products;