You’re right. SQL isn’t just for interviews—it’s the core tool in a data analyst’s daily arsenal.
This guide focuses on the 25 essential SQL queries you will use on the job to extract data, clean it, calculate metrics, and build reports. Master these, and you won’t just ace the interview; you’ll immediately be productive in your new role.
Part 1: The Basics (Retrieving and Filtering Data)
These queries are your first conversation with the database. They cover how to retrieve data and apply simple filtering logic.
| # | Query Concept | Example Clause/Statement | Analyst Focus |
| 1 | Select all columns and rows | SELECT * FROM table_name; | Understands basic retrieval. |
| 2 | Select specific columns | SELECT column1, column2 FROM table_name; | Focuses on necessary data. |
| 3 | Select distinct values | SELECT DISTINCT city FROM customers; | Identifying unique categories. |
| 4 | Filter rows (simple) | SELECT * FROM sales WHERE amount > 100; | Applying basic business logic. |
| 5 | Filter rows (multiple conditions) | SELECT * FROM orders WHERE region = 'East' AND status = 'Shipped'; | Combining multiple criteria. |
| 6 | Filter using text patterns | SELECT * FROM products WHERE name LIKE 'Laptop%'; | Searching for partial matches. |
| 7 | Filter using a list of values | SELECT * FROM users WHERE country IN ('USA', 'Canada', 'Mexico'); | Checking against predefined groups. |
| 8 | Order the results | SELECT * FROM employees ORDER BY salary DESC; | Structuring data for review. |
| 9 | Limit the number of rows | SELECT * FROM logs LIMIT 10; | Previewing large datasets. |
Part 2: Working with Aggregations and Groups
Data Analysts live in this section. You use these functions to transform raw data into key metrics (KPIs) and summaries.
| # | Query Concept | Example Clause/Statement | Analyst Focus |
|---|---|---|---|
| 10 | Calculate count | SELECT COUNT(*) FROM transactions; | Finding total records/volume. |
| 11 | Calculate sum and average | SELECT SUM(revenue), AVG(price) FROM products; | Key performance indicators (KPIs). |
| 12 | Find min and max | SELECT MIN(date), MAX(date) FROM visits; | Determining range and scope. |
| 13 | Group by a column | SELECT category, COUNT(*) FROM products GROUP BY category; | Summarizing by dimension. |
| 14 | Group with multiple aggregations | SELECT department, AVG(salary), MAX(salary) FROM employees GROUP BY department; | Comparing metrics across groups. |
| 15 | Filter groups with HAVING | SELECT product, SUM(quantity) FROM sales GROUP BY product HAVING SUM(quantity) > 1000; | Filtering after grouping (a common interview trap). |
| 16 | Combine COUNT with DISTINCT | SELECT COUNT(DISTINCT user_id) FROM events; | Calculating unique users/items. |
Part 3: Joining Tables (The Relational Core)
The ability to correctly join tables is what separates an analyst from a beginner. You must understand the data relationship and choose the right join for the job.
| # | Join Type | Example Clause/Statement | Analyst Focus |
|---|---|---|---|
| 17 | INNER JOIN | SELECT o.order_id, c.name FROM orders o INNER JOIN customers c ON o.customer_id = c.customer_id; | Returning only matched rows. |
| 18 | LEFT JOIN | SELECT c.name, o.order_id FROM customers c LEFT JOIN orders o ON c.id = o.customer_id; | Keeping all rows from the primary table (e.g., all customers, even those with no orders). |
| 19 | FULL OUTER JOIN | SELECT a.*, b.* FROM table_a a FULL OUTER JOIN table_b b ON a.key = b.key; | Returning all rows from both tables (useful for comprehensive data matching/auditing). |
| 20 | SELF-JOIN | SELECT a.employee_name, b.employee_name AS manager_name FROM employees a JOIN employees b ON a.manager_id = b.employee_id; | Joining a table to itself (e.g., finding all employees and their respective managers). |
Part 4: Intermediate/Advanced Techniques (The Polish)
These are the techniques that make your queries readable, powerful, and ready for advanced analysis. They are essential for handling multi-step logic.
| # | Query Concept | Example Clause/Statement | Analyst Focus |
|---|---|---|---|
| 21 | Using Subqueries | SELECT name FROM products WHERE product_id IN (SELECT product_id FROM sales WHERE region = 'West'); | Breaking down complex problems into smaller parts. |
| 22 | Using Common Table Expressions (CTEs) | WITH monthly_sales AS (SELECT MONTH(date) AS m, SUM(revenue) AS r FROM sales GROUP BY m) SELECT AVG(r) FROM monthly_sales; | Improving readability and calculating multi-step metrics. (A must-know!) |
| 23 | Basic Window Function (ROW_NUMBER) | SELECT *, ROW_NUMBER() OVER (PARTITION BY category ORDER BY price DESC) as rank FROM products; | Ranking items within a group (e.g., top product per category). |
| 24 | Conditional Logic (CASE statement) | SELECT order_id, CASE WHEN total > 500 THEN 'High Value' ELSE 'Standard' END AS value_segment FROM orders; | Creating new categorical flags based on existing data. |
| 25 | Calculating a Date Difference | SELECT DATEDIFF(day, start_date, end_date) AS duration FROM projects; | Working with time-series data to calculate key metrics like cycle time or duration. |
Next Steps for Mastery
Don’t just read this list; implement it. Set up a simple database with a few mock tables (Customers, Orders, Products) and write every single query.
Mastering these 25 queries will fundamentally change how fast you can work and how deeply you can analyze data. You’ll stop struggling with syntax and start focusing purely on the business problem.
Mock Database Schema
This schema is designed to model a basic e-commerce scenario, allowing you to practice joins, aggregations, filtering, and more.
| Table Name | Description | Key Columns | Other Columns |
|---|---|---|---|
| Customers | Information about registered users. | customer_id (Primary Key) | first_name, last_name, city, registration_date |
| Products | Details about items sold. | product_id (Primary Key) | product_name, category, unit_price |
| Orders | Records of sales transactions. | order_id (Primary Key) | customer_id (Foreign Key), order_date, total_amount, status |
How to Practice the 25 Queries
To get started, you can use any free SQL environment, such as SQLite, PostgreSQL, MySQL, or an online SQL sandbox.
Step 1: Create the Tables
You’ll need to run the following Data Definition Language (DDL) queries to set up the structure:
— 1. Create the Customers tableCREATE TABLE Customers (
customer_id INTEGER PRIMARY KEY,
first_name VARCHAR(50),
last_name VARCHAR(50),
city VARCHAR(50),
registration_date DATE
);
— 2. Create the Products tableCREATE TABLE Products (
product_id INTEGER PRIMARY KEY,
product_name VARCHAR(100),
category VARCHAR(50),
unit_price DECIMAL(10, 2)
);
— 3. Create the Orders tableCREATE TABLE Orders (
order_id INTEGER PRIMARY KEY,
customer_id INTEGER,
order_date DATE,
total_amount DECIMAL(10, 2),
status VARCHAR(20),
-- Define Foreign Key relationship
FOREIGN KEY (customer_id) REFERENCES Customers(customer_id)
);
Step 2: Insert Sample Data
To practice the queries, you need data! Here are some sample Data Manipulation Language (DML) queries to populate your tables:
— Insert data into CustomersINSERT INTO Customers VALUES (1, 'Alice', 'Smith', 'New York', '2024-01-15');
INSERT INTO Customers VALUES (2, 'Bob', 'Johnson', 'Los Angeles', '2024-02-20');
INSERT INTO Customers VALUES (3, 'Charlie', 'Brown', 'Chicago', '2024-03-10');
INSERT INTO Customers VALUES (4, 'Diana', 'Prince', 'New York', '2024-05-01');
INSERT INTO Customers VALUES (5, 'Eve', 'Adams', 'Miami', '2024-06-05'); -- Customer with no orders (for LEFT JOIN practice)
— Insert data into ProductsINSERT INTO Products VALUES (101, 'Laptop Pro', 'Electronics', 1200.00);
INSERT INTO Products VALUES (102, 'T-Shirt', 'Apparel', 25.00);
INSERT INTO Products VALUES (103, 'Coffee Mug', 'Home Goods', 15.50);
INSERT INTO Products VALUES (104, 'Wireless Mouse', 'Electronics', 35.00);
— Insert data into OrdersINSERT INTO Orders VALUES (1001, 1, '2024-01-16', 1225.00, 'Shipped'); -- Alice
INSERT INTO Orders VALUES (1002, 2, '2024-03-01', 50.00, 'Pending'); -- Bob
INSERT INTO Orders VALUES (1003, 1, '2024-03-05', 15.50, 'Shipped'); -- Alice (Multiple orders)
INSERT INTO Orders VALUES (1004, 4, '2024-05-10', 1235.00, 'Shipped'); -- Diana
INSERT INTO Orders VALUES (1005, 3, '2024-05-15', 35.00, 'Shipped'); -- Charlie
INSERT INTO Orders VALUES (1006, 3, '2024-06-25', 10.00, 'Cancelled'); -- Charlie (Cancelled order)
Now you have a fully functional set of tables and data. You can directly apply all 25 queries from the blog post to this schema, adapting the column and table names as needed!
Take Your SQL Practice Further
To get the most out of these 25 essential SQL queries, we’ve provided two powerful resources. First, you can immediately [Generate free dummy SQL data] using a free tool from Grafieks. This allows you to create realistic INSERT statements so you can practice every query without needing to set up a real database. Once you’ve mastered the results, see how they translate to real-world business insights. You can [Turn your data into dashboards using Grafieks]—an AI-powered BI tool that helps you visualize and analyze your SQL-generated data with ease.
