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 visualize 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 Foreign 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