Data Retrieval with SQL

Objective: Understand how to retrieve and filter data using SQL queries.

In this lesson, we’ll focus on how to use SQL to retrieve data from a database, filter results, and perform basic data analysis. By the end of this lesson, you’ll know how to craft SELECT statements, apply filters using the WHERE clause, sort data, and perform calculations using aggregate functions.

1. Basic SELECT Statements

The SELECT statement is the foundation of SQL queries. It allows you to specify the exact columns of data you want to retrieve from a table.

• Syntax:

SELECT column1, column2, …
FROM table_name;

• Example:

SELECT first_name, last_name
FROM students;

• Explanation: This query retrieves data from the students table, specifically the first_name and last_name columns. It will return a list of all students’ first and last names from the database. If there are 100 students in the table, you’ll see 100 rows, each showing a first name and a last name.
  
  
  

2. Filtering Data with WHERE Clause

The WHERE clause is used to filter the data returned by the SELECT statement. It allows you to specify conditions that the data must meet to be included in the result set.

• Syntax:

SELECT column1, column2, …
FROM table_name
WHERE condition;

• Example:

SELECT first_name, last_name
FROM students
WHERE age > 18;

• Explanation: This query returns the first and last names of students who are older than 18. The WHERE clause filters the results to only include rows where the age column has a value greater than 18. If there are 50 students older than 18, only those 50 will be returned. This is useful for narrowing down your data to only what’s relevant.
  
  
  

3. Sorting Data with ORDER BY Clause

The ORDER BY clause allows you to sort the data returned by a query. You can sort data in ascending (default) or descending order based on one or more columns.

• Syntax:

SELECT column1, column2, …
FROM table_name
ORDER BY column1 [ASC|DESC];

• Example:

SELECT first_name, last_name
FROM students
ORDER BY last_name ASC;

• Explanation: This query retrieves the first and last names of all students, just like the previous example, but the ORDER BY clause sorts the results alphabetically by last_name in ascending order (A to Z). If two students have the same last name, their order in the result set will depend on how they appear in the database unless further sorting criteria are added.
  
  
  

4. Using Aggregate Functions

Aggregate functions perform a calculation on a set of values and return a single value. They are useful for summarizing data, such as finding totals, averages, or counts.

• Syntax:

SELECT AGGREGATE_FUNCTION(column_name)
FROM table_name
WHERE condition;

• Example:

SELECT COUNT(*)
FROM students
WHERE age > 18;

• Explanation: This query counts the number of students who are older than 18. The COUNT(*) function counts all the rows that meet the condition specified in the WHERE clause. If there are 50 students older than 18, the result will be a single number: 50. Aggregate functions like COUNT, SUM, AVG, MIN, and MAX help you quickly analyze large datasets by summarizing the data in meaningful ways.
  
  
  

5. Grouping Data with GROUP BY Clause

The GROUP BY clause groups rows that have the same values in specified columns into summary rows, such as totals or averages per group.

• Syntax:

SELECT column_name, AGGREGATE_FUNCTION(column_name)
FROM table_name
GROUP BY column_name;

• Example:

SELECT age, COUNT(*)
FROM students
GROUP BY age;

• Explanation: This query groups the students by their age and counts how many students are in each age group. The GROUP BY clause tells SQL to aggregate the data by the age column. If there are 10 students who are 18 years old, 15 students who are 19, and 25 students who are 20, the result set will show three rows—one for each age—with the corresponding student count. This is especially useful when you want to see how many records share the same characteristic.
  
  
  

6. Filtering Grouped Data with HAVING Clause

The HAVING clause is similar to the WHERE clause, but it’s used to filter the results after they’ve been grouped. It’s useful when you want to apply conditions to the aggregated data.

• Syntax:

SELECT column_name, AGGREGATE_FUNCTION(column_name)
FROM table_name
GROUP BY column_name
HAVING condition;

• Example:

SELECT age, COUNT(*)
FROM students
GROUP BY age
HAVING COUNT(*) > 5;

• Explanation: This query first groups the students by age and then filters those groups to show only the age groups that have more than 5 students. If only the 19 and 20 age groups have more than 5 students, those will be the only rows returned. The HAVING clause is used to filter groups in a way that WHERE cannot, as WHERE applies to individual rows before they are grouped.
  
  

Conclusion

By the end of this lesson, you should be able to retrieve, filter, and sort data using SQL. You’ve learned how to use the SELECT statement, filter with WHERE, sort with ORDER BY, and perform basic calculations with aggregate functions. These skills are essential for querying databases effectively. In the next lesson, we’ll explore SQL joins and relationships, helping you work with data across multiple tables.