Answer To: Microsoft Word - MIS602-Assign2-v.1.0.docx MIS602 Assign 3 Page 1 of 5 ASSESSMENT BRIEF Subject Code...
Neha answered on Apr 08 2021
53531/53531.sql
create table employee(
employee_id int primary key,
EmpName varchar(25),
ManagerId int,
DateOfHire Date,
JobName varchar(25),
Salary decimal(10,2),
Department_Id int,
DOB date,
address varchar(30),
salary_level int,
foreign key (salary_level) references salary (salary_level),
foreign key (Department_Id) references Department (Department_Id)
);
Create table Department(
Department_Id int primary key,
DeptName varchar(30),
deptLocation varchar(20),
deptFloor varchar(20)
);
Create table salary(
salary_level int primary key,
salaryMin int,
salaryMax int
);
insert into salary(salary_level,salaryMin,salaryMax)
values(101,90000,100000),
(102,80000,850011),
(103,40000,45000),
(104,50000,55000),
(105,55000,80000);
insert into Department(Department_Id,DeptName,deptLocation,deptFloor)
values(2010,'Manager','Australia','Second'),
(2011,'Coder','Australia','First'),
(2012,'Analyst','Australia','Third'),
(2013,'Sales','Australia','Fourth'),
(2014,'Operations','Australia','Fifth');
Insert into employee(employee_id,EmpName,ManagerId,DateOfHire,JobName,Salary,Department_Id,DOB,address,salary_level)
values(901,'James',5000,'2020-09-18','Manager',100000,2010,'1997-10-01','Australia',101),
(902,'Joy',5001,'2001-09-18','Manager',55000,2010,'1999-10-01','Australia',105),
(903,'Robert',5009,'2001-01-10','Analyst',91000,2012,'1998-10-01','Australia',101),
(904,'Flynn',5002,'2011-08-01','Manager',94000,2010,'1996-10-01','Australia',101),
(905,'James',5003,'2019-07-01','Analyst',45000,2012,'1996-10-01','Australia',103),
(906,'Flynn',5004,'2011-04-03','Programmer',96000,2011,'1994-10-01','Avenue',101),
(907,'Parag',5005,'2010-03-03','Programmer',97000,2011,'1990-10-01','Australia',101),
(908,'Harry',5006,'2002-01-18','Saler',93000,2013,'2000-10-01','Avenue',101),
(909,'Monica',5007,'2003-12-10','Operations',99000,2014,'1999-10-01','Australia',101),
(9010,'Rosy',5008,'2005-11-28','Manager',100000,2010,'1996-10-01','Australia',101);
Select * from employee;
Select EmpName from employee;
Select EmpName, JobName from employee;
Select distinct(JobName) from employee;
Update employee
set salary = salary + (salary * 0.12);
Select EmpName, JobName, salary+(salary * 0.12) As UpdatedSalary from employee;
Select EmpName, salaryMin, salaryMax from employee
inner join salary on salary.salary_level = employee.salary_level;
Select employee_id, EmpName, JobName from employee where salary > 90000;
Select * from employee where JobName like 'Manager';
Select * from employee where EmpName like 'Robert';
Select * from employee where JobName like 'Manager' and salary > 95000;
Select employee_id, EmpName, JobName, DateOfHire from employee where year(DateOfHire) > 2001;
Select * from employee where salary between 55000 and 95000;
Select * from employee order by salary desc;
Select count(employee_id) AS NumberOfEmployees from employee;
Insert into employee(employee_id,EmpName,ManagerId,DateOfHire,JobName,Salary,Department_Id,DOB,address,salary_level)
values(9011,'Shally',5010,'2020-09-18','Analyst',41000,2010,'1987-10-01','Australia',103);
select * from employee where employee_id = 9011;
Insert into employee(employee_id,EmpName,ManagerId,DateOfHire,JobName,Salary,Department_Id,DOB,address,salary_level)
values(1011,'Janet',5095,'2014-10-12','Programmer',90000,2011,'1987-10-01','Australia',101);
select * from employee where employee_id = 1011;
Delete from employee where EmpName like 'Flynn';
Select * from employee where EmpName like 'Flynn';
Update employee
set salary = (salary * 0.15) where EmpName like 'Robert';
Select * from employee where EmpName like 'Robert';
Select DeptName, sum(salary) from employee
inner join Department on Department.Department_Id = employee.Department_Id
group by employee.Department_Id;
Select * from employee where address like 'Avenue';
53531/query1.png
53531/query10.png
53531/query11.png
53531/query12.png
53531/query13.png
53531/query14.png
53531/query15.png
53531/query16.png
53531/query17.png
53531/query18.png
53531/query19.png
53531/query2.png
53531/query20.png
53531/query3.png
53531/query4.png
53531/query5.png
53531/query6.png
53531/query7.png
53531/query8.png
53531/query9.png
53531/Report.docx
Assessment 2 Summary
Data Modelling and Database Design
I have data modelling and database design as one of my core subjects in the University. This contains information about the creation,...