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

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 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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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 a table in SQL using ALTER TABLE, with IF EXISTS, and rename from an existing table to a new table name.

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.

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.

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 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.

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.

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.

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

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.

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.

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.

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.

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

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.

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 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.

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.

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.

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.

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.

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.

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.

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.

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