SQL Level 2: Advanced Data Querying and Management

30 Hours

Beginner to advanced

8 Modules

10 customers are viewing this product
This advanced SQL course is designed for individuals who have a foundational understanding of SQL and are looking to expand their skills in data querying, extraction, and management. Focusing on the extensive use of DDL (Data Definition Language) and DML (Data Manipulation Language) statements, this course covers advanced SQL techniques to retrieve, manipulate, and manage data across multiple tables, supporting real-world data analysis and reporting tasks. The course begins with a quick recap of SQL basics, before diving into complex data filtering, sorting, and formatting using advanced SELECT statements. Students will master the art of joining tables, working with subqueries, and using aggregation and window functions for powerful analytical queries. A dedicated section on DML commands guides students in batch data insertion, updates, and conditional data manipulation using transactions and error handling. Schema management is explored through DDL commands, with lessons on table modification, indexing, and enforcing data integrity through constraints. Designed with practical applications in mind, the course culminates in a final project where students will create comprehensive queries, build reports, and apply insights for business scenarios such as sales analysis, customer insights, or inventory management. By the end of the course, students will be proficient in extracting and analyzing data from complex databases, empowering them with skills essential for roles in data analytics, business intelligence, and database management.
    • Course Outline
    • What you will learn
    • Audience profile
    Lesson 1: Quick Review of SQL Level 1 Key Concepts from SQL Level 1 (ERD, Schema, Normalization) Basic SQL Syntax Review Lesson 2: Understanding DDL and DML Overview of DDL (CREATE, ALTER, DROP) Overview of DML (INSERT, UPDATE, DELETE) Importance of DDL/DML in Database Management
    Lesson 1: Filtering Data with Advanced WHERE Clauses Complex WHERE Conditions (AND, OR, IN, BETWEEN, LIKE) Pattern Matching and Wildcards Activity: Filtering Data with Complex Conditions Lesson 2: Data Sorting and Limiting Results ORDER BY with Multiple Columns Using LIMIT/OFFSET for Pagination Activity: Sorting and Limiting Query Results Lesson 3: Column Aliases and Formatting Data Output Using Aliases for Readability Concatenating Strings and Formatting Data Activity: Query Practice with Aliases and Formatting
    Lesson 1: Inner and Outer Joins INNER JOIN for Related Data Extraction LEFT, RIGHT, and FULL OUTER Joins Practical Use Cases for Each Join Type Activity: Joining Tables to Extract Data Across Tables Lesson 2: Cross Joins and Self Joins Cross Joins for Cartesian Products Self Joins for Comparing Records in the Same Table Activity: Practice with Cross and Self Joins Lesson 3: Subqueries and Nested Queries What are Subqueries? Using Subqueries in SELECT, WHERE, and FROM Clauses Activity: Writing Nested Queries to Solve Complex Problems
    Lesson 1: Aggregation Functions COUNT, SUM, AVG, MIN, MAX Functions Grouping Data for Summary Reports Lesson 2: GROUP BY and HAVING Clauses Grouping Data by One or More Columns Filtering Groups with HAVING Activity: Writing Aggregation Queries for Data Analysis Lesson 3: Window Functions for Advanced Analytics ROW_NUMBER, RANK, DENSE_RANK PARTITION BY for Segmenting Data Activity: Using Window Functions for Analysis
    Lesson 1: Inserting and Updating Data Efficiently Batch Inserts and Updates Conditional Updates with CASE Statements Activity: Data Manipulation with Conditional Updates Lesson 2: Using Transactions and Rollbacks Understanding Transactions and Atomicity COMMIT and ROLLBACK for Data Integrity Activity: Practicing Transactions and Error Handling Lesson 3: Advanced DELETE Statements and Data Cleanup Cascading Deletes with Foreign Keys Conditional Deletes and Data Cleanup Activity: Managing Data Cleanup in Large Datasets
    Lesson 1: Altering Table Structures Adding, Dropping, and Modifying Columns Adding Constraints (FOREIGN KEY, UNIQUE) Activity: Modifying Table Structures Practically Lesson 2: Creating and Managing Indexes Importance of Indexing for Performance Creating, Viewing, and Deleting Indexes Activity: Implementing Indexes to Optimize Queries Lesson 3: Advanced Table Constraints and Data Integrity Enforcing Data Integrity with Constraints Working with Complex Foreign Key Relationships Activity: Enforcing Data Integrity with Constraints
    Lesson 1: Building Complex Reports with SQL Designing Reports with Multiple Metrics Using CTEs (Common Table Expressions) Activity: Building Reports from Complex Queries Lesson 2: SQL Views for Data Simplification Creating and Using Views for Simplified Access Benefits of Using Views in Reporting Activity: Designing and Implementing Views for Reports Lesson 3: Using Stored Procedures for Repeatable Processes Introduction to Stored Procedures Benefits for Data Management and Extraction Activity: Writing Stored Procedures for Data Retrieval
    Project Description Select a Real-World Scenario (e.g., Sales, Customer Service, Inventory) Build Queries to Extract Key Insights (e.g., Monthly Sales Reports, Customer Analysis) Use DDL/DML for Database Creation and Data Manipulation Create Complex Reports and Views for Easy Access Project Presentation Present Findings from SQL Queries and Reports Demonstrate Advanced Queries and Business Insights
    All SQL funcations from basic to expert level
    Individuals looking to enter data analysis, data science, or database administration. They may have little to no experience with SQL but aim to gain foundational knowledge for building or querying databases.
    Analysts who want to enhance their data manipulation skills and optimize report generation. Understanding SQL allows them to work directly with databases and extract insights without always needing support from a database administrator. IT and Database Professionals: Those in IT roles who interact with data, work with backend systems, or manage databases and seek to deepen their technical skills. This course is ideal for database administrators, system engineers, or IT support professionals who need a solid foundation in SQL.

    Related Products

    CLA
    Example course title
    LE 4,999.00
    LE 9,999.00
    LE 4,999.00
    CLA
    Example course title
    LE 4,999.00
    LE 9,999.00
    LE 4,999.00
    CLA
    Example course title
    LE 4,999.00
    LE 9,999.00
    LE 4,999.00
    CLA
    Example course title
    LE 4,999.00
    LE 9,999.00
    LE 4,999.00