Week5 - Usage of Subqueries to Solve Queries / Explore

The questions can come in any order, so make sure you are selecting right option for all questions.

Which of the following are not valid subquery type:

(b) All are valid

There cannot be a subquery which is independent of the outer query.

(b) False

What is true about the ANY operator used for sub-queries?

(c) Returns rows that match any value in a list/sub-query

What is true about co-related sub-queries?

(b) The sub-queries which reference a column used in the main query are called co-related sub-queries

A subquery is evaluated . . . . . . for the entire parent statement.

(a) once

A ___________ subquery is one that depends on a value in the outer query.

(b) correlated

You need to find out the employees which belong to the department of 'Jessica Butcher' and have salary greater than the salary of 'Jessica Butcher' who has an employee ID of 40. Which of the following queries will work?

(d)
SELECT first_name, last_name
FROM employees
WHERE department = (SELECT department
			FROM employees
			WHERE first_name = 'Jessica'
			AND last_name = 'Butcher'
			AND employee_id = 40)
AND salary > (SELECT salary
			 FROM employees
			 WHERE first_name = 'Jessica'
			 AND last_name = 'Butcher'
			 AND employee_id = 40); 

Oracle attempts to flatten some subqueries into joins when possible, to allow the Query Optimizer to select the optimal ________ order rather than be forced to process the query inside-out.

(c) join

Which of the following clause is mandatorily used in a sub-query?

(d) select

Which of the following is a method for writing a sub-query in a main query?

(d) By writing a SELECT statement embedded in the clause of another SELECT statement

Below is an example of

SELECT a.EmployeeID
FROM HumanResources.Employee a
WHERE a.ContactID IN
(
SELECT b.ContactID
FROM Person.Contact b
WHERE b.Title = 'Mrs.'
)
GO

(b) Noncorrelated Subquery

You need to find out the names of all employees who belong to the same department as the employee 'Jessica Butcher' who is in department 100 and has an employee ID 40. Which of the following queries will be correct?

(d)
SELECT first_name, last_name FROM employees WHERE dept_id = (SELECT dept_id FROM employees WHERE first_name = 'Jessica' AND last_name = 'Butcher' AND dept_id = 100 AND emp_id = 40);

Consider the query given below.How many records will be returned as a result of the above query?
(Assuming the no employee with job id XX exists in the company)

SELECT first_name, last_name
FROM employees
WHERE salary = (SELECT salary
FROM employees
WHERE job_id = 'XX');

(c) 0

What will be the query to updates SALARY by 0.25 times in CUSTOMERS table for all the customers whose AGE is greater than or equal to 27.

A) UPDATE CUSTOMERS SET SALARY = SALARY * 0.25
WHERE AGE NOT IN (SELECT AGE FROM CUSTOMERS
WHERE AGE >= 27 );

B) UPDATE CUSTOMERS SET SALARY = SALARY * 0.25
WHERE AGE IN (SELECT AGE FROM CUSTOMERS
WHERE AGE >= 27 );

C) UPDATE CUSTOMERS
WHERE AGE IN (SELECT AGE FROM CUSTOMERS
WHERE AGE >= 27 ) SET SALARY = SALARY * 0.25;

(b) B

Consider the following query :
SELECT employee_id, first_name, last_name, job_id
FROM employees
WHERE job_id = (SELECT job_id FROM employees);
You need to find all the employees whose job ID is the same as that of an employee with ID as 210.

Which of the following WHERE clauses would you add / modify to achieve this result?

(a) WHERE job_id = (SELECT job_id FROM employees WHERE employee_id = 210);

Consider the following query :

SELECT employee_id, first_name, last_name, job_id

FROM employees

WHERE job_id = (SELECT job_id FROM employees WHERE employee_id < 210);

While executing, the above query will throw an error.

Choose the query that will not throw any error.

(b)
SELECT employee_id, first_name, last_name, job_id
FROM employees
WHERE job_id IN (SELECT job_id FROM employees WHERE employee_id < 210);

What will be the outcome of the query that follows?

(b) SELECT first_name, last_name, min(salary)
FROM employees
GROUP BY department_id
HAVING MIN(salary) >
(SELECT min(salary)
FROM employees
WHERE department_id = 100);

(a) It executes successfully and gives the names and minimum salary greater than department 100 of all employees

The following query throws an error.
Choose the correct reason for the error as given in the options.
SELECT first_name, last_name
FROM employees
WHERE commission_pct = (SELECT min(commission_pct )
FROM employees
GROUP BY department_id);
A. The GROUP BY clause is not required in the sub-query
B. A function cannot be used in a sub-query SELECT statement
C. The single row sub-query gives multiple records

(c) C

Consider the query given below
SELECT first_name, last_name, salary, commission_pct
FROM employees
WHERE salary < ANY (SELECT salary
FROM employees
WHERE department_id = 100)
What will be the outcome of the query given above if the < ANY operator is replaced with = ANY operator, if we assume that the department 100 has more employees?

(a) It will treat each value of the salary returned from the sub-query as it does with IN operator

A subquery must be placed in the outer query's HAVING clause if:

(b) The value returned by the inner query is to be compared to grouped data in the outer query.