Lesson 7.3: Querying Data with SQL

Introduction

Welcome to Lesson 7.3! Today, we will explore how to retrieve and manipulate data from databases using SQL (Structured Query Language). This lesson is crucial because, in today's data-driven world, knowing how to interact with databases is a vital skill.

Learning Objectives

By the end of this lesson, students will be able to:

• Retrieve data using the SELECT statement along with WHERE, ORDER BY, and DISTINCT clauses.
• Understand and use aggregate functions like COUNT, SUM, AVG, MIN, and MAX in queries.
• Combine data from multiple tables with JOINs to reflect relationships within the data.
• Read SQL queries and predict their resulting data sets.
• Write SQL SELECT queries that include filtering, sorting, and aggregation.

Retrieving Data with SELECT

In SQL, the SELECT statement is fundamental for querying data from a database. For instance, if we have a table named Students, and we want to display all the records, we write:

SELECT * FROM Students;

The * indicates that we want to select all columns from the Students table. However, often we might only need specific data. For example, to fetch just the names and ages of all students, we'd use:

SELECT Name, Age FROM Students;

Filtering Data with WHERE

To filter results, we use the WHERE clause. Suppose we want to find students older than 18:

SELECT * FROM Students WHERE Age > 18;

This statement retrieves all records from the Students table where the Age is greater than 18.

Sorting Data with ORDER BY

It's also possible to sort our results using the ORDER BY clause. If you want to list all students ordered by age in ascending order:

SELECT * FROM Students ORDER BY Age ASC;

To sort in descending order, simply replace ASC with DESC.

Eliminating Duplicates with DISTINCT

Sometimes, you might want to see only unique results without duplicates. The DISTINCT keyword helps with that. If we have a table of course enrollments, and we want to know all unique courses taken by students:

SELECT DISTINCT Course FROM Enrollments;

Aggregate Functions and GROUP BY

Aggregate functions perform a calculation on a set of values and return a single value. They are incredibly useful for summarizing data.

Using Aggregate Functions

Here are the commonly used aggregate functions:

• COUNT(): Counts the number of rows.
• SUM(): Adds up values.
• AVG(): Calculates the average.
• MIN(): Finds the minimum value.
• MAX(): Finds the maximum value.

For example, to count how many students are enrolled in a course:

SELECT COUNT(*) FROM Enrollments;

Grouping Results

To group the results of an aggregate function, we use the GROUP BY clause. If we want to count how many students are in each course:

SELECT Course, COUNT(*) FROM Enrollments GROUP BY Course;

This SQL statement returns a list of courses along with a count of how many students are enrolled in each course.

Combining Tables with JOINs

In databases, data is often spread across multiple tables. The JOIN clause allows us to combine rows from two or more tables based on a related column.

Types of Joins

1. INNER JOIN: Returns only the rows with matching values in both tables.
2. LEFT JOIN: Returns all rows from the left table and the matched rows from the right table. If no match, NULL values are returned for columns from the right table.
3. RIGHT JOIN: Opposite of the left join; it returns all rows from the right table and matched rows from the left.
4. FULL JOIN: Returns all rows when there is a match in one of the tables.

Example of INNER JOIN

Let's consider two tables: Students and Enrollments. To list all students along with the courses they are enrolled in, we can perform an inner join:

SELECT Students.Name, Enrollments.Course 
FROM Students 
INNER JOIN Enrollments ON Students.StudentID = Enrollments.StudentID;

Reading and Predicting Results from SQL Queries

As you start to write more complex SQL queries, it’s essential to understand how to read and predict the results. Always break down the query step by step, identifying the components:

• Identify the tables involved.
• Look at the clauses (SELECT, WHERE, ORDER BY).
• Determine what filtering and sorting will apply to the results.

For example, consider the following query:

SELECT Name, AVG(Grade) 
FROM Students 
WHERE Age > 18 
GROUP BY Name;

You can break it down as follows:

• It selects the Name and average Grade from Students where age is over 18, grouping by student names.

Conclusion

In this lesson, students learned about querying data in SQL using various techniques such as selecting specific columns, filtering results, sorting data, and utilizing aggregate functions. Also, we explored how to join tables and how to read SQL statements to predict results effectively. Mastering these skills will provide a strong foundation for managing databases and analyzing data effectively.

Study Notes

• The SELECT statement retrieves data from a database.
• Use WHERE to filter results.
• The ORDER BY clause sorts results.
• DISTINCT eliminates duplicate results.
• Aggregate functions summarize data: COUNT, SUM, AVG, MIN, MAX.
• GROUP BY works with aggregate functions to categorize results.
• JOIN clauses combine data from multiple tables based on related columns.
• Carefully read SQL queries to predict their outcome.