The Complete SQL Bootcamp for Beginner

There are some similarity between spreadsheets and SQL databases. We will talk about SQL and why we use it to interact with the databases.

This lecture talks about retrieving the data from a database using simple SELECT FROM statement.

In order to filter certain results from being returned, we need to use a WHERE clause with constraints in the query.

SQL supports a number of useful operators, such as LIKE, BETWEEN, IN to allow us to filter results.

SQL supports a number of useful operators to do things like case-insensitive string comparison and wildcard pattern matching.

This lecture will discuss how to use IN, BETWEEN, NOT IN, NOT BETWEEN constraints to filter results.

To help with this, SQL provides a way to sort your results by a given column in ascending or descending order using the ORDER BY clause. And ORDER BY clause are commonly used with the LIMIT and OFFSET clauses, which are a useful optimisation to indicate to the database the subset of the results you care about.

We can use DISTINCT to discard duplicate rows and get the unique values.

In the database management, there are four basic and essential operations: create, read, update, and delete.

The video talks about how to install MySQL server on MacOS step by step.

You can refer to the MySQL official guide on their website.

PopSQL is a modern SQL editor for teams. It lets you write queries, organize them in folders, visualize your data, and collaborate with your team. The video talks about how to install PoPSQL and connect PoPSQL with Mysql server.

When you have new entities and relationships to store in your database, you can create a new database table using the CREATE TABLE statement.

When inserting data into a database, we need to use an INSERT statement, which declares which table to write into, the columns of data that we are filling, and one or more rows of data to insert.

To update existing rows, use the UPDATE command.

When you need to delete data from a table, you can use delete statement.

We will also discuss the difference between DELETE and DROP tables.

We can use ALTER TABLE statement to add, remove, or modify columns and table constraints.

Build the database on W3Schools example

An aggregate function performs a calculation one or more values and returns a single value. The aggregate function is often used with the GROUP BY clause and HAVING clause of the SELECT statement.

String function is used to perform on input string and return an output string or numeric value.

Date functions are used to deal with different date types.

SQL also has more advanced functions, such as CASE WHEN, IFNULL, CAST, CURRENT_USER, etc.

4 types of JOINs: left join, right join, inner join, full outer join

Primary key and foreign key to build table relationships

UNION operator is used to combine two or more result-sets.

SET operators such as UNION, UNION ALL, EXCEPT, INTERCEPT.

Build a more complex query using SQL subqueries. A subquery can be referenced anywhere a normal table can be referenced.

Subqueries are not limited to use in WHERE clause. You can use it in FROM clause

INSERT statement can use the returned result from the subquery and insert into another table.

UPDATE statement can update either single or multiple columns in a table.

We look at a real world case study and see how SQL can help to answer business questions and drive business decisions.

We can the combination of GROUP BY, ORDER BY and LIMIT to get the top customers and use BETWEEN AND statement to filter results.

To understand which region has the highest growth rate, the team can adjust their sales and marketing campaign accordingly.

Rank the channels and get their percentage contribution to the total sales.

Identify the new customers or lost customers. It allows the team to engage customers differently, in a more targeted way.

We can track the team members' sales performance so that they can work on it.

We break down the business question into 5 questions we can answer using the data we have. It shows how SQL can help us with data wrangling and how we can identify ways to increase profit, analyse customer behaviours, track performance. And we can use the insights to drive the business decisions.

Test the basic understanding of SQL knowledge

A simple pattern matching and subquery question