SQL Bootcamp

Foundations of SQL & Databases

SQL Fundamental Concepts

• What is SQL & why is it used?
• Flavors of SQL: Postgres vs SQL Server, etc.
• Database Tables, 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.
• 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 & Finding NULLs

• Left Join
• Right Join
• Full Join
• Find NULL values

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

Advanced Query Techniques

Subqueries

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

Aggregate Window Functions

• Using OVER and PARTITION BY to apply Aggregate Functions

Conditionals with CASE

• Adding If-Else Logic with CASE
• Simple CASE vs Searched CASE

String Functions

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

Self Join

• How a Self Join is different from other types of joins
• Using a Self Join

Views & User-Defined Functions

Views

• What are Views?
• The Advantages & Disadvantages of using Views
• How to Use & Query a View

Materialized Views

• The difference between regular Views and Materialized Views

User-Defined Functions

• Using Scalar-Value (Single-Value) Functions
• Using Table-Value Functions