#---------------------------
# 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) ;