PostgreSQL

Sillabus

Foundations of SQL & Databases
SQL Fundamental Concepts
• What is SQL & why is it used?;
• Flavors of SQL: Postgres Rows&Columns;
• Using ER (Entity Relationship) Diagrams to visual what’s in a database;

Exploring Databases & Writing SQL Statements (using the free DBeaver app)
• Connecting to a Database Database Navigator;
• SQL Query Editor Using Code Hints;
• Viewing the Results of your SQL query Setting Preferences;

Writing SQL Queries
Writing SELECT Statements;
• Syntax of a SELECT statement;
• Selecting all columns or specific columns from a table Limiting the number of results using LIMIT;
• Ordering the results using ORDER BY;
• Returning only DISTINCT records (eliminating duplicates);

Filtering Results
• Data Types (Strings vs Numbers)
• Comparison Operators: equal to, greater or less than, not equal to, etc.                                                                                                                                1
• Filtering results using WHERE, AND, OR, IN, and NOT Pattern Matching: Wildcard Filters
• Case Sensitivity

Using Joins to Combine Data from Multiple Tables
Understanding Table Relationships
• What are Primary vs Primary Keys;
• Database Relations: One-to-One, One-to-Many, & Many-to-Many;

Inner Joins
• The difference between Inner & Outer Joins Inner Joins;
• Column & Table Aliases;

Outer Joins
• Left Join Right Join Full Join;

Manipulating, Aggregating, & Filtering Data
Using CAST to Change Data Types
• Why and how to use CAST to make a data type fit your query’s needs;

Aggregate Functions
• Using Aggregate Functions to perform common statistical calculations Using SUM, COUNT, AVG, MAX & MIN;

Working with Dates&Time
• Date Functions: Getting the desired part of a date/time (Year, Month, Day, etc.) Formatting dates, including the day of the week (Sunday, Monday, etc.) Calculating the difference between 2 dates

Grouping Data&Filtering Grouped Data
Grouping Data with Aggregate Functions
• Using GROUP BY to organize results into categories Applying Aggregate Functions to groups;

Filtering Grouped Data with HAVING
• Using HAVING to filter the results of a GROUP BY HAVING vs WHERE;

Other Types of Joins: Self Join & Union
Self Join
• How a Self Join is different from other types of joins Using a Self Join;

Union
• How UNION is different from other types of joins Combining result sets with UNION

Advanced Query Techniques
Conditionals
• Adding If-Else Logic with CASE Simple CASE vs Searched CASE;

String Functions
• Manipulating text using string functions;
• Using SUBSTRING, SPLIT_PART, UPPER, etc.

Subqueries
• Subqueries: Queries within queries Single-Value vs Multi-Value Subqueries

Views (Regular & Materialized)
Views
• What are Views?
• The Advantages & Disadvantages of using Views Creating & Deleting a View
• Querying a View

Materialized Views
• The difference between regular Views and Materialized Views

Variables & User-Defined Functions
User-Defined Functions
• Creating User-Defined Functions;
• Using Variables in User-Defined Functions;