CREATE TABLE Region

(

RegionID NUMBER NOT NULL,

RegionDescription CHAR(50) NOT NULL,

CONSTRAINT PK_Region

PRIMARY KEY (RegionID)

)

/

CREATE TABLE Territories

(

TerritoryID VARCHAR2(20) NOT NULL,

TerritoryDescription CHAR(50) NOT NULL,

RegionID NUMBER NOT NULL,

CONSTRAINT PK_Territories

PRIMARY KEY (TerritoryID),

CONSTRAINT FK_Territories_Region FOREIGN KEY (RegionID) REFERENCES Region(RegionID)

)

/

CREATE TABLE Categories

(

CategoryID NUMBER NOT NULL,

CategoryName VARCHAR2(15) NOT NULL,

Description VARCHAR2(300),

Picture LONG RAW,

CONSTRAINT PK_Categories

PRIMARY KEY (CategoryID)

)

/

CREATE TABLE Suppliers

(

SupplierID NUMBER NOT NULL,

CompanyName VARCHAR2(40) NOT NULL,

ContactName VARCHAR2(30),

ContactTitle VARCHAR2(30),

Address VARCHAR2(60),

City VARCHAR2(15),

Region VARCHAR2(15),

PostalCode VARCHAR2(10),

Country VARCHAR2(15),

Phone VARCHAR2(24),

Fax VARCHAR2(24),

HomePage VARCHAR2(200),

CONSTRAINT PK_Suppliers

PRIMARY KEY (SupplierID)

)

/

CREATE TABLE Products

(

ProductID NUMBER NOT NULL,

ProductName VARCHAR2(40) NOT NULL,

SupplierID NUMBER,

CategoryID NUMBER,

QuantityPerUnit VARCHAR2(20),

UnitPrice NUMBER,

UnitsInStock NUMBER,

UnitsOnOrder NUMBER,

ReorderLevel NUMBER,

Discontinued NUMBER(1) NOT NULL,

CONSTRAINT PK_Products

PRIMARY KEY (ProductID),

CONSTRAINT CK_Products_UnitPrice CHECK ((UnitPrice >= 0)),

CONSTRAINT CK_ReorderLevel CHECK ((ReorderLevel >= 0)),

CONSTRAINT CK_UnitsInStock CHECK ((UnitsInStock >= 0)),

CONSTRAINT CK_UnitsOnOrder CHECK ((UnitsOnOrder >= 0)),

CONSTRAINT FK_Products_Categories FOREIGN KEY (CategoryID) REFERENCES Categories(CategoryID),

CONSTRAINT FK_Products_Suppliers FOREIGN KEY (SupplierID) REFERENCES Suppliers(SupplierID)

)

/

CREATE TABLE Shippers

(

ShipperID NUMBER NOT NULL,

CompanyName VARCHAR2(40) NOT NULL,

Phone VARCHAR2(24),

CONSTRAINT PK_Shippers

PRIMARY KEY (ShipperID)

)

/

CREATE TABLE Customers

(

CustomerID CHAR(5) NOT NULL,

CompanyName VARCHAR2(40) NOT NULL,

ContactName VARCHAR2(30),

ContactTitle VARCHAR2(30),

Address VARCHAR2(60),

City VARCHAR2(15),

Region VARCHAR2(15),

PostalCode VARCHAR2(10),

Country VARCHAR2(15),

Phone VARCHAR2(24),

Fax VARCHAR2(24),

CONSTRAINT PK_Customers

PRIMARY KEY (CustomerID)

)

/

CREATE TABLE Employees

(

EmployeeID NUMBER NOT NULL,

LastName VARCHAR2(20) NOT NULL,

FirstName VARCHAR2(10) NOT NULL,

Title VARCHAR2(30),

TitleOfCourtesy VARCHAR2(25),

BirthDate DATE,

HireDate DATE,

Address VARCHAR2(60),

City VARCHAR2(15),

Region VARCHAR2(15),

PostalCode VARCHAR2(10),

Country VARCHAR2(15),

HomePhone VARCHAR2(24),

Extension VARCHAR2(4),

Photo LONG RAW,

Notes VARCHAR2(600),

ReportsTo NUMBER,

PhotoPath VARCHAR2(255),

CONSTRAINT PK_Employees

PRIMARY KEY (EmployeeID),

CONSTRAINT FK_Employees_Employees FOREIGN KEY (ReportsTo) REFERENCES Employees(EmployeeID)

)

/

CREATE TABLE EmployeeTerritories

(

EmployeeID NUMBER NOT NULL,

TerritoryID VARCHAR2(20) NOT NULL,

CONSTRAINT PK_EmpTerritories

PRIMARY KEY (EmployeeID, TerritoryID),

CONSTRAINT FK_EmpTerri_Employees FOREIGN KEY (EmployeeID) REFERENCES Employees(EmployeeID),

CONSTRAINT FK_EmpTerri_Territories FOREIGN KEY (TerritoryID) REFERENCES Territories(TerritoryID)

)

/

CREATE TABLE CustomerDemographics

(

CustomerTypeID CHAR(10) NOT NULL,

CustomerDesc LONG,

CONSTRAINT PK_CustomerDemographics

PRIMARY KEY (CustomerTypeID)

)

/

CREATE TABLE CustomerCustomerDemo

(

CustomerID CHAR(5) NOT NULL,

CustomerTypeID CHAR(10) NOT NULL,

CONSTRAINT PK_CustomerDemo

PRIMARY KEY (CustomerID, CustomerTypeID),

CONSTRAINT FK_CustomerDemo FOREIGN KEY (CustomerTypeID) REFERENCESCustomerDemographics(CustomerTypeID),

CONSTRAINT FK_CustomerDemo_Customers FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID)

)

/

CREATE TABLE Orders

(

OrderID NUMBER NOT NULL,

CustomerID CHAR(5),

EmployeeID NUMBER,

TerritoryID VARCHAR2(20),

OrderDate DATE,

RequiredDate DATE,

ShippedDate DATE,

ShipVia NUMBER,

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),

CONSTRAINT FK_Orders_Shippers FOREIGN KEY (ShipVia) REFERENCES Shippers(ShipperID),

CONSTRAINT FK_Orders_Territories FOREIGN KEY (TerritoryID) REFERENCES Territories(TerritoryID)

)

/

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)),

CONSTRAINT FK_OrderDetails_Orders FOREIGN KEY (OrderID) REFERENCES Orders(OrderID),

CONSTRAINT FK_OrderDetails_Products FOREIGN KEY (ProductID) REFERENCES Products(ProductID)

)

/

Insert data

Insert INTO Categories (CategoryID, CategoryName,Description)

values (1, 'Beverages', 'Soft drinks, coffees, teas, beers, and ales');

Insert INTO Categories (CategoryID, CategoryName,Description)

values (2, 'Condiments','Sweet and savory sauces, relishes, spreads, and seasonings');

Insert INTO Categories (CategoryID, CategoryName,Description)

values (3, 'Seafood','Seaweed and fish');

Insert INTO Categories (CategoryID, CategoryName,Description)

values (4, 'Other','Other Products');

insert INTO Suppliers (SupplierID, CompanyName, ContactName, ContactTitle, Address, City, Region, Country)

VALUES (1, 'alles AG', 'Harald Reitmeyer', 'Prof', 'Fischergasse 8', 'Heidelberg', 'B-W', 'Germany');

insert INTO Suppliers (SupplierID, CompanyName, ContactName, ContactTitle, Address, City, Region, Country)

VALUES (4, 'Microsoft', 'Mr Allen', 'Monopolist', '1 MS', 'Redmond', 'WA', 'USA');

INSERT INTO Suppliers (SupplierID, CompanyName, ContactName, ContactTitle, Address, City, Region,PostalCode, Country, Phone, Fax)

VALUES (10, 'Pavlova, Ltd.', 'Ian Devling', 'Marketing Manager', '74 Rose St. Moonie Ponds', 'Melbourne', 'Victoria', '3058', 'Australia', '(03) 444-2343', '(03) 444-6588');

insert INTO Products (ProductID, ProductName,SupplierID, CategoryID,QuantityPerUnit,UnitsInStock,UnitsOnOrder,Discontinued)

VALUES (4411, 'Pen',1, 4, 10, 12, 2, 0);

insert INTO Products (ProductID, ProductName,SupplierID, CategoryID,QuantityPerUnit,UnitsInStock,UnitsOnOrder,Discontinued)

VALUES (4412, 'Bicycle',1, 4, 1, 6, 0, 0);

insert INTO Products (ProductID, ProductName,SupplierID, CategoryID,QuantityPerUnit,UnitsInStock,UnitsOnOrder,Discontinued)

VALUES (4413, 'Linq Book',4, 2, 1, 0, 26, 0);

INSERT INTO Products (ProductID, ProductName,SupplierID, CategoryID,QuantityPerUnit,UnitPrice,UnitsInStock,UnitsOnOrder,Discontinued)

VALUES (4414, 'Carnarvon Tigers', 10, 3,'16 kg pkg.',62.50, 42, 0, 0);

INSERT INTO Region VALUES (1, 'North America');

INSERT INTO Region VALUES (2, 'Europe');

INSERT INTO Territories (TerritoryID,TerritoryDescription, RegionID) VALUES (10012, 'US.Northwest', 1);

insert INTO Customers (CustomerID, CompanyName,ContactName,Country,PostalCode,City)

values ('AIRBU', 'airbus','jacques','France','10000','Paris');

insert INTO Customers (CustomerID, CompanyName,ContactName,Country,PostalCode,City)

values ('BT___','BT','graeme','U.K.','E14','London');

insert INTO Customers (CustomerID, CompanyName,ContactName,Country,PostalCode,City)

values ('ATT__','ATT','bob','USA','10021','New York');

insert INTO Customers (CustomerID, CompanyName,ContactName,Country,PostalCode,City)

values ('UKMOD', 'MOD','(secret)','U.K.','E14','London');

insert INTO Customers (CustomerID, CompanyName,ContactName, ContactTitle, Country,PostalCode,City, Phone)

values ('ALFKI', 'Alfreds Futterkiste','Maria Anders','Sales Representative','Germany','12209','Berlin','030-0074321');

insert INTO Customers (CustomerID, CompanyName,ContactName, ContactTitle,Country,PostalCode,Address,City, Phone, Fax)

values ('BONAP', 'Bon app''','Laurence Lebihan','Owner','France','13008','12, rue des Bouchers','Marseille','91.24.45.40', '91.24.45.41');

insert INTO Customers (CustomerID, CompanyName,ContactName, ContactTitle, Country,PostalCode,City, Phone)

values ('WARTH', 'Wartian Herkku','Pirkko Koskitalo','Accounting Manager','Finland','90110','Oulu','981-443655');

insert INTO Employees (EmployeeID,LastName,FirstName,Title,BirthDate,HireDate,Address,City,ReportsTo,Country,HomePhone)

VALUES (511, 'Fuller','Andrew','Vice President, Sales','01-JAN-54','01-JAN-89', '908 W. CapitalWay','Tacoma',NULL,'USA','(111)222333');

insert INTO Employees (EmployeeID,LastName,FirstName,Title,BirthDate,HireDate,Address,City,ReportsTo,Country,HomePhone)

VALUES (512, 'Davolio','Nancy','Sales Representative','01-JAN-64','01-JAN-94','507 - 20th Ave. E. Apt. 2A','Seattle',511,'USA','(444)555666');

insert INTO Employees (EmployeeID,LastName,FirstName,Title,BirthDate,HireDate,Address,City,ReportsTo,Country,HomePhone)

VALUES (513, 'Builder','Bob','Handyman','01-JAN-64','01-JAN-94','666 dark street','Seattle',511,'USA','(777)888999');

insert into employeeTerritories (EmployeeID,TerritoryID)

values (511,10012);

insert into employeeTerritories (EmployeeID,TerritoryID)

values (512,10012);

insert into employeeTerritories (EmployeeID,TerritoryID)

values (513,10012);

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);

Part 1. Projection and Selection

1.1 Please get all data in the Employees table.

1.2 I want to get the contact names and phone numbers of my suppliers in Australia.

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

1.4 Get all products whose name ends with ers, list product name and unit price.

Part 2. Sorting; logical operators (and, or)

2.1 I want to get a list of customer in these cities: Berlin, Madrid, London, Bern. Order the results by city.

2.2 Get all orders shipped to USA or France, and after 6/1/1996; list order id, ship country, and order date; sorted by country first, and then by order date.

2.3 I am looking for customers (companies) whose name starts with Al or AT and contact title is not missing. Please sort the results by postal code.

Part 3. Simple table join

3.1 Display first and last names of ALL employees along with names of their managers.

3.2 Get the employee who processed the order 9905 (order id); list first name and last name.

3.3 I need a list of orders by customers; I want see CompanyName, OrderId, OrderDate, and Product name.