Database Design and SQL Implementation for MIS602 Assessment Two

This assignment solution provides a comprehensive overview of database design and SQL query implementation, addressing questions related to creating tables, inserting data, and performing various SQL operations. The solution includes SQL code for creating employee, department, and salary tables, inserting sample data into these tables, and executing queries for data retrieval, filtering, sorting, and updating. Specific queries demonstrate how to select all employees, select employee names, select distinct job names, update salaries, find employees with minimum and maximum salaries, and filter employees based on salary ranges, job titles, and hire dates. Additionally, the assignment covers aggregate functions like COUNT and SUM, as well as the use of LIKE operator for pattern matching. The document concludes with a bibliography referencing relevant resources on database systems and SQL.

Running head: DATABASE DESIGN AND SQL
DATABASE DESIGN AND SQL
Name of the Student
Name of the University
Author Note

1DATABASE DESIGN AND SQL
Question 1:
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
);
CREATE TABLE `department` ( `department_id` INT NOT NULL
AUTO_INCREMENT ,`deptname` VARCHAR(30) NOT NULL , `deptlocation`
VARCHAR(20) NOT NULL , `deptfloor`VARCHAR(20) NOT NULL , PRIMARY KEY
(`departmentid`));
CREATE TABLE `salary` ( `salary_level` INT NOT NULL , `salarymin` INT NOTNULL ,
`salarymax` INT NOT NULL , PRIMARY KEY (`salary_level`));

2DATABASE DESIGN AND SQL
Question 2:
INSERT INTO `employee` (`employee_id`, `empname`, `managerid`, `dateofhire`, `jobname`,
`salary`, `department_id`, `DOB`, `address`) VALUES
(1, 'Flynn Jones', 105, '2010-07-21', 'Finance manager', '40000.00', 4, '1980-07-21', 'Sydney '),
(2, 'Smith James', 102, '2015-02-23', 'Executive manag', '59214.68', 2, '0000-00-00', 'Wesley'),
(3, 'Nick Jonas', 102, '2016-03-08', 'Operational Man', '78952.90', 1, '1987-08-21', 'Sydney '),
(4, 'Harry Smith', 102, '2014-03-21', 'Executive manag', '59214.68', 4, '1980-03-21', 'Sydney '),
(5, 'Rachel smith', 104, '2010-07-21', 'Intern ', '19738.22', 3, '1980-07-21', 'Sydney '),

3DATABASE DESIGN AND SQL
(6, 'Robert smith ', 105, '2013-07-21', 'Writer ', '59214.68', 5, '1989-08-21', 'Sydney '),
(7, 'James Rodger', 106, '2013-04-23', 'Executive manag', '78952.90', 5, '1990-02-03', 'Wesley'),
(8, 'Richie Steve', 106, '2010-03-25', 'Manager', '98691.14', 5, '1988-02-24', 'Wesley'),
(10, 'Lee Smith ', 104, '1997-07-21', 'Finance manager', '78952.90', 4, '1978-08-21', 'Sydney '),
(12, 'Smiley john ', 107, '2002-04-23', 'Manager', '98691.14', 4, '1980-02-12', 'Wesley');
Question 3:
INSERT INTO `department` (`department_id`, `deptname`, `deptlocation`, `deptfloor`)
VALUES (1, 'Manager', 'Sydney', Null), (2, 'Operational', 'Sydney', NULL), (3, ‘Writer’,
‘Wesley’, NULL), (4, ‘Finance’, ‘Sydney’, NULL), (5, ‘HR Manager, ‘Sydney’, NULL);
Question 4:
INSERT INTO `salary` (`salary_level`, `salarymin`, `salarymax`) VALUES ('1','10000', '20000'),
('2', '15000', '25000'), ('3', '30000', '40000'), ('4','35000', '50000'), ('5', '50000', '100000');

4DATABASE DESIGN AND SQL
Question 5:
SELECT * FROM employee;
Question 6:
SELECT empname FROM employee;
Question 7:
SELECT empname, jobname FROM employee;
Question 8:
SELECT DISTINCT jobname FROM `employee`;
Question 9:
UPDATE employee
SET salary = (salary * 112)/100;
Select * from employee;

5DATABASE DESIGN AND SQL
Question 10:
Select empname, salary from employee where salary in((select min(salary) from employee),
(select max(salary) from employee) )
Question 11:
SELECT employee_id, empname, jobname, salary
FROM employee
WHERE salary>=90000;
Question 12:
SELECT * FROM employee WHERE jobname="Manager";
Question 13:
SELECT * FROM employee WHERE empname="Robert";
Question 14:
SELECT *
FROM employee
WHERE salary>=90000 AND jobname="Manager";

6DATABASE DESIGN AND SQL
Question 15:
SELECT * FROM employee WHERE dateofhire>('2001-1-1');
Question 16:
SELECT * FROM `employee` WHERE salary BETWEEN 55000 and 95000;
Question 17:
SELECT *
FROM employee emp
ORDER BY emp.salary DESC;
Question 18:
SELECT COUNT(*) FROM employee
Question 19:
INSERT INTO `employee` (`employee_id`, `empname`, `managerid`, `dateofhire`, `jobname`, `s
alary`, `department_id`, `dob`, `address`) VALUES ('13', 'Zara', '103', '2017-010-18', 'Analyst', '8
8000', '1', '1989-07-21', 'Northern Avenue');

7DATABASE DESIGN AND SQL
Question 20:
INSERT INTO `employee` (`employee_id`, `empname`, `managerid`, `dateofhire`, `jobname`,
`salary`, `department_id`, `dob`, `address`) VALUES ('1102', 'Janet', '5095', '2014-10-12',
'TestingEngineer', '90000', '2011', '1975-05-11', 'Wallington Street');
Question 21:
DELETE FROM `employee` WHERE `employee`.`empname` = ‘Flynn Jones’;
Question 22:
SET salary = (salary * 115)/100
WHERE empname="Robert";
Question 23:
SELECT department_id, COUNT(*),SUM(salary) FROM employee GROUP BY
department_id;
Question 24:
SELECT * FROM employee WHERE address LIKE '%Avenue';

8DATABASE DESIGN AND SQL
Bibliography
[1]S.A. Samy, Implementation of Database models with SQL Queries for a typical mobile
application using c. Intelligent Systems And Communication (NCISC-2016), p.155, 2016.
[2] I.Shingari and P.Sharma, Securing Database using SQL Injection: A Review. IITM Journal
of Management and IT, 7(1), pp.52-56, 2016.
[3] K.Atchariyachanvanich, S.Nalintippayawong and T.Julavanich, Reverse SQL Question
Generation Algorithm in the DBLearn Adaptive E-Learning System. IEEE Access, 7, pp.54993-
55004, 2019.
[4] C.Coronel and S.Morris, Database systems: design, implementation, & management.
Cengage Learning, 2016.