Note that the EMPLOYEES and the DEPARTMENTS tables referred in the following que

Question

Note that the

EMPLOYEES

and the

DEPARTMENTS

tables referred in the following questions are the ones in the HR account.

Q6: Give the SQL statement that creates a view called

M10V03

, which consists of first name, last name,

department id of employees

LEFT

OUTER

JOIN

on table

DEPARTMENTS

using the

MANAGER

ID

as

the join condition.

Q7: Give the SQL statement that finds the average salary for employees via

M10V01

Q8: Give the SQL statement that increases the salary of Jason Mallin by 20% via

M10V01

Q9: Give the SQL statement that counts the number of employees via

M10V02

Explanation / Answer

Q6)

Give the SQL statement that creates a view called

M10V03

, which consists of first name, last name,

department id of employees

LEFT

OUTER

JOIN

on table

DEPARTMENTS

using the

MANAGER

ID

as

the join condition.

Query:

create view M10V03 as select Emp.firstname,Emp.lastname,Dept.departmentID from employee Emp LEFT OUTER JOIN department Dept ON Emp.managerID=Dept.managerID;

Q7)

Give the SQL statement that finds the average salary for employees via M10V01

Query:

Create or replace view M10V03 as select departmentID,avg(salary) as avg_sal from employee group by departmentID;

Q8)

Give the SQL statement that increases the salary of Jason Mallin by 20% via M10V01

Query:

Update employee set salary = salary +salary *0.2 where firstname=’Jason Mallin’

Q9) GIve the SQL statment that counts the number of employees via M10V02

create or replace view M10V02 as select count(employee) from employees;

Related Questions

Navigate

• Browse (All)
• Browse N
• Subjects

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