Mastering SQL using Postgresql

Connect to a Postgres database in a JupyterHub environment to master SQL using Postgres, with sql magic and a configured database URL.

Explore using psql command line to connect to a Postgres server, manage databases and tables, run queries, and use commands like \l, \c, \d, and \i for troubleshooting and testing.

set up postgres with docker by pulling the postgres image, creating and starting a container, and publishing ports; connect with psql via docker exec or host port.

Install and set up SQL Workbench as a database IDE to run queries, manage databases, and preserve scripts, including download, unzip, and driver consolidation steps.

Connect to a postgres database using sql workbench, download the jdbc driver, gather ip or dns alias, port, database name, username, and password, test the connection, and save the profile.

Leverage SQL Workbench to connect to Postgres and other RDBMS, save profiles, explore database objects via the database explorer, and generate scripts for data operations to boost productivity.

Load data into a postgres database in a docker container using the copy command. Create the database and user, grant pg_read_server_files, then copy csv data and load into a table.

Get an overview of database operations, including ddl, dml, and dql, covering tables, indexes, constraints, views, sequences, and basic transaction control with commit and rollback.

Explore CRUD operations in databases, detailing create, read, update, and delete with DML and REST API mappings, and contrast roles for developers, data engineers, testers, and analysts.

Create the table by connecting to the database and running a create table statement, using jupyter sql magic or sql workbench. Drop and recreate when needed, then validate with queries.

Mastering sql using PostgreSQL explains how to insert data into tables with the insert into syntax, including column lists, values, serial handling, defaults, and committing.

Learn to update data in postgres with update and set clauses, optional where conditions, derived values via upper and concat, and adding columns with alter table.

Delete data from a table using delete from table where condition; use is null for null, avoid deleting all rows without a where clause, and validate with select and count.

Understand how transactions manage changes via commit and rollback within a session, with auto commit defaults and examples like cart checkout and payment outcomes.

Practice database operations in Postgresql by creating a courses table, inserting records, updating drafts to published with current_date, deleting inactive records, and validating published counts by author.

Explore standard SQL transformations: projecting data with select and derived columns, filtering with where, aggregations and group by, joining tables with inner and outer joins, and sorting by multiple columns.

Explore the retail data model with six tables: orders, order items, products, categories, departments, and customers, and learn one-to-many relationships, hierarchical structures, and basic SQL queries including joins and aggregations.

Define the problem and compute daily product revenue by joining orders, order items, and products, grouping by date, product id, and product name, summing order item subtotals for complete orders.

Prepare retail tables in postgres by creating the database and user, granting permissions, loading data with scripts, and validating with jupyter hub or sql workbench.

Learn how to project data with select queries, alias columns with as, and use distinct and aggregates to summarize orders while deriving year-month values and inspecting metadata.

Discover how to filter data in PostgreSQL with where clauses, comparison operators, like and tilde pattern matching, and null handling using is null and is not null.

Join orders and order items with an inner join using on, alias columns for clarity, and place filters in where; you can extend to multi-table equi joins.

Master outer joins in PostgreSQL by using left, right, and full outer joins to combine orders and order items, handle nulls, and validate data quality.

Master SQL aggregations, including global totals and aggregations by key, using group by and having; understand execution order, revenue calculations, rounding, and filtering of aggregated results.

learn to sort data in sql with single and composite fields using asc or desc. grasp the query execution order from select to order by and alias usage.

Learn to compute daily product revenue by joining orders, order items, and products, filtering complete or closed orders, and grouping by date, product id, and product name to produce revenue.

Practice basic sql queries through five exercises on orders and customers for January 2014. Solve for customer order counts, dormant customers, revenue by customer and category, and department product counts.

Explore data definition language (DDL) in PostgreSQL, including creating tables, indexes, and constraints, with best practices for production and how to manage comments and metadata using information_schema views.

Explore postgres data types like integer, serial, varchar, boolean, and date, and see how surrogate keys use sequences and how not null and length constraints apply.

Master the alter table command to add, modify, or drop columns, create sequences for serials, set defaults including false and current timestamp, and apply multiple column operations in one statement.

Learn the core rdbms constraints not null, check, unique, primary key, and foreign key through practical lms examples with users, courses, and students, including composite and surrogate keys.

Manage constraints in PostgreSQL by adding, dropping, and naming constraints with alter table, enforce not null, check, primary key, unique, and foreign key rules, and model users to user_logins relationships.

Explore how postgres indexes work, including unique and non-unique b-tree indexes and bit-map options for dense columns, and how foreign key indexes on order items order id boost join performance.

Learn how indexes support constraints such as primary keys and unique constraints, enforce not null, and improve query performance; discover how foreign key indexes and implicit indexes work in PostgreSQL.

Learn how Postgres sequences support surrogate primary keys, using serial, nextval and currval, and how to configure start, increment, min and max values, restart with, alter sequence, and cascade drop.

Learn how to drop tables, handle dependent objects and constraints with cascade or by dropping constraints first, and understand when sequences are dropped based on column ownership.

Discover postgres partitioning with list, range, and hash methods, including multilevel and nested partitioning, primary key considerations, and global versus partition indexes.

Explore list partitioning in a database by partitioning a users table by user_role, creating a partitioned table users_part, and managing partitions (add, default, detach) to ensure data insertion succeeds.

Manage list partitions for a partitioned table by creating partitions such as users_part_u, users_part_a, and a default partition, then move data between partitions with detach and drop operations.

Learn how to manipulate data in a partitioned table by using the main table rather than partition tables, covering insert, update, and delete across list, range, and hash partitions.

Learn range partitioning by creating a table partitioned by range on created date, ensuring the partition key is in the primary key and adding yearly partitions before inserts.

Learn range partition management in PostgreSQL by creating year-based partitions, handling default values, and using the main table for inserts and queries.

Repartition a range-partitioned table from yearly to monthly by detaching yearly partitions, creating 60 monthly partitions with a Python and pandas automation, and validating data placement.

Explore hash partitioning for sparse columns by creating a users_hash_part table partitioned by hash on user_id, manage partitions, and validate with inserts.

Master hash partitioning in Postgres by creating eight partitions with modulus and remainder, using user id as the partition key and hashing to route inserts.

Explore partitioning usage scenarios for large tables, including log and reporting tables, and learn to reclaim storage by dropping partitions while comparing list and range strategies on created date.

Master sub partitioning in Postgresql with nested list and list-range strategies, creating yearly and quarterly partitions using main and sub tables with year and month keys.

Partition the orders table by month using range partitioning on order_date. Load data into orders_part and validate that counts match orders across all partitions, with a default empty partition.

Master predefined Postgres functions across string, date, numeric, type conversion, and case and when. Learn to query catalogs, consult postgresql.org docs for version 13, and explore examples like substring.

Explore PostgreSQL date manipulation functions to get current date or timestamp and add or subtract days. Write queries for month-to-date and year-to-date reports, compute differences, and work with calendar components.

Explore numeric functions in Postgresql, including abs, round, ceil, floor, greatest, min, max, sum, average, random, pow, and sqrt, with practical aggregation, decimals, and type casting.

Demonstrate data type conversion in Postgresql by casting values with :: and cast(... as ...), and using split_part and to_char to extract and convert date and numeric values.

Learn how to handle null values in Postgres, differentiate null from empty string, and use coalesce to replace nulls with defaults, plus nullif for safe comparisons.

Learn to use case and when to derive order statuses from the orders table, mapping complete or closed to completed, pending variants to pending, and else to other.

Understand how views act as named queries that do not store data, and learn to create updatable views and joins to support modular, reusable sql queries.

Explore how the with clause defines named queries to simplify large, repeated logic and how using views can reuse these named queries for consistent daily revenue reporting.

Learn sub queries and nested sub queries in postgres, including from clause usage, alias rules, and in or exists filtering.

Master ctas (create table as select) to build tables from query results, leveraging aliasing and explicit type casting for derived fields, while ctas copies names and types but not constraints.

Explore advanced DML operations, including insert with select, update, and delete using query results; learn pre aggregating customer order metrics and using not exists or not in for deletes.

Master upsert techniques in Postgres by merging data with update and insert. Compare two-statement and one-statement approaches using on conflict to optimize performance.

Explore SQL analytics with aggregate and window functions, using over, partition by and order by, to compute daily revenue and daily product revenue via practical demonstrations.

Learn to perform aggregations with analytic functions using the over clause and partition by, producing department salary expenses and employee percentage with raw data alongside aggregates.

Learn to implement cumulative and moving aggregations in Postgresql using window functions and the ROWS BETWEEN clause, with examples on monthly partitions and moving sums and averages.

Learn windowing functions in PostgreSQL, including lead, lag, first value, and last value. Use over with partition by and order by to compute prior or following records.

Learn to apply rank, dense_rank, and row_number analytic functions with partition by and order by to compute global and partitioned rankings on revenue and salary.

Explore filtering on analytic function results and computing top 5 daily revenue products using derived values, subqueries, and proper sql execution order.

Compute daily product revenue from orders and order items, then rank products by revenue and filter the top 5 per day using raw queries, subqueries, or precomputed tables or views.

Sign up for Google Cloud Platform and claim $300 in credit for a year to practice Linux, Python, and SQL, with optional use of AWS or Azure.

Explore the GCP web console to provision and manage virtual machines with compute engine, view VM instances, configure firewall rules, and review billing details.

Explore using the GCP pricing calculator to estimate a 4 vCPUs, 16 GB RAM, 60 GB storage virtual machine in Los Angeles, and learn cost optimization with credits and stops.

Provision an Ubuntu 18.04 virtual machine on Google cloud platform with an e2 standard 4 and 60 gb disk. Use the web console and set a static ip.

Validate python3 on Ubuntu 18.04, launch the python CLI, and verify installation of pip, venv, and configparser to support libraries.

Set up a Jupyter lab on an Ubuntu Gcp vm by creating a Python virtual environment, installing Jupyter Lab, and configuring Gcp firewall to access via port 8888.

Set up a Postgres database on an Ubuntu VM using Docker, create a database and user, and verify a remote connection with psql and SQL Workbench.

Set up a postgres database in docker by pulling the postgres image, creating and starting a container named sms_pg, publishing port 5433, and verifying via logs.

Explore essential docker commands using postgres as an example, including pulling images, creating and starting containers, publishing ports, viewing logs, and managing images and containers.

Connect to a postgres database running in a docker container using the docker command line interface. Learn to attach to containers with docker exec, run psql, and validate connectivity.

Learn to create a postgres database and user via psql in a docker container, grant permissions, and connect as the user to perform basic table operations in sms_db.

Learn to execute sql scripts with psql, clone the GitHub retail_db repo, set up retail_db and retail_user, run scripts to create tables and load data, and validate with queries.

Learn how to install and set up SQL Workbench, a database IDE, including downloading, unzipping, launching the jar, and organizing drivers to connect to databases.

Connect to a Postgres database running in a docker container on a gcp ubuntu vm using sql workbench, after configuring firewall rules, port 5432, and the jdbc driver.

Explore SQL Workbench features for connecting to Postgres, MySQL, Oracle, Redshift, and other DBs with saved profiles, and use the database explorer to view tables, constraints, and sequences.

Integrate Jupyter Lab with Postgres to practice SQL in an interactive notebook. Install ipython-sql, sql alchemy, and psycopg 2 to connect notebooks to Postgres and run queries without extra code.