Advanced SQL Bootcamp

Description

In this course, you will :

• Learn the power of Subqueries in SQL
• Discover how to use Window Functions and Partitions in SQL
• Understand advanced JOIN commands in SQL
• Learn about SQL Set Operations
• Discover Grouping Sets
• Building table relationships and schema structures in SQL
• Use SQL Transactions with Update and Set calls
• Understand Table Inheritance with SQL
• Create Views in SQL
• Learn how to use Stored Procedures
• Discover how to use Triggers across SQL Tables
• Understand general useful methods and commands in Advanced SQL

Syllabus :

1. Subqueries and CTEs

• Subqueries Overview
• Subqueries in FROM Statements
• Common Table Expressions
• Subqueries for Comparisons
• IN and NOT IN Statements
• ANY and ALL statements
• EXISTS clause in Subqueries
• Recursive CTEs
• Subqueries - Exercises
• Subqueries - Exercise Solutions

2. Window Functions

• Window Functions Overview
• OVER, PARTITION and ORDER BY
• WINDOW and Window Functions
• Window Functions Exercise
• Window Functions Exercise - Solutions

3. Advanced JOIN Operations

• Self-Joins
• Cross-Join
• Full Join
• USING Keyword and Natural JOINs
• Advanced JOINs Exercise
• Advanced JOINs Exercise Solutions

4. Set Operations

• UNION
• INTERSECT
• EXCEPT
• Set Operations - Exercise
• Set Operations - Exercise Solutions

5. Grouping Sets

• Grouping Sets Overview
• CUBE
• ROLLUP
• Grouping Sets Exercises
• Grouping Sets - Exercise Solutions

6. Schema Structures and Table Relationships

• Information Schema
• COMMENT
• Adding and Dropping Constraints
• Adding and Dropping Foreign Keys
• Schema Structure Exercises
• Schema Structure - Exercise Solutions

7. Transactions

• Transactions Overview
• UPDATE and SET
• Beginning and Ending Transactions
• SAVEPOINT
• Database Locks
• Transactions Exercises
• Transactions Exercise - Solutions

8. Table Inheritance and Partioning

• Range Partitioning
• List Partitioning
• Hash Partitioning
• Table Inheritance
• Table Partitioning Exercises
• Table Partitioning Exercises - Solutions

9. Views

• Creating Views
• Modifying and Deleting Views
• Updatable Views
• Materialized Views
• Recursive Views
• Views Exercise
• Views Exercise - Solutions

10. SQL Functions

• Creating Functions
• Modifying Functions
• Functions Exercises
• Functions Exercise - Solutions

11. Stored Procedures

• Creating Stored Procedures
• Modifying Stored Procedures
• Stored Procedures Exercise
• Stored Procedures Exercise - Solutions

12. Triggers

• Creating Triggers
• Enabling and Disabling Triggers
• Modifying and Dropping Triggers
• Triggers Exercises
• Triggers Exercises - Solutions

13. Useful Methods and Tools

• EXPLAIN and EXPLAIN ANALYZE
• Dropping Data with TRUNCATE
• Exporting and Importing Data with COPY
• Array and Array Functions
• JSON and JSON Functions
• Modules and Extensions
• Useful Methods and Tools Exercises

Show more