Write the SQL to build the PRODUCT and PURCHASE_ORDER tables, as shown in the picture. Use your best guess on the data types for each column. Write queries to get the records from the PRODUCT table meeting for each of the following: Give the purchase cost and description of all products that are available. List the purchase cost and quantity of all products with product codes HW, FW, and BK . List all details for any products that are speakers. (Hint: look to descriptions.) List all products and how many are on hand, giving the products of which there are the most at the top of the output. List all products and how many are on hand, giving the products of which there are the most at the top of the output. This time, display the descriptions in all capital letters. List all details of all available products costing between $20 and $100. List all products along with their manufacturer IDs and how much all of the products of that type on hand are worth. Sort them such that the cheapest products are at the top of the list and most expensive are at the bottom. Suppose someone is interested in knowing product costs in whole dollars, but since amounts like $10.95 really require spending $11, always wants to round up. Show a listing of manufacturer IDs and the rounded costs of all products. List only product IDs for products whose product code is BK. Now consider the PURCHASE_ORDER table: List for each order the order number, customer ID, and average shipping cost per item. Determine the order number of all purchase orders whose product IDs were in the result set of the query you wrote in #8 above.

New Perspectives on HTML5, CSS3, and JavaScript

6th Edition

ISBN:9781305503922

Author:Patrick M. Carey

Publisher:Patrick M. Carey

Chapter2: Getting Started With Css: Designing A Website For A Fitness Club

Section2.3: Visual Overview: Pseudo Elements And Classes

Problem 7QC

See similar textbooks

Related questions

Concept explainers

Question

1. Write the SQL to build the PRODUCT and PURCHASE_ORDER tables, as shown in the picture. Use your best guess on the data types for each column.

Write queries to get the records from the PRODUCT table meeting for each of the following:

Give the purchase cost and description of all products that are available.

List the purchase cost and quantity of all products with product codes HW, FW, and BK .
List all details for any products that are speakers. (Hint: look to descriptions.)
List all products and how many are on hand, giving the products of which there are the most at the top of the output.
List all products and how many are on hand, giving the products of which there are the most at the top of the output. This time, display the descriptions in all capital letters.
List all details of all available products costing between $20 and $100.
List all products along with their manufacturer IDs and how much all of the products of that type on hand are worth. Sort them such that the cheapest products are at the top of the list and most expensive are at the bottom.
Suppose someone is interested in knowing product costs in whole dollars, but since amounts like $10.95 really require spending $11, always wants to round up. Show a listing of manufacturer IDs and the rounded costs of all products.
List only product IDs for products whose product code is BK.

Now consider the PURCHASE_ORDER table:

List for each order the order number, customer ID, and average shipping cost per item.
Determine the order number of all purchase orders whose product IDs were in the result set of the query you wrote in #8 above.

jdbc:derby://localhost: 1527/SampleDB [app on APP]
APP
Tables
CUSTOMER
DISCOUNT_CODE
MANAGERINFO
MANUFACTURER
MICRO_MARKET
PRODUCT
-PRODUCT_ID
MANUFACTURER_ID
PRODUCT_CODE
PURCHASE COST
QUANTITY_ON_HAND
MARKUP
AVAILABLE
DESCRIPTION
Indexes
Foreign Keys
PRODUCT_CODE
PURCHASE ORDER
ORDER_NUM
CUSTOMER_ID
-PRODUCT_ID
QUANTITY
SHIPPING COST
SALES_DATE
SHIPPING DATE
FREIGHT COMPANY
Indexes
Foreign Keys
Views
Procedures
Other schemas

Expert Solution

Step by step

Solved in 3 steps

SEE SOLUTION Check out a sample Q&A here

Knowledge Booster

Learn more about

Need a deep-dive on the concept behind this application? Look no further. Learn more about this topic, computer-science and related others by exploring similar questions and additional content below.