Applying the WHERE Clause: SQL Challenges

Now that we have learned about the WHERE clause in SQL, it's time to put our knowledge to practical use by solving two real-world challenges.

Challenge 1: Finding Payments by a Specific Customer

The marketing team at Green Cycles is planning an event where they want to give a special gift voucher to a specific customer. The plan is to award customer ID 100 with a voucher worth $1 per payment they have made. To determine the total number of payments made by this customer, we need to write an SQL query that counts their transactions.

Query to Solve Challenge 1:

SELECT COUNT(*) AS total_payments 
FROM payment 
WHERE customer_id = 100;

Explanation:

• We use SELECT COUNT(*) to count the total number of payments.
• The FROM payment specifies that we are querying from the payment table.
• The WHERE customer_id = 100 filters the results to include only payments made by the customer with ID 100.

Challenge 2: Finding the Last Name of a Customer Named "Erica"

The support team has requested the last name of a customer whose first name is "Erica." To get this information, we need to retrieve the last name of any customer with the first name "Erica" from the customer database.

Query to Solve Challenge 2:

SELECT last_name 
FROM customer 
WHERE first_name = 'Erica';

Explanation:

• SELECT last_name retrieves the last name of the customer.
• The FROM customer specifies that we are querying the customer table.
• The WHERE first_name = 'Erica' ensures that only customers with the first name "Erica" are included in the results.

Executing Multiple Queries in PGAdmin

In PostgreSQL's PGAdmin, we can execute multiple queries at once. Simply separate the queries with a semicolon (;), as shown below:

SELECT COUNT(*) AS total_payments 
FROM payment 
WHERE customer_id = 100;

SELECT last_name 
FROM customer 
WHERE first_name = 'Erica';

Running both queries together allows us to retrieve the needed information in a single execution.

Conclusion

By applying the WHERE clause effectively, we can filter data and answer specific business queries efficiently. These SQL skills are essential for handling data analytics tasks in any organization. Stay tuned for more advanced SQL concepts and best practices!