Sql & Database Design A-Z™: Learn Ms Sql Server + Postgresql (updated 1/2023)

Learn Both SQL Server & PostgreSQL By Doing. Enhance Your Data Analytics Career With Real World Data Science Exercises

What you'll learn

Create basic SQL Queries

Create advanced SQL Queries

Create Left, Right, Inner and Full Outer joins

Create new tables, alter existing tables in Databases

Normalize Databases

Understand database design

Understand first, second and third normal form schemas

Requirements

A basic knowledge of computers

Description

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

Overview

Section 1: Introduction

Lecture 1 Intro Video

Lecture 2 Some Additional Resources!!

Lecture 3 Learning Paths

Lecture 4 Get the materials

Lecture 5 Your Shortcut To Becoming A Better Data Scientist!

Section 2: Installation

Lecture 6 Welcome to this section! This is what you wil learn

Lecture 7 Installation: PostgreSQL & PgAdmin 4 for Windows

Lecture 8 Installation: PostgreSQL & PgAdmin 4 for MAC OS

Lecture 9 Installation: MS SQL Server & MS SQL Server Management Studio (SSMS) for Windows

Section 3: Preparation

Lecture 10 Welcome to this section! This is what you will learn

Lecture 11 The challenge: Investigating Consumer Complaints

Lecture 12 Upload Consumer Complaints

Lecture 13 Getting To Know The PGADMIN

Lecture 14 Upload COnsumer COmplaints MS SQL

Lecture 15 Getting to know MS SQL SERVER

Section 4: Basics of SQL

Lecture 16 Welcome to this section! This is what you will learn

Lecture 17 Theory Select Query

Lecture 18 Time to write your first query in PostgreSQL

Lecture 19 Adding a filter using WHERE in PostgreSQL

Lecture 20 Logical conditions AND, OR in PostgreSQL

Lecture 21 Using wildcards % and _ (advanced tutorial)

Lecture 22 Comments in SQL

Lecture 23 Getting to write your first query MS SQL

Lecture 24 Where MS SQL

Lecture 25 Logical conditions, AND OR MS SQL

Lecture 26 Using wildcards MS SQL

Lecture 27 Comments in SQL MS SQL

Section 5: Working With Data

Lecture 28 Welcome to this section! This is what you will learn

Lecture 29 The challenge: Analyzing Console Games Sales

Lecture 30 Uploading Console Games Dataset

Lecture 31 Working with arithmetic data types

Lecture 32 ORDER BY

Lecture 33 WORKING WITH VARCHARS

Lecture 34 WORKING WITH DATES

Lecture 35 Implicit Data Conversion in SQL

Lecture 36 WORKING WITH NULL

Lecture 37 Uploading Console Games DATASET MS SQL

Lecture 38 Working with arithmetic data types MS SQL

Lecture 39 ORDER BY MS SQL

Lecture 40 Working with VARCHAR MS SQL

Lecture 41 Working with Dates MS SQL

Lecture 42 Implicit DATA conversion MS SQL

Section 6: Fundamentals of Database Theory

Lecture 43 Welcome to this section! This is what you will learn

Lecture 44 Structured vs Unstructured Data

Lecture 45 Elements Of a Database

Lecture 46 Relational Keys

Lecture 47 Relational Database

Lecture 48 Functional Dependency

Section 7: Joining tables in SQL

Lecture 49 Welcome to this section! This is what you wil learn

Lecture 50 Types of Joins

Lecture 51 Duplicates in Joins

Lecture 52 Joining on multiple fields

Lecture 53 The challenge: The Vet's Clinic

Lecture 54 Upload the data

Lecture 55 Your First Left Outer Join

Lecture 56 Joins Tips & Tricks

Lecture 57 Inner Join

Lecture 58 Full Outer join

Lecture 59 Joining on Multiple Fields (Practice)

Lecture 60 Complex Joins

Lecture 61 Cleaning up your Join

Lecture 62 Cross Join (advanced tutorial)

Lecture 63 Upload The Data - MS SQL

Lecture 64 Your First Left Outer Join - MS SQL

Lecture 65 Joins Tips & Tricks - MS SQL

Lecture 66 Inner Join - MS SQL

Lecture 67 Full Outer Join - MS SQL

Lecture 68 Joining on Multiple Fields (Practice) - MS SQL

Lecture 69 Complex Joins - MS SQL

Lecture 70 Cleaning Up Your Join - MS SQL

Lecture 71 Cross Join (advanced tutorial) - MS SQL

Section 8: Creating Tables in SQL

Lecture 72 Welcome to this section! This is what you will learn

Lecture 73 Create Database

Lecture 74 Create Table

Lecture 75 Insert Data

Lecture 76 Alter Table

Lecture 77 Drop Table

Lecture 78 Create Database MS SQL

Lecture 79 Create Table MS SQL

Lecture 80 Insert Data MS SQL

Lecture 81 ALTER TABLE MS SQL

Lecture 82 DROP TABLE MS SQL

Section 9: Database Design

Lecture 83 Welcome to this section! This is what you wil learn

Lecture 84 MS SQL Server: What is Database Normalization?

Lecture 85 Prime and non-prime attributes

Lecture 86 First Normal Form (1NF)

Lecture 87 Second Normal Form (2NF)

Lecture 88 Third Normal Form (3NF)

Lecture 89 The challenge: Online Clothing Store (OLTP Database)

Lecture 90 Upload the data

Lecture 91 Normalization 1NF Investigation

Lecture 92 Normalization 2NF Investigation

Lecture 93 Normalization 2NF Modification

Lecture 94 Normalization 3NF Investigation

Lecture 95 Normalization 3NF Modification

Lecture 96 Upload The Data - MS SQL

Lecture 97 Normalization 1NF Investigation - MS SQL

Lecture 98 Normalization 2NF Investigation - MS SQL

Lecture 99 Normalization 2NF Modification - MS SQL

Lecture 100 Normalization 3NF Investigation - MS SQL

Lecture 101 Normalization 3NF Modification - MS SQL

Lecture 102 THANK YOU Video

Section 10: Special Offer

Lecture 103 ***YOUR SPECIAL BONUS***

Anybody who wants to learn PostgreSQL,Anybody who wants to better understand how databases work,Anybody who wants to enhance their Data Science career