Explore BrainMass
Share

Question about SQL Commands

This content was STOLEN from BrainMass.com - View the original, and get the already-completed solution here!

See attach file for charts and data.

Use SQL, and the Premiere Products database (see Figure 1.2) to complete the following exercises.

1. For each order placed on October 20, 2003, list the order number along with the number and name of the customer that placed the order.

2. Use the IN operator to find the number and name of each customer that placed an order on October 20, 2003.

3. Find the number and name of each customer that did not place an order on October 20, 2003.

4. For each order, list the order number, order date, part number, part description, and item class for each part that makes up the order but order the rows by item class and then by order number.

5. Find the rep number, last name, and first name of each sales rep who represents at least one customer with a credit limit of $10,000. List each sales rep only once in the results. Do not use a subquery.

6. List the part number, part description, and item class for each pair of parts that are in the same item class.

7. List the order number and order date for each order that contains an order line for a Gas Range.

8. List the order number and order date for each order that was placed by Ferguline for a Gas Range.

9. List the part number, part description, unit price, and item class for each part that has a unit price greater that the unit price of every part in item class AP. Use either the ALL or ANY operator in your query. (Hint: Make sure you select the correct operator.)

10. For each part, list the part number, description, units on hand, order number, and number of units ordered. All parts should be included in the results. For those parts that are currently not on order, the order number and number of units ordered should be left blank. Order the results by part number.

© BrainMass Inc. brainmass.com October 16, 2018, 4:24 pm ad1c9bdddf
https://brainmass.com/computer-science/sql/question-about-sql-commands-23718

Attachments

Solution Summary

The solution addresses about SQL command.

$2.19
Similar Posting

Writing SQL statements

I'm not grasping the full concept of these problems, I've completed most successfully. I've tried to get them, but I am have a hard time reworking these.

(This is the table referenced for the following questions)

INVENTORY (SKU, Description, QuantityOnHand, QuantityOnOrder, Warehouse)

1. Write a SQL statement to display the SKU and Description of all items stored in the Seattle, Chicago or New Jersey warhouse. Do not use IN.

2. Write a SQL statement to show SKU and Description for all products having a description starting with 'Half-Dome'.

3. Write a SQL expression to display the Warehouse and a count of QuantityOnHand, grouped by Warehouse. Name the count TotalItemsOnHand.

4. Write a SQL expression to display the Warehouse and a count of QuantityOnHand, grouped by Warehouse. Omit all items that have a count greater than 2. Name the count TotalItemsOnHand and display the reslults in descending order of TotalItemsOnHand.

The following table is for the next set of questions.

INVENTORY (SKU, Description, QuantityOnHand, QuantityOnOrder, Warehouse)

WAREHOUSE (Warehouse, Manager, SquareFeet)

5. Write a SQL statement to show the SKU ande Description of all items stored in a warehouse managed by 'Smith'. Use a subquery.

6. Write a SQL statement to show the Warehouse and average QuantityOnHand of all items stored in a warehouse managed by 'Smith'. Use subquery.

7. Write a SQL statement to show Warehouse, Manager, and QuantityOnHand of all items stored in a warehouse managed by 'Smith'. Use a join.

View Full Posting Details