Complex joins

1. Complex joins

What do you do when the data you need to answer a question is scattered across many tables in your database? How do you find it and put it together?

2. A complex question

Let's take this example question: you are interested in finding out how many videos were rented in each city. What data will you need to answer this question?

3. Connecting the data

A good start would be the rental table. This contains information about rentals but nothing about what city they were rented in.

4. Connecting the data

This means you need to find another table that contains city information.

5. Connecting the data

The address table appears to be a good source. However, there is no overlap in columns between these two tables and hence there isn't a direct way to connect them.

6. Connecting the data

so there is likely a need to use an intermediate table to unite them.

7. Entity Relationship Diagram (ERD)

The table that does this is the customer table. As you can see it has the customer_id column from the rental table and the address_id column from the address table. Joining these three tables together will give you the opportunity you need to determine how many videos were rented in each city. By following this process of identifying the correct tables you need for your query and visually drawing connections between them we just created what is known as an entity relationship diagram or ERD for short. The ERD can be an invaluable tool for making sense of the tables in your database. In some cases this diagram may be available to you, in others you would have to build one for the question at hand.

8. Tools for finding your data

With the tools you learned in this chapter you can do exactly that. You can explore your database to find the data elements that are necessary to answer the questions you have.

9. Your turn!

Now it's your turn to use these tools to build an entity relationship diagram and answer a question requiring multiple joins.

Create Your Free Account

or

By continuing, you accept our Terms of Use, our Privacy Policy and that your data is stored in the USA.

This exercise is part of the course

Applying SQL to Real-World Problems

IntermediateSkill Level

4.9+

Start Course for Free

You will review some of the most commonly used SQL commands to ensure you are prepared to tackle both real-world problems as well as every exercise covered in this course.

Exercise 1: Essential SQL Exercise 2: Review the essentials Exercise 3: Practice the essentials Exercise 4: Transforming your results Exercise 5: Transform numeric & strings Exercise 6: Extract what you need Exercise 7: Working with aggregate functions Exercise 8: Aggregating finances Exercise 9: Aggregating strings Exercise 10: Which tables?

How do you find the data you need in your database in order to answer real-world business questions? Here you will learn how to use system tables to explore your database. You will use these tables to create a new tool that contains a list of all tables and columns in your database. Finally, you will create an Entity Relationship Diagram (ERD) which will help you connect multiple tables.

Exercise 1: Find the right table Exercise 2: LIMITing your search Exercise 3: Which table to use?Exercise 4: What tables are in your database?Exercise 5: Determine the monthly income Exercise 6: Join the correct tables Exercise 7: What columns are in your database?Exercise 8: A VIEW of all your columns Exercise 9: Testing out your new VIEW Exercise 10: The average length of films by category Exercise 11: Complex joins

Current Exercise

Exercise 12: Build the entity relationship diagram Exercise 13: Which films are most frequently rented?

Working with SQL to solve real-world problems will oftentimes require you to do more than retrieve the data you need, oftentimes you will need to manage the data in your database. This includes creating data, updating it and, when necessary, deleting it.

Exercise 1: Store your data Exercise 2: Storing new data Exercise 3: Using existing data Exercise 4: TABLE vs VIEW Exercise 5: Update your data Exercise 6: What should you modify?Exercise 7: Update the price of rentals Exercise 8: Updated based on other tables Exercise 9: Delete your data Exercise 10: Deleting all table data Exercise 11: Delete selected records Exercise 12: A family friendly video store

How do you ensure that the SQL scripts you write will be easy to understand for anyone who needs to read them? This chapter will cover approaches you can leverage to ensure that your code clearly conveys your intent, is readable by others and follows best practices.

Exercise 1: Convey your intent Exercise 2: How to convey our intent Exercise 3: Clarify the intent of this query Exercise 4: Fix this query - intent Exercise 5: Write readable code Exercise 6: How to make code easier to read Exercise 7: Make this query easier to read - Part I Exercise 8: Make this query easier to read - Part II Exercise 9: Avoid common mistakes Exercise 10: What are the don'ts of writing SQL code?Exercise 11: Apply best practices to your code Exercise 12: Recap