HomeworkLib
Question

Database In the lab on April 25, we created a database with the following tables. Price (prodID, from, price) Product (prodID

a) Determine the order-ID and the total price of each order.(Note that the total price of an order is the price for the ordered products on the day of the order plus the price of the selected shipping.)

Note. The requested query is quite complicated(and long). The main problem is that you have to determine the price of the ordered products at the day of the order. Recall that the price table stores the price of a product starting at a particular date(from atribute). Hence, the price of a product on day d is the price in the entry with the largest from-value f such that f ≤ d.

engineering   Computer-Science   
0 0
Add a comment Improve this question Transcribed image text
Next > < Previous
Sort answers by oldest

Homework Answers

Answer #1

If you have any doubts, please give me comment...

SELECT [Order].orderID, SUM(amount)+price

FROM [Order], PO, Shipping

WHERE [Order].orderID = PO.orderID AND [Order].shipID = Shipping.shipID

GROUP BY [Order].orderID;

0 0
Add a comment
Know the answer?
Add Answer to:
a) Determine the order-ID and the total price of each order.(Note that the total price of...
Your Answer:

Post as a guest

Your Name:

What's your source?

Earn Coins

Coins can be redeemed for fabulous gifts.

Log In

Sign Up

Not the answer you're looking for? Ask your own homework help question. Our experts will answer your question WITHIN MINUTES for Free.
Similar Homework Help Questions

  • NEED THE SQL QUERIES ASAP PLEASE(LIKE 1 HOUR) THE ONES WITH ID ARE PRIMARY KEYS OR...

    NEED THE SQL QUERIES ASAP PLEASE(LIKE 1 HOUR) THE ONES WITH ID ARE PRIMARY KEYS OR FOREIGN etc Customer [ CustID, LastName, FirstName, Address, City, State, Zip, Phone, Fax, Email] Product [ ProdID, Description, Color, Size, Pack] Sales [ TransID, CustID, ProdID, Price, Quantity, Amount] Write SQL statement to produce a list of unique products and their prices from the Sales table. Please ensure that the products do not repeat. Write an SQL statement to list ProdID and Description for...

  • Sales Database: Customer(custId, lastName, firstName, address, phone, creditLimit) Order(orderNumber, date, total, custId) LineItem(orderNumber, itemNumber, qtyOrdered) Item(itemNumber,...

    Sales Database: Customer(custId, lastName, firstName, address, phone, creditLimit) Order(orderNumber, date, total, custId) LineItem(orderNumber, itemNumber, qtyOrdered) Item(itemNumber, itemName, price) Give the SQL required to create the LineItem table in the Sales Database with the required keys and appropriate data types for the attributes.

  • You will develop an E-Commerce database used to maintain customers, products and sales information. You are...

    You will develop an E-Commerce database used to maintain customers, products and sales information. You are required to 1) gather and analyze requirements 2) design logical structure of the database 3) create stored procedures to develop the tables and insert the data 4) write SQL statements for data extraction and reporting. Throughout the course of this semester you have analyzed the requirements for an eCommerce database, designed and developed your database. As a class we have gone through the process...

  • 7. Using the provided schema of a Purchase Order Administration database, write the following queries in...

    7. Using the provided schema of a Purchase Order Administration database, write the following queries in SQL. (In the schema, bold attributes are primary keys and italicized attributes are foreign keys.) SUPPLIER (SUPNR, SUPNAME, SUPADDRESS, SUPCITY, SUPSTATUS) SUPPLIES (SUPNR, PRODNR, PURCHASE_PRICE, DELIV_PERIOD) PRODUCT (PRODNR, PRODNAME, PRODTYPE, AVAILABLE_QUANTITY) PO_LINE (PONR, PRODNR, QUANTITY) PURCHASE_ORDER (PONR, PODATE, SUPNR) 7d) Write a nested SQL query to retrieve the supplier number, supplier name, and supplier status of each supplier who has a higher supplier status...

  • Can you look and see where I could improve on this ERD and ensure that the...

    Can you look and see where I could improve on this ERD and ensure that the ERD is correct? For this task, you are to normalize the ERD that has been developed to remove any partial dependencies or transitive dependencies that may exist. Two things about customers to consider when modifying your ERD are that customers may have more than one shipping address and multiple credit cards they can use to pay for their order. Also, there are several shipping...

  • There are 7 problems that require using joins. Each problem has 10 points. 1. Write an...

    There are 7 problems that require using joins. Each problem has 10 points. 1. Write an SQL command that will find any customers who have not placed orders and sort them out by CustomerID in ascending order. 2. List the employees and supervisors names for each supervisor who supervises more than two employees. 3. List the name of each employee, his or her birth date, the name of his or her manager, and the manager’s birth date for those employees...

  • Describe each questions: Explain why the object-oriented database model was developed. How does ...

    Describe each questions: Explain why the object-oriented database model was developed. How does the OID in the OO model differ from the primary key in the relational model? Is it possible for a superkey not to be a candidate key? Why or why not? Briefly describe the four types of binary relationships possible between an entity set A and an entity set B based on relationship cardinality. Sales Database: Customer(custId, lastName, firstName, address, phone, creditLimit) Order(orderNumber, date, total, custID) LineItem(orderNumber,...

  • Describe each questions: Explain why the object-oriented database model was developed. How does the OID in...

    Describe each questions: Explain why the object-oriented database model was developed. How does the OID in the OO model differ from the primary key in the relational model? Is it possible for a superkey not to be a candidate key? Why or why not? Briefly describe the four types of binary relationships possible between an entity set A and an entity set B based on relationship cardinality. Sales Database: Customer(custId, lastName, firstName, address, phone, creditLimit) Order(orderNumber, date, total, custID) LineItem(orderNumber, itemNumber,...

  • CUSTTYPE (TID, TypeDesc) CUSTOMER (CID, Lname, Fname, TID, Address, ZIPcode) CUSTORDER (OrderNo, OrderDate, PromiseDate, ShippedDate, ShippingTerm,...

    CUSTTYPE (TID, TypeDesc) CUSTOMER (CID, Lname, Fname, TID, Address, ZIPcode) CUSTORDER (OrderNo, OrderDate, PromiseDate, ShippedDate, ShippingTerm, SalesID, CID) SALESREP (SalesID, Name, Phone, Address, ZIPcode, RepType) ORDERLINE (OrderNo, PID, Qty, Qty_RETD) FTSALESREP (SalesID, MonthPay, Rank) PTSALESREP (SalesID, HourlyRate, WeekHours) ZIP_TABLE (ZIPcode, City, State) VENDOR (VID, Name, Phone ZIPcode) PRODUCT (PID, CateID, ProdName, ProdFinish, Price, Qty_on_Hand, Description, VID) CATEGORY (CateID, CateType) Local View #1: Use three base tables to develop a SQL statement that will list the following information for a customer,...

  • Chapter 2 How to use the Management Studio Before you start the exercises... Before you start...

    Chapter 2 How to use the Management Studio Before you start the exercises... Before you start these exercises, you need to install SQL Server and the SQL Server Management Studio. The procedures for doing both of these tasks are provided in appendix A of the book. In addition, you'll need to get the Exercise Starts directory from your instructor. This directory contains some script files that you need to do these exercises. Exercises In these exercises, you'll use SQL Server...

ADVERTISEMENT
Free Homework Help App
Download From Google Play
Scan Your Homework
to Get Instant Free Answers
Need Online Homework Help?
Ask a Question
Get Answers For Free
Most questions answered within 3 hours.
ADVERTISEMENT
ADVERTISEMENT