BNUcx9E5

1DROP SCHEMA IF EXISTS test;
2create schema test;
3
4use test;
5
6/*This is a test table*/
7
8DROP TABLE IF EXISTS employee;
9create table employee (
10emp_no integer,
11emp_fname varchar(30),
12emp_lname varchar(50) not null,
13emp_status varchar(10) default 'Active',
14emp_salary numeric(7,2),
15dept_no integer,
16constraint employee_pk primary key (emp_no)
17);
18
19DROP TABLE IF EXISTS department;
20create table department (
21dep_no varchar(3),
22dep_name varchar(10),
23loc varchar(40),
24dep_head varchar(4);
25constraint department_pk primary key (dep_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;
37
38select emp_no, emp_fname
39from employee;
40
41update employee
42set emp_status = 'Inactive'
43where emp_no = 1002;
44
45show tables;
46select * from employee;
47
48delete from employee
49where emp_no = 1003;
50
51show tables;
52select * from employee;