mcs/class/System.Data.Linq/examples/DbLinq.Ora.Example/sql/create_Northwind_ora.sql

   1 --####################################################################\r
   2 -- Script to create Oracle version of the Northwind test DB\r
   3 --\r
   4 -- this script was tested on Oracle XE - so it does not contain any 'CREATE DATABASE' statements.\r
   5 --####################################################################\r
   6 CREATE TABLE "Region" (\r
   7   "RegionID" INTEGER NOT NULL,\r
   8   "RegionDescription" VARCHAR(50) NOT NULL,\r
   9   PRIMARY KEY("RegionID")\r
  10 );\r
  11 \r
  12 CREATE TABLE "Territories" (\r
  13   "TerritoryID" VARCHAR(20) NOT NULL,\r
  14   "TerritoryDescription" VARCHAR(50) NOT NULL,\r
  15   "RegionID" INTEGER NOT NULL,\r
  16   PRIMARY KEY("TerritoryID"),\r
  17   FOREIGN KEY ("RegionID") REFERENCES "Region" ("RegionID")\r
  18 );\r
  19 \r
  20 --####################################################################\r
  21 CREATE TABLE "Categories" (\r
  22   "CategoryID" INTEGER  NOT NULL,\r
  23   "CategoryName" VARCHAR(15) NOT NULL,\r
  24   "Description" VARCHAR(500) NULL,\r
  25   "Picture" BLOB NULL,\r
  26   PRIMARY KEY("CategoryID")\r
  27 );\r
  28 \r
  29 CREATE TABLE "Suppliers" (\r
  30   "SupplierID" INTEGER  NOT NULL,\r
  31   "CompanyName" VARCHAR(40) NOT NULL,\r
  32   "ContactName" VARCHAR(30) NULL,\r
  33   "ContactTitle" VARCHAR(30) NULL,\r
  34   "Address" VARCHAR(60) NULL,\r
  35   "City" VARCHAR(15) NULL,\r
  36   "Region" VARCHAR(15) NULL,\r
  37   "PostalCode" VARCHAR(10) NULL,\r
  38   "Country" VARCHAR(15) NULL,\r
  39   "Phone" VARCHAR(24) NULL,\r
  40   "Fax" VARCHAR(24) NULL,\r
  41   PRIMARY KEY("SupplierID")\r
  42 );\r
  43 --####################################################################\r
  44 \r
  45 CREATE TABLE "Products" (\r
  46   "ProductID" INTEGER NOT NULL,\r
  47   "ProductName" VARCHAR(40) NOT NULL,\r
  48   "SupplierID" INTEGER NULL,\r
  49   "CategoryID" INTEGER NULL,\r
  50   "QuantityPerUnit" VARCHAR(20) NULL,\r
  51   "UnitPrice" DECIMAL NULL,\r
  52   "UnitsInStock" SMALLINT NULL,\r
  53   "UnitsOnOrder" SMALLINT NULL,\r
  54   "ReorderLevel" SMALLINT NULL,\r
  55   "Discontinued" NUMBER(1) NOT NULL, --'bool' field\r
  56   PRIMARY KEY("ProductID"),\r
  57   FOREIGN KEY ("CategoryID") REFERENCES "Categories" ("CategoryID"),\r
  58   FOREIGN KEY ("SupplierID") REFERENCES "Suppliers" ("SupplierID")\r
  59 );\r
  60   \r
  61 CREATE TABLE "Customers" (\r
  62   "CustomerID" VARCHAR(5) NOT NULL,\r
  63   "CompanyName" VARCHAR(40) NOT NULL,\r
  64   "ContactName" VARCHAR(30) NULL,\r
  65   "ContactTitle" VARCHAR(30) NULL,\r
  66   "Address" VARCHAR(60) NULL,\r
  67   "City" VARCHAR(15) NULL,\r
  68   "Region" VARCHAR(15) NULL,\r
  69   "PostalCode" VARCHAR(10) NULL,\r
  70   "Country" VARCHAR(15) NULL,\r
  71   "Phone" VARCHAR(24) NULL,\r
  72   "Fax" VARCHAR(24) NULL,\r
  73   PRIMARY KEY("CustomerID")\r
  74 );\r
  75 \r
  76 --####################################################################\r
  77 CREATE TABLE "Employees" (\r
  78   "EmployeeID" INTEGER NOT NULL,\r
  79   "LastName" VARCHAR(20) NOT NULL,\r
  80   "FirstName" VARCHAR(10) NOT NULL,\r
  81   "Title" VARCHAR(30) NULL,\r
  82   "BirthDate" DATE NULL,\r
  83   "HireDate" DATE NULL,\r
  84   "Address" VARCHAR(60) NULL,\r
  85   "City" VARCHAR(15) NULL,\r
  86   "Region" VARCHAR(15) NULL,\r
  87   "PostalCode" VARCHAR(10) NULL,\r
  88   "Country" VARCHAR(15) NULL,\r
  89   "HomePhone" VARCHAR(24) NULL,\r
  90   "Photo" BLOB NULL,\r
  91   "Notes" VARCHAR(100) NULL,\r
  92   "TitleOfCourtesy" VARCHAR(25) NULL,\r
  93   "PhotoPath" VARCHAR (255) NULL,\r
  94   "Extension" VARCHAR(5) NULL,\r
  95   "ReportsTo" INTEGER NULL,\r
  96   PRIMARY KEY("EmployeeID"),\r
  97   FOREIGN KEY ("ReportsTo")  REFERENCES "Employees" ("EmployeeID")\r
  98 );\r
  99 \r
 100 CREATE TABLE "EmployeeTerritories" (\r
 101   "EmployeeID" INTEGER NOT NULL,\r
 102   "TerritoryID" VARCHAR(20) NOT NULL,\r
 103   PRIMARY KEY("EmployeeID","TerritoryID"),\r
 104   FOREIGN KEY ("EmployeeID") REFERENCES "Employees" ("EmployeeID"),\r
 105   FOREIGN KEY ("TerritoryID") REFERENCES "Territories" ("TerritoryID")\r
 106 );\r
 107 \r
 108 --####################################################################\r
 109 CREATE TABLE "Orders" (\r
 110   "OrderID" INTEGER NOT NULL,\r
 111   "CustomerID" VARCHAR(5) NULL,\r
 112   "EmployeeID" INTEGER NULL,\r
 113   "OrderDate" DATE NULL,\r
 114   "RequiredDate" DATE NULL,\r
 115   "ShippedDate" DATE NULL,\r
 116   "ShipVia" INT NULL,\r
 117   "Freight" DECIMAL NULL,\r
 118   "ShipName" VARCHAR(40) NULL,\r
 119   "ShipAddress" VARCHAR(60) NULL,\r
 120   "ShipCity" VARCHAR(15) NULL,\r
 121   "ShipRegion" VARCHAR(15) NULL,\r
 122   "ShipPostalCode" VARCHAR(10) NULL,\r
 123   "ShipCountry" VARCHAR(15) NULL,\r
 124   PRIMARY KEY("OrderID"),\r
 125   FOREIGN KEY ("CustomerID") REFERENCES "Customers" ("CustomerID"),\r
 126   FOREIGN KEY ("EmployeeID") REFERENCES "Employees" ("EmployeeID")\r
 127 );\r
 128 \r
 129 --####################################################################\r
 130 CREATE TABLE "OrderDetails" (\r
 131   "OrderID" INTEGER NOT NULL,\r
 132   "ProductID" INTEGER NOT NULL,\r
 133   "UnitPrice" DECIMAL NOT NULL,\r
 134   "Quantity" SMALLINT NOT NULL,\r
 135   "Discount" FLOAT NOT NULL,\r
 136   PRIMARY KEY("OrderID","ProductID"),\r
 137   FOREIGN KEY ("OrderID") REFERENCES "Orders" ("OrderID"),\r
 138   FOREIGN KEY ("ProductID") REFERENCES "Products" ("ProductID")\r
 139 );\r
 140 \r
 141 --####################################################################\r
 142 CREATE SEQUENCE Region_seq      START WITH 1    INCREMENT BY 1;\r
 143 CREATE SEQUENCE Categories_seq  START WITH 1    INCREMENT BY 1;\r
 144 CREATE SEQUENCE Suppliers_seq   START WITH 1    INCREMENT BY 1;\r
 145 CREATE SEQUENCE Products_seq    START WITH 1    INCREMENT BY 1;\r
 146 CREATE SEQUENCE Orders_seq      START WITH 1    INCREMENT BY 1;\r
 147 CREATE SEQUENCE Employees_seq   START WITH 1    INCREMENT BY 1;\r
 148 CREATE SEQUENCE Territories_seq START WITH 1    INCREMENT BY 1;\r
 149  \r
 150 --####################################################################\r
 151 \r
 152 CREATE OR REPLACE TRIGGER Employees_Trigger\r
 153 BEFORE INSERT ON "Employees" \r
 154 FOR EACH ROW\r
 155 BEGIN\r
 156    IF (:new."EmployeeID" IS NULL) THEN\r
 157         SELECT Employees_seq.NEXTVAL INTO :new."EmployeeID" FROM DUAL;\r
 158    END IF;\r
 159 END;\r
 160 /\r
 161 \r
 162 CREATE OR REPLACE TRIGGER Products_Trigger\r
 163 BEFORE INSERT ON "Products" \r
 164 FOR EACH ROW\r
 165 BEGIN\r
 166    IF (:new."ProductID" IS NULL) THEN\r
 167         SELECT Products_seq.NEXTVAL INTO :new."ProductID" FROM DUAL;\r
 168    END IF;\r
 169 END;\r
 170 /\r
 171 \r
 172 CREATE OR REPLACE TRIGGER Categories_Trigger\r
 173 BEFORE INSERT ON "Categories" \r
 174 FOR EACH ROW\r
 175 BEGIN\r
 176    IF (:new."CategoryID" IS NULL) THEN\r
 177         SELECT Categories_seq.NEXTVAL INTO :new."CategoryID" FROM DUAL;\r
 178    END IF;\r
 179 END;\r
 180 /\r
 181 \r
 182 CREATE OR REPLACE TRIGGER Region_Trigger\r
 183 BEFORE INSERT ON "Region" \r
 184 FOR EACH ROW\r
 185 BEGIN\r
 186    IF (:new."RegionID" IS NULL) THEN\r
 187         SELECT Region_seq.NEXTVAL INTO :new."RegionID" FROM DUAL;\r
 188    END IF;\r
 189 END;\r
 190 /\r
 191 \r
 192 CREATE OR REPLACE TRIGGER Suppliers_Trigger\r
 193 BEFORE INSERT ON "Suppliers" \r
 194 FOR EACH ROW\r
 195 BEGIN\r
 196    IF (:new."SupplierID" IS NULL) THEN\r
 197         SELECT Suppliers_seq.NEXTVAL INTO :new."SupplierID" FROM DUAL;\r
 198    END IF;\r
 199 END;\r
 200 /\r
 201 \r
 202 CREATE OR REPLACE TRIGGER Orders_Trigger\r
 203 BEFORE INSERT ON "Orders" \r
 204 FOR EACH ROW\r
 205 BEGIN\r
 206    IF (:new."OrderID" IS NULL) THEN\r
 207         SELECT Orders_seq.NEXTVAL INTO :new."OrderID" FROM DUAL;\r
 208    END IF;\r
 209 END;\r
 210 /\r
 211 \r
 212 CREATE OR REPLACE TRIGGER Territories_Trigger\r
 213 BEFORE INSERT ON "Territories" \r
 214 FOR EACH ROW\r
 215 BEGIN\r
 216    IF (:new."TerritoryID" IS NULL) THEN\r
 217         SELECT Territories_seq.NEXTVAL INTO :new."TerritoryID" FROM DUAL;\r
 218    END IF;\r
 219 END;\r
 220 /\r
 221 \r
 222 --####################################################################\r
 223 Insert INTO "Categories" ("CategoryID", "CategoryName","Description")\r
 224 values (Categories_seq.NextVal, 'Beverages',    'Soft drinks, coffees, teas, beers, and ales');\r
 225 Insert INTO "Categories" ("CategoryID", "CategoryName","Description")\r
 226 values (Categories_seq.NextVal, 'Condiments','Sweet and savory sauces, relishes, spreads, and seasonings');\r
 227 Insert INTO "Categories" ("CategoryID", "CategoryName","Description")\r
 228 values (Categories_seq.NextVal, 'Seafood','Seaweed and fish');\r
 229 \r
 230 --####################################################################\r
 231 INSERT INTO "Region" ("RegionDescription") VALUES ('North America');\r
 232 INSERT INTO "Region" ("RegionDescription") VALUES ('Europe');\r
 233 \r
 234 --####################################################################\r
 235 INSERT INTO "Territories" ("TerritoryID", "TerritoryDescription", "RegionID") VALUES ('US.Northwest', 'Northwest', 1);\r
 236 \r
 237 --####################################################################\r
 238 insert INTO "Customers" ("CustomerID", "CompanyName","ContactName","Country","PostalCode","City")\r
 239 values ('AIRBU', 'airbus','jacques','France','10000','Paris');\r
 240 insert INTO "Customers" ("CustomerID", "CompanyName","ContactName","Country","PostalCode","City")\r
 241 values ('BT___','BT','graeme','U.K.','E14','London');\r
 242 \r
 243 insert INTO "Customers" ("CustomerID", "CompanyName","ContactName","Country","PostalCode","City")\r
 244 values ('ATT__','ATT','bob','USA','10021','New York');\r
 245 insert INTO "Customers" ("CustomerID", "CompanyName","ContactName","Country","PostalCode","City")\r
 246 values ('UKMOD', 'MOD','(secret)','U.K.','E14','London');\r
 247 \r
 248 insert INTO "Customers" ("CustomerID", "CompanyName","ContactName", "ContactTitle", "Country","PostalCode","City", "Phone")\r
 249 values ('ALFKI', 'Alfreds Futterkiste','Maria Anders','Sales Representative','Germany','12209','Berlin','030-0074321');\r
 250 \r
 251 insert INTO "Customers" ("CustomerID", "CompanyName","ContactName", "ContactTitle", "Country", "PostalCode", "Address", "City", "Phone", "Fax")\r
 252 values ('BONAP', 'Bon app''','Laurence Lebihan','Owner','France','13008','12, rue des Bouchers','Marseille','91.24.45.40', '91.24.45.41');\r
 253 \r
 254 insert INTO "Customers" ("CustomerID", "CompanyName","ContactName", "ContactTitle", "Country","PostalCode","City", "Phone")\r
 255 values ('WARTH', 'Wartian Herkku','Pirkko Koskitalo','Accounting Manager','Finland','90110','Oulu','981-443655');\r
 256 \r
 257 --####################################################################\r
 258 insert INTO "Suppliers" ("SupplierID", "CompanyName", "ContactName", "ContactTitle", "Address", "City", "Region", "Country")\r
 259 VALUES (Suppliers_seq.Nextval, 'alles AG', 'Harald Reitmeyer', 'Prof', 'Fischergasse 8', 'Heidelberg', 'B-W', 'Germany');\r
 260 \r
 261 insert INTO "Suppliers" ("SupplierID", "CompanyName", "ContactName", "ContactTitle", "Address", "City", "Region", "Country")\r
 262 VALUES (Suppliers_seq.Nextval, 'Microsoft', 'Mr Allen', 'Monopolist', '1 MS', 'Redmond', 'WA', 'USA');\r
 263 \r
 264 INSERT INTO "Suppliers" ("SupplierID", "CompanyName", "ContactName", "ContactTitle", "Address", "City", "Region", "PostalCode", "Country", "Phone", "Fax")\r
 265 VALUES (Suppliers_seq.Nextval, 'Pavlova, Ltd.', 'Ian Devling', 'Marketing Manager', '74 Rose St. Moonie Ponds', 'Melbourne', 'Victoria', '3058', 'Australia', '(03) 444-2343', '(03) 444-6588');\r
 266 \r
 267 \r
 268 insert INTO "Products" ("ProductID", "ProductName","SupplierID", "QuantityPerUnit","UnitsInStock","UnitsOnOrder","Discontinued")\r
 269 VALUES (Products_seq.nextval, 'Pen',1, 10,     12, 2,  0);\r
 270 insert INTO "Products" ("ProductID", "ProductName","SupplierID", "QuantityPerUnit","UnitsInStock","UnitsOnOrder","Discontinued")\r
 271 VALUES (Products_seq.nextval, 'Bicycle',1, 1,  6, 0,  0);\r
 272 insert INTO "Products" ("ProductID", "ProductName","QuantityPerUnit","UnitsInStock","UnitsOnOrder","Discontinued")\r
 273 VALUES (Products_seq.nextval, 'Phone',3,    7, 0,  0);\r
 274 insert INTO "Products" ("ProductID", "ProductName","QuantityPerUnit","UnitsInStock","UnitsOnOrder","Discontinued")\r
 275 VALUES (Products_seq.nextval, 'SAM',1,      51, 11, 0);\r
 276 insert INTO "Products" ("ProductID", "ProductName","QuantityPerUnit","UnitsInStock","UnitsOnOrder","Discontinued")\r
 277 VALUES (Products_seq.nextval, 'iPod',0,     11, 0, 0);\r
 278 insert INTO "Products" ("ProductID", "ProductName","QuantityPerUnit","UnitsInStock","UnitsOnOrder","Discontinued")\r
 279 VALUES (Products_seq.nextval, 'Toilet Paper',2,  0, 3, 1);\r
 280 insert INTO "Products" ("ProductID", "ProductName","QuantityPerUnit","UnitsInStock","UnitsOnOrder","Discontinued")\r
 281 VALUES (Products_seq.nextval, 'Fork',5,   111, 0, 0);\r
 282 insert INTO "Products" ("ProductID", "ProductName","SupplierID", "QuantityPerUnit","UnitsInStock","UnitsOnOrder","Discontinued")\r
 283 VALUES (Products_seq.nextval, 'Linq Book',2, 1, 0, 26, 0);\r
 284 INSERT INTO "Products" ("ProductID", "ProductName","SupplierID", "QuantityPerUnit","UnitPrice",  "UnitsInStock","UnitsOnOrder","Discontinued")\r
 285 VALUES (Products_seq.nextval, 'Carnarvon Tigers', 3,'16 kg pkg.',62.50,  42, 0, 0);\r
 286 \r
 287 --####################################################################\r
 288 insert INTO "Employees" ("EmployeeID", "LastName","FirstName","Title","BirthDate","HireDate","Address","City","ReportsTo","Country","HomePhone")\r
 289 VALUES (Employees_seq.nextval, 'Fuller','Andrew','Vice President, Sales',to_date('01-01-1964','dd-mm-yyyy'),to_date('01-01-1989','dd-mm-yyyy'), '908 W. Capital Way','Tacoma',NULL,'USA','(111)222333');\r
 290 \r
 291 insert INTO "Employees" ("EmployeeID", "LastName","FirstName","Title","BirthDate","HireDate","Address","City","ReportsTo","Country","HomePhone")\r
 292 VALUES (Employees_seq.nextval, 'Davolio','Nancy','Sales Representative',to_date('01-01-1964','dd-mm-yyyy'),to_date('01-01-1994','dd-mm-yyyy'),'507 - 20th Ave. E.  Apt. 2A','Seattle',1,'USA','(444)555666');\r
 293 \r
 294 insert INTO "Employees" ("EmployeeID", "LastName","FirstName","Title","BirthDate","HireDate","Address","City","ReportsTo","Country","HomePhone")\r
 295 VALUES (Employees_seq.nextval, 'Builder','Bob','Handyman',to_date('01-01-1964','dd-mm-yyyy'),to_date('01-01-1964','dd-mm-yyyy'),'666 dark street','Seattle',2,'USA','(777)888999');\r
 296 \r
 297 --####################################################################\r
 298 INSERT INTO "EmployeeTerritories" ("EmployeeID", "TerritoryID") VALUES (2, 'US.Northwest');\r
 299 \r
 300 \r
 301 --####################################################################\r
 302 --truncate table Orders;\r
 303 --\r
 304 insert INTO "Orders" ("OrderID", "CustomerID", "EmployeeID", "OrderDate", "Freight")\r
 305 Values (Orders_seq.NextVal, 'AIRBU', 1, sysdate, 21.3);\r
 306 \r
 307 insert INTO "Orders" ("OrderID", "CustomerID", "EmployeeID", "OrderDate", "Freight")\r
 308 Values (Orders_seq.NextVal, 'BT___', 1, sysdate, 11.1);\r
 309 \r
 310 insert INTO "Orders" ("OrderID", "CustomerID", "EmployeeID", "OrderDate", "Freight")\r
 311 Values (Orders_seq.NextVal, 'BT___', 1, sysdate, 11.5);\r
 312 \r
 313 insert INTO "Orders" ("OrderID", "CustomerID", "EmployeeID", "OrderDate", "Freight")\r
 314 Values (Orders_seq.NextVal, 'UKMOD', 1, sysdate, 32.5);\r
 315 \r
 316 insert INTO "Orders" ("OrderID", "CustomerID", "EmployeeID", "OrderDate", "RequiredDate", "ShippedDate", "Freight", "ShipName", "ShipAddress", "ShipCity", "ShipCountry")\r
 317 Values (Orders_seq.NextVal, 'BONAP', 1, to_date('1996-10-16', 'yyyy-mm-dd'), to_date('1996-11-27', 'yyyy-mm-dd'), to_date('1996-10-21', 'yyyy-mm-dd'), 10.21, 'Bon app''', '12, rue des Bouchers', 'Marseille', 'France' );\r
 318 \r
 319 INSERT INTO "OrderDetails" ("OrderID", "ProductID", "UnitPrice", "Quantity", "Discount")\r
 320 VALUES (1,2, 33, 5, 11);\r
 321 \r
 322 INSERT INTO "OrderDetails" ("OrderID", "ProductID", "UnitPrice", "Quantity",   "Discount")\r
 323 VALUES (5,9, 50, 20,   0.05); --## CanarvonTigers for customer BONAP\r
 324 \r
 325 \r
 326 --####################################################################\r
 327 \r
 328 CREATE OR REPLACE FUNCTION NORTHWIND.HELLO0\r
 329 RETURN varchar\r
 330   IS\r
 331 BEGIN\r
 332   return 'hello0';\r
 333 END;\r
 334 /\r
 335 \r
 336 CREATE OR REPLACE FUNCTION NORTHWIND.HELLO1\r
 337 (s varchar)\r
 338 RETURN varchar\r
 339   IS\r
 340 BEGIN\r
 341   return 'Hello, ' || s || '!';\r
 342 END;\r
 343 /\r
 344 \r
 345 CREATE OR REPLACE FUNCTION NORTHWIND.HELLO2\r
 346 (s varchar, s2 number)\r
 347 RETURN varchar\r
 348   IS\r
 349 BEGIN\r
 350   return 'Hello, ' || s || '!';\r
 351 END;\r
 352 /\r
 353 \r
 354 CREATE OR REPLACE FUNCTION NORTHWIND.GETORDERCOUNT\r
 355 (custId varchar)\r
 356 RETURN number\r
 357   IS\r
 358 count1 number;\r
 359 BEGIN\r
 360 SELECT COUNT(*) INTO count1 FROM "Orders" WHERE "CustomerID"=custId;\r
 361 RETURN count1;\r
 362 END;\r
 363 /\r
 364 \r
 365 CREATE OR REPLACE PROCEDURE NORTHWIND.SP_SELORDERS\r
 366 (s varchar, s2 out number)\r
 367   IS    \r
 368 BEGIN\r
 369 select 22 into s2 from dual;\r
 370 END;\r
 371 /\r
 372 \r
 373 COMMIT;\r
 374 \r
 375 EXIT\r
 376 \r