The Most Important SQL Queries Every Beginner Should Know

SQL, or Structured Query Language, is an essential skill for anyone working with databases. Whether you‘re an aspiring data analyst, software engineer, or just curious about how websites and apps store information, being able to write SQL queries to retrieve and manipulate data is incredibly valuable.

In this article, we‘ll walk through the most important SQL concepts and queries that every beginner should know. We‘ll focus on practical, useful queries that you can apply to real-world datasets. Even if you‘ve never written a line of SQL before, by the end you‘ll have the knowledge and confidence to dive into your own databases and start extracting insights.

But first, let‘s make sure we understand some key concepts. A relational database stores information in tables, which you can think of like Excel spreadsheets. Tables have columns, or fields, and rows, or records. SQL lets us query these tables to find the specific data we‘re looking for.

Retrieving Data with SELECT Statements

The foundation of SQL is the SELECT statement. SELECT lets you retrieve data from one or more tables. In its simplest form, you can select all columns from a table:

SELECT *
FROM employees;

But usually you only want specific columns. You can choose them by name:

SELECT first_name, last_name, hire_date
FROM employees;

For readability, you can give columns aliases with the AS keyword:

SELECT first_name AS "First Name",
last_name AS "Last Name"
FROM employees;

Aliases are especially useful for calculations and aggregate functions (more on those later).

To filter the rows returned, add a WHERE clause with conditions:

SELECT *
FROM employees
WHERE department = ‘Sales‘ AND salary > 50000;

You can sort the results with ORDER BY:

SELECT *
FROM employees
ORDER BY last_name;

And remove duplicates with DISTINCT:

SELECT DISTINCT department
FROM employees;

Aggregate functions like COUNT, SUM, AVG, MIN, and MAX summarize data. They‘re often combined with GROUP BY, which groups rows that have the same values in specified columns:

SELECT department, COUNT(*) as num_employees
FROM employees
GROUP BY department;

To filter groups, use HAVING:

SELECT department, AVG(salary) as avg_salary
FROM employees
GROUP BY department
HAVING AVG(salary) > 60000;

Combining Data with JOINs

Storing related data across multiple tables is a key feature of relational databases. To combine this data into a single result set, we use JOINs.

The most common type is an inner join, which returns only rows that have matching values in both tables:

SELECT first_name, last_name, order_date, amount
FROM customers c
JOIN orders o ON c.customer_id = o.customer_id;

This query combines customer names from the customers table with order details from the orders table. The ON clause specifies how the two tables are related – in this case, by the customer_id field which appears in both.

If you want to include all rows from the left table (customers) whether or not they have a match in the right table, use a left join:

SELECT first_name, last_name, order_date, amount
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id;

This will return all customers, with NULL for order_date and amount for those without orders. A right join does the opposite, including all rows from the right table. A full outer join returns all rows from both tables.

You can join on multiple conditions by ANDing them together:

SELECT *
FROM order_items oi
JOIN products p ON oi.product_id = p.product_id AND oi.product_price = p.price;

A self join combines rows with other rows in the same table. This is useful for hierarchical data:

SELECT e.first_name || ‘ ‘ || e.last_name AS employee,
m.first_name || ‘ ‘ || m.last_name AS manager
FROM employees e
JOIN employees m ON e.manager_id = m.employee_id;

Subqueries: Queries Within Queries

A subquery is a SELECT statement nested inside another query. There are a few main ways to use them.

In a WHERE clause, to filter based on results from another query:

SELECT *
FROM employees
WHERE department IN (SELECT department
FROM departments
WHERE division = ‘Electronics‘);

You can use subquery results as a derived table in the FROM clause:

SELECT category, AVG(amount) AS avg_amount
FROM (
SELECT c.category, SUM(oi.quantity * oi.unit_price) AS amount
FROM order_items oi
JOIN products p ON p.product_id = oi.product_id
JOIN categories c ON c.category_id = p.category_id
GROUP BY c.category, oi.order_id
)
GROUP BY category;

A correlated subquery references columns from the outer query. It‘s evaluated once for each row:

SELECT *
FROM employees e1
WHERE salary > (
SELECT AVG(salary)
FROM employees e2
WHERE e2.department = e1.department
);

This returns employees who earn more than the average salary in their department.

The EXISTS operator checks if a subquery returns any rows:

SELECT
FROM customers c
WHERE EXISTS (
SELECT
FROM orders o
WHERE o.customer_id = c.customer_id
);

This finds customers who have placed at least one order.

Combining Result Sets

Sometimes you need to combine results from separate queries. SQL provides set operations for this.

UNION appends the results of the second query to the first, removing duplicates:

SELECT first_name, last_name
FROM employees
WHERE department = ‘Sales‘

UNION

SELECT first_name, last_name
FROM employees
WHERE department = ‘Marketing‘;

Use UNION ALL to keep duplicates.

INTERSECT returns only rows that appear in both result sets:

SELECT employee_id
FROM employees
WHERE department = ‘Sales‘

INTERSECT

SELECT employee_id
FROM employees
WHERE salary > 50000;

EXCEPT returns rows from the first query that don‘t appear in the second:

SELECT employee_id
FROM employees

EXCEPT

SELECT employee_id
FROM orders;

This finds employees who have never placed an order.

Working with Text

SQL has robust support for working with text data.

The LIKE operator lets you match patterns using wildcards. % matches any number of characters, _ matches a single character:

SELECT *
FROM products
WHERE name LIKE ‘App%‘;

This finds all products whose name starts with "App".

Useful string manipulation functions include:

• UPPER(string) and LOWER(string) to convert case
• SUBSTRING(string, start, length) to extract part of a string
• LENGTH(string) to get the number of characters
• TRIM(string) to remove leading/trailing spaces

Dates and Times

Dates and times have their own data types and functions in SQL.

To get the current date and time:

SELECT CURRENT_DATE AS date,
CURRENT_TIME AS time;

DATE_PART extracts a specific part of a date/time value:

SELECT DATE_PART(‘year‘, hire_date) AS hire_year,
DATE_PART(‘month‘, hire_date) AS hire_month,
DATE_PART(‘day‘, hire_date) AS hire_day
FROM employees;

Interval arithmetic lets you add and subtract dates:

SELECT first_name, last_name,
AGE(CURRENT_DATE, hire_date) AS "time with company"
FROM employees;

TO_CHAR lets you format dates as strings:

SELECT TO_CHAR(CURRENT_TIMESTAMP, ‘FMDay, DDth FMMonth YYYY‘) AS formatted_date;

Window Functions

Window functions perform calculations across sets of rows related to the current row. They‘re incredibly powerful for analyzing data.

Ranking functions like ROW_NUMBER, RANK, and DENSE_RANK assign numbers to rows based on their order:

SELECT last_name, salary,
RANK() OVER (ORDER BY salary DESC) AS salary_rank
FROM employees;

You can also partition data into separate groups with PARTITION BY:

SELECT last_name, department, salary,
RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS dept_salary_rank
FROM employees;

Aggregate functions like SUM and AVG can be used as window functions too:

SELECT last_name, salary,
AVG(salary) OVER (PARTITION BY department) AS avg_dept_salary
FROM employees;

This shows each employee‘s salary next to the average for their department.

Making Queries Readable with CTEs

As queries get more complex, common table expressions (CTEs) can improve readability by breaking them into named subqueries.

WITH top_customers AS (
SELECT customer_id, SUM(amount) AS total_amount
FROM orders
GROUP BY customer_id
ORDER BY total_amount DESC
LIMIT 10
)
SELECT c.first_name, c.last_name, tc.total_amount
FROM top_customers tc
JOIN customers c USING (customer_id);

This finds the top 10 customers by total order amount.

You can even define recursive CTEs, useful for hierarchical data:

WITH RECURSIVE employee_hierarchy AS (
SELECT employee_id, first_name, last_name, manager_id, 1 AS level
FROM employees
WHERE manager_id IS NULL
UNION ALL
SELECT e.employee_id, e.first_name, e.last_name, e.manager_id, eh.level + 1
FROM employees e
JOIN employee_hierarchy eh ON e.manager_id = eh.employee_id
)
SELECT first_name, last_name, level
FROM employee_hierarchy;

This CTE starts with top-level employees (those with no manager) and recursively finds their direct reports to build an employee hierarchy.

Putting It All Together

Congratulations, you now have a solid foundation in the most essential SQL queries! Of course, there are many more advanced concepts to learn, but mastering these basics will let you solve the majority of data retrieval and analysis problems you‘re likely to face.

The key is practice. Take what you‘ve learned and apply it to real datasets. Websites like Mode Analytics and SQLZoo offer sample databases to play with, along with practice problems to test your skills.

For further study, resources like the book SQL Queries for Mere Mortals and sites like Exercism provide clear explanations and hands-on practice with more advanced topics like correlated subqueries, pivoting data, and query optimization. Don‘t hesitate to search and ask questions on Stack Overflow as you dive deeper – the SQL community is very welcoming to beginners.

With dedication and curiosity, you‘ll soon be writing complex queries with confidence. The world of data awaits!