create table Dep (
 n_dep integer not null primary key,
 Nome_dep varchar(20) not null) engine=InnoDB;


 create table emp (
 n_emp int not null primary key,
 nome_emp varchar(50) not null,
 n_dep integer not null,
 foreign key (n_dep) references dep(n_dep) on delete cascade)
 engine=InnoDB;
	
create view emp_10 as select n_emp, nome_emp, n_dep from emp where n_dep = 10;

insert into dep values (10, "ADMINISTRAÇÃO");
insert into dep values (20, "PESQUISA");
insert into dep values (30, "VENDAS");
insert into dep values (40, "PRODUÇÃO");

INSERT INTO EMP VALUES (103,"SAMANTA",20);
INSERT INTO EMP VALUES (110,"URATAN",20);
INSERT INTO EMP VALUES (173,"PAULO",10);
INSERT INTO EMP VALUES (189,"RITA",30);
INSERT INTO EMP VALUES (201,"EVERALDO",30);
INSERT INTO EMP VALUES (208,"SILVIO",30);
INSERT INTO EMP VALUES (230,"ANA",10);
INSERT INTO EMP VALUES (276,"RENATO",20);


SELECT * FROM EMP_10;
+-------+----------+-------+
| n_emp | nome_emp | n_dep |
+-------+----------+-------+
|   173 | PAULO    |    10 |
|   230 | ANA      |    10 |
+-------+----------+-------+

mysql> delete from emp_10 where n_emp = 201;
Query OK, 0 rows affected (0.03 sec)

mysql> SELECT * FROM EMP_10;
+-------+----------+-------+
| n_emp | nome_emp | n_dep |
+-------+----------+-------+
|   173 | PAULO    |    10 |
|   230 | ANA      |    10 |
+-------+----------+-------+
2 rows in set (0.00 sec)

mysql> SELECT * FROM EMP;
+-------+----------+-------+
| n_emp | nome_emp | n_dep |
+-------+----------+-------+
|   103 | SAMANTA  |    20 |
|   110 | URATAN   |    20 |
|   173 | PAULO    |    10 |
|   189 | RITA     |    30 |
|   201 | EVERALDO |    30 |
|   208 | SILVIO   |    30 |
|   230 | ANA      |    10 |
|   276 | RENATO   |    20 |
+-------+----------+-------+
8 rows in set (0.00 sec)

mysql> delete from emp_10 where n_emp = 230;
Query OK, 1 row affected (0.03 sec)

mysql> SELECT * FROM EMP_10;
+-------+----------+-------+
| n_emp | nome_emp | n_dep |
+-------+----------+-------+
|   173 | PAULO    |    10 |
+-------+----------+-------+
1 row in set (0.00 sec)

mysql> SELECT * FROM EMP;
+-------+----------+-------+
| n_emp | nome_emp | n_dep |
+-------+----------+-------+
|   103 | SAMANTA  |    20 |
|   110 | URATAN   |    20 |
|   173 | PAULO    |    10 |
|   189 | RITA     |    30 |
|   201 | EVERALDO |    30 |
|   208 | SILVIO   |    30 |
|   276 | RENATO   |    20 |
+-------+----------+-------+
7 rows in set (0.00 sec)

mysql> create view emp_dep ( nome, num, num_dep, nome_dep ) as select
    -> nome_emp, n_emp, dep.n_dep, nome_dep from emp, dep where
    -> emp.n_dep = dep.n_dep;
Query OK, 0 rows affected (0.05 sec)

mysql> select * from emp;
+-------+----------+-------+
| n_emp | nome_emp | n_dep |
+-------+----------+-------+
|   103 | SAMANTA  |    20 |
|   110 | URATAN   |    20 |
|   173 | PAULO    |    10 |
|   189 | RITA     |    30 |
|   201 | EVERALDO |    30 |
|   208 | SILVIO   |    30 |
|   276 | RENATO   |    20 |
+-------+----------+-------+
7 rows in set (0.00 sec)

desc emp_dep;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| nome     | varchar(50) | NO   |     | NULL    |       |
| num      | int(11)     | NO   |     | NULL    |       |
| num_dep  | int(11)     | NO   |     | NULL    |       |
| nome_dep | varchar(20) | NO   |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+
4 rows in set (0.02 sec)

drop view emp_dep;