SQLize Online / PHPize Online  /  SQLtest Online

CREATE TABLE Regions ( region_id INT PRIMARY KEY, region_name VARCHAR(25) ); INSERT INTO Regions (region_id, region_name) VALUES (1, 'Europe'), (2, 'Americas'), (3, 'Asia'), (4, 'Middle East and Africa'); CREATE TABLE Countries ( country_id CHAR(2) PRIMARY KEY, country_name VARCHAR(40) NOT NULL, region_id INT, FOREIGN KEY (region_id) REFERENCES Regions(region_id) ); INSERT INTO Countries (country_id, country_name, region_id) VALUES ('AR', 'Argentina', 2), ('AU', 'Australia', 3), ('BE', 'Belgium', 1), ('BR', 'Brazil', 2), ('CA', 'Canada', 2), ('CH', 'Switzerland', 1), ('CN', 'China', 3), ('DE', 'Germany', 1), ('DK', 'Denmark', 1), ('EG', 'Egypt', 4), ('FR', 'France', 1), ('HK', 'Hong Kong', 3), ('IL', 'Israel', 4), ('IN', 'India', 3), ('IT', 'Italy', 1), ('JP', 'Japan', 3), ('KW', 'Kuwait', 4), ('MX', 'Mexico', 2), ('NG', 'Nigeria', 4), ('NL', 'Netherlands', 1), ('SG', 'Singapore', 3), ('UK', 'United Kingdom', 1), ('US', 'United States of America', 2), ('ZM', 'Zambia', 4), ('ZW', 'Zimbabwe', 4); CREATE TABLE Jobs ( job_id VARCHAR(10) PRIMARY KEY, job_title VARCHAR(35) NOT NULL, min_salary INT, max_salary INT ); INSERT INTO Jobs (job_id, job_title, min_salary, max_salary) VALUES ('AD_PRES', 'President', 20000, 40000), ('AD_VP', 'Administration Vice President', 15000, 30000), ('AD_ASST', 'Administration Assistant', 3000, 6000), ('FI_MGR', 'Finance Manager', 8200, 16000), ('FI_ACCOUNT', 'Accountant', 4200, 9000), ('AC_MGR', 'Accounting Manager', 8200, 16000), ('AC_ACCOUNT', 'Public Accountant', 4200, 9000), ('SA_MAN', 'Sales Manager', 10000, 20000), ('SA_REP', 'Sales Representative', 6000, 12000), ('PU_MAN', 'Purchasing Manager', 8000, 15000), ('PU_CLERK', 'Purchasing Clerk', 2500, 5500), ('ST_MAN', 'Stock Manager', 5500, 8500), ('ST_CLERK', 'Stock Clerk', 2000, 5000), ('SH_CLERK', 'Shipping Clerk', 2500, 5500), ('IT_PROG', 'Programmer', 4000, 10000), ('MK_MAN', 'Marketing Manager', 9000, 15000), ('MK_REP', 'Marketing Representative', 4000, 9000), ('HR_REP', 'Human Resources Representative', 4000, 9000), ('PR_REP', 'Public Relations Representative', 4500, 10500); CREATE TABLE Locations ( location_id INT PRIMARY KEY, street_address VARCHAR(40), postal_code VARCHAR(12), city VARCHAR(30) NOT NULL, state_province VARCHAR(25), country_id CHAR(2), FOREIGN KEY (country_id) REFERENCES Countries(country_id) ); INSERT INTO Locations (location_id, street_address, postal_code, city, state_province, country_id) VALUES (1000, '1297 Via Cola di Rie', '989', 'Roma', NULL, 'IT'), (1100, '93091 Calle della Testa', '10934', 'Venice', NULL, 'IT'), (1200, '2017 Shinjuku-ku', '1689', 'Tokyo', 'Tokyo Prefecture', 'JP'), (1300, '9450 Kamiya-cho', '6823', 'Hiroshima', NULL, 'JP'), (1400, '2014 Jabberwocky Rd', '26192', 'Southlake', 'Texas', 'US'), (1500, '2011 Interiors Blvd', '99236', 'South San Francisco', 'California', 'US'), (1600, '2007 Zagora St', '50090', 'South Brunswick', 'New Jersey', 'US'), (1700, '2004 Charade Rd', '98199', 'Seattle', 'Washington', 'US'), (1800, '147 Spadina Ave', 'M5V 2L7', 'Toronto', 'Ontario', 'CA'), (1900, '6092 Boxwood St', 'YSW 9T2', 'Whitehorse', 'Yukon', 'CA'), (2000, '40-5-12 Laogianggen', '190518', 'Beijing', NULL, 'CN'), (2100, '1298 Vileparle (E)', '490231', 'Bombay', 'Maharashtra', 'IN'), (2200, '12-98 Victoria Street', '2901', 'Sydney', 'New South Wales', 'AU'), (2300, '198 Clementi North', '540198', 'Singapore', NULL, 'SG'), (2400, '8204 Arthur St', '14024', 'London', NULL, 'UK'), (2500, 'Magdalen Centre, The Oxford Science Park', 'OX9 9ZB', 'Oxford', 'Oxford', 'UK'), (2600, '9702 Chester Road', '9629850293', 'Stretford', 'Manchester', 'UK'), (2700, 'Schwanthalerstr. 7031', '80925', 'Munich', 'Bavaria', 'DE'), (2800, 'Rua Frei Caneca 1360', '01307-002', 'Sao Paulo', 'Sao Paulo', 'BR'), (2900, '20 Rue des Corps-Saints', '1730', 'Geneva', 'Geneve', 'CH'), (3000, 'Murtenstrasse 921', '3095', 'Bern', 'BE', 'CH'), (3100, 'Pieter Breughelstraat 837', '3029SK', 'Utrecht', 'Utrecht', 'NL'), (3200, 'Mariano Escobedo 9991', '11932', 'Mexico City', 'Distrito Federal', 'MX'); CREATE TABLE Departments ( department_id INT PRIMARY KEY, department_name VARCHAR(30) NOT NULL, manager_id INT, location_id INT, FOREIGN KEY (manager_id) REFERENCES Employees(employee_id), FOREIGN KEY (location_id) REFERENCES Locations(location_id) ); INSERT INTO Departments (department_id, department_name, manager_id, location_id) VALUES (10, 'Administration', 200, 1700), (20, 'Marketing', 201, 1800), (30, 'Purchasing', 114, 1700), (40, 'Human Resources', 203, 2400), (50, 'Shipping', 121, 1500), (60, 'IT', 103, 1400), (70, 'Public Relations', 204, 2700), (80, 'Sales', 145, 2500), (90, 'Executive', 100, 1700), (100, 'Finance', 108, 1700), (110, 'Accounting', 205, 1700), (120, 'Treasury', NULL, 1700), (130, 'Corporate Tax', NULL, 1700), (140, 'Control And Credit', NULL, 1700), (150, 'Shareholder Services', NULL, 1700), (160, 'Benefits', NULL, 1700), (170, 'Manufacturing', NULL, 1700), (180, 'Construction', NULL, 1700), (190, 'Contracting', NULL, 1700), (200, 'Operations', NULL, 1700); CREATE TABLE Job_history ( employee_id INT, start_date DATE, end_date DATE, job_id VARCHAR(10), department_id INT, PRIMARY KEY (employee_id, start_date), FOREIGN KEY (employee_id) REFERENCES Employees(employee_id), FOREIGN KEY (job_id) REFERENCES Jobs(job_id), FOREIGN KEY (department_id) REFERENCES Departments(department_id) ); INSERT INTO Job_history (employee_id, start_date, end_date, job_id, department_id) VALUES (102, '2001-01-13', '2006-07-24', 'IT_PROG', 60), (101, '1997-09-21', '2001-10-27', 'AC_ACCOUNT', 110), (101, '2001-10-28', '2005-03-15', 'AC_MGR', 110), (201, '2004-02-17', '2007-12-19', 'MK_REP', 20), (114, '2006-03-24', '2007-12-31', 'ST_CLERK', 50), (122, '2007-01-01', '2007-12-31', 'ST_CLERK', 50), (200, '1995-09-17', '2001-06-17', 'AD_ASST', 90), (176, '2006-03-24', '2006-12-31', 'SA_REP', 80), (176, '2007-01-01', '2007-12-31', 'SA_MAN', 80), (200, '2002-07-01', '2006-12-31', 'AC_ACCOUNT', 90); CREATE TABLE Employees ( employee_id INT PRIMARY KEY, first_name VARCHAR(20), last_name VARCHAR(25) NOT NULL, email VARCHAR(25) NOT NULL, phone_number VARCHAR(20), hire_date DATE NOT NULL, job_id VARCHAR(10) NOT NULL, salary DECIMAL(8,2), commission_pct DECIMAL(2,2), manager_id INT, department_id INT, FOREIGN KEY (job_id) REFERENCES Jobs(job_id), FOREIGN KEY (manager_id) REFERENCES Employees(employee_id), FOREIGN KEY (department_id) REFERENCES Departments(department_id) ); INSERT INTO Employees (employee_id, first_name, last_name, email, phone_number, hire_date, job_id, salary, commission_pct, manager_id, department_id) VALUES (100, 'Steven', 'King', 'SKING', '515.123.4567', '1987-06-17', 'AD_PRES', 24000, NULL, NULL, 90), (101, 'Neena', 'Kochhar', 'NKOCHHAR', '515.123.4568', '1989-09-21', 'AD_VP', 17000, NULL, 100, 90), (102, 'Lex', 'De Haan', 'LDEHAAN', '515.123.4569', '1993-01-13', 'AD_VP', 17000, NULL, 100, 90), (103, 'Alexander', 'Hunold', 'AHUNOLD', '590.423.4567', '1990-01-03', 'IT_PROG', 9000, NULL, 102, 60), (104, 'Bruce', 'Ernst', 'BERNST', '590.423.4568', '1991-05-21', 'IT_PROG', 6000, NULL, 103, 60), (105, 'David', 'Austin', 'DAUSTIN', '590.423.4569', '1997-06-25', 'IT_PROG', 4800, NULL, 103, 60), (106, 'Valli', 'Pataballa', 'VPATABAL', '590.423.4560', '1998-02-05', 'IT_PROG', 4800, NULL, 103, 60), (107, 'Diana', 'Lorentz', 'DLORENTZ', '590.423.5567', '1999-02-07', 'IT_PROG', 4200, NULL, 103, 60), (108, 'Nancy', 'Greenberg', 'NGREENBE', '515.124.4569', '1994-08-17', 'FI_MGR', 12000, NULL, 101, 100), (109, 'Daniel', 'Faviet', 'DFAVIET', '515.124.4169', '1994-08-16', 'FI_ACCOUNT', 9000, NULL, 108, 100), (110, 'John', 'Chen', 'JCHEN', '515.124.4269', '1997-09-28', 'FI_ACCOUNT', 8200, NULL, 108, 100), (111, 'Ismael', 'Sciarra', 'ISCIARRA', '515.124.4369', '1997-09-30', 'FI_ACCOUNT', 7700, NULL, 108, 100), (112, 'Jose Manuel', 'Urman', 'JMURMAN', '515.124.4469', '1998-03-07', 'FI_ACCOUNT', 7800, NULL, 108, 100), (113, 'Luis', 'Popp', 'LPOPP', '515.124.4567', '1999-12-07', 'FI_ACCOUNT', 6900, NULL, 108, 100), (114, 'Den', 'Raphaely', 'DRAPHEAL', '515.127.4561', '1994-12-07', 'PU_MAN', 11000, NULL, 100, 30), (115, 'Alexander', 'Khoo', 'AKHOO', '515.127.4562', '1995-05-18', 'PU_CLERK', 3100, NULL, 114, 30), (116, 'Shelli', 'Baida', 'SBAIDA', '515.127.4563', '1997-12-24', 'PU_CLERK', 2900, NULL, 114, 30), (117, 'Sigal', 'Tobias', 'STOBIAS', '515.127.4564', '1997-07-24', 'PU_CLERK', 2800, NULL, 114, 30), (118, 'Guy', 'Himuro', 'GHIMURO', '515.127.4565', '1998-11-15', 'PU_CLERK', 2600, NULL, 114, 30), (119, 'Karen', 'Colmenares', 'KCOLMENA', '515.127.4566', '1999-08-10', 'PU_CLERK', 2500, NULL, 114, 30), (120, 'Matthew', 'Weiss', 'MWEISS', '650.123.1234', '1996-07-18', 'ST_MAN', 8000, NULL, 100, 50), (121, 'Adam', 'Fripp', 'AFRIPP', '650.123.2234', '1997-04-10', 'ST_CLERK', 4800, NULL, 120, 50), (122, 'Payam', 'Kaufling', 'PKAUFLIN', '650.123.3234', '1995-05-01', 'ST_CLERK', 4200, NULL, 120, 50), (123, 'Shanta', 'Vollman', 'SVOLLMAN', '650.123.4234', '1997-10-10', 'ST_CLERK', 4100, NULL, 120, 50), (124, 'Kevin', 'Mourgos', 'KMOURGOS', '650.123.5234', '1999-11-16', 'ST_CLERK', 4000, NULL, 120, 50), (125, 'Julia', 'Nayer', 'JNAYER', '650.124.6234', '1997-07-16', 'ST_CLERK', 3200, NULL, 120, 50);