Learn SQL Using PostgreSQL: From Zero to Hero
Master PostgreSQL starting with basics to advanced features like triggers, PL/pgSQL functions, Window Functions and CTE
- Description
- Curriculum
- FAQ
- Reviews
Learning SQL was one of the most valuable skills I learned while building Match. We had one of the largest instances of Microsoft SQL Server in the 90s with millions of records to keep the site running. The better we got at SQL, the better we could make the site work for our users and answers sophisticated questions about our users.
I want to teach you how to use PostgreSQL. We will walk through
-
Basic selection statements
-
Joining multiple tables together
-
Grouping records to get aggregate data
-
Inserting, updating and deleting records
-
Creating tables and indexes
-
Subqueries to create sophisticated reports
-
Table constraints to keep data clean
-
Sequences to create auto incrementing fields
-
CTE – common table expressions that include recursive queries
-
Views to simply accessing complex queries
-
Conditional Expressions for queries
-
Window functions to combine regular queries with aggregate data
-
How to work with date, time and intervals
-
Create SQL Functions to capture complex statements
-
Create PL/pgSQL Functions that allow programming with if/then and loops
-
Triggers
-
Array data types
-
Composite data types
-
Transactions and concurrency control
Installing PostgreSQL and pgAdmin
Simple Selection of All Records
-
2
Installing PostgreSQL on Mac and WindowsHow to install PostgreSQL using EnterpriseDB installer
-
3
Installing PostgreSQL on Ubuntu
Get up and running with PostgreSQL 11 and pgAdmin 4 on Ubuntu.
-
4
Install Northwind Database
How to install Northwind database using pgAdmin using the restore feature and northwind.tar file.
-
5
Install Some Additional Databases.Add 3 more databases to learn from.
Using WHERE to Select Records
-
6
Selecting All Data From a Table
How to select all information from a table using simple SELECT statement.
-
7
Selecting Specific Fields
How to return specific fields when running a SELECT statement.
-
8
Selecting Distinct Values
If you want to find all the unique values in a specific field in table, you use the DISTINCT keyword.
-
9
Counting Results
Using COUNT statement to return the number of records.
-
10
Combining Fields in SELECT
How to derive information using more than one field.
-
11
Practice What You've Learned
Use pagila database to practice your basic SELECT.
Schemas
-
12
What If You Don't Want All Records
Learn about the WHERE clause and how it is used to narrow down the number of records returned.
-
13
Searching For Specific Text
How to select records based on matching text fields.
-
14
Searching Numeric Fields
How to use WHERE with numeric fields with =, >, >=, <, and <=
-
15
Searching Date Fields
How to select records that have date fields.
-
16
WHERE Using Logical AND Operator
Using AND to select records where all conditions must be true.
-
17
WHERE Using Logical OR Operator
You can select records where any of the conditions are true using OR operator.
-
18
WHERE Using Logical NOT Operator
Reverse the meaning of operator using logical NOT operator.
-
19
WHERE Combining AND, OR, and NOT
Using parenthesis to create more complicated queries that combine logical operators.
-
20
Using BETWEEN
Using BETWEEN to find values >= and <=.
-
21
Using IN
If you have a long list of values the IN operator is easier to understand and read.
-
22
Practice What You've Learned
Use the usda resource to practice your WHERE clauses.
Using psql To Connect To Postgres
Intermediate SELECT Statements
-
24
Connecting With psql
Use psql command line to connect to your local database and run commands.
-
25
Eliminate Typing Connection Parameters
Remove inputing connection parameters for command line tools by using environment variables, a .pgpass file, or .pg_service.conf file.
-
26
Databases In psql
Learn how to what databases are present and connect to them in psql.
-
27
Schemas In psql
How to list the schemas and see the tables in a schema using psql.
Joining multiple tables together
-
28
ORDER BY
If you need the results to be returned in a specific order, use ORDER BY.
-
29
Using MIN and MAX Functions
Find the smallest record with MIN and largest with MAX.
-
30
Using AVG and SUM
Use these function to find the average value or the sum of all the values.
-
31
LIKE to Match Patterns
LIKE allows you to match text patterns for partial matches.
-
32
Renaming Columns With Alias
You can change the name of a column with AS syntax.
-
33
LIMIT to Control Number of Records Returned
If you only need a certain number of records use LIMIT to control.
-
34
NULL Values
Nulls are a special value to indicate an unknown. Learn how to use IS NULL and IS NOT NULL to select based on NULLs.
-
35
Practice What You've Learned
Practice using AdventureWorks database.
Grouping and Aggregation Functions
-
36
Diagramming Table Relationships
How to map out the tables and relationships in a database with diagrams.
-
37
Grabbing Information From Two Tables
How to pull information from 2 different tables in a single statement using JOIN.
-
38
Grabbing Information From Multiple Tables
Use multiple JOIN statements to pull together 3 or more tables.
-
39
Left Joins
Left joins allow you to pull all records from first table and any matching records from second table.
-
40
Right Joins
Right joins allow you to pull matching records from first table and all records from second table.
-
41
Full Joins
Full joins pull all records from both tables.
-
42
Self Joins
Connect a table back to itself.
-
43
USING To Reduce Typing
Reduce typing with USING instead of ON in joins.
-
44
Even Less Typing With NATURAL
NATURAL joins combine tables where fields are named the same in each table.
-
45
Practice What You've Learned
Practice joins using the AdventureWorks database.
Combining Queries
-
46
Group By
GROUP BY allows you to aggregate records and perform an aggregate function like AVG.
-
47
Use HAVING to Filter Groups
HAVING clause lets you filter out results of your GROUP BY results.
-
48
Grouping Sets
Use GROUPING SETS to group by multiple fields separately in a single query.
-
49
Rollup
Using ROLLUP as a shortcut for complex GROUPING SET
-
50
Cube - Rollup On Steroids
CUBE creates all combinations of fields while grouping.
Subqueries
Modifying Data In Tables: INSERT, UPDATE and DELETE
-
54
Subquery Using EXISTS
EXISTS subqueries allow you to check a condition in another table as part of the criteria to return a record.
-
55
Subquery Using ANY and ALL
Find records that return if any or all of the subquery match the condition.
-
56
IN Using Subquery
You can use a subquery with IN operator to dynamically build list.
Indexes and Performance Tuning
-
57
INSERT INTO
You will learn how to insert new data into a table.
-
58
UPDATE
Alter existing records using UPDATE statement.
-
59
DELETE
Remove data using the DELETE statement.
-
60
SELECT INTO
Create a new table based on records returned from select statement.
-
61
INSERT INTO SELECT
Insert records into an existing table by selecting from another table.
-
62
Returning Data From Update, Delete, and Insert
Using RETURNING to bring back data after INSERT, UPDATE, or DELETE.
Database Design and Normalization
-
63
What Are Indexes?
Learn what indexes are, what they help with and the drawbacks of too many indexes.
-
64
CREATE INDEX
Create indexes on tables that will result in faster searches.
-
65
DROP INDEX
Remove an existing index from a table.
-
66
How To Kill Runaway Queries
Find running queries using pg_stat_activity table and cancel them with pg_cancel_backend.
-
67
Using Explain To See Query Plan
We will create a large table and demonstrate how EXPLAIN works. Then see the difference when an index is added.
-
68
Use Analyze To Update Table Statistics
Learn to use EXPLAIN ANALYZE to see actual performance versus the prediction by the query analyzer. Use ANALYZE table_name to update the table statistics.
-
69
How Is Query Plan Cost Calculated
Learn how PostgreSQL uses calculates the query plan cost by estimated disk I/O and CPU usage for the query.
-
70
Using Indexes On More Than One Field
Learn how to properly use multi-column indexes.
-
71
Expression Indexes
Make indexes on modified columns using expression indexes.
-
72
Types Of Indexes
Learn about B-Tree, Hash, GIN, GiST, BRIN, and SP-GiST indexes and what situations are best for each type.
-
73
Speeding Up Text Matching
Use a GIN index with gin_trgm_ops to speed up text matching in the middle of text for LIKE '%some%' operations.
Creating and Modifying Tables
-
74
Design Process Overview
-
75
Database Terminology
-
76
A Design Process
Learn 7 steps to go through in order to design a database.
-
77
Finding Mission Statement And Ojectives
Conduct interviews with employees and management to find out main purpose and tasks the database should support.
-
78
Analyzing Current Systems
Learn how to analyze the current paper and database systems in a business and turn into tables and field lists.
-
79
Create Table Structure
Take the interviews and information collected in previous step and identify tables and fields that will go in new database.
-
80
Establishing Keys
Learn what makes a good primary key and how to find or create one for every table.
-
81
Specifying Fields
Document and specify all the fields in database, including descriptions, uniqueness, data type, and length.
-
82
Relationships Between Tables
Create a table matrix to map out the relationships between tables. Make an ER diagram to communicate to others what the relationships are.
-
83
Business Rules
Learn about different types of business rules and how to document them.
-
84
Establish Needed Views
Learn what views are used for and how to map out needed views.
-
85
Double Checking Data Integrity
The final step is reviewing data integrity and pulling documentation into a single source.
How long do I have access to the course materials?
You can view and review the lecture materials indefinitely, like an on-demand channel.
Can I take my courses with me wherever I go?
Definitely! If you have an internet connection, courses on Udemy are available on any device at any time. If you don't have an internet connection, some instructors also let their students download course lectures. That's up to the instructor though, so make sure you get on their good side!
Stars 5
802
Stars 4
638
Stars 3
191
Stars 2
40
Stars 1
18
Course details
Video
16 hours
Lectures
6
Certificate of Completion
Popular courses
Social Network