SQL範例資料庫
在本教學中，我們將向您介紹在整個教學中使用的SQL範例資料庫。以下資料庫關係圖 - HR 範例資料庫：
在這個HR 範例資料庫有7個表：
employees表儲存員工的資料資訊。jobs表儲存工作資料資訊，包括職位和工資範圍。departments表儲存部門資料資訊。dependents表儲存員工的家屬資訊。locations表儲存公司各部門的所在位置資訊。countries表儲存公司開展業務的國家/地區的資料。regions表儲存亞洲，歐洲，美洲，中東和非洲等地區的資料。 這些國家分為不同的地區。
下圖顯示了表名稱及其記錄資料數量。
| 編號 | 表名稱 | 行數 |
|---|---|---|
| 1 | employees |
40 |
| 2 | dependents |
30 |
| 3 | departments |
11 |
| 4 | jobs |
19 |
| 5 | locations |
7 |
| 6 | countries |
25 |
| 7 | regions |
4 |
通常，要使用SQL，需要安裝關聯式資料庫管理系統(RDBMS)。 如果您使用過MySQL，PostgreSQL，Oracle資料庫，SQL Server或SQLite等關聯式資料庫管理系統(RDBMS)，則可以使用以下對應指令碼建立範例資料庫。
1. MySQL
以下SQL指令碼是用於在MySQL中建立HR 範例資料庫。
CREATE TABLE regions (
region_id INT (11) AUTO_INCREMENT PRIMARY KEY,
region_name VARCHAR (25) DEFAULT NULL
);
CREATE TABLE countries (
country_id CHAR (2) PRIMARY KEY,
country_name VARCHAR (40) DEFAULT NULL,
region_id INT (11) NOT NULL,
FOREIGN KEY (region_id) REFERENCES regions (region_id) ON DELETE CASCADE ON UPDATE CASCADE
);
CREATE TABLE locations (
location_id INT (11) AUTO_INCREMENT PRIMARY KEY,
street_address VARCHAR (40) DEFAULT NULL,
postal_code VARCHAR (12) DEFAULT NULL,
city VARCHAR (30) NOT NULL,
state_province VARCHAR (25) DEFAULT NULL,
country_id CHAR (2) NOT NULL,
FOREIGN KEY (country_id) REFERENCES countries (country_id) ON DELETE CASCADE ON UPDATE CASCADE
);
CREATE TABLE jobs (
job_id INT (11) AUTO_INCREMENT PRIMARY KEY,
job_title VARCHAR (35) NOT NULL,
min_salary DECIMAL (8, 2) DEFAULT NULL,
max_salary DECIMAL (8, 2) DEFAULT NULL
);
CREATE TABLE departments (
department_id INT (11) AUTO_INCREMENT PRIMARY KEY,
department_name VARCHAR (30) NOT NULL,
location_id INT (11) DEFAULT NULL,
FOREIGN KEY (location_id) REFERENCES locations (location_id) ON DELETE CASCADE ON UPDATE CASCADE
);
CREATE TABLE employees (
employee_id INT (11) AUTO_INCREMENT PRIMARY KEY,
first_name VARCHAR (20) DEFAULT NULL,
last_name VARCHAR (25) NOT NULL,
email VARCHAR (100) NOT NULL,
phone_number VARCHAR (20) DEFAULT NULL,
hire_date DATE NOT NULL,
job_id INT (11) NOT NULL,
salary DECIMAL (8, 2) NOT NULL,
manager_id INT (11) DEFAULT NULL,
department_id INT (11) DEFAULT NULL,
FOREIGN KEY (job_id) REFERENCES jobs (job_id) ON DELETE CASCADE ON UPDATE CASCADE,
FOREIGN KEY (department_id) REFERENCES departments (department_id) ON DELETE CASCADE ON UPDATE CASCADE,
FOREIGN KEY (manager_id) REFERENCES employees (employee_id)
);
CREATE TABLE dependents (
dependent_id INT (11) AUTO_INCREMENT PRIMARY KEY,
first_name VARCHAR (50) NOT NULL,
last_name VARCHAR (50) NOT NULL,
relationship VARCHAR (25) NOT NULL,
employee_id INT (11) NOT NULL,
FOREIGN KEY (employee_id) REFERENCES employees (employee_id) ON DELETE CASCADE ON UPDATE CASCADE
);
2. PostgreSQL
以下指令碼用於在PostgreSQL中建立HR 範例資料庫結構。
CREATE TABLE regions (
region_id SERIAL PRIMARY KEY,
region_name CHARACTER VARYING (25)
);
CREATE TABLE countries (
country_id CHARACTER (2) PRIMARY KEY,
country_name CHARACTER VARYING (40),
region_id INTEGER NOT NULL,
FOREIGN KEY (region_id) REFERENCES regions (region_id) ON UPDATE CASCADE ON DELETE CASCADE
);
CREATE TABLE locations (
location_id SERIAL PRIMARY KEY,
street_address CHARACTER VARYING (40),
postal_code CHARACTER VARYING (12),
city CHARACTER VARYING (30) NOT NULL,
state_province CHARACTER VARYING (25),
country_id CHARACTER (2) NOT NULL,
FOREIGN KEY (country_id) REFERENCES countries (country_id) ON UPDATE CASCADE ON DELETE CASCADE
);
CREATE TABLE departments (
department_id SERIAL PRIMARY KEY,
department_name CHARACTER VARYING (30) NOT NULL,
location_id INTEGER,
FOREIGN KEY (location_id) REFERENCES locations (location_id) ON UPDATE CASCADE ON DELETE CASCADE
);
CREATE TABLE jobs (
job_id SERIAL PRIMARY KEY,
job_title CHARACTER VARYING (35) NOT NULL,
min_salary NUMERIC (8, 2),
max_salary NUMERIC (8, 2)
);
CREATE TABLE employees (
employee_id SERIAL PRIMARY KEY,
first_name CHARACTER VARYING (20),
last_name CHARACTER VARYING (25) NOT NULL,
email CHARACTER VARYING (100) NOT NULL,
phone_number CHARACTER VARYING (20),
hire_date DATE NOT NULL,
job_id INTEGER NOT NULL,
salary NUMERIC (8, 2) NOT NULL,
manager_id INTEGER,
department_id INTEGER,
FOREIGN KEY (job_id) REFERENCES jobs (job_id) ON UPDATE CASCADE ON DELETE CASCADE,
FOREIGN KEY (department_id) REFERENCES departments (department_id) ON UPDATE CASCADE ON DELETE CASCADE,
FOREIGN KEY (manager_id) REFERENCES employees (employee_id) ON UPDATE CASCADE ON DELETE CASCADE
);
CREATE TABLE dependents (
dependent_id SERIAL PRIMARY KEY,
first_name CHARACTER VARYING (50) NOT NULL,
last_name CHARACTER VARYING (50) NOT NULL,
relationship CHARACTER VARYING (25) NOT NULL,
employee_id INTEGER NOT NULL,
FOREIGN KEY (employee_id) REFERENCES employees (employee_id) ON DELETE CASCADE ON UPDATE CASCADE
);
3. Microsoft SQL Server
以下指令碼用於在Microsoft SQL Server中建立HR 範例資料庫結構。
CREATE TABLE regions (
region_id INT IDENTITY(1,1) PRIMARY KEY,
region_name VARCHAR (25) DEFAULT NULL
);
CREATE TABLE countries (
country_id CHAR (2) PRIMARY KEY,
country_name VARCHAR (40) DEFAULT NULL,
region_id INT NOT NULL,
FOREIGN KEY (region_id) REFERENCES regions (region_id) ON DELETE CASCADE ON UPDATE CASCADE
);
CREATE TABLE locations (
location_id INT IDENTITY(1,1) PRIMARY KEY,
street_address VARCHAR (40) DEFAULT NULL,
postal_code VARCHAR (12) DEFAULT NULL,
city VARCHAR (30) NOT NULL,
state_province VARCHAR (25) DEFAULT NULL,
country_id CHAR (2) NOT NULL,
FOREIGN KEY (country_id) REFERENCES countries (country_id) ON DELETE CASCADE ON UPDATE CASCADE
);
CREATE TABLE jobs (
job_id INT IDENTITY(1,1) PRIMARY KEY,
job_title VARCHAR (35) NOT NULL,
min_salary DECIMAL (8, 2) DEFAULT NULL,
max_salary DECIMAL (8, 2) DEFAULT NULL
);
CREATE TABLE departments (
department_id INT IDENTITY(1,1) PRIMARY KEY,
department_name VARCHAR (30) NOT NULL,
location_id INT DEFAULT NULL,
FOREIGN KEY (location_id) REFERENCES locations (location_id) ON DELETE CASCADE ON UPDATE CASCADE
);
CREATE TABLE employees (
employee_id INT IDENTITY(1,1) PRIMARY KEY,
first_name VARCHAR (20) DEFAULT NULL,
last_name VARCHAR (25) NOT NULL,
email VARCHAR (100) NOT NULL,
phone_number VARCHAR (20) DEFAULT NULL,
hire_date DATE NOT NULL,
job_id INT NOT NULL,
salary DECIMAL (8, 2) NOT NULL,
manager_id INT DEFAULT NULL,
department_id INT DEFAULT NULL,
FOREIGN KEY (job_id) REFERENCES jobs (job_id) ON DELETE CASCADE ON UPDATE CASCADE,
FOREIGN KEY (department_id) REFERENCES departments (department_id) ON DELETE CASCADE ON UPDATE CASCADE,
FOREIGN KEY (manager_id) REFERENCES employees (employee_id)
);
CREATE TABLE dependents (
dependent_id INT IDENTITY(1,1) PRIMARY KEY,
first_name VARCHAR (50) NOT NULL,
last_name VARCHAR (50) NOT NULL,
relationship VARCHAR (25) NOT NULL,
employee_id INT NOT NULL,
FOREIGN KEY (employee_id) REFERENCES employees (employee_id) ON DELETE CASCADE ON UPDATE CASCADE
);
4. Oracle資料庫(> 12c)
以下指令碼用於在Oracle Database 12c中建立HR 範例資料庫結構。
CREATE TABLE regions (
region_id NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
region_name VARCHAR2 (25) DEFAULT NULL
);
CREATE TABLE countries (
country_id CHAR (2) PRIMARY KEY,
country_name VARCHAR2 (40) DEFAULT NULL,
region_id NUMBER NOT NULL,
FOREIGN KEY (region_id) REFERENCES regions (region_id) ON DELETE CASCADE
);
CREATE TABLE locations (
location_id NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
street_address VARCHAR2 (40) DEFAULT NULL,
postal_code VARCHAR2 (12) DEFAULT NULL,
city VARCHAR2 (30) NOT NULL,
state_province VARCHAR2 (25) DEFAULT NULL,
country_id CHAR (2) NOT NULL,
FOREIGN KEY (country_id) REFERENCES countries (country_id) ON DELETE CASCADE
);
CREATE TABLE jobs (
job_id NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
job_title VARCHAR2 (35) NOT NULL,
min_salary NUMBER (8, 2) DEFAULT NULL,
max_salary NUMBER (8, 2) DEFAULT NULL
);
CREATE TABLE departments (
department_id NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
department_name VARCHAR2 (30) NOT NULL,
location_id NUMBER DEFAULT NULL,
FOREIGN KEY (location_id) REFERENCES locations (location_id) ON DELETE CASCADE
);
CREATE TABLE employees (
employee_id NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
first_name VARCHAR2 (20) DEFAULT NULL,
last_name VARCHAR2 (25) NOT NULL,
email VARCHAR2 (100) NOT NULL,
phone_number VARCHAR2 (20) DEFAULT NULL,
hire_date DATE NOT NULL,
job_id NUMBER NOT NULL,
salary NUMBER (8, 2) NOT NULL,
manager_id NUMBER DEFAULT NULL,
department_id NUMBER DEFAULT NULL,
FOREIGN KEY (job_id) REFERENCES jobs (job_id) ON DELETE CASCADE,
FOREIGN KEY (department_id) REFERENCES departments (department_id) ON DELETE CASCADE,
FOREIGN KEY (manager_id) REFERENCES employees (employee_id)
);
CREATE TABLE dependents (
dependent_id NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
first_name VARCHAR2 (50) NOT NULL,
last_name VARCHAR2 (50) NOT NULL,
relationship VARCHAR2 (25) NOT NULL,
employee_id NUMBER NOT NULL,
FOREIGN KEY (employee_id) REFERENCES employees (employee_id) ON DELETE CASCADE
);
5. SQLite
以下指令碼用於在SQLite中建立HR 範例資料庫結構。
CREATE TABLE regions (
region_id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
region_name text NOT NULL
);
CREATE TABLE countries (
country_id text NOT NULL,
country_name text NOT NULL,
region_id INTEGER NOT NULL,
PRIMARY KEY (country_id ASC),
FOREIGN KEY (region_id) REFERENCES regions (region_id) ON DELETE CASCADE ON UPDATE CASCADE
);
CREATE TABLE locations (
location_id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
street_address text,
postal_code text,
city text NOT NULL,
state_province text,
country_id INTEGER NOT NULL,
FOREIGN KEY (country_id) REFERENCES countries (country_id) ON DELETE CASCADE ON UPDATE CASCADE
);
CREATE TABLE departments (
department_id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
department_name text NOT NULL,
location_id INTEGER NOT NULL,
FOREIGN KEY (location_id) REFERENCES locations (location_id) ON DELETE CASCADE ON UPDATE CASCADE
);
CREATE TABLE jobs (
job_id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
job_title text NOT NULL,
min_salary double NOT NULL,
max_salary double NOT NULL
);
CREATE TABLE employees (
employee_id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
first_name text,
last_name text NOT NULL,
email text NOT NULL,
phone_number text,
hire_date text NOT NULL,
job_id INTEGER NOT NULL,
salary double NOT NULL,
manager_id INTEGER,
department_id INTEGER NOT NULL,
FOREIGN KEY (job_id) REFERENCES jobs (job_id) ON DELETE CASCADE ON UPDATE CASCADE,
FOREIGN KEY (department_id) REFERENCES departments (department_id) ON DELETE CASCADE ON UPDATE CASCADE,
FOREIGN KEY (manager_id) REFERENCES employees (employee_id) ON DELETE CASCADE ON UPDATE CASCADE
);
CREATE TABLE dependents (
dependent_id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
first_name text NOT NULL,
last_name text NOT NULL,
relationship text NOT NULL,
employee_id INTEGER NOT NULL,
FOREIGN KEY (employee_id) REFERENCES employees (employee_id) ON DELETE CASCADE ON UPDATE CASCADE
);
6. 匯入資料
以下指令碼用於將資料載入到上面HR範例資料庫建立的表中。
/*Data for the table regions */
INSERT INTO regions(region_id,region_name) VALUES (1,'歐洲');
INSERT INTO regions(region_id,region_name) VALUES (2,'美洲');
INSERT INTO regions(region_id,region_name) VALUES (3,'亞洲');
INSERT INTO regions(region_id,region_name) VALUES (4,'中東和非洲');
/*Data for the table countries */
INSERT INTO countries(country_id,country_name,region_id) VALUES ('AR','阿根廷',2);
INSERT INTO countries(country_id,country_name,region_id) VALUES ('AU','澳大利亞',3);
INSERT INTO countries(country_id,country_name,region_id) VALUES ('BE','比利時',1);
INSERT INTO countries(country_id,country_name,region_id) VALUES ('BR','巴西',2);
INSERT INTO countries(country_id,country_name,region_id) VALUES ('CA','加拿大',2);
INSERT INTO countries(country_id,country_name,region_id) VALUES ('CH','瑞士',1);
INSERT INTO countries(country_id,country_name,region_id) VALUES ('CN','中國',3);
INSERT INTO countries(country_id,country_name,region_id) VALUES ('DE','德國',1);
INSERT INTO countries(country_id,country_name,region_id) VALUES ('DK','丹麥',1);
INSERT INTO countries(country_id,country_name,region_id) VALUES ('EG','埃及',4);
INSERT INTO countries(country_id,country_name,region_id) VALUES ('FR','法國',1);
INSERT INTO countries(country_id,country_name,region_id) VALUES ('HK','香港',3);
INSERT INTO countries(country_id,country_name,region_id) VALUES ('IL','以色列',4);
INSERT INTO countries(country_id,country_name,region_id) VALUES ('IN','印度',3);
INSERT INTO countries(country_id,country_name,region_id) VALUES ('IT','意大利',1);
INSERT INTO countries(country_id,country_name,region_id) VALUES ('JP','日本',3);
INSERT INTO countries(country_id,country_name,region_id) VALUES ('KW','科威特',4);
INSERT INTO countries(country_id,country_name,region_id) VALUES ('MX','墨西哥',2);
INSERT INTO countries(country_id,country_name,region_id) VALUES ('NG','尼日利亞',4);
INSERT INTO countries(country_id,country_name,region_id) VALUES ('NL','荷蘭',1);
INSERT INTO countries(country_id,country_name,region_id) VALUES ('SG','新加坡',3);
INSERT INTO countries(country_id,country_name,region_id) VALUES ('UK','英國',1);
INSERT INTO countries(country_id,country_name,region_id) VALUES ('US','美國',2);
INSERT INTO countries(country_id,country_name,region_id) VALUES ('ZM','贊比亞',4);
INSERT INTO countries(country_id,country_name,region_id) VALUES ('ZW','津巴布韋',4);
/*Data for the table locations */
INSERT INTO locations(location_id,street_address,postal_code,city,state_province,country_id) VALUES (1400,'2014 Jabberwocky Rd','26192','Southlake','Texas','US');
INSERT INTO locations(location_id,street_address,postal_code,city,state_province,country_id) VALUES (1500,'2011 Interiors Blvd','99236','South San Francisco','California','US');
INSERT INTO locations(location_id,street_address,postal_code,city,state_province,country_id) VALUES (1700,'2004 Charade Rd','98199','Seattle','Washington','US');
INSERT INTO locations(location_id,street_address,postal_code,city,state_province,country_id) VALUES (1800,'147 Spadina Ave','M5V 2L7','Toronto','Ontario','CA');
INSERT INTO locations(location_id,street_address,postal_code,city,state_province,country_id) VALUES (2400,'8204 Arthur St',NULL,'London',NULL,'UK');
INSERT INTO locations(location_id,street_address,postal_code,city,state_province,country_id) VALUES (2500,'Magdalen Centre, The Oxford Science Park','OX9 9ZB','Oxford','Oxford','UK');
INSERT INTO locations(location_id,street_address,postal_code,city,state_province,country_id) VALUES (2700,'Schwanthalerstr. 7031','80925','Munich','Bavaria','DE');
/*Data for the table jobs */
INSERT INTO jobs(job_id,job_title,min_salary,max_salary) VALUES (1,'會計師',4200.00,9000.00);
INSERT INTO jobs(job_id,job_title,min_salary,max_salary) VALUES (2,'會計經理',8200.00,16000.00);
INSERT INTO jobs(job_id,job_title,min_salary,max_salary) VALUES (3,'行政助理',3000.00,6000.00);
INSERT INTO jobs(job_id,job_title,min_salary,max_salary) VALUES (4,'主席',20000.00,40000.00);
INSERT INTO jobs(job_id,job_title,min_salary,max_salary) VALUES (5,'行政副主席',15000.00,30000.00);
INSERT INTO jobs(job_id,job_title,min_salary,max_salary) VALUES (6,'會計',4200.00,9000.00);
INSERT INTO jobs(job_id,job_title,min_salary,max_salary) VALUES (7,'財務經理',8200.00,16000.00);
INSERT INTO jobs(job_id,job_title,min_salary,max_salary) VALUES (8,'人力資源代表',4000.00,9000.00);
INSERT INTO jobs(job_id,job_title,min_salary,max_salary) VALUES (9,'程式員',4000.00,10000.00);
INSERT INTO jobs(job_id,job_title,min_salary,max_salary) VALUES (10,'市場經理',9000.00,15000.00);
INSERT INTO jobs(job_id,job_title,min_salary,max_salary) VALUES (11,'市場代表',4000.00,9000.00);
INSERT INTO jobs(job_id,job_title,min_salary,max_salary) VALUES (12,'公關代表',4500.00,10500.00);
INSERT INTO jobs(job_id,job_title,min_salary,max_salary) VALUES (13,'採購職員',2500.00,5500.00);
INSERT INTO jobs(job_id,job_title,min_salary,max_salary) VALUES (14,'採購經理',8000.00,15000.00);
INSERT INTO jobs(job_id,job_title,min_salary,max_salary) VALUES (15,'銷售經理',10000.00,20000.00);
INSERT INTO jobs(job_id,job_title,min_salary,max_salary) VALUES (16,'銷售代表',6000.00,12000.00);
INSERT INTO jobs(job_id,job_title,min_salary,max_salary) VALUES (17,'運輸職員',2500.00,5500.00);
INSERT INTO jobs(job_id,job_title,min_salary,max_salary) VALUES (18,'庫存職員',2000.00,5000.00);
INSERT INTO jobs(job_id,job_title,min_salary,max_salary) VALUES (19,'庫存管理',5500.00,8500.00);
/*Data for the table departments */
INSERT INTO departments(department_id,department_name,location_id) VALUES (1,'管理',1700);
INSERT INTO departments(department_id,department_name,location_id) VALUES (2,'市場行銷',1800);
INSERT INTO departments(department_id,department_name,location_id) VALUES (3,'採購',1700);
INSERT INTO departments(department_id,department_name,location_id) VALUES (4,'人力資源',2400);
INSERT INTO departments(department_id,department_name,location_id) VALUES (5,'運輸',1500);
INSERT INTO departments(department_id,department_name,location_id) VALUES (6,'IT',1400);
INSERT INTO departments(department_id,department_name,location_id) VALUES (7,'公共關係',2700);
INSERT INTO departments(department_id,department_name,location_id) VALUES (8,'銷售',2500);
INSERT INTO departments(department_id,department_name,location_id) VALUES (9,'行政人員',1700);
INSERT INTO departments(department_id,department_name,location_id) VALUES (10,'財務',1700);
INSERT INTO departments(department_id,department_name,location_id) VALUES (11,'會計',1700);
/*Data for the table employees */
INSERT INTO `employees` VALUES ('100', 'Steven', 'Lee', '[email protected]', '0532-86011111', '1987-06-17', '4', '24000.00', null, '9');
INSERT INTO `employees` VALUES ('101', 'Neena', 'Wong', '[email protected]', '0551-4243311', '1989-09-21', '5', '17000.00', '100', '9');
INSERT INTO `employees` VALUES ('102', 'Lex', 'Liang', '[email protected]', '0571-87622362', '1993-01-13', '5', '17000.00', '100', '9');
INSERT INTO `employees` VALUES ('103', 'Alexander', 'Lee', '[email protected]', '020-95105105', '1990-01-03', '9', '9000.00', '102', '6');
INSERT INTO `employees` VALUES ('104', 'Bruce', 'Wong', '[email protected]', '0371-68356666', '1991-05-21', '9', '6000.00', '103', '6');
INSERT INTO `employees` VALUES ('105', 'David', 'Liang', '[email protected]', '0512-67513131', '1997-06-25', '9', '4800.00', '103', '6');
INSERT INTO `employees` VALUES ('106', 'Valli', 'Chen', '[email protected]', '0535-95105175', '1998-02-05', '9', '4800.00', '103', '6');
INSERT INTO `employees` VALUES ('107', 'Diana', 'Chen', '[email protected]', '025-95105105', '1999-02-07', '9', '4200.00', '103', '6');
INSERT INTO `employees` VALUES ('108', 'Nancy', 'Chen', '[email protected]', '0531-86012520', '1994-08-17', '7', '12000.00', '101', '10');
INSERT INTO `employees` VALUES ('109', 'Daniel', 'Chen', '[email protected]', '021-8008207890', '1994-08-16', '6', '9000.00', '108', '10');
INSERT INTO `employees` VALUES ('110', 'John', 'Chen', '[email protected]', '0592-2088888', '1997-09-28', '6', '8200.00', '108', '10');
INSERT INTO `employees` VALUES ('111', 'Ismael', 'Su', '[email protected]', '029-95105688', '1997-09-30', '6', '7700.00', '108', '10');
INSERT INTO `employees` VALUES ('112', 'Max', 'Su', '[email protected]', '021-95105105', '1998-03-07', '6', '7800.00', '108', '10');
INSERT INTO `employees` VALUES ('113', 'Min', 'Su', '[email protected]', '027-88068888', '1999-12-07', '6', '6900.00', '108', '10');
INSERT INTO `employees` VALUES ('114', 'Avg', 'Su', '[email protected]', '0755-82328647', '1994-12-07', '14', '11000.00', '100', '3');
INSERT INTO `employees` VALUES ('115', 'Alexander', 'Su', '[email protected]', '0431-86122222', '1995-05-18', '13', '3100.00', '114', '3');
INSERT INTO `employees` VALUES ('116', 'Shelli', 'Zhang', '[email protected]', '0771-2222222', '1997-12-24', '13', '2900.00', '114', '3');
INSERT INTO `employees` VALUES ('117', 'Sigal', 'Zhang', '[email protected]', '0791-6101074', '1997-07-24', '13', '2800.00', '114', '3');
INSERT INTO `employees` VALUES ('118', 'Guy', 'Zhang', '[email protected]', '0411-82603331', '1998-11-15', '13', '2600.00', '114', '3');
INSERT INTO `employees` VALUES ('119', 'Karen', 'Zhang', '[email protected]', '010-51019999', '1999-08-10', '13', '2500.00', '114', '3');
INSERT INTO `employees` VALUES ('120', 'Matthew', 'Han', '[email protected]', '0574-56163111', '1996-07-18', '19', '8000.00', '100', '5');
INSERT INTO `employees` VALUES ('121', 'Max', 'Han', '[email protected]', '0731-2637122', '1997-04-10', '19', '8200.00', '100', '5');
INSERT INTO `employees` VALUES ('122', 'Min', 'Liu', '[email protected]', '023-63862607', '1995-05-01', '19', '7900.00', '100', '5');
INSERT INTO `employees` VALUES ('123', 'Shanta', 'Liu', '[email protected]', '311-87600111', '1997-10-10', '19', '6500.00', '100', '5');
INSERT INTO `employees` VALUES ('126', 'Irene', 'Liu', '[email protected]', '0752-95105688', '1998-09-28', '18', '2700.00', '120', '5');
INSERT INTO `employees` VALUES ('145', 'John', 'Liu', '[email protected]', null, '1996-10-01', '15', '14000.00', '100', '8');
INSERT INTO `employees` VALUES ('146', 'Karen', 'Liu', '[email protected]', null, '1997-01-05', '15', '13500.00', '100', '8');
INSERT INTO `employees` VALUES ('176', 'Jonathon', 'Yang', '[email protected]', null, '1998-03-24', '16', '8600.00', '100', '8');
INSERT INTO `employees` VALUES ('177', 'Jack', 'Yang', '[email protected]', null, '1998-04-23', '16', '8400.00', '100', '8');
INSERT INTO `employees` VALUES ('178', 'Kimberely', 'Yang', '[email protected]', null, '1999-05-24', '16', '7000.00', '100', '8');
INSERT INTO `employees` VALUES ('179', 'Charles', 'Yang', '[email protected]', null, '2000-01-04', '16', '6200.00', '100', '8');
INSERT INTO `employees` VALUES ('192', 'Sarah', 'Yang', '[email protected]', '0351-2233611', '1996-02-04', '17', '4000.00', '123', '5');
INSERT INTO `employees` VALUES ('193', 'Britney', 'Zhao', '[email protected]', '0351-2233611', '1997-03-03', '17', '3900.00', '123', '5');
INSERT INTO `employees` VALUES ('200', 'Jennifer', 'Zhao', '[email protected]', '021-66050000', '1987-09-17', '3', '4400.00', '101', '1');
INSERT INTO `employees` VALUES ('201', 'Michael', 'Zhou', '[email protected]', '010-67237328', '1996-02-17', '10', '13000.00', '100', '2');
INSERT INTO `employees` VALUES ('202', 'Pat', 'Zhou', '[email protected]', '0755-28114518', '1997-08-17', '11', '6000.00', '201', '2');
INSERT INTO `employees` VALUES ('203', 'Susan', 'Zhou', '[email protected]', '0755-83587526', '1994-06-07', '8', '6500.00', '101', '4');
INSERT INTO `employees` VALUES ('204', 'Hermann', 'Wu', '[email protected]', '0513-83512816', '1994-06-07', '12', '10000.00', '101', '7');
INSERT INTO `employees` VALUES ('205', 'Shelley', 'Wu', '[email protected]', '0898-31686222', '1994-06-07', '2', '12000.00', '101', '11');
INSERT INTO `employees` VALUES ('206', 'William', 'Wu', '[email protected]', '022-26144822', '1994-06-07', '1', '8300.00', '205', '11');
/*Data for the table dependents */
INSERT INTO dependents(dependent_id,first_name,last_name,relationship,employee_id) VALUES (1,'Penelope','Wu','Child',206);
INSERT INTO dependents(dependent_id,first_name,last_name,relationship,employee_id) VALUES (2,'Nick','Wu','Child',205);
INSERT INTO dependents(dependent_id,first_name,last_name,relationship,employee_id) VALUES (3,'Ed','Zhao','Child',200);
INSERT INTO dependents(dependent_id,first_name,last_name,relationship,employee_id) VALUES (4,'Jennifer','Lee','Child',100);
INSERT INTO dependents(dependent_id,first_name,last_name,relationship,employee_id) VALUES (5,'Johnny','Wong','Child',101);
INSERT INTO dependents(dependent_id,first_name,last_name,relationship,employee_id) VALUES (6,'Bette','Liang','Child',102);
INSERT INTO dependents(dependent_id,first_name,last_name,relationship,employee_id) VALUES (7,'Grace','Chen','Child',109);
INSERT INTO dependents(dependent_id,first_name,last_name,relationship,employee_id) VALUES (8,'Matthew','Chen','Child',110);
INSERT INTO dependents(dependent_id,first_name,last_name,relationship,employee_id) VALUES (9,'Joe','Su','Child',111);
INSERT INTO dependents(dependent_id,first_name,last_name,relationship,employee_id) VALUES (10,'Christian','Su','Child',112);
INSERT INTO dependents(dependent_id,first_name,last_name,relationship,employee_id) VALUES (11,'Zero','Su','Child',113);
INSERT INTO dependents(dependent_id,first_name,last_name,relationship,employee_id) VALUES (12,'Karl','Chen','Child',108);
INSERT INTO dependents(dependent_id,first_name,last_name,relationship,employee_id) VALUES (13,'Uma','Zhou','Child',203);
INSERT INTO dependents(dependent_id,first_name,last_name,relationship,employee_id) VALUES (14,'Vivien','Lee','Child',103);
INSERT INTO dependents(dependent_id,first_name,last_name,relationship,employee_id) VALUES (15,'Cuba','Wong','Child',104);
INSERT INTO dependents(dependent_id,first_name,last_name,relationship,employee_id) VALUES (16,'Fred','Liang','Child',105);
INSERT INTO dependents(dependent_id,first_name,last_name,relationship,employee_id) VALUES (17,'Helen','Chen','Child',106);
INSERT INTO dependents(dependent_id,first_name,last_name,relationship,employee_id) VALUES (18,'Dan','Chen','Child',107);
INSERT INTO dependents(dependent_id,first_name,last_name,relationship,employee_id) VALUES (19,'Bob','Zhou','Child',201);
INSERT INTO dependents(dependent_id,first_name,last_name,relationship,employee_id) VALUES (20,'Lucille','Zhou','Child',202);
INSERT INTO dependents(dependent_id,first_name,last_name,relationship,employee_id) VALUES (21,'Kirsten','Wu','Child',204);
INSERT INTO dependents(dependent_id,first_name,last_name,relationship,employee_id) VALUES (22,'Elvis','Su','Child',115);
INSERT INTO dependents(dependent_id,first_name,last_name,relationship,employee_id) VALUES (23,'Sandra','Zhang','Child',116);
INSERT INTO dependents(dependent_id,first_name,last_name,relationship,employee_id) VALUES (24,'Cameron','Zhang','Child',117);
INSERT INTO dependents(dependent_id,first_name,last_name,relationship,employee_id) VALUES (25,'Kevin','Zhang','Child',118);
INSERT INTO dependents(dependent_id,first_name,last_name,relationship,employee_id) VALUES (26,'Rip','Zhang','Child',119);
INSERT INTO dependents(dependent_id,first_name,last_name,relationship,employee_id) VALUES (27,'Julia','Su','Child',114);
INSERT INTO dependents(dependent_id,first_name,last_name,relationship,employee_id) VALUES (28,'Woody','Liu','Child',145);
INSERT INTO dependents(dependent_id,first_name,last_name,relationship,employee_id) VALUES (29,'Alec','Liu','Child',146);
INSERT INTO dependents(dependent_id,first_name,last_name,relationship,employee_id) VALUES (30,'Sandra','Yang','Child',176);
7. 刪除表
以下是用於重新整理範例資料庫時，刪除所有錶的指令碼。
DROP TABLE employees;
DROP TABLE dependents;
DROP TABLE departments;
DROP TABLE locations;
DROP TABLE countries;
DROP TABLE regions;
DROP TABLE jobs;