16. Subqueries

Question 1

DBMS Server has two types of storage:
1. Cache
2. Disk

The Disk storage has 3 partitions. List their name & purpose.

Answer 1

1. User Data (databases, tables)
2. System Catalog (metadata)
3. Temp Storage (temporary tables)

Question 2

The metadata in System Catalog is available for viewing through _ .

Answer 2

Information Schema

It has built-in views that provide information (metadata) about databases and tables.

Question 3

Subquery result is computed by DB engine and stored in Cache. It is accessible only inside the main query and is discarded after the execution of main query. Any external query _ access that result.

Answer 3

cannot

Question 4

Based on dependency, subqueries can be classified into two categories.
Name them.

Answer 4

1. Correlated Subquery
2. Non-correlated Subquery

Question 5

Based on result type, subqueries can be classified into three categories.
Name them.

Answer 5

1. Scalar Subquery (returns single aggregate value)
2. Row Subquery (returns rows of a single column)
3. Table Subquery (returns tabular result)

Question 6

– Find all orders with sales that are higher than the global average sales.

select
  OrderID,
  Sales,
  meanSales
from
(select
OrderID,
Sales,
avg(Sales) over() as meanSales
from MyOrders)t
where Sales > meanSales;

Here t is a _ in the from clause.

Answer 6

subquery

Question 7

Scalar subquery is used in select clause to display result from _ table.

Show ProductID, product name, price from Products table along with total number of orders.

Answer 7

different

select
  ProductID,
	Product,
	Price
	(select count(*) from Orders) as totalOrders
	from Product;

Here, totalOrders is a subquery in select clause

Question 8

Table subquery is used in join clause to bring the data in required shape _ applying join.

Find all customer names from Customers table who made an order (in Orders table).

Answer 8

before

select c.name
from Customers join
(select distinct customerId
from Orders)o
on c.customerId = o.customerId;

Here o is a subquery in join clause.

Question 9

Scalar subquery is used in where clause for _ filtering.

Answer 9

dynamic

Question 10

Row Subquery is used in IN clause inside the where clause for filtering across a _ of choices.

Show the details of orders made by customers in Germany.

Note: Orders table has orderID and customerID.
Countries table has cutomerID and country.

Answer 10

list

select orderId, customerId
from Orders
where customerId in
(select customerId from Countries where country = 'Germany');

Question 11

Show id, name and totalSales of orders made by each customer in Germany.
Note: MyOrders table has orderID and customerID.
customers table has id and country.

Answer 11

select o.CustomerID, c.first_name, o.totalSales
from (select CustomerID, sum(Sales) as totalSales from MyOrders group by CustomerID) o
join (select * from customers where country = 'Germany') c 
on c.id = o.customerID;

Question 12

Show id, name and totalSales of orders made by each customer in Germany.
but instead of join, use subquery in where clause.

Hint: where ... in clause takes row subquery (single column)

Answer 12

select
  CustomerID,
  sum(Sales) as totalSales
from MyOrders
where CustomerID in (
  select id
  from customers
  where country = 'Germany'
)
group by CustomerID;

Question 13

Show all customer details and find total orders made by each customer.

Answer 13

select
  c.*,
  o.totalOrders
from customers c
left join (
  select CustomerID,
  count(*) as totalOrders
  from MyOrders
  group by CustomerID
) o
on c.id = o.CustomerID

Question 14

When to prefer row subquery over join:

Answer 14

Ask yourself if you need value from another column of the right table.

single column -> go for row subquery
multi column -> go for join

Question 15

Find all orders with sales that are higher than the global average sales.

Answer 15

select
  OrderID,
  Sales,
  avgSales
from
(select
  OrderID,
  Sales,
  avg(Sales) over() as avgSales
from MyOrders)t
where sales > avgSales;

Question 16

Find all orders with sales that are higher than the global average sales,
using scalar subquery.

Hint: this time, try using subquery with comparison operator in where clause

Answer 16

select
  OrderID,
  Sales
from MyOrders
where sales > (select avg(Sales) from MyOrders);

Question 17

Find female employees whose salaries are greater than salaries of at least one (any) male employee.

Employees table: EmployeeId, FirstName, Salary, Gender

Answer 17

select EmployeeId,
FirstName,
Salary
where Gender = 'F'
and Salary > ANY (
select salary from Employees where Gender = 'M'
)

Question 18

The unique feature of “correlated subquery” is that
it gets executed for each _ returned by main query.

Answer 18

row

Think of it like streaming execution (map operation)

Question 19

Show all customer details and total orders of each customer.

Answer 19

select *,
  (select count(Sales) from MyOrders o where o.CustomerID = c.id) totalOrdersPerCustomer
from customers c;

correlation helps in making the subquery scalar!

Question 20

Show the details of orders made by customers in Germany.

Note: Orders table has orderID and customerID.
Countries table has cutomerID and country.

Answer 20

select orderId, customerId
from Orders o
where exists (
select 1
from Countries
where country = 'Germany' and customerId = o.customerId
)

exists is more efficient than in