SQL Data Analysis - Learn by Doing!

Database creation scripts. Use utilities such as WinZip / WinRar / 7-zip in order to extract the compressed content.

In this lesson we're going to learn how to perform a basic SELECT statement in order to retrieve certain columns of a specific table. This lesson also covers several guidelines regarding the way of writing a "clean" and understandable query

You may not always want to retrieve the data as is. In some cases, you may want to display your data with certain calculations, or look at what-if scenarios (for example, you may want to know what the prices would look like after adding VAT).

In many cases, the column’s heading is not descriptive enough. In addition, different operations, such as: string concatenation or mathematical calculation, will change the column’s heading to be even less readable and more difficult to understand.

Column Aliases allow us to change the headings, and make them easy to understand and meaningful

The SELECT statement by default retrieves all rows, including rows containing duplicate values. The DISTINCT keyword is used to eliminate duplicate rows and display a unique list of values.

The WHERE Clause is used to restrict the rows returned from a query. While the previous chapter explained how to extract all rows from a certain table, using the WHERE clause, we are able to restrict our query to rows that meet specific certain condition.

For example: extract the employees whose salary is higher than 5000, or the employees who work at the HR department, and so on.

The IN Operator is used to test whether a value is “in” a specified list

The BETWEEN operator is used to retrieve values based on a certain range

The LIKE operator is used to perform a wildcard searches and retrieve rows that match a certain character pattern

NULL value indicates an unavailable or unassigned value. The value NULL does not equal zero (0), nor does it equal a space (‘ ‘). Because the NULL value cannot be equal or unequal to any value, you cannot perform any comparison on this value by using operators such as ‘=’ or ‘<>’.

In order to handle comparison against NULL values, we need to use the IS NULL / IS NOT NULL operators

The purpose of the AND & OR operators is to allow filtering based on multiple conditions. Using these operators, you can combine the result of two (or more) conditions in order produce a single result

In integer division and modulus, the dividend is divided by the divisor into an integer quotient and a remainder. The integer quotient operation is referred to as integer division, and the integer remainder operation is the modulus

Interim summary

The order of records retrieved by a query is by default - undefined. In order to specify the order in which rows are displayed, we can use the ORDER BY clause. This lecture explains how to sort the query result set in an ascending and descending order, and also how to sort it by multiple columns.

The LIMIT statement allows us to limit the number of records returned from a query, as well as displaying the number of highest/lowest rows, according to a certain condition (Top N Analysis).

Scalar Functions are used to carry out operations such as: mathematical tasks on numerical data, different manipulations on string and dates values, and NULL-related operations. This lesson provides an introduction to the various functions we're going to cover in the next upcoming lessons

String functions perform an operation on a string input value, and return a string or numeric value

Numeric functions perform a calculation, usually based on input values, and return a numeric value

Datetime related functions

NULL Related functions

Summary of all we've learned so far

The CASE statement goes through list of conditions and returns a value when the first condition is met (similar to an if-then-else statement)

In this lecture we're going to learn the concept of Group Functions. As you're about to see, Group Functions operate on sets of rows to give one result per group.

In the previous lesson we saw that Group Functions treats the table as one large group of data. In many cases you need to divide the table into smaller groups, so for example instead of getting the average salary of all employees, you would rather see, for example, the average salary grouped by each department. As you're about to see, that's the purpose of the GROUP BY clause

In this short lesson we're going to learn how to group the dataset by manipulated columns

The HAVING clause allows filtering of aggregated results produced by the GROUP BY clause. In the same way you used the WHERE clause to restrict rows, you use the HAVING clause to restrict aggregated results.

In this lecture we'll summarize all the clauses we've learned so far, and discuss the difference between order of syntax and execution.

In this lecture we'll introduce the mechanics and preliminary concepts of joins in SQL

The INNER JOIN keywords select records that have matching values in both tables (in contrast to other types of Join statements, which we'll cover shortly)

You will frequently need to perform a join in which you have to get data from more than two tables. In this lecture we'll explain how

LEFT and RIGHT Joins (aka LEFT and RIGHT OUTER Joins) allow us to show all the data from one table, even if there's no match in the other table.

The FULL join (aka FULL OUTER Join) allows us to display all data from both tables, even if there's no match on the left (table 1) or right (table 2) table records

The SQL Server Self Join allows joining a table to itself

In general, a Subquery (or Inner query or a Nested Query) is a query within another SQL query.

In this lecture we'll cover Single Row Subqueries, which return zero or one row to the outer SQL statement

In contrast to Single Row Subqueries, Multiple Row Subqueries may return multiple rows to the parent query