Task 1 CREATE TABLE EMPDB

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

DATABASE DESIGN AND SQL
1
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')

DATABASE DESIGN AND SQL
2
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