SQL & Database Design A-Z™: Learn MS SQL Server + PostgreSQL from Udemy

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

What's inside

Learning objectives

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

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

Syllabus

Introduction

Welcome Challenge!

Intro Video

Some Additional Resources!!

Learning Paths

Get the materials

Your Shortcut To Becoming A Better Data Scientist!

Installation

Welcome to this section! This is what you wil learn

Installation: PostgreSQL & PgAdmin 4 for Windows

Installation: PostgreSQL & PgAdmin 4 for MAC OS

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

Preparation

Welcome to this section! This is what you will learn

The challenge: Investigating Consumer Complaints

Upload Consumer Complaints

Getting To Know The PGADMIN

Upload Consumer Complaints MS SQL

Getting to know MS SQL SERVER

Basics of SQL

Theory Select Query

Time to write your first query in PostgreSQL

Adding a filter using WHERE in PostgreSQL

Logical conditions AND, OR in PostgreSQL

Using wildcards % and _ (advanced tutorial)

Comments in SQL

Getting to write your first query MS SQL

Where MS SQL

Logical conditions, AND OR MS SQL

Using wildcards MS SQL

Comments in SQL MS SQL

Basics of SQL quiz

Working With Data

The challenge: Analyzing Console Games Sales

Uploading Console Games Dataset

Working with arithmetic data types

ORDER BY

WORKING WITH VARCHARS

WORKING WITH DATES

Implicit Data Conversion in SQL

WORKING WITH NULL

Uploading Console Games DATASET MS SQL

Working with arithmetic data types MS SQL

ORDER BY MS SQL

Working with VARCHAR MS SQL

Working with Dates MS SQL

Implicit DATA conversion MS SQL

Working With Data quiz

Fundamentals of Database Theory

Structured vs Unstructured Data

Elements Of a Database

Relational Keys

Relational Database

Functional Dependency

Joining tables in SQL

Types of Joins

Duplicates in Joins

Joining on multiple fields

The challenge: The Vet's Clinic

Upload the data

Your First Left Outer Join

Joins Tips & Tricks

Inner Join

Full Outer join

Joining on Multiple Fields (Practice)

Complex Joins

Cleaning up your Join

Cross Join (advanced tutorial)

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

Creating Tables in SQL

Create Database

Create Table

Insert Data

Alter Table

Drop Table

Create Database MS SQL

Create Table MS SQL

Insert Data MS SQL

ALTER TABLE MS SQL

DROP TABLE MS SQL

Creating Tables in SQL quiz

Database Design

In this tutorial, you will learn why you should know how the databases design and why it is essential for data scientists

Prime and non-prime attributes

First Normal Form (1NF)

Second Normal Form (2NF)

Good to know

Know what's good

, what to watch for

, and possible dealbreakers

Develops strong foundational knowledge of SQL and database design, two crucial skills for data scientists

Taught by recognized experts in data science and analytics

Offers hands-on learning through interactive materials and exercises

Covers advanced topics such as database normalization, which is essential for efficient data management

Explores the concepts behind database design, providing a deeper understanding of how databases are structured to optimize data storage and retrieval

Introduces industry-standard tools and practices, ensuring relevance in the job market

Activities

Be better prepared before your course. Deepen your understanding during and after it. Supplement your coursework and achieve mastery of the topics covered in SQL & Database Design A-Z™: Learn MS SQL Server + PostgreSQL with these activities:

Find a mentor or study buddy

Show steps

Connect with a mentor or study buddy to provide support, guidance, and motivation throughout the course.

Show steps

Reach out to friends, colleagues, or online communities to find someone who is experienced in SQL or data science.
Explain your goals and ask if they would be willing to mentor or support you.

Organize your course materials

Show steps

Stay organized by compiling and reviewing your notes, assignments, and other course materials.

Show steps

Create a dedicated folder or notebook for your course materials.
Organize your materials by topic or lesson.
Review your materials regularly to reinforce your understanding.

Review SQL syntax

Show steps

Review the basics of SQL syntax to refresh your memory and strengthen your foundation for this course.

Browse courses on SQL Syntax

Show steps

Go over your notes or textbooks from previous SQL courses or tutorials.
Take practice quizzes or exercises to test your understanding of SQL syntax.

Five other activities

Expand to see all activities and additional details

Show all eight activities

Follow along with SQL tutorials

Show steps

Supplement your learning by following along with tutorials and videos that demonstrate SQL concepts in a practical way.

Show steps

Search for SQL tutorials online or on platforms like YouTube.
Choose tutorials that cover topics relevant to this course, such as database design or query optimization.
Follow along with the tutorials and take notes on key concepts and techniques.

Write SQL queries to practice

Show steps

Put your SQL skills to the test by writing your own queries and solving problems.

Show steps

Find a dataset online or use the one provided in the course materials.
Create a list of questions or scenarios that you want to solve using SQL.
Write SQL queries to answer your questions or solve your scenarios.
Check your results and identify any errors or areas for improvement.

Participate in SQL competitions

Show steps

Test your SQL skills and knowledge by participating in online or local SQL competitions.

Show steps

Search for SQL competitions online or through organizations like Kaggle.
Choose a competition that aligns with your interests and skill level.
Study and practice SQL to prepare for the competition.
Participate in the competition and submit your solutions.

Create a data analysis project

Show steps

Apply your SQL skills by creating a data analysis project that addresses a real-world problem or question.

Show steps

Identify a problem or question that you want to solve using data.
Gather and clean the necessary data.
Write SQL queries to analyze the data and extract insights.
Create visualizations and reports to present your findings.
Share your project with others for feedback and discussion.

Build a database and design

Show steps

Demonstrate your understanding of database design by creating a database and implementing a specific design.

Show steps

Choose a dataset or scenario for your database.
Design the database schema, including tables, columns, and relationships.
Create the database using PostgreSQL or another SQL database management system.
Populate the database with data.
Write SQL queries to demonstrate the functionality and capabilities of your database.

Career center

Learners who complete SQL & Database Design A-Z™: Learn MS SQL Server + PostgreSQL will develop knowledge and skills that may be useful to these careers:

Database Administrator

A Database Administrator (DBA) is responsible for the maintenance and performance of databases. They work with database software, hardware, and network systems to ensure that databases are available, secure, and performant. This course helps build a foundation in SQL, which is a key skill for DBAs. The course also covers database design, which is essential for understanding how databases are structured and organized. This knowledge can help DBAs to better manage and maintain databases.

See salaries and explore the career path for Database Administrator

Data Analyst

A Data Analyst collects, analyzes, interprets, and presents data. They work with business stakeholders to identify and solve problems, and to improve decision-making. This course helps build a foundation in SQL, which is a key skill for Data Analysts. The course also covers database design, which is essential for understanding how data is structured and organized. This knowledge can help Data Analysts to better understand the data they are working with and to develop more effective solutions.

See salaries and explore the career path for Data Analyst

Data Scientist

A Data Scientist uses scientific methods, processes, algorithms, and systems to extract knowledge and insights from data in various forms, both structured and unstructured. This course helps build a foundation in SQL, which is a key skill for Data Scientists. The course also covers database design, which is essential for understanding how data is structured and organized. This knowledge can help Data Scientists to better understand the data they are working with and to develop more effective models.

See salaries and explore the career path for Data Scientist

Data Visualization Engineer

A Data Visualization Engineer designs and develops data visualizations that communicate data insights to users. They work with data analysts and scientists to identify the most effective ways to visualize data and to create visualizations that are both informative and engaging. This course may be helpful for Data Visualization Engineers who want to learn more about SQL and database design. The course can help Data Visualization Engineers to develop a better understanding of how data is stored and organized, and how to use SQL to access and manipulate data.

See salaries and explore the career path for Data Visualization Engineer

Data Engineer

A Data Engineer designs, builds, and maintains data pipelines and systems. They work with a variety of data sources and technologies to ensure that data is clean, consistent, and available for analysis. This course may be helpful for Data Engineers who want to learn more about SQL and database design. The course can help Data Engineers to develop a better understanding of how data is stored and organized, and how to use SQL to access and manipulate data.

See salaries and explore the career path for Data Engineer

Information Architect

An Information Architect designs and organizes information systems, including databases. They work with stakeholders to identify and define the information needs of an organization and to develop systems that meet those needs. This course may be helpful for Information Architects who want to learn more about SQL and database design. The course can help Information Architects to develop a better understanding of how data is stored and organized, and how to use SQL to access and manipulate data.

See salaries and explore the career path for Information Architect

Operations Research Analyst

An Operations Research Analyst uses mathematical and analytical techniques to solve problems in a variety of industries. They work with data to develop models and solutions that improve efficiency and effectiveness. This course may be helpful for Operations Research Analysts who want to learn more about SQL and database design. The course can help Operations Research Analysts to develop a better understanding of how data is stored and organized, and how to use SQL to access and manipulate data.

See salaries and explore the career path for Operations Research Analyst

Business Analyst

A Business Analyst works with business stakeholders to identify and solve problems, and to improve decision-making. They use a variety of tools and techniques to analyze data, develop solutions, and communicate findings. This course may be helpful for Business Analysts who want to learn more about SQL and database design. The course can help Business Analysts to develop a better understanding of how data is stored and organized, and how to use SQL to access and manipulate data.

See salaries and explore the career path for Business Analyst

Financial Analyst

A Financial Analyst uses financial data to make investment recommendations and to evaluate the performance of investments. They work with data to develop models and to make recommendations on how to allocate funds. This course may be helpful for Financial Analysts who want to learn more about SQL and database design. The course can help Financial Analysts to develop a better understanding of how data is stored and organized, and how to use SQL to access and manipulate data.

See salaries and explore the career path for Financial Analyst

Risk Analyst

A Risk Analyst identifies, assesses, and manages risk. They work with data to develop models that can be used to predict future events and to make decisions about risk management. This course may be helpful for Risk Analysts who want to learn more about SQL and database design. The course can help Risk Analysts to develop a better understanding of how data is stored and organized, and how to use SQL to access and manipulate data.

See salaries and explore the career path for Risk Analyst

Management Consultant

A Management Consultant helps organizations to improve their performance. They work with clients to identify and solve problems, and to develop and implement solutions. This course may be helpful for Management Consultants who want to learn more about SQL and database design. The course can help Management Consultants to develop a better understanding of how data is stored and organized, and how to use SQL to access and manipulate data.

See salaries and explore the career path for Management Consultant

Actuary

An Actuary uses mathematical and statistical techniques to assess risk and uncertainty. They work with data to develop models that can be used to predict future events and to make decisions about risk management. This course may be helpful for Actuaries who want to learn more about SQL and database design. The course can help Actuaries to develop a better understanding of how data is stored and organized, and how to use SQL to access and manipulate data.

See salaries and explore the career path for Actuary

Market Research Analyst

A Market Research Analyst collects, analyzes, and interprets data about consumers and markets. They work with data to identify trends and opportunities, and to develop strategies for marketing products and services. This course may be helpful for Market Research Analysts who want to learn more about SQL and database design. The course can help Market Research Analysts to develop a better understanding of how data is stored and organized, and how to use SQL to access and manipulate data.

See salaries and explore the career path for Market Research Analyst

Statistician

A Statistician collects, analyzes, and interprets data. They work with data to identify patterns and trends, and to develop models that can be used to make predictions. This course may be helpful for Statisticians who want to learn more about SQL and database design. The course can help Statisticians to develop a better understanding of how data is stored and organized, and how to use SQL to access and manipulate data.

See salaries and explore the career path for Statistician

Software Engineer

A Software Engineer designs, develops, tests, and maintains software systems. They work with a variety of programming languages and technologies to create software that meets the needs of users. This course may be helpful for Software Engineers who want to learn more about SQL and database design. The course can help Software Engineers to develop a better understanding of how data is stored and organized, and how to use SQL to access and manipulate data.

See salaries and explore the career path for Software Engineer