sql SAS SAS IVIEWTABLE: Orion.Employee organization) File Edit View Tools Data S

Question

sql SAS SAS IVIEWTABLE: Orion.Employee organization) File Edit View Tools Data Solutions Window Help Job Ttle 120101 Director 20102 Sales Manager 20103 Sales Manager 20104 Administration Manager 120105 Secretary I 20106 Office Assist ant 120107 Office Assistant I 120108 Warehouse Assistant I 120109 Warehouse Assist ant 120110 Warehouse Assist.antl 20111 Securty Quard 120112 Security Guard I 120113 Security Guand Sales Management Sales Management Sales Management Administration Results SQL: Employee IDs || Query Results Query Results dministration Administration

Explanation / Answer

a) Answer

SELECT orien.employee_organization.department, Dept_Salary_Total FROM orien.employee_organization INNER JOIN orien.employee_payroll ON orien.employee_organization.Employee_ID=orien.employee_payroll.Employee_ID WHERE orien.employee_payroll.Salary IN (SELECT sum(orien.employee_payroll.Salary) AS Dept_Salary_Total FROM orien.employee_payroll )

Explanation:

->select the department as a cloumn from the emplyoee_organization table and

->select the sum of salary of each department as cloumn and this column using the INNER JOIN,

->SELECT sum(orien.employee_payroll,Salary) AS salary FROM orien.employee_payroll ) this statement used to get the sum of salary of each department.

->orien.employee_organization INNER JOIN orien.employee_payroll ON orien.employee_orgnization.Employee_ID=orien.employee_payroll.Employee_ID this statement represents emplyoee_payroll table joins to employee_organization table using the Employee_ID

=======================================================================================

b) Answer

select orien.employee_organization.Employee_ID, orien.employee_organizatio.Employee_Name, orien.employee_addresses.Department FROM orien.employee_organization INNER JOIN orien.employee_address ON orien.employee_organization.Employee_ID =orien.employee_addresses.Employee_ID ;

explanation:

->orien.employee_organization INNER JOIN orien.employee_address ON orien.employee_organization.Employee_ID =orien.employee_addresses.Employee_ID ;

this statement represents addresses table joins to employee_organization table based on Employee_ID

-> Generating the report using employee_id ,employee_name from employee_organizatin table,Department from Employee_address table using employee_id

=======================================================================================

c) Answer

Creation of view using (a) first query

CREATE VIEW DEPT_VIEW AS SELECT orien.employee_organization.department, Dept_Salary_Total FROM orien.employee_organization INNER JOIN orien.employee_payroll ON orien.employee_organization.Employee_ID=orien.employee_payroll.Employee_ID WHERE orien.employee_payroll.Salary IN (SELECT sum(orien.employee_payroll.Salary) AS Dept_Salary_Total FROM orien.employee_payroll )

Creation of view using (b) second query

select orien.employee_organization.Employee_ID, orien.employee_organizatio.Employee_Name, orien.employee_addresses.Department FROM orien.employee_organization INNER JOIN orien.employee_address ON orien.employee_organization.Employee_ID =orien.employee_addresses.Employee_ID ;

select DEPT_VIEW.Department,EMPLOYEE_VIEW.Employee_Name, orien.employee_payroll.salary , (orien.employee_payroll.salary *100 /DEPT_VIEW.Dept_Salary_Total) AS Percentage from orien.employee_payroll

INNER JOIN EMPLOYEE_VIEW ON orien.employee_payroll.Employee_ID = EMPLOYEE_VIEW.Employee_ID

INNER JOIN EMPLOYEE_VIEW.Department = DEPT_VIEW.Department.

Explanation

-> creates a DEPT_VIEW using first qurery

-> creates a CUSTOMER_VIEW using second query.

->

INNER JOIN EMPLOYEE_VIEW ON orien.employee_payroll.Employee_ID = EMPLOYEE_VIEW.Employee_ID

INNER JOIN EMPLOYEE_VIEW.Department = DEPT_VIEW.Department.

above statement will joins deptment view and employee view first and then joins this to payroll table.

-> generating the report by taking department from DEPT_VIEW ,Employee_Names from EMPlOYEE_VIEW , salary from payroll table and calcuates the percentage with the salary and total dept salary of particular department

Related Questions

Navigate

• Browse (All)
• Browse S
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.