Task 1 CREATE TABLE EMPDB

DATABASE DESIGN AND SQL DATABASE DESIGN AND SQL Database design and SQL Name of the Student Name of the University Authors note Task 1 CREATE TABLE EMPDB.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

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'