Intermediate SQL: Data Reporting and Analysis

2h 12mIntermediate2022-11-14

Authors

Emma Saunders

Data Consultant specializing in data visualization on the web

Course details

If you’re already familiar with the fundamentals of SQL covered in our introductory course on SQL: Data Reporting and Analysis, you may be interested in taking your skills to the next level and learning a few more new tricks of the trade. Join data consultant Emma Saunders in this hands-on, practical follow-up, designed specifically to help you grow beyond the basics so you can start writing more complex queries to change data in the database instead of just reporting it.

Expand your SQL toolbox, including how to query data in real time for reporting, editing, inserting, and deleting. Learn new date, number, and string functions to manipulate data as you retrieve it. Along the way, test out your technical know-how with the coding challenges and quizzes at the end of each section.

Skills covered

SQLDatabase AdministrationDatabase DevelopmentDatabase ManagementData AnalysisProgramming LanguagesData ScienceBusiness Analysis and StrategyBusiness Software and ToolsOpen SourceSoftware DevelopmentOne-Off

Concepts

0. Introduction

01 - Learn hands-on to extract, manipulate, edit, and delete real data
02 - Who this course is for
03 - What can you do with intermediate SQL

1. Prepare to Code in SQL

04 - Get familiar with phpMyAdmin
05 - Understand the demo database

2. Reporting Data with SQL

06 - The basic SELECT query
07 - Aggregated results with GROUP BY
08 - Wildcards and aliases
09 - Merging tables with JOIN
10 - Use string functions
11 - Use number functions with grouped data
12 - Use date functions
13 - Challenge - Report average cost by staff
14 - Solution - Report average cost by staff

3. Editing Data

15 - UPDATE fields in a table
16 - Common issues with the UPDATE command
17 - Automating your query creation
18 - Updating a field using JOIN
19 - Updating dynamically
20 - Challenge - Update the film table using JOIN
21 - Solution - Update the film table using JOIN

4. Inserting Data

22 - INSERT rows into a table
23 - Common issues with the INSERT command
24 - Using MERGE instead of UPDATE INSERT
25 - Duplicate table with SELECT
26 - Challenge - Insert a row into the film table
27 - Solution - Insert a row into the film table

5. Deleting Data

28 - DELETE rows from a table
29 - DROP an entire table
30 - ALTER TABLE for modifying fields
31 - Problems with foreign key constraints
32 - Challenge - Delete from the country table
33 - Solution - Delete from the country table

Conclusion

34 - Next steps