Database Design and SQL Programming Evaluation - MIS602

This assignment focuses on database design and SQL programming, covering various aspects of database management. The solution includes creating tables (employee, department, and salary), inserting data into these tables, and executing a series of SQL queries to retrieve, update, and delete data. The queries demonstrate different SQL operations, including SELECT, INSERT, UPDATE, and DELETE statements, along with clauses like WHERE, ORDER BY, GROUP BY, and JOIN. The assignment also covers tasks such as calculating salaries, filtering data based on specific criteria, and modifying existing data within the database. This practical exercise provides a comprehensive understanding of database design principles and SQL query writing.

Running head: DATABASE DESIGN AND SQL
Database design and SQL
Name of the Student
Name of the University
Authors note

1DATABASE DESIGN AND SQL
Task 1
CREATE TABLE `EMPDB`.`employee` ( `employee_id` INT NOT NULL
AUTO_INCREMENT ,`empname` VARCHAR(25) NOT NULL , `Managerid` INT NOT
NULL , `Dateofhire` DATE NOT NULL , `Jobname` VARCHAR(15) NOT NULL , `Salary`
DECIMAL NOT NULL , `department_id`INT NOT NULL , `DOB` DATE NOT NULL ,
`address` VARCHAR(30) NOT NULL , PRIMARY KEY(`employee_id`) FOREIGN KEY
(`department_id`) REFERENCES Department(`department_id`)) ENGINE =InnoDB
CREATE TABLE `empdb`.`Department` ( `departmentid` INT NOT NULL
AUTO_INCREMENT ,`deptname` VARCHAR(30) NOT NULL , `deptlocation`
VARCHAR(20) NOT NULL , `deptfloor`VARCHAR(20) NOT NULL , PRIMARY KEY
(`departmentid`)) ENGINE = InnoDB;
CREATE TABLE `empdb`.`salary` ( `salary_level` INT NOT NULL , `salarymin`
INT NOTNULL , `salarymax` INT NOT NULL , PRIMARY KEY (`salary_level`))
ENGINE = InnoDB;
Task 2
INSERT INTO `department` (`department_id`, `deptname`,
`deptlocation`, `deptfloor`) VALUES (NULL, 'Development', 'Sydney',
'5th'), (NULL, 'Testing', 'Cannabera', '4th'), (NULL, 'Analysis',
'Adelaide', 'Ground'), (NULL, 'Sales', 'Gabba', '7th'), (NULL, 'Marketing',
'London', '2nd')

2DATABASE DESIGN AND SQL
Task 3
INSERT INTO `employee` (`employee_id`, `empname`, `managerid`,
`dateofhire`, `jobname`, `salary`, `department_id`, `dob`, `address`)
VALUES ('1', 'Tina', '2', '2019-07-16', 'Developer', '85000', '1', '1989-
04-02', '23 rd Evenue'), ('2', 'Juan', '1', '2019-06-04', 'Analyst',
'75000', '2', '1987-05-08', 'Madison Square'), ('3', 'Hughes', '3', '1998-
01-20', 'Analyst', '125000', '3', '1945-07-01', 'Johanson street'), ('4',
'Robert', '3', '2000-04-22', 'analyst', '98000', '5', '1965-07-02', 'Rawdon
street'), ('5', 'Flynn', '1', '2019-02-11', 'Tester', '65000', '2', '1956-
02-15', 'Johnson street')
INSERT INTO `employee` (`employee_id`, `empname`, `managerid`,
`dateofhire`, `jobname`, `salary`, `department_id`, `dob`, `address`)
VALUES ('6', 'Sanchez', '3', '2016-07-23', 'Analyst', '45000', '3',
'1992-08-08', 'Rawdon Street'), ('7', 'Heather', '2', '2014-08-15',
'Manager', '85000', '4', '2012-06-08', 'Loyyed Street'), ('8', 'Miller',
'3', '2011-05-07', 'Developer', '65000', '1', '1974-04-01', 'M G Road'),
('9', 'Joshua', '3', '2019-05-12', 'Developer', '70000', '1', '1990-05-08',
'Eliot Road'), ('10', 'Fernandez', '4', '2014-06-11', 'Analyst', '65000',
'1', '1964-02-21', 'Megan Street')
Task 4
INSERT INTO `salary` (`salary_level`, `salarymin`, `salarymax`) VALUES
('1','40000', '85000'), ('2', '50000', '90000'), ('3', '55000', '105000'), ('4','52000', '92000'), ('5',
'55000', '115000')
Task 5
SELECT * FROM employee

3DATABASE DESIGN AND SQL
Task 6
SELECT empname FROM employee
Task 7
SELECT empname, jobname FROM employee
Task 8
SELECT DISTINCT jobname FROM `employee`
Task 9
UPDATE employee
SET salary = (salary * 112)/100
Increased Salary of the Employees
Task 10
select empname,salary from employee where salary in((select min(salary) from
employee),(select max(salary) from employee) )
Task 11
SELECT employee_id, empname, jobname, salary
FROM employee
WHERE salary>=90000;
Task 12

4DATABASE DESIGN AND SQL
SELECT * FROM employee WHERE jobname="Manager"
Task 13
SELECT * FROM employee WHERE empname="Robert"
Task 14
SELECT *
FROM employee
WHERE salary>=90000 AND jobname="Manager";
Task 15
SELECT *
FROM employee
WHERE salary>=90000 AND jobname="Manager";
Task 16
SELECT * FROM employee WHERE dateofhire>('2001-1-1')
Task 17
SELECT *
FROM employee emp
ORDER BY emp.salary DESC;

5DATABASE DESIGN AND SQL
Task 18
SELECT COUNT(*) FROM employee
Task 19
INSERT INTO `employee` (`employee_id`, `empname`, `managerid`, `dateofhire`,
`jobname`, `salary`, `department_id`, `dob`, `address`) VALUES ('1487', 'Watson', '5', '2017-
03-11', 'Analyst', '88000', '1', '1986-04-30', 'Sqadron Avenue');
Task 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');
Task 21
DELETE FROM employee
WHERE empname="Flynn";
Task 22
SET salary = (salary * 115)/100
WHERE empname="Robert";
Task 23
SELECT department_id, COUNT(*),SUM(salary) FROM employee GROUP BY
department_id

6DATABASE DESIGN AND SQL
Task 24
SELECT * FROM employee WHERE address LIKE '%Avenue'