Introduction to SQL Using PostgreSQL (SQL106)

This course provides a comprehensive introduction to SQL using PostgreSQL, one of the most advanced open-source relational database systems. Designed for beginners, data analysts, and anyone new to SQL, this course covers the essential SQL skills needed to query, manipulate, and manage data within a PostgreSQL environment.

The course begins with Relational Database Basics, where you'll explore the history of SQL, the fundamentals of relational databases, and the key features of PostgreSQL. This module sets the stage for understanding how SQL interacts with databases and introduces you to PostgreSQL, a powerful and widely used database system.

In Simple SELECTs, you'll learn the basics of querying data using the SELECT statement. This module covers selecting specific columns, sorting results, and using the WHERE clause to filter records based on various conditions. You’ll also practice running queries in pgAdmin, the popular management tool for PostgreSQL, allowing you to explore and interact with the database hands-on.

The Advanced SELECTs section dives deeper into SQL querying techniques. You'll learn to limit and offset results, create calculated fields, and use aggregate functions for grouping and summarizing data. This module also covers selecting distinct records, manipulating data with built-in functions for math and strings, and working with date and time data types, enhancing your ability to perform complex data retrieval and analysis.

In the Subqueries, Joins, and Unions module, you’ll explore powerful ways to combine and manipulate data across multiple tables. You’ll learn to write subqueries, use various join types, including outer joins, and merge datasets with unions. These skills are crucial for creating more sophisticated queries that can handle complex data relationships.

The Conditional Processing with CASE section teaches you how to use the CASE statement for conditional logic within your queries. This module allows you to add dynamic, decision-based processing to your SQL queries, making them more adaptable and capable of handling a wider range of data scenarios.

Next, in Inserting, Updating, and Deleting Records, you'll learn how to modify data in your tables. This module covers the INSERT, UPDATE, and DELETE statements, providing you with the skills to manage data efficiently and maintain data integrity within your database.

The course concludes with Creating Tables, where you’ll learn to create databases and tables using pgAdmin and SQL commands. You’ll explore table creation options, including how to use CASCADE and RESTRICT constraints to manage table dependencies. This module equips you with the foundational skills to design and implement your own database structures.

By the end of this course, you will have developed a strong understanding of SQL fundamentals using PostgreSQL. You’ll be equipped with the skills needed to write effective queries, manage data, and create databases, making you well-prepared to work with SQL in a PostgreSQL environment.

1. Relational Database Basics
   1. Brief History of SQL
   2. Relational Databases
   3. PostgreSQL
2. Simple SELECTs
   1. Introduction to the Jokes Database
   2. Some Basics
   3. SELECTing All Columns in All Rows
   4. Running Queries in pgAdmin
   5. Exercise: Exploring the Tables
   6. SELECTing Specific Columns
   7. Exercise: SELECTing Specific Columns
   8. Sorting Records
   9. Exercise: Sorting Results
   10. The WHERE Clause and Logical Operator Symbols
   11. Exercise: Using the WHERE Clause to Check for Equality or Inequality
   12. Checking for Greater or Less Than
   13. Exercise: Using WHERE to Check for Greater or Less Than
   14. Checking for NULL
   15. Exercise: Checking for NULL
   16. WHERE and ORDER BY
   17. Exercise: Using WHERE and ORDER BY Together
   18. Checking Multiple Conditions with Boolean Operators
   19. Exercise: Writing SELECTs with Multiple Conditions
   20. The WHERE Clause and Logical Operator Keywords
   21. Exercise: More SELECTs with WHERE
3. Advanced SELECTs
   1. LIMIT and OFFSET
   2. Calculated Fields
   3. Exercise: Calculating Fields
   4. Aggregate Functions and Grouping
   5. Exercise: Working with Aggregate Functions
   6. Selecting Distinct Records
   7. Built-in Data Manipulation Functions for Math and Strings
   8. Exercise: Data Manipulation Functions for Math and Strings
   9. Date/Time Types, Operators, and Functions
   10. Exercise: Date/Time Manipulation
4. Subqueries, Joins and Unions
   1. Subqueries
   2. Exercise: Subqueries
   3. Joins
   4. Exercise: Using Joins
   5. Outer Joins
   6. Exercise: Using Outer Joins
   7. Unions
   8. Exercise: Working with Unions
5. Conditional Processing with CASE
   1. Using CASE
   2. Exercise: Working with CASE
6. Inserting, Updating and Deleting Records
   1. INSERT
   2. Exercise: Inserting Records
   3. UPDATE
   4. DELETE
   5. Exercise: Updating and Deleting Records
7. Creating Tables
   1. Creating a Database with pgAdmin
   2. How to Create Tables
   3. Exercise: Creating Tables
   4. CASCADE and RESTRICT
   5. Exercise: Using CASCADE

Each student receives a comprehensive set of materials, including course notes and all class examples.