Exercises, Questions, and Projects

1. Write out the result of joining the orders, customer, and catalog tables. This is the result of:

       SELECT * FROM orders as o JOIN customer as c ON o.cust_id =  
       c.cust_id JOIN catalog as p ON o.prod_id = p.prod_id

2. Produce a select statement that will order the products by price, listing the product name and description.

3. Produce a select statement that will list the most expensive (by price) two products.

4. Produce a select statement that will list the products by name, cost, price, and percentage markup. Order this list by the percentage markup.

5. Produce a select statement that will list the customer name, total the prices of all purchases, and be in order of this total.

6. For the airline flights example, produce the select statement that lists all the flights departing from a particular city within a certain time range.

7. Go to the Web to find out how Access or MySQL or other specific DBMS support date and time calculations. In particular, you what to know how to construct a date/time value, add such values, and check if one date/time value is greater than another.

8. For the airline flights example, produce the select statement that lists all two-leg flights taking off from a specified airport and arriving at a specified airport making one stop anywhere in between. You will need the arriving flight of the first leg to be before the departing flight of the second leg. More exactly, you want to constrain the time gap to be not less than a certain amount, say one hour, and not more than a certain amount, say three hours. Consult with a travel agent for typical values. Building on the airline flights example, how would you define a new table that grouped some airports into regions so you could ask questions about flights to and from regions containing one or more airports?

9. Consider the course and prerequisite database described in Chapter 5. You might want to add more courses, with some having one or two prerequisites. Produce the select statement that lists all courses that have prerequisites, listing the course name and course id of the course first, and then the course id of the prerequisite course.

10. Building on the previous question, produce the select statement that lists the name of the course and the name of the prerequisite. You will need to review the example with flights.

11. Produce the select statement that gives a list of courses with the number of prerequisites. You will need to use a left join and a summary function to get the courses to be included that have no prerequisites.

12. Go to the Web and find the list of summary/aggregate functions allowed by Access and by MySQL. Similarly, go online and find the functions provided for expressions.

.