SQL Essential Training

Course details

The ubiquity of big data means that now, more than ever, there is a burning need to warehouse, access, and understand the contents of massive databases quickly and efficiently. Attaining proficiency in SQL is essential to prepare for our data-driven present and future. It is also crucial to furthering your skills as a data analyst or application developer. In this course, Walter Shields teaches you: the basic structure of databases—what they are, how they work, and how to successfully navigate them; how to use SQL to retrieve and understand data no matter the scale of a database; and how to master the most important SQL query syntax, along with how and when to use it best.

Concepts

0. Introduction

01 - The data-driven world
02 - What you should know

1. The Basics of Database Structures

03 - Relational database theory
04 - The SQLite database engine

2. The SQL Stack

05 - WSDA Music (sample database)
06 - The DB Browser software
07 - How to install DB Browser
08 - Learning tips

3. The SQLite Database Environment

09 - How to access the DB Browser for SQLite software
10 - Load the sample database file
11 - Getting familiar with a database
12 - The browse data area
13 - The query writing area

4. Composing Queries

14 - Queries
15 - Query commenting
16 - Query composition
17 - Query composition best practices
18 - Column custom names
19 - Sorting query results
20 - Limiting query results

5. Discovering Insights in Data

21 - Types of SQL operators
22 - Filter and analyze numeric data
23 - BETWEEN and IN operators
24 - Filter and analyze text data
25 - Search records without an exact match
26 - Filter and analyze using dates
27 - Filter records based on more than one condition
28 - Logical operator OR
29 - Brackets and order
30 - IF THEN logic with CASE

6. Accessing Data from Multiple Tables

31 - Joins explained
32 - How tables share a relationship, part 1
33 - How tables share a relationship, part 2
34 - Simplifying JOINs
35 - Types of JOINs
36 - The INNER JOIN
37 - The LEFT JOIN
38 - The RIGHT JOIN
39 - Tables and Entity Relationship diagrams
40 - Joining many tables

7. SQL Functions

41 - Calculating with functions
42 - String, date, and aggregate function types
43 - Connecting strings
44 - Separating text
45 - UPPER and LOWER string functions
46 - Date functions
47 - Aggregate functions
48 - Nesting functions

8. Grouping

49 - Grouping your query results
50 - Filtering with a grouped condition
51 - Grouping with the WHERE clause
52 - Grouping with the HAVING clause
53 - Grouping with the WHERE and HAVING clause
54 - Grouping by many fields

9. Nesting Queries

55 - Subqueries and aggregate functions
56 - SELECT clause subquery
57 - Aggregated subqueries
58 - Non-aggregate subqueries
59 - IN clause subquery
60 - DISTINCT clause subquery

10. Stored Queries

61 - View introduction
62 - Creating a view
63 - Editing a view
64 - Joining views
65 - Deleting views

11. Adding, Modifying, and Deleting Data

66 - Analysis and administration
67 - Inserting data
68 - Updating data
69 - Deleting data

12. Scenario - Mock SQL Interview

70 - Preparing for a Non-Technical Interview
71 - Non-Technical Interview Practice

13. Challenge - SQL Practice Code Test

72 - CoderPad Tour
73 - Solution - Playlist recommendation
74 - Solution - Smart Customer Engagement Strategies
75 - Solution - AI-Powered Support Workload Analysis

Conclusion

76 - Next steps and tips