SQL Essential Training
4h 27mBeginner2023-09-14
Authors
Walter Shields
Tech Educator and Best-Selling Author
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.
Skills covered
SQLDatabase AdministrationDatabase DevelopmentDatabase ManagementData AnalysisEssential TrainingProgramming LanguagesData ScienceBusiness Analysis and StrategyBusiness Software and ToolsOpen SourceSoftware Development
Concepts
Introduction
- The data-driven world
- What you should know
The Basics of Database Structures
- Relational database theory
- The SQLite database engine
The SQL Stack
- WSDA Music (sample database)
- The DB Browser software
- How to install DB Browser
- Learning tips
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
Composing Queries
- Queries
- Query commenting
- Query composition
- Query composition best practices
- Column custom names
- Sorting query results
- Limiting query results
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
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
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
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
Nesting Queries
- Subqueries and aggregate functions
- SELECT clause subquery
- Aggregated subqueries
- Non-aggregate subqueries
- IN clause subquery
- DISTINCT clause subquery
Stored Queries
- View introduction
- Creating a view
- Editing a view
- Joining views
- Deleting views
Adding, Modifying, and Deleting Data
- Analysis and administration
- Inserting data
- Updating data
- Deleting data
Scenario - Mock SQL Interview
- Preparing for a Non-Technical Interview
- Non-Technical Interview Practice
Challenge - SQL Practice Code Test
- CoderPad Tour
- Solution - Playlist recommendation
- Solution - Smart Customer Engagement Strategies
- Solution - AI-Powered Support Workload Analysis
Conclusion
- Next steps and tips