SQL & Database Design A-Z™: Learn MS SQL Server + PostgreSQL
This is a subtitle. Get more detailed about your course here!
Are you interested in a career in Data Science or Data Analytics?
In that case, inevitably you are going to encounter databases in your work.
But how do you interact with databases?
The answer is simple: SQL
SQL stands for Structured Query Language and this is one of the main tools used to organize databases, input data into them and extract it on request.
In this course you will learn how to create queries in a popular variation of SQL called PostgreSQL.
And even if at your workplace you are using a different variation (e.g. Oracle, SQL Server or MySQL), you will find that the skills you learn in this course are easily transferable.
But there are many SQL courses out there, so the question is:
What makes this course stand out?
The unique advantage of this course is that in addition to learning SQL you will also master the concepts of Database Design.
We will cover off topics such as:
- OLAP vs OLTP databases (Online Analytics Processing & Online Transaction Processing): you will understand exactly how and why the designs of these two types of Databases differ
- Normalization of Databases: we will show you the theory behind normalization AND together we will practice how to normalize a Database step-by-step
Why is that important?
Knowing how databases are designed is not a compulsory skill to have for a Data Scientist / Analyst. However, it's a HUGE added benefit.
These skills will allow you to better interact with databases and derive results and extract insights from your data faster.
This course is designed with the Data Scientists and Analysts in mind, so if you want to propel your Data Science career, then this course is for you!
We look forward to seeing you inside,
Kirill & Ilya
Who this course is for:
- Anybody who wants to learn PostgreSQL
- Anybody who wants to better understand how databases work
- Anybody who wants to enhance their Data Science career
Course Curriculum
-
1
Introduction
-
Intro video
-
-
2
Installation
-
Welcome to this Section! This is what you will learn
-
Installation: PostgreSQL & PgAdmin 4 for Windows
-
Installation: PostgreSQL & PgAdmin 4 for Mac
-
Installation: MS SQL Server & MS SQL Server Management Studio (SSMS) for Windows
-
-
3
Preparation
-
Welcome to this Section! This is what you will learn
-
The Challenge Investigating Consumer Complaints
-
Upload Consumer Complaints PostgreSQL
-
Getting to know PgAdmin
-
Upload Consumer Complaints MS SQL
-
Getting to know MS SQL Server
-
-
4
Basics of SQL
-
Welcome to this Section! This is what you will learn
-
Theory Select Query
-
Time to Write Your First Query PostgreSQL
-
Adding a Filter Using WHERE PostgreSQL
-
Logical Conditions AND, OR PostgreSQL
-
Using Wildcards % and _ PostgreSQL (advanced tutorial)
-
Comments in SQL PostgreSQL
-
Time to Write Your First Query MS SQL
-
Adding a Filter Using WHERE MS SQL
-
Logical Conditions AND, OR MS SQL
-
Using Wildcards MS SQL (advanced tutorial)
-
Comments in SQL MS SQL
-
Quiz 1 - Basics of SQL
-
-
5
Working With Data
-
Welcome to this Section! This is what you will learn
-
The Challenge Analyzing Console Games Sales
-
Uploading Console Games Data Set PostgreSQL
-
Working With Arithmetic Data Types PostgreSQL
-
ORDER BY PostgreSQL
-
Working with Varchars PostgreSQL
-
Working with Dates PostgreSQL
-
Implicit Data Conversion in SQL
-
Working with NULL
-
Uploading Console Games Data Set MS SQL
-
Working With Arithmetic Data Types MS SQL
-
ORDER BY MS SQL
-
Working with Dates MS SQL
-
Working with Varchars MS SQL
-
Implicit Data Conversion in MS SQL
-
Working with NULL MS SQL
-
Quiz 2 - Working With Data
-
-
6
Fundamentals of Database Theory
-
Welcome to this Section! This is what you will learn
-
Structured VS Unstructured Data
-
Elemetns of a Database
-
Relational Keys
-
Relational Database
-
Functional Dependency
-
-
7
Joining Tables in SQL
-
Welcome to this Section! This is what you will learn
-
Types of Joins
-
Duplicate Joins
-
Joining on Multiple Fields
-
The Challenge The Vet's Clinic
-
Upload the Data PostgreSQL
-
Your First Left Outer Join PostgreSQL
-
Joins Tips & Tricks PostgreSQL
-
Inner Join PostgreSQL
-
Full Outer Join PostgreSQL
-
Joining on Multiple Fields (Practice) PostgreSQL
-
Complex Joins PostgreSQL
-
Cleaning Up Your Join PostgreSQL
-
Cross Join (advanced tutorial) PostgreSQL
-
Upload the Data MS SQL
-
Your First Left Outer Join MS SQL
-
Joins Tips & Tricks MS SQL
-
Inner Join MS SQL
-
Full Outer Join MS SQL
-
Joining on Multiple Fields (Practice) MS SQL
-
Complex Joins MS SQL
-
Cleaning Up Your Join MS SQL
-
Cross Join (advanced tutorial) MS SQL
-
-
8
Creating Tables in SQL
-
Welcome to this Section! This is what you will learn
-
Create Database PostgreSQL
-
Create Table PostgreSQL
-
Insert Data PostgreSQL
-
Alter Table PostgreSQL
-
Drop Table PostgreSQL
-
Create Database MS SQL
-
Create Table MS SQL
-
Insert Data MS SQL
-
Alter Table MS SQL
-
Drop Table MS SQL
-
Quiz 3 - Creating Tables in SQL
-
-
9
Database Design
-
Welcome to this Section! This is what you will learn
-
What is Database Normalization
-
Prime and non-prime attributes
-
First Normal Form (1NF)
-
Second Normal Form (2NF)
-
Third Normal Form (3NF)
-
The Challenge Online Clothing Store (OLTP Database)
-
Upload the Data PostgreSQL
-
Normalization 1NF Investigation PostgreSQL
-
Normalization 2NF Investigation PostgreSQL
-
Normalization 2NF Modification PostgreSQL
-
Normalization 3NF Investigation PostgreSQL
-
Normalization 3NF Modification PostgreSQL
-
Upload the Data MS SQL
-
Normalization 1NF Investigation MS SQL
-
Normalization 2NF Investigation MS SQL
-
Normalization 2NF Modification MS SQL
-
Normalization 3NF Investigation MS SQL
-
Normalization 3NF Modification MS SQL
-
About the instructor
IT Manager
Eric Chu
A proven and experienced IT Manager, customer-focused and equipped with a technical background. I have over 8 years’ experience in IT Service Delivery Management and leading small to medium crossfunctional teams, and over 15 years of experience in IT Training and Sales.Through my passion for technology, science, and innovation, I have fostered extensive practical knowledge of emerging technologies, complex networks, and data centre eco-systems.I have had the opportunity to work across the Asia Pacific region as an experienced Business Development Manager and IT Project Manager delivering end to end solutions.