MIS602: Database Programming Practical Assignment Solution

This assignment solution demonstrates the creation of an employee database using SQL. The solution begins with creating two tables, 'employee' and 'department', defining their attributes, data types, and primary/foreign key constraints. It then populates these tables with sample data, including employee details, job roles, salaries, and department information. Following data insertion, the solution provides a series of SQL queries to perform various operations. These queries include selecting all data, retrieving specific columns, filtering data based on different criteria (e.g., salary, job title, date of hire), updating salaries, calculating total employees, and grouping data. The solution covers a wide range of SQL functionalities, demonstrating practical database management skills and providing a comprehensive example for students to learn from and adapt for their assignments.

create table employee (employee_id integer (10) primary key NOT NULL, empname varchar2
(25), managerid integer (10), Dateofhire date, Jobbname varchar2 (25), Salary float (10, 2),
department_id integer (10), DOB date, address varchar2(30), foreign key (department_id)
references department (department_id));
create table department (department_id integer (10) primary key NOT NULL, Deptname
varchar2 (30), deptLocation varchar2 (30), deptFloor varchar2 (30));
create table salary (salary_level integer (10) primary key NOT NULL, salarymin integer (10),
salarymax integer (10));
insert into employee values (01,'Robert', 1011, '1994-10-20', 'Manager', 85000.00, 2011, '1978-
10-12', '22 Sydney');
insert into employee values (02,'Flynn', 1012, '1998-04-22', 'Programmer', 105000.00, 101,
'1982-02-10', '87 Adelaide');
insert into employee values (03,'Andrew', 1013, '2000-12-20', 'Accountant', 65000.00, 102,
'1974-02-22', '23 Central Avenue');
insert into employee values (04,'Steve', 1014, '2002-05-13', 'Manager', 96000.00, 103, '1989-11-
24', '54 Sydney');
insert into employee values (05,'Ricky', 1015, '1999-08-24', 'Tester', 45000.00, 104, '1988-05-16',
'93 Perth');
insert into employee values (06,'Mathew', 1016, '1997-11-20', 'Project lead', 98000.00, 104,
'1982-02-18', '32 Canberra');

insert into employee values (07,'Adam', 1017, '1996-12-10', 'Programmer', 65000.00, 2011,
'1984-08-13', '101 Victoria');
insert into employee values (08,'Lee', 1018, '1998-03-11', 'Tester', 46000.00, 2011, '1979-02-12',
'192 New South Wales');
insert into employee values (09,'Kane', 1019, '1997-11-29', 'Accountant', 66000.00, 102, '1983-
02-24', '223 Perth');
insert into employee values (10,'James', 1020, '1993-03-22', 'Manager', 35000.00, 101, '1981-05-
12', '03 Melbourne');
insert into department values (101, 'Manager', 'A2', 'E1');
insert into department values (102, 'IT', 'A5', 'E3');
insert into department values (103, 'Finance', 'A4', 'E7');
insert into department values (104, 'Finance', 'A15', 'E32');
insert into department values (2011, 'IT', 'A6', 'E13');
insert into salary values (10, 15000, 25000);
insert into salary values (11, 26000, 35000);
insert into salary values (12, 36000, 55000);
insert into salary values (13, 56000, 85000);
insert into salary values (14, 86000, 120000);
SELECT * FROM employee;

SELECT empname FROM employee;
Select empname, jobname from employee;
select DISTINCT (Jobname) from employee;
update employee set salary = salary + (salary * (12/100));
select empname, jobname, salary from employee;
select empname, salary from employee where salary = (select min(salary) from employee);
select empname, salary from employee where salary = (select max(salary) from employee);
select employee_id, empname, jobname from employee where salary> 90000;
select * from employee where jobname = 'Manager';
select * from employee where empname = 'Robert';
select * from employee where jobname = 'Manager' and salary> 95000;
select employee_id, empname, jobname, Dateofhire from employee where Dateofhire> '2001-
12-31';
select * from employee where salary between 55000 and 95000;
select * from employee order by (salary) desc;
select count(empname) as 'Total Employee' from employee;
insert into employee values (11,'Shane', 1018, '1998-02-12', 'Analyst', 545000.00, 101, '1999--
10-13', '156 NSW');

insert into employee values (1011, 'Janet', 5095, '2014-10-12', 'Programmer', 90000, 2011, '1998-
08-23', '346 Perth');
delete from employee where empname= 'Flynn';
update employee set salary = salary + (salary * (15/100)) where empname= 'Robert';
select count(employee_id) as 'Total employees', Department, sum(salary) as 'Total salary' from
employee, department where employee.department_id=department.department_id group by
employee.department_id;
select * from employee where address like ' %Avenue';