#---------------------------
# Xcompany dump file.
# create a database xcompany
# and enters sample records
#---------------------------
create database if not exists xcompany;
use xcompany ;
#----------------------------
# Table structure for categories
#----------------------------
drop table if exists categories;
create table categories (
cat_code int(10) unsigned not null auto_increment,
category char(10),
cat_sort_order int(11) not null default '0',
primary key (cat_code),
index category (category))
type=MyISAM;
#----------------------------
# Records for table categories
#----------------------------
insert into categories values (1, 'Software', 2) ;
insert into categories values (2, 'Hardware', 3) ;
insert into categories values (3, 'Services', 1) ;
insert into categories values (4, 'Education', 4) ;
#----------------------------
# Table structure for countries
#----------------------------
drop table if exists countries;
create table countries (
country_code int(10) unsigned not null auto_increment,
country char(20) not null,
country_sort_order int(10) unsigned not null default '0',
country_abbr char(3) not null,
primary key (country_code),
index country (country))
type=MyISAM;
#----------------------------
# Records for table countries
#----------------------------
insert into countries values (1, 'France', 3, 'FRA') ;
insert into countries values (2, 'Italy', 2, 'ITA') ;
insert into countries values (3, 'United Kingdom', 4, 'UK') ;
insert into countries values (4, 'USA', 1, 'USA') ;
#----------------------------
# Table structure for departments
#----------------------------
drop table if exists departments;
create table departments (
dept_code int(10) unsigned not null auto_increment,
dept char(20) not null,
dept_sort_order int(11) not null default '0',
primary key (dept_code),
index dept (dept))
type=MyISAM;
#----------------------------
# Records for table departments
#----------------------------
insert into departments values (1, 'Personnel', 5) ;
insert into departments values (2, 'Training', 2) ;
insert into departments values (3, 'Research', 4) ;
insert into departments values (4, 'Sales', 1) ;
insert into departments values (5, 'Development', 3) ;
#----------------------------
# Table structure for employees
#----------------------------
drop table if exists employees;
create table employees (
employee_code int(10) unsigned not null auto_increment,
gender enum('M','F','UNKN') default 'F',
name char(20) not null,
salary int(11),
DOB date not null default '0000-00-00',
dept_code int(10) unsigned,
loc_code int(10) unsigned,
primary key (employee_code),
index name (name),
index loc_code (loc_code),
index dept_code (dept_code))
type=MyISAM;
#----------------------------
# Records for table employees
#----------------------------
insert into employees values (1, 'M', 'Luigi', 5000, '1962-02-03', 1, 1) ;
insert into employees values (2, 'M', 'Mario', 5100, '1956-10-24', 2, 1) ;
insert into employees values (3, 'M', 'Fred', 4900, '1970-05-05', 2, 2) ;
insert into employees values (4, 'F', 'Cinzia', 5600, '1978-01-09', 3, 3) ;
insert into employees values (5, 'M', 'Marco', 5600, '1943-03-09', 3, 3) ;
insert into employees values (6, 'M', 'Jim', 5500, '1961-11-30', 4, 1) ;
insert into employees values (7, 'M', 'John', 5550, '1955-04-02', 4, 2) ;
insert into employees values (8, 'F', 'Sue', 5600, '1975-09-07', 3, 3) ;
insert into employees values (9, 'F', 'Maria', 5700, '1979-12-11', 5, 4) ;
insert into employees values (10, 'F', 'Giselle', 5800, '1977-07-20', 4, 5) ;
insert into employees values (11, 'F', 'Sonia', 5600, '1974-05-27', 5, 5) ;
insert into employees values (12, 'M', 'Jacques', 5550, '1976-11-16', 5, 5) ;
insert into employees values (13, 'M', 'Paul', 5400, '1967-08-15', 4, 4) ;
insert into employees values (14, 'F', 'Jennifer', 5950, '1968-01-26', 4, 7) ;
insert into employees values (15, 'F', 'Julie', 5900, '1964-05-21', 5, 8) ;
insert into employees values (16, 'F', 'Christine', 5700, '1980-02-20', 2, 6) ;
insert into employees values (17, 'M', 'Don', 5700, '1961-07-12', 1, 6) ;
insert into employees values (18, 'M', 'Sam', 5600, '1975-10-17', 4, 7) ;
insert into employees values (19, 'F', 'Colette', 6100, '1960-08-14', 4, 8) ;
insert into employees values (20, 'F', 'Connie', 5950, '1972-03-07', 4, 9) ;
insert into employees values (21, 'M', 'Guy', 5800, '1971-06-19', 5, 9) ;
insert into employees values (22, 'M', 'Steve', 5750, '1974-10-30', 5, 8) ;
insert into employees values (23, 'M', 'Antonio', 6200, '1968-12-08', 5, 8) ;
insert into employees values (24, 'F', 'Nina', 6100, '1967-05-24', 5, 9) ;
#----------------------------
# Table structure for locations
#----------------------------
drop table if exists locations;
create table locations (
loc_code int(10) unsigned not null auto_increment,
location char(20) not null,
country_code int(10) unsigned not null default '0',
loc_sort_order int(11) not null default '0',
abbr char(3) not null,
primary key (loc_code),
index location (location))
type=MyISAM;
#----------------------------
# Records for table locations
#----------------------------
insert into locations values (1, 'Roma', 2, 6, 'ROM') ;
insert into locations values (2, 'Milano', 2, 5, 'MIL') ;
insert into locations values (3, 'Cagliari', 2, 4, 'CAG') ;
insert into locations values (4, 'Paris', 1, 7, 'PAR') ;
insert into locations values (5, 'Marseille', 1, 8, 'MAR') ;
insert into locations values (6, 'London', 3, 9, 'LON') ;
insert into locations values (7, 'Manchester', 3, 10, 'MAN') ;
insert into locations values (8, 'New York', 4, 1, 'NY') ;
insert into locations values (9, 'Boston', 4, 2, 'BOS') ;
insert into locations values (10, 'Miami', 4, 3, 'MIA') ;
#----------------------------
# Table structure for sales
#----------------------------
drop table if exists sales;
create table sales (
sale_date date not null default '0000-00-00',
sale_amount int(11),
cat_code int(10) unsigned not null default '0',
employee_code int(10) unsigned not null default '0',
primary key (sale_date, cat_code, employee_code))
type=MyISAM;
#----------------------------
# Records for table sales
#----------------------------
insert into sales values ('2001-01-23', 72000, 2, 10) ;
insert into sales values ('2001-01-24', 20250, 1, 13) ;
insert into sales values ('2001-01-24', 38250, 3, 7) ;
insert into sales values ('2001-01-25', 40500, 1, 4) ;
insert into sales values ('2001-02-01', 90000, 4, 6) ;
insert into sales values ('2001-02-04', 74250, 3, 14) ;
insert into sales values ('2001-02-06', 36000, 2, 14) ;
insert into sales values ('2001-02-12', 54000, 2, 18) ;
insert into sales values ('2001-02-27', 85500, 1, 10) ;
insert into sales values ('2001-03-03', 76500, 3, 18) ;
insert into sales values ('2001-03-09', 27000, 4, 20) ;
insert into sales values ('2001-02-18', 26550, 2, 7) ;
insert into sales values ('2001-03-17', 30150, 2, 19) ;
insert into sales values ('2001-03-22', 35100, 4, 13) ;
insert into sales values ('2001-03-23', 19350, 3, 6) ;
insert into sales values ('2001-03-29', 34200, 4, 7) ;
insert into sales values ('2001-04-02', 81000, 2, 20) ;
insert into sales values ('2001-04-07', 49500, 3, 4) ;
insert into sales values ('2001-02-12', 34200, 1, 19) ;
insert into sales values ('2001-04-15', 30600, 4, 7) ;