Database programming evaluation practical

TASK 1:

· CREATE TABLE EMPLOYEE

(employee_id INT NOT NULL UNIQUE, empname VARCHAR(25) NOT NULL, managerid INT NOT NULL, dateofhire DATE, jobname VARCHAR(15), salary DECIMAL(10,2), department_id INT NOT NULL, DOB DATE, address VARCHAR(30),

PRIMARY KEY (employee_id),

UNIQUE (employee_id),

FOREIGN KEY (department_id) REFERENCES DEPARTMENT (department_id) ON DELETE SET NULL ON UPDATE CASCADE);

· CREATE TABLE DEPARTMENT

(department_id INT NOT NULL, deptname VARCHAR(30), deptlocation VARCHAR(20), deptfloor VARCHAR(20), PRIMARY KEY (department_id));

· CREATE TABLE SALARY

(salary_level INT NOT NULL, salarymin INT, salarymax INT, PRIMARY KEY (salary_level));

TASK 2:

INSERT INTO EMPLOYEE (employee_id, empname, managerid, dateofhire, jobname, salary, department_id, DOB, address)

VALUES (01, ‘SMITH’, 001, ‘01-01-2000’, ‘HR’, 30000, 111, ‘1990-04-05’, Abuklea Rd. 21),

(02, ‘JOHN’, 002, ‘03-04-1998’, ‘Web Designer’, 56000, 112, ‘1988-05-04’, Culloden Avenue 02),

(03, ‘PETER’, 003, ‘07-11-1999’, ‘Manager’, 70000, 113, ‘1986-07-23’, Victoria Rd. 34),

(04, ‘ROBERT, 004, ‘12-10-1998’, ‘Manager’, 110000, 114, ‘1992-06-30’, Waterloo Avenue 22),

(05, ‘TOM’, 005, ‘06-04-2002’, ‘Manager’, 99000, 115, ‘1987-07-07’, Busaco Rd. 54),

(06, ‘FLYNN’, 006, ‘23-08-1997’, ‘JAVA Developer’, 88000, 116, ‘1982-09-16’, Kings Cross 26),

(07, ‘THOMAS’ 007, ‘11-11-2006’, ‘Manager’, 105000, 117, ‘1990-08-24’, Meroo St. 44),

(08, ‘HARRY’, 008, ‘05-12-2005’, ‘Architecture’, 95000, 118, ‘1988-10-22’, Epping Rd. 04),

(09, ‘HENRY’ 009, ‘10-10-1999’, ‘Manager’, 91000, 119, ‘1986-12-20’, Carlingford Rd. 66),

(10, ‘JACK’ 010, ‘28-02-2001’, ‘Software Developer’, 102000, 120, ‘1982-01-10’, Chatswood St. 06);

TASK 3:

INSERT INTO DEPARTMENT (department_id, deptname, deptlocation, deptfloor)

VALUES (111, ‘Finance’, ‘Sydney’, 2),

(112, ‘Service’, ‘Darwin’, 5),

(113, ‘Production’, ‘Perth’, 3),

(114, ‘Quality Assurance’, ‘Melbourne’, 1),

(115, ‘Information Technology’, ‘Tasmania’,7);

Task 4:

INSERT INTO SALARY (salary_level, salarymin, salarymax)

VALUES (1, 2000, 3000), (2, 5000, 5200), (3, 3300, 3600), (4, 1500, 2000), (5, 4400, 5000);

TASK 5:

SELECT * “No. of Employees”

FROM EMPLOYEE;

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 + (salary * 12/100));

SELECT empname, jobname, salary

FROM EMPLOYEE;

TASK 10:

SELECT empname, max(salary) As MaxSalary, min(salary) As MinSalary

FROM EMPLOYEE

GROUP BY empname;

SELECT empname FROM EMPLOYEE

WHERE salary = (SELECT MAX(salary) FROM EMPLOYEE);

SELECT empname FROM EMPLOYEE

WHERE salary = (SELECT MIN(salary) FROM EMPLOYEE);

TASK 11:

SELECT employee_id, empname, jobname

FROM EMPLOYEE

WHERE salary > 90000;

TASK 12:

SELECT * “No. of Employees”

FROM EMPLOYEE

WHERE jobname = ‘manager’;

TASK 13:

SELECT employee_id, empname, managerid, dateofhire, jobname, salary, department_id, DOB, address

FROM EMPLOYEE

WHERE empname = ‘ROBERT’;

TASK 14:

SELECT * “No. of Employees”

FROM EMPLOYEE

WHERE jobname = ‘manager’ AND salary > 95000;

TASK 15:

SELECT employee_id, empname, jobname, dateofhire

FROM EMPLOYEE

WHERE dateofhire >= ‘28-02-2001’;

TASK 16:

SELECT * “No. of Employees”

FROM EMPLOYEE

WHERE salary BETWEEN 55000 AND 95000;

TASK 17:

SELECT * “No. of Employees”

FROM EMPLOYEE

ORDER BY salary DESC;

TASK 18:

SELECT COUNT(employee_id)

FROM EMPLOYEE;

TASK 19:

INSERT INTO EMPLOYEE (employee_id, empname, managerid, dateofhire, jobname, salary, department_id, DOB, address)

VALUES (11, ‘SAM’, 011, ‘03-09-1999’, ‘ANALYST’, 98500, 121, ‘1990-07-01’, Kent St. 33);

TASK 20:

INSERT INTO EMPLOYEE

VALUES (1011, ‘JANET’, 5095, ‘12-10-2014’, ‘PROGRAMMER’, 90000, 2011, ‘1999-08-25’, Pitt St. 10);

TASK 21:

DELETE FROM EMPLOYEE

WHERE empname = ‘FLYNN’;

TASK 22:

UPDATE EMPLOYEE

SET salary = (salary + (salary * 15/100))

WHERE empname = ‘ROBERT’;

TASK 23:

SELECT department_id,

COUNT (*) “No. of Employees”,

SUM (salary)

FROM EMPLOYEE

GROUP BY department_id;

TASK 24:

SELECT * “No. of Employees”

FROM EMPLOYEE

WHERE address LIKE ‘Avenue%’;