Sql Full Course In 1.5 H: From Basics To Advanced Topics

From SQL query syntax to advanced concepts: indexes, query optimization, normal forms, SQL Injections. Interview prep.

What you'll learn

Exploring free tools to work with SQL (e.g., pgAdmin, VS Code, DBeaver, psql)

Deploy a free tier database with Amazon.

Learn basic SQL commands (ORDER BY ASC / DESC, OFFSET / LIMIT, LIKE, iLIKE, UNION (ALL), COALESCE, DATE, NOW(), INTERVAL, AGE)

Connecting Database to Tableau / Power BI and data visualization

Deploy a free tier database with AWS

Aggregate functions (SUM, ROUND, AVG, MAX, MIN, COUNT)

Searching and deleting duplicates, writing double joins, etc.

Joining multiple tables together (INNER JOIN, LEFT JOIN, RIGHT JOIN, FULL OUTER JOIN)

How to use WHERE and HAVING clauses / DO UPDATE / DO NOTHING

Query optimization and increase performance of queries when working with big tables

How to use (CHECK) CONSTRAINTS, VIEWS, STORED PROCEDURES, TRANSACTIONS, WINDOW FUNCTIONS, COMMON TABLE EXPRESSIONS, FULL-TEXT SEARCH, CASE EXPRESSIONS, TRIGGERS

Exploring relations between tables (ONE TO ONE, ONE TO MANY, MANY TO MANY)

writing JOINS (JOINS: INNER JOIN, LEFT JOIN, RIGHT JOIN, FULL OUTER JOIN, CROSS JOIN)

SQL (NON) NORMAL FORMS (first normal form, second normal form and third normal form)

SQL INJECTIONS on practical examples: Rest API web / Authorization application injection with consequences of exploiting such injection

Pass practical tests to be prepared for an interview

Requirements

No programming experience needed

Description

Throughout this course, we will delve into a variety of essential topics to equip you with the necessary skills to work with SQL:Connecting Database to Tableau / Power BI and data visualization;Deploy a free tier database with Amazon;Exploring the free tools to work with SQL (e.g., pgAdmin, VS Code, DBeaver, psql);Learn basic SQL commands (ORDER BY ASC / DESC, OFFSET / LIMIT, LIKE, iLIKE, UNION (ALL), COALESCE, DATE, NOW(), INTERVAL, AGE);Writing subqueries;Aggregate functions (SUM, ROUND, AVG, MAX, MIN, COUNT);Understand when to use WHERE and HAVING clauses / DO UPDATE / DO NOTHING;How to optimize queries and increase performance of queries when working with big tables;How to use (CHECK) CONSTRAINTS, VIEWS, STORED PROCEDURES, TRANSACTIONS, WINDOW FUNCTIONS, COMMON TABLE EXPRESSIONS, FULL-TEXT SEARCH, CASE EXPRESSIONS, TRIGGERS;Exploring relations between tables (ONE TO ONE, ONE TO MANY, MANY TO MANY);Writing JOINS (JOINS: INNER JOIN, LEFT JOIN, RIGHT JOIN, FULL OUTER JOIN, CROSS JOIN);SQL (NON) NORMAL FORMS (first normal form, second normal form and third normal form);SQL INJECTIONS on practical examples: Rest API web / Authorization application injection with consequences of exploiting such injection.Pass practical tests to be prepared for an interview.Throughout the course, you will receive a detailed presentation with all the queries we discuss. This will help you consolidate your learning and provide you with a reference resource you can use later.I'm excited to guide you through this journey of SQL learning, and I hope that by the end of the course, you will feel confident in your ability to use SQL to solve a variety of problems.

Overview

Section 1: SQL Free Tools and Mock Data

Lecture 1 SQL Free Tools (psql, pgAdmin, DBeaver, VS Code)

Lecture 2 SQL Tools - Connecting to DB, generation and inserting mock data

Lecture 3 PSQL (terminal) - main commands for working with database

Section 2: Deployment of database with AWS (Amazon) (optional)

Lecture 4 Deployment of a free tier relational database with AWS (Amazon) (optional)

Section 3: Visualization of data from Database Tableau / Power BI (optional)

Lecture 5 Connecting Tableau to Database and visualizing data

Lecture 6 Connecting Power BI to Database and visualizing data

Section 4: SQL Basic queries

Lecture 7 Table creation

Lecture 8 Creating and importing a dump file

Lecture 9 Sorting Data - ORDER BY

Lecture 10 Searching Data - LIKE, iLIKE

Lecture 11 Finding and deleting duplicates

Lecture 12 Aggregation functions - SUM, AVG, COUNT

Lecture 13 UNION or UNION ALL?

Lecture 14 WHERE or HAVING?

Lecture 15 COALESCE

Lecture 16 Working with DATE

Lecture 17 AGE()

Lecture 18 Check Constraints

Lecture 19 ON CONFLICT - DO UPDATE or DO NOTHING ?

Lecture 20 Subqueries

Lecture 21 INDEX

Lecture 22 Improve performance and speed up your queries. Cost of query execution

Section 5: SQL advanced queries

Lecture 23 Views

Lecture 24 Stored Procedures

Lecture 25 Transactions

Lecture 26 Transactions Rollback

Lecture 27 Window Functions

Lecture 28 Window Functions - PARTITION BY

Lecture 29 CTEs (Common Table Expressions)

Lecture 30 Full-text search

Lecture 31 Case Expressions

Lecture 32 Triggers

Section 6: Joining Tables

Lecture 33 Inner Join

Lecture 34 Left Join

Lecture 35 Right Join

Lecture 36 Full Outer Join

Lecture 37 Cross Join

Section 7: Proper relationships between tables

Lecture 38 One to one

Lecture 39 One to Many

Lecture 40 Many to Many & Double Join

Section 8: From Non-normal to Normal forms

Lecture 41 First Normal Form

Lecture 42 Second Normal Form

Lecture 43 Third Normal Form

Section 9: Case study: SQL Injection cause fetching data / bypassing authorization

Lecture 44 SQL Injection. Rest API vulnarability and its prevention (Java example)

Lecture 45 SQL Injection. Bypass authorization vulnarability, its prevention (PHP example)

Section 10: Practice interview questions (case based)

Data analysts, Database administrators, Software developers and anyone interested in SQL