13 MySQL Exercises With Premiere Products Database

Use SQL and the Premiere Products database (see Figure 1-2 in Chapter 1) to complete the following exercises. If directed to do so by your instructor, use the information provided with the Chapter 3 Exercises to print your output.

1). For each order, list the order number and order date along with the number and name of the customer that placed the order.

2). For each order placed on October 23, 2010, list the order number along with the number and name of the customer that placed the order.

3). For each order, list the order number, order date, part number, number of units ordered, and quoted price for each order line that makes up the order.

4). Use the IN operator to find the number and name of each customer that placed an order on October 23, 2010.

5). Repeat Exercise 4, but this time use the EXISTS operator in your answer.

6). Find the number and name of each customer that did not place an order on October 23, 2010.

7). For each order, list the order number, order date, part number, part description, and item class for each part that makes up the order.

8). Repeat Exercise 7, but this time order the rows by item class and then by order number.

9). Use a sub-query to find the rep number, last name, and first name of each sales rep who rep-resents at least one customer with a credit limit of $10,000. List each sales rep only once in the results.

10). Repeat Exercise 9, but this time do not use a sub-query.

11). Find the number and name of each customer that currently has an order on file for a Gas Range.

12). List the part number, part description, and item class for each pair of parts that are in the same item class. (For example, one such pair would be part AT94 and part FD21, because the item class for both parts is HW.)

13). List the order number and order date for each order placed by the customer named Johnson’s Department Store. (Hint:To enter an apostrophe (single quotation mark) within a string of characters, type two single quotation marks.)

This material may consist of step-by-step explanations on how to solve a problem or examples of proper writing, including the use of citations, references, bibliographies, and formatting. This material is made available for the sole purpose of studying and learning - misuse is strictly forbidden.

Problem 1

mysql> SELECT ORDER_NUM, ORDER_DATE, ORDERS.CUSTOMER_NUM, CUSTOMER_NAME FROM ORDERS, CUSTOMER WHERE ORDERS.CUSTOMER_NUM = CUSTOMER.CUSTOMER_NUM;...