Introduction: what are you going to learn?
•Download resource for this course
•Introduce tools for the course
•Installation of PostgreSQL
•Installation of Anaconda (Python)
•Introduce programming tool — Spyder
•Start and connect PostgreSQL
•Introduce PgAdmin interface and usages
•Change password under PgAdmin
•Ecosystem and components of PostgreSQL
•Write SQL under PgAdmin
•Create first table in PostgreSQL
•Explain solution SQL
•Create second table in PostgreSQL
•Create table by uploading CSV files
•Solve two similar questions (1)
•Solve two similar questions (2)
•Use Python and PostgreSQL for data analysis
•Introduce SQLAlchemy
•Python programming using SQLAlchemy
•Create table using Python and SQLAlchemy
•Execute solution code
•Validate solution
•Browse data using select statements
•Browse PostgreSQL table using Pandas data frames
•Create a series of tables for course usages
•Import CSV files into PostgresSQL using PgAdmin
•Execuate solution SQL
•Browse tables — columns with quotes
•Browse table with DISTINCT and LIMIT options
•Create resulting table under PostgreSQL
•Questions for WHERE statements
•Introduce alias in PostgreSQL
•Explain and execute solution SQL
•Introduce Psycopg2 and SQL in Python
•Learn GROUP BY in SQL
•Using column expression and aggregation functions in SQL
•More question examples about column expression and aggregation functions in SQL
•Introduce ORDER BY options in SQL
•Introduce HAVING statement in SQL
•Explain and execuate solution SQL
•More question examples on HAVING
•Introduce String functions in PostgreSQL
•Solve String functions question in PostgreSQL (1)
•Solve String functions question in PostgreSQL (2)
•Solve String functions question in PostgreSQL (3)
•Solve String functions question in PostgreSQL (4)
•Summarize String functions in PostgreSQL
•Fuzzy query: like, %
•More fuzzy query practices
•SQL for data sampling
•Inner Join tables in SQL (1)
•Outer Join tables in SQL (2)
•Equivalent inner Join tables in SQL (3)
•Another inner Join example (4)
•Special (self) join in SQL (1)
•special (self) join in SQL (2)
•Special (cross) join in SQL (3)
•Special (cross) join in SQL (4)
•Special (natural) join in SQL (5)
•Data conversion functions in SQL (1)
•Data conversion functions in SQL (2)
•Data conversion functions in SQL (3)
•Data conversion functions in SQL (4)
•Calculate date and time intervals in SQL
•Extract time in SQL
•Make time and time interval variables in SQL
•Missing values and queries in SQL
•project 1: Past due analysis
•Conditional expression – case…when (1)
•Conditional expression – case…when (2)
•Conditional expression – case…when (3)
•Conditional expression – case…when (4)
•Sub-Query in SQL (1)
•Sub-Query in SQL (2)
•Sub-Query in SQL (3)
•Sub-Query in SQL (4)
•Sub-Query in SQL (5)
•Sub-Query in SQL (6)
•Stack tables in SQL (1)
•Stack tables in SQL (2)
•Stack tables in SQL (3)
•Stack tables in SQL (4)
•Stack tables in SQL (5)
•Table constraints — serial
•Table constraints — unique not null
•Verify unique not null constrains
•Primary key and check constraints
•Verify Primary key and check constraints
•Group primary key constraints
•Foreign key constraints
•More table constraints examples
•Update tables (1)
•Update tables (2)
•Update tables (3)
•Add columns to a table
•Drop columns from a table
•Rename columns of a table
•Rename a table
•Delete data from a table (1)
•Delete data from a table (2)
•Roll up option in SQL (1)
•Roll up option in SQL (2)
•Array columns and query in SQL (1)
•Array columns and query in SQL (2)
•Add columns and functions in SQL
•View in PostgreSQL (1)
•View in PostgreSQL (2)
•View in PostgreSQL (3)
•View in PostgreSQL (4)
•Project: year over year analysis
•Apply window functions in SQL (1)
•Apply window functions in SQL (2)
•Apply window functions in SQL (3)
•Introduce stored procedure under PostgreSQL (1)
•Create stored procedure under PgAdmin
•Save stored procedure under PgAdmin
•Write stored procedure under PostgreSQL (1)
•Write stored procedure under PostgreSQL (2)
•Write stored procedure under PostgreSQL (3)
•Write stored procedure under PostgreSQL (4)
•Write stored procedure under PostgreSQL (5)
•Write stored procedure under PostgreSQL (6)
•Introduce triggers under PostgreSQL
•Create triggers in PostgreSQL (1)
•Create triggers in PostgreSQL (2)
•Introduce Regular expression in SQL (1)
•Introduce Regular expression in SQL (2)
•Introduce Regular expression in SQL (3)
•Extract text using regular expression with SQL (1)
•Extract text using regular expression with SQL (2)
•Extract text using regular expression with SQL (3)
•Replace text string using regular expression with SQL
•Split text string using regular expression with SQL
•Get Sub-String using regular expression with SQL
•Introduce statistical functions in PostgreSQL
•Preparing data for statistical analysis with SQL
•Apply statistical functions in PostgreSQL (1)
•Apply statistical functions in PostgreSQL (2)
•Introduce administration in PostgreSQL (1)
•Introduce administration in PostgreSQL (2)
•Introduce administration in PostgreSQL (3)
•Introduce administration in PostgreSQL (4)
•Show version of PostgreSQL
•Understand ownership of databases
•Solution SQL code
•Explain and execute solution SQL (1)
•Explain and execute solution SQL (2)
•Explain and execute solution SQL (3)
•Administration of table’s schema
•Create new schema using SQL under Pgadmin (1)
•Create new schema using SQL under PgAdmin (2)
•Create new schema using SQL under PgAdmin (3)
•Understand users under PostgreSQL
•Create a user under PgAdmin
•Create a user and assign privilege using SQL
•Set users’ password
•Understand roles in databases
•Browse roles under PostgreSQL
•Understand role group and questions (1)
•Understand role group and questions (2)
•Revoke roles under PostgreSQL (1)
•Revoke roles under PostgreSQL (2)
•Understand tablespaces under PostgreSQL
•Check tablespaces with SQL
•Create and drop tablespeces in PostgreSQL
•Explain create tablespace statements
•Create database under PostgreSQL
•Explain create database statements
•Create databases under PgAdmin
•Alter databases with SQL
•Backup and restore databases
•Show cases (1)
•Show cases (2)
•Show cases (3)
•Drop database
•Introduce ownership of tables and change ownership
•Describe a table under PostgreSQL
•SQL projects for job interviews
•Introduce project: fraud detection with SQL (1)
•Introduce project: fraud detection with SQL (2)
•Introduce project: fraud detection with SQL (3)
•Introduce project: recommandor system with SQL
•Ingest data
•Introduce project: cohort analysis with SQL (1)
•Introduce project: cohort analysis with SQL (2)
•Introduce project: cohort analysis with SQL (3)
•Ingest data and validation
•Introduce project: setup stock database with SQL (1)
•Introduce project: setup stock database with SQL (2)
•Introduce project: setup stock database with SQL (3)
•Project: Counting Max Number of Transactions in a Specific Period (1)
•Project: Counting Max Number of Transactions in a Specific Period (2)
•Project: Counting Max Number of Transactions in a Specific Period (3)
•