Lesson 1
Logical Operations with AND in SQL Queries
Introduction and Context Setting

Welcome to the course "Learning SQL with Online Shopping Data"! In this course, you'll learn how to use SQL to query and analyze data from a real-world dataset revolving around an online shopping environment.

We'll be working with several main tables:

  • Orders: Contains data about the orders placed by customers.
  • Customers: Contains data about the customers who place orders.

Here's a quick preview of what these tables look like:

Orders Table:

order_idcustomer_idorder_dateorder_status
1412021-08-17Delivered
2162022-04-03Processed

Customers Table:

customer_idcustomer_name
1John Doe
2Jane Smith
Logical Operators

Logical operations are vital in SQL for filtering data based on certain conditions. The main logical operators are:

  • AND: All conditions must be true.
  • OR: At least one condition must be true.
  • NOT: The condition must be false.

Here we'll focus on the AND operator, which we'll be using extensively.

Logical operators allow you to fine-tune your data queries, making it possible to extract exactly what you need from your dataset. Understanding these operations is fundamental for effective SQL querying.

Understanding Syntax and Clause Structure

Think of SQL statements as real-world phrases that you can dissect into multiple parts. Each part plays a specific role. For instance, consider:

SQL
1SELECT * 2FROM Orders 3WHERE customer_id = 1 AND order_date BETWEEN '2021-01-01' AND '2021-12-31';

This SQL query has distinct parts:

  • SELECT *: This phrase indicates that it wants to retrieve all columns.
  • FROM Orders: This phrase specifies the table from which to retrieve the data.
  • WHERE: This word starts the condition clause, which refines the query.
  • customer_id = 1 AND order_date BETWEEN '2021-01-01' AND '2021-12-31': These are the conditions that rows must meet to be included in the result.

Notice the AND operator here? It helps us set multiple conditions. Our statement tells SQL to "Show me all columns from the Orders table, but only those where the customer is customer_id 1 (customer_id = 1) and whose order_date falls within the year 2021 (order_date BETWEEN '2021-01-01' AND '2021-12-31')". We use the AND logical operator to specify that both conditions must be met. Similarly, the OR operator allows querying data that meet either one condition or another, enabling more flexible data retrieval based on varying criteria.

How the AND Operator Works

The AND operator ensures that all conditions specified in the WHERE clause must be true for a row to be included in the result set.

SQL
1SELECT * 2FROM Orders 3WHERE order_status = 'Delivered' AND customer_id = 1;

In this example:

  • SQL evaluates conditions in the order they are written in the WHERE clause.
  • SQL first checks if order_status = 'Delivered' is true for each row.
  • Then, it checks if customer_id = 1 is true for rows that passed the first condition.
  • Only rows meeting both conditions are returned.
Working with Numeric Fields Using AND/OR

Logical operators like AND and OR aren't just for boolean values. They can be used with numeric data, too. In SQL, numeric comparisons are quite straightforward: you can use = for equality, > for greater than, < for less than, >= for greater than or equal to, <= for less than or equal to, and <> or != for not equal to.

Another Example: Using Logical Operators

Just like numeric fields, we can use logical operators to work with date fields. Here is an example:

SQL
1SELECT Customers.customer_id, Customers.customer_name, Orders.order_id, Orders.order_date, Orders.order_status 2FROM Orders 3JOIN Customers ON Customers.customer_id = Orders.customer_id 4WHERE Customers.customer_name = 'John Doe' AND Orders.order_date BETWEEN '2023-01-01' AND '2023-12-31';

In this statement, we're looking for all orders from the dataset where the customer name is 'John Doe', AND the order date falls within the year 2023 (order_date BETWEEN '2023-01-01' AND '2023-12-31'). We use the AND operator to combine these two conditions, effectively filtering the data based on a specific date range along with the customer's name.

Summary and Upcoming Practice Overview

That wraps up our introductory lesson on using AND/OR logical operators in SQL. We've learned how to construct SQL queries using these operators to filter out specific data based on multiple conditions. Now that you've gained a solid understanding of using AND/OR operators, it's time to put this knowledge into practice. In the next section, you'll encounter various exercises to solve, giving you hands-on experience in using logical operators in SQL to retrieve specific data. Keep practicing and happy querying!

Enjoy this lesson? Now it's time to practice with Cosmo!
Practice is how you turn knowledge into actual skills.