Complete SQL for Data Analytics and Business Intelligence

Set up your local environment by installing the necessary software to follow along and practice on your own, and learn why these tools fit into the database ecosystem.

Install and connect a database server (PostgreSQL) and a database client (Pgadmin) to manage data and metadata on local or cloud servers using SQL.

install and initialize the postgres sql server and pgadmin client on a mac, configure localhost access, and verify setup with a current date query.

Install and configure Postgres SQL server and Pgadmin on Windows, connect to localhost, and run a basic query like select current_date to verify the setup in the query tool.

Explore the pgAdmin interface, including how to access local databases, navigate schemas and tables, run SQL queries in the query tool, save and format SQL, and view results.

Explore how a database serves as a container for database objects and how schemas organize data with tables inside the public schema, demonstrated in pgadmin.

Learn when to create a separate database for an app and when to use separate schemas inside that database, using an e-commerce app and microservices as examples.

Create a database for an e-commerce app using PgAdmin and set up a payment schema. Connect, run SQL statements, and format queries to manage schemas, including dropping schemas and databases.

Learn how to create a customers table in Postgres, define columns and data types, and insert data, with an overview of common Postgres data types and syntax.

Explore the most used numeric data types in Postgres, including serial, integer, numeric, and double precision, with guidance on exact decimals and integer ranges like -2 billion to 2 billion.

Understand char, varchar, and text: char uses fixed length with padding, varchar uses a limit without padding, and text has no limit. Use right type for fixed or variable strings.

Explore date and time data types, including date, time, timestamp, and timestamp with time zone, and see how fields like createdat and login at capture precise moments across time zones.

Explore the boolean data type, which represents true or false values, and learn how null denotes unknown states with examples like a user's active status.

Explore how uuid and enum data types provide unique values and predefined value sets for database design, with practical examples for session ids, mood values, and delivery status.

Create a customers table in Postgres using create table statement, defining id as serial, first_name as varchar(50), last_name as varchar(50), email as varchar(100), and createdat as timestamp with default current_timestamp.

Learn how to insert data into a table using insert into, specify columns and values, and rely on serial id and current_timestamp defaults. Insert multiple rows in one statement.

Deepen your understanding of the select statement to retrieve data from the table in a variety of ways, building on the basics of create table statement and insert into statement.

Learn how to retrieve data with the select statement, using star to get all columns or listing specific columns like first name, last name, and email, with flexible column order.

Transform and format data with string functions and operators to join first and last names, apply upper or lower case, and compute lengths, then alias results for readable output.

Learn how SQL comments, including single-line with double dash and multi-line /* */ syntax, let you annotate queries without affecting execution, improving readability and future reference.

Learn how to filter records in SQL to retrieve a single specific record, such as James, rather than all records, using a variety of filtering methods.

Filter records in SQL using the where clause to select from customers where first name equals 'James'. Learn the execution order: from, where, then select.

Explore how to filter data with the where clause using comparison operators such as equals, not equals, greater than, less than, and between, including is null and is not null.

Master how to filter rows with the in operator in the where clause, matching a list of values such as first names Emily or James to return only relevant records.

Learn how to use the between operator in the where clause to select records within a range, including inclusive endpoints and the not between and not in variations.

Use the like operator to perform pattern matching on strings in SQL, using % and _ wildcards in Postgres to filter first names containing a specific letter.

Learn to build compound where clauses by combining multiple conditions with and or, filtering records like first name James and email equals James dot Brown at example.com from customers.

Extend your SQL skills by updating and deleting records in a table, building on creating, inserting, and querying with where clause conditions.

Update records in a table using the SQL update statement, set keyword, and a where condition. Update single or multiple fields and see why a precise where condition matters.

Use the drop table statement to delete the complete table from the database, including its data and structure. Be careful: after dropping, the table no longer exists.

Explore table relationships, primary keys, foreign keys, and constraints to design a robust database, prevent duplicate IDs and null values, and manage relationships across multiple tables.

Demonstrates one-to-many and many-to-one relationships using a five-table soap database, with examples showing how customers, orders, order items, products, and reviews relate.

Explore 1 to 1 and many to many relationships with real examples like users and profiles, citizens and passports, and authors and books to clarify relational types.

Identify how a primary key enforces unique, not null identification of records, with the id column as the typical choice and constraints preventing duplicates.

Relate tables with foreign keys and enforce data consistency, using orders.customer_id to reference customers.id. Discover how primary keys anchor these relationships.

Explore how foreign key constraints enforce data integrity between customers and orders, and how on delete options such as restrict, cascade, set null, and set default control deletions.

Understand how foreign key constraints affect inserts between parent and child tables. Inserting a non-existent customer_id fails; setting the foreign key to null enables the insert.

Explore how column constraints enforce data rules in sql, including primary key, foreign key, unique, not null, default, and check, with practical examples on customers and orders tables.

learn how to obtain complete table schema information using information_schema queries, psql backslash d, and pgadmin create script, including primary keys, foreign keys, not null, unique, and check constraints.

Explore sql joins with practical e-commerce examples, building five tables and practicing join concepts through exercises. Build a solid understanding of joins using a customers and orders dataset.

Build a complete e-commerce database by creating five tables with proper constraints, using the shop_dataset.sql script to run via pgadmin, validating data, and learning numeric monetary types and check constraints.

Explore why joins are essential to combine data from multiple tables, enabling you to list customers with their orders and details like customer id, name, order id, date, and status.

Master inner join basics and compare it with left, right, and full joins. Link customers and orders using the on condition customers.id = orders.customer_id to surface only matching records.

Explore left join, also known as left outer join, by joining customers and orders, compare with inner join, and see how unmatched left-table rows become nulls.

Explore the full outer join by combining customers and orders, showing all matching records and remaining rows with nulls where there is no match.

practice solving inner join problems by linking customers and orders to list customers who placed orders, selecting and aliasing fields such as customer id, name, order id, date, and status.

Learn to use table and column aliases to produce readable, compact sql output by renaming columns and tables, with the as keyword optional and conventions guiding usage.

Join the products and reviews tables to show only items with customer reviews, displaying product id, product name, rating, and review text using an inner join.

Demonstrates using a left join between customers and orders to show all customers, including those with no orders, returning customer id, customer name, order id, order date, and status.

Apply a full outer join to combine customers and orders, including customers with no orders and orders without a customer, and select customer_id, full name, order_id, order_date, and status.

Learn inner join with filters to list customers who placed delivered orders, including customer and order details such as customer id, name, order id, date, and status.

Master aggregation and grouping in SQL to answer analytical questions on an e-commerce database, using sum, average, and count to analyze sales, ratings, and customer data.

Explore how aggregation reduces many values to one using SQL aggregate functions like max, min, sum, and average, including expressions, aliases, total revenue, and null-aware counts.

Learn how grouping reveals unique order statuses by grouping rows on the status column, identifying delivered, shipped, pending, and canceled, and preparing for the SQL queries in the next video.

See how the group by operation clusters the orders table by status to yield four unique values—delivered, pending, shipped, cancelled—while the video explains the internal grouping process.

Combine grouping and aggregation to count orders per status using group by and count, demonstrating how groups form and how aggregate functions apply to each group.

Learn to compute the average price of products by category using grouping and the average function on the products table.

Learn to filter groups after aggregating by category using the having clause, extracting categories with an average price above 50, and contrast with the where clause for row filtering.

Learn how to use where and having together to filter rows before grouping and filter groups after, with an example counting delivered orders per customer.