Database Design and SQL Queries for MIS602 Assignment 1

This assignment solution demonstrates the creation and manipulation of a relational database using SQL queries. The student begins by creating three tables: `department`, `employee`, and `salary`, defining their respective attributes and primary keys. Foreign key relationships are established between the `employee` and `department` tables. The solution then proceeds to insert data into these tables, populating them with sample employee, department, and salary information. A series of SQL queries are implemented to retrieve, modify, and analyze the data. These queries cover a range of operations, including selecting all data from a table, selecting specific columns, filtering data based on conditions (e.g., salary, job title, date of hire), updating salaries, deleting records, and counting the number of employees. The solution encompasses a variety of SQL commands, including `CREATE TABLE`, `INSERT INTO`, `ALTER TABLE`, `SELECT`, `UPDATE`, `DELETE`, `WHERE`, `ORDER BY`, `DISTINCT`, `COUNT`, and `GROUP BY`, showcasing a comprehensive understanding of database management principles. The student also provides an overall experience of the assignment and the bibliography.

Running head: ASSIGNMENT 1
ASSIGNMENT 1
Name of the Student:
Name of the University:
Author note

1
ASSIGNMENT 1
Task 1
I have developed the database using sql queries. For the first I have been asked to create
three tables with the ERD as provided below:
At first I created the department table as with the help of the following SQL queries:
CREATE TABLE `department` (
`Department_ID` int(11) NOT NULL,
`DeptName` varchar(30) NOT NULL,
`DeptLocation` varchar(20) NOT NULL,
`DeptFloor` varchar(30) NOT NULL
);

2
ASSIGNMENT 1
I created the department table before the employee table as the employee table is linked
directly with the department table and hence, the primary was required to be set in the
department table before it can be used in the employee table. After completion of the department
table the employee table has been created with the flowing command:
CREATE TABLE `employee` (
`Employee_ID` int(11) NOT NULL,
`EmpName` varchar(25) NOT NULL,
`ManagerID` int(11) NOT NULL,
`DateOfHire` date NOT NULL,
`JobName` varchar(15) NOT NULL,
`Salary` decimal(10,2) NOT NULL,
`Department_ID` int(11) NOT NULL,
`DOB` date NOT NULL,
`Address` varchar(30) NOT NULL
) ;
The salary table was developed by me with the following command.
CREATE TABLE `salary` (
`Salary_Level` int(11) NOT NULL,
`SalaryMin` int(11) NOT NULL,

3
ASSIGNMENT 1
`SalaryMax` int(11) NOT NULL
) ;
After the three tables were developed I decided to create the primary keys for each of the
table and the following commands were used for creating the primary keys:
ALTER TABLE `department`
ADD PRIMARY KEY (`Department_ID`);
ALTER TABLE `employee`
ADD PRIMARY KEY (`Employee_ID`),
ADD KEY `Department_ID` (`Department_ID`);
ALTER TABLE `salary`
ADD PRIMARY KEY (`Salary_Level`);
Now, that all the primary keys are set I would like to form a reference in between the
employee table and department table. Therefore, I developed a foreign key for the employee
table by using the following commands:
ALTER TABLE `employee`
ADD CONSTRAINT `employee_ibfk_1` FOREIGN KEY (`Department_ID`) REFERENCES
`department` (`Department_ID`);
The following database has been developed:

4
SALARY
Salary_level(PK)
salarymin
salarymax
DEPARTMENT
Department_id (PK)
DeptName
DeptName
deptFloor
EMPLOYEE
employee_id (PK)
EmpName
Managerid
Jobname
Salary
Department_id (FK)
DOB
address
ASSIGNMENT 1
Task 2
It was asked to insert 10 data into the employee table, however it was important that the
employee should belong to a department. Hence, task 3 was completed by me before coming to
task 2. The following command was used for inserting data in the employee table:
INSERT INTO `employee` (`Employee_ID`, `EmpName`, `ManagerID`, `DateOfHire`,
`JobName`, `Salary`, `Department_ID`, `DOB`, `Address`) VALUES

5
ASSIGNMENT 1
(1001, 'Harry', 5003, '2016-10-14', 'Senior associate', '5000.00', 20044, '1994-09-18', '98 Pier
Avenue'),
(1002, 'Benson', 5001, '2015-09-17', 'Manager', '9200.00', 2001, '1990-05-11', '67 Fr street'),
(1003, 'Richards', 5002, '2013-03-04', 'Junior Associat', '4700.00', 2007, '1990-12-31', '44 Jf
Avenue'),
(1004, 'Roberto', 5001, '2018-06-12', 'Chief Engineer', '7100.00', 2011, '1990-08-23', '55 Rm
road'),
(1005, 'Stephen', 5003, '2017-09-17', 'Analyst', '5700.00', 2008, '1998-12-10', '45 west end'),
(1006, 'Robert', 5004, '2019-09-10', 'Engineer', '6700.00', 2007, '1990-12-31', '56 Ternt road'),
(1007, 'Henry', 5003, '2011-08-05', 'Analyst', '5100.00', 2001, '1998-04-21', '45 Rt street'),
(1008, 'Kevin', 5002, '2010-05-14', 'Manager', '9300.00', 2004, '2019-06-12', '46 Rd street'),
(1009, 'Flynn', 5004, '2016-08-14', 'Engineer', '6300.00', 2004, '2019-12-10', '54 De road'),
(1010, 'Mike', 5001, '2015-11-06', 'Manager', '9500.00', 2004, '1990-08-07', 'CS Avenue street');
Task 3
The following command was used for inserting data in the department table:
INSERT INTO `department` (`Department_ID`, `DeptName`, `DeptLocation`, `DeptFloor`)
VALUES
(2001, 'Human Resources', 'Sydney', 'Second'),
(2004, 'Finnance', 'Melbourne', 'First'),

6
ASSIGNMENT 1
(2007, 'Operations', 'Sydney', 'Second'),
(2008, 'Development', 'Hobbart', 'First'),
(2011, 'Accounts', 'Sydney', 'Second');
Task 4
The following command was used for inserting data in the salary table:
INSERT INTO `salary` (`Salary_Level`, `SalaryMin`, `SalaryMax`) VALUES
(1, 3000, 3500),
(2, 3500, 4500),
(3, 4500, 5000),
(4, 5000, 7000),
(5, 7000, 10000);
Task 5
The following query was used to display the information about the employees in the
employee table:
SELECT * FROM `employee` ;
Task 6
The following query has been used for displaying all the names of the employees:
SELECT EmpName FROM `employee` ;

7
ASSIGNMENT 1
Task 7
The following query has been used to display the name of all the employees and their
jobname:
SELECT EmpName, Jobname FROM `employee` ;
Task 8
The following query displays all the the unique jobname for all the employees:
SELECT DISTINCT JobName FROM `employee` ;
It has been made sure that a same JobName is not repeated twice and hence the command
DISTINCT has been used with the select statement.
Task 9
The flowing query has been used to increase the salary for all the employees by 12%.
UPDATE employee SET Salary = Salary + (salary * 12 / 100);
Task 10
The flowing query has been used to display the employee names with minimum and
maximum salary:
SELECT EmpName,salary FROM employee
where salary = (select max(salary) from employee) OR salary = (select min(salary) from
employee);

8
ASSIGNMENT 1
Task 11
The following query has been used to display the employee id, employee name, jobname
of all the employees whose salary is greater than 90,000 P.A:
SELECT Employee_ID, EmpName,Jobname FROM `employee`
HAVING (Salary*12) > 90000;
For obtaining the perfect result I have assumed the monthly salary of the employees and inserted
in the table. Hence I multiplies 12 with the original data to fetch the yearly salary.
Task 12
The following query has been used to display the all the details of all the employees
whose jobname is Manager:
SELECT * FROM `employee` WHERE JobName = "Manager";
Task 13
The following query has been used to display the all the details of the employee whose
name is Robert:
SELECT * FROM `employee` WHERE EmpName = "Robert";
Task 14
The following query has been used to display all the details of the employee who work as
a manager and have salary greater than 95000 P.A:
SELECT * FROM `employee`

9
ASSIGNMENT 1
HAVING (Salary*12) > 90000 and JobName = "Manager";
Task 15
The following query has been used to display employeeid, employee name, jobname and
date of joining of all the employees who joined after year 2001:
SELECT Employee_ID, EmpName,Jobname, DateOfHire FROM `employee`
WHERE YEAR(DateOfHire) > 2001;
Task 16
The following query has been used to display the list of all the employees whose annual
salary is within the range 55000 and 95000:
SELECT Employee_ID, EmpName,Jobname FROM `employee`
HAVING (Salary*12) < 95000 AND (Salary*12) > 55000;
Task 17
The following query has been used to display the list of all the employees in the
descending order of their salaries:
SELECT EmpName,Salary FROM `employee`
ORDER BY Salary DESC;
Task 18
The following query has been used to count the number of employees in the employee
table:

10
ASSIGNMENT 1
SELECT COUNT(EmpName) FROM `employee`
Task 19
The following query has been used to Insert a new record in the employee table:
INSERT INTO `employee` (`Employee_ID`, `EmpName`, `ManagerID`, `DateOfHire`,
`JobName`, `Salary`, `Department_ID`, `DOB`, `Address`) VALUES
(11, 'James', 3, '2019-10-14', 'Analyst', '5000.00', 4, '1994-09-18', '98 Rd street');
Task 20
The following query has been used to insert a new record in the employee table:
INSERT INTO `employee` (`Employee_ID`, `EmpName`, `ManagerID`, `DateOfHire`,
`JobName`, `Salary`, `Department_ID`, `DOB`, `Address`) VALUES
(1011, 'Janet', 5095, '2014-10-12', 'Programmer', '90000.00', 2011, '1994-09-18', '98 Rd street');
Task 21
The following query has been used to delete the record of the employee whose name is
‘Flynn’:
DELETE FROM employee WHERE EmpName = "Flynn";
Task 22
The following query has been used to update the salary by 15% of the employee whose
employee name is ROBERT:
UPDATE employee SET Salary = Salary + (salary * 15 / 100) WHERE EmpName = "Robert";

11
ASSIGNMENT 1
Task 23
The following query has been used to find the number of staff working in each
department:
SELECT COUNT department.DeptName,(employee.EmpName), SUM(employee.Salary)
FROM department
INNER JOIN employee ON department.Department_ID = employee.Department_ID
GROUP BY department.DeptName;
Task 24
The following query has been used to find all employees with the string ‘Avenue’ in their
address:
SELECT * FROM employee WHERE Address LIKE '%Avenue%';
Overall Experience
I would like to state the overall experience of completion of the assignment has been
immensely helpful for me in understanding the basics of database and sql. This would be help be
further in the advanced studies on database. The table developments and query development so
that data can be fetched from the tables were fascinating and helped grow interest on this subject.
Bibliography
Harris, R. B., & Vaught, K. L. (2019). The recovery care and treatment center: A database design
and development case. Journal of Information Systems Education, 19(3), 4.

12
ASSIGNMENT 1
Stonebraker, M., Deng, D., & Brodie, M. L. (2017). Application-database co-evolution: A new
design and development paradigm. New England Database Day, 1-3.