MySQL用のSCOTTデータベース
Oracleを触ったことのある人は、一度はSCOTTスキーマのEMPテーブル、DEPTテーブルを見たことがあると思います。それぞれ14レコード、4レコードしかないのにSQLの基礎を学ぶための要素がいろいろ詰まっていて、とても便利なスキーマです。
というわけでMySQLに移植してみました。
create table dept
(deptno decimal(2) primary key,
dname varchar(14) ,
loc varchar(13));
create table emp
(empno decimal(4) primary key,
ename varchar(10),
job varchar(9),
mgr decimal(4),
hiredate date,
sal decimal(7,2),
comm decimal(7,2),
deptno decimal(2),
foreign key (deptno) references dept (deptno));
create table bonus
(ename varchar(10),
job varchar(9),
sal decimal,
comm decimal);
create table salgrade
(grade decimal,
losal decimal,
hisal decimal);
begin;
insert into dept values (10,'accounting','new york');
insert into dept values (20,'research','dallas');
insert into dept values (30,'sales','chicago');
insert into dept values (40,'operations','boston');
insert into emp values (7369,'smith','clerk',7902,str_to_date('17-12-1980','%d-%m-%Y'),800,null,20);
insert into emp values (7499,'allen','salesman',7698,str_to_date('20-2-1981','%d-%m-%Y'),1600,300,30);
insert into emp values (7521,'ward','salesman',7698,str_to_date('22-2-1981','%d-%m-%Y'),1250,500,30);
insert into emp values (7566,'jones','manager',7839,str_to_date('2-4-1981','%d-%m-%Y'),2975,null,20);
insert into emp values (7654,'martin','salesman',7698,str_to_date('28-9-1981','%d-%m-%Y'),1250,1400,30);
insert into emp values (7698,'blake','manager',7839,str_to_date('1-5-1981','%d-%m-%Y'),2850,null,30);
insert into emp values (7782,'clark','manager',7839,str_to_date('9-6-1981','%d-%m-%Y'),2450,null,10);
insert into emp values (7788,'scott','analyst',7566,date_add(str_to_date('13-jul-87','%d-%b-%y'), interval -85 day),3000,null,20);
insert into emp values (7839,'king','president',null,str_to_date('17-11-1981','%d-%m-%Y'),5000,null,10);
insert into emp values (7844,'turner','salesman',7698,str_to_date('8-9-1981','%d-%m-%Y'),1500,0,30);
insert into emp values (7876,'adams','clerk',7788,date_add(str_to_date('13-jul-87', '%d-%b-%y'), interval -51 day),1100,null,20);
insert into emp values (7900,'james','clerk',7698,str_to_date('3-12-1981','%d-%m-%Y'),950,null,30);
insert into emp values (7902,'ford','analyst',7566,str_to_date('3-12-1981','%d-%m-%Y'),3000,null,20);
insert into emp values (7934,'miller','clerk',7782,str_to_date('23-1-1982','%d-%m-%Y'),1300,null,10);
insert into salgrade values (1,700,1200);
insert into salgrade values (2,1201,1400);
insert into salgrade values (3,1401,2000);
insert into salgrade values (4,2001,3000);
insert into salgrade values (5,3001,9999);
commit;MySQLだとEMPテーブルが幅80桁のターミナルでちょうど1画面に収まります。とても見やすい。
mysql> select * from emp; +-------+--------+-----------+------+------------+---------+---------+--------+ | empno | ename | job | mgr | hiredate | sal | comm | deptno | +-------+--------+-----------+------+------------+---------+---------+--------+ | 7369 | smith | clerk | 7902 | 1980-12-17 | 800.00 | NULL | 20 | | 7499 | allen | salesman | 7698 | 1981-02-20 | 1600.00 | 300.00 | 30 | | 7521 | ward | salesman | 7698 | 1981-02-22 | 1250.00 | 500.00 | 30 | | 7566 | jones | manager | 7839 | 1981-04-02 | 2975.00 | NULL | 20 | | 7654 | martin | salesman | 7698 | 1981-09-28 | 1250.00 | 1400.00 | 30 | | 7698 | blake | manager | 7839 | 1981-05-01 | 2850.00 | NULL | 30 | | 7782 | clark | manager | 7839 | 1981-06-09 | 2450.00 | NULL | 10 | | 7788 | scott | analyst | 7566 | 1987-04-19 | 3000.00 | NULL | 20 | | 7839 | king | president | NULL | 1981-11-17 | 5000.00 | NULL | 10 | | 7844 | turner | salesman | 7698 | 1981-09-08 | 1500.00 | 0.00 | 30 | | 7876 | adams | clerk | 7788 | 1987-05-23 | 1100.00 | NULL | 20 | | 7900 | james | clerk | 7698 | 1981-12-03 | 950.00 | NULL | 30 | | 7902 | ford | analyst | 7566 | 1981-12-03 | 3000.00 | NULL | 20 | | 7934 | miller | clerk | 7782 | 1982-01-23 | 1300.00 | NULL | 10 | +-------+--------+-----------+------+------------+---------+---------+--------+ 14 rows in set (0.00 sec) mysql> select * from dept; +--------+------------+----------+ | deptno | dname | loc | +--------+------------+----------+ | 10 | accounting | new york | | 20 | research | dallas | | 30 | sales | chicago | | 40 | operations | boston | +--------+------------+----------+ 4 rows in set (0.00 sec)
ORACLE MASTER Bronzeの試験対策にも使えると思います(Oracleのセットアップは骨が折れるので)。新人さんは是非試してみてください。
