SQL - MySQL for Data Analytics and Business Intelligence

Although with some procedural elements, SQL is mainly regarded as a declarative programming language, it is nonprocedural. This means, while coding, you will not be interested in how you want the job done. The focus is on what result you want to obtain.

The Data Definition Language, DDL, is called a language, but you can think of it as a syntax, a set of statements that allow the user to define or modify data structures and objects, such as tables. Examples of the statements of the Data Definition Language are:

• CREATE
• ALTER
• RENAME
• TRUNCATE

A brief explanation on what SQL keywords are about.

We will learn about some DML statements that allow us to manipulate the data in the tables of a database:

• SELECT
• INSERT
• UPDATE
• DELETE

The Data Control Language is a syntax containing only two statements – GRANT and REVOKE. These statements allow us to manage the rights users have in a database.

A brief introduction the Transaction Control Language (TCL) and its application.

To complete our theoretical preparation, we will need to learn more database terminology. We will discuss the meaning of such terms as:

• database designer
• entity-relationship diagram
• relational and database schema
• database manipulation, management, and administration

In this video, we will introduce the concept of using primary keys in MySQL.

In this lesson, we will present the concept of using foreign keys in MySQL, as well as their relation to primary keys.

Here, we will explain what unique keys are, and will introduce you to null values in SQL.

This is a lecture in which we will try to illustrate that relationships between tables can be categorized. We will study the main types of relationships you will likely need in your workplace.

A guide for the installation of the MySQL server and MySQL Workbench.

This is a visualization that explains which MySQL features we must install and why.

A brief description of what it means to set up a connection between Workbench and the MySQL Server. 

The focus of this video is getting acquainted with the MySQL Workbench interface.

In this lesson, we will show you how to create your own database, also called schema, starting from scratch.

In this video, we will look at the MySQL Workbench interface in more detail as it can help us manage our database better. 

In this lecture, we will introduce you to the measures that characterize the various data types available in SQL.

The text format in SQL is called string. In this lecture, we will explore a few string data types in SQL.

Numbers are stored in so-called numeric data types. We will go through the most notable types in MySQL – integer, fixed-point, and floating-point data types.

In this lesson, we will compare two numeric data types in SQL – the fixed-point and the floating-point data type. 

In addition to string and numeric data types, SQL allows you to store data that represents a date, time, binary, or list of options, amongst others. In this lesson, we will cover a few noteworthy data types that you will frequently see and work with.

In this lecture, we will take our “first steps” into creating SQL tables. We will study in a bit more detail the rules allowing us to create a table. 

In this video we will explain what the purpose of using queries is. As an example, we will use a query to set a default database.

Here we will share a few notes on using data tables in MySQL.

In this section, we’ll learn how to assign constraints on tables. Our focus in this lesson will be on the primary key constraint.

You know that relationships between the tables in a database are formed through the interaction between primary and foreign keys. We’ll learn how to work with the latter in this lecture.

In this video, we will execute the query we created during the previous lesson, and then we will present a few possible ways to add a foreign key constraint in SQL.

Unique keys ensure that all values in a column (or a set of columns) are different. Just like primary and foreign keys, unique keys are implemented in SQL through a constraint – the Unique constraint.

The DEFAULT constraint helps us assign a default value to every row of a column. In this lecture, we will go through a couple of examples that will best clarify the subject.

The “not null” restriction in MySQL is applied through the NOT NULL constraint. It means that, when you insert values in the table, you cannot leave the respective field empty. And if you leave it empty, MySQL will signal an error.

In this lesson we will explain the differences between the following three elements:

• a NULL value
• a “NONE” response
• the number zero

Throughout the course, we are strictly complying with coding style rules. There are many ways you can write your SQL code, but there are only a few that are considered to be professional.

Here we’ll apply what we learned in the previous lecture in an exercise.

In the remaining part of the course, you’ll be manipulating data in MySQL. To help you achieve this goal, we have re-organized the huge ‘employees’ database script in an SQL file that you can download and run in Workbench. 

The statement you will be using almost all the time is SELECT. It is one of the most important statements in MySQL and SQL. When extracting information, SELECT goes with FROM. You are always selecting something from a certain table or other types of SQL objects. 

In the body of the query, after the SELECT - FROM structure we’ve already seen, we must add the WHERE keyword and then specify a condition. In the lectures to come, we will examine all possible types of conditions to be used after the WHERE clause.

AND is an operator that allows you to logically combine two statements in the condition code block.

In this lecture, we will see the output we can expect when declaring conditions with the OR operator. 

This video will introduce you to the logical order with which you must comply when you use both the AND and OR operators in the same WHERE block.

Using the IN operator allows for a quicker and more professional way to approach data retrieval when we must satisfy more than two conditions. 

Sometimes you will need to look for a specific pattern in a column. Technically, this requires the use of the LIKE and NOT LIKE operators in combination with parentheses within which the pattern must be indicated.

This is a brief video about using the following wildcard characters in MySQL:

• the percentage symbol
• the underscore symbol
• the star sign

The BETWEEN operator helps us designate the interval to which a given value belongs. That’s why it is always used in combination with the AND operator. 

Next on our agenda is the IS NOT NULL operator. As its name suggests, it will be used to extract values that are not null. IS NULL is simply the operator with opposite functionality.

In this video, we’ll focus on the way we work with other comparison operators in SQL – the ones derived from mathematics (=, >, >=, <, <=).

SELECT DISTINCT is the MySQL combination you need to retrieve a list from a data table that contains no duplicate values.

A separate and more detailed section about MySQL Aggregate Functions follows later in the course. Therefore, in this lesson we will briefly present the idea about using the SELECT statement with aggregate functions.

The SQL ORDER BY clause is used to sort the output in ascending or descending order. It can be used in combination with the ASC or DESC keywords respectively:

• ORDER BY… ASC
• ORDER BY… DESC

When working in SQL, results can be grouped according to a specific field or fields. Technically, this is done by using the GROUP BY clause. Remember, GROUP BY is one of the most powerful and useful tools in SQL!

Knowing how to use an alias, or an alias name, is crucial. This tool will allow you to rename a selection from your query. Doing so won’t change your result, however it can significantly clarify the analysis undertaken.

HAVING is a clause frequently implemented with GROUP BY because it refines the data in your output from records that do not satisfy a certain condition. 

In this lesson, we will make a few observations on the queries executed in the previous videos.

To end this extensive section about the SELECT statement, we will show you a few ways in which you could set a limit to the number of rows displayed in the output of a specific query. 

In this section, we are ready to explore the SQL INSERT statement in a bit more detail. More precisely, we will focus on the following structure:

• INSERT INTO… VALUES…

In this video we will show a few characteristics of the SQL INSERT statement.

Here, we will explore a very powerful feature of the SQL INSERT statement – you can use a classical SQL SELECT statement to retrieve information from a table and then insert it into another table. The SQL structure required in this case is:

• INSERT INTO… SELECT…