Query Optimization

Accessibility Notice

This document is also available in HTML format at:

https://aholdengouveia.name/AdvData/labs/week5optimization.html

The HTML version provides enhanced accessibility features including keyboard navigation, screen reader support, responsive design, dark mode support, and high contrast options.

Objectives:

• Learn how to work with indexes
• Demonstrate how to add an index to a table to make it more efficient

Complete the following problems

References, a video, a PowerPoint and some notes are available at my website https://www.aholdengouveia.name/AdvData/optimization.html

Query optimization practice on Kaggle

• Go through the Advanced SQL course. Take a screenshot of the completed course in your profile. Make sure to include your name and the term in your screenshot. This should show you completed the tutorials
• Now you'll be doing the exercises. Go through the exercise on Query Optimization, it should be problems related to Pet Costumes International. Take a screenshot of your solution to both problems. Make sure to include your name and term in the screenshot.

Query Optimization Challenges

For each query make sure to include a short (1 paragraph or less) explanation of how you optimized it and solved the problem. Each problem should also include your solution typed out, and a screenshot of your solution being run. Make sure all screenshots include your name and the term.

Go to https://www.sandboxsql.com/ and load in the database located here https://github.com/aholdengouveia/aholdengouveia.github.io/blob/main/AdvData/labs/BooksDatabase.sqlite for the following problems.

Note: The queries don't always copy over well, especially symbols like the single quote.

SELECT authors.name, books.genre
            FROM books INNER JOIN authors 
            ON books.author_id = authors.author_id
            WHERE books.title = 'Dune'

Deliverables

• Document for the portion of this lab using Kaggle to practice query optimization and review your SQL.
• Document for the challenges. Each challenge should include the solution typed out, an explanation of how you arrived at your solution including any resources used, and a screenshot of the solution being run, each screenshot must include your name and the term.