Example of how to creat a database using a script file spj.sql
Copy the text below, starting with the drop command, into a file named spj.sql and run the following SQLExec application at a dos prompt:
>SqlExec "DSN=spj" spj.sql
where "DSN=spj" referes to the ODBC data source name: spj .
| 1. | Click Start, point to Control Panel, double-click Administrative Tools, and then double-click Data Sources(ODBC). |
| 2. | Click the System DSN tab, and then click Add. |
| 3. | Click the database driver that corresponds with the database type to which you are connecting, and then click Finish. |
| 4. | Type the data source name. Make sure that you choose a name that you can remember. You will need to use this name later. |
| 5. | Click Select. |
| 6. | Click the correct database, and then click OK. |
| 7. | Click OK, and then click OK. |
Copy the SQl scrip below:
////////////////////////////////////////////////////////////////
drop table SPJ;
drop table J;
drop table P;
drop table S;
create table S
(sNo char(2) ,
name char(5),
status char(2),
city char(6));
alter table S add constraint SPK primary key (sNo);
create table P
(pNo char(2) ,
name char(5),
color char(5),
weight integer,
city char(6));
alter table P add constraint PPK primary key (pNo);
create table J
(jNo char(2) ,
name char(8),
city char(6));
alter table J add constraint JPK primary key (jNo);
create table SPJ
(sNo char(2),
pNo char(2),
jNo char(2),
qty integer);
alter table SPJ add constraint SPJPK primary key (sNo,pNo,jNo);
alter table SPJ add constraint sFK
foreign key (sNo)
references S(sNo);
alter table SPJ add constraint pFK
foreign key (pNo)
references P(pNo);
alter table SPJ add constraint jFK
foreign key (jNo)
references J(jNo);
INSERT INTO S
VALUES ('S1','Smith',20,'London');
INSERT INTO S
VALUES ('S2','Jones',10,'Paris');
insert into S
values ('S3','Blake',30,'Paris');
insert into S
values ('S4','Clark',20,'London');
insert into S
values ('S5','Adams',30,'Athens');
insert into P
values ('P1','Nut','Red',12,'London');
insert into P
values ('P2','Bolt','Green',17,'Paris');
insert into P
values ('P3','Screw','Blue',17,'Rome');
insert into P
values ('P4','Screw','Red',14,'London');
insert into P
values ('P5','Cam','Blue',12,'Paris');
insert into P
values ('P6','Cog','Red',19,'London');
insert into J
values ('J1','Sorter','Paris');
insert into J
values ('J2','Punch','Rome');
insert into J
values ('J3','Reader','Athens');
insert into J
values ('J4','Console','Athens');
insert into J
values ('J5','Collator','London');
insert into J
values ('J6','Terminal','Oslo');
insert into J
values ('J7','Tape','London');
insert into SPJ
values ('S1','P1','J1',200);
insert into SPJ
values ('S1','P1','J4',700);
insert into SPJ
values ('S2','P3','J1',400);
insert into SPJ
values ('S2','P3','J2',200);
insert into SPJ
values ('S2','P3','J3',200);
insert into SPJ
values ('S2','P3','J4',500);
insert into SPJ
values ('S2','P3','J5',600);
insert into SPJ
values ('S2','P3','J6',400);
insert into SPJ
values ('S2','P3','J7',800);
insert into SPJ
values ('S2','P5','J2',100);
insert into SPJ
values ('S3','P3','J1',200);
insert into SPJ
values ('S3','P4','J2',500);
insert into SPJ
values ('S4','P6','J3',300);
insert into SPJ
values ('S4','P6','J7',300);
insert into SPJ
values ('S5','P2','J2',200);
insert into SPJ
values ('S5','P2','J4',100);
insert into SPJ
values ('S5','P5','J5',500);
insert into SPJ
values ('S5','P5','J7',100);
insert into SPJ
values ('S5','P6','J2',200);
insert into SPJ
values ('S5','P1','J4',100);
insert into SPJ
values ('S5','P3','J4',200);
insert into SPJ
values ('S5','P4','J4',800);
insert into SPJ
values ('S5','P5','J4',400);
insert into SPJ
values ('S5','P6','J4',500);