Lesson 6.3: Querying Data with SQL

Introduction

In this lesson, we will explore how to retrieve data from a database using SQL, focusing on the SELECT statement and various techniques to filter, sort, and aggregate data. By the end of this lesson, students will be able to write SQL queries that efficiently retrieve desired data from a relational database.

Learning Objectives

• Understand how to retrieve data with SELECT, including WHERE, ORDER BY, and DISTINCT.
• Learn about aggregate functions such as COUNT, SUM, AVG, MIN, and MAX, along with the GROUP BY clause.
• Gain insight into combining tables with JOINs to create queries that address questions spanning multiple relationships.
• Develop skills to read a query and predict its result set.
• Write SQL SELECT queries featuring filtering, sorting, and aggregation.

Understanding the SELECT Statement

The SELECT statement is the foundation of querying in SQL. It allows users to specify exactly which data they want to retrieve from a database.

Basic Syntax

The basic syntax of a SELECT statement is as follows:

SELECT column1, column2, ...
FROM table_name;

Example 1: Selecting Data

Consider a table named Students with the following columns: ID, Name, Age, and Grade.

To retrieve all records from the Students table, you would write:

SELECT *
FROM Students;

The asterisk (*) signifies that you want to select all columns from the Students table. The output will display all rows and columns in the table.

Filtering Data with WHERE

In many cases, you may want to filter the results to include only specific records. The WHERE clause allows us to specify conditions that the data must meet.

Syntax

The syntax for the WHERE clause is:

SELECT column1, column2, ...
FROM table_name
WHERE condition;

Example 2: Filtering Results

Suppose we want to select only the students who are 18 years old:

SELECT *
FROM Students
WHERE Age = 18;

This query will return only those rows where the Age column is equal to 18. It's important to use the correct comparison operators, like =, >, <, >=, and <=, when formulating conditions.

Common Misconception

A common misconception is that the WHERE clause can be applied to aggregate results. However, WHERE must be used before any aggregation takes place. For aggregate conditions, HAVING is the appropriate clause (as we will see later).

Ordering Results with ORDER BY

Often, it is necessary to present data sorted in a specific order. The ORDER BY clause helps in organizing the result set according to one or more columns.

Syntax

The syntax is as follows:

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

• ASC denotes ascending order (default).
• DESC denotes descending order.

Example 3: Sorting Results

To sort the students by their Age in descending order, you would write:

SELECT *
FROM Students
ORDER BY Age DESC;

This query returns all students, sorted from oldest to youngest. If you wanted it in ascending order, you would replace DESC with ASC or omit it altogether, as it is the default.

Using DISTINCT to Avoid Duplicates

When querying data, sometimes you encounter duplicate records in your result set. The DISTINCT keyword helps in fetching unique values.

Syntax

The syntax for using DISTINCT is:

SELECT DISTINCT column1, column2, ...
FROM table_name;

Example 4: Fetching Unique Values

If you only want to see unique grades from the Students table, you could write:

SELECT DISTINCT Grade
FROM Students;

This query results in a list of unique grades without repetitions.

Aggregate Functions

Aggregate functions perform calculations on a set of values and return a single value. Common aggregate functions include:

• COUNT() - counts the number of rows.
• SUM() - sums the values in a numeric column.
• AVG() - computes the average of a numeric column.
• MIN() - finds the minimum value in a column.
• MAX() - finds the maximum value in a column.

Example 5: Using COUNT

Consider our Students table again. To count the total number of students, you would write:

SELECT COUNT(*)
FROM Students;

This will give you the total number of entries in the Students table.

Example 6: Using SUM and AVG

If you want to get the total age of all students, use:

SELECT SUM(Age)
FROM Students;

To find the average age:

SELECT AVG(Age)
FROM Students;

These queries will return the total and average age, respectively.

GROUP BY Clause

The GROUP BY clause is used in collaboration with aggregate functions to arrange identical data into groups. It allows us to apply aggregate functions on each group of data.

Syntax

The syntax is:

SELECT column1, aggregate_function(column2)
FROM table_name
GROUP BY column1;

Example 7: Grouping Results

If we want to count how many students there are for each grade, you would write:

SELECT Grade, COUNT(*)
FROM Students
GROUP BY Grade;

This will provide the number of students in each grade, displaying results in groups based on the Grade column.

Common Misconception

It's important to note that when using GROUP BY, all selected columns must either be included in the GROUP BY clause or be used as an argument in an aggregate function. Failing to follow this rule leads to SQL errors.

Joining Tables

To create more powerful queries, SQL allows you to join multiple tables based on relationships. The most common type of join is the INNER JOIN.

Syntax

The basic syntax for an inner join is:

SELECT columns
FROM table1
INNER JOIN table2
ON table1.common_column = table2.common_column;

Example 8: Joining Tables

Consider a table Courses that stores course details, with ID and CourseName, and a table Enrollments that links students to their courses by StudentID and CourseID. To find which students are enrolled in each course, you would write:

SELECT Students.Name, Courses.CourseName
FROM Students
INNER JOIN Enrollments ON Students.ID = Enrollments.StudentID
INNER JOIN Courses ON Enrollments.CourseID = Courses.ID;

This query merges data from Students, Enrollments, and Courses to display the names of students along with the course names they are enrolled in.

Conclusion

In this lesson, we have covered fundamental techniques for querying data using SQL. We explored the SELECT statement, methods for filtering and sorting data, the use of aggregate functions, and how to join multiple tables to enrich the result set. students should feel more confident writing SQL queries that can retrieve meaningful information from databases. The ability to harness these querying techniques is essential for effective data management and analysis.

Study Notes

• The SELECT statement retrieves data from a table.
• Use WHERE to filter records based on conditions.
• The ORDER BY clause sorts results in ascending or descending order.
• DISTINCT ensures unique records in the result set.
• Aggregate functions summarize data: COUNT(), SUM(), AVG(), MIN(), and MAX().
• GROUP BY arranges identical data into groups for aggregate functions.
• JOIN combines rows from two or more tables based on a related column between them.