In this exercise, you restrict the query results to the customers who are currently renting videos and to the videos that are currently being rented. You also limit the query results to a specific customer whose name you specify when you run the query. And lastly, you organize the query results by the day of the week that the rented videos are due. Within each day of the week, you order the videos by the video title, and for each video title, by customer name.
A join operation lets you retrieve data from two or more tables based on matching column values. The data in the tables is linked into a single result. Two joins are needed for this query. The query result needs to include the RENTALS and CUSTOMERS table entries that have matching CUST_ID column values. The query result must also include the RENTALS and VIDEOS table entries that have matching video IDs (VID_ID column values).
To join tables:
FROM XMLTEST.VIDEOS AS V JOIN XMLTEST.RENTALS AS R JOIN XMLTEST.CUSTOMERS AS C ON R.CUST_ID = C.CUST_ID ON V.VID_ID = R.VID_ID
In the Tables pane, both joins have been created as shown here:
You can change the join type (for example, from the default inner join to a left, right, or full outer join) in the Tables pane by right-clicking the connector, clicking Specify Join Type on the pop-up menu, and then selecting the join type that you want in the Specify Join window.
Next, the query needs a query condition so that the query extracts only result rows that have the customer name that you want. You add conditions to the query by using the Conditions page in the Design pane.
To create a query condition:
You will group the query by the day of the week, then by title, and then by customer name.
To add a GROUP BY clause: