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

Learn SQLs & Python for SQLs by systematically solving more than 300 practical Questions

Created byShenggang Li

Last updated 6/2019

English

English [Auto],

What you'll learn

All fundamental SQLs for everyone
Complete Intermediate SQLs for job seekers
Advanced SQLs for experienced learners
More than 300 practical questions related to SQLs and Python
How to utilize and manage PostgreSQL and Pgadmin interfaces to write SQLs
Efficient Database administration
How to solve real world & capstone projects about SQLs and Python
Store procedures, triggers, regular expressions and statistical analysis with PostgreSQL & Python
Business analysis methods and backgrounds such as cohort analysis, credit risk analysis, retail recommendation system, fraud detection and stock trend analysis
How to work with Python, SQLs and external files together
Comprehensive job interview guidance on SQLs and Python

Course content

5 sections • 241 lectures • 15h 31m total length

Introduction: what are you going to learn?14:58
I will introduce the major contents in the course
Download resource for this course0:57
Introduce tools for the course2:14
introduce the software you will use in this course
Installation of PostgreSQL7:10
Installation of Anaconda (Python)5:30
Introduce programming tool -- Spyder2:41
Start and connect PostgreSQL4:26
Introduce PgAdmin interface and usages5:27
Change password under PgAdmin2:22
Ecosystem and components of PostgreSQL8:15

Write SQL under PgAdmin4:45
Create first table in PostgreSQL6:34
Explain solution SQL2:14
Create second table in PostgreSQL6:50
Create table by uploading CSV files7:02
Explain solution SQL2:45
Solve two similar questions (1)2:50
Explain solution SQL1:11
Solve two similar questions (2)1:44
Explain solution SQL1:20
Use Python and PostgreSQL for data analysis2:22
Introduce SQLAlchemy3:11
Python programming using SQLAlchemy0:54
Create table using Python and SQLAlchemy5:43
Execute solution code4:05
Validate solution1:21
Browse data using select statements2:31
Explain solution SQL0:52
Browse PostgreSQL table using Pandas data frames3:03
Create a series of tables for course usages5:57
Import CSV files into PostgresSQL using PgAdmin4:57
Execuate solution SQL1:20
Browse tables -- columns with quotes1:20
Explain solution SQL2:03
Browse table with DISTINCT and LIMIT options3:16
Create resulting table under PostgreSQL2:41
Questions for WHERE statements4:57
Introduce alias in PostgreSQL5:29
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 solution SQL2:20
Introduce Psycopg2 and SQL in Python6:14
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 GROUP BY in SQL5:54
Using column expression and aggregation functions in SQL3:42
More question examples about column expression and aggregation functions in SQL2:02
Introduce ORDER BY options in SQL5:39
Introduce HAVING statement in SQL5:25
Explain and execuate solution SQL3:00
More question examples on HAVING6:15
Explain and execute solution SQL3:51
Introduce String functions in PostgreSQL2:32
Solve String functions question in PostgreSQL (1)2:33
Solve String functions question in PostgreSQL (2)1:57
Solve String functions question in PostgreSQL (3)2:41
Explain and execute solution SQL4:01
Solve String functions question in PostgreSQL (4)2:19
Summarize String functions in PostgreSQL1:12
Fuzzy query: like, %2:43
Solving a fuzzy query question1:26
More fuzzy query practices6:20
SQL for data sampling2:16
Explain and execute solution SQL2:58
Inner Join tables in SQL (1)7:48
Explain and execute solution SQL3:39
Outer Join tables in SQL (2)3:57
Equivalent inner Join tables in SQL (3)4:08
Explain and execute solution SQL2:47
Another inner Join example (4)3:23
Explain and execute solution SQL2:35
Special (self) join in SQL (1)2:03
Explain and execute solution SQL3:11
special (self) join in SQL (2)3:15
Explain and execute solution SQL3:03
Special (cross) join in SQL (3)3:56
Special (cross) join in SQL (4)2:47
Special (natural) join in SQL (5)3:39
Data conversion functions in SQL (1)4:15
Data conversion functions in SQL (2)2:40
Explain and execute solution SQL4:11
Data conversion functions in SQL (3)5:59
Explain and execute solution SQL1:17
Data conversion functions in SQL (4)6:50
Calculate date and time intervals in SQL7:06
Extract time in SQL4:45
Explain and execute solution SQL4:34
Make time and time interval variables in SQL7:43
Missing values and queries in SQL3:16
project 1: Past due analysis8:20
Explain and execute solution SQL5:26

Conditional expression - case...when (1)4:58
Conditional expression - case...when (2)4:59
Conditional expression - case...when (3)3:35
Explain and execute solution SQL3:26
Conditional expression - case...when (4)3:55
Sub-Query in SQL (1)6:39
Sub-Query in SQL (2)3:18
Explain and execute solution SQL2:56
Sub-Query in SQL (3)4:15
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.
Sub-Query in SQL (4)4:40
Sub-Query in SQL (5)5:34
Sub-Query in SQL (6)4:32
Stack tables in SQL (1)4:13
Stack tables in SQL (2)3:56
Stack tables in SQL (3)7:14
Stack tables in SQL (4)3:41
Stack tables in SQL (5)5:07
Table constraints -- serial5:57
Table constraints -- unique not null3:27
Verify unique not null constrains2:31
Primary key and check constraints5:57
Verify Primary key and check constraints1:36
Group primary key constraints2:55
Foreign key constraints4:38
More table constraints examples6:28
Update tables (1)5:44
Update tables (2)1:18
Update tables (3)2:51
Add columns to a table4:57
Drop columns from a table2:47
Rename columns of a table2:38
Rename a table2:14
Learn to rename a table in SQL using ALTER TABLE, with IF EXISTS, and rename from an existing table to a new table name.
Delete data from a table (1)3:54
Delete data from a table (2)4:16
Roll up option in SQL (1)3:11
Roll up option in SQL (2)5:37
Array columns and query in SQL (1)6:04
Array columns and query in SQL (2)8:32
Add columns and functions in SQL6:10
View in PostgreSQL (1)4:22
View in PostgreSQL (2)5:01
View in PostgreSQL (3)3:34
View in PostgreSQL (4)3:12
Project: year over year analysis10:05

Apply window functions in SQL (1)8:42
Explain and execute solution SQL5:36
Apply window functions in SQL (2)4:50
Apply window functions in SQL (3)7:08
Introduce stored procedure under PostgreSQL (1)4:10
Create stored procedure under PgAdmin5:52
Save stored procedure under PgAdmin3:52
Write stored procedure under PostgreSQL (1)10:50
Write stored procedure under PostgreSQL (2)3:49
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 stored procedure under PostgreSQL (3)7:32
Write stored procedure under PostgreSQL (4)5:54
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.
Write stored procedure under PostgreSQL (5)7:35
Write stored procedure under PostgreSQL (6)10:48
Introduce triggers under PostgreSQL11:18
Create triggers in PostgreSQL (1)7:21
Create triggers in PostgreSQL (2)6:41
Introduce Regular expression in SQL (1)5:35
Introduce Regular expression in SQL (2)6:28
Introduce Regular expression in SQL (3)10:29
Extract text using regular expression with SQL (1)2:33
Extract text using regular expression with SQL (2)5:30
Extract text using regular expression with SQL (3)6:27
Replace text string using regular expression with SQL2:21
Split text string using regular expression with SQL3:43
Get Sub-String using regular expression with SQL4:53
Introduce statistical functions in PostgreSQL1:46
Preparing data for statistical analysis with SQL4:02
Apply statistical functions in PostgreSQL (1)5:55
Apply statistical functions in PostgreSQL (2)9:20
Introduce administration in PostgreSQL (1)2:10
Introduce administration in PostgreSQL (2)2:19
Introduce administration in PostgreSQL (3)2:15
Introduce administration in PostgreSQL (4)0:51
Show version of PostgreSQL0:31
Explain and execute solution SQL0:20
Understand ownership of databases1:56
Solution SQL code1:12
Explain and execute solution SQL (1)4:13
Explain and execute solution SQL (2)2:07
Explain and execute solution SQL (3)0:45
Administration of table's schema4:03
Explain and execute solution SQL (1)2:29
Explain and execute solution SQL (2)0:41
Create new schema using SQL under Pgadmin (1)1:17
Create new schema using SQL under PgAdmin (2)0:40
Create new schema using SQL under PgAdmin (3)1:00
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.
Understand users under PostgreSQL1:29
Explain and execute solution SQL0:29
Create a user under PgAdmin1:18
Create a user and assign privilege using SQL2:18
Solution SQL code1:52
Explain and execute solution SQL1:26
Set users' password1:56
Understand roles in databases1:26
Browse roles under PostgreSQL1:07
Explain and execute solution SQL (1)1:17
Explain and execute solution SQL (2)1:11
Explain and execute solution SQL (3)1:50
Understand role group and questions (1)1:41
Understand role group and questions (2)5:10
Explain and execute solution SQL (1)1:25
Explain and execute solution SQL (2)0:49
Revoke roles under PostgreSQL (1)2:10
Revoke roles under PostgreSQL (2)4:23
Explain and execute solution SQL0:57
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.
Understand tablespaces under PostgreSQL2:24
Check tablespaces with SQL1:46
Create and drop tablespeces in PostgreSQL1:22
Explain create tablespace statements2:27
Create database under PostgreSQL0:59
Explain create database statements2:59
Create databases under PgAdmin2:54
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.
Alter databases with SQL5:05
Backup and restore databases0:39
Show cases (1)2:27
Show cases (2)1:54
Show cases (3)2:11
Drop database1:32
Introduce ownership of tables and change ownership1:54
Explain and execute solution SQL1:06
Describe a table under PostgreSQL2:25
Explain and execute solution SQL2:38

Introduction3:32
Introduce project: fraud detection with SQL (1)2:37
Introduce project: fraud detection with SQL (2)2:42
Introduce project: fraud detection with SQL (3)3:22
Solution SQL code2:27
Explain and execute solution SQL (1)4:58
Explain and execute solution SQL (2)13:04
Introduce project: recommandor system with SQL6:02
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 data4:31
Explain and execute solution SQL (1)4:17
Explain and execute solution SQL (2)6:46
Introduce project: cohort analysis with SQL (1)5:29
Introduce project: cohort analysis with SQL (2)4:41
Introduce project: cohort analysis with SQL (3)2:59
Ingest data and validation2:06
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 solution SQL5:55
Introduce project: setup stock database with SQL (1)2:07
Introduce project: setup stock database with SQL (2)2:25
Explain and execute solution SQL (1)3:33
Explain and execute solution SQL (2)2:48
Explain and execute solution SQL (3)1:37
Introduce project: setup stock database with SQL (3)4:39
Explain and execute solution SQL5:42
Project: Counting Max Number of Transactions in a Specific Period (1)4:01
Project: Counting Max Number of Transactions in a Specific Period (2)1:13
Project: Counting Max Number of Transactions in a Specific Period (3)1:54
Explain and execute solution SQL (1)1:54
Explain and execute solution SQL (2)4:59

Requirements

Able to use computer including being able to install software
Prior exposure to programming languages will be helpful

Description

In this more than 16 hours course, we would teach the complete PostgreSQL Database, SQL, and Python for starting the career in data science! Please note, this is a really “for everyone’s” course.

You must take this course, not only because SQL and Python are so far the most useful tools for data analysis in the world, but also because you are learning a real hands-on as well as theoretical course about how to apply SQL and Python into practice! After you have solved and digested all those 300 practical questions, you will definitely become a real SQL & Python expert!

This is so far the most comprehensive guide to learn PostgreSQL database, PgAdmin management, all levels SQL commands and Python programming for SQL and database connections. One of most important features of this course is that it is a question proposition and solution based – we are trying to take a hands-on approach to tackle each practical problem while providing you a systematic explanation on theories.

This is also a business and data analytics jobs based course, as we have designed several real world and job interview projects for different levels’ students. We are not only proposing these questions and projects but also providing you with complete solution codes in SQL and Python

Upon completing this course, you’ll be able to apply SQL and Python to solve various data science, machine learning, statistical analysis and business problems under different environments and interfaces.

Want to be successful? Then join this course! You’ll learn by doing and meet various challenges!

Who this course is for:

Anyone who desires to learn SQL, PostgreSQL, Pgadmin, Anaconda, Spyder, Python and Data Science
Anyone who wishes to learn basic knowledge and skills statistics
Anyone who needs to get started data analysis or science career using Database, SQLs and Python
Anyone who wants to learn how to apply SQLs and Python into various business and industry areas
Anyone who needs to expend and improve knowledge and skill set in Python, SQLs and PostgreSQL database administration

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

What you'll learn

Explore related topics

Course content

Introduction10 lectures • 54min

Solve basic SQL & Python questions77 lectures • 4hr 47min

Intermediate SQL and questions44 lectures • 3hr 17min

Advanced SQL and questions82 lectures • 4hr 41min

SQL projects for job interviews28 lectures • 1hr 52min

Requirements

Description

Who this course is for: