Introduction to SQL - free course from ProductStar, training, Date: December 2, 2023.

Miscellaneous / by admin / December 05, 2023

click fraud protection

In our opinion, the first thing when mastering a new profession is to discover the requirements for the desired vacancies and write down the skills that need to be acquired. Let's look at the requirements for Analyst positions. And we will see that SQL is one of the main requirements for an analyst position. That's why we will study it!

1. Studying a topic
Lessons in video format, workshops with case studies and quizzes to consolidate knowledge

2. Complete tasks
At the pace at which you are comfortable and receive feedback.

3. Communicate with a mentor
Consolidate knowledge and correct mistakes

All tasks are collected based on real cases of well-known companies; for your convenience, the tasks have a template for completing them

Introductory lesson (free)

Let's study:
What is SQL
SQL Features
Why SQL Analytics?
SQL Examples

Practice:
Let's download our first airbnb database from GitHub
Let's get our first result in SQL!

Tools:
GitHub
SQLite Online

Lesson 1 Introduction to SQL

What is SQL
SQL Features
Examples of using SQL in different businesses

instagram viewer

A little history of SQL
Block manifesto
What tools will we use?
First practice

Lesson 2 Extracting and filtering data (part 1)

Learning to work with data extraction
SELECT, FROM
Keyword Order in SQL
LIMIT
Writing different data types in SQL
WHERE and operators =, IS, LIKE, , <=, >=, !=, <>, IS NOT
Practice

Lesson 3 Data Extraction and Filtering (Part 2)

Comments in SQL
SQL Case Sensitivity
WHERE and NULL, IN, NOT IN, AND, OR, BETWEEN operators
Extracting text/number/NULL
Retrieving a table and adding a text/number/date/NULL column
Practice

Lesson 4 Transforming and sorting data (part 1)

Alias alias
Tips for using Alias
Transformations UPPER(), LOWER()
Converting using arithmetic operations
CASE conversion
Practice

Lesson 5 Transforming and sorting data (part 2)

Convert current date/time
Date/Time Conversion
Sorting ORDER BY
Retrieving Top N or Last N Records
Practice

Lesson 6 Grouping Data

Aggregation functions MIN(), MAX(), SUM(), AVG()
Aggregation functions COUNT()
GROUP BY
GROUP BY and aggregation functions MIN(), MAX(), SUM(), AVG(), COUNT()
Query execution order in SQL
HAVING
Difference between WHERE and HAVING
Practice

Lesson 7 Introduction to Databases

The role of databases in human life
History of database development
SQL query architecture and communication with databases
Basic terms of relational databases:
Table
Key (primary and foreign)
Database and relationships between tables
DBMS
Practice: setting up a database in PostgreSQL DBMS

Lesson 8 Joining tables

Why know how to join tables?
How does joining tables work?
Methods for joining tables
Additional terms of association
Practice

Lesson 9 Nested queries (subqueries)

How does a late request work?
What is a subquery?
Subquery structure
Subquery execution order
Subqueries in SELECT
Subqueries in FROM
Subqueries in JOIN
Subqueries in WHERE
Tips for working with subqueries
Practice

Lesson 10 Updating, adding and deleting data

Why and how to change data?
Adding data (INSERT)
Data update (UPDATE)
Deleting data (DELETE)
Practice

Lesson 11 Creating, modifying and deleting tables

Why and how to change tables?
Creating tables
Creating new tables
Data types
Restrictions
Creating Temporary Tables
Creating tables based on existing ones
Changing tables
Removing tables
Practice: creating an online store database

Lesson 12 SQL: Advanced

Understanding the pitfalls in SQL
Operations with text
String concatenation
Determining String Length
Returning part of a string
Regular Expressions
Window functions
Query acceleration and optimization
Views and common table expressions
The final