Working with (even more) SQL Queries - Part 2 🔎

Welcome back to part two of this fun feature. Today we're looking at (you can probably guess) even more SQL Queries!

Let's jump into one!

Age

This is something that I didn't even know that existed with SQL, calculating the age of a column in a table.

Say we have a table of payments and there is a column that has the the date of when the payment was made, but we want to know the age of that column for each row in the column.

We can do that with the following:

select payment_id, age(payment_date) as age_of_payment
from payment

With this query, the column payment (which will be called age_of_payment) will show the age of each record in years, months and days.

Handy for working out the age of a column!

Case

Case is similar to an if/else statement. Here's an example of a query using a case statement and then I'll break it down.

select customer_id,
case
when (customer_id <= 100) then 'Premium'
when (customer_id between 100 and 200) then 'Plus'
else 'Normal'
end as customer_class
from customer

A case statement starts with the phrase 'case' and ends with, well, 'end'. In between we can begin to build out what we're looking for.

Each statement we're looking at begins with 'when' and in the brackets we describe the condition we're looking for. Once we've described the condition we follow up with what the query should do with a 'then' statement.

If you look at the first 'when' statement, it reads like this:

When the customer ID is less than or equal to 100 then display 'Premium'

After the two 'when' statements, we finish the case with a 'else' statement. This is used when if the data doesn't meet the condition of a when statement, then the condition of the else statement is used.

In the example above, if the data doesn't meet the two when conditions then it will display 'Normal'.

Using case is handy if you're trying to find data that meets a specific criteria!

Here's another example:

select customer_id,
case customer_id
when 2 then 'Winner'
when 5 then 'Second place'
else 'Normal'
end as raffle_results
from customer

Notice how the case follows with the name of the column we want to apply our conditions to.

Let's expand on this again by using a sum aggregate function:

select 
sum(case price_rate
when 0.99 then 1
else 0
end) as bargains,
sum(case price_rate
when 2.99 then 1
else 0
end) as regular,
sum(case price_rate
when 4.99 then 1
else 0
end) as premium
from film

We have three sum functions which will calculate the sum from values. In this case, the values will either be 1 or 0 as defined by the case statement. So, with all the rows being either 1 or 0 as a result of the case statement, finding the sum is relatively simple.

This is a very handy way to count the number of rows that meet a certain condition!

Full outer join

I hate full outer joins. Why can't everthing just be a simple inner join? Wouldn't that be so much easier?

Well, regardless, let me (attempt) to example full outer joins.

The full outer join is a combination of the left and right join, and will display all data (even if the other table doesn't have matching rows!)

Let's take a look at an example and break it down.

select distinct *
from customer
full outer join payment
on customer.customer_id = payment.customer_id
where customer.customer_id > 300
limit 15

Okay, so this query does the following:

• Select everything (distinct) from the customer table
• A full outer join on the payment table
• The join will happen on the customer ID column (which appears on both the customer and payment table)
• We only want to display results where the Customer ID is greater than 300
• And only show 15 results

I find the fact that the full outer join will display everything even if there isn't a match is something I don't understand the use for. I understand what it does, but not it's purpose. If I'm doing a join, I'd want to make sure that the rows I'm joining match in some way.

Maths

Here is another aspect thatn I didn't know about SQL is the use of Maths.

Let's take a look at a quick example:

select round(rental_rate/replacement_cost, 2) * 100 as percent_cost
from film

This rounds the rental rate divided by the replacement cost (to two numerical places), multiplied by 100 (with the column displayed as 'percent cost')

In fact, there are so many different Mathematical functions. In fact, Microsoft has a great article about the different functions available.

Let's take a look at another example using the floor function:

select floor(rental_rate * replacement_cost) as floored_value
from film

In this example, the floor value is calculated by taking the rental rate and multiplying it by the replacement cost. The results are displayed in a column called 'floored_value'

Conclusion

We've covered some good ground here, and I'm feeling more confident with SQL as well. It's a skill I can apply to my current role (as a tester) and in my next role.

Be sure to keep a look out for more SQL related posts in the future!

Thanks for reading! 👏