/* Written by Andrea Sansottera (andrea.sansottera@fastwebnet.it)
 * This script creates a Firebird database which is similar to the Northwind sample database for MS SqlServer.
 * This script is intended for testing the Firebird provider for the Microsoft Enterprise Library.
 * This script generates a minimal subset of the data, just what is needed for testing the Firebird provider.
 */

SET SQL DIALECT 3;

CREATE DATABASE 'd:\Test.fdb'
USER 'SYSDBA' PASSWORD 'masterkey';


/* Tables definition: Region */

CREATE TABLE Region (
    RegionID INTEGER NOT NULL,
    RegionDescription VARCHAR (50) CHARACTER SET UNICODE_FSS);

ALTER TABLE Region ADD CONSTRAINT PK_Region PRIMARY KEY (RegionID);

/* Table definition: Categories */

CREATE TABLE Categories (
    CategoryID  integer not null,
    CategoryName varchar (15) not null,
    Description BLOB SUB_TYPE 1,
    Picture BLOB SUB_TYPE 0);

ALTER TABLE Categories ADD CONSTRAINT PK_Category PRIMARY KEY (CategoryID);
CREATE INDEX Index_ProductCategoryName ON Categories (CategoryName);

/* Table definition: Suppliers */

CREATE TABLE Suppliers (
    SupplierID INTEGER NOT NULL,
    CompanyName VARCHAR (40) NOT NULL,
    ContactName VARCHAR (30),
    ContactTitle VARCHAR (30),
    Address VARCHAR (60),
    City VARCHAR (15),
    Region VARCHAR (15),
    PostalCode VARCHAR (10),
    Country VARCHAR (15),
    Phone VARCHAR (24),
    Fax VARCHAR (24),
    HomePage BLOB SUB_TYPE 1);

ALTER TABLE Suppliers ADD CONSTRAINT PK_Supplier PRIMARY KEY (SupplierID);
CREATE INDEX Index_SupplierCompanyName ON Suppliers (CompanyName);
CREATE INDEX Index_SupplierPostalCode ON Suppliers (PostalCode);

/* Table definition: Products */

CREATE TABLE Products (
    ProductID INTEGER NOT NULL,
    ProductName VARCHAR (40) NOT NULL,
    SupplierID INTEGER,
    CategoryID INTEGER,
    QuantityPerUnit VARCHAR (20),
    UnitPrice DOUBLE PRECISION,
    UnitsInStock SMALLINT,
    UnitsOnOrder SMALLINT,
    ReorderLevel SMALLINT,
    Discontinued SMALLINT);

ALTER TABLE Products ADD CONSTRAINT PK_Product PRIMARY KEY (ProductID);
ALTER TABLE Products ADD CONSTRAINT CK_ProductUnitPrice CHECK (UnitPrice > 0);
ALTER TABLE Products ADD CONSTRAINT CK_ProductReorderLevel CHECK (ReorderLevel > 0);
ALTER TABLE Products ADD CONSTRAINT CK_ProductUnitsInStock CHECK (UnitsInStock > 0);
ALTER TABLE Products ADD CONSTRAINT CK_ProductUnitsOnOrder CHECK (UnitsOnOrder > 0);
ALTER TABLE Products ADD CONSTRAINT FK_ProductCategory FOREIGN KEY (CategoryID) REFERENCES Categories (CategoryID);
ALTER TABLE Products ADD CONSTRAINT FK_ProductSupplier FOREIGN KEY (SupplierID) REFERENCES Suppliers (SupplierID);
CREATE INDEX Index_ProductName ON Products (ProductName);

/* Table definition: Employees */

CREATE TABLE Employees (
    EmployeeID INTEGER NOT NULL,
    LastName VARCHAR (20) NOT NULL,
    FirstName VARCHAR (20) NOT NULL,
    Title VARCHAR (30),
    TitleOfCourtesy VARCHAR (25),
    BirthDate TIMESTAMP,
    HireDate TIMESTAMP,
    Address VARCHAR (60),
    City VARCHAR (15),
    Region VARCHAR (15),
    PostalCode VARCHAR (10),
    Country VARCHAR (15),
    HomePhone VARCHAR (24),
    Extension VARCHAR (4),
    Photo BLOB SUB_TYPE 0,
    Notes BLOB SUB_TYPE 1,
    ReportsTo INTEGER,
    PhotoPath VARCHAR (255));

ALTER TABLE Employees ADD CONSTRAINT PK_Employee PRIMARY KEY (EmployeeID);
ALTER TABLE Employees ADD CONSTRAINT CK_EmployeeBirthDate CHECK (BirthDate < current_timestamp);
ALTER TABLE Employees ADD CONSTRAINT FK_EmployeeReportsTo FOREIGN KEY (ReportsTo) REFERENCES Employees (EmployeeID);
CREATE INDEX Index_EmployeeLastName ON Employees (LastName);
CREATE INDEX Index_EmployeePostalCode ON Employees (PostalCode);

/* Table definition: Shippers */

CREATE TABLE Shippers (
    ShipperID INTEGER NOT NULL,
    CompanyName VARCHAR (40) NOT NULL,
    Phone VARCHAR (24));

ALTER TABLE Shippers ADD CONSTRAINT PK_Shipper PRIMARY KEY (ShipperID);

/* Table defintion: Customers */

CREATE TABLE Customers (
    CustomerID CHAR (5) NOT NULL,
    CompanyName VARCHAR (40) NOT NULL,
    ContactName VARCHAR (30),
    ContactTitle VARCHAR (30),
    Address VARCHAR (60),
    City VARCHAR (15),
    Region VARCHAR (15),
    PostalCode VARCHAR (10),
    Country VARCHAR (15),
    Phone VARCHAR (24),
    Fax VARCHAR (24));

ALTER TABLE Customers ADD CONSTRAINT PK_Customer PRIMARY KEY (CustomerID);
CREATE INDEX Index_CustomerCity ON Customers (City);
CREATE INDEX Index_CustomerCompanyName ON Customers (CompanyName);
CREATE INDEX Index_CustomerPostalCode ON Customers (PostalCode);
CREATE INDEX Index_CustomerRegion ON Customers (Region);

/* Table definition: Orders */

CREATE TABLE Orders (
    OrderID INTEGER NOT NULL,
    CustomerID CHAR (5),
    EmployeeID INTEGER,
    OrderDate TIMESTAMP,
    RequiredDate TIMESTAMP,
    ShippedDate TIMESTAMP,
    ShipVia INTEGER,
    Freight DOUBLE PRECISION,
    ShipName VARCHAR (40),
    ShipAddress VARCHAR (60),
    ShipCity VARCHAR (15),
    ShipRegion VARCHAR (15),
    ShipPostalCode VARCHAR (10),
    ShipCountry VARCHAR (15));

ALTER TABLE Orders ADD CONSTRAINT PK_Order PRIMARY KEY (OrderID);
ALTER TABLE Orders ADD CONSTRAINT FK_OrderCustomer FOREIGN KEY (CustomerID) REFERENCES Customers (CustomerID);
ALTER TABLE Orders ADD CONSTRAINT FK_OrderEmployee FOREIGN KEY (EmployeeID) REFERENCES Employees (EmployeeID);
ALTER TABLE Orders ADD CONSTRAINT FK_OrderShipper FOREIGN KEY (ShipVia) REFERENCES Shippers (ShipperID);
CREATE INDEX Index_OrderDate ON Orders (OrderDate);
CREATE INDEX Index_OrderShippedDate ON Orders (ShippedDate);
CREATE INDEX Index_OrderShipPostalCode ON Orders (ShipPostalCode);

/* Table definition: Territories */

CREATE TABLE Territories (
    TerritoryID VARCHAR (20) NOT NULL,
    TerritoryDescription CHAR (50) NOT NULL,
    RegionID INTEGER NOT NULL);

ALTER TABLE Territories ADD CONSTRAINT PK_Territory PRIMARY KEY (TerritoryID);
ALTER TABLE Territories ADD CONSTRAINT FK_TerritoryRegion FOREIGN KEY (RegionID) REFERENCES Region (RegionID);

/* Table definition: EmployeeTerritories */

CREATE TABLE EmployeeTerritories (
    EmployeeID INTEGER NOT NULL,
    TerritoryID VARCHAR (20) NOT NULL);

ALTER TABLE EmployeeTerritories ADD CONSTRAINT PK_EmployeeTerritory PRIMARY KEY (EmployeeID, TerritoryID);
ALTER TABLE EmployeeTerritories ADD CONSTRAINT FK_EmployeeHasTerritory FOREIGN KEY (EmployeeID) REFERENCES Employees (EmployeeID);
ALTER TABLE EmployeeTerritories ADD CONSTRAINT FK_TerritoryOfEmployee FOREIGN KEY (TerritoryID) REFERENCES Territories (TerritoryID);

/* Table definition: CustomerDemographics */

CREATE TABLE CustomerDemographics (
    CustomerTypeID CHAR (10) NOT NULL,
    CustomerDesc BLOB SUB_TYPE 1);

ALTER TABLE CustomerDemographics ADD CONSTRAINT PK_CustomerDemographic PRIMARY KEY (CustomerTypeID);

/* Table definition: Order Details */

CREATE TABLE "Order Details" (
    OrderID INTEGER NOT NULL,
    ProductID INTEGER NOT NULL,
    UnitPrice DOUBLE PRECISION,
    Quantity SMALLINT,
    Discount FLOAT);

ALTER TABLE "Order Details" ADD CONSTRAINT PK_OrderDetail PRIMARY KEY (OrderID, ProductID);
ALTER TABLE "Order Details" ADD CONSTRAINT FK_OrderDetailOrder FOREIGN KEY (OrderID) REFERENCES Orders (OrderID);
ALTER TABLE "Order Details" ADD CONSTRAINT FK_OrderDetailProduct FOREIGN KEY (ProductID) REFERENCES Products (ProductID);

/* Table definition: CustomerCustomerDemo */

CREATE TABLE CustomerCustomerDemo (
    CustomerID CHAR (5) NOT NULL,
    CustomerTypeID CHAR (10) NOT NULL);

ALTER TABLE CustomerCustomerDemo ADD CONSTRAINT PK_DemographicOfCustomer PRIMARY KEY (CustomerID, CustomerTypeID);
ALTER TABLE CustomerCustomerDemo ADD CONSTRAINT FK_CustomerHasDemographic FOREIGN KEY (CustomerID) REFERENCES Customers (CustomerID);
ALTER TABLE CustomerCustomerDemo ADD CONSTRAINT FK_DemographicOfCustomer FOREIGN KEY (CustomerTypeID) REFERENCES CustomerDemographics (CustomerTypeID);

/* View definition: Alphabetical list of products */

CREATE VIEW "Alphabetical list of products" AS
SELECT Products.*, Categories.CategoryName
FROM Categories INNER JOIN Products ON Categories.CategoryID = Products.CategoryID
WHERE ((Products.Discontinued) = 0);

/* View definition: Product Sales for 1997 */

CREATE VIEW "Product Sales for 1997" (CategoryName, ProductName, ProductSales) AS
SELECT
    Categories.CategoryName,
    Products.ProductName,
    SUM (("Order Details".UnitPrice * Quantity * (1 - Discount) / 100) * 100)
FROM (Categories INNER JOIN Products ON Categories.CategoryID = Products.CategoryID)
INNER JOIN (Orders INNER JOIN "Order Details" ON Orders.OrderID = "Order Details".OrderID) ON Products.ProductID = "Order Details".ProductID
GROUP BY Categories.CategoryName, Products.ProductName;

/* View definition: Category Sales for 1997 */

CREATE VIEW "Category Sales for 1997" (CategoryName, CategorySales) AS
SELECT
    "Product Sales for 1997".CategoryName,
    SUM ("Product Sales for 1997".ProductSales)
FROM "Product Sales for 1997"
GROUP BY "Product Sales for 1997".CategoryName;


/* View definition: Current Product List */

CREATE VIEW "Current Product List" AS
SELECT
    ProductID,
    ProductName
FROM Products
WHERE ((Discontinued) = 0)
--ORDER BY ProductName;

/* View definition: Customer and Suppliers by City */

CREATE VIEW "Customer and Suppliers by City"
    (City,
    CompanyName,
    ContactName,
    Relationship) AS
SELECT City, CompanyName, ContactName, 'Customers'
FROM Customers
UNION SELECT City, CompanyName, ContactName, 'Suppliers'
FROM Suppliers
--ORDER BY City, CompanyName;

/* Invoices */

CREATE VIEW Invoices (
    ShipName,
    ShipAddress,
    ShipCity,
    ShipRegion,
    ShipPostalCode,
    ShipCountry,
    CustomerID,
    CustomerName,
    Address,
    City,
    Region,
    PostalCode,
    Country,
    Salesperson,
    OrderID,
    OrderDate,
    RequiredDate,
    ShippedDate,
    ShipperName,
    ProductID,
    ProductName,
    UnitPrice,
    Quantity,
    Discount,
    ExtendedPrice,
    Freight) AS
SELECT
    Orders.ShipName,
    Orders.ShipAddress,
    Orders.ShipCity,
    Orders.ShipRegion,
    Orders.ShipPostalCode,
	Orders.ShipCountry,
    Orders.CustomerID,
    Customers.CompanyName,
    Customers.Address,
    Customers.City,
	Customers.Region,
    Customers.PostalCode,
    Customers.Country,
	(Employees.FirstName || ' ' || Employees.LastName),
	Orders.OrderID,
    Orders.OrderDate,
    Orders.RequiredDate,
    Orders.ShippedDate,
    Shippers.CompanyName,
	"Order Details".ProductID,
    Products.ProductName,
    "Order Details".UnitPrice,
    "Order Details".Quantity,
	"Order Details".Discount,
	(("Order Details".UnitPrice*Quantity*(1-Discount)/100)*100),
    Orders.Freight
FROM Shippers 
    INNER JOIN (Products INNER JOIN
    ((Employees 
        INNER JOIN (Customers INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID) ON Employees.EmployeeID = Orders.EmployeeID)
         INNER JOIN "Order Details" ON Orders.OrderID = "Order Details".OrderID)
    ON Products.ProductID = "Order Details".ProductID)
	ON Shippers.ShipperID = Orders.ShipVia;

/* View definition: Order Details Extended */

CREATE VIEW "Order Details Extended"
    (OrderID,
    ProductID,
    ProductName,
    UnitPrice,
    Quantity,
    Discount,
    ExtendedPrice)
AS
SELECT
    "Order Details".OrderID,
    "Order Details".ProductID,
    Products.ProductName,
	"Order Details".UnitPrice,
    "Order Details".Quantity,
    "Order Details".Discount,
	(("Order Details".UnitPrice*Quantity*(1-Discount)/100)*100)
FROM Products
INNER JOIN "Order Details" ON Products.ProductID = "Order Details".ProductID
--ORDER BY "Order Details".OrderID;

/* View definition: Orders Subtotals */

CREATE VIEW "Order Subtotals"
    (OrderID,
    Subtotal)
AS
SELECT
    "Order Details".OrderID,
    SUM ((("Order Details".UnitPrice*Quantity*(1-Discount)/100))*100)
FROM "Order Details"
GROUP BY "Order Details".OrderID;

/* View definition: Orders Qry */

CREATE VIEW "Orders Qry" AS
SELECT
    Orders.OrderID,
    Orders.CustomerID,
    Orders.EmployeeID,
    Orders.OrderDate,
    Orders.RequiredDate,
	Orders.ShippedDate,
    Orders.ShipVia,
    Orders.Freight,
    Orders.ShipName,
    Orders.ShipAddress,
    Orders.ShipCity,
	Orders.ShipRegion,
    Orders.ShipPostalCode,
    Orders.ShipCountry,
	Customers.CompanyName,
    Customers.Address,
    Customers.City,
    Customers.Region,
    Customers.PostalCode,
    Customers.Country
FROM Customers
INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID;

/* View definition: Products Above Average Price */

CREATE VIEW "Products Above Average Price" AS
SELECT
    Products.ProductName,
    Products.UnitPrice
FROM Products
WHERE Products.UnitPrice > (SELECT AVG(UnitPrice) FROM Products)
--ORDER BY Products.UnitPrice DESC;

/* View definition: Products by Category */

CREATE VIEW "Products by Category" AS
SELECT
    Categories.CategoryName,
    Products.ProductName,
    Products.QuantityPerUnit,
    Products.UnitsInStock,
    Products.Discontinued
FROM Categories
INNER JOIN Products ON Categories.CategoryID = Products.CategoryID
WHERE Products.Discontinued <> 1
--ORDER BY Categories.CategoryName, Products.ProductName;

/* View definition: Quarterly Orders */

CREATE VIEW "Quarterly Orders" AS
SELECT DISTINCT
    Customers.CustomerID,
    Customers.CompanyName,
    Customers.City,
    Customers.Country
FROM Customers
RIGHT JOIN Orders ON Customers.CustomerID = Orders.CustomerID
WHERE Orders.OrderDate BETWEEN '19970101' And '19971231';

/* View definition: Sales by Category */

CREATE VIEW "Sales by Category"
    (CategoryID,
    CategoryName,
    ProductName,
    ProductSales)
AS
SELECT
    Categories.CategoryID,
    Categories.CategoryName,
    Products.ProductName,
	SUM ("Order Details Extended".ExtendedPrice)
FROM Categories
INNER JOIN (Products
    INNER JOIN (Orders
        INNER JOIN "Order Details Extended"
        ON Orders.OrderID = "Order Details Extended".OrderID)
    ON Products.ProductID = "Order Details Extended".ProductID)
ON Categories.CategoryID = Products.CategoryID
WHERE Orders.OrderDate BETWEEN '19970101' And '19971231'
GROUP BY Categories.CategoryID, Categories.CategoryName, Products.ProductName
--ORDER BY Products.ProductName;

/* View definition: Sales Totals by Amount */

CREATE VIEW "Sales Totals by Amount"
    (SaleAmount,
    OrderID,
    CompanyName,
    ShippedDate)
AS
SELECT
    "Order Subtotals".Subtotal,
    Orders.OrderID, Customers.CompanyName,
    Orders.ShippedDate
FROM Customers
INNER JOIN (Orders
    INNER JOIN "Order Subtotals"
    ON Orders.OrderID = "Order Subtotals".OrderID)
ON Customers.CustomerID = Orders.CustomerID
WHERE ("Order Subtotals".Subtotal > 2500) AND (Orders.ShippedDate BETWEEN '19970101' And '19971231');

/* View definition: Summary of Sales by Quarter */

CREATE VIEW "Summary of Sales by Quarter" AS
SELECT
    Orders.ShippedDate,
    Orders.OrderID,
    "Order Subtotals".Subtotal
FROM Orders
INNER JOIN "Order Subtotals" ON Orders.OrderID = "Order Subtotals".OrderID
WHERE Orders.ShippedDate IS NOT NULL
--ORDER BY Orders.ShippedDate;

/* View definition: Summary of Sales by Year */

CREATE VIEW "Summary of Sales by Year" AS
SELECT
    Orders.ShippedDate,
    Orders.OrderID,
    "Order Subtotals".Subtotal
FROM Orders
INNER JOIN "Order Subtotals" ON Orders.OrderID = "Order Subtotals".OrderID
WHERE Orders.ShippedDate IS NOT NULL
--ORDER BY Orders.ShippedDate;

/* Stored procedure definition: CustOrdersHist */

SET TERM ^ ;

CREATE PROCEDURE CustOrderHist (CustomerID CHAR (5))
RETURNS (ProductName VARCHAR (40), Total SMALLINT)
AS
BEGIN
    FOR
    SELECT ProductName, SUM(Quantity)
    FROM Products P, "Order Details" OD, Orders O, Customers C
    WHERE
        C.CustomerID = :CustomerID AND
        C.CustomerID = O.CustomerID AND
        O.OrderID = OD.OrderID AND
        OD.ProductID = P.ProductID
    GROUP BY ProductName
    INTO :ProductName, :Total
    DO
        SUSPEND;
END ^

SET TERM ; ^

/* Stored procedure definition: CustOrdersDetail */

SET TERM ^ ;

CREATE PROCEDURE CustOrdersDetail (OrderID INTEGER)
RETURNS (ProductName VARCHAR (40), UnitPrice DOUBLE PRECISION, Quantity SMALLINT, Discount INTEGER, ExtendedPrice DOUBLE PRECISION)
AS
BEGIN
    FOR
    SELECT
        ProductName,
        Od.UnitPrice,
        Quantity,
        CAST ((Discount * 100) AS INTEGER),
        (Quantity * (1 - Discount) * Od.UnitPrice)
    FROM Products P, "Order Details" Od
    WHERE Od.ProductID = P.ProductID and Od.OrderID = :OrderID
    INTO :ProductName, :UnitPrice, :Quantity, :Discount, :ExtendedPrice
    DO
        SUSPEND;
END ^

SET TERM ; ^

/* Stored procedure definition: CustOrdersOrders */

SET TERM ^ ;

CREATE PROCEDURE CustOrdersOrders (CustomerID CHAR (5))
RETURNS (OrderID INTEGER, OrderDate TIMESTAMP, RequiredDate TIMESTAMP, ShippedDate TIMESTAMP)
AS
BEGIN
    FOR
    SELECT
        OrderID,
        OrderDate,
        RequiredDate,
        ShippedDate
    FROM Orders
    WHERE CustomerID = :CustomerID
    ORDER BY OrderID
    INTO :OrderID, :OrderDate, :RequiredDate, :ShippedDate
    DO
        SUSPEND;
END ^

SET TERM ; ^

/* Stored procedure definition: Employee Sales by Country */

SET TERM ^ ;

CREATE PROCEDURE "Employee Sales by Country" (Beginning_Date TIMESTAMP, Ending_Date TIMESTAMP)
RETURNS (Country VARCHAR (15), LastName VARCHAR (20), FirstName VARCHAR (10), ShippedDate TIMESTAMP, OrderID INTEGER, SaleAmount DOUBLE PRECISION)
AS
BEGIN
    FOR
    SELECT
        Employees.Country,
        Employees.LastName,
        Employees.FirstName,
        Orders.ShippedDate,
        Orders.OrderID,
        "Order Subtotals".Subtotal
    FROM Employees
    INNER JOIN (Orders INNER JOIN "Order Subtotals" ON Orders.OrderID = "Order Subtotals".OrderID)
	ON Employees.EmployeeID = Orders.EmployeeID
    WHERE Orders.ShippedDate Between :Beginning_Date And :Ending_Date
    INTO :Country, :LastName, :FirstName, :ShippedDate, :OrderID, :SaleAmount
    DO
        SUSPEND;
 END ^
 
 SET TERM ; ^
 
/* Stored procedure definition: Sales by Year */

SET TERM ^ ;

CREATE PROCEDURE "Sales by Year" (Beginning_Date TIMESTAMP, Ending_Date TIMESTAMP)
RETURNS (ShippedDate TIMESTAMP, OrderID INTEGER, Subtotal DOUBLE PRECISION, "Year" INTEGER)
AS
BEGIN
    FOR
    SELECT
        Orders.ShippedDate,
        Orders.OrderID,
        "Order Subtotals".Subtotal,
        EXTRACT (YEAR FROM Orders.ShippedDate)
    FROM Orders
    INNER JOIN "Order Subtotals" ON Orders.OrderID = "Order Subtotals".OrderID
    WHERE Orders.ShippedDate Between :Beginning_Date And :Ending_Date
    INTO :ShippedDate, :OrderID, :Subtotal, :"Year"
    DO
        SUSPEND;
END ^

SET TERM ; ^

/* Stored procedure definition: SalesByCategory */

SET TERM ^ ;

CREATE PROCEDURE SalesByCategory (CategoryName VARCHAR (15), OrdYear VARCHAR (4))
RETURNS (ProductName VARCHAR (40), TotalPurchase DECIMAL (14,2))
AS
BEGIN
    IF (OrdYear != '1996' AND OrdYear != '1997' AND OrdYear != '1998') THEN
    BEGIN
	   OrdYear = '1998';
    END

    FOR SELECT
        ProductName,
        SUM(CAST ((OD.Quantity * (1 - OD.Discount) * OD.UnitPrice) AS DECIMAL (14,2)))
    FROM "Order Details" OD, Orders O, Products P, Categories C
    WHERE OD.OrderID = O.OrderID
        AND OD.ProductID = P.ProductID
        AND P.CategoryID = C.CategoryID
        AND C.CategoryName = :CategoryName
        AND (CAST (EXTRACT (YEAR FROM O.OrderDate) AS VARCHAR (4))) = :OrdYear
    GROUP BY ProductName
    ORDER BY ProductName
    INTO :ProductName, :TotalPurchase
    DO
        SUSPEND;
END ^

SET TERM ; ^

/* Stored procedure definition: Ten Most Expensive Products */

SET TERM ^ ;

CREATE PROCEDURE "Ten Most Expensive Products"
RETURNS (TenMostExpensiveProducts VARCHAR (40), UnitPrice DOUBLE PRECISION)
AS
declare variable rows SMALLINT = 10;
BEGIN
    WHILE (rows > 0) DO
    BEGIN
        SELECT
            Products.ProductName,
            Products.UnitPrice
        FROM Products
        ORDER BY Products.UnitPrice DESC
        INTO :TenMostExpensiveProducts, :UnitPrice;
        SUSPEND;
        rows = :rows - 1;
    END

END ^

SET TERM ; ^

/* Data insertion: Region */

INSERT INTO Region (RegionID, RegionDescription) VALUES (1, 'Eastern');
INSERT INTO Region (RegionID, RegionDescription) VALUES (2, 'Western');
INSERT INTO Region (RegionID, RegionDescription) VALUES (3, 'Northern');
INSERT INTO Region (RegionID, RegionDescription) VALUES (4, 'Southern');

/* Data insertion: Categories */

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, 'Confections', 'Desserts, candies, and sweet breads');
INSERT INTO Categories (CategoryID, CategoryName, Description) VALUES (4, 'Dairy Products', 'Cheeses');
INSERT INTO Categories (CategoryID, CategoryName, Description) VALUES (5, 'Grains/Cereals', 'Breads, crackers, pasta, and cereal');
INSERT INTO Categories (CategoryID, CategoryName, Description) VALUES (6, 'Meat/Poultry', 'Prepared meats');
INSERT INTO Categories (CategoryID, CategoryName, Description) VALUES (7, 'Produce', 'Dried fruit and bean curd');
INSERT INTO Categories (CategoryID, CategoryName, Description) VALUES (8, 'Seafood', 'Seaweed and fish');

/* Data insertion: Customers */

INSERT INTO Customers VALUES ('ALFKI', 'Alfreds Futterkiste', 'Maria Anders', 'Sales Representative', 'Obere Str. 57', 'Berlin', null, 12209, 'Germany', '030-0074321', '030-0076545');

/* Data insertion: Employees */

INSERT INTO Employees
VALUES ( 2, 'Fuller', 'Andrew', 'Vice President, Sales', 'Dr.', '1952-02-19 00:00:00.000', '1992-08-14 00:00:00.000', '908 W. Capital Way', 'Tacoma', 'WA', '98401', 'USA', '(206) 555-9482', '3457', null, 'Andrew received his BTS commercial in 1974 and a Ph.D. in international marketing from the University of Dallas in 1981.  He is fluent in French and Italian and reads German.  He joined the company as a sales representative, was promoted to sales manager in January 1992 and to vice president of sales in March 1993.  Andrew is a member of the Sales Management Roundtable, the Seattle Chamber of Commerce, and the Pacific Rim Importers Association.', NULL, 'http://accweb/emmployees/fuller.bmp' );

INSERT INTO Employees
VALUES ( 1, 'Davolio', 'Nancy', 'Sales Representative', 'Ms.', '1948-12-08 00:00:00.000', '1992-05-01 00:00:00.000', '507 - 20th Ave. E.
Apt. 2A', 'Seattle', 'WA', '98122', 'USA', '(206) 555-9857', '5467', null, 'Education includes a BA in psychology from Colorado State University in 1970.  She also completed "The Art of the Cold Call."  Nancy is a member of Toastmasters International.', 2, 'http://accweb/emmployees/davolio.bmp' );

INSERT INTO Employees
VALUES ( 3, 'Leverling', 'Janet', 'Sales Representative', 'Ms.', '1963-08-30 00:00:00.000', '1992-04-01 00:00:00.000', '722 Moss Bay Blvd.', 'Kirkland', 'WA', '98033', 'USA', '(206) 555-3412', '3355', null, 'Janet has a BS degree in chemistry from Boston College (1984).  She has also completed a certificate program in food retailing management.  Janet was hired as a sales associate in 1991 and promoted to sales representative in February 1992.', 2, 'http://accweb/emmployees/leverling.bmp' );

INSERT INTO Employees
VALUES ( 4, 'Peacock', 'Margaret', 'Sales Representative', 'Mrs.', '1937-09-19 00:00:00.000', '1993-05-03 00:00:00.000', '4110 Old Redmond Rd.', 'Redmond', 'WA', '98052', 'USA', '(206) 555-8122', '5176', null, 'Margaret holds a BA in English literature from Concordia College (1958) and an MA from the American Institute of Culinary Arts (1966).  She was assigned to the London office temporarily from July through November 1992.', 2, 'http://accweb/emmployees/peacock.bmp' );

INSERT INTO Employees
VALUES ( 5, 'Buchanan', 'Steven', 'Sales Manager', 'Mr.', '1955-03-04 00:00:00.000', '1993-10-17 00:00:00.000', '14 Garrett Hill', 'London', NULL, 'SW1 8JR', 'UK', '(71) 555-4848', '3453', null, 'Steven Buchanan graduated from St. Andrews University, Scotland, with a BSC degree in 1976.  Upon joining the company as a sales representative in 1992, he spent 6 months in an orientation program at the Seattle office and then returned to his permanent post in London.  He was promoted to sales manager in March 1993.  Mr. Buchanan has completed the courses "Successful Telemarketing" and "International Sales Management."  He is fluent in French.', 2, 'http://accweb/emmployees/buchanan.bmp' );

INSERT INTO Employees
VALUES ( 6, 'Suyama', 'Michael', 'Sales Representative', 'Mr.', '1963-07-02 00:00:00.000', '1993-10-17 00:00:00.000', 'Coventry House
Miner Rd.', 'London', NULL, 'EC2 7JR', 'UK', '(71) 555-7773', '428', null, 'Michael is a graduate of Sussex University (MA, economics, 1983) and the University of California at Los Angeles (MBA, marketing, 1986).  He has also taken the courses "Multi-Cultural Selling" and "Time Management for the Sales Professional."  He is fluent in Japanese and can read and write French, Portuguese, and Spanish.', 5, 'http://accweb/emmployees/davolio.bmp' );

INSERT INTO Employees
VALUES ( 7, 'King', 'Robert', 'Sales Representative', 'Mr.', '1960-05-29 00:00:00.000', '1994-01-02 00:00:00.000', 'Edgeham Hollow
Winchester Way', 'London', NULL, 'RG1 9SP', 'UK', '(71) 555-5598', '465', null, 'Robert King served in the Peace Corps and traveled extensively before completing his degree in English at the University of Michigan in 1992, the year he joined the company.  After completing a course entitled "Selling in Europe," he was transferred to the London office in March 1993.', 5, 'http://accweb/emmployees/davolio.bmp' );

INSERT INTO Employees
VALUES ( 8, 'Callahan', 'Laura', 'Inside Sales Coordinator', 'Ms.', '1958-01-09 00:00:00.000', '1994-03-05 00:00:00.000', '4726 - 11th Ave. N.E.', 'Seattle', 'WA', '98105', 'USA', '(206) 555-1189', '2344', null, 'Laura received a BA in psychology from the University of Washington.  She has also completed a course in business French.  She reads and writes French.', 2, 'http://accweb/emmployees/davolio.bmp' );

INSERT INTO Employees
VALUES ( 9, 'Dodsworth', 'Anne', 'Sales Representative', 'Ms.', '1966-01-27 00:00:00.000', '1994-11-15 00:00:00.000', '7 Houndstooth Rd.', 'London', NULL, 'WG2 7LT', 'UK', '(71) 555-4444', '452', null, 'Anne has a BA degree in English from St. Lawrence College.  She is fluent in French and German.', 5, 'http://accweb/emmployees/davolio.bmp' );

/* Data insertion: Shippers */

INSERT INTO Shippers VALUES (1, 'Speedy Express', '(503) 555-9831');
INSERT INTO Shippers VALUES (2, 'United Package', '(503) 555-3199');
INSERT INTO Shippers VALUES (3, 'Federal Shipping', '(503) 555-9931');

/* Data insertion: Orders */

INSERT INTO Orders VALUES (10643, 'ALFKI', 6, '1997-08-25', '1997-09-22', '1997-09-02', 1, 29.46, 'Alfreds Futterkiste', 'Obere Str. 57', 'Berlin', null, 12209, 'Germany');
INSERT INTO Orders VALUES (10692, 'ALFKI', 4, '1997-10-03', '1997-10-31', '1997-10-13', 2, 61.02, 'Alfreds Futterkiste', 'Obere Str. 57', 'Berlin', null, 12209, 'Germany');
INSERT INTO Orders VALUES (10702, 'ALFKI', 4, '1997-10-13', '1997-11-24', '1997-10-21', 1, 23.94, 'Alfreds Futterkiste', 'Obere Str. 57', 'Berlin', null, 12209, 'Germany');
INSERT INTO Orders VALUES (10835, 'ALFKI', 1, '1998-01-15', '1998-02-12', '1998-01-21', 3, 69.53, 'Alfreds Futterkiste', 'Obere Str. 57', 'Berlin', null, 12209, 'Germany');
INSERT INTO Orders VALUES (10952, 'ALFKI', 1, '1998-03-16', '1998-04-27', '1998-03-24', 1, 40.42, 'Alfreds Futterkiste', 'Obere Str. 57', 'Berlin', null, 12209, 'Germany');
INSERT INTO Orders VALUES (11011, 'ALFKI', 3, '1998-04-09', '1998-05-07', '1998-04-13', 1, 1.21, 'Alfreds Futterkiste', 'Obere Str. 57', 'Berlin', null, 12209, 'Germany');