Database Design and SQL

This document covers various SQL queries related to database design and management. It includes creating tables, inserting data, updating data, and retrieving data using SQL. It also covers topics like securing databases, reverse SQL question generation, and more. The document is useful for students studying database management 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.