Clinical Professor, School of Information
Intermediate PostgreSQL
What You'll Learn
- Utilize SQL commands for editing tables in a PostgreSQL database and produce properly normalized tables from CSV files.
- Appropriately handle text and dates in databases and create stored procedures.
- Identify hashtag algorithm and their attributes.
- Construct regular expressions to select rows that match a pattern.
4 Modules
16 Hours
4 hrs per module (approx.)
Rating
About Intermediate PostgreSQL
This course covers a wide range of SQL techniques, beyond basic CRUD (Create, Read, Update, and Delete) operations in PostgreSQL. You will learn the specifics of aggregation, transactions, reading and parsing CSV files and inserting data into a database. You’ll also take a look at how PostgreSQL handles and indexes text data.
Specifically, students will do assignments that alter table schemas, create stored procedures, construct advanced queries, explore sorting and grouping query data, and techniques for working with text in databases including regular expressions.
Skills You'll Gain
- Database Design
- PL/pgSQL
- PostgreSQL
What You'll Earn
- Certificate of Completion:
- Certificates of completion acknowledge knowledge acquired upon completion of a non-credit course or program.
Welcome Message
Intermediate PostgreSQL, the second course in the PostgreSQL for Everybody Specialization, builds advanced SQL skills beyond basic database operations. Learners work with aggregation, transactions, stored procedures, indexing, and text processing while completing hands-on assignments using PostgreSQL to manage and query complex datasets. Assignments include those that alter table schemas, create stored procedures, construct advanced queries, explore sorting and grouping query data, and techniques for working with text in databases, including regular expressions.
This abbreviated syllabus description was created with the help of AI tools and reviewed by staff. The full syllabus is available to those who enroll in the course.
Course Schedule
Module 1: SQL Techniques
- Video: Welcome to Intermediate SQL!
- Reading: Help Us Learn More About You
- Reading: Syllabus
- Video: Altering Table Schema
- Video: Dates
- Video: DISTINCT / GROUP BY
- Video: Demonstration: SELECT DISTINCT
- Video: Demonstration: GROUP BY
- Video: Subqueries
- Video: Demonstration: Subqueries
- Video: Concurrency and Transactions
- Video: Demonstration: Concurrency and Transactions
- Video: Stored Procedures
- Video: Demonstration: Stored Procedures
- Reading: Each Course Uses a Different Database
- Reading: How Assignments Work
- Reading: Connecting to Your Database Server
- App Item: Initial Database Setup
- Reading: Assessment Philosophy
- Video: Office Hours Hyderabad, India - PyCon
- Video: Office Hours: Puebla, Mexico
- Graded: Making our First Tables
- Graded: Intermediate SQL
- Graded: Alter Table
- Graded: SELECT DISTINCT
- Graded: Creating a Stored Procedure
Module 2: Using SQL Techniques
- Video: Demo Reading and Parsing Files
- Video: Demonstration: Creating and Loading a Database
- Video: Demonstration: Loading and Normalizing CSV Data
- Video: Office Hours - Gingerman Raceway, South Haven, Michigan, USA
- Video: Office Hours - Haag, Netherlands
- Graded: Musical Tracks Many-to-One
- Graded: Unesco Heritage Sites Many-to-One
- Graded: Musical Track Database plus Artists
Module 3: Text in PostgreSQL
- Video: Text in Databases
- Video: Text Function
- Video: Character Sets
- Video: Character Sets in Python
- Video: Inside Hashes
- Reading: Bruce Schneier - Cryptography, Hashing, and NIST
- Video: Bruce Schneier - Cryptography, Hashing, and NIST
- Video: Index Choices and Index Techniques
- Video: Demonstration: Generating and Scanning Text
- Video: Office Hours - New York City
- Video: Office Hours, Miami, Florida, USA
- Graded: Text and PostgreSQL
- Graded: A Hash-based Puzzle
- Graded: Generating Text
Module 4: Regular Expressions
- Video: Regular Expressions
- Video: Using Regular Expressions
- Video: Demonstration: Regular Expressions
- Video: Demonstration: Flat files, Regex, Email
- Video: Course Wrap Up
- Reading: Post-Course Survey
- Video: Office Hours, Mountain View, California, USA
- Graded: Regular Expression Queries
- Graded: Regular Expressions
Grading Policy
Learners must complete all graded assignments. Three quizzes are worth 9% (3% each) of your grade and 10 graded assignments are worth 9.1% each.
Intermediate Level
Completion of first two courses in the Python for Everybody specialization or an equivalent basic understanding of Python
