Question

Using database db_pvfc12_big, write SQL queries to answer the following questions and execute them (finding the correct answers!!) Remember, for each question you must submit the question, your SQL query and the results of executing it, together. Be sure to number your answers. You may only use the information given in the questions to write your SQL queries. Some of these questions require joins; some require subqueries. Do not use subqueries IF joins can be used to answer a question.

****Answers should look like this*****
SELECT EmpNum
FROM EMPLOYEE
WHERE State=’Tennessee’
AND Salary =
(SELECT MAX(Salary)
FROM EMPLOYEE
WHERE State=’Tennessee’);
Join Example*****
SELECT CUSTOMER.CustomerNum
FROM CUSTOMER, ORDER
WHERE CUSTOMER.CustomerNum=ORDER.CustomerNum
AND OrderDate BETWEEN 1/1/2020 AND 12/31/2020
AND State=’Tennessee’
AND Cost=
(SELECT MAX(Cost)
FROM CUSTOMER, ORDER
WHERE CUSTOMER.CustomerNum=ORDER.CustomerNum
AND OrderDate BETWEEN 1/1/2020 AND 12/31/2020
AND State=’Tennessee’);
Tables and Fields in Database db_pvfc12_big
(Note: The use of fields in Teradata SQL is not case sensitive.)
CUSTOMER_T
Customerid
Customername
Customeraddress
Customercity
Customerstate
Customerpostalcode
PRODUCT_T
Productid
Productdescription
Productfinish
Productstandardprice
Productonhand
Productlineid
RAWMATERIAL_T
Materialid
Materialname
Thickness
Width
Size
Material
Materialstandardprice
Unitofmeasure
Materialtype
ORDER_T
Orderid
Orderdate
Customerid
Fulfillmentdate
Salespersonid
Shipadrsid
ORDERLINE_T
Orderlineid
Orderid
Productid
Orderedquantity
USES_T
Productid
Materialid
Quantityrequired
SUPPLIES_T
Vendorid
Materialid
Supplyunitprice

1. What is the name and address of the customer that placed order 57?
2. Assume there is only one product with the description Cherry End Table. List the names of the raw materials that go into making that product.
3. List the product id, description, and finish of the least expensive products. (Note: A couple of rows show a price of 0. Exclude products with a price of 0 from your query.)
4. How many orders did each customer from New York state (i.e. NY) place? List the answers in order from the most to the least orders.

Answer 1

The student's SQL queries were crafted based on the schema of the database db_pvfc12_big, using joins and conditions where appropriate to address specific questions about customer details, raw materials for a product, the least expensive products, and the number of orders placed by customers in New York state.

Step-by-step explanation:

To answer the student's database query questions, one would need to write and execute SQL queries using the provided information from the tables in database db_pvfc12_big. Following the instructions provided, here are the queries tailored for each specific question:

1. What is the name and address of the customer that placed order 57?
2. List the names of the raw materials that go into making the product with the description 'Cherry End Table'.
3. List the product id, description, and finish of the least expensive products, excluding any that have a price of 0.
4. How many orders did each customer from New York state (NY) place, listed from most to least orders?

While I am unable to execute these queries without access to the actual database, I have provided the SQL statements based on the information available.