Complete SQL Bootcamp in the ERA of AI from Zero to Expert

Explore data, databases, and the SQL language, including relational database management systems such as PostgreSQL, and understand when to use SQL over spreadsheet tools.

Master the select statement to pull specific columns from tables, with examples from the customers table and table2, and note why using the asterisk is discouraged for performance.

Learn how the distinct keyword removes duplicates in a column by using select distinct column from table, returning unique values such as names and fruits.

Learn how the count function, an aggregate, returns the number of rows. Explore counting by column or by asterisk, handling distinct values and nulls with pgAdmin examples.

Learn to use the where clause to filter rows with conditional expressions, employing comparison operators and logical operators (and, or, not) and handling text values in single quotes.

Master the order by clause in SQL to sort results by one or more columns, using ascending or descending order, and understand behavior with numeric and string data.

Master the SQL in operator to filter by multiple values and exclude with not in, using practical pgadmin examples from film data and first names like John, Isabel, and Liz.

Master pattern matching with like and ilike in SQL, using percent and underscore wildcards, learn case sensitivity, and apply not like and not ilike in PostgreSQL.

Develop SQL skills with challenges that extract distinct film titles from the film table, identify customers named John, and count films by replacement cost between 1 and 10.

Explore aggregate functions and how they simplify queries. Master the group by concept, its theory, and practical use in PgAdmin, with having exercises for solid understanding.

Master aggregate functions in SQL, including sum, average, count, max, and mean. Learn how nulls, distinct values, and rounding affect results in practical queries.

Group by statement teaches you to aggregate data by category, use sum, max, count, and average. Combine multiple groupings with where and order by.

Explore group by challenges to count customers per store, films by rental duration, and payments by customer, using count, average, min, and max aggregations with guided hints and solutions.

Learn to filter grouped data using the having clause after a group by, understand how where vs having differ, and apply aggregate functions such as count and sum in SQL.

Learn sql aliases, temporary names for columns and tables, usable with or without as, improving readability; they apply at the end and cannot be used in where or group by.

Learn why group by still matters in the era of ai, and how human insight, business context, and query verification improve ai-generated sql with efficient aggregation.

Explore how sql joins combine data from multiple tables to create a complete picture, covering inner, left, right, full, cross, self, natural joins, and union, with practical exercises.

The inner join merges data from two tables on a common column, illustrated by employees and departments, with join order not affecting the result.

Master full outer join concepts in sql by combining two tables with all rows, using on to specify the common column, and handling nulls when there is no match.

Explore joining multiple tables using inner joins to map employees to departments and projects, illustrate one-to-many and many-to-many relationships, and use a junction table (employee_projects) to connect data.

Master left join in sql by retrieving all records from the left table and matched rows from the right, handling nulls and using on conditions with practical examples.

Master the right join in SQL by returning all rows from the right table with matched left rows, using on clauses, and compare it to the left join.

Explore the common and different characteristics of inner, left, right, and full joins, how on clauses link tables, and how nonmatches yield null values.

Explore self-joins to compare rows within the same table, learn cross-joins and Cartesian products, distinguish natural joins, and use union and union all to combine query results.

Understand why joins remain essential even as ai writes queries, including inner joins, left joins, and right joins, linking tables, handling missing data, and optimizing performance for complex questions.

Explore core SQL functions, including string, math, and date operations, to transform and analyze data. Blend text, numbers, and timestamps with hands-on exercises to boost SQL power and versatility.

Explore essential string functions in PostgreSQL, including upper and lower case, length, substring, position, trim, ltrim, rtrim, concat, and replace, with PgAdmin practice on a customer table.

Master math operations in PostgreSQL, from basic arithmetic to abs, round, ceiling, floor, power, square, and modulo. Understand edge cases like division by zero, floating point precision, and negative inputs.

Explore PostgreSQL date types and date functions, including current date, current time, now, extract, interval arithmetic, age, and to_char, to manipulate and format date data.

Combine string, math, and date functions in PostgreSQL to build powerful queries, calculate totals and fines, and generate personalized loan messages.

Learn why SQL functions matter in the age of AI, compare function-based filters with range queries, and see how indexing speeds data retrieval.

Explore PostgreSQL building blocks for databases by mastering data types, constraints, and core commands: create table, insert, update, delete, drop, alter, and check constraint.

Create tables in PostgreSQL by defining columns, data types including serial for IDs, and primary keys. Apply not null, unique, and foreign key constraints to link students, subjects, and scores.

Master the insert into command in PostgreSQL to add rows by specifying the table, columns, and values, while honoring primary keys, not null, data types, and foreign key constraints.

Learn to update data in PostgreSQL with the update command, set values, and where conditions, plus the returning keyword, demonstrated in PgAdmin on the city table.

Delete rows from a table with the delete statement using a where clause to target records. Use returning clause to view deletions and perform batch deletes to avoid log contention.

The drop command in PostgreSQL permanently removes objects such as tables, columns, indexes, or databases, with cascade or restrict options to manage dependencies.

Master the alter command in PostgreSQL to add, rename, or drop columns, rename tables, and adjust constraints, with pgAdmin practice and reference to official syntax.

Explore how Postgres check constraints gate data with column-level and table-level rules, reject invalid inserts or updates, and enforce ranges for age, salary, and grade.

Master conditional expressions in SQL to group customers by spending, label transactions as low, medium, or high, and handle missing data gracefully using case, cast, NULLIF, views, and indexes.

Explore casting data types in PostgreSQL using the cast function and cast operator, converting text to integers, handling nulls, and labeling results with case statements.

The QALES function in Postgres SQL returns the first non-null value from an ordered list of expressions and acts as a shortcut for case expressions.

Learn how the nullif function in PostgreSQL compares two expressions and returns null when they are equal, preventing division by zero and simplifying conditional logic.

Level up your SQL by mastering window functions and common table expressions to break down complex queries, perform advanced calculations, and write cleaner, more readable queries.

Explore window functions in PostgreSQL to compute total sales per region without collapsing rows, contrast with aggregates, and learn over, partition by, and order by.

Explore window functions, including ranking, offset, and percentile types, with PGAdmin demonstrations using a payment table. Understand row_number, rank, dense_rank, lead, lag, first_value, last_value, and percent_rank.

Discover how common table expressions with the with clause create temporary result sets to simplify aggregates and improve readability. Reuse computed results in the main query without repeating logic.

Explore how window functions and common table expressions optimize SQL queries, compute daily totals, and calculate each transaction's percentage of daily sales, highlighting human skill in writing clean, efficient queries.