Join Queries  «Prev  Next»

Using Query in the FROM clause - Exercise

Course Project: Converting queries to different formats

Objective: Write a query three ways:
  1. using the query in the FROM clause,
  2. using a join, and
  3. using a subquery with the EXISTS clause.

Exercise Scoring

This exercise is worth 30 points. Each of the three queries is worth 10 points. You will be scored on the use of the required techniques in each of the queries. Once you have completed your answer you will submit your answer.
  • Background and Overview: You will be working with the course project, a pet store called House-O-Pets. As you use SQL, you will notice that there are a variety of ways to write a query and get the same results. This exercise lets you work creatively with queries to accomplish a goal using the techniques that you have practiced in the last two lessons.
  • Download Files Download the PETSTORE schema files FROM the Resources page if you plan to install them into your own database.
  • Instructions: You must create three queries that use three different techniques to reach the same end result.
    All three queries answer this question: What is the first and last name of each of my customers who live in California and who have made at least one purchase?

Here is your starting query: 
SELECT FIRSTNAME, LASTNAME, STATEFROM CUSTOMER C
WHERE CUST_ID IN (SELECT CUST_IDFROM CUSTOMER_SALE CS)
AND C.STATE = 'CA'

The result FROM executing the query is: 
FIRSTNAME            LASTNAME                  ST
-------------------- ------------------------- --
Lester               Lee                       CA

Query 1: EXISTS
Take the starting query and rewrite it so that it uses the EXISTS clause instead of the IN clause. You should get the same results when you execute the query.
Query 2: JOIN
Take the starting query and change it so that it does not contain a subquery, and uses a JOIN of the CUSTOMER and the CUSTOMER_SALE table.
You will need to add the Distinct function at the beginning of the SELECT clause to avoid getting duplicate rows returned in the result.
Query 3: Sub-query in FROM clause
Take the starting query and change it so that it uses a subquery in the FROM clause. You may need to add the Distinct function at the beginning of the SELECT clause to avoid getting duplicate rows returned in the result.

Submitting your Exercise

Enter your queries into the text box below. Click the Submit button to submit your answers.Remember that you must submit all your responses to this exercise at once.