Introduction
  • Introduction
Installing PostgreSQL and pgAdmin
  • Installing PostgreSQL on Mac and Windows
  • Installing PostgreSQL on Ubuntu
  • Install Northwind Database
  • Install Some Additional Databases.
Simple Selection of All Records
  • Selecting All Data From a Table
  • Selecting Specific Fields
  • Selecting Distinct Values
  • Counting Results
  • Combining Fields in SELECT
  • Practice What You've Learned
Using WHERE to Select Records
  • What If You Don't Want All Records
  • Searching For Specific Text
  • Searching Numeric Fields
  • Searching Date Fields
  • WHERE Using Logical AND Operator
  • WHERE Using Logical OR Operator
  • WHERE Using Logical NOT Operator
  • WHERE Combining AND, OR, and NOT
  • Using BETWEEN
  • Using IN
  • Practice What You've Learned
Schemas
  • Schema Basics
Using psql To Connect To Postgres
  • Connecting With psql
  • Eliminate Typing Connection Parameters
  • Databases In psql
  • Schemas In psql
Intermediate SELECT Statements
  • ORDER BY
  • Using MIN and MAX Functions
  • Using AVG and SUM
  • LIKE to Match Patterns
  • Renaming Columns With Alias
  • LIMIT to Control Number of Records Returned
  • NULL Values
  • Practice What You've Learned
Joining multiple tables together
  • Diagramming Table Relationships
  • Grabbing Information From Two Tables
  • Grabbing Information From Multiple Tables
  • Left Joins
  • Right Joins
  • Full Joins
  • Self Joins
  • USING To Reduce Typing
  • Even Less Typing With NATURAL
  • Practice What You've Learned
Grouping and Aggregation Functions
  • Group By
  • Use HAVING to Filter Groups
  • Grouping Sets
  • Rollup
  • Cube - Rollup On Steroids
Combining Queries
  • Union
  • Intersect
  • Except
Subqueries
  • Subquery Using EXISTS
  • Subquery Using ANY and ALL
  • IN Using Subquery
Modifying Data In Tables: INSERT, UPDATE and DELETE
  • INSERT INTO
  • UPDATE
  • DELETE
  • SELECT INTO
  • INSERT INTO SELECT
  • Returning Data From Update, Delete, and Insert
Indexes and Performance Tuning
  • What Are Indexes?
  • CREATE INDEX
  • DROP INDEX
  • How To Kill Runaway Queries
  • Using Explain To See Query Plan
  • Use Analyze To Update Table Statistics
  • How Is Query Plan Cost Calculated
  • Using Indexes On More Than One Field
  • Expression Indexes
  • Types Of Indexes
  • Speeding Up Text Matching
Database Design and Normalization
  • Design Process Overview
  • Database Terminology
  • A Design Process
  • Finding Mission Statement And Ojectives
  • Analyzing Current Systems
  • Create Table Structure
  • Establishing Keys
  • Specifying Fields
  • Relationships Between Tables
  • Business Rules
  • Establish Needed Views
  • Double Checking Data Integrity
Creating and Modifying Tables