M73e7Em0

1DROP schema IF EXISTS test;
2create schema test;
3use test;
4
5DROP TABLE IF exists employee;
6create table employee (
7emp_no integer,
8emp_fname varchar(30),
9emp_lname varchar(30) not null,
10emp_status varchar(12) default 'Active',
11emp_salary numeric(7, 2),
12dept_no integer,
13constraint employee_pk primary key (emp_no)
14
15);
16
17DROP TABLE IF exists department;
18create table department (
19dep_no varchar(3),
20emp_fname varchar(10),
21loc varchar(30),
22dep_head varchar(4),
23emp_no integer,
24constraint department_pk primary key (dep_no),
25constraint fk_department_employee foreign key (emp_no) references employee (emp_no)
26);
27
28insert into employee(`emp_no`, `emp_fname`, `emp_lname`, `emp_status`, `emp_salary`, `dept_no`)
29values (1001, 'Ali', 'Amin', 'Active', 30000, 1),
30(1002, 'Halim', 'Alhassan', 'Active', 23000, 1),
31(1003, 'Mahammed', 'Salad', 'Active', 11000, 2),
32(1004, 'Per', 'Johnsen', 'Inactive', 34000, 0),
33(1005, 'Olaf', 'Svenson', 'Active', 67000, 3);
34
35show tables;
36select * from employee;