Business Intelligence Mastery with SQL and Tableau [2-in-1]

Install PostgreSQL and pgAdmin to create and connect databases, and run SQL queries in a graphical interface. Learn basic setup, port considerations, and password management.

Celebrate reaching the milestone in business intelligence mastery with SQL and Tableau, joining the top 50% of learners and staying motivated to complete the course.

Create and connect a test database in pgadmin, then design tables for customers, products, and transactions using create table syntax with data types and primary/foreign keys.

Build a classroom database and a science class table with enrollment number, name, and signs marks using sql in the query tool, guided by practice exercises.

Learn to insert data into the customer table using insert into, with optional column names and correct value ordering, including single and multi-row inserts and handling not null constraints.

Import data from files into a table using the copy command in Postgres. Specify the table, columns, file location, delimiter, and file type (csv or text) with header handling.

Insert the scores for three students into the science class table using the insert into command, then import the attached student CSV file with eight more scores.

Learn to retrieve data from the customer table using the select command, selecting specific columns or all columns with a wildcard, and view the resulting records.

Use the distinct keyword in select queries to remove duplicate rows and fetch only unique values from a table, with examples handling single and multiple columns and case sensitivity.

Master retrieving data with the where clause by applying numeric and textual conditions in select statements, such as age equals 25 or first name matches, including distinct results.

Learn to retrieve data with multiple conditions using and, or, and not operators. Use syntax select columns from table where condition, and apply age between 20 and 30 with and.

Practice SQL select and where to filter student scores, retrieving names above 60, data for scores between 35 and 60 marks, and the remaining records using logical operators.

Master SQL update queries by setting new values in a table, using where conditions to target records, with examples updating age, last name, and emails.

Learn how to delete rows in SQL using delete from with a where clause, including single and multiple row deletions and deleting all rows from a table.

Master how to use the alter table command to reshape a table by adding, deleting, modifying, or renaming columns, managing constraints and indexes, with pgadmin examples.

Apply and remove constraints on SQL tables using alter table, including not null and check constraints. Demonstrate adding primary keys and foreign keys, and observe constraint enforcement during inserts.

Update Popeye's science class score from 33 to 45. Delete Rob's row and alter the table to rename the column from name to student_name.

Learn to backup and restore a database using tar format, create a new database named supermart_db, restore the customer, product, and sales tables, and run select star queries to explore data.

Learn to diagnose and fix database restoration issues in PostgreSQL with pgAdmin by adjusting binary paths, ensuring a new database, and using tar, backup, sql, or directory restores.

Create the supermarket database by building customer, product, and sales tables from CSV files and loading data with PostgreSQL's copy command. Define primary keys and verify data.

Back up your database into a tar file, then restore it after accidentally dropping the science class table, mastering backup and restoration workflows.

Explore the in condition within where clauses, replacing multiple or statements with a single block for many values, with examples from Philadelphia and Seattle.

Use the between condition in SQL to retrieve values within a range with inclusive lower and upper bounds, including dates, and note that not between excludes a range.

Explore the like condition in sql for pattern matching with wildcards, using percent and underscore to filter strings by start or end, and not like, distinct, and escape character.

Apply SQL operators in where clauses to filter distinct cities in the north or east regions, select sales orders between 100 and 500, and find customers with four-character last names.

Learn how to add multi-line and single-line comments in SQL, understand that commented text isn’t executed, and see how comments are highlighted in the editor.

Master the order by command to sort query results, using ascending or descending order, multiple columns, and column numbers, for strings and integers, with optional where clauses.

Limit the number of rows returned by a query with the limit statement, using order by to show the top eight oldest customers and the ten youngest ones.

Sort the result set by discount in descending order to show orders with positive discounts, placing the maximum discount at the top and limit to the top ten orders.

Learn how to use the as keyword to alias columns and tables, create meaningful headers like serial number and customer age, and prepare for joins in SQL.

Explore aggregate functions in SQL, focusing on count, sum, average, min, and max; learn counting non-null values, using where clauses, aliases, and distinct to analyze sales data.

Master the sum function in SQL to compute aggregates, using select sum, aliasing, and where conditions to calculate totals like total profit and total quantity.

Explore the avg function to compute a column’s mean with select avg(column) from table, illustrated by average customer age and average commission values.

Learn how to use min and max functions in SQL to find the lowest and highest sales within a date range, with practical examples from June 2015.

Apply aggregate functions to sum sales, count North region customers aged 20–30, and determine the average age in the East region plus min and max ages in Philadelphia.

Use the group by clause with aggregate functions—sum, count, average, min, max—to group data by one or more columns and compute regional or customer statistics. Learn aliases and multi-column groupings.

Explore how the having clause filters groups by applying conditions to aggregate functions, contrasting it with the where clause that filters non-aggregates, through practical examples.

Master group by in SQL to compute sales value, quantity, orders, and max, min, and average sales for product ID. Use having to filter product IDs with quantity over 10.

Practice lab guides you through sql tasks using split-screen modes in phpMyAdmin or PgAdmin, tackling California customers, payment methods, and cod orders in 2021.

Learn to use case expression as a conditional expression to categorize data with when-then-else-end, aliasing the result as age category.