Description

Welcome to a hands-on SQL course. In this course, you will learn the fundamentals of SQL databases and the relational model. We will cover core concepts such as inserting data into SQL databases, extracting data from SQL databases, aggregations, subqueries, joins, unions, and window functions. This course will help you write complex queries to perform tasks as required in technical interviews or on the job.

SQL is the language that you can use to interact with one major database type (SQL databases), one that is very widespread in today's world. Knowing SQL will give you a nice ability and understanding of how to handle data and databases and being able to integrate it into Python code enables you to create great, dynamic, and automatic systems that combine the power of programming and data. In this course, you will be learning SQL. We will start by going through some fundamental knowledge such as learning about the relational model, as well as what schemas are, the different data types available, and how database tables are organized. Then we will go ahead and set up our own local Postgres database and populate it with data so that we have data to run queries against. Once our setup is done, we are then ready to jump directly into writing SQL code. We will start with the basics such as creating databases and tables, inserting, and selecting data, and performing filtering and result order. This way, we can ensure we are comfortable with how the data is laid out and that we feel good performing the basic operations that usually make up all queries. Then we will jump deeper into SQL by learning about aggregations, subqueries, joins, unions, and window functions. By the end of this course, you should be ready to successfully apply your SQL knowledge in technical interviews and on the job. All the resources for this course are available at: https://github.com/PacktPublishing/Job-Ready-SQL-in-an-Afternoon

What You Will Learn

Understand SQL databases
Understand the relational model
Learn to insert data into SQL databases
Extract data from SQL databases
Learn about aggregations, subqueries, joins, and unions
Learn about different window functions

Audience

Anyone who wishes to learn about databases, integrate databases into their programs, or learn about SQL databases should take this course. One should be familiar with Python's fundamentals.

Approach

In this practical, hands-on course, we will start with the fundamentals, such as building databases and tables, adding and choosing data, and performing filtering and result ordering. Then, by studying aggregations, subqueries, joins, unions, and window functions, we will dive even further into the world of SQL.

Key Features

Write complex queries to perform tasks as required in technical interviews or on the job * A hands-on course, designed for complete beginners * Cover different self-learning exercises to improve your query-writing skills

Github Repo

https://github.com/PacktPublishing/Job-Ready-SQL-in-an-Afternoon

About the Author

Maximilian Schallwig

Maximilian Schallwig is a data engineer and a proficient Python programmer. He holds a bachelor's degree in physics and a master's degree in astrophysics. He has been working on data for over five years, first as a data scientist and then as a data engineer. He can talk endlessly about big data pipelines, data infrastructure, and his unwavering devotion to Python. Even after two unsuccessful attempts in high school, he still decided to learn Python at the University. He cautiously stepped into the realm of data, beginning with a simple Google search for 'what does a data scientist do'. He was determined to pursue a career in data science to become a data engineer by learning about big data tools and infrastructure design to build scalable systems and pipelines. He enjoys sharing his programming skills with the rest of the world.

Course Outline

1. Fundamentals and Setup

1. Intro to Relational Databases

In this video, we will have a quick introduction to Relational databases.

2. Data Types and Schemas

In this video, you will learn about data types and schemas.

3. [Mac] Setting Up a Local PostgreSQL Server

In this video, we will work on setting up a local PostgreSQL server for Mac systems.

4. [Windows] Setting Up a Local PostgreSQL Server

In this video, we will work on setting up a local PostgreSQL server for Windows systems.

5. [Mac] SQL Editor Postico

In this video, we will cover SQL Editor Postico for Mac.

6. SQL Editor DataGrip

In this video, we will explore SQL Editor DataGrip.

7. SQL Editor CLI (Command Line Interface)

In this video, we will explore SQL Editor CLI (Command Line Interface).

8. Creating and Removing a Database

In this video, you will learn how to create and remove a database.

2. Databases, Tables, and Basic Queries

1. Creating Schemas

In this video, you will learn how to create schemas.

2. Creating Tables

In this video, you will learn how to create tables.

3. Altering Tables

In this video, you will learn how to alter tables.

4. Enumerated Types

In this video, you will learn about Enumerated types.

5. Inserting Values

In this video, you will learn how to insert values.

6. Running an SQL Script

In this video, you will learn how to run an SQL script.

7. Reading Data

In this video, you will learn how to read data.

8. Creating Tables Using Query Results

In this video, you will learn how to create tables using query results.

9. Aliases and Ordering

In this video, you will learn about aliases and ordering.

3. Conditionals and Arithmetic

1. Conditional Filtering Direct Comparisons

In this video, we will cover conditional filtering direct comparisons.

2. Conditional Filtering Grouped Comparisons

In this video, we will cover conditional filtering grouped comparisons.

3. Joining Conditionals

In this video, we will cover joining conditionals.

4. Negating Conditionals

In this video, we will cover negating conditionals.

5. Filtering Null Values

In this video, we will cover filtering null values.

6. Creating New Columns and Arithmetic

In this video, you will learn how to create new columns and arithmetic.

7. Datatype Conversions

In this video, we will cover datatype conversions.

8. Logic Statements

In this video, we will cover the logic statements.

4. Dates, Times, and Manipulating Data

1. Manipulating Strings

In this video, we will cover manipulating strings.

2. String Positional Information

In this video, we will cover string positional information.

3. String Subsets and Concatenations

In this video, we will cover string subsets and concatenations.

4. String Replacements

In this video, we will cover string replacements.

5. Date and Time

In this video, we will cover date and time.

6. Date and Time Intervals

In this video, we will cover date and time intervals.

7. Comments

In this video, we will cover comments.

8. Aggregation Functions

In this video, we will cover aggregation functions.

9. Grouping and Filtering Aggregations

In this video, you will learn how to group and filter aggregations.

5. Joins, Unions, and Subqueries

1. Inner Joins

In this video, we will cover inner joins.

2. Left, Right, and Outer Joins

In this video, we will cover left, right, and outer joins.

3. Self Joins

In this video, we will cover self joins.

4. Unions

In this video, we will cover unions.

5. Subqueries

In this video, we will cover subqueries.

6. CTEs

In this video, we will cover CTEs (Common Table Expressions).

6. Window Functions

1. Window Functions and Aliases

In this video, we will cover Window functions and aliases.

2. Row Numbers and Ranks

In this video, we will cover row numbers and ranks.

3. Using Other Rows with Window Functions

In this video, you will learn how to use other rows with Window functions.

4. NTiles

In this video, we will cover NTiles.

5. Understanding Query Performance

In this video, we will understand query performance.

7. Exercises

1. Exercise 1

In this exercise video, we will practice understanding a new database, as we may not always be the creator of the database.

2. Exercise 2

In this exercise video, we will work on data filtering for easier analysis.

3. Exercise 3

In this exercise video, we will work on data preparation so that we have clean data to work with.

4. Exercise 4

In this exercise video, we will find out if there are any customers who purchased a track more than once.

5. Exercise 5

In this exercise video, we will find out how much people from each country spent on tracks in total.

Course Images

Job-Ready SQL in an Afternoon

By Packt

Booking options

Highlights