DROP SCHEMA test_views;

CREATE SCHEMA test_views;

use test_views;

create table if not exists dept (
deptno int primary key,
dname  varchar(15),
loc    varchar(15)
);

create table if not exists job (
jobcode int primary key,
job_descr varchar(15),
sal float
);

create table if not exists emp (
empno int primary key,
name varchar(15),
jobno int,
deptno int,
comm float,
foreign key (jobno) references job(jobcode),
foreign key (deptno) references dept(deptno)
);

insert into dept values(50, "sales", "athens");
insert into dept values(60, "accounting", "athens");
insert into dept values(70, "payrol", "volos");

insert into job values(100, "salesman", 2000.00);
insert into job values(200, "analyst", 2000.00);
insert into job values(300, "dba", 3000.00);

insert into emp values(10,"codd",100,50,NULL);
insert into emp values(20,"navathe",200,50,450.00);
insert into emp values(30,"elmasri",300,60,NULL);
insert into emp values(40,"date",100,50,NULL);
insert into emp values(50,"codd",100,50,NULL);
insert into emp values(60,"codd",200,50,450.00);
insert into emp values(70,"codd",200,50,500.00);
insert into emp values(80,"codd",100,50,NULL);