Udemy Business

Teach on Udemy

Turn what you know into an opportunity and reach millions around the world.

Learn More

Your cart is empty.

Keep shopping

2022-Basics of SQL using PostgreSQL

Name: 2022-Basics of SQL using PostgreSQL
Rating: 4.3 (89 reviews)

A course that teaches SQL from the ground up

Created byMurthy Karra PhD

Last updated 1/2023

English

ArabicEnglish

What you'll learn

Structured Query Language (SQL)
PostgreSQL
Relational Database Management Systems
Build a three-tier web application
CRUD (Create, Read, Update & Delete) Operations
JOINs, GROUPs, PLPGSQL, FUNCTIONS, Python
VIEWS, TRIGGERS, TRIGGER Functions
INDEX, VACUUM
3-Tier Application

Course content

17 sections • 69 lectures • 7h 4m total length

Learning Objectives4:00
Explore the basics of SQL with PostgreSQL, covering select and filter, joins, creating and inserting with constraints, group by, subqueries, views, transactions, and indexing.
A Word about Udemy Reviews0:12
Introduction to Relational Databases and Management Systems5:29
Introduction to SQL2:29
Learn sql, the structured query language for querying relational databases using Postgres, an open source rdbms, and explore ddl, dml, dql, dcl, and tcl commands for data retrieval and modification.
Install PostgreSQL in Windows2:38
Install Postgres on Windows ten environment by downloading and running the 14.5 installer, keeping defaults, and noting the Postgres superuser password; then open PG Admin and import databases.
Install PostgreSQL in MAC2:01
PGAdmin and Sample Databases6:44
Introduction-Quiz

SELECT columns and ROWS FROM tables7:42
We learn how to select some or all columns from a single table. Learn about aliases for column names.
More SELECT using ALIASes2:38
We see some more uses of the SELECT keyword and use aliases to change column names using the AS keyword.
ORDER rows in ASCENDING or DESCENDING order5:19
Order query results with the order by clause in PostgreSQL using ascending or descending order, including multiple columns. Learn the execution order: from, select, order by, with asc optional.
Exercises with SELECT and ORDER BY3:42
Practice basic sql queries in PostgreSQL by selecting film titles and attributes, and sorting by rental rate and replacement cost in descending order, film length descending, and alphabetical title order.
Selecting unique entries using DISTINCT3:20
Learn to use the distinct clause to remove duplicates and retrieve unique countries and cities from the address table, including country-city combinations, with proper ordering.
Exercises with DISTINCT clause1:50
Use the distinct clause to count unique customers, identify distinct rental rates with minimum and maximum values, and reveal MPAA ratings from the DVD rental database.
Filtering rows using the WHERE clause7:10
Exercises using the WHERE clause6:35
Filter rows with the where clause using between, like, and ilike operators; use percent and underscore wildcards, with not and is null handling, and apply regex patterns.
Limiting the number of rows using the LIMIT clause1:58
The Basics-Quiz
Using the SELECT and ALIAS statements.
Assignment-1: Using the WHERE clause
Assignment-2: Using WHERE clause

INNER JOINS- Explanation5:29
Explain what an INNER JOIN is and why we need it and how it works.
INNER JOINS - Example5:07
A practical example of an INNER JOIN.
INNER JOINS - A more complex example6:34
We look at an example where we need to join more than two tables.
OUTER JOINS6:05
Explains what are outer joins, why we need them and examples.
SELF JOINS8:31
Explain what a self join is and two examples where we can use them.
JOINS - Quiz
Simple JOINs
Use JOINs to determine the names of all the actors in Club Graffiti in one SQL q
Use JOINs to determine the films that Emily Dee has acted in

Basic Datatypes in SQL5:12
In this video we look at some basic datatypes in SQL such as VARCHAR, CHAR, INT and REAL (and their variations).
Basic CRUD on databases, tables and rows10:02
Explore basic crud operations in PostgreSQL, creating databases and tables, inserting, updating, deleting, and reading data with examples using pgAdmin and sql syntax.
Manipulating columns in SQL3:00
Explore manipulating entire columns in Postgres by using a script to add, rename, cast, and drop columns in the people table, and understand double-dash comments and table recreation.
Constraints on columns: PRIMARY KEY and SERIAL6:45
Explore column and table constraints, focusing on the primary key as a unique, not-null identifier and how the serial data type auto-increments keys in PostgreSQL.
Constraints on columns: NOT NULL, DEFAULT, UNIQUE and CHECK4:29
Foreign Keys Constraints and their implications8:13
Explore foreign key constraints that link scores to students (and teachers), enforce referential integrity, prevent invalid insertions or deletions, and support multi-foreign-key relationships across related tables.
CRUD-Quiz
CRUD Operations

Useful built-in functions in Postgres5:41
The DATE Datatype7:07
Explore the date data type in Postgres, its storage as yyyy-mm-dd, and formatting with to_char, current_date, and now, plus age, extract, and interval for date calculations.
The TIME Datatype3:48
Discover the Postgres time data type, storing 24-hour times with optional time zones and fractional seconds, performing arithmetic and extraction, and comparing current time, local time, and zones.
TIMESTAMPs and TIMESTAMPs with TIMEZONE6:37
Explore the timestamp and timestamp with time zone data types, and learn how time zones affect values in Postgres, plus using interval for readable date arithmetic.

Basic grouping of data using the GROUP BY and HAVING clauses7:53
We group our rows into groups based on distinct values of a column and get data about the groups.
Aggregate calculation deep dive2:08
Since aggregate calculations are very important, we take a deeper look into when exactly these aggregates are calculated.
Complex grouping using multiple columns and extracted data9:03
We group our rows using more than one column and data that is not immediately available in the table as a simple column.
Combine many grouping operations into a single query7:24
We look at how we can combine multiple types of grouping operations into a single SQL query and how we read the result set.
Common SET operations to stack rows on top of each other.5:08
Here we look at ways to combine multiple tables with compatible datatypes using SET type operations: UNION, INTERSECT and EXCEPT.
Quiz - GROUPS

Use Sub-queries to construct complex queries.6:02
Here we look at ways that we can use a sub-query which is essentially feeding the result set of one query to another.
Use the CASE statement to take decisions7:27
We use the CASE statement along with WHEN, THEN to produce results based on the values of a column.
Common Table Expressions (CTE)8:49
We explore CTEs and apply them to two problems.
Virtual Tables using VIEWS6:41
We create virtual tables called VIEWS and show how we can use it for ease of use and some security feature.
Virtual Tables with MATERIALIZED VIEWS4:28
We discuss MATERIALIZED VIEWS which are nothing but VIEWS with actual data backing them. They can be used as a regular table and are useful for storing results are requested frequently.

Requirements

No programming experience required
Windows, Mac or Linux computer
Basic computer knowledge

Description

Structured Query Language (SQL) is THE language of relational databases.

Every software developer and data analytics engineer WILL interact with a relational database at some point in their career and SQL is the language of choice when talking to these databases.

Employers desire SQL language skills and it is a great resume enhancer if you know anything beyond just the basics.

In this course, you will learn the basics of SQL and some fairly advanced topics that will make you a valuable addition to any software team.

Most of the course (90%) will be spent on demos and problem solving rather than power point presentations and there are multiple quizzes, assignments and projects.

Finally, we will build a 3-tier web application using a PostgreSQL backend database which will teach you the advanced skills required to perform such a task.

If you have ever felt that you would like to learn more about SQL but are not sure about what course to take, Basics of SQL using PostgreSQL is an excellent course to try out.

There is no risk. This course has a 30-day money back guaranteed by Udemy.

I wish you the best of luck and hope you will have as much fun learning about SQL as I had teaching this course.

Who this course is for:

Beginner developers curious about SQL
Data Analytics engineers
Data Scientists
Intermediate software developers
Database administrators

2022-Basics of SQL using PostgreSQL

What you'll learn

Explore related topics

Course content

Introduction7 lectures • 24min

The Basics of SQL9 lectures • 40min

Joining multiple tables with JOINS5 lectures • 32min

Create, Read, Update and Delete Operations (CRUD)6 lectures • 38min

Built-in functions, Date, Time and Timestamps4 lectures • 23min

Combining rows into GROUPS5 lectures • 32min

Mini-Project - I0

Complex SQL queries5 lectures • 33min

Transactions in SQL2 lectures • 16min

SQL Programming using PLPGSQL2 lectures • 17min

Requirements

Description

Who this course is for: