Tuesday, October 27, 2015

Sample SQL Database script with data

DB Diagram:




DB Script:

 
create table zipcodes (
  zip int primary key,
  city  varchar(30),
  State varchar(20));
 
 
create table employees (
  eno  varchar(10) primary key,
  ename  varchar(30),
  zip  int references zipcodes,
  hire_date date);
 
 
 
create table books (
  bno  int primary key,
  bname  varchar(30),
  qoh  int not null,
  price  dec(6,2) not null);
 
 
create table customers (
  cno   int primary key,
  cname  varchar(30),
  street varchar(30),
  zip  int  references zipcodes,
  phone  char(12));
 
 
 
create table orders (
  ono  int  primary key,
  cno  int  references customers,
  eno  varchar(10)  references employees,
  received date,
  shipped date
);
 
 
 
create table odetails (
  ono  int  references orders,
  bno  int  references books,
  quantity int not null,
  primary key (ono, bno));
 
insert into zipcodes values (98225, 'Bellingham', 'WA'); 
insert into zipcodes values (95388, 'Winton', 'CA'); 
insert into zipcodes values (44242, 'Stow', 'OH'); 
insert into zipcodes values (61536, 'Hanna city', 'IL'); 
insert into zipcodes values (01254, 'Richmond', 'MA'); 
insert into zipcodes values (95124, 'San Jose', 'CA'); 
insert into zipcodes values (95382, 'Turlock', 'MA'); 
insert into zipcodes values (95380, 'Turlock', 'CA');
 
 
insert into employees values ('P0239400', 'Jones Hoffer',98225, '2000-12-12'); 
insert into employees values ('P0239401', 'Jeffrey Prescott',95388, '2006-01-01'); 
insert into employees values ('P0239402', 'Fred NcFaddeb',95124, '2008-09-01');
 
 
insert into books values (10506, 'Accounting 101',200, 129.99); 
insert into books values (10507, 'Management 101',159, 109.99); 
insert into books values (10508, 'Fraud Cases',190, 179.99); 
insert into books values (10509, 'CPA Review',65, 299.99); 
insert into books values (10601, 'Peachtree for Dummies',322, 49.99); 
insert into books values (10701, 'Financial Accounting',129, 164.99); 
insert into books values (10800, 'Managerial Accounting',155, 114.99); 
insert into books values (10900, 'Cost Accounting',122, 119.99); 
insert into books values (10901, 'Intermediate Accounting',123, 164.99); 
insert into books values (10902, 'XBRL in Nutshell',124, 109.99);
 
insert into customers values (23511, 'Michelle Kuan', '123 Main St.',98225, '360-636-5555'); 
insert into customers values (23512, 'George Myer', '237 Ash Ave.',95124, '312-678-5555'); 
insert into customers values (23513, 'Richard Gold', '111 Inwood St.',95124, '312-883-7337'); 
insert into customers values (23514, 'Robert Smith', '54 Gate Dr.',95388, '206-832-1221'); 
insert into customers values (23515, 'Christopher David', '777 Loto St.',98225, '360-458-9878'); 
insert into customers values (23516, 'Adam Beethoven', '234 Park Rd..',95380, '209-546-7299'); 
insert into customers values (23517, 'Lidwig Bach', '5790 Walnut St.',95382, '209-638-2712');
 
 
insert into orders values (1020, 23511, 'P0239400', '2009-10-18', '2009-10-20'); 
insert into orders values (1021, 23511, 'P0239400', '2009-10-29', '2009-10-31'); 
insert into orders values (1022, 23512, 'P0239401', '2009-11-10', '2009-11-13'); 
insert into orders (ONO, CNO, ENO, RECEIVED) values (1023, 23513, 'P0239402', '2009-11-15'); 
insert into orders (ONO, CNO, ENO, RECEIVED) values (1024, 23511, 'P0239400', '2009-11-16');
 
 
insert into odetails values (1020, 10506,1); insert into odetails values (1020, 10507,2); 
insert into odetails values (1020, 10508,2); insert into odetails values (1020, 10509,3); 
insert into odetails values (1021, 10601,4); insert into odetails values (1022, 10601,1); 
insert into odetails values (1022, 10701,2); insert into odetails values (1023, 10800,4); 
insert into odetails values (1023, 10900,1); insert into odetails values (1024, 10900,7);