create table course(
        courseno integer primary key,
        cname varchar(30),
        cdate date
);

create table department(
        depno integer primary key,
        dname varchar(20),
        location varchar(15),
        head integer
);

create table employee(
        empno integer primary key,
        surname varchar(15),
        forenames varchar(30),
        dob date,
        address varchar(50),
        telno varchar(20),
        depno integer
              references department (depno)
);



create table jobhistory(empno integer references employee (empno),
                        position varchar(30),
                        startdate date,
                        enddate date,
                        salary  decimal(8,2),
                        primary key (empno, position));

create table empcourse(empno integer references employee (empno),
                       courseno integer references course (courseno),
                       primary key (empno, courseno));

insert into course (courseno, cname, cdate)
       values ( 1, 'Basic Accounting','1989-01-11');
insert into course (courseno, cname, cdate)
       values ( 2, 'Further Accounting','1989-02-25');
insert into course (courseno, cname, cdate)
       values ( 3,'Issues In Administration','1988-09-27');
insert into course (courseno, cname, cdate)
       values ( 4,'More Administration','1988-10-16');
insert into course (courseno, cname, cdate)
       values ( 5,'Ada','1988-11-01');
insert into course (courseno, cname, cdate)
       values ( 6, 'Introduction To Ingres','1989-02-05');
insert into course (courseno, cname, cdate)
       values ( 7, 'New Topologies','1989-03-10');
insert into course (courseno, cname, cdate)
       values ( 8,'LANs','1988-12-12');
insert into course (courseno, cname, cdate)
       values ( 9,'Structural Stress Analysis','1989/02-20');
insert into course (courseno, cname, cdate)
       values ( 10,'Taffic Flow Analysis','1988-10-24');
insert into department (depno, dname, location, head)
       values (1,'accounts','floor 3',1);
insert into department (depno, dname, location, head)
       values (2, 'administration', 'floor 2', 1);
insert into department (depno, dname, location, head)
       values (3, 'software design', 'floor 1', 2);
insert into department (depno, dname, location, head)
       values (4, 'communications', 'floor 4', 3);
insert into department (depno, dname, location, head)
       values (5, 'engineering', 'floor 5', 7);
insert into employee (empno, surname, forenames, dob, address, telno, depno)
       values (1,'Jones','Elizabeth Barbara','1944-01-05',
       '26 Agnews Terrace, Shamrock Bay','212 337 2288',1);
insert into employee (empno, surname, forenames, dob, address, telno, depno)
       values (2,'Smith','Robert', '1947-02-07',
       '18 Marsh Street, Tollcross, Edinburgh','031 732 8972', 1);
insert into employee (empno, surname, forenames, dob, address, telno, depno)
       values (3,'White','Allan','1961-05-05',
       '6 Remote Place, North Berwick','121 555 6622', 1);
insert into employee (empno, surname, forenames, dob, address, telno, depno)
       values (4,'Reid','Gordon', '1963-08-10',
       '9 Noble Road, Penicuik', '629 424 6713', 1);
insert into employee (empno, surname, forenames, dob, address, telno, depno)
       values (5,'MacCallan','Claire', '1958-09-18',
       '25 Crisis Avenue, Leith, Edinburgh', '031 337 4166',1);
insert into employee (empno, surname, forenames, dob, address, telno, depno)
       values (6,'Murphy','Brian Charles', '1954-06-30',
       '9 Roberts Street, Biggar', '331 229 4147', 1);
insert into employee (empno, surname, forenames, dob, address, telno, depno)
       values (7,'Gibson','James', '1948-03-09',
       '11 Depressed Way, Glasgow', '041 447 8001', 2);
insert into employee (empno, surname, forenames, dob, address, telno, depno)
       values (8,'Andrews','John', '1958-01-02','73 Long Road, Lengthitown',
      '70 229 7213', 2);
insert into employee (empno, surname, forenames, dob, address, telno, depno)
       values (9,'Wright','Audrey Mary',
       '1958-02-02','10 Nile Terrace, Polwarth, Edinburgh', '031 424 7092', 2);
insert into employee (empno, surname, forenames, dob, address, telno, depno)
       values (10,'Reagan','Anne', '1961-08-17',
       '82 Longstone Road, Longstone, Edinburgh', '031 111 2799',2);
insert into employee (empno, surname, forenames, dob, address, telno, depno)
       values (11,'North','Annabel', '1962-09-01',
       '35 Marchmont Terrace, Marchmont, Edinburgh', '031 447 2266', 2);
insert into employee (empno, surname, forenames, dob, address, telno, depno)
       values (12,'South','Todd James','1959-02-28',
       '10 Shandon Road, Merchiston, Edinburgh', '031 333 1008', 2);
insert into employee (empno, surname, forenames, dob, address, telno, depno)
       values (13,'East','Ian','1942-05-13',
       '47 Colinton Road, Craighlochart, Edinburgh', '031 424 5665', 2);
insert into employee (empno, surname, forenames, dob, address, telno, depno)
       values (14,'West','Jack', '1946-05-15',
       '15 South West Gardens, Peebles','466 3176', 3);
insert into employee (empno, surname, forenames, dob, address, telno, depno)
       values (15,'Allen','Ester','1955-02-27',
       '10 Troon Street, Leith, Edinburgh', '031 424 2907', 3);
insert into employee (empno, surname, forenames, dob, address, telno, depno)
       values (16,'Brunell','Liza', '1962-08-18',
       '8 Commercial Street, Tollcross, Edinburgh','031 424 1656', 3);
insert into employee (empno, surname, forenames, dob, address, telno, depno)
       values (17,'Robertson','David Andrew', '1960-08-07',
       '9 North Loan,South Queensferry', '031 447 8213', 3);
insert into employee (empno, surname, forenames, dob, address, telno, depno)
       values (18,'Robinson','Allan','1961-05-09','31 Newall Terrace, Hawick',
       '229 0854', 3);
insert into employee (empno, surname, forenames, dob, address, telno, depno)
       values (19,'Roberts','Robert', '1959-01-01',
       '7 Peebles Road,Melrose','402 9213', 3);
insert into employee (empno, surname, forenames, dob, address, telno, depno)
       values (20,'Murray','James', '1964-03-06',
       '40 Blackhall Loan,Biggar','121 444 4580', 3);
insert into employee (empno, surname, forenames, dob, address, telno, depno)
       values (21,'Bryce','Anne', '1965-09-13',
       '22 Forresthill Place, Greyfriars, Edinburgh','031 402 6666', 3);
insert into employee (empno, surname, forenames, dob, address, telno, depno)
       values (22,'Cowan','Audrey', '1940-04-03',
       '12 Down Street, Brayend', '228 9321', 4);
insert into employee (empno, surname, forenames, dob, address, telno, depno)
       values (23,'Stevenson','John David', '1954-12-12',
       '16 Rubber Road,Stampingham', '337 6262', 4);
insert into employee (empno, surname, forenames, dob, address, telno, depno)
       values (24,'Dickson','William', '1950-01-06',
       '11 Newplace Road, Newton', '041 444 6730', 4);
insert into employee (empno, surname, forenames, dob, address, telno, depno)
       values (25,'Brownlie','Matthew',	'1954-05-14',
       '80 Cowan Road, Shandon, Edinburgh', '031 228 4141', 4);
insert into employee (empno, surname, forenames, dob, address, telno, depno)
       values (26,'Gordon','Mark','1960-02-12',
       '10 Whyte Place, North Berwick','424 1024', 4);
insert into employee (empno, surname, forenames, dob, address, telno, depno)
       values (27,'Kennedy','Luke','1952-06-26',
       '14 Bucket Street, Musselburgh','031 414 7312', 5);
insert into employee (empno, surname, forenames, dob, address, telno, depno)
       values (28,'Grant','Lynn','1956-07-12','3 Dey Terrace, Selkirk',
       '337 8911', 5);
insert into employee (empno, surname, forenames, dob, address, telno, depno)
       values (29,'MacDonald','Elizabeth', '1968-09-03',
       '23 Neidpath Road, Peebles', '444 8998',5);
insert into employee (empno, surname, forenames, dob, address, telno, depno)
       values (30,'MacDugle','Shirley', '1964-10-09',
       '8 Rosebank Terrace, Shandon, Edinburgh', '031 447 1189', 5);
insert into employee (empno, surname, forenames, dob, address, telno, depno)
       values (31,'Douglas','Chris', '1955-06-23',
       '19 High Street, Livingston', '031 229 6792', 5);
insert into employee (empno, surname, forenames, dob, address, telno, depno)
       values (32,'Christie','Malcolm',	'1959-07-25',
       '11 Rounders Park, Polwarth, Edinburgh','031 424 8406', 5);
insert into jobhistory (empno, position, startdate, enddate, salary)
       values (1, 'Accounts Manager', '1976-01-12', NULL, 30000.00);
insert into jobhistory (empno, position, startdate, enddate, salary)
       values (1, 'Assistant Accounts Manager',	'1972-02-11', '1976-01-12',
       22000.00);
insert into jobhistory (empno, position, startdate, enddate, salary)
       values (1, 'Accountant',	'1968-03-10','1972-02-11',  15000.00);
insert into jobhistory (empno, position, startdate, enddate, salary)
       values (1, 'Junior Accountant','1964-04-09','1968-03-10', 6000.00);
insert into jobhistory (empno, position, startdate, enddate, salary)
       values (2, 'Assistant Accounts Manager',	'1976-05-08', NULL, 25000.00);
insert into jobhistory (empno, position, startdate, enddate, salary)
       values (2, 'Accountant',	'1971-06-07', '1976-05-08', 16000.00);
insert into jobhistory (empno, position, startdate, enddate, salary)
       values (2,  'Junior Accountant', '1967-07-06', '1971-06-07',
       8000.00);
insert into jobhistory (empno, position, startdate, enddate, salary)
       values (3, 'Accountant', '1984-08-05', NULL, 16000.00);
insert into jobhistory (empno, position, startdate, enddate, salary)
       values (3, 'Junior Accountant', '1981-09-04','1984-08-05', 8000.00);
insert into jobhistory (empno, position, startdate, enddate, salary)
       values (4, 'Accountant', '1989-10-05', NULL, 16000.00);
insert into jobhistory (empno, position, startdate, enddate, salary)
       values (5,'Accountant','1980-11-02',NULL, 16000.00);
insert into jobhistory (empno, position, startdate, enddate, salary)
       values (5, 'Junior Accountant','1978-12-01','1980-11-02', 8000.00);
insert into jobhistory (empno, position, startdate, enddate, salary)
       values (6, 'Accountant', '1980-01-12', NULL, 16000.00);
insert into jobhistory (empno, position, startdate, enddate, salary)
       values (6, 'Junior Accountant', '1974-02-11', '1980-01-12',
       8000.00);
insert into jobhistory (empno, position, startdate, enddate, salary)
       values (7, 'Admin Manager', '1980-03-10',NULL, 30000.00);
insert into jobhistory (empno, position, startdate, enddate, salary)
       values (7, 'Assistant Admin Manager', '1974-04-09', '1980-03-10',
       22000.00);
insert into jobhistory (empno, position, startdate, enddate, salary)
       values (7, 'Senior Admin Assistant', '1968-12-01', '1974-04-09',
       16000.00);
insert into jobhistory (empno, position, startdate, enddate, salary)
       values (7, 'Admin Assistant', '1960-05-07', '1968-12-01', 10000.00);
insert into jobhistory (empno, position, startdate, enddate, salary)
       values (8, 'Assistant Admin Manager', '1980-03-10', NULL, 20000.00);
insert into jobhistory (empno, position, startdate, enddate, salary)
       values (8, 'Senior Admin Assistant', '1977-04-09', '1980-03-10',
       12000.00);
insert into jobhistory (empno, position, startdate, enddate, salary)
       values (8, 'Admin Assistant', '1976-12-01', '1977-04-09', 8000.00);
insert into jobhistory (empno, position, startdate, enddate, salary)
       values (9, 'Senior Admin Assistant', '1988-07-10', NULL, 14000.00);
insert into jobhistory (empno, position, startdate, enddate, salary)
       values (9, 'Admin Assistant', '1982-06-08', '1988-07-10',  10000.00);
insert into jobhistory (empno, position, startdate, enddate, salary)
       values (10,'Admin Assistant', '1986-09-06', NULL, 12000.00);
insert into jobhistory (empno, position, startdate, enddate, salary)
       values (10,'Junior Admin Assistant', '1983-09-22','1986-09-06',
       6000.00);
insert into jobhistory (empno, position, startdate, enddate, salary)
       values (11,'Admin Assistant', '1982-03-13', NULL, 15000.00);
insert into jobhistory (empno, position, startdate, enddate, salary)
       values (11,'Junior Admin Assistant', '1980-10-23', '1982-03-13',
       8000.00);
insert into jobhistory (empno, position, startdate, enddate, salary)
       values (12,'Admin Assistant','1982-02-26', NULL, 15000.00);
insert into jobhistory (empno, position, startdate, enddate, salary)
       values (12,'Junior Admin Assistant', '1977-01-03', '1982-02-26',
       6500.00);
insert into jobhistory (empno, position, startdate, enddate, salary)
       values (13,'Admin Assistant', '1988-04-17', NULL, 16000.00);
insert into jobhistory (empno, position, startdate, enddate, salary)
       values (14,'Admin Assistant', '1985-11-14', NULL, 16000.00);
insert into jobhistory (empno, position, startdate, enddate, salary)
       values (15,'Senior Systems Analyst', '1988-08-21', NULL, 35000.00);
insert into jobhistory (empno, position, startdate, enddate, salary)
       values (15,'Systems Analyst', '1986-02-09','1988-08-21', 31000.00);
insert into jobhistory (empno, position, startdate, enddate, salary)
       values (15,'Analyst Programmer', '1984-01-17', '1986-02-09', 25000.00);
insert into jobhistory (empno, position, startdate, enddate, salary)
       values (15, 'Programmer', '1980-03-13', '1984-01-17', 16000.00);
insert into jobhistory (empno, position, startdate, enddate, salary)
       values (16,'Systems Analyst', '1991-04-25', NULL, 30000.00);
insert into jobhistory (empno, position, startdate, enddate, salary)
       values (16,'Analyst Programmer',	'1986-11-16', '1991-04-25',
       21000.00);
insert into jobhistory (empno, position, startdate, enddate, salary)
       values (16,'Programmer',	'1984-10-01', '1986-11-16', 16000.00);
insert into jobhistory (empno, position, startdate, enddate, salary)
       values (17,'Analyst Programmer',	'1988-01-17', NULL,  21000.00);
insert into jobhistory (empno, position, startdate, enddate, salary)
       values (17,'Programmer', '1984-10-01','1988-01-17', 16000.00);
insert into jobhistory (empno, position, startdate, enddate, salary)
       values (18,'Analyst Programmer',	'1988-09-23', NULL,  22000.00);
insert into jobhistory (empno, position, startdate, enddate, salary)
       values (18,'Programmer',	'1984-10-01','1988-09-23', 16000.00);
insert into jobhistory (empno, position, startdate, enddate, salary)
       values (19,'Analyst Programmer',	'1988-09-23', NULL, 22000.00);
insert into jobhistory (empno, position, startdate, enddate, salary)
       values (19,'Programmer',	'1984-10-01','1988-09-23'  , 16000.00);
insert into jobhistory (empno, position, startdate, enddate, salary)
       values (20,'Analyst Programmer',	'1988-09-23', NULL,  22000.00);
insert into jobhistory (empno, position, startdate, enddate, salary)
       values (20,'Programmer',	'1984-10-01' ,'1988-09-23' , 16000.00);
insert into jobhistory (empno, position, startdate, enddate, salary)
       values (21,'Analyst Programmer',	'1991-12-13', NULL, 25000.00);
insert into jobhistory (empno, position, startdate, enddate, salary)
       values (21,'Programmer',	'1989-09-11','1991-12-13', 21000.00);
insert into jobhistory (empno, position, startdate, enddate, salary)
       values (22,'Communications Manager', '1985-08-03', NULL, 36000.00);
insert into jobhistory (empno, position, startdate, enddate, salary)
       values (22,'Senior Communications Engineer', '1977-06-21','1985-08-03',
       26000.00);
insert into jobhistory (empno, position, startdate, enddate, salary)
       values (22,'Communications Engineer', '1975-05-13', '1977-06-21',
       12000.00);
insert into jobhistory (empno, position, startdate, enddate, salary)
       values (22,'Electrical Engineer', '1966-02-08', '1975-05-13' ,
       8000.00);
insert into jobhistory (empno, position, startdate, enddate, salary)
       values (23,'Senior Communications Engineer', '1985-08-03', NULL,
      18000.00);
insert into jobhistory (empno, position, startdate, enddate, salary)
       values (23,'Communications Engineer','1979-06-21', '1985-08-03',
       9000.00);
insert into jobhistory (empno, position, startdate, enddate, salary)
       values (23,'Software Engineer', '1977-02-14', '1979-06-21', 5000.00);
insert into jobhistory (empno, position, startdate, enddate, salary)
       values (24,'Communications Engineer','1985-10-08', NULL, 15000.00);
insert into jobhistory (empno, position, startdate, enddate, salary)
       values (24,'Electrical Engineer', '1980-05-06', '1985-10-08',
       13000.00);
insert into jobhistory (empno, position, startdate, enddate, salary)
       values (25,'Communications Engineer', '1988-10-01', NULL, 19000.00);
insert into jobhistory (empno, position, startdate, enddate, salary)
       values (25,'Software Engineer', '1985-08-25','1988-10-01', 16000.00);
insert into jobhistory (empno, position, startdate, enddate, salary)
       values (25,'Systems Programmer',	'1983-01-29', '1985-08-25',
       11000.00);
insert into jobhistory (empno, position, startdate, enddate, salary)
       values (25,'Programmer',	'1980-09-14', '1983-01-29', 9000.00);
insert into jobhistory (empno, position, startdate, enddate, salary)
       values (26,'Communications Engineer', '1988-03-13', NULL, 19500.00);
insert into jobhistory (empno, position, startdate, enddate, salary)
       values (26,'Software Engineer', '1985-08-30','1988-03-13', 17000.00);
insert into jobhistory (empno, position, startdate, enddate, salary)
       values (26,'Trainee Engineer', '1982-09-13','1985-08-30', 11000.00);
insert into jobhistory (empno, position, startdate, enddate, salary)
       values (26,'Electronic Engineer', '1980-09-01','1982-09-13', 9000.00);
insert into jobhistory (empno, position, startdate, enddate, salary)
       values (27,'Senior Chief Engineer', '1990-11-01', NULL, 25000.00);
insert into jobhistory (empno, position, startdate, enddate, salary)
       values (27,'Chief Engineer', '1986-06-20','1990-11-01', 17000.00);
insert into jobhistory (empno, position, startdate, enddate, salary)
       values (27,'Mechanical Engineer', '1981-04-19' ,'1986-06-20',
       11000.00);
insert into jobhistory (empno, position, startdate, enddate, salary)
       values (27,'Graduate Engineer', '1975-09-13','1981-04-19', 6000.00);
insert into jobhistory (empno, position, startdate, enddate, salary)
       values (28,'Chief Engineer', '1989-07-06', NULL, 29000.00);
insert into jobhistory (empno, position, startdate, enddate, salary)
       values (28,'Civil Engineer', '1984-04-17', '1989-07-06', 17000.00);
insert into jobhistory (empno, position, startdate, enddate, salary)
       values (28,'Trainee Engineer', '1978-12-10','1984-04-17', 12000.00);
insert into jobhistory (empno, position, startdate, enddate, salary)
       values (28,'Graduate Engineer','1978-09-01','1978-12-10', 3000.00);
insert into jobhistory (empno, position, startdate, enddate, salary)
       values (29,'Electrical Engineer', '1986-02-09', NULL, 17000.00);
insert into jobhistory (empno, position, startdate, enddate, salary)
       values (29,'Graduate Engineer',	'1980-10-03','1986-02-09',
       14000.00);
insert into jobhistory (empno, position, startdate, enddate, salary)
       values (30,'Senior Electrical Engineer',	'1992-04-30', NULL,  22000.00);
insert into jobhistory (empno, position, startdate, enddate, salary)
       values (30,'Electrical Engineer', '1987-03-06','1992-04-30',
       17000.00);
insert into jobhistory (empno, position, startdate, enddate, salary)
       values (31,'Senior Mechanical Engineer',	'1987-05-30',NULL, 22000.00);
insert into jobhistory (empno, position, startdate, enddate, salary)
       values (31,'Mechanical Engineer', '1980-06-06', '1987-05-30',
       17000.00);
insert into jobhistory (empno, position, startdate, enddate, salary)
       values (32,'Systems Engineer', '1991-03-01', NULL, 19000.00);
insert into jobhistory (empno, position, startdate, enddate, salary)
       values (32,'Electronics Engineer', '1989-10-31','1991-03-01',
       18000.00);

insert into empcourse(empno, courseno) values (1, 1);

insert into empcourse(empno, courseno) values (1, 2);

insert into empcourse(empno, courseno) values (2, 1);

insert into empcourse(empno, courseno) values (2, 2);

insert into empcourse(empno, courseno) values (7, 3);

insert into empcourse(empno, courseno) values (7,   4);

insert into empcourse(empno, courseno) values (8, 3);

insert into empcourse(empno, courseno) values (8, 4);

insert into empcourse(empno, courseno) values (14, 5);

insert into empcourse(empno, courseno) values (14, 6);

insert into empcourse(empno, courseno) values (15, 5);

insert into empcourse(empno, courseno) values (15, 6);

insert into empcourse(empno, courseno) values (21, 7);

insert into empcourse(empno, courseno) values (22, 7);

insert into empcourse(empno, courseno) values (22, 8);

insert into empcourse(empno, courseno) values (19, 5);

insert into empcourse(empno, courseno) values (19, 6);