. For each query shown in the screen clipping below, explain if it is a subquery

Question

. For each query shown in the screen clipping below, explain if it is a subquery or join and determine the type of subquery or join. Write a question that the return set of the query answers.

Screen Clipping: Queries:

ueries. Worksheet Query Builder Query1 selectfrom Orders where Order ID in 3(select Order Number from RETURNs where REASONProduct Damaged') Query 6select o.order ID, Subquery.Total Amount from Orders o, 7(select distinet order ID,to_char (sum (sales), *999,990.99" Total Amount fron Order items group by order_ID) Subquery vhere Subquery. order-ID ?.ORDER ID; . Query 12 select 0.Order_ID from ORDERS O where not exists 13| ( select 1 from returns R where R.Order Nurber. ?.Order-ID); Suery 16 select C.Customer Name,c.Customer_State,0.0rder_ID, to_char (I.Profit, 999,990.99) 17| : from customer C Join orders O on c.customer-ID . ?.customer-ID 10 19 Join Ozderitens I on I.order_id - 0.0rdez_id where I.Pzofit (select MAX(I.Profit) from Ordez Items I join Ozdezs 0 on I.orderid 0.OrderId) 1 22--Query 23 select c..,o.shap-date from CUSTOMERS c Left Outer Join orders O 24On c.customer Id o.Customez Id vhere extract (AR from to date (o.ship date,DDrm)> 2016: C6 -Query ?8B select c.customer Name, c.customer State 29| -order-ID, to char(I·Profit, '4999,990.99") 30 31 from alstomersc Join Orders on c.Customer ID .Customer ID Join Order Items I on I.order Id o.Order Id where I.Protit(select MAX(I.Profit) from orderItems I join Orders 0 on I.order Id-0.Order Id) 34 35uery select distinet c.customer Name,o.ship Mode, o.ship date from Customers c 37 38 39 40 join Orders O on c.customer-ID·o.customer-ID vhere 0.Ship mode- Second Class" and extract (AR from to date(o.ship date, 'M DD_YYYY')> 2016:

Explanation / Answer

1. It is using subquery because in the latter part it is making a new temporary table. Multiple row subquery

2. Subquery -  Multiple column subquery

3. Subquery - Single Row Subquery

4. Subquery + Join - Single Row Subquery + Left Outer Join

5. Subquery + Join - Single Row Subquery + Inner Join

6. Join - Left Inner Join

Explanations: All the queries that include join keywords are joins and that contains another query inside another query that is a subquery.

Related Questions

Navigate

• Browse (All)
• Browse #
• Subjects

---

---

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