Question 11What does this code do?12345678SELECT e.employee_id, e.first_name, e.last_name, e.salaryFROM employees eWHERE e.salary > (  SELECT AVG(salary)  FROM employees  WHERE department_id = e.department_id); 1 pointIt finds employees who earn more than the average salary in their department.It calculates the average salary for all employees.It groups employees by department.It joins the employees table with itself.

What will the following SQL query return? SELECT AVG(salary) AS average_salary FROM employees WHERE department_id IN (SELECT department_id FROM departments WHERE department_name = 'IT');Select one:a. The number of employees in the IT department.b. The total salary of employees in the IT department.c. The highest salary of employees in the IT department.d. The average salary of employees in the IT department.

SELECT c.nameFROM COMPANY cJOIN EMPLOYEE e ON c.company_id= e.company_idJOIN SALARY s ON e.employee_id = s.employee_idGROUP BY c.nameHAVING AVG(s.salary) >=40000

To display the names of employees who earns more than the average salary of allemployees.SELECT last_name, first_nameFROMemployeeWHEREsalary > AVG(salary);Which change should you make to achieve the desired results?Select one:a.Move the function to the SELECT clause and add a GROUP BY clause and a HAVING clause.b.Move the function to the SELECT clause and add a GROUP BY clause.c.Use a subquery in the WHERE clause to compare the average salary value.d.Change the function in the WHERE clause.

Time left 0:18:35Question 1Not yet answeredMarked out of 1.00Flag questionTipsQuestion textWhich SQL statement produces an error? Select one:a.SELECT department_id, job_id, AVG(salary)FROM emp_dept_vuGROUP BY department_id, job_id;b.SELECT job_id, SUM(salary)FROM emp_dept_vuWHERE department_id IN (10,20)GROUP BY job_idHAVING SUM(salary) > 20000;c.None of the statements produce an error; all are valid. d.SELECT department_id, SUM(salary)FROM emp_dept_vuGROUP BY department_id;e.SELECT *FROM emp_dept_vu;Clear my choiceQuestion 2Not yet answeredMarked out of 1.00Flag questionTipsQuestion textWhich statements would execute successfully?Select one or more:a.SELECT student_name,SUM(subject1) FROM marks WHERE student_name LIKE 'R%'; b.SELECT SUM (subject1+subject2+subject3)FROM marksWHERE student_name IS NULLc.SELECT student_name,subject1 FROM marks WHERE subject1 > AVG(subject1); d.SELECT SUM (DISTINCT NVL(subject1,0)),MAX(subject1)FROM marksWHERE subject1 > subject2;Question 3Not yet answeredMarked out of 1.00Flag questionTipsQuestion textSELECT cust_city, COUNT(cust_last_name) FROM customersWHERE cust_credit_limit > 1000 GROUP BY cust_city HAVING AVG(cust_credit_limit) BETWEEN 5000 AND 6000;Which statement is true regarding the outcome of the above query? Select one:a.It executes successfully.b.It returns an error because WHERE and HAVING clauses cannot be used in the same SELECT statement.c.It returns an error because the BETWEEN operator cannot be used in the HAVING clause.d.It returns an error because WHERE and HAVING clauses cannot be used to apply conditions on the same column. e.Date functionsClear my choiceQuestion 4Not yet answeredMarked out of 1.00Flag questionTipsQuestion textConsider the below tables:Promotions TableColumn NameDatatypeConstraintPromo_idNumberPKPromo_nameVarchar Promo_begin_dateDate Promo_end_dateDate  Sales TableColumn NameDatatypeConstraintPromo_idNumberFKCust_idNumberFKTime_idDate  Customer TableColumn NameDatatypeConstraintcust_idNumberPKcust_nameVarchar The Below query will generate a report showing the promo name along with the customer name for all products that were sold during their promo campaign and before 30th October 2007.SELECT promo_name,cust_name FROM promotions p JOIN sales s ON(time_id BETWEEN promo_begin_date AND promo_end_date) JOIN customer c ON (s.cust_id = c.cust_id) AND time_id < '30-oct-2007'; Which statement is true regarding the above query? Select one:a.It produces an error because the join order of the tables is incorrect.b.It produces an error because equijoin and nonequijoin conditions cannot be used in the samec.It executes successfully but does not give the required result.d.It executes successfully and gives the required result.Clear my choiceQuestion 5Not yet answeredMarked out of 1.00Flag questionTipsQuestion textThe COMMISSION column shows the monthly commission earned by the employee. Emp_IdDept_IdCommission1105002201000310 410600530800630200710 820300   Which tasks would require sub queries or joins in order to be performed in a single step?Select one or more:a.Finding the number of employees who earn a commission that is higher than the average commission of the company b.Finding the total commission earned by the employees in department 10c.Listing the employees who earn the same amount of commission as employee 3d.Listing the departments whose average commission is more that 600 e.Listing the employees whose annual commission is more than 6000 f.Listing the employees who do not earn commission and who are working for department 20 in descending order of the employee ID

Consider the table Employee given below: EmpId EmpName Salary DeptId200 Alex 30000 100201 Tom 20000 101202 Dick 40000 100202 Harry 15000 102205 Mary 50000 101206 Karl 40000 101SELECT EmpName FROM Employee WHERE Salary > ALL (SELECT Salary FROM Employee WHERE DeptId = 100);What will be the outcome of the query given above?