Problem 1
The table in worksheet "Problem 1" is a list of orders from a
company...
Problem 1
The table in worksheet "Problem 1" is a list of orders from a
company that sells sports products. Each row is an order. Only one
type of product is sold in each order, but the quantity and price
of each unit vary across orders. Information about each order
includes location, customer, segment, product sold, order date, the
list price per unit, the price each unit was actually sold in the
order (unit price), quantity of units sold, revenue...
CREATE TABLE Customers
(Customer# NUMBER(4),
LastName VARCHAR2(10) NOT NULL,
FirstName VARCHAR2(10) NOT NULL,
Address VARCHAR2(20),
City...
CREATE TABLE Customers
(Customer# NUMBER(4),
LastName VARCHAR2(10) NOT NULL,
FirstName VARCHAR2(10) NOT NULL,
Address VARCHAR2(20),
City VARCHAR2(12),
State VARCHAR2(2),
Zip VARCHAR2(5),
Referred NUMBER(4),
Region CHAR(2),
Email VARCHAR2(30),
CONSTRAINT customers_customer#_pk PRIMARY KEY(customer#),
CONSTRAINT customers_region_ck
CHECK (region IN ('N', 'NW', 'NE', 'S', 'SE', 'SW', 'W', 'E'))
);
CREATE TABLE Orders
(Order# NUMBER(4),
Customer# NUMBER(4),
OrderDate DATE NOT NULL,
ShipDate DATE,
ShipStreet VARCHAR2(18),
ShipCity VARCHAR2(15),
ShipState VARCHAR2(2),
ShipZip VARCHAR2(5),
ShipCost NUMBER(4,2),
CONSTRAINT orders_order#_pk PRIMARY KEY(order#),
CONSTRAINT orders_customer#_fk FOREIGN KEY (customer#)
REFERENCES customers(customer#));
In...
Rita placed an order for 300 shares of each of four separate
IPOs (Orders A, B,...
Rita placed an order for 300 shares of each of four separate
IPOs (Orders A, B, C, and D) with an offer price of $16 each. She
received 100 shares of Order B, 200 shares of Order D, and 300
shares of the other orders. At the end of the first day, Order A
was overpriced by $2 a share, Order B was underpriced by $4 a
share, Order C was correctly priced, and Order D was overpriced by
$1...
CREATE TABLE Orders
(Order# NUMBER(4),
Customer# NUMBER(4),
OrderDate DATE NOT NULL,
ShipDate DATE,
ShipStreet VARCHAR2(18),
ShipCity...
CREATE TABLE Orders
(Order# NUMBER(4),
Customer# NUMBER(4),
OrderDate DATE NOT NULL,
ShipDate DATE,
ShipStreet VARCHAR2(18),
ShipCity VARCHAR2(15),
ShipState VARCHAR2(2),
ShipZip VARCHAR2(5),
ShipCost NUMBER(4,2),
CONSTRAINT orders_order#_pk PRIMARY KEY(order#),
CONSTRAINT orders_customer#_fk FOREIGN KEY (customer#)
REFERENCES customers(customer#));
INSERT INTO ORDERS
VALUES
(1000,1005,TO_DATE('31-MAR-09','DD-MON-YY'),TO_DATE('02-APR-09','DD-MON-YY'),'1201
ORANGE AVE', 'SEATTLE', 'WA', '98114' , 2.00);
INSERT INTO ORDERS
VALUES
(1001,1010,TO_DATE('31-MAR-09','DD-MON-YY'),TO_DATE('01-APR-09','DD-MON-YY'),
'114 EAST SAVANNAH', 'ATLANTA', 'GA', '30314', 3.00);
INSERT INTO ORDERS
VALUES
(1002,1011,TO_DATE('31-MAR-09','DD-MON-YY'),TO_DATE('01-APR-09','DD-MON-YY'),'58
TILA CIRCLE', 'CHICAGO', 'IL', '60605', 3.00);
INSERT INTO ORDERS
VALUES
(1003,1001,TO_DATE('01-APR-09','DD-MON-YY'),TO_DATE('01-APR-09','DD-MON-YY'),'958
MAGNOLIA LANE', 'EASTPOINT', 'FL', '32328', 4.00);...