· 6 years ago · May 20, 2019, 10:42 AM
1-- phpMyAdmin SQL Dump
2-- version 3.3.7deb7
3-- http://www.phpmyadmin.net
4--
5-- Host: localhost
6-- Erstellungszeit: 18. September 2012 um 14:46
7-- Server Version: 5.1.63
8-- PHP-Version: 5.3.3-7+squeeze14
9
10SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";
11
12
13/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
14/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
15/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
16/*!40101 SET NAMES utf8 */;
17
18--
19-- Datenbank: `schule`
20--
21CREATE DATABASE `schule` DEFAULT CHARACTER SET latin1 COLLATE latin1_swedish_ci;
22USE `schule`;
23
24-- --------------------------------------------------------
25
26--
27-- Tabellenstruktur für Tabelle `COUNTRIES`
28--
29
30CREATE TABLE IF NOT EXISTS `COUNTRIES` (
31 `COUNTRY_ID` char(2) NOT NULL,
32 `COUNTRY_NAME` varchar(40) DEFAULT NULL,
33 `REGION_ID` int(11) DEFAULT NULL,
34 PRIMARY KEY (`COUNTRY_ID`)
35) ENGINE=MyISAM DEFAULT CHARSET=latin1;
36
37--
38-- Daten für Tabelle `COUNTRIES`
39--
40
41INSERT INTO `COUNTRIES` (`COUNTRY_ID`, `COUNTRY_NAME`, `REGION_ID`) VALUES
42('CA', 'Canada', 2),
43('DE', 'Germany', 1),
44('UK', 'United Kingdom', 1),
45('US', 'United States of America', 2);
46
47-- --------------------------------------------------------
48
49--
50-- Tabellenstruktur für Tabelle `DEPARTMENTS`
51--
52
53CREATE TABLE IF NOT EXISTS `DEPARTMENTS` (
54 `DEPARTMENT_ID` int(4) NOT NULL,
55 `DEPARTMENT_NAME` varchar(30) NOT NULL,
56 `MANAGER_ID` int(6) DEFAULT NULL,
57 `LOCATION_ID` int(4) DEFAULT NULL,
58 PRIMARY KEY (`DEPARTMENT_ID`)
59) ENGINE=MyISAM DEFAULT CHARSET=latin1;
60
61--
62-- Daten für Tabelle `DEPARTMENTS`
63--
64
65INSERT INTO `DEPARTMENTS` (`DEPARTMENT_ID`, `DEPARTMENT_NAME`, `MANAGER_ID`, `LOCATION_ID`) VALUES
66(10, 'Administration', 200, 1700),
67(20, 'Marketing', 201, 1800),
68(50, 'Shipping', 124, 1500),
69(60, 'IT', 103, 1400),
70(80, 'Sales', 149, 2500),
71(90, 'Executive', 100, 1700),
72(110, 'Accounting', 205, 1700),
73(190, 'Contracting', NULL, 1700);
74
75-- --------------------------------------------------------
76
77--
78-- Tabellenstruktur für Tabelle `EMPLOYEES`
79--
80
81CREATE TABLE IF NOT EXISTS `EMPLOYEES` (
82 `EMPLOYEE_ID` int(6) NOT NULL,
83 `FIRST_NAME` varchar(20) DEFAULT NULL,
84 `LAST_NAME` varchar(25) NOT NULL,
85 `EMAIL` varchar(25) NOT NULL,
86 `PHONE_NUMBER` varchar(20) DEFAULT NULL,
87 `HIRE_DATE` date NOT NULL,
88 `JOB_ID` varchar(10) NOT NULL,
89 `SALARY` double(8,2) DEFAULT NULL,
90 `COMMISSION_PCT` double(2,2) DEFAULT NULL,
91 `MANAGER_ID` int(6) DEFAULT NULL,
92 `DEPARTMENT_ID` int(4) DEFAULT NULL,
93 PRIMARY KEY (`EMPLOYEE_ID`)
94) ENGINE=MyISAM DEFAULT CHARSET=latin1;
95
96--
97-- Daten für Tabelle `EMPLOYEES`
98--
99
100INSERT INTO `EMPLOYEES` (`EMPLOYEE_ID`, `FIRST_NAME`, `LAST_NAME`, `EMAIL`, `PHONE_NUMBER`, `HIRE_DATE`, `JOB_ID`, `SALARY`, `COMMISSION_PCT`, `MANAGER_ID`, `DEPARTMENT_ID`) VALUES
101(100, 'Steven', 'King', 'SKING', '515.123.4567', '1987-06-17', 'AD_PRES', 24000.00, NULL, NULL, 90),
102(101, 'Neena', 'Kochhar', 'NKOCHHAR', '515.123.4568', '1989-09-21', 'AD_VP', 17000.00, NULL, 100, 90),
103(102, 'Lex', 'De Haan', 'LDEHAAN', '515.123.4569', '1993-01-13', 'AD_VP', 17000.00, NULL, 100, 90),
104(103, 'Alexander', 'Hunold', 'AHUNOLD', '590.423.4567', '1990-01-03', 'IT_PROG', 9000.00, NULL, 102, 60),
105(104, 'Bruce', 'Ernst', 'BERNST', '590.423.4568', '1991-05-21', 'IT_PROG', 6000.00, NULL, 103, 60),
106(107, 'Diana', 'Lorentz', 'DLORENTZ', '590.423.5567', '1999-02-07', 'IT_PROG', 4200.00, NULL, 103, 60),
107(124, 'Kevin', 'Mourgos', 'KMOURGOS', '650.123.5234', '1999-11-16', 'ST_MAN', 5800.00, NULL, 100, 50),
108(141, 'Trenna', 'Rajs', 'TRAJS', '650.121.8009', '1995-10-17', 'ST_CLERK', 3500.00, NULL, 124, 50),
109(142, 'Curtis', 'Davies', 'CDAVIES', '650.121.2994', '1997-01-29', 'ST_CLERK', 3100.00, NULL, 124, 50),
110(143, 'Randall', 'Matos', 'RMATOS', '650.121.2874', '1998-03-15', 'ST_CLERK', 2600.00, NULL, 124, 50),
111(144, 'Peter', 'Vargas', 'PVARGAS', '650.121.2004', '1998-07-09', 'ST_CLERK', 2500.00, NULL, 124, 50),
112(149, 'Eleni', 'Zlotkey', 'EZLOTKEY', '011.44.1344.429018', '2000-01-29', 'SA_MAN', 10500.00, 0.20, 100, 80),
113(174, 'Ellen', 'Abel', 'EABEL', '011.44.1644.429267', '1996-05-11', 'SA_REP', 11000.00, 0.30, 149, 80),
114(176, 'Jonathon', 'Taylor', 'JTAYLOR', '011.44.1644.429265', '1998-03-24', 'SA_REP', 8600.00, 0.20, 149, 80),
115(178, 'Kimberely', 'Grant', 'KGRANT', '011.44.1644.429263', '1999-05-24', 'SA_REP', 7000.00, 0.15, 149, NULL),
116(200, 'Jennifer', 'Whalen', 'JWHALEN', '515.123.4444', '1987-09-17', 'AD_ASST', 4400.00, NULL, 101, 10),
117(201, 'Michael', 'Hartstein', 'MHARTSTE', '515.123.5555', '1996-02-17', 'MK_MAN', 13000.00, NULL, 100, 20),
118(202, 'Pat', 'Fay', 'PFAY', '603.123.6666', '1997-08-17', 'MK_REP', 6000.00, NULL, 201, 20),
119(205, 'Shelley', 'Higgins', 'SHIGGINS', '515.123.8080', '1994-06-07', 'AC_MGR', 12000.00, NULL, 101, 110),
120(206, 'Wiliam', 'Gietz', 'WGIETZ', '515.123.8181', '1994-06-07', 'AC_ACCOUNT', 8300.00, NULL, 205, 110);
121
122-- --------------------------------------------------------
123
124--
125-- Tabellenstruktur für Tabelle `JOBS`
126--
127
128CREATE TABLE IF NOT EXISTS `JOBS` (
129 `JOB_ID` varchar(10) NOT NULL,
130 `JOB_TITLE` varchar(35) NOT NULL,
131 `MIN_SALARY` int(6) DEFAULT NULL,
132 `MAX_SALARY` int(6) DEFAULT NULL,
133 PRIMARY KEY (`JOB_ID`)
134) ENGINE=MyISAM DEFAULT CHARSET=latin1;
135
136--
137-- Daten für Tabelle `JOBS`
138--
139
140INSERT INTO `JOBS` (`JOB_ID`, `JOB_TITLE`, `MIN_SALARY`, `MAX_SALARY`) VALUES
141('AD_PRES', 'President', 20000, 40000),
142('AD_VP', 'Administration Vice President', 15000, 30000),
143('AD_ASST', 'Administration Assistant', 3000, 6000),
144('AC_MGR', 'Accounting Manager', 8200, 16000),
145('AC_ACCOUNT', 'Public Accountant', 4200, 9000),
146('SA_MAN', 'Sales Manager', 10000, 20000),
147('SA_REP', 'SAles Representative', 6000, 1200),
148('ST_MAN', 'Stock Manager', 5500, 8500),
149('ST_CLERK', 'Stock Clerk', 2000, 5000),
150('IT_PROG', 'Programmer', 4000, 10000),
151('MK_MAN', 'Marketing Manager', 9000, 15000),
152('MK_REP', 'Marketing Representative', 4000, 9000);
153
154-- --------------------------------------------------------
155
156--
157-- Tabellenstruktur für Tabelle `JOB_GRADES`
158--
159
160CREATE TABLE IF NOT EXISTS `JOB_GRADES` (
161 `GRADE_LEVEL` varchar(3) NOT NULL DEFAULT '',
162 `LOWEST_SAL` int(11) DEFAULT NULL,
163 `HIGHEST_SAL` int(11) DEFAULT NULL,
164 PRIMARY KEY (`GRADE_LEVEL`)
165) ENGINE=MyISAM DEFAULT CHARSET=latin1;
166
167--
168-- Daten für Tabelle `JOB_GRADES`
169--
170
171INSERT INTO `JOB_GRADES` (`GRADE_LEVEL`, `LOWEST_SAL`, `HIGHEST_SAL`) VALUES
172('A', 1000, 2999),
173('B', 3000, 5999),
174('C', 6000, 9999),
175('D', 10000, 14999),
176('E', 15000, 24999),
177('F', 25000, 40000);
178
179-- --------------------------------------------------------
180
181--
182-- Tabellenstruktur für Tabelle `LOCATIONS`
183--
184
185CREATE TABLE IF NOT EXISTS `LOCATIONS` (
186 `LOCATION_ID` int(4) NOT NULL,
187 `STREET_ADDRESS` varchar(40) DEFAULT NULL,
188 `POSTAL_CODE` varchar(12) DEFAULT NULL,
189 `CITY` varchar(30) NOT NULL,
190 `STATE_PROVINCE` varchar(25) DEFAULT NULL,
191 `COUNTRY_ID` char(2) DEFAULT NULL,
192 PRIMARY KEY (`LOCATION_ID`)
193) ENGINE=MyISAM DEFAULT CHARSET=latin1;
194
195--
196-- Daten für Tabelle `LOCATIONS`
197--
198
199INSERT INTO `LOCATIONS` (`LOCATION_ID`, `STREET_ADDRESS`, `POSTAL_CODE`, `CITY`, `STATE_PROVINCE`, `COUNTRY_ID`) VALUES
200(1400, '2014 Jabberwocky RD', '26192', 'Southlake', 'Texas', 'US'),
201(1500, '2011 Interiors Blvd', '99236', 'South San Francisco', 'California', 'US'),
202(1700, '2004 Charade Rd', '98199', 'Seattle', 'Washington', 'US'),
203(1800, '460 Bloor St. W.', 'ON M5S 1X8', 'Toronto', 'Ontario', 'CA'),
204(2500, 'Magdalen Centre, The Oxford Science Park', 'OX9 9ZB', 'Oxford', 'Oxford', 'UK');
205
206-- --------------------------------------------------------
207
208--
209-- Tabellenstruktur für Tabelle `job_history`
210--
211
212CREATE TABLE IF NOT EXISTS `job_history` (
213 `EMPLOYEE_ID` int(6) NOT NULL,
214 `START_DATE` date NOT NULL,
215 `END_DATE` date NOT NULL,
216 `JOB_ID` varchar(10) NOT NULL,
217 `DEPARTMENT_ID` int(4) NOT NULL
218) ENGINE=MyISAM DEFAULT CHARSET=latin1;
219
220--
221-- Daten für Tabelle `job_history`
222--
223
224INSERT INTO `job_history` (`EMPLOYEE_ID`, `START_DATE`, `END_DATE`, `JOB_ID`, `DEPARTMENT_ID`) VALUES
225(102, '0000-00-00', '0000-00-00', 'IT_PROG', 60),
226(101, '0000-00-00', '0000-00-00', 'AC_ACCOUNT', 110),
227(101, '0000-00-00', '0000-00-00', 'AC_MGR', 110),
228(201, '0000-00-00', '0000-00-00', 'MK_KEP', 20),
229(114, '0000-00-00', '0000-00-00', 'ST_CLERK', 50),
230(122, '0000-00-00', '0000-00-00', 'ST_CLERK', 50),
231(200, '0000-00-00', '0000-00-00', 'AD_ASST', 90),
232(176, '0000-00-00', '0000-00-00', 'SA_REP', 80),
233(176, '0000-00-00', '0000-00-00', 'SA_MAN', 80),
234(200, '0000-00-00', '0000-00-00', 'AC_ACCOUNT', 90);
235
236-- --------------------------------------------------------
237
238--
239-- Tabellenstruktur für Tabelle `regions`
240--
241
242CREATE TABLE IF NOT EXISTS `regions` (
243 `REGION_ID` int(11) NOT NULL,
244 `REGION_NAME` varchar(25) DEFAULT NULL
245) ENGINE=MyISAM DEFAULT CHARSET=latin1;
246
247--
248-- Daten für Tabelle `regions`
249--
250
251INSERT INTO `regions` (`REGION_ID`, `REGION_NAME`) VALUES
252(1, 'EUROPE'),
253(2, 'Americas'),
254(3, 'Asia'),
255(4, 'Middle EAST and Africa');