The complete SQL & Python for SQL with Hands-on Questions

Drop the student table if it exists, then create a student table with tax as the data type for the name and an integer column, and run a select *.

Create a second table in PostgreSQL by dropping existing, defining id serial primary key, custom_id varchar(50), transition bigint, bertha date, sales_date date, quantity numeric, and inserting three records.

Drop the table with cascade to remove dependencies, define the data structure, load data from a file using the copy statement, and verify the contents match the source.

Explain how to create the teachers table with cascade, define integer id and text name, load data from a csv with copy, and verify by selecting all records.

Navigate the PGA domain, drop the subject poker table with cascade, create the subjects table with id (text) not null and subject (varchar(50)), then upload and browse data.

Learn to use Python to connect to PostgreSQL, upload CSV files, extract data into pandas data frames, and automate queries and batch tasks in PostgreSQL using Anaconda and Spyder.

Learn how to connect Python with PostgreSQL using SQLAlchemy and psycopg2, install the packages, and test the connection to run database-agnostic code with an ORM.

Learn to create tables like subjects and teachers in PostgreSQL by loading csv data with pandas, connecting via SQLAlchemy, and uploading data frames into the database.

Run python code to upload two CSP files, validate data with select statements in sql and pg admin, refresh, and confirm two data frames appear in the python console.

Learn to browse data with select statements in SQL, specify columns or use * to fetch all, and run queries in pgAdmin to fetch rows from tables.

Connect Python to a PostgreSQL database via SQLAlchemy, issue queries with SQL statements, load the results into a pandas DataFrame, and print the DataFrame in the Python console.

Learn to automate uploading multiple CSP files into Postgres tables with Python, using pandas dataframes and SQLAlchemy, creating a table per CSP file and validating results.

Import CSV files into PostgreSQL using PgAdmin by creating a sub_score table with student_id integer not null, subject_id text, and score numeric, then import via PgAdmin with utf-8 encoding.

Learn to save a query result into a specified table by creating a table as select from retail_transactions, handling drop if exists, and retrieving the top 10 records.

Apply where clause filters to the retail table using gender, H, and range conditions, including between 35 and 40, and not missing with and or logical operators.

Learn aliasing in PostgreSQL using as to assign aliases. Apply max, min, and average with where and group by to summarize retail sales and save results in a table.

Explain and execute SQL solutions by creating a table, using aggregate functions (count, max, min, avg), and filtering scores above 60 with subject ids 1, 2, 3.

Connect python with a postgresql database using psycopg2, create a connection and cursor, execute sql, fetch results into a data frame for manipulation, and close the connection.

Learn how to use group by in SQL to compute transaction counts, total sales, and average sales per product_id and score_id, with a where clause filtering sales_amount greater than 100.

Compute the average sales_quantity and average sales_amount (sales_quantity times price), and the total number of transactions for each class of transaction_type, then save the results into a new table.

Learn to use the order by clause in sql to sort by average score and subject count in descending order, grouping by student id with a column alias for ranking.

Use the having clause with group by to filter aggregated results, computing each student's average score and subject count, and retain rows with average > 80 and count > 2.

Explain and execute SQL solution that drops and creates table, computes each student's average score and subject count, groups by student id, uses having, and orders by average descending.

Explore advanced having usage in sql with the retail_transactions table, using count, average, min, and max to answer queries, apply group by, and sort results with order by.

Learn to explain and execute sql solutions using aggregation like count and average, with group by, having, quotes for identifiers, and aliases to produce clear per-customer and per-category results.

Explore string functions in PostgreSQL by creating a worker table, inserting sample records, and executing queries to practice text manipulation.

Convert the full name column to uppercase using the upper function in PostgreSQL, and verify with the lower function, while practicing create drop table and select statements.

Extract the last name from a full name and concatenate it with gender using PostgreSQL string functions, including char_length, position, substring, and double pipe concatenation.

Create and analyze sql queries to build a worker table, compute full name length, locate the space position, extract the first name with substring, and concatenate it with gender.

Summarize and review key PostgreSQL string functions and operations, including manipulating string values in select statements and working with character and text types.

Execute fuzzy queries using like with underscores and percent to match full names in the worker table, recognizing underscore as a single character and percent as any string.

Solve a fuzzy query using like, percentage, and underscore operators to filter worker records by a first-name pattern, employing substring on the full name for the match.

Explore fuzzy query practices with like patterns, underscore and percentage wildcards, and substring-based conditions in where clauses.

Learn to generate 30 percent random samples from the sales_employees table using the random() function and a where clause with 0.3 threshold.

Demonstrate extracting a 50 percent random sample from the original sales_employees table using the random function in a select statement, then create and verify the new table sales_employees_random.

Master inner joins to merge rows from two tables on a common key, using aliases and join conditions, and extend with where, group by, and having clauses.

Join (inner) retail_cost_ideas and retail_transactions on customer_id, compute average(quantity) and count by customer_id and gender, with where quantity > 1, gender = 'female', having count > 3.

Join the public dog retail transactions with a full/outer join on customer_id to create tb, selecting unique records where quantity >3, item category bakery, gender F, and age 45–50.

Learn to perform an equivalent inner join across student lists, scores, teachers, and teacher class to filter students by average grade above 85 using group by, having, and order by.

Execute and test a SQL solution by creating a table, using distinct selects, applying outer joins and unions, and filtering by quantity, category bakery and meat, gender, and age.

Explain and execute SQL solutions by dropping and creating tables, inner joining sales records with sales employees, extracting substring, and computing sum, count, and average by area and product id.

Master self-join in SQL to compare quantities across paired retail transactions, using sum(a.quantity) and sum(b.quantity), with group by sales date and item name pairs in pgAdmin.

Learn how to use cross join in SQL to generate a cartesian product of two customer tables, creating paired customer IDs and average quantity columns, with cautions about large results.

Explore cross joins and self joins in SQL by creating tb_cross and pairing price sets for any two products. Filter to ensure the products differ.

Master data type conversion functions in SQL, including converting numbers to text, strings to date, and strings to timestamp, and analyze related tables with diagrams to understand their relationships.

Learn how to create a new SQL table with a date column, define default current_date, set up a serial primary key, and insert data (including from another table) using insert…select.

Learn to manipulate dates in SQL using extract, date_trunc, and date_part to derive year, quarter, month, week, and day from timestamps and intervals, plus formatting options.

Learn to generate date, interval, time, and timestamp values using make_date, make_interval, make_time, and make_timestamp; compute today with date_trunc and current_date; derive hire dates by subtracting intervals and casting.

Explore handling missing values in SQL using is null and is not null, filter income records, and run the provided scripts on the GAAP table.

Analyze credit risk by studying past due patterns in an SQL database, load pass_do data, take 50 percent random samples, determine minimum time to become bad with Python.

Learn to use case when in SQL to conditionally assign a new column, such as H < 30 yields L30, otherwise san, highlighting its similarity to if-else in Python.

Demonstrate the first case when method to create a new column 'student level' from aggregated average scores, assigning fair, good, or excellent, and order results by descending average scores.

Explore sub queries in sql, including inner and outer queries, and apply exists, not in, any, sum, and all within where to create temporary data sets.

Use a sub query with exists to filter customers by quantity in retail transactions, item category (bakery or meat), female gender, and age 45–50, joined on customer id.

Explain and execute a SQL solution using subqueries and joins to create a table, filter customers by age 45–50 and female gender, and retrieve ids from bakery and meat datasets.

Learn to apply the any operator with subqueries to select records where quantity exceeds the average quantity per item category, and validate with a follow-up query.

Learn to use subqueries with the all operator to ensure 30 percent of quantity is less than the minimum average quantity across all item categories in retail transactions.

Stack tables in SQL (2) demonstrates building a new table by stacking grocery and meat data using union, matching column positions, with quantity greater than three.

Stack two tables in sql using union and union all, demonstrating how union removes duplicates while union all preserves duplicates, with grocery_costs and meat_costs examples.

Learn how to stack two SQL tables using the intersect operator to obtain records common to both grocery_cars and meat_cuts, and create a new table from their intersection.

Demonstrates inserting data into a customers table with unique and not null constraints by selecting customer ids by gender from a source table and inserting them, verifying in pg admin.

Define a retail table with a serial id as primary key and check constraints: id > 0, birth date > 1910-12-31, sales date > birth date, and quantity 1-99.

Demonstrate group primary keys in SQL by creating a product table with store and item category as the primary key, ensuring unique (store, item category) pairs.

Learn to implement foreign key constraints by ensuring a column's values match the referenced table, defining foreign keys to product, and inserting filtered retail transactions data.

Learn to apply table constraints by creating a car drivers table with primary key, unique and not null fields, and a check that expiring date is after birthday, via inserts.

Update the H column in young_gaap by adding five for records where H is under change, ensuring the customer has a retail transaction with quantity above the average.

Learn conditional updates across related tables with join-like syntax, updating qualified customers in retail_transactions by adding 5 to ages using data from the young_gaap table, where transactions exceed the average.

Add columns to a table using alter table add column, creating birthday (date) and birthday_year (date), then update birthday_year from birthday using current_date and interval year.

Learn to drop columns from a table using alter table drop column, including removing two columns in a single statement in a practical sql example.

Learn to rename columns in an existing table with alter table rename column, including renaming gender to sex and another column to its new name, demonstrated with an example.

Learn to rename a table in SQL using ALTER TABLE, with IF EXISTS, and rename from an existing table to a new table name.

Learn to delete records from tables using where clauses, delete from a table using another table, and manage tables with create and drop statements including cascade, then count the results.

Learn two SQL methods to delete records from a table using related data from other tables: delete with a where/join condition and delete with a subquery, and validate the results.

Demonstrate roll up in a group by to join goods and goods_sell, apply filters MasterCard, Visa, PayPal, and CMT > 100, and compute sums and counts in pandas.

Define a marks table with an auto-increment serial key and array columns for score and subjects. Store addresses as arrays and leverage four-element integer and text arrays for efficient searches.

Learn how to work with array columns in SQL by applying any and all conditions, aggregating values, and converting arrays to strings for data manipulation and analysis.

Learn to create a PostgreSQL view from the sale_records table, selecting sales_id, product_id, and sale_amount, with records where sale_amount exceeds the product average using a subquery and self-join.

Create a view named sales_south in PostgreSQL from sales_employees, then insert through the view to modify underlying data and observe how inserts, updates, and deletes map to the base table.

Learn to create a PostgreSQL view named students_score from three tables by joining on student_id and subject_id, and observe why deleting from this multi-table view fails.

Solve year over year analysis of sales using sql by calculating monthly percentage changes and identifying top five salespersons by total sales, using month-level and salesperson-level aggregation.

Explain and execute SQL window function queries to compute per item category aggregates (count, average, max) and a grand total in the same row, highlighting limitations of group by.

Learn to create a stored procedure in pgAdmin by using the functions wizard, defining return type and language, viewing generated scripts, and executing the procedure with sample queries.

Write a PostgreSQL stored procedure (function) using create or replace, returns integer, with a begin…end block in PL/pgSQL and a loop summing 1..k up to 100.

Write a PostgreSQL stored procedure using pure SQL language that returns all columns with returns setof, and execute it to query public.retail_transactions where customer_id > 2470, limit 20.

Learn to write a PostgreSQL stored procedure that creates a table with a serial primary key and a customer_id column, then inserts five rows using dynamic SQL with format.

Write a plpgsql stored procedure in PostgreSQL to join two tables and create a new table by inner joining on a given key, returning void.

Create a PostgreSQL stored procedure that adds a per-customer sequential transaction number column for retail transactions, accommodating multiple transactions per customer.

Learn to implement PostgreSQL triggers and a trigger function that, on updating the donuts table, inserts the old customer_id into a history table when the customer_id changes.

Master regular expressions in sql to perform fuzzy matching in where clauses, using patterns, case sensitive and case insensitive matching, and dot and star operators to filter results.

Introduce regular expressions in SQL with examples that explain dot and star for matching any character and repetitions, plus brackets, digits, whitespace, and word boundaries.

Learn how to use regular expressions in sql to test pattern matches and extract the matched text, including default options for flexible extraction.

Extract text using regular expressions in SQL, returning arrays of matches, substrings, and patterns, while understanding capturing versus noncapturing groups and star and plus quantifiers.

learn to use the regular expression replace function in sql to modify strings by pattern, with examples like replacing bakery_score in the store_department column with food.

Learn to split text strings with a regular expression in SQL, using regexp_split_to_array to divide values by spaces and to compute sums by the split columns.

Introduce statistical functions in PostgreSQL, building on aggregate functions like sum, average, min, max, and count. Use these to compute advanced statistics such as correlation and regression coefficient.

Prepare data for statistical analysis by loading a CSP file into a SQL table with pandas and SQLAlchemy, creating car_dealer, and verifying results via a top-5 display.

Apply linear regression to predict price from kilometres, grouped by automatic, using Python and Postgres to compute slope and intercept and store results in car_model, with r-squared interpretation.

Explore PostgreSQL administration basics, including checking the current database version, identifying table ownership, and managing users and privileges. Create tablespaces and schemas, and learn how to duplicate data across databases.

Learn how to administer PostgreSQL by listing and managing databases, viewing current database and schema, working with tables and tablespaces, and understanding owners, users, and roles.

Learn PostgreSQL administration by managing roles, login users, and superusers; grant and revoke privileges, alter roles, and control table ownership and access, plus tablespace and database operations.

Explain how to check the current version with select version() and execute the SQL to confirm the version is 9.5.40.

Learn how database ownership works in PostgreSQL, including who owns a new database, how the current user becomes the owner, and how to set a different owner with create database.

Discover how to access and interpret database metadata, including system catalogs and the PD catalog, and review operations like dropping, recreating, and updating tables while preserving catalog integrity.

Inspect the PD and PGE catalogs and views to see database names, owner IDs, and tablespace IDs, then translate IDs to names with the PGE get user by ID function.

Explore how namespaces and catalogs organize schemas, including the public schema, and how the information_schema exposes metadata about tables, columns, and other objects.

Query the information_schema's schema metadata view to retrieve the resulting dataset and identify all schemas in the current database that a user has accessed.

Learn how to create a new schema in the database using pgadmin, and understand that a schema groups tables, indexes, sequences, data types, operators, and functions.

Create a new schema in PgAdmin by entering the schema name and owner, then grant privileges on the public default schema and copy the generated schema code for reuse.

Create a user using the create user command, assign a password, and grant all privileges to that user. Then check the user in the pg_catalog.pg_user table.

Execute and verify SQL user management: revoke permissions, drop and recreate user ABC with encrypted password, grant post privileges, and verify creation by querying the user catalog.

Set a user's password in SQL using the ALTER USER statement, then verify the new password takes effect after briefly locking the user.

Understand roles in databases by defining, creating with a password, registering as a user, assigning rights, and practicing search, drop, and manage operations.

explore how PostgreSQL treats roles as users or groups to manage access permissions, identify login rights for the current user, and query pg_catalog.pg_roles to browse roles.

Explore role groups in PostgreSQL, learn to check and create a role group like ABC with a password, modify it to no logging, and grant it to users.

Select roles, create a row abc with password abc, then grant the logging super user right to abc and verify the catalog reflects the change.

Execute the discussed solution code: drop the row, create the new road signs with a password, use auto roll to set the locking user, grant post GRC, and revoke.

Explore how tablespaces represent the physical on-disk locations of database objects in PostgreSQL, and learn to browse and list them with select * from pg_catalog.pg_tablespaces.

Learn how to check and inspect tablespaces with SQL, view existing and new tablespaces, and explore their properties.

Learn to create and drop table spaces in PostgreSQL, defining alternative storage locations for tables and indexes, using distinct names and the create table space statement.

Explain how to use the create database statement, including syntax, required privileges, specifying database name and owner, encoding, template, and tablespace, with a practical example.

Learn to create a new database in pgAdmin, using the UI or SQL, by setting the owner, name, encoding, template, and tablespace, then executing the create statement.

Learn to use alter database to rename databases, transfer ownership, and move the default tablespace, with syntax rules and privilege requirements.

Learn how to back up and restore databases using pgAdmin, with practical examples of backing up and restoring databases.

Navigate the P.G. Artemis interface to the item database and perform maintenance tasks—analyze, reindex, cluster, and vacuum—to manage data tuples and garbage collection.

Learn how to maintain and back up databases and perform restores from a dump file, using options to restore blobs, owner, or restore only schema or only data.

Master how to drop a database using the drop database command or pgAdmin, with a built-in warning about data loss and the two available methods.

Learn how table ownership works: the owner, a user or role who created the table, holds all privileges, and how to change ownership with alter table students owner to ABC.

Execute and verify SQL code by changing ownership to ABC, confirm the query runs successfully, and inspect the table to ensure ABC is the owner.

Explore how to describe a table in PostgreSQL by querying information_schema.columns to print schema and column data types, with examples using table_name filters.

Inspect and describe table structures in a database by querying catalog metadata, listing schemas, tables, and columns, and filter to identify column names and data types for a specific table.

Master SQL and Python through hands-on questions, using SQL Server and Oracle Database templates, and extend to SaaS or cloud data analysis for practical data science and machine learning skills.

Ingest CSP transaction data into the PRANs table with Python, pandas, and psycopg2, then apply three fraud rules: >6 transactions in 22 hours, >4 short durations, total >5k.

Load a csv file into a table, transpose data to a customer-level layout, and apply SQL rules to detect fraud from rapid, multiple transactions and high totals.

Connect Python with PostgreSQL using psycopg2, ingest transaction data into a new table, then extract with pandas to validate the data ingestion.

Explore item-based collaborative filtering by building a movie recommender system that uses Pearson correlation on user ratings to compute item similarity and generate personalized recommendations.

Ingest data by joining a user ratings data set with a movie data set, read tab-separated files into pandas data frames, and upload them to two new tables via SQLAlchemy.

The lecture demonstrates using Python with an obscure alchemy package to create a database engine and upload data frames as Postgres tables, and verify by reading 10 rows into pandas.

Create a pairwise movie similarity table in SQL, compute Pearson correlation per movie pair with group by and having, and rank results to support item-based recommendations.

Explore cohort analysis in SQL using a credit risk example to track bad rates by cohort months and analyze patterns over time.

Explore cohort analysis with SQL to analyze credit card customer behavior by opening month, track overdue and bad-rate trends, and interpret when delinquency stabilizes.

Ingest data by uploading a dataset or csv, create and populate the target table, and validate results by exporting to a dataframe and listing the top 15 records.

Explain and execute a cohort-based SQL workflow: create and transform tables, apply window functions and rank by cohort, compute past-due sums, and generate a final cumulative delinquency ratio.

Upload 22 daily stock text files into 22 PostgreSQL tables, then union them into a master stock table to analyze price and volume trends using Python.

Connect Python with PostgreSQL and use a loop to upload multiple files into the database, creating a table per file name without extension and loading data with the copy statement.

Demonstrates a Python-driven workflow to generate a file list, upload tables to PostgreSQL, verify public schema entries, and union twenty-two tables into a final stock table.

Introduce the project to set up a stock information table in PostgreSQL, loading daily price and volume data with Python and building a wide, ticker-level view for trend analysis.

Connect Python to Postgres, load 22 files as tables, loop to inner join on ticker, drop and rename intermediates, producing a long stock table of daily prices.

Implement a store procedure to count max transactions in a period by evaluating sessions between a start and end time, using a method parameter to include one or both sides.

Develop a stored procedure to count sessions between two time points, using Python solutions to analyze peak hours in transaction and traffic data.

Create and execute a store procedure that builds a final result table from raw data using parameters, method 1 with union and a where clause, and method 2 with intersect.