RDBMS_Day5 - RDBMS- Day5 Correlated Sub Queries Exists and...

Info iconThis preview shows pages 1–11. Sign up to view the full content.

View Full Document Right Arrow Icon
RDBMS- Day5 • Correlated Sub Queries • Exists and Not Exists •V iew s • Data Control Language • Embedded SQL In today’s session we would be discussing about the concept of views, the concept of an index and
Background image of page 1

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full DocumentRight Arrow Icon
ER/CORP/CRS/DB07/003 Version No: 2.0 2 Copyright © 2004, Infosys Technologies Ltd Correlated Sub Queries You can refer to the table in the FROM clause of the outer query in the inner query using Correlated sub-queries. The inner query is executed separately for each row of the outer query. (i.e. In Co-Related Sub-queries, SQL performs a sub-query over and over again – once for each row of the main query. )
Background image of page 2
ER/CORP/CRS/DB07/003 Version No: 2.0 3 Copyright © 2004, Infosys Technologies Ltd To list all Customers who have a fixed deposit of amount less than the sum of all their loans. Select Cust_Id, Cust_Last_Name, cust_Mid_Name, cust_First_Name From Customer_fixed_Deposit Where amount_in_dollars < (Select sum(amount_in_dollars) From Customer_Loan Where Customer_Loan.Cust_Id = Customer_Fixed_Deposit.Cust_ID); Correlated Sub Queries
Background image of page 3

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full DocumentRight Arrow Icon
ER/CORP/CRS/DB07/003 Version No: 2.0 4 Copyright © 2004, Infosys Technologies Ltd Correlated Sub Queries (Contd…)
Background image of page 4
ER/CORP/CRS/DB07/003 Version No: 2.0 5 Copyright © 2004, Infosys Technologies Ltd List customer IDs of all customers who have both a Fixed Deposit and a loan at any of Bank Branches SELECT Cust_ID FROM Customer_Details WHERE Cust_ID IN ( SELECT Cust_ID FROM Customer_Loan WHERE Customer_Loan.Cust_ID = Customer_Details.Cust_ID) AND Cust_ID IN ( SELECT Cust_ID FROM Customer_Fixed_Deposit WHERE Customer_Fixed_Deposit.Cust_ID = Customer_Details.Cust_ID); Correlated Sub Queries
Background image of page 5

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full DocumentRight Arrow Icon
ER/CORP/CRS/DB07/003 Version No: 2.0 6 Copyright © 2004, Infosys Technologies Ltd SELECT DISTINCT S# FROM Shipments X WHERE P# = ‘P1’ AND QTY > ( SELECT AVG (QTY) FROM Shipments Y WHERE P# = ‘P1’ AND X.J# = Y.J#) Get S# for suppliers supplying some project with P1 in a quantity greater than the average qty of P1 supplied to that project Correlated Sub Queries . ..
Background image of page 6
ER/CORP/CRS/DB07/003 Version No: 2.0 7 Copyright © 2004, Infosys Technologies Ltd Get P# for all parts supplied by more than one supplier SELECT P# FROM Shipment X WHERE P# IN ( SELECT P# FROM Shipment Y WHERE Y.S# <> X.S#) Correlated Sub Queries
Background image of page 7

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full DocumentRight Arrow Icon
EXISTS Vs NOT EXISTS
Background image of page 8
ER/CORP/CRS/DB07/003 Version No: 2.0 9 Copyright © 2004, Infosys Technologies Ltd List all Customers who have at least one Fixed Deposit more than $3000.00. SELECT Cust_ID, Cust_Last_Name, Cust_Mid_Name, Cust_First_Name FROM Customer_Details S WHERE EXISTS (SELECT * FROM Customer_Fixed_Deposit O WHERE O.Amount_in_Dollars > 3000.00 AND O.Cust_ID = S.Cust_ID); Retrieval using EXISTS Exists check for the existence of a situation/condition.
Background image of page 9

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full DocumentRight Arrow Icon
ER/CORP/CRS/DB07/003 Version No: 2.0 10 Copyright © 2004, Infosys Technologies Ltd List all Customers who have both a Fixed Deposit and a Loan at the Bank
Background image of page 10
Image of page 11
This is the end of the preview. Sign up to access the rest of the document.

Page1 / 47

RDBMS_Day5 - RDBMS- Day5 Correlated Sub Queries Exists and...

This preview shows document pages 1 - 11. Sign up to view the full document.

View Full Document Right Arrow Icon
Ask a homework question - tutors are online