View the Exhibit and examine the structure of the CUSTOMERS table. Which two tasks would require subqueries or joins to be executed in a single statement? (Choose two.)

Question

Exhibit

Options

• Alisting of customers who do not have a credit limit and were born before 1980
• Bfinding the number of customers, in each city, whose marital status is 'married'
• Cfinding the average credit limit of male customers residing in 'Tokyo' or 'Sydney'
• Dlisting of those customers whose credit limit is the same as the credit limit of customers residing in
• Efinding the number of customers, in each city, whose credit limit is more than the average credit

• A
  9% (3)
• B
  3% (1)
• C
  15% (5)
• D
  73% (24)

Explanation

Describe the Types of Problems That the Subqueries Can Solve There are many situations where you will need the result of one query as the input for another. Use of a Subquery Result Set for Comparison Purposes Which employees have a salary that is less than the average salary? This could be answered by two statements, or by a single statement with a subquery. The following example uses two statements: select avg(salary) from employees; select last_name from employees where salary < result_of_previous_query ; Alternatively, this example uses one statement with a subquery: select last_name from employees where salary < (select avg(salary)from employees); In this example, the subquery is used to substitute a value into the WHERE clause of the parent query: it is returning a single value, used for comparison with the rows retrieved by the parent query. The subquery could return a set of rows. For example, you could use the following to find all departments that do actually have one or more employees assigned to them: select department_name from departments where department_id in (select distinct(department_id) from employees);

No community discussion yet for this question.