insert.sql

这是一个文件型数据库的学习资料

CREATE TABLE employees (id INTEGER PRIMARY KEY, first_name CHAR NOT NULL,last_name CHAR NOT NULL,sex CHAR NOT NULL,email CHAR NOT NULL);
CREATE TABLE clients (id INTEGER PRIMARY KEY,company_name CHAR,contact_name CHAR,telephone CHAR);
CREATE TABLE projects (code CHAR PRIMARY KEY,client_id INTEGER NOT NULL, title CHAR NOT NULL,start_date INTEGER,due_date INTEGER);
CREATE TABLE timesheets (id INTEGER PRIMARY KEY,employee_id INTEGER NOT NULL,project_code CHAR NOT NULL,date_worked INTEGER NOT NULL,hours DECIMAL(5,2)NOT NULL);
CREATE TABLE employee_rates (  employee_id INTEGER NOT NULL,  rate DECIMAL(5,2),  start_date INTEGER NOT NULL, end_date INTEGER);
INSERT INTO employees (id, first_name, last_name, sex, email)VALUES (101, 'Alex', 'Gladstone', 'M', '[e-mail]');
INSERT INTO employees (id, first_name, last_name, sex, email)VALUES (102, 'Brenda', 'Dudson', 'F', '[e-mail]');
INSERT INTO employees (id, first_name, last_name, sex, email)VALUES (103, 'Colin', 'Aynsley', 'M', '[e-mail]');
INSERT INTO employees (id, first_name, last_name, sex, email)VALUES (104, 'Debbie', 'Churchill', 'F', '[e-mail]');
INSERT INTO clients (id, company_name, contact_name, telephone)VALUES (501, 'Acme Products', 'Mr R. Runner', '555-6800');
INSERT INTO clients (id, company_name, contact_name, telephone)VALUES (502, 'ABC Enterprises', 'Mr T. Boss', '555-2999');
INSERT INTO clients (id, company_name, contact_name, telephone)VALUES (503, 'Premier Things Ltd', 'Mr U. First', '555-4001');
INSERT INTO projects (code, client_id, title, start_date, due_date)VALUES ('ACME01', 501, 'Ordering system', 20030401, 20031231);
INSERT INTO projects (code, client_id, title, start_date, due_date)VALUES ('ABCCONS', 502, 'Ongoing consultancy', 20030601, NULL);
INSERT INTO projects (code, client_id, title, start_date, due_date)VALUES ('PREM3K', 503, 'Thing 3000', 20031201, 20040430);
INSERT INTO projects (code, client_id, title, start_date, due_date)VALUES ('PREM4K', 503, 'Thing 4000', 20031201, 20040731);
INSERT INTO projects (code, client_id, title, start_date, due_date)VALUES ('PREM5K', 503, 'Thing 5000', 20031201, 20041031);
INSERT INTO employee_rates (employee_id, rate, start_date, end_date)VALUES (101, 30.00, 20030401, NULL);
INSERT INTO employee_rates (employee_id, rate, start_date, end_date)VALUES (102, 15.00, 20020601, NULL);
INSERT INTO employee_rates (employee_id, rate, start_date, end_date)VALUES (103, 25.00, 20011001, NULL);
INSERT INTO employee_rates (employee_id, rate, start_date, end_date)VALUES (104, 20.00, 20010401, 20031231);
INSERT INTO employee_rates (employee_id, rate, start_date, end_date)VALUES (104, 25.00, 20040101, NULL);
INSERT INTO timesheets (employee_id, project_code, date_worked, hours)VALUES (101, 'ACME01', 20031229, 4);
INSERT INTO timesheets (employee_id, project_code, date_worked, hours)VALUES (101, 'ABCCONS', 20031229, 2);
INSERT INTO timesheets (employee_id, project_code, date_worked, hours)VALUES (101, 'PREM3K', 20040102, 6);
INSERT INTO timesheets (employee_id, project_code, date_worked, hours)VALUES (102, 'ACME01', 20031229, 3);
INSERT INTO timesheets (employee_id, project_code, date_worked, hours)VALUES (102, 'PREM4K', 20040102, 5);
INSERT INTO timesheets (employee_id, project_code, date_worked, hours)VALUES (103, 'ABCCONS', 20031229, 2);
INSERT INTO timesheets (employee_id, project_code, date_worked, hours)VALUES (103, 'PREM4K', 20040102, 3);
INSERT INTO timesheets (employee_id, project_code, date_worked, hours)VALUES (103, 'ACME01', 20031229, 8);
INSERT INTO timesheets (employee_id, project_code, date_worked, hours)VALUES (104, 'ACME01', 20040102, 8);
INSERT INTO timesheets (employee_id, project_code, date_worked, hours)VALUES (104, 'ABCCONS', 20040102, 4);