Fluent Essays

Program: Oracl for sqlAll requirements in the file attached (((READ THE REQUIREMENTS CAREFULLY PLEASE))))Similarity less than 5\%Plagiarism report neededFor bci3913_sql_assignment.pdf Unformatted Attachment Preview C Department of Computing FINAL ASSIGNMENT COURSE : STRUCTURED QUERY LANGUAGE COURSE CODE : BCI3913 LECTURER : PRASEEDA MANOJ DATE : 07th JUNE 2020 TOTAL MARKS : 100 SEMESTER /SESSION : SEMESTER 2 / 2019-20 PROGRAMME : BCN/BCS The distribution of marks among the parts of the question is indicated. INSTRUCTIONS TO CANDIDATE Please upload your answers to the provided “Final Exam /Assignment” space on Moodle. You may either type your answers in Word or handwrite and scan / photograph them. It is essential that you write your student number at the top of your answers and, if handwriting, on every sheet you use. If you have written or drawn anything on paper, please keep your copy so further photographs can be sent if needed during marking. THIS IS AN OPEN-BOOK EXAM / ASSIGNMENT AND YOU MAY CONSULT YOUR NOTES, BUT YOU MAY NOT CONSULT ANY ONLINE RESOURCE. YOU MUST COMPLETE THE EXAM / ASSIGNMENT UNDER EXAM CONDITIONS. YOU MUST TALK TO NO ONE AND MUST WORK ALONE. INCLUDE THE FOLLOWING DECLARATION AT THE END OF YOUR SUBMISSION: I DECLARE THAT THIS IS MY OWN WORK DO NOT SIGN THIS DECLARATION. This exam / assignment question paper consists of five [05] printed pages including the front page. Final Assignment The following Entity Relationship Diagram illustrates the database diagram of Human Resource Management System for the Muscat Mobiles Company dealing with the distribution of major mobile brands. From the E-R diagram and you are asked to do the following tasks: Task A: Prepare a report using E-R modelling [25 Marks] Outline the aims of your system. In your written report, fully document the design components: this should include details about entities and their relationships, that model the information requirements of the Human Resource System. Your report should contain details about the relations, show all attributes of each entity and identify the primary key and any foreign keys used to form relationships. Task B: Create a new relation and adding records [10 Marks] We want to keep track of the dependents for each employee. Each dependent has a dependentid,name, birthdate and relationship with the employee. Create a new table Dependents in your ORACLE HR database by executing CREATE statements. Choose appropriate data types for your attributes and set the primary and foreign keys that will be needed. BCI3913 Page 2 of 5 Final Assignment Task C: Querying your database [65 Marks] Write and execute single SQL statements to provide answers to each of the following queries. Show your SQL and the query results in your report: 1. Write a query to help H R Director to display unique job id of all employees. [1 Marks] 2. The HR department has requested a report of all employees and their job IDs . Display the last name concatenated with the job ID (separated by a comma) and name the column Employee and Title. [2 Marks] Sample output Employee and Title King , AD_PRES Ernst, IT_PROG 3. The HR department needs a report to display the employee number, last_name, and salary for all employees in departments 100 or 120 in ascending alphabetical order by last_name. Label these columns as EMPLOYEE#, NAME,MONTHLY SALARY. [4 Marks] 4. Display the last names and hire-date of all employees who were hired in 1994 and have “u” in their last name. [4 Marks] 5. Write a query to display the number of people working with the same Manager. [4 Marks] 6. Following query is written by an IT staff to show the total salary of employees in each department. Is there any error in the following query? Justify your answer. Rewrite the query, if any mistake found. SELECT department_id,TOTAL(salary) FROM employees WHERE TOTAL(salary)>20000 GROUP BY employee_id; BCI3913 [5 Marks] Page 3 of 5 Final Assignment 7. Create a report that displays manager number and average salary of its employees. Exclude any group that the average salary is lower than $5000. Make sure you round the average to avoid decimal value. Sort the output in descending order of salary. [5 Marks] Output example: MANAGER_ID LOWEST SALARY 102 9000 202 8400 155 7500 8. Create a join query that displays the employee first name, employee last name, jobid, departmentid, department name and salary for all employees whose department name is IT_PROGRAMMING. Sort the output in descending order of department_name. [5 Marks] 9. Find the Manager id and Manager name of all employees. [5 Marks] (Hint : sample output) Employee Firstname Employee lastname Manager# Manager Firstname Manager Lastname Ahmed Al Zadjali 103 Dr.Malik Said 10. Write a statement that prompts the user to input a manager ID, and afterwards will display employee_ ID, first_name, department_id, and salary for those employee who worked under that manager, provided the employees’s salary is more than 7000. Make sure that display order is sorted based on the employee_id. [5 Marks] 11. Write an SQL statement that display the first name and last name, department id, job id and salary, of employees who works as the same job id as the employee whose employee id is 201. [5 Marks] BCI3913 Page 4 of 5 Final Assignment 12. Write a statement that display the names (First and Last Name) and hire dates for all employees who were hired before their managers, together with their managers’ last_names and hire date. Label the First Name and Last Names as “ Employee Name”, and Manager’s Last Name as “Manager”. [5 Marks] 13. The Oracle server uses constraints to prevent invalid data entry into tables. Constraints enforce rules on the data in a table whenever a row is inserted, updated, or deleted from that table. Explain the different type of constraints with appropriate examples. [15 Marks] END OF QUESTION PAPER BCI3913 Page 5 of 5 ... Purchase answer to see full attachment