home *** CD-ROM | disk | FTP | other *** search
/ Symantec Visual Cafe for Java 2.5 / symantec-visual-cafe-2.5-database-dev-edition.iso / DBServ / SAMPLES / SAMPLES.ZIP / SAMPLES.SQL next >
Encoding:
Text File  |  1997-02-21  |  14.9 KB  |  457 lines
  1. Sample Database Command File 
  2.  
  3.  
  4. The following ISQL commands were used to create the sample database.
  5.      % Usage:        isql read e:dbfilessample50makesdb5.sql
  6.      %
  7.      % This command file reloads a database that was unloaded using "unload".
  8.      %
  9.      %
  10.  
  11.      SET OPTION Statistics = 3;
  12.      SET OPTION Date_order = 'YMD';
  13.  
  14.  
  15.      %%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
  16.      %    Create userids and grant user permissions
  17.      %%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
  18.  
  19.      GRANT CONNECT TO "DBA" IDENTIFIED BY "SQL";
  20.      GRANT RESOURCE, DBA, SCHEDULE TO "DBA";
  21.      commit work;
  22.  
  23.  
  24.      %%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
  25.      %    Create tables
  26.  
  27.      %%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
  28.  
  29.      CREATE TABLE "DBA"."sales_order"
  30.      (
  31.              "id"            integer NOT NULL,
  32.              "cust_id"               integer NOT NULL,
  33.              "order_date"            date NOT NULL,
  34.              "fin_code_id"           char(2),
  35.              "region"                char(7),
  36.              "sales_rep"             integer NOT NULL,
  37.              PRIMARY KEY ("id"),
  38.      );
  39.      CREATE TABLE "DBA"."sales_order_items"
  40.      (
  41.  
  42.              "id"            integer NOT NULL,
  43.              "line_id"               smallint NOT NULL,
  44.              "prod_id"               integer NOT NULL,
  45.              "quantity"              integer NOT NULL,
  46.              "ship_date"             date NOT NULL,
  47.              PRIMARY KEY ("id", "line_id"),
  48.      );
  49.      CREATE TABLE "DBA"."contact"
  50.      (
  51.              "id"            integer NOT NULL,
  52.              "last_name"             char(15) NOT NULL,
  53.              "first_name"            char(15) NOT NULL,
  54.  
  55.              "title"     char(2) NOT NULL,
  56.              "street"                char(30) NOT NULL,
  57.              "city"          char(20) NOT NULL,
  58.              "state"     char(2) NOT NULL,
  59.              "zip"           char(5) NOT NULL,
  60.              "phone"     char(10),
  61.              "fax"           char(10),
  62.              PRIMARY KEY ("id"),
  63.      );
  64.      CREATE TABLE "DBA"."customer"
  65.      (
  66.              "id"            integer NOT NULL,
  67.  
  68.              "fname"     char(15) NOT NULL,
  69.              "lname"     char(20) NOT NULL,
  70.              "address"               char(35) NOT NULL,
  71.              "city"          char(20) NOT NULL,
  72.              "state"     char(2) NOT NULL,
  73.              "zip"           char(10) NOT NULL,
  74.              "phone"     char(12) NOT NULL,
  75.              "company_name"          char(35),
  76.              PRIMARY KEY ("id"),
  77.      );
  78.      CREATE TABLE "DBA"."fin_code"
  79.  
  80.      (
  81.              "code"          char(2) NOT NULL,
  82.              "type"          char(10) NOT NULL,
  83.              "description"           char(50),
  84.              PRIMARY KEY ("code"),
  85.      );
  86.      CREATE TABLE "DBA"."fin_data"
  87.      (
  88.              "year"          char(4) NOT NULL,
  89.              "quarter"               char(2) NOT NULL,
  90.              "code"          char(2) NOT NULL,
  91.              "amount"                numeric(9,0),
  92.  
  93.              PRIMARY KEY ("year", "quarter", "code"),
  94.      );
  95.      CREATE TABLE "DBA"."product"
  96.      (
  97.              "id"            integer NOT NULL,
  98.              "name"          char(15) NOT NULL,
  99.              "description"           char(30) NOT NULL,
  100.              "size"          char(18) NOT NULL,
  101.              "color"     char(6) NOT NULL,
  102.              "quantity"              integer NOT NULL,
  103.              "unit_price"            numeric(15,2) NOT NULL,
  104.              PRIMARY KEY ("id"),
  105.  
  106.      );
  107.      CREATE TABLE "DBA"."department"
  108.      (
  109.              "dept_id"               integer NOT NULL,
  110.              "dept_name"             char(40) NOT NULL,
  111.              "dept_head_id"          integer,
  112.              PRIMARY KEY ("dept_id"),
  113.      );
  114.      CREATE TABLE "DBA"."employee"
  115.      (
  116.              "emp_id"                integer NOT NULL,
  117.              "manager_id"            integer,
  118.              "emp_fname"             char(20) NOT NULL,
  119.  
  120.              "emp_lname"             char(20) NOT NULL,
  121.              "dept_id"               integer NOT NULL,
  122.              "street"                char(40) NOT NULL,
  123.              "city"          char(20) NOT NULL,
  124.              "state"     char(4) NOT NULL,
  125.              "zip_code"              char(9) NOT NULL,
  126.              "phone"     char(10),
  127.              "status"                char(1),
  128.              "ss_number"             char(11) NOT NULL,
  129.  
  130.              "salary"                numeric(20,3) NOT NULL,
  131.              "start_date"            date NOT NULL,
  132.              "termination_date"              date,
  133.              "birth_date"            date,
  134.              "bene_health_ins"               char(1),
  135.              "bene_life_ins"  char(1),
  136.              "bene_day_care"  char(1),
  137.              "sex"           char(1),
  138.              PRIMARY KEY ("emp_id"),
  139.      );
  140.  
  141.      commit work;
  142.  
  143.  
  144.      %%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
  145.      %    Reload data
  146.      %%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
  147.  
  148.      INPUT INTO "DBA"."sales_order"
  149.              FROM e:dbfilesdocsamplunload117.dat
  150.              FORMAT ASCII
  151.              BY ORDER;
  152.  
  153.      INPUT INTO "DBA"."sales_order_items"
  154.              FROM e:dbfilesdocsamplunload118.dat
  155.              FORMAT ASCII
  156.              BY ORDER;
  157.  
  158.      INPUT INTO "DBA"."contact"
  159.              FROM e:dbfilesdocsamplunload119.dat
  160.              FORMAT ASCII
  161.              BY ORDER;
  162.  
  163.  
  164.      INPUT INTO "DBA"."customer"
  165.              FROM e:dbfilesdocsamplunload120.dat
  166.              FORMAT ASCII
  167.              BY ORDER;
  168.  
  169.      INPUT INTO "DBA"."fin_code"
  170.              FROM e:dbfilesdocsamplunload121.dat
  171.              FORMAT ASCII
  172.              BY ORDER;
  173.  
  174.      INPUT INTO "DBA"."fin_data"
  175.              FROM e:dbfilesdocsamplunload122.dat
  176.              FORMAT ASCII
  177.              BY ORDER;
  178.  
  179.      INPUT INTO "DBA"."product"
  180.              FROM e:dbfilesdocsamplunload123.dat
  181.              FORMAT ASCII
  182.  
  183.              BY ORDER;
  184.  
  185.      INPUT INTO "DBA"."department"
  186.              FROM e:dbfilesdocsamplunload124.dat
  187.              FORMAT ASCII
  188.              BY ORDER;
  189.  
  190.      INPUT INTO "DBA"."employee"
  191.              FROM e:dbfilesdocsamplunload125.dat
  192.              FORMAT ASCII
  193.              BY ORDER;
  194.  
  195.      commit work;
  196.  
  197.  
  198.      %%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
  199.      %    Add foreign keys definitions
  200.      %%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
  201.  
  202.  
  203.      ALTER TABLE "DBA"."sales_order"
  204.              ADD FOREIGN KEY "ky_so_employee_id" ("sales_rep") REFERENCES "DBA"."employee" ("emp_id");
  205.  
  206.  
  207.      ALTER TABLE "DBA"."sales_order"
  208.              ADD FOREIGN KEY "ky_so_fincode" ("fin_code_id") REFERENCES "DBA"."fin_code" ("code") on delete set null;
  209.  
  210.      ALTER TABLE "DBA"."sales_order"
  211.              ADD FOREIGN KEY "ky_so_customer" ("cust_id") REFERENCES "DBA"."customer" ("id");
  212.      CREATE INDEX "ix_sales_cust" ON "DBA"."sales_order"
  213.      (
  214.              "cust_id" ASC
  215.      );
  216.  
  217.      ALTER TABLE "DBA"."sales_order_items"
  218.              ADD FOREIGN KEY "ky_prod_id" ("prod_id") REFERENCES "DBA"."product" ("id");
  219.  
  220.  
  221.      ALTER TABLE "DBA"."sales_order_items"
  222.              ADD FOREIGN KEY "id_fk" ("id") REFERENCES "DBA"."sales_order" ("id") on delete cascade;
  223.      CREATE INDEX "ix_item_prod" ON "DBA"."sales_order_items"
  224.      (
  225.              "prod_id" ASC
  226.      );
  227.      CREATE INDEX "ix_cust_name" ON "DBA"."customer"
  228.      (
  229.              "lname" ASC,
  230.              "fname" ASC
  231.      );
  232.  
  233.      ALTER TABLE "DBA"."fin_data"
  234.              ADD FOREIGN KEY "ky_code_data" ("code") REFERENCES "DBA"."fin_code" ("code") on delete cascade;
  235.  
  236.      CREATE INDEX "fin_data_idx" ON "DBA"."fin_data"
  237.      (
  238.              "code" ASC
  239.      );
  240.      CREATE INDEX "ix_prod_name" ON "DBA"."product"
  241.      (
  242.              "name" ASC);
  243.      CREATE INDEX "ix_prod_desc" ON "DBA"."product"
  244.      (
  245.              "description" ASC);
  246.      CREATE INDEX "ix_prod_size" ON "DBA"."product"
  247.      (
  248.              "size" ASC);
  249.      CREATE INDEX "ix_prod_color" ON "DBA"."product"
  250.      (
  251.              "color" ASC
  252.      );
  253.  
  254.  
  255.      ALTER TABLE "DBA"."department"
  256.              ADD FOREIGN KEY "ky_dept_head" ("dept_head_id") REFERENCES "DBA"."employee" ("emp_id") on delete set null;
  257.  
  258.      ALTER TABLE "DBA"."employee"
  259.              ADD FOREIGN KEY "ky_dept_id" ("dept_id") REFERENCES "DBA"."department" ("dept_id");
  260.      commit work;
  261.  
  262.  
  263.      %%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
  264.      %    Create views
  265.      %%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
  266.  
  267.      commit work;
  268.  
  269.  
  270.      %%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
  271.      %    Set option values
  272.      %%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
  273.  
  274.  
  275.      SET OPTION Statistics =;
  276.  
  277.      SET OPTION Date_order =;
  278.  
  279.  
  280.      %
  281.      %SQL Option Statements for user
  282.      %
  283.  
  284.      SET OPTION "PUBLIC"."Blocking" = 'On';
  285.      SET OPTION "PUBLIC"."Checkpoint_time" = '60';
  286.      SET OPTION "PUBLIC"."Conversion_error" = 'On';
  287.      SET OPTION "PUBLIC"."Timestamp_format" = 'YYYY-MM-DD HH:NN:SS.SSS';
  288.      SET OPTION "PUBLIC"."Time_format" = 'HH:NN:SS.SSS';
  289.      SET OPTION "PUBLIC"."Date_format" = 'YYYY-MM-DD';
  290.      SET OPTION "PUBLIC"."Date_order" = 'YMD';
  291.      SET OPTION "PUBLIC"."Isolation_level" = '0';
  292.      SET OPTION "PUBLIC"."Precision" = '30';
  293.  
  294.      SET OPTION "PUBLIC"."Recovery_time" = '2';
  295.      SET OPTION "PUBLIC"."Row_counts" = 'Off';
  296.      SET OPTION "PUBLIC"."Scale" = '6';
  297.      SET OPTION "PUBLIC"."Thread_count" = '0';
  298.      SET OPTION "PUBLIC"."Wait_for_commit" = 'Off';
  299.      SET OPTION "PUBLIC"."Auto_commit" = 'Off';
  300.      SET OPTION "PUBLIC"."Auto_refetch" = 'On';
  301.      SET OPTION "PUBLIC"."Bell" = 'On';
  302.      SET OPTION "PUBLIC"."Commit_on_exit" = 'On';
  303.      SET OPTION "PUBLIC"."Echo" = 'On';
  304.      SET OPTION "PUBLIC"."Headings" = 'On';
  305.      SET OPTION "PUBLIC"."Input_format" = 'ASCII';
  306.      SET OPTION "PUBLIC"."ISQL_log" = '';
  307.  
  308.      SET OPTION "PUBLIC"."NULLS" = '(NULL)';
  309.      SET OPTION "PUBLIC"."On_error" = 'Prompt';
  310.      SET OPTION "PUBLIC"."Output_format" = 'ASCII';
  311.      SET OPTION "PUBLIC"."Output_length" = '0';
  312.      SET OPTION "PUBLIC"."Screen_format" = 'Text';
  313.      SET OPTION "PUBLIC"."Statistics" = '3';
  314.      SET OPTION "PUBLIC"."Truncation_length" = '30';
  315.      SET OPTION "PUBLIC"."Command_delimiter" = ';';
  316.  
  317.  
  318.      %
  319.      %SQL Option Statements for user
  320.      %
  321.  
  322.      SET OPTION "DBA"."Wait_for_commit" = 'off';
  323.      SET OPTION "DBA"."Statistics" = '3';
  324.  
  325.      SET OPTION "DBA"."Date_order" = 'YMD';
  326.      commit work;
  327.  
  328.  
  329.      %%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
  330.      %    Create procedures
  331.      %%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
  332.  
  333.      CONNECT "DBA" IDENTIFIED BY "SQL";
  334.  
  335.      SET TEMPORARY OPTION Command_delimiter = ';;';
  336.  
  337.      create procedure
  338.      sp_retrieve_contacts()
  339.      result(id integer,last_name char(15),first_name char(15),title char(2),street char(30),city char(20),state char(2),zip char(5),phone char(10),fax char(10))
  340.      begin
  341.        select id,last_name,first_name,title,street,city,state,zip,phone,fax
  342.  
  343.          from contact order by contact.id asc
  344.      end;;
  345.  
  346.  
  347.      create procedure
  348.      sp_product_info(inout prod_id integer)
  349.      result(id integer,name char(15),description char(30),size char(18),color char(6),quantity integer,unit_price decimal(15,2),picture_name char(12))
  350.      begin
  351.        select id,name,description,size,color,quantity,unit_price,picture_name
  352.          from product where id=prod_id
  353.      end;;
  354.  
  355.  
  356.      create procedure
  357.      sp_customer_list()
  358.      result(id integer,company_name char(35))
  359.  
  360.      begin
  361.        select id,company_name from customer
  362.      end;;
  363.  
  364.  
  365.      create procedure
  366.      sp_contacts(in action char(1),in contact_id integer,in contact_old_id integer,in contact_last_name char(15),in contact_first_name char(15),in contact_title char(2),in contact_street char(30),in contact_city char(20),in contact_state char(2),in contact_zip char(5),in contact_phone char(10),in contact_fax char(10))
  367.      begin
  368.        case action when 'I' then
  369.          insert into contact(id,last_name,first_name,title,street,city,state,zip,
  370.  
  371.            phone,fax) values(contact_id,contact_last_name,contact_first_name,
  372.            contact_title,contact_street,contact_city,contact_state,contact_zip,
  373.            contact_phone,contact_fax) when 'U' then
  374.          update contact set contact.id=contact_id,contact.last_name=contact_last_name,
  375.            contact.first_name=contact_first_name,contact.title=contact_title,
  376.            contact.street=contact_street,contact.city=contact_city,contact.state=contact_state,
  377.            contact.zip=contact_zip,contact.phone=contact_phone,contact.fax=contact_fax
  378.  
  379.            where contact.id=contact_old_id when 'D' then
  380.          delete from contact where contact.id=contact_old_id
  381.        end case
  382.      end;;
  383.  
  384.  
  385.      create procedure
  386.      sp_sales_order_items(in ord_id integer,in product integer)
  387.      result(line_id integer,prod_id integer,quantity integer,ship_date date)
  388.      begin
  389.        select line_id,prod_id,quantity,ship_date from sales_order_items where id
  390.          =ord_id
  391.      end;;
  392.  
  393.  
  394.      create procedure
  395.      sp_sales_order(in customer_id integer,in product_id integer)
  396.  
  397.      result(id integer,order_date date,fin_code_id char(2),region char(7),sales_rep integer)
  398.      begin
  399.        select s.id,s.order_date,s.fin_code_id,s.region,s.sales_rep
  400.          from sales_order as s,sales_order_items as i where s.cust_id=customer_id and i.prod_id
  401.          =product_id and s.id=i.id
  402.      end;;
  403.  
  404.  
  405.      create procedure
  406.      sp_customer_products(inout customer_id integer)
  407.      result(id integer,quantity_ordered integer)
  408.      begin
  409.        select product.id,sum(sales_order_items.quantity) from product
  410.  
  411.          ,sales_order_items,sales_order where sales_order.cust_id=customer_id
  412.          and sales_order.id=sales_order_items.id and sales_order_items.prod_id=product.id
  413.          group by product.id
  414.      end;;
  415.  
  416.      SET TEMPORARY OPTION Command_delimiter = ';';;
  417.  
  418.      CONNECT "DBA" IDENTIFIED BY "SQL";
  419.  
  420.      commit work;
  421.  
  422.  
  423.      %%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
  424.      %    Create triggers
  425.      %%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
  426.  
  427.      CONNECT "DBA" IDENTIFIED BY "SQL";
  428.  
  429.      SET TEMPORARY OPTION Command_delimiter = ';;';
  430.  
  431.      create trigger
  432.      tr_manager before update of dept_head_id on department
  433.      referencing old as old_dept new as new_dept
  434.  
  435.      for each row
  436.      begin
  437.        update employee set employee.manager_id=new_dept.dept_head_id
  438.          where employee.dept_id=old_dept.dept_id
  439.      end;;
  440.  
  441.  
  442.      create trigger
  443.      tr_dept_id after update of dept_id on department
  444.      referencing old as old_dept new as new_dept
  445.      for each row
  446.      begin
  447.        update employee set employee.dept_id=new_dept.dept_id where employee.dept_id
  448.          =old_dept.dept_id
  449.      end;;
  450.  
  451.      SET TEMPORARY OPTION Command_delimiter = ';';;
  452.  
  453.      CONNECT "DBA" IDENTIFIED BY "SQL";
  454.  
  455.      commit work;
  456.  
  457.