session.log

root@:26257/> create database test;
CREATE DATABASE
root@:26257/> set database=test;
SET
root@:26257/test> CREATE TABLE "Employees" ("EmployeeID" SERIAL, "LastName" VARCHAR(20) NOT NULL, "FirstName" VARCHAR(10) NOT NULL, "Title" VARCHAR(30) NULL, "TitleOfCourtesy" VARCHAR(25) NULL, "BirthDate" TIMESTAMP NULL, "HireDate" TIMESTAMP NULL, "Address" VARCHAR(60) NULL, "City" VARCHAR(15) NULL, "Region" VARCHAR(15) NULL, "PostalCode" VARCHAR(10) NULL, "Country" VARCHAR(15) NULL, "HomePhone" VARCHAR(24) NULL, "Extension" VARCHAR(4) NULL, "Photo" BYTEA NULL, "Notes" TEXT NULL, "ReportsTo" INT NULL, "PhotoPath" VARCHAR(255) NULL, CONSTRAINT "PK_Employees" PRIMARY KEY ("EmployeeID"), CONSTRAINT "FK_Employees_Employees" FOREIGN KEY ("ReportsTo") REFERENCES "Employees" ("EmployeeID"), CONSTRAINT "CK_Birthdate" CHECK ("BirthDate" < current_date()));
CREATE TABLE
root@:26257/test> CREATE TABLE "Categories" ("CategoryID" SERIAL, "CategoryName" VARCHAR(15) NOT NULL, "Description" TEXT NULL, "Picture" BYTEA NULL, CONSTRAINT "PK_Categories" PRIMARY KEY ("CategoryID"));
CREATE TABLE
root@:26257/test> CREATE TABLE "Customers" ("CustomerID" VARCHAR(5) NOT NULL, "CompanyName" VARCHAR(40) NOT NULL, "ContactName" VARCHAR(30) NULL, "ContactTitle" VARCHAR(30) NULL, "Address" VARCHAR(60) NULL, "City" VARCHAR(15) NULL, "Region" VARCHAR(15) NULL, "PostalCode" VARCHAR(10) NULL, "Country" VARCHAR(15) NULL, "Phone" VARCHAR(24) NULL, "Fax" VARCHAR(24) NULL, CONSTRAINT "PK_Customers" PRIMARY KEY ("CustomerID"));
CREATE TABLE
root@:26257/test> CREATE TABLE "Shippers" ("ShipperID" SERIAL, "CompanyName" VARCHAR(40) NOT NULL, "Phone" VARCHAR(24) NULL, CONSTRAINT "PK_Shippers" PRIMARY KEY ("ShipperID"));
CREATE TABLE
root@:26257/test> CREATE TABLE "Suppliers" ("SupplierID" SERIAL, "CompanyName" VARCHAR(40) NOT NULL, "ContactName" VARCHAR(30) NULL, "ContactTitle" VARCHAR(30) NULL, "Address" VARCHAR(60) NULL, "City" VARCHAR(15) NULL, "Region" VARCHAR(15) NULL, "PostalCode" VARCHAR(10) NULL, "Country" VARCHAR(15) NULL, "Phone" VARCHAR(24) NULL, "Fax" VARCHAR(24) NULL, "HomePage" TEXT NULL, CONSTRAINT "PK_Suppliers" PRIMARY KEY ("SupplierID"));
CREATE TABLE
root@:26257/test> CREATE TABLE "Orders" ("OrderID" SERIAL, "CustomerID" VARCHAR(5) NULL, "EmployeeID" INTEGER NULL, "OrderDate" TIMESTAMP NULL, "RequiredDate" TIMESTAMP NULL, "ShippedDate" TIMESTAMP NULL, "ShipVia" INTEGER NULL, "Freight" NUMERIC(12,2) NULL CONSTRAINT "DF_Orders_Freight" DEFAULT 0, "ShipName" VARCHAR(40) NULL, "ShipAddress" VARCHAR(60) NULL, "ShipCity" VARCHAR(15) NULL, "ShipRegion" VARCHAR(15) NULL, "ShipPostalCode" VARCHAR(10) NULL, "ShipCountry" VARCHAR(15) NULL, 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"));
CREATE TABLE
root@:26257/test> CREATE TABLE "Products" ("ProductID" SERIAL, "ProductName" VARCHAR(40) NOT NULL, "SupplierID" INTEGER NULL, "CategoryID" INTEGER NULL, "QuantityPerUnit" VARCHAR(20) NULL, "UnitPrice" NUMERIC(12,2) NULL CONSTRAINT "DF_Products_UnitPrice" DEFAULT 0, "UnitsInStock" SMALLINT NULL CONSTRAINT "DF_Products_UnitsInStock" DEFAULT 0, "UnitsOnOrder" SMALLINT NULL CONSTRAINT "DF_Products_UnitsOnOrder" DEFAULT 0, "ReorderLevel" SMALLINT NULL CONSTRAINT "DF_Products_ReorderLevel" DEFAULT 0, "Discontinued" BOOLEAN NOT NULL CONSTRAINT "DF_Products_Discontinued" DEFAULT false, CONSTRAINT "PK_Products" PRIMARY KEY ("ProductID"), CONSTRAINT "FK_Products_Categories" FOREIGN KEY ("CategoryID") REFERENCES "Categories" ("CategoryID"), CONSTRAINT "FK_Products_Suppliers" FOREIGN KEY ("SupplierID") REFERENCES "Suppliers" ("SupplierID"), 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));
CREATE TABLE
root@:26257/test> CREATE TABLE "Order Details" ("OrderID" INTEGER NOT NULL, "ProductID" INTEGER NOT NULL, "UnitPrice" NUMERIC(12,2) NOT NULL CONSTRAINT "DF_Order_Details_UnitPrice" DEFAULT 0, "Quantity" SMALLINT NOT NULL CONSTRAINT "DF_Order_Details_Quantity" DEFAULT 1, "Discount" REAL NOT NULL CONSTRAINT "DF_Order_Details_Discount" DEFAULT 0.0, CONSTRAINT "PK_Order_Details" PRIMARY KEY ("OrderID", "ProductID"), CONSTRAINT "FK_Order_Details_Orders" FOREIGN KEY ("OrderID") REFERENCES "Orders" ("OrderID"), CONSTRAINT "FK_Order_Details_Products" FOREIGN KEY ("ProductID") REFERENCES "Products" ("ProductID"), CONSTRAINT "CK_Discount" CHECK (("Discount" >= 0) AND ("Discount" <= 1)), CONSTRAINT "CK_Quantity" CHECK ("Quantity" > 0), CONSTRAINT "CK_UnitPrice" CHECK ("UnitPrice" >= 0));
CREATE TABLE
root@:26257/test> CREATE INDEX ON "Employees" ("LastName");
CREATE INDEX
root@:26257/test> CREATE INDEX ON "Employees" ("PostalCode");
CREATE INDEX
root@:26257/test> CREATE INDEX ON "Categories" ("CategoryName");
CREATE INDEX
root@:26257/test> CREATE INDEX ON "Customers" ("City");
CREATE INDEX
root@:26257/test> CREATE INDEX ON "Customers" ("CompanyName");
CREATE INDEX
root@:26257/test> CREATE INDEX ON "Customers" ("PostalCode");
CREATE INDEX
root@:26257/test> CREATE INDEX ON "Customers" ("Region");
CREATE INDEX
root@:26257/test> CREATE INDEX ON "Suppliers" ("CompanyName");
CREATE INDEX
root@:26257/test> CREATE INDEX ON "Suppliers" ("PostalCode");
CREATE INDEX
root@:26257/test> CREATE INDEX ON "Orders" ("CustomerID");
CREATE INDEX
root@:26257/test> CREATE INDEX ON "Orders" ("EmployeeID");
CREATE INDEX
root@:26257/test> CREATE INDEX ON "Orders" ("OrderDate");
CREATE INDEX
root@:26257/test> CREATE INDEX ON "Orders" ("ShippedDate");
CREATE INDEX
root@:26257/test> CREATE INDEX ON "Orders" ("ShipVia");
CREATE INDEX
root@:26257/test> CREATE INDEX ON "Orders" ("ShipPostalCode");
CREATE INDEX
root@:26257/test> CREATE INDEX ON "Products" ("CategoryID");
CREATE INDEX
root@:26257/test> CREATE INDEX ON "Products" ("ProductName");
CREATE INDEX
root@:26257/test> CREATE INDEX ON "Products" ("SupplierID");
CREATE INDEX
root@:26257/test> CREATE INDEX ON "Order Details" ("OrderID");
CREATE INDEX
root@:26257/test> CREATE INDEX ON "Order Details" ("ProductID");
CREATE INDEX
root@:26257/test> CREATE VIEW "Customer and Suppliers by City" AS SELECT "City", "CompanyName", "ContactName", 'Customers' AS "Relationship" FROM "Customers" UNION SELECT "City", "CompanyName", "ContactName", 'Suppliers' FROM "Suppliers";
CREATE VIEW
root@:26257/test> CREATE VIEW "Current Product List" AS SELECT "Product_List"."ProductID", "Product_List"."ProductName" FROM "Products" AS "Product_List" WHERE ((("Product_List"."Discontinued") = false));
CREATE VIEW
root@:26257/test> 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";
CREATE VIEW
root@:26257/test> CREATE VIEW "Products Above Average Price" AS SELECT "Products"."ProductName", "Products"."UnitPrice" FROM "Products" WHERE "Products"."UnitPrice"::NUMERIC > (SELECT avg("UnitPrice"::NUMERIC) FROM "Products");
CREATE VIEW