Commands

📄️ SQL Query

Execution path of a query

📄️ Commands

📄️ CTE (Common Table Expressions)

A common table expression is a named temporary result set that exists only within the execution scope of a single SQL statement e.g.,SELECT, INSERT, UPDATE, or DELETE.

📄️ Subquery

Subqueries (also known as inner queries or nested queries) are a tool for performing operations in multiple steps. For example, if you wanted to take the sums of several columns, then average all of those values, you'd need to do each aggregation in a distinct step.

📄️ Views

A view can be considered a virtual table because it is composed of rows and columns of data, the results of a SELECT SQL instruction in one or more database tables. Views are great resources for organizing information from different tables to create reports.

📄️ Stored Procedures

A stored procedure is a set of SQL statements stored in a database. These statements can request data entry parameters, which are used as variables during execution, and can constitute a data output.

📄️ Triggers

Triggers are a type of stored procedure, configured to call whenever an event occurs. This trigger can be used, for example, to signalize the execution of some statements whenever new data is included in a table, or a record is edited in the table.

📄️ Indexes

An index is created by a table or view to define a field that can be used to optimize queries.

📄️ SQL Comparison Operators

Comparison operators on numerical data

📄️ SQL Logical Operators

You'll likely also want to filter data using several conditions---possibly more often than you'll want to filter by only one condition. Logical operators allow you to use multiple comparison operators in one query.

📄️ SQL Joins

It might be helpful to refer to this JOIN visualization by Patrik Spathon.

📄️ Comments

You can "comment out" pieces of code by adding combinations of characters. In other words, you can specify parts of your query that will not actually be treated like SQL code. It can be helpful to include comments that explain your thinking so that you can easily remember what you intended to do if you ever want to revisit your work. Commenting can also be useful if you want to test variations on your query while keeping all of your code intact.

📄️ Cursor

To handle a result set inside a stored procedure, you use a cursor. A cursor allows you to iterate a set of rows returned by a query and process each row individually.

📄️ SQL Aggregate Functions

| Aggregate function | Description |

📄️ SQL String Functions

| Name | Description |

📄️ SQL Date Functions

Assuming you've got some dates properly stored as a date or time data type, you can do some pretty powerful things. Maybe you'd like to calculate a field of dates a week after an existing field. Or maybe you'd like to create a field that indicates how many days apart the values in two other date fields are. These are trivially simple, but it's important to keep in mind that the data type of your results will depend on exactly what you are doing to the dates.

📄️ Window Functions

There is no official categorisation of Window Functions but based on the usage, we can briefly categorise them in 3 ways:

📄️ Performance Tuning

SQL tuning is the process of improving SQL queries to accelerate your servers performance. It's general purpose is to reduce the amount of time it takes a user to receive a result after issuing a query, and to reduce the amount of resources used to process a query.

📄️ SQL Joins

It might be helpful to refer to this JOIN visualization by Patrik Spathon.

📄️ Lab: Data Ingestion to MySQL

Process Flow

📄️ Lab: SQL Basics to Advanced Primer

Notebooks

📄️ Lab: Postgres SQL basics to advanced

Notebooks

📄️ Lab: Postgres Sales

Running Dates, String and Advanced queries in Postgres on Sales data

📄️ Lab: SQLite Basics

Working with Book dataset on SQLite database

📄️ Challenge - Yammer Advanced Analytics

Yammer is a social network for communicating with coworkers. Individuals share documents, updates, and ideas by posting them in groups. Yammer is free to use indefinitely, but companies must pay license fees if they want access to administrative controls, including integration with user management systems like ActiveDirectory.

📄️ Challenge - BrainTree SQL Code Challenge

Instructions

📄️ Challenge - Employee Analytics

Small dataset | 6 Questions

📄️ Challenge - Danny's Diner

Danny seriously loves Japanese food so in the beginning of 2021, he decides to embark upon a risky venture and opens up a cute little restaurant that sells his 3 favourite foods: sushi, curry and ramen.

📄️ Explore Further

1. SQL Cheat Sheet