Quelle sample.sql Sprache: SQL

-- Licensed to the Apache Software Foundation (ASF) under one
-- or more contributor license agreements.  See the NOTICE file
-- distributed with this work for additional information
-- regarding copyright ownership.  The ASF licenses this file
-- to you under the Apache License, Version 2.0 (the
-- "License"); you may not use this file except in compliance
-- with the License.  You may obtain a copy of the License at
--
--   http://www.apache.org/licenses/LICENSE-2.0
--
-- Unless required by applicable law or agreed to in writing,
-- software distributed under the License is distributed on an
-- "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
-- KIND, either express or implied.  See the License for the
-- specific language governing permissions and limitations
-- under the License.
--
-- Script to create the Java DB sample database. Run in the db/external directory using:
--
-- java -cp $DERBY_HOME/lib/derbytools.jar:$DERBY_HOME/lib/derby.jar org.apache.derby.tools.ij ../derby/etc/sample.sql

connect 'jdbc:derby:sample;create=true';

-- CREATE SCHEMA SAMPLE;

CALL SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.connection.requireAuthentication', 'true');
CALL SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.authentication.provider', 'BUILTIN');
CALL SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.user.app', 'app');

disconnect;

-- in order to set the connection's default schema,
-- so we don't have to qualify the table and view names

connect 'jdbc:derby:sample' user 'app' password 'app';

CREATE TABLE CUSTOMER (
   CUSTOMER_ID INTEGER PRIMARY KEY NOT NULL,
   DISCOUNT_CODE CHARACTER(1) NOT NULL,
   ZIP VARCHAR(10) NOT NULL,
   "NAME" VARCHAR(30),
   ADDRESSLINE1 VARCHAR(30),
   ADDRESSLINE2 VARCHAR(30),
   CITY VARCHAR(25),
   STATE CHARACTER(2),
   PHONE CHARACTER(12),
   FAX CHARACTER(12),
   EMAIL VARCHAR(40),
   CREDIT_LIMIT INTEGER ) ;

INSERT INTO CUSTOMER
values(
1,'N','95117','Jumbo Eagle Corp','111 E. Las Olivas Blvd','Suite 51','Fort Lauderdale','FL','305-555-0188','305-555-0189','jumboeagle@example.com',100000
);
INSERT INTO CUSTOMER
values(

2,'M','95035','New Enterprises','9754 Main Street','P.O. Box 567','Miami','FL','305-555-0148','305-555-0149','www.new.example.com',50000
);
INSERT INTO CUSTOMER
values(
25,'M','85638','Wren Computers','8989 Red Albatross Drive','Suite 9897','Houston','TX','214-555-0133','214-555-0134','www.wrencomp.example.com',25000
);
INSERT INTO CUSTOMER
values(
3,'L','12347','Small Bill Company','8585 South Upper Murray Drive','P.O. Box 456','Alanta','GA','555-555-0175','555-555-0176','www.smallbill.example.com',90000
);
INSERT INTO CUSTOMER
values(
36,'H','94401','Bob Hosting Corp.','65653 Lake Road','Suite 2323','San Mateo','CA','650-555-0160','650-555-0161','www.bobhostcorp.example.com',65000
);
INSERT INTO CUSTOMER
values(
106,'L','95035','Early CentralComp','829 E Flex Drive','Suite 853','San Jose','CA','408-555-0157','408-555-0150','www.centralcomp.example.com',26500
);
INSERT INTO CUSTOMER
values(
149,'L','95117','John Valley Computers','4381 Kelly Valley Ave','Suite 77','Santa Clara','CA','408-555-0169','408-555-0167','www.johnvalley.example.com',70000
);
INSERT INTO CUSTOMER
values(
863,'N','94401','Big Network Systems','456 444th Street','Suite 45','Redwood City','CA','650-555-0181','650-555-0180','www.bignet.example.com',25000
);
INSERT INTO CUSTOMER
values(
777,'L','48128','West Valley Inc.','88 Northsouth Drive','Building C','Dearborn','MI','313-555-0172','313-555-0171','www.westv.example.com',100000
);
INSERT INTO CUSTOMER
values(
753,'H','48128','Zed Motor Co','2267 NE Michigan Ave','Building 21','Dearborn','MI','313-555-0151','313-555-0152','www.parts@ford.example.com',5000000
);
INSERT INTO CUSTOMER
values(
722,'N','48124','Big Car Parts','52963 Notouter Dr','Suite 35','Detroit','MI','313-555-0144','313-555-0145','www.bparts.example.com',50000
);
INSERT INTO CUSTOMER
values(
409,'L','10095','Old Media Productions','4400 527th Street','Suite 562','New York','NY','212-555-0110','212-555-0111','www.oldmedia.example.com',10000
);
INSERT INTO CUSTOMER
values(
410,'M','10096','Yankee Computer Repair Ltd','9653 211th Ave','Floor 4','New York','NY','212-555-0191','212-555-0197','www.nycompltd@repair.example.com',25000
);

CREATE TABLE DISCOUNT_CODE (
   DISCOUNT_CODE CHARACTER(1) PRIMARY KEY  NOT NULL,
   RATE DECIMAL(4,2) ) ;

INSERT INTO DISCOUNT_CODE (
DISCOUNT_CODE, RATE )
VALUES

('H',16),
('M',11),
('L',7),
('N',0)
;

CREATE TABLE MANUFACTURER (
   MANUFACTURER_ID INTEGER PRIMARY KEY  NOT NULL,
   "NAME" VARCHAR(30),
   ADDRESSLINE1 VARCHAR(30),
   ADDRESSLINE2 VARCHAR(30),
   CITY VARCHAR(25),
   "STATE" CHARACTER(2),
   ZIP CHARACTER(10),
   PHONE VARCHAR(12),
   FAX VARCHAR(12),
   EMAIL VARCHAR(40),
   REP VARCHAR(30)
    ) ;

INSERT INTO MANUFACTURER (
MANUFACTURER_ID, "NAME", ADDRESSLINE1, ADDRESSLINE2, CITY, "STATE", ZIP, PHONE, FAX, EMAIL,
REP )
VALUES

(19985678,'Happy End Searching','5 81st Street','Suite 100','Mountain View','CA','94043','650-555-0102','408-555-0103','happysearching@example.com','John Snow'),
(19986982,'Smith Bird Watching','4000 Finch Circle','Building 14','Santa Clara','CA','95051','650-555-0111','408-555-0112','www.sbw@example.com','Brian Washington'),
(19974892,'Wilson Fish Co','20959 Whalers Ave','Building 3','San Jose','OH','95128','650-555-0133','408-555-0133','www.wilsonfish@example.com','Matthew Williams'),
(19986196,'James Deli','250 Marinade Blvd','Suite C','Novato','IL','94949','650-555-0144','408-555-0145','www.jdeli@example.net','Phil Jones'),
(19978451,'All Sushi','399 San Pablo Ave','Building 600','Cleveland','CA','94530','650-555-0140','408-555-0143','www.allsushi@example.com','Teresa Ho'),
(19982461,'Soft Cables','9988 Main Upper Street','Suite 100','Indianapolis','IA','46290','650-555-0151','408-555-0152','www.cbales@example.com','Henry Adams'),
(19984899,'Mike Recording Industries','5109 Union Street Road','Building 8A','San Alfred','CA','94123','415-555-0166','415-555-0165','www.mikerecording@example.com','Mike Black'),
(19965794,'Easy Reach Telephones','975 El Camino Circle','Suite 4055','Santa Clara','VA','95051','408-555-0167','408-555-0168','www.easyreach@example.com','Walter James'),
(19955656,'Soft Circle Opticians','95 Eastway Clearview Drive','Building 1','Boston','MA','02100','617-555-0171','617-555-0172','www.softcircle@example.com','Alfred Nelson'),
(19989719,'Fast Boards','1000 Van Nuys Lane','Suite 904537','Van Nuys','VT','91405','800-555-0173','800-555-0174','www.fboards@example.com','Julie Board'),
(19977775,'Sams Photo Center','9447 West 13th Street','Suite 25','Reading','MN','01867','617-555-0177','617-555-0178','www.photctr@example.com','Laurie Brown'),
(19948494,'Computer Support Center','5632 Michigam Ave',' ','Dearborn','RI','48127','313-555-0181','313-555-0182','www.comsup.example.net','Sam Wright'),
(19971233,'Bills Bank and Sons','5960 Inglewood Pkwy','Building C5','Pleasantville','WI','94588','408-555-0183','408-555-0184','www.billbank@example.com','Frank Smith'),
(19980198,'Pleasant Enterprises','76342 865th Ave','Suite 450','New York','NY','10044','212-555-0184','212-555-0185','www.pleasant@example.com','Louis Lewis'),
(19960022,'Super Computer Products','63 Garcia Rock Way','Floor 22','Albuqerque','NM','87119','505-555-0193','505-555-0193','www.supercomputer@example.com','Tom Cross'),
(19986542,'Florenc Bakery','795 Stone Flour Road','Suite 4','Tombstone','DE','85638','602-555-0182','602-555-0188','florenc.example.com','Jeff Green'),
(19977346,'Upper Cargo Lift Services','2845 Smith Under Road','Suite 7','San Mateo','GA','94403','650-555-0171','650-555-0172','uppercargo.example.com','Frank Peters'),
(19977347,'Super Savings Pharmacy','56 Broadway Lane','Floor 123','Oakland','NH','98123','510-555-0173','510-555-0173','superpharmace.example.com','Tom Brown'),
(19977348,'Early Posting Corp','235 E Market St.','Suite 1','San David','CA','94567','415-555-0138','415-555-0139','superposting.example.com','John Adams'),
(19963322,'Pauls Dairy','236 Hill Street Lane','Suite 6','Orlando','CA','94567','415-555-0140','415-555-0141','paulsdairy.example.com','John White'),
(19963323,'Joseph Ironworks','7655 382nd Street','Suite 200','Mountainside','TX','94043','408-555-0122','408-555-0128','joseph.ironworks@example.com','John Green'),
(19963324,'Nails and Screws','7654 First Avenue','Suite 1005','Ypsilanti','MI','94043','302-555-0191','302-555-0193','nails.screws@example.com','Fred Stanford'),
(19963325,'Main Beauty Hair Salon','44 Overload Street','Building 150','Chicago','WA','94043','211-555-0182','211-555-0183','mainbeauty@example.com','7 of 9'),
(19985590,'Birders United','4000 Cormorant Circle','Building 14','Burlington','OR','95051','206-555-0178','206-555-0179','ann.jones@example.com','Ann Jones'),
(19955564,'Birders United','4000 Cormorant Circle','Building 15','Burlington','OR','95051','206-555-0179','206-555-0179','phil@example.com','Phil Waters'),
(19955565,'Birders United','4000 Cormorant Circle','Building 16','Burlington','OR','95051','206-555-0180','206-555-0179','birders@example.com','Birders'),
(19984681,'Birders United','4000 Cormorant Circle','Building 17','Burlington','OR','95051','206-555-0181','206-555-0179','returnpalace@example.com','Nick Phillips'),
(19984682,'Birders United','4000 Cormorant Circle','Building 18','Burlington','OR','95051','206-555-0182','206-555-0179','brian@example.com','Brian Brown'),
(19941212,'Birders United','4000 Cormorant Circle','Building 19','Burlington','OR','95051','206-555-0183','206-555-0179','bill@example.com','Bill Snider'),
(19987296,'Birders United','4000 Cormorant Circle','Building 20','Burlington','OR','95051','206-555-0184','206-555-0179','gerard@example.com','Jerry Young')
;

CREATE TABLE MICRO_MARKET (
   ZIP_CODE VARCHAR(10) PRIMARY KEY  NOT NULL,
   RADIUS FLOAT(26),
   AREA_LENGTH DOUBLE PRECISION,
   AREA_WIDTH DOUBLE PRECISION ) ;

INSERT INTO MICRO_MARKET (
ZIP_CODE, RADIUS, AREA_LENGTH, AREA_WIDTH )
VALUES

('95051',2.5559E2,6.89856E2,4.78479E2),
('94043',1.57869E2,3.85821E2,1.47538E2),
('85638',7.58648E2,3.28963E2,4.82164E2),
('12347',4.75965E2,3.85849E2,1.46937E2),
('94401',3.68386E2,2.85848E2,1.73794E2),
('95035',6.83396E2,4.72859E2,3.79757E2),
('95117',7.55778E2,5.47967E2,4.68858E2),
('48128',6.84675E2,4.75854E2,4.08074E2),
('48124',7.53765E2,4.87664E2,4.56632E2),
('10095',1.987854E3,9.75875E2,8.65681E2),
('10096',1.876766E3,9.55666E2,9.23556E2)

;

CREATE TABLE PURCHASE_ORDER (
   ORDER_NUM INTEGER PRIMARY KEY  NOT NULL,
   CUSTOMER_ID INTEGER NOT NULL,
   PRODUCT_ID INTEGER NOT NULL,
   QUANTITY SMALLINT,
   SHIPPING_COST DECIMAL(12,2),
   SALES_DATE DATE,
   SHIPPING_DATE DATE,
   FREIGHT_COMPANY VARCHAR(30) ) ;

INSERT INTO PURCHASE_ORDER (
ORDER_NUM, CUSTOMER_ID,  PRODUCT_ID, QUANTITY, SHIPPING_COST,
SALES_DATE, SHIPPING_DATE,  FREIGHT_COMPANY )
VALUES

(10398001,1,980001,10,449,CURRENT_DATE,CURRENT_DATE,'Poney Express'),
(10398002,2,980005,8,359.99,CURRENT_DATE,CURRENT_DATE,'Poney Express'),
(10398003,2,980025,25,275,CURRENT_DATE,CURRENT_DATE,'Poney Express'),
(10398004,3,980030,10,275,CURRENT_DATE,CURRENT_DATE,'Poney Express'),
(10398005,1,980032,100,459,CURRENT_DATE,CURRENT_DATE,'Poney Express'),
(10398006,36,986710,60,55,CURRENT_DATE,CURRENT_DATE,'Slow Snail'),
(10398007,36,985510,120,65,CURRENT_DATE,CURRENT_DATE,'Slow Snail'),
(10398008,106,988765,500,265,CURRENT_DATE,CURRENT_DATE,'Slow Snail'),
(10398009,149,986420,1000,700,CURRENT_DATE,CURRENT_DATE,'Western Fast'),
(10398010,863,986712,100,25,CURRENT_DATE,CURRENT_DATE,'Slow Snail'),
(20198001,777,971266,75,105,CURRENT_DATE,CURRENT_DATE,'We deliver'),
(20598100,753,980601,100,200.99,CURRENT_DATE,CURRENT_DATE,'We deliver'),
(20598101,722,980500,250,2500,CURRENT_DATE,CURRENT_DATE,'Coastal Freight'),
(30198001,409,980001,50,2000.99,CURRENT_DATE,CURRENT_DATE,'Southern Delivery Service'),
(30298004,410,980031,100,700,CURRENT_DATE,CURRENT_DATE,'FR Express')
;

CREATE TABLE PRODUCT_CODE (
   PROD_CODE CHARACTER(2) PRIMARY KEY  NOT NULL,
   DISCOUNT_CODE CHARACTER(1) NOT NULL,
   DESCRIPTION VARCHAR(10) ) ;

INSERT INTO PRODUCT_CODE (
PROD_CODE, DISCOUNT_CODE, DESCRIPTION )
VALUES

('SW','M','Software'),
('HW','H','Hardware'),
('FW','L','Firmware'),
('BK','L','Books'),
('CB','N','Cables'),
('MS','N','Misc')
;

CREATE TABLE PRODUCT (
   PRODUCT_ID INTEGER PRIMARY KEY  NOT NULL,
   MANUFACTURER_ID INTEGER NOT NULL,
   PRODUCT_CODE CHARACTER(2) NOT NULL,
   PURCHASE_COST DECIMAL(12,2),
   QUANTITY_ON_HAND INTEGER,
   MARKUP DECIMAL(4,2),
   AVAILABLE VARCHAR(5) ,
   DESCRIPTION VARCHAR(50) ) ;

INSERT INTO PRODUCT (
PRODUCT_ID, MANUFACTURER_ID, PRODUCT_CODE, PURCHASE_COST, QUANTITY_ON_HAND, MARKUP,
AVAILABLE, DESCRIPTION )
VALUES

(980001,19985678,'SW',1095,800000,8.25,'TRUE','Identity Server'),
(980005,19986982,'SW',11500.99,500,55.25,'TRUE','Accounting Application'),
(980025,19974892,'HW',2095.99,3000,15.75,'TRUE','1Ghz Sun Blade Computer'),
(980030,19986196,'FW',59.95,250,40,'TRUE','10Gb Ram'),
(980032,19978451,'FW',39.95,50,25.5,'TRUE','Sound Card'),
(986710,19982461,'CB',15.98,400,30,'TRUE','Printer Cable'),
(985510,19984899,'HW',595,800,5.75,'TRUE','24 inch Digital Monitor'),
(988765,19965794,'HW',10.95,25,9.75,'TRUE','104-Key Keyboard'),
(986420,19955656,'SW',49.95,0,5.25,'FALSE','Directory Server'),
(986712,19989719,'HW',69.95,1000,10.5,'TRUE','512X IDE DVD-ROM'),
(975789,19977775,'BK',29.98,25,5,'TRUE','Learn Solaris 10'),
(971266,19948494,'CB',25.95,500,30,'TRUE','Network Cable'),
(980601,19971233,'HW',2000.95,2000,25,'TRUE','300Mhz Pentium Computer'),
(980500,19980198,'BK',29.95,1000,33,'TRUE','Learn NetBeans'),
(980002,19960022,'MS',75,0,12,'FALSE','Corporate Expense Survey'),
(980031,19986542,'SW',595.95,75,14,'TRUE','Sun Studio C++'),
(978493,19977346,'BK',19.95,100,5,'TRUE','Client Server Testing'),
(978494,19977347,'BK',18.95,43,4,'TRUE','Learn Java in 1/2 hour'),
(978495,19977348,'BK',24.99,0,1,'FALSE','Writing Web Service Applications'),
(964025,19963322,'SW',209.95,300,41,'TRUE','Jax WS Application Development Environment'),
(964026,19963323,'SW',259.95,220,51,'TRUE','Java EE 6 Application Development Environment'),
(964027,19963324,'SW',269.95,700,61,'TRUE','Java Application Development Environment'),
(964028,19963325,'SW',219.95,300,32,'TRUE','NetBeans Development Environment'),
(980122,19985590,'HW',1400.95,100,25,'TRUE','Solaris x86 Computer'),
(958888,19955564,'HW',799.99,0,1.5,'FALSE','Ultra Spacr 999Mhz Computer'),
(958889,19955565,'HW',595.95,0,1.25,'FALSE','686 7Ghz Computer'),
(986733,19984681,'HW',69.98,400,55,'TRUE','A1 900 watts Speakers'),
(986734,19984682,'HW',49.95,200,65,'TRUE','Mini Computer Speakers'),
(948933,19941212,'MS',36.95,50,75,'TRUE','Computer Tool Kit'),
(984666,19987296,'HW',199.95,25,45,'TRUE','Flat screen Monitor')
;

ALTER TABLE PRODUCT ADD CONSTRAINT FOREIGNKEY_MANUFACTURER_ID FOREIGN KEY (
MANUFACTURER_ID )
        REFERENCES MANUFACTURER ( MANUFACTURER_ID ) ON UPDATE no action ON DELETE
no action;

ALTER TABLE PRODUCT ADD CONSTRAINT FOREIGNKEY_PRODUCT_CODE FOREIGN KEY (
PRODUCT_CODE )
        REFERENCES PRODUCT_CODE ( PROD_CODE ) ON UPDATE no action ON
DELETE no action;

ALTER TABLE CUSTOMER ADD CONSTRAINT FOREIGNKEY_DISCOUNT_CODE FOREIGN KEY
( DISCOUNT_CODE )
        REFERENCES DISCOUNT_CODE ( DISCOUNT_CODE ) ON UPDATE no action
ON DELETE no action;

ALTER TABLE CUSTOMER ADD CONSTRAINT FOREIGNKEY_ZIP FOREIGN KEY ( ZIP )
        REFERENCES MICRO_MARKET ( ZIP_CODE ) ON UPDATE no action ON
DELETE no action;

ALTER TABLE PURCHASE_ORDER ADD CONSTRAINT FOREIGNKEY_CUSTOMER_ID FOREIGN KEY (
CUSTOMER_ID )
        REFERENCES CUSTOMER ( CUSTOMER_ID ) ON UPDATE no action ON
DELETE no action;

ALTER TABLE PURCHASE_ORDER ADD CONSTRAINT FOREIGNKEY_PRODUCT_ID FOREIGN KEY (
PRODUCT_ID )
        REFERENCES PRODUCT ( PRODUCT_ID ) ON UPDATE no action ON DELETE
no action;

disconnect;

quality97%

¤ Die Informationen auf dieser Webseite wurden nach bestem Wissen sorgfältig zusammengestellt. Es wird jedoch weder Vollständigkeit, noch Richtigkeit, noch Qualität der bereit gestellten Informationen zugesichert.0.19Bemerkung: (vorverarbeitet) ¤

*Bot Zugriff

Wurzel

Suchen

Beweissystem der NASA

Beweissystem Isabelle

NIST Cobol Testsuite

Cephes Mathematical Library

Wiener Entwicklungsmethode

Haftungshinweis

Die Informationen auf dieser Webseite wurden nach bestem Wissen sorgfältig zusammengestellt. Es wird jedoch weder Vollständigkeit, noch Richtigkeit, noch Qualität der bereit gestellten Informationen zugesichert.

Bemerkung:

Die farbliche Syntaxdarstellung ist noch experimentell.