We start by examining the training database and briefly recap SQL the basic SELECT statement and JOINs with a quiz and exercise.

This section includes several useful exercises for querying data from multiple tables. Topics include:

• CASE statements
• Union vs Union All
• Useful string, date and convert functions

Sub queries allow you to control the data returned from your main query with the results from another query.  A typical example might be to first calculate the total that a customer has spent with you during the year, then use this query to return the customers that have spent above average.  You can also use sub queries to find duplicate data.

Window functions can be used to combine individual data with aggregate data. For example, in the same query you could display an individual’s salary, the average salary for the department/company and calculate the difference.  Window functions belong to a type of function known as a ‘set function’, which means a function that applies to a set of rows. The word ‘window’ is used to refer to the set of rows that the function works on.

During the session of the workshop we complete various exercises:

• Examples of useful sub queries
• Introduction to Window functions
  • Over() and Over(Partition by)
  • Row() v Rank()

SQL offers a number of ways to consolidate and aggregate the output of your queries. We begin by learning how to calculate the SUM, COUNT AVERAGE, MIN and MAX across the whole dataset and then look at how to write GROUP BY queries and limit these with the HAVING clause.

The key topics trained include:

• How to write aggregate queries
• COUNT vs Count(*)
• GROUP BY
• HAVING