SQL Essential Training

Introduction

• The data-driven world
• What you should know

1. The Basics of Database Structures

• Relational database theory
• The SQLite database engine

2. The SQL Stack

• WSDA Music (sample database)
• The DB Browser software
• How to install DB Browser
• Learning tips

3. The SQLite Database Environment

• How to access the DB Browser for SQLite software
• Load the sample database file
• Getting familiar with a database
• The browse data area
• The query writing area

4. Composing Queries

• Queries
• Query commenting
• Query composition
• Query composition best practices
• Column custom names
• Sorting query results
• Limiting query results

5. Discovering Insights in Data

• Types of SQL operators
• Filter and analyze numeric data
• BETWEEN and IN operators
• Filter and analyze text data
• Search records without an exact match
• Filter and analyze using dates
• Filter records based on more than one condition
• Logical operator OR
• Brackets and order
• IF THEN logic with CASE

6. Accessing Data from Multiple Tables

• Joins explained
• How tables share a relationship, part 1
• How tables share a relationship, part 2
• Simplifying JOINs
• Types of JOINs
• The INNER JOIN
• The LEFT JOIN
• The RIGHT JOIN
• Tables and Entity Relationship diagrams
• Joining many tables

7. SQL Functions

• Calculating with functions
• String, date, and aggregate function types
• Connecting strings
• Separating text
• UPPER and LOWER string functions
• Date functions
• Aggregate functions
• Nesting functions

8. Grouping

• Grouping your query results
• Filtering with a grouped condition
• Grouping with the WHERE clause
• Grouping with the HAVING clause
• Grouping with the WHERE and HAVING clause
• Grouping by many fields

9. Nesting Queries

• Subqueries and aggregate functions
• SELECT clause subquery
• Aggregated subqueries
• Non-aggregate subqueries
• IN clause subquery
• DISTINCT clause subquery

10. Stored Queries

• View introduction
• Creating a view
• Editing a view
• Joining views
• Deleting views

11. Adding, Modifying, and Deleting Data

• Analysis and administration
• Inserting data
• Updating data
• Deleting data

12. Scenario: Mock SQL Interview

• Preparing for a Non-Technical Interview
• Non-Technical Interview Practice

Conclusion

• Next steps and tips