Learn SQL Basics: A Beginner’s Guide to Writing Queries and Manipulating Data

Learning SQL doesn’t have to be intimidating. Whether you’re looking to fetch data, organize it, or combine information from multiple tables, SQL is the key to unlocking the power of databases. This guide walks you through fundamental SQL concepts, focusing on writing efficient queries. By the end, you'll be ready to confidently interact with databases and tackle real-world scenarios.

What is SQL?

SQL stands for Structured Query Language. It’s the primary language for interacting with databases such as SQL Server and Oracle. Databases consist of tables with rows and columns, and SQL allows you to retrieve, sort, filter, and manipulate this data. Let’s dive into learning how to build basic to intermediate SQL queries.

Fetching Data

The SELECT statement is the foundation of SQL queries for retrieving data.

Select All Columns: To fetch all records from a table, use:
```
SELECT * FROM employees;  
```
Select Specific Columns: For retrieving certain columns, list their names after SELECT:
```
SELECT employee_id, full_name FROM employees;  
```

Sorting Data

Sorting allows you to order your query results. SQL makes it simple with the ORDER BY clause.

Ascending Order (Default):

SELECT * FROM employees ORDER BY full_name;

Descending Order:

SELECT * FROM employees ORDER BY full_name DESC;

Filtering Data

Filtering narrows down results using the WHERE clause.

Filter by a Single Condition:

SELECT * FROM employees WHERE department_id = 1;

Filter by Multiple Conditions (AND/OR):

SELECT *  
FROM employees  
WHERE department_id = 1  
  AND salary > 50000;

Filter by Range (BETWEEN):

SELECT *  
FROM employees  
WHERE salary BETWEEN 50000 AND 100000;

Null and Not Null Filtering:

SELECT * FROM employees WHERE manager_id IS NULL;  
SELECT * FROM employees WHERE manager_id IS NOT NULL;

Using Wildcards for Pattern Matching

Wildcards paired with the LIKE operator let you filter results based on patterns.

Names Starting with ‘A’:

SELECT *  
FROM employees  
WHERE full_name LIKE 'A%';

Names Containing ‘A’:

SELECT *  
FROM employees  
WHERE full_name LIKE '%A%';

Distinct Values

To eliminate duplicates and get unique values, use DISTINCT.

Unique Department IDs:

SELECT DISTINCT department_id FROM employees;

Distinct Across Multiple Columns:

SELECT DISTINCT department_id, manager_id FROM employees;

Aggregating Data

SQL’s aggregation functions help summarize data across rows.

Count Records in a Table:

SELECT COUNT(*) AS total_employees FROM employees;

Group Data and Count for Each Group:

SELECT department_id, COUNT(*) AS employee_count  
FROM employees  
GROUP BY department_id;

Filter Grouped Results (HAVING):

SELECT department_id, COUNT(*) AS employee_count  
FROM employees  
GROUP BY department_id  
HAVING COUNT(*) > 3;

More Aggregates (SUM, MIN, MAX):

SELECT department_id,  
       SUM(salary) AS total_salary,  
       MIN(salary) AS min_salary,  
       MAX(salary) AS max_salary  
FROM employees  
GROUP BY department_id;

Joining Tables

Joins combine data from multiple tables by matching column values.

Inner Join (Only Matching Records):

SELECT e.employee_id, d.department_name  
FROM employees e  
INNER JOIN department d  
  ON e.department_id = d.department_id;

Left Join (All Records from Left Table):

SELECT d.department_id, d.department_name, e.employee_id  
FROM department d  
LEFT JOIN employees e  
  ON d.department_id = e.department_id;

Right Join:

SELECT e.employee_id, d.department_name  
FROM employees e  
RIGHT JOIN department d  
  ON e.department_id = d.department_id;

Subqueries

Subqueries are nested queries used to filter or compute data dynamically.

Filtering Based on a Subquery:

SELECT *  
FROM employees  
WHERE department_id IN (  
  SELECT department_id  
  FROM department  
  ORDER BY department_name DESC  
  LIMIT 2  
);

Correlated Subqueries:

SELECT *  
FROM department d  
WHERE EXISTS (  
  SELECT 1  
  FROM employees e  
  WHERE e.department_id = d.department_id  
);

Combining Queries

SQL offers operators to merge query outputs.

Union of Two Queries:

SELECT department_id FROM employees  
UNION  
SELECT department_id FROM department;

Intersection:

SELECT department_id FROM employees  
INTERSECT  
SELECT department_id FROM department;

Difference (Records in Table 1 Not in Table 2):

SELECT department_id FROM employees  
EXCEPT  
SELECT department_id FROM department;

Conditional Logic

SQL’s CASE function acts like an if-then-else statement.

Create a Conditional Column:

SELECT employee_id, salary,  
       CASE  
         WHEN salary > 90000 THEN 'High'  
         WHEN salary BETWEEN 45000 AND 85000 THEN 'Medium'  
         ELSE 'Low'  
       END AS salary_grade  
FROM employees;

Conclusion

SQL bridges the gap between raw database data and actionable insights. Whether you're fetching records, organizing them with filters, or combining tables with joins, these concepts form the backbone of any SQL journey. Once you've mastered these fundamentals, put them into practice by experimenting with exercises or platforms like LeetCode. Ready to take the next step? Start practicing today and explore how SQL will boost your data skills.

Best SQL and Data Analytics Books

T-SQL Fundamentals (By Itzik Ben-Gan) - https://amzn.to/4koKGdC
Ace the Data Science Interview - https://amzn.to/3D2ne5n