CREATE TABLE Orders

(

OrderID NUMBER NOT NULL,

CustomerID CHAR(5),

EmployeeID NUMBER,

OrderDate DATE,

RequiredDate DATE,

ShippedDate DATE,

Freight NUMBER,

ShipName VARCHAR2(40),

ShipAddress VARCHAR2(60),

ShipCity VARCHAR2(15),

ShipRegion VARCHAR2(15),

ShipPostalCode VARCHAR2(10),

ShipCountry VARCHAR2(15),

CONSTRAINT PK_Orders

PRIMARY KEY (OrderID),

CONSTRAINT FK_Orders_Customers FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID),

CONSTRAINT FK_Orders_Employees FOREIGN KEY (EmployeeID) REFERENCES Employees(EmployeeID)

)

/

CREATE TABLE OrderDetails

(

OrderID NUMBER NOT NULL,

ProductID NUMBER NOT NULL,

UnitPrice NUMBER NOT NULL,

Quantity NUMBER NOT NULL,

Discount NUMBER NOT NULL,

CONSTRAINT PK_Order_Details

PRIMARY KEY (OrderID, ProductID),

CONSTRAINT CK_Discount CHECK ((Discount >= 0 and Discount <= 1)),

CONSTRAINT CK_Quantity CHECK ((Quantity > 0)),

CONSTRAINT CK_UnitPrice CHECK ((UnitPrice >= 0))

)

/

insert INTO Orders (OrderID, CustomerID, EmployeeID, OrderDate, Freight)

Values (9901, 'ATT__', 511, sysdate, 21.3);

insert INTO Orders (OrderID, CustomerID, EmployeeID, OrderDate, Freight)

Values (9902, 'ATT__', 511, sysdate, 11.1);

insert INTO Orders (OrderID, CustomerID, EmployeeID, OrderDate, Freight)

Values (9903, 'ALFKI', 512, sysdate, 11.5);

insert INTO Orders (OrderID, CustomerID, EmployeeID, OrderDate, Freight)

Values (9904, 'UKMOD', 513, sysdate, 32.5);

insert INTO Orders (OrderID, CustomerID, EmployeeID, OrderDate, RequiredDate, ShippedDate, Freight, ShipName, ShipAddress, ShipCity, ShipCountry)

Values (9905, 'BONAP', 513, '16-JUL-96', '27-JUL-96', '20-JUL-96', 10.21, 'Bon app''', '12, rue des Bouchers', 'Marseille', 'France' );

INSERT INTO OrderDetails (OrderID, ProductID, UnitPrice, Quantity, Discount)

VALUES (9901,4411, 33, 5, 0.11);

INSERT INTO OrderDetails (OrderID, ProductID, UnitPrice, Quantity, Discount)

VALUES (9901,4412, 50, 20, 0.05);

Get all orders placed in July 1996 (between 7/1/1996 and 8/1/1996)