SQL

Curriculum

• Introduction to Databases, SQL, and MySQL Free

  4 Lessons 14 Min

  Whether you are working in business intelligence (BI), data science, database administration, or back-end development, you will have to retrieve information from a server storing large amounts of data. To achieve this, you need SQL. The relational database management system we chose for this course is MySQL. We did that because MySQL is open-source, reliable, and mature. In one of the videos of this section, we will provide you with step-by-step guidance when you install MySQL Server and MySQL Workbench. The introductory part of this course pays significant attention to database theory. You will learn the meaning of terms like database, data table, data entity, record, field, relation, and more.

  Welcome to SQL Free

  Why SQL? Free

  Why MySQL? Free

  Introduction to databases Free
• SQL Theory Free

  6 Lessons 20 Min

  In this section, we study the components of the Structured Query Language, SQL. We will focus on the following syntaxes: Data Definition Language (DDL), Data Manipulation Language (DML), Data Control Language (DCL), and Transaction Control Language (TCL). In addition, we will introduce you to the concept of using SQL Keywords.

  SQL as a declarative language Free

  Data definition language (DDL) Free

  SQL keywords Free

  Data manipulation language (DML) Free

  Data control language (DCL) Free

  Transaction control language (TCL) Free
• Basic Database Terminology Free

  7 Lessons 34 Min

  We are sure that you will be many times more efficient and precise in producing your SQL queries if you are in good command of basic database terminology. At the end of the day, that's why SQL has been created: to help you store, retrieve, and manipulate data from relational databases. So, knowing exactly what a relational database is and understanding the meaning of terms like 'relational schema,' 'primary key,' 'foreign key,' 'unique key,' and 'entity relationships' is something that is crucial for giving you confidence and precision in your work with SQL.

  Relational database essentials Free

  Databases vs spreadsheets Free

  Database terminology Free

  Relational schemas - Primary key Free

  Relational schemas - Foreign key Free

  Relational schemas - Unique key and null values Free

  Relationships Free
• Installing MySQL and Getting Acquainted with the Interface

  8 Lessons 25 Min

  In this section, we will provide you with step-by-step guidance on how to install MySQL Server and MySQL Workbench, the two components you need in order to start working with MySQL. Then, you'll need to set up a connection between Workbench and the server. Of course, we will show you how to do that, and then we will conclude by making a quick tour of the MySQL Workbench interface. What you will see in these lectures will be more than enough of a preparation for working with the software before you start coding in the next section.

  Installing MySQL

  Additional note – Installing – Visual C

  Installing MySQL on macOS and Unix systems

  The Client-Server Model

  Setting up a connection

  Important - Please read

  New Authentication Plugin - Creating a New User

  Introduction to the MySQL interface
• First Steps in SQL

  11 Lessons 40 Min

  It is time to create your first database and make your first steps in SQL. In this section, we will introduce you to string, fixed- and floating-point, and other useful data types. You will learn how to create a database table and how to use such a table. Not only that, but we will also introduce the different types of constraints that can be assigned to tables (primary key, foreign key, unique key, default, not null, and other types of constraints)

  Creating a Database - Part I

  SQL files

  Creating a Database - Part II

  Introduction to data types

  String data types

  Integers

  Fixed and floating-point data types

  Other useful data types

  Creating a table

  Using databases and tables

  Additional notes on using tables
• MySQL Constraints

  7 Lessons 33 Min

  So far, you've been acquainted with crucial theoretical topics and fundamentals in coding in MySQL. In this section, both will come together to show you how primary keys, foreign keys, and unique keys are applied in practice through MySQL Constraints. Once you have mastered these three, we will continue with other types of constraints that you will encounter daily in your work, such as the DEFAULT Constraint and the NOT NULL Constraint.

  PRIMARY KEY constraint

  FOREIGN KEY constraint - Part I

  FOREIGN KEY constraint - Part II

  UNIQUE Constraint

  DEFAULT Constraint

  NOT NULL Constraint - Part I

  NOT NULL Constraint - Part II
• SQL Best Practices

  2 Lessons 10 Min

  There are many ways you can write your SQL code, but there are only a few that are considered professional. In this part of the course, we will teach you how to write professional code and how to adhere to professional best practices. To reinforce what you have learned, we will wrap up this section with an easy-to-understand practical example.

  Coding techniques and best practices - Part I

  Coding techniques and best practices - Part II
• Loading the Data

  1 Lesson 2 Min

  One of the best features of our SQL training is that it uses a real-life database – the “Employees” database. We will use it to manipulate data in MySQL in all lessons. In this chapter, you will download the SQL file and will run it in Workbench.

  Loading the 'employees' database
• SQL SELECT STATEMENT

  20 Lessons 70 Min

  When doing analysis with SQL, regardless of whether in that moment you are working on the administration of your relational database, on a business intelligence analysis, or are doing some preliminary work in the data science field, you will be constantly dealing with the SQL SELECT Statement. We cannot stress enough how important it is to have completely internalized the structure of this particular SQL statement. That's why, in this section, we will make sure you will learn and practice all the fundamentals of the SELECT Statement. We won't let you proceed before you feel comfortable using it.

  SELECT… FROM…

  WHERE

  AND

  OR

  Operator Precedence

  IN - NOT IN

  LIKE - NOT LIKE

  Wildcard Characters

  BETWEEN… AND…

  IS NOT NULL - IS NULL

  Other Comparison Operators

  SELECT DISTINCT

  Introduction to Aggregate Functions

  ORDER BY

  GROUP BY

  Using Aliases (AS)

  HAVING

  WHERE vs HAVING - Part I

  WHERE vs HAVING - Part II

  LIMIT
• SQL INSERT Statement

  3 Lessons 12 Min

  Apart from delivering the segment of the data you need from your database through the SELECT Statement, SQL also gives you the chance to manipulate your data. The Data Manipulation Language, DML, is the part of the SQL syntax that contains the commands that will allow you to do that. In this section, we will focus on the INSERT Statement, which is obviously about inserting data to a table from your database.

  The INSERT statement - Part I

  The INSERT statement - Part II

  Inserting Data INTO a New Table
• SQL UPDATE Statement

  3 Lessons 9 Min

  Still on the subject of manipulating your data set, in this section we will focus on another DML Statement: the UPDATE Statement. Sometimes you won't need to insert new data, but you will want to correct or update some of the existing data in your database. That's why you have the UPDATE Statement, which is there just for that!

  TCL's COMMIT and ROLLBACK

  The UPDATE Statement - Part I

  The UPDATE Statement - Part II
• SQL DELETE Statement

  3 Lessons 8 Min

  A bit trickier than the SQL INSERT and UPDATE Statements is the SQL DELETE Statement. Yes, it definitely is about removing information from your data set, but you must be prudent when using it. First, you risk losing data that you might actually need, and second, you shouldn't confuse it with the DROP and TRUNCATE statements. This section is devoted to clarifying all these subject matters.

  The DELETE Statement - Part I

  The DELETE Statement - Part II

  DROP vs TRUNCATE vs DELETE
• MySQL Aggregate Functions

  8 Lessons 19 Min

  Aggregate functions come in handy when we want to perform some arithmetic operations with the data in our database. The most commonly-used aggregate functions in SQL are COUNT(), SUM(), MIN(), MAX(), and AVG().

  COUNT()

  SUM()

  MIN() and MAX()

  AVG()

  ROUND()

  COALESCE() - Preamble

  IFNULL() and COALESCE()

  Another Example of Using COALESCE()
• SQL JOINs

  15 Lessons 73 Min

  Joins are one of the most powerful and frequently-used tools in SQL. This is a tool you will need when combining the information from two or more tables. After completing this section, you will be able to use inner, left, right, and cross joins.

  Introduction to JOINs

  INNER JOIN - Part I

  INNER JOIN - Part II

  A Note on Using Joins

  Duplicate Records

  LEFT JOIN - Part I

  LEFT JOIN - Part II

  RIGHT JOIN

  The new and the old join syntax

  JOIN and WHERE Used Together

  CROSS JOIN

  Using Aggregate Functions with Joins

  JOIN more than two tables in SQL

  Tips and tricks for joins

  UNION vs UNION ALL
• SQL Subqueries

  3 Lessons 17 Min

  Here, you will learn how to write subqueries, a key SQL tool for more advanced users. The section also includes a number of useful tips and tricks and aims to take your SQL skills to the next level.

  SQL Subqueries with IN nested inside WHERE

  SQL Subqueries with EXISTS-NOT EXISTS nested inside WHERE

  SQL Subqueries nested in SELECT and FROM
• SQL Self Join

  1 Lesson 8 Min

  Joins are one of the most powerful and frequently-used tools in SQL. This is a tool you will need when combining the information from two or more tables. After completing this section, you will be able to use inner, left, right, and cross joins.

  SQL Self Join
• SQL Views

  1 Lesson 7 Min

  The focus of this part of the course is the SQL View. A view in SQL is a virtual table whose contents are obtained from an existing table or tables. Using this, SQL Tools has several advantages that will all be discussed in this lecture.

  Using SQL views
• Stored Routines

  10 Lessons 45 Min

  Stored routines are a set of SQL statements that have been pre-written and stored on a server, allowing users to re-run them at a later stage. You will learn how to create your own stored procedures and functions.

  Introduction to Stored Routines

  The MySQL syntax for stored procedures

  Stored Procedures - Example - Part I

  Stored Procedures - Example - Part II

  Another Way to Create a Procedure in MySQL

  Stored Procedures with an Input Parameter

  Stored Procedures with an Output Parameter

  Variables

  User-defined functions in MySQL

  Stored routines - conclusion
• Advanced SQL Topics

  8 Lessons 41 Min

  In the last part of the training, you will learn advanced SQL topics like local variables, session variables, global variances, MySQL triggers, MySQL indexes, and the CASE Statement.

  Types of MySQL Variables - Local Variables

  Session Variables

  Global Variables

  User-Defined vs System Variables

  MySQL Triggers - Preamble

  MySQL Triggers

  MySQL Indexes

  The CASE Statement