Assignment 8 Solution

$30.00

Category: Tag:

Description

Create LOCATION Table with columns Location_Id, Regional_Group.

Constraints on LOCATION table: Location_Id Primary Key.

Create DEPARTMENT Table with columns Department_Id, N a m e , Loc ation_ID.

Constraints on DEPARTMENT table: Department_Id Primary Key,Location_Id references LOCATION table.

Create JOB Table with columns Job_Id, Function.

Constraints on JOB table: Job_ID Primary Key.

Function column can have only these values (CLERK, STAFF, ANALYST, SALESPERSON, MANAGER, PRESIDENT ) .

Create EMPLOYEE Table with columns Employee_Id, Last_Name, First_ Name, Middle_Name, Job_Id, Manager_Id, Hire_Date, Salary, Comm, Department_ID.

Constraints on EMPLOYEE table: Employee_Id Primary

Key, Last_Name NotNull, Department_Id references DEPARTMENT table, Job_ID refernces JOB table.

Make at least 8 entries for each table.

Submission link: https://www.dropbox.com/request/7iDTQCBNIJTeE0Qd9dnH

1. Create a view having first name, last_name, salary, commission for all employees.

2 . Create a view having the employee’s annual salary with their names only.

3 . Create a view having the employees who are working in department 20.

4 . Create a view having the employees who are earning salary between 3000 and 4500.

5 . Create a view having the employees who are working in department 10 or 20.

6 . Create a view having the employees whose name starts with “S”.

  1. Create a view having the employees whose name length is 4 and start with

S”.

  1. Create a view having the employee details according to their last_name in ascending order and salaries in descending order.

  1. List out the department wise maximum salary, minimum salary, average salary of the employees.

  1. Create a view having the no. of employees for each month and year, in the ascending order based on the year, month.

  1. List out the department id having at least four employees.

1 2 . How many employees in January month.

  1. List the department id, having greater than or equal to 3 employees joined in April 1985.

  1. Create a view having the common jobs in Research and Accounting Departments in ascending order


error: Content is protected !!