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.
Homework Answers
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;
Add Answer to:
a) Determine the order-ID and the total price of each
order.(Note that the total price of...
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,...
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...
Consider the following relations. Product: (Product#, p-description, p-indate, p-price) Vendor: (V-code, v-name, v-address) Purchase: (Product#, V-code,...
Consider the following relations. Product: (Product#, p-description, p-indate, p-price) Vendor: (V-code, v-name, v-address) Purchase: (Product#, V-code, date) Write an SQL query to list the prices of the products that were purchased on 9 December 2019
use workbench please Task 1: Create a database and name it practical2DB. (2 marks) Task 2:...
use workbench please
Task 1: Create a database and name it practical2DB. (2
marks)
Task 2: Create all the four tables according to the relational
scheme in Figure 1. (8 marks)
Task 3: Insert 5 records to into each table. (5 marks)
Task 4: Write a query to list alphabetically ordered names,
addresses, and IDs of all the customers whose postcode is in
(40150, 40400, 47500).
(10 marks)
Task 5: Delete 1 record from Products table using their primary
keys....
Given the following relational schema, write queries in SQL to answer the English questions. The Access...
Given the following relational schema, write queries in SQL to answer the English questions. The Access Database for the schema is available, as is a DDL file. It is also available on the MySQL server. You must only submit the SQL for your answers. You can get your answers without using a DBMS or by using Access or MySQL. Customer(cid: integer, cname: string, address: string, city: string, state: string) Product(pid: integer, pname: string, price: currency, inventory: integer) Shipment(sid: integer, cid:...
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...
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...
use workbench please
Task 1: Create a database and name it practical2DB. (2
marks)
Task 2: Create all the four tables according to the relational
scheme in Figure 1. (8 marks)
Task 3: Insert 5 records to into each table. (5 marks)
Task 4: Write a query to list alphabetically ordered names,
addresses, and IDs of all the customers whose postcode is in
(40150, 40400, 47500).
(10 marks)
Task 5: Delete 1 record from Products table using their primary
keys....
Most questions answered within 3 hours.
-
(63
#14)
which of the following statments best describes how chamging
the concentration of the substances...
asked 3 hours ago -
In the following reaction, which element is undergoing
oxidation: Na2SO3 + N2O --> N2 + Na2SO4...
asked 3 hours ago -
Which of the following pairs of ions have the same electron
configuration?
I: Br− and Se2−...
asked 6 hours ago -
The Foremost Composite Materials Company is planning a two-day
sales conference for October 19-20. The conference...
asked 6 hours ago -
3) Illustrate the observed pattern of relatedness of organisms
versus adaptations to specific conditions. This means...
asked 7 hours ago -
In winter a lake has a 0.35 m thick ice layer over 1.10 m of
water....
asked 8 hours ago -
Assuming the following has been encrypted with a Vigenere cipher
below, use the method(s) and assumptions...
asked 8 hours ago -
How would I use switch statements to write a program that will
take an input of...
asked 8 hours ago -
Imagine a reaction in which methane gas combusts at a constant
pressure of 1 atm and...
asked 8 hours ago -
Two parallel wires (each 12 m in length) are separated by a
distance of 0.065 m...
asked 8 hours ago -
Suppose there were three masses at the corner of uniform
equilateral triangle. The masses are m1...
asked 8 hours ago -
Situation: A building that is 618 m above the ground floor. How
many times would a...
asked 8 hours ago