MIS602 Data Modelling and Database Design - Assessment 2

Assessment Two – Database Programming Evaluation Practical

Task 1: Create three tables with relevant keys as suggested in the above diagram
Task 2: Insert record of 10 employees in the employee table
Task 3: Insert record of 5 departments in the department table
Task 4: Insert record of 5 salary levels in the salary table
Task 5: Write a query to display the information about the employees in the employee table
Task 6: Write a query to display the name of all the employees
Task 7: Write a query to display the name of all the employees and their jobname.
Task 8: Write a query in SQL to display the unique jobname for all the employees
Task 9: Write a query to increase the salary for all the employees by 12%. Display the empname, jobname and salary after the increment

Task 10: Write a query to display the employee names with minimum and maximum salary.
Task 11: Write a query to display the employee id, employee name, jobname of all the employees whose salary is greater than 90,000 P.A.
Task 12: write a query to display the all the details of all the employees whose jobname is Manager. (Hint: While entering the records for employee, make sure to add manager as jobname for a few employees.)
Task 13: Write a query to display the all the details of the employee whose name is Robert. (Hint: While entering the records for employee, make sure to add Robert as empname for at least one employee.)
Task 14: Write a query to display all the details of the employee who work as a manager and have salary greater than 95000 P.A.
Task 15: Write a query to display employeeid, employee name, jobname and date of joining of all the employees who joined after year 2001.
Task 16: Write a query to display the list of all the employees whose annual salary is within the range 55000 and 95000.( Hint: make sure to add the salary in this range while entering records in the employee table)
Task 17: Write a query to display the list of all the employees in the descending order of their salaries.
Task 18: Write a query to count the number of employees in the employee table.
Task 19: Insert a new record in the employee table and add ANALYST as their jobname. The other fields can be added as per your choice
Task 20: Insert a new record in the employee table with the following data fields

employee_id= 1011
empname= Janet
jobname= PROGRAMMER
managerid= 5095
dateofhire= 12-10-2014
salary= 90000
department_id=2011

Task 21: Write a query to delete the record of the employee whose name is ‘Flynn’.
(Hint: Make sure to add a record with employee name ‘Flynn’ in the beginning.
Task 22: Write a query to update the salary by 15% of the employee whose employee name is ROBERT.
Task 23: Write a query to find the number of staff working in each department and the sum of their salaries.
Task 24: Write a query to find all employees with the string ‘Avenue’ in their address