SQL Started kit(part2)

This will be a continuation of my first blog which you can find here:

SQL Starter kit

The last thing I mentioned was the IN/NOT IN syntax which allows you to select certain items or exclude certain ones when selecting data in your query.

We will continue with more conditional clauses as well as other Syntax and even applying Aggregations on columns. I will be using the same SQL Schema as the previous blog.

ORDER BY:

you use this when you want to order your data. Examples of this are if you want to order by Ascending or Descending. When you apply this you need to also specify which column you want to order by.

SELECT * FROM employees ORDER BY salary DESC;

In the syntax above we are ordering all the data in descending order based on the Salary column, meaning that the rows will go from highest to lowest salaries.

LIKE:

The LIKE syntax will return those entries that match a pattern. You will often use Regular expressions in cases where you use the LIKE clause

SELECT * FROM employees 
WHERE first_name LIKE 'a%'

This will only return those entries that match the pattern above which is ‘%a”. This means only those rows where firt_name starts with ‘a’ will be returned and all other rows will be ignored.

SQL AGGREGATION:

you can apply some of the most common aggregations such as MIN, MAX, COUNT, SUM, AVG, and much more on your data.

SELECT MAX(salary) FROM employees
SELECT MIN(salary) FROM employees
SELECT COUNT(job_title) FROM employees
SELECT AVG(salary)FROM employees
SELECT SUM(email) FROM employees

in the above, we are performing basic aggregations on different columns in the employee's table.

The output name for column after applying an aggregation will be the aggregation+column name like such:

• MAX(salary)
• MIN(salary)

you can set the column name to something else if you want to make it look clean and organized by including ‘AS’ after the aggregation

SELECT MAX(salary) AS max_salary, FROM employees
SELECT MIN(salary) AS min_salary, FROM employees
SELECT COUNT(job_title) AS job_count, FROM employees
SELECT AVG(salary) AS average_salary, FROM employees
SELECT SUM(email) AS total_email, FROM employees

GROUP BY:

GROUP BY will group the results of a select query based on one or more columns. To explain this one I will be using a different Dataset where the table Customers contain the ID of the customer and their country of residence.

SELECT COUNT(CustomerID), Country
FROM Customers
GROUP BY Country;

The output from the query above.

Using the above syntax we are grouping by each country and counting the number of CustomerID in each one. By default, it is ordered by Country but we can also organize by the count, we can also use many clauses alongside one another.

SELECT COUNT(CustomerID) AS count_customer_id, Country
FROM Customers
GROUP BY Country
ORDER BY count_customer_id DESC;

The output from the query above.

This will do the same as above(changed column name using AS clause to make it cleaner) the only difference is that we are applying the ORDER BY clause which will order the count_customer_id from highest to lowest instead of the country like the previous query.

We discussed more advanced clauses and showed that you can apply different aggregations to columns in order to extract the relevant information. We also discussed the GROUP BY clause and showcased the use of all the different syntax in one with the last query.

SQL Tutorial