SQL Essentials: From Basic To Advance

Build a foundation in data and databases by exploring fundamental concepts, how data is created, stored, and organized, and the role of tables and data models.

Explore what data is, why databases matter, how they differ from spreadsheets, and cover scalability, multi-user access, data integrity, security, and PostgreSQL as the course focus.

Explore the differences between database management systems and relational database management systems, including DBMs and RDBMS, their structures, keys, SQL support, data integrity, and multi-user security.

Explore how tables structure data with rows and columns, apply primary keys to uniquely identify records, and use foreign keys to link tables and enable joins in relational databases.

Explore core database modelling concepts by examining joins (inner, left, right, full), cardinality, and normalization, and learn how these relate to entities and shape your SQL data models.

Define SQL, its purpose, and why it’s a critical skill across technologies and business roles, focusing on Postgres SQL server and setup for hands-on practice.

Explore the origins of SQL and its role as the universal language for relational databases, and how it manipulates data in tables via insert, update, retrieve, and delete operations.

Learn SQL to access relational data across industries and roles, from analysts to managers. It speeds insight, boosts decisions, and pairs with tools like Power BI, Tableau, Python, and R.

Discover how SQL dialects vary across databases while core commands stay standard, with examples in PostgreSQL and Oracle, and learn limit vs row number for five rows.

Install the PostgreSQL server and PgAdmin from the official downloader, run the 64-bit Windows installer with admin rights, and connect via PgAdmin to explore databases and schemas.

Explore the PgAdmin interface, manage PostgreSQL servers, create databases, import tables via restore, and run sql queries with the query tool to view results.

Explore the foundation of SQL by mastering core commands, data types, constraints, and the five command types (DDL, DML, DQL, TCL, DCL) to define, manipulate, query, and control data.

Explore the five SQL command categories, data definition, data manipulation, data control, transaction control, and data retrieval, and learn how to use select to query data and answer questions.

Learn SQL data types and constraints, and design a table structure with columns such as customer id, name, age, and purchase amount, using primary key and not null constraints.

Learn how data definition language (DDL) defines database structure with create, alter, drop, and rename commands for tables, including constraints, data types, and basic Postgres examples.

Learn how data manipulation language (DML) inserts, updates, and deletes records in the employees table, demonstrates multi-row inserts, conditional updates with where, and table truncation to clear all data.

Explore SQL data retrieval language via select statements, including selecting all or specific columns, where clauses, null checks, not null checks, and operators like between and like.

Explore data control language (dcl) and transaction control language (tcl) to secure and protect data, using grant and revoke, then commit, rollback, and save point for robust data integrity.

Master the select command to retrieve and sort with distinct, where, order by, group by, and limit. Learn basic aggregation with count and sum to extract insights from data files.

Upload two CSV data files to pgadmin, create tables list of orders and order breakdown from provided code, then copy paths and run the data copy, verify with select.

Master select distinct to retrieve unique values, eliminate duplicates, and create meaningful insights from single-column and multi-column queries, including counting unique orders with discounts.

Explore SQL's count function to determine total rows, ignore nulls, count distinct values, and apply conditional counts with where clauses, using examples like total orders, regions, and no discount orders.

Learn to sort query results with order by, limit, and offset, show top-n rows, paginate results, and apply conditions to fetch latest orders or top regions in PostgreSQL.

Master the where clause in SQL to filter data with select statements using like, in, between, is null, and is not null, enabling France 2023 and furniture profit queries.

Master the group by feature in SQL to group and summarize data with count, sum, and average. Use where and having, and apply order by to sort grouped results.

Explore joins and unions across multiple tables in SQL Essentials, mastering inner, left, right, and self joins, and union operators, while understanding query execution orders.

Learn how SQL joins combine data from multiple tables using common fields like order id and customer id. Explore inner, left, right, and full joins across order and product tables.

Create three tables named orders, orders_2022, and product, upload corresponding csv data via pgadmin, and prepare data to explore inner, left, right, and full joins.

Learn how inner joins return only matching records from orders and products, using common fields like order id, with aliases, selecting specific columns, and basic syntax.

Learn how left outer joins return all records from the left table with matched data from the right, and nulls when there is no match, ideal for audits.

Learn how right outer joins pull all rows from the right table, match them to the left, and produce nulls for missing left data, with orders and products as examples.

Learn how a full outer join combines left and right joins to match all rows from orders and products, with nulls where data is missing.

Explore union in SQL: learn how union merges rows from tables with the same structure, when to use union versus union all, and common use cases for combining multiple sources.

Uncover the actual order of SQL query execution. Master the from clause and joins, where, group by, having, and aliases to write efficient queries and troubleshoot performance.

Explore essential SQL functions—string, numeric, date, and conversion—to clean, transform, and analyze data by combining multiple functions for powerful queries.

Explore how SQL functions clean, transform, and format data, perform calculations, and extract parts from dates, with examples of string, numeric, date, conversion, and aggregate functions.

Explore string functions in SQL, including upper, left, length, substring, trim, concat, position, and replace. Use real-world examples to format names, clean text, and extract domains from emails.

Explore numeric functions in SQL to perform calculations and transformations on numeric data, including round, ceil, floor, absolute value, power, and mod for real world data analysis.

Explore SQL date functions to analyze time-based data, calculate shipping delays, and extract year or month from order and shipping dates using current date, age, and extract functions.

Master SQL conversion functions to ensure data types align for calculations and dashboards, using cast and the double colon to convert strings to numeric, date, or timestamp formats.

Combine string, numeric, date, and conversion functions to build powerful SQL queries, and apply case studies to extract order years, count orders, and generate region wise stats.

Explore windows functions in SQL to analyze data across rows without collapsing them, and master ranking, aggregation, and advanced use cases through practical case studies.

Learn how windows functions perform calculations across related rows without losing each row. Discover ranking, running totals, and sums by category using over, partition by, and order by in SQL.

Master the building blocks of window functions—over, partition by, order by, and rows between—and perform advanced calculations across rows without collapsing them.

Explore aggregate window functions in SQL essentials, calculating running totals and moving averages by category with over partition by and order by, and compare to traditional group by.

Explore SQL ranking window functions, including row_number, rank, and dense_rank, and learn to partition by category and order by profit for both within the category and overall rankings.

Explore value-based window functions like lag, lead, first_value, and last_value to compare current rows with previous or next ones using partition by and order by for trend analysis and planning.

Examine three case studies of window functions: average sales by segment with partition by, regional trends with lag, and top products by category with rank, using orders and products.

Master subqueries in SQL by nesting queries and applying them in select, where, and join clauses to craft clean, powerful, and optimized multi-layer SQL queries.

Explore subqueries in SQL, defined as inner or nested queries, which dynamically compute results like average profit and feed them to the outer query for smarter data analysis.

Explore the main types of subqueries in SQL, including non correlated and correlated, plus scalar, column, and table subqueries, and their use in select, from, where, and join clauses.

Master subqueries in the select clause to return a scalar value per row and compare each order's discount or profit against benchmarks like average discount or maximum profit.

Use subqueries in the from clause to create a derived table for pre-aggregation, enabling where filters and joins on aggregated results.

Master subqueries in the where clause to dynamically filter data by comparing main query columns with derived values, such as average sales, to identify high-performing orders or customers.

Master subqueries in joins to connect main data with filtered or pre-aggregated results, such as high profit orders. Apply these techniques to rank, summarize, and filter data within joins.

Apply SQL to a real Vega International retail case, analyzing the normalized sales database for category performance, regional trends, and customer segmentation to create a portfolio project.

Learn to gather and map requirements, document them in a business requirement document and functional specification, and deliver csv extracts and sql queries from five tables.

Create and populate five postgres tables: order transaction, product master, customer master, location master, and date master, from csv files, then explore and validate data against the specification.

Dive into the development phase by writing SQL queries to analyze total profit by customer segment, use joins and aggregates to count orders per customer, and identify top selling products.

Develop advanced sql analytics by joining order transaction, product, and date dimension tables to generate monthly sales by category and sum sales, profit, and quantity by month and category.

Deliver a regional sales dashboard by calculating total sales, total profit, and average shipping time from order transaction data with customer and location joins.

Deliver final go-live deliverables, conduct user training, and export query results to CSV for portfolio-ready sharing on GitHub and resume.