SQL

SQL is one of the fundamental programming languages you need to learn to work with databases. When you are a data scientist in a company and you need data to perform your analysis, you usually have two options: extract it on your own or contact the IT team. Of course, the ability to extract your own data is an extremely valuable skill to have. In this course, we will teach you everything you need to know in terms of database management and creating SQL queries.

Sign up to
preview the course
for FREE!

Create a free account and start learning data science today.

create free account
Our graduates work at exciting places:
walmart
tesla
paypal
citibank
booking.com

Section 1

Introduction to Databases, SQL, and MySQL

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.

FREE Welcome to SQL
FREE Why SQL
FREE Why MySQL
FREE Introduction to Databases

Section 2

SQL Theory

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

FREE 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)

Section 3

Basic Database Terminology

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

FREE Relational Database Essentials
FREE Databases vs spreadsheets
FREE Database Terminology
FREE Relational Schemas: Primary Key
FREE Foreign Key
FREE Unique Key and Null Values
FREE Relationships

Section 4

Installing MySQL and Getting Acquainted with the Interface

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 on working with the software before you start coding in the next section.

FREE Installing MySQL
FREE Setting Up a Connection
FREE New Authentication Plugin - Creating a New User
FREE Introduction to the MySQL Interface

Section 5

First Steps in SQL

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)

FREE Creating a Database - Part I
FREE Creating a Database - Part II
FREE Introduction to Data Types
FREE String Data Types
FREE Integers
FREE Fixed and Floating-Point Data Types
FREE Other Useful Data Types
FREE Creating a Table
Show all lessons
FREE Using Databases and Tables
FREE Additional Notes on Using Tables
Show fewer lessons

Section 6

MySQL Constraints

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.

Premium course icon PRIMARY KEY Constraint
Premium course icon FOREIGN KEY Constraint - Part I
Premium course icon FOREIGN KEY Constraint - Part II
Premium course icon UNIQUE Constraint
Premium course icon DEFAULT Constraint
Premium course icon NOT NULL Constraint - Part I
Premium course icon NOT NULL Constraint - Part II

Section 7

SQL Best Practices

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.

Premium course icon Coding Techniques and Best Practices - Part I
Premium course icon Coding Techniques and Best Practices - Part II

Section 8

Loading the Data

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.

Premium course icon Loading the 'employees' Database

Section 9

SQL SELECT STATEMENT

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 on all the fundamentals of the SELECT Statement and won't let you proceed before you feel comfortable using it.

Premium course icon The SELECT Statement - Introduction
Premium course icon Setting a condition with the WHERE Clause
Premium course icon SQL Operators - AND, OR, BETWEEN, and more
Premium course icon The IN, NOT IN, LIKE, and NOT LIKE Operators
Premium course icon Wildcard Characters
Premium course icon IS NOT NULL - IS NULL
Premium course icon The ORDER BY Clause
Premium course icon Introduction to Aggregate Functions
Show all lessons
Premium course icon The GROUP BY Clause
Premium course icon The HAVING Clause
Premium course icon LIMIT
Show fewer lessons

Section 10

SQL INSERT Statement

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.

Premium course icon The INSERT Statement - Part I
Premium course icon The INSERT Statement - Part II
Premium course icon Inserting Data INTO a New Table

Section 11

SQL UPDATE Statement

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!

Premium course icon TCL's COMMIT and ROLLBACK
Premium course icon The UPDATE Statement- Part I
Premium course icon The UPDATE Statement- Part II

Section 12

SQL DELETE Statement

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 because you risk losing data that you might actually need, and second because you shouldn't confuse it with the DROP and TRUNCATE statements. This section is devoted to clarifying all these subject matters.

Premium course icon The DELETE Statement - Part I
Premium course icon The DELETE Statement - Part II
Premium course icon DROP vs TRUNCATE vs DELETE

Section 13

MySQL Aggregate Functions

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().

Premium course icon COUNT()
Premium course icon SUM()
Premium course icon MIN() and MAX()
Premium course icon AVG()
Premium course icon ROUND()
Premium course icon IFNULL() and COALESCE()
Premium course icon Another Example of Using COALESCE()

Section 14

SQL JOINs

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.

Premium course icon Introduction to JOINs
Premium course icon INNER JOIN
Premium course icon More on Using Joins
Premium course icon Duplicate Records
Premium course icon LEFT & RIGHT JOIN
Premium course icon The New and the Old Join Syntax
Premium course icon Use of JOINs - Examples
Premium course icon CROSS JOIN
Show all lessons
Premium course icon Using Aggregate Functions with JOINs
Premium course icon UNION vs UNION ALL
Show fewer lessons

Section 15

SQL Subqueries

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.

Premium course icon SQL Subqueries with IN nested inside WHERE
Premium course icon SQL Subqueries with EXISTS-NOT EXISTS nested inside WHERE
Premium course icon SQL Subqueries nested in SELECT and FROM

Section 16

SQL Self Join

In this short section you will learn how to use the SQL Self Join. This will be useful for querying hierarchical data or comparing rows within the same table.

Premium course icon SQL Self Join

Section 17

SQL Views

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.

Premium course icon Using SQL views

Section 18

Stored Routines

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.

Premium course icon Introduction to Stored Routines
Premium course icon The MySQL Syntax for Stored Procedures
Premium course icon Stored Procedures - Example - Part I
Premium course icon Stored Procedures - Example - Part II
Premium course icon Another Way to Create a Procedure in MySQL
Premium course icon Stored Procedures with an Input Parameter
Premium course icon Stored Procedures with an Output Parameter
Premium course icon Variables
Show all lessons
Premium course icon User-Defined Functions in MySQL
Premium course icon Stored Routines - Conclusion
Show fewer lessons

Section 19

Advanced SQL Topics

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.

Premium course icon Types of MySQL Variables - Local Variables
Premium course icon Session Variables
Premium course icon Global Variables
Premium course icon User-Defined vs System Variables
Premium course icon MySQL Indexes
Premium course icon The CASE Statement
MODULE 2

Programming for Data Science

This course is part of Module 2 of the 365 Data Science Program. The complete training consists of four modules, each building up on your knowledge from the previous one. In contrast to the introductory nature of Module 1, Module 2 is designed to tackle all aspects of programming for data science. You will learn how to work with relational databases and SQL, as well as how to code in Python and R. By the end of this Module you will have a versatile programming skillset.

See All Modules

Trust the other 276,000 students

Ready to start?
Sign up today for FREE!

Whether you want to scale your career or transition into a new field, data science is the number one skillset employers look for. Grow your analytics expertise and get hired as a data scientist!