Intermediate SQL: Data Reporting and Analysis

Take your data skills to the next level with SQL by learning how to write code for more complex queries to report, edit, insert, and delete data from your database.

Introduction

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

1. Prepare to Code in SQL

• Get familiar with phpMyAdmin
• Understand the demo database

2. Reporting Data with SQL

• The basic SELECT query
• Aggregated results with GROUP BY
• Wildcards and aliases
• Merging tables with JOIN
• Use string functions
• Use number functions with grouped data
• Use date functions
• Challenge: Report average cost by staff
• Solution: Report average cost by staff

3. Editing Data

• UPDATE fields in a table
• Common issues with the UPDATE command
• Automating your query creation
• Updating a field using JOIN
• Updating dynamically
• Challenge: Update the film table using JOIN
• Solution: Update the film table using JOIN

4. Inserting Data

• INSERT rows into a table
• Common issues with the INSERT command
• Using MERGE instead of UPDATE/INSERT
• Duplicate table with SELECT
• Challenge: Insert a row into the film table
• Solution: Insert a row into the film table

5. Deleting Data

• DELETE rows from a table
• DROP an entire table
• ALTER TABLE for modifying fields
• Problems with foreign key constraints
• Challenge: Delete from the country table
• Solution: Delete from the country table

Conclusion

• Next steps