Base de donnees
Base de donnees
Chapitre 1
TP 1
DROP TABLE IF EXISTS emp;
CREATE TABLE emp (
empno decimal(4,0) NOT NULL,
ename varchar(10) default NULL,
job varchar(9) default NULL,
mgr decimal(4,0) default NULL,
hiredate date default NULL,
sal decimal(7,2) default NULL,
comm decimal(7,2) default NULL,
deptno decimal(2,0) default NULL
);
DROP TABLE IF EXISTS dept;
CREATE TABLE dept (
deptno decimal(2,0) default NULL,
dname varchar(14) default NULL,
loc varchar(13) default NULL
);
INSERT INTO emp (EMPNO, ENAME, JOB, MGR, SAL, COMM, DEPTNO) VALUES
(7369, 'SMITH', 'CLERK', 7902, 800, NULL, 20),
(7499, 'ALLEN', 'SALESMAN', 7698, 1600, 300, 30),
(7521, 'WARD', 'SALESMAN', 7698, 1250, 500, 30),
(7566, 'JONES', 'MANAGER', 7839, 2975, NULL, 20),
(7654, 'MARTIN', 'SALESMAN', 7698, 1250, 1400, 30),
(7698, 'BLAKE', 'MANAGER', 7839, 2850, NULL, 30),
(7782, 'CLARK', 'MANAGER', 7839, 2450, NULL, 10),
(7788, 'SCOTT', 'ANALYST', 7566, 3000, NULL, 20),
(7839, 'KING', 'PRESIDENT', NULL, 5000, NULL, 10),
(7844, 'TURNER', 'SALESMAN', 7698, 1500, 0, 30),
(7876, 'ADAMS', 'CLERK', 7788, 1100, NULL, 20),
(7900, 'JAMES', 'CLERK', 7698, 950, NULL, 30),
(7902, 'FORD', 'ANALYST', 7566, 3000, NULL, 20),
(7934, 'MILLER', 'CLERK', 7782, 1300, NULL, 10);
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');
DROP TABLE IF EXISTS salgrade;
CREATE TABLE IF NOT EXISTS salgrade (
GRADE int DEFAULT NULL,
LOSAL int DEFAULT NULL,
HISAL int DEFAULT NULL
) ;
INSERT INTO salgrade VALUES
(1, 700, 1200),
(2, 1201, 1400),
(3, 1401, 2000),
(4, 2001, 3000),
(5, 3001, 9999);