[Solved] DBS301 Assignment 3

Solve the following using tables provided for labs like employees, departments, etc. Copy the commands under each question and also the screen shots of the result. For instance, if you create a view the result will be view is created. Take the screen shot and copy under the command. One member in the group upload the document on BlackBoard.

1. Display department number, department name, and the number of employees working in each department that has the highest number of employees. Use subquery only. (5 marks)

2. Display all the employees who were hired on the day of the week on which the highest number of employees were hired. List the first name and last name columns together with a space in between. Label the column as Full Name and label day as day of the week. Use subquery only. (5 marks)

3. Department 50 needs access to its employee data. Create a view named DEPT50 that contains the employee numbers, employee last names, and department numbers for all employees in department 50. They have requested that you label the view columns EMPNO, EMPLOYEE, and DEPTNO. For security purposes, do not allow an employee to be reassigned to another department through the view. (5 marks)

4. Create a view named emp_hired that has the names and hire dates for all employees who were hired before their managers, along with their manager’s names and hire dates. Label the columns Employee, Emp Hired, Manager, and Mgr Hired, respectively. (5 marks)

5. The Date base Administrator needs you to create a table, which has a primary key constraint, but she wants to name the index to have a different name than the constraint. Create the Locations_Index table based on the following table instance chart. Name the index for the PRIMARY KEY column as LOCATIONS_PK_IDX. (5 marks)

6. Create a report of a list of employees who are up for review in March; so you are requested to do the following: (5 marks)

Write a query to display the last names, month of the date of hire, and hire date of those employees who have been hired in the month of March, irrespective of the year of hire.

7. The Accounting department requires an analysis on maximum and minimum salaries by job, manager, and department. They have asked you to do the following: (5 marks)

Write a query to display the following groupings:

Department_id, job_id

Job_id, manager_id

8. Create the EMP table based on the structure of the EMPLOYEES table. Include only the EMPLOYEE_ID, FIRST_NAME, LAST_NAME, SALARY, and JOB_ID columns for departments 90,80,60, and 50. Name the columns in your new table NO, FIRST_NAME, LAST_NAME, SALARY, and JOB_TITLE, respectively. (5 marks)

9. (15 marks)

10. (5 marks)