Working with SQL Queries - Part 1 🔎
Recently I've been learning about SQL and it's been something that I've never gone beyond the basics of doing some select statements. So I decided to take a course on Udemy and for the next few posts I'm going to talk about what I've been learning about.
Who knows, maybe it'll help you if you're learning SQL!
So let's dive in!
Selecting (and with aggregate functions)
I'm used to using select statements, but I wanted to take things further by using aggregate functions and group by to group the results of the query together.
Here's the query in question (a table that has multiple columns):
select customer_id, sum(amount)
from payment
where staff_id = 2
group by customer_id
having sum(amount) >= 110
So let's break down the query!
Let's start with a simple select query
select customer_id
from payment
But now let's filter the results down where the staff ID is equal to 2
select customer_id
from payment
where staff_id = 2
And now we'll use an aggregate function (sum) to find the total of the amount column. But in order to use this aggregate function, we'll need to use a group by statement. So in this case, we'll group by the Customer ID.
select customer_id, sum(amount)
from payment
where staff_id = 2
group by customer_id
But what if we wanted to filter this query further? We can do so by using a having statement with the aggregate function that we used before. Which gives us the query we had at the start!
select customer_id, sum(amount)
from payment
where staff_id = 2
group by customer_id
having sum(amount) >= 110
So this query shows two columns; the Customer ID and the sum of the amount column from a payment table. However, we're only showing results where the staff ID is equal to 2 and if each customer's total sum of the amount columnn is greater than or equal to 110.
Good! Let's take a look at another query!
Inner Joins
I always get confused with joins. It's one part that always trips me up when it comes to SQL. So I thought I'd take a look at one of the easier joins first, the inner join.
select customer.customer_id, customer.first_name, customer.last_name, payment_id
from payment
inner join customer on customer.customer_id = payment.customer_id
order by payment.customer_id desc
Once again, let's break this query down.
We're selecting columns from two tables, customer (customer ID, first name, last name) and payment (payment ID).
Now we want to make the join between the two tables (customer and payment). In this case, we need to use two columns that share the same data (so we'll be using customer_id from the customer and payment tables). If the data on the columns aren't the same - then nothing will be returned from the query! (Which is why having a good naming convention is important when creating a database)
Finally, we'll order the results by the customer ID in descending order.
Putting it all together!
So now we've seen a quick example of both selecting and a join, let's create a query that's a little more tricky.
select distinct customer.last_name, sum(payment.amount)
from customer
inner join payment on customer.customer_id = payment.customer_id
group by customer.last_name
having sum(payment.amount) > 100
order by customer.last_name asc
In this query, we're selecting:
- distinct last names from the customer table
- using an aggregate function to get the sum of the amount from the payment table
We're doing an inner join on the customer ID (which is in both the customer and payment tables).
We're grouping the results by the customer last name.
We want results where the sum of a payment amount for a customer is greter than 100
And we're ordering the results by the last name in descending order.
Conclusion
Once you've got your head around certain aspects of SQL, it's not too bad in terms of understanding it. Like most programming languages, the key is constant practice if you want to fully understand it.
Make sure to keep an eye out for my next post on SQL! Where I'll be looking at queries which involve left join and math functions.
Thanks for reading! 👏
