SLIDES_RDBMS_5 - Education and Research We enable you to leverage knowledge anytime anywhere RDBMS \u2013 Part 5 o f In ER\/CORP\/CRS\/DB92 Ver No 1.0 s y

SLIDES_RDBMS_5 - Education and Research We enable you...

This preview shows page 1 out of 42 pages.

Unformatted text preview: Education and Research We enable you to leverage knowledge anytime, anywhere! RDBMS – Part 5 o f In ER/CORP/CRS/DB92 Ver. No.: 1.0 s y s Confidential Copyright © 2008, Infosys Technologies Ltd. General Guideline © (2008) Infosys Technologies Ltd. This document contains valuable confidential and proprietary information of Infosys. Such confidential and proprietary information includes, amongst others, proprietary intellectual property which can be legally protected and commercialized. Such information is furnished herein for training purposes only. Except with the express prior written permission of Infosys, this document and the information contained herein may not be published, disclosed, or used for any other purpose. o f In Copyright © 2008, Infosys Technologies Ltd. 2 s y s Confidential 2 Confidential Information   This Document is confidential to Infosys Technologies Limited. This document contains information and data that Infosys considers confidential and proprietary (“Confidential Information”). Confidential Information includes, but is not limited to, the following: s y s  Corporate and Infrastructure information about Infosys  Infosys’ project management and quality processes  Project experiences provided included as illustrative case studies     Any disclosure of Confidential Information to, or use of it by a third party, will be damaging to Infosys. Ownership of all Infosys Confidential Information, no matter in what media it resides, remains with Infosys. Confidential information in this document shall not be disclosed, duplicated or used – in whole or in part – for any purpose other than reading without specific written permission of an authorized representative of Infosys. This document also contains third party confidential and proprietary information. Such third party information has been included by Infosys after receiving due written permissions and authorizations from the party/ies. Such third party confidential and proprietary information shall not be disclosed, duplicated or used – in whole or in part – for any purpose other than reading without specific written permission of an authorized representative of Infosys. o f In Copyright © 2008, Infosys Technologies Ltd. 3 Confidential 3 Recap        Aggregate Function Group By Relational Algebra Cartesian Product Inner Join Self Join Outer Join o f In Copyright © 2008, Infosys Technologies Ltd. 4 s y s Confidential 4 Session Plan     Sub Query Independent Sub Query Correlated Query Exists and Not Exists o f In Copyright © 2008, Infosys Technologies Ltd. 5 s y s Confidential 5 Education and Research We enable you to leverage knowledge anytime, anywhere! SUB-QUERIES o f In Copyright © 2008, Infosys Technologies Ltd. 6 s y s Confidential Sub-queries  A sub-query is a query within a query.  A sub-query answers the queries that have multiple parts s y s  The sub-query answers one part of the question o f In  The sub query is enclosed in parentheses . Copyright © 2008, Infosys Technologies Ltd. 7 Confidential The query which is enclosed is called Inner query The query which encloses another query is called Outer query 7 Independent SUB QUERIES  Inner query is independent of outer query.  Inner query is executed first and the results are stored. s y s  Outer query then runs on the stored results.  Example: List the name of Item whose unit price is greater than the unit price of item id BAK101 o f In SELECT ItemName FROM Item If the Inner Query does not have any reference of outer Query tables then it is independent Sub query WHERE UnitPrice > (SELECT UnitPrice FROM Item WHERE ItemId=‘BAK101’ ); Copyright © 2008, Infosys Technologies Ltd. 8 Confidential Sub-queries: A sub-query is a query within a query. A sub-query answers the queries that have multiple parts; the sub-query answers one part of the question, and the parent query answers the other part. When you nest many sub-queries, the innermost query is evaluated first. SubQueries can be used in SELECT , FROM, WHERE and HAVING clauses. For e.g : Select all sales reps who have a higher quota than sales rep 101. We need to analyze this query and understand how to break it into sub problems 1. First we need to find out what is the quota of sales rep 101 2. Based on this info, we need to select sales reps who have a higher quota than this value 3. So, the inner query will find the quota of sales rep 101 and the outer query will extract sales reps exceeding this quota value. The solution would look like: SELECT Rep FROM SalesReps WHERE Quota > SELECT Quota FROM SalesReps WHERE Empl_Num = 101; 8 Retrieval using SUB QUERIES List the details of the items whose unit price is maximum. Item ItemId ItemName s y s UnitPrice o f n Class SELECT ItemId, ItemName, UnitPrice I FROM Item WHERE UnitPrice = Get the Maximum Unit Price available in the item table (SELECT SELECT MAX(UnitPrice) FROM Item); MAX Copyright © 2008, Infosys Technologies Ltd. 9 Confidential 9 Retrieval using SUB QUERIES List the second highest Unit price from the item table. Item ItemId ItemName s y s UnitPrice o f In Class SELECT MAX MAX(UnitPrice) Discard the highest Unit Price FROM Item WHERE UnitPrice != Get the highest Unit Price (SELECT SELECT MAX(UnitPrice) FROM Item); ; MAX Copyright © 2008, Infosys Technologies Ltd. 10 Confidential 10 Retrieval using SUB QUERIES List the details of the item whose unit price is more than the average Unit price of items. Item ItemId ItemName s y s UnitPrice o f In Class SELECT ItemId,ItemName,UnitPrice FROM Item WHERE UnitPrice Get the average Unit Price > (SELECT AVG( ) FROM Item); ); AVG(UnitPrice) Copyright © 2008, Infosys Technologies Ltd. 11 Confidential 11 Retrieval using SUB QUERIES List the id of the customer and sum of total purchase amount for those customers whose sum of total purchase amount is more than the average of the sum of total purchase amount of other Customers. CustomerPurchase CustomerId ItemId s y s QtyPurchased o f In SELECT CustomerId,Sum(NetPrice) FROM CustomerPurchase BillId NetPrice GROUP BY CustomerId HAVING SUM SUM(NetPrice) Get the average of Sum of Net price of all the purchases > (SELECT AVG(SUM FROM CustomerPurchase AVG SUM(NetPrice)) SUM GROUP BY CustomerId); ); Copyright © 2008, Infosys Technologies Ltd. 12 Confidential 12 Dry running the inner query of previous example CustomerPurchase CustomerId C1 C2 C1 C2 C3 ItemId STN001 GRO001 ELC001 STN002 STN002 QtyPurchased 5 1 1 2 2 BillId 1001 1002 1001 1002 1003 NetPrice 150 10 5000 400 400 s y s After grouping by Customer Id and finding sum of net Price for each group CustomerId C1 C2 C3 o f In Sum(NetPrice) 5150 410 400 Copyright © 2008, Infosys Technologies Ltd. Avg(sum(NetPrice)) 1986.666667 After finding average of sum of net price for each group 13 Confidential 13 Retrieval using SUB QUERIES List the details of the customer(s) who has paid a bill of maximum amount. Customer CustomerId CustomerName DateOfReg CustomerPurchase CustomerId ItemId QtyPurchased UserId s y s BillId SELECT CustomerId, CustomerName FROM Customer o f In WHERE CustomerId IN Password NetPrice Get the customer id(s) who have paid the maximum bill. Assuming one bill will be paid by only one Customer (SELECT CustomerId FROM CustomerPurchase GROUP BY BillId, CustomerId HAVING SUM(NetPrice) SUM = (SELECT SELECT MAX(SUM MAX SUM(NetPrice)) SUM FROM CustomerPurchase GROUP BY BillId)); Copyright © 2008, Infosys Technologies Ltd. 14 Get the maximum of Confidential Sum of Net price of all the bill ids You can reduce the three level sub query by joining Customer and CustomerPurchase of first and second query. (left as an exercise) 14 Dry running the innermost query of previous example CustomerPurchase CustomerId C1 C2 C1 C2 C3 ItemId STN001 GRO001 ELC001 STN002 STN002 QtyPurchased 5 1 1 2 2 BillId 1001 1002 1001 1002 1003 NetPrice 150 10 5000 400 400 s y s After grouping by BillId and finding sum of net Price for each group BillId 1001 1002 1003 o f In Sum(NetPrice) 5150 410 400 Copyright © 2008, Infosys Technologies Ltd. Max(sum(NetPrice)) 5150 After finding maximum of sum of net price for each group 15 Confidential 15 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. s y s  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. ) o f In Copyright © 2008, Infosys Technologies Ltd. 16 Confidential 16 Correlated SUB QUERIES List the details of the item which is having maximum unit price in each class . Item ItemId ItemName UnitPrice Class s y s SELECT Io.ItemId,Io.ItemName,Io.UnitPrice FROM Item Io Note the reference of outer table in inner query o f In WHERE UnitPrice = Since it is correlated query for each item in the item table the inner query will execute once and find the maximum unit price for that class. (SELECT SELECT Max(UnitPrice) FROM Item Ii WHERE Ii.Class = Io.Class ); Copyright © 2008, Infosys Technologies Ltd. 17 Confidential Note: The column names in the parent queries are available for reference in sub-queries. The column names from the tables in the sub-query cannot be used in the parent queries. The scope is only the current query level and its sub-queries. 17 Dry running the previous query Io ItemId STN001 BAK003 ELC001 ItemName Pen Bread Mobile UnitPrice 30 20 5000 Ii ItemId STN001 BAK003 ELC001 I ItemName UnitPrice Pen 30 o f n Bread 20 Mobile 5000 Inner query is executing for the first row of outer Query s y s Class A A C Maximum of Class A only Max(UnitPrice) 30 Class A A C Copyright © 2008, Infosys Technologies Ltd. 18 Item details of STN001 is selected Confidential Note: The column names in the parent queries are available for reference in sub-queries. The column names from the tables in the sub-query cannot be used in the parent queries. The scope is only the current query level and its sub-queries. 18 Dry running the previous query Io ItemId STN001 BAK003 ELC001 ItemName Pen Bread Mobile UnitPrice 30 20 5000 Ii ItemId STN001 BAK003 ELC001 I ItemName UnitPrice Pen 30 o f n Bread 20 Mobile 5000 Inner query is executing for the second row of outer Query s y s Class A A C Maximum of Class A only Max(UnitPrice) 30 Class A A C Copyright © 2008, Infosys Technologies Ltd. 19 Item details of BAK003 is not selected Confidential Note: The column names in the parent queries are available for reference in sub-queries. The column names from the tables in the sub-query cannot be used in the parent queries. The scope is only the current query level and its sub-queries. 19 Dry running the previous query Io ItemId STN001 BAK003 ELC001 ItemName Pen Bread Mobile UnitPrice 30 20 5000 Ii ItemId STN001 BAK003 ELC001 I ItemName UnitPrice Pen 30 o f n Bread 20 Mobile 5000 Class A A C Inner query is executing for the third row of outer Query s y s Class A A C Maximum of Class C only Max(UnitPrice) 5000 Copyright © 2008, Infosys Technologies Ltd. 20 Item details of ELC001 is selected Confidential Note: The column names in the parent queries are available for reference in sub-queries. The column names from the tables in the sub-query cannot be used in the parent queries. The scope is only the current query level and its sub-queries. 20 Correlated SUB QUERIES List the customer details of the customers, whose total NetPrice is more than 1000. Customer CustomerId CustomerName DateOfReg s y s CustomerPurchase CustomerId ItemId QtyPurchased o f In UserId BillId Password NetPrice SELECT CustomerId,CustomerName FROM Customer C WHERE 1000<(SELECT (SELECT Sum(netprice) FROM Note the reference of outer table in inner query customerpurchase CP WHERE CP.Custid = C.CustId) ) Copyright © 2008, Infosys Technologies Ltd. 21 Since it is correlated query for each customer in the customer table the inner query will execute once and sum of all net amount for that customer. Confidential Constant can be used in the LHS of where clause 21 Dry run the previous query Customer CustomerId C1 C2 C3 C4 C5 CustomerName John Jack Bob Allan Simon DateOfReg 1-Mar-09 10-Mar-09 12-Mar-09 13-Mar-09 o f In CustomerPurchase CustomerId C1 C2 C1 C2 C3 ItemId QtyPurchased STN001 5 GRO001 1 ELC001 1 STN002 2 STN002 2 Copyright © 2008, Infosys Technologies Ltd. 22 UserId Password John1001 [email protected] Jack1002 [email protected] Bob1003 [email protected] Allan1004 [email protected] Symon1005 [email protected] s y s BillNo 1001 1002 1001 1002 1003 NetPrice 150 10 5000 400 400 Confidential 22 Education and Research We enable you to leverage knowledge anytime, anywhere! EXISTS VS NOT-EXISTS o f In Copyright © 2008, Infosys Technologies Ltd. 23 s y s Confidential EXISTS The EXISTS Keyword is used check whether a sub query produces any row(s) of results s y s If the query following the EXISTS returns at least one row, the EXISTS returns TRUE o f In If the query following the EXISTS returns no rows, the EXISTS returns FALSE Copyright © 2008, Infosys Technologies Ltd. 24 Confidential EXISTS: The EXISTS operator is always followed by a sub-query in parentheses. EXISTS evaluates to TRUE if the sub-query returns at least one row. 24 Retrieval using EXISTS List the details of Customers who have purchased an item. Customer CustomerId CustomerName DateOfReg UserId s y s CustomerPurchase CustomerId ItemId QtyPurchased BillId Password NetPrice SELECT C.CustomerId, C.CustomerName o f In FROM Customer C WHERE EXISTS For each customer in the customer table the inner query executes to check whether such customer record is present in the CustomerPurchase table (SELECT SELECT * FROM CustomerPurchase CP WHERE C.CustomerId=CP.CustomerId); Copyright © 2008, Infosys Technologies Ltd. 25 Confidential 25 Dry running the previous query Customer CustomerId C1 C2 C3 C4 C5 CustomerNAme John Jack Bob Allan Simon DateOfReg 1-Mar-09 10-Mar-09 12-Mar-09 13-Mar-09 CustomerPurchase o f In Custome rId ItemId C1 STN001 C2 C1 C2 C3 GRO001 ELC001 STN002 STN002 QtyPurchased 5 BillNo 1001 1 1 2 2 1002 1001 1002 1003 Copyright © 2008, Infosys Technologies Ltd. 26 UserId Password John1001 [email protected] Jack1002 [email protected] Bob1003 [email protected] Allan1004 [email protected] Symon1005 [email protected] s y s For first row of outer Query inner query is executing NetPrice 150 10 5000 400 400 For first row of outer Query Inner query satisfies two rows hence EXISTS will return TRUE Confidential 26 Dry running the previous query Customer CustomerId C1 C2 C3 C4 C5 CustomerNAme John Jack Bob Allan Simon DateOfReg 1-Mar-09 10-Mar-09 12-Mar-09 13-Mar-09 CustomerPurchase o f In Custome rId ItemId C1 STN001 C2 C1 C2 C3 GRO001 ELC001 STN002 STN002 QtyPurchased 5 BillNo 1001 1 1 2 2 1002 1001 1002 1003 Copyright © 2008, Infosys Technologies Ltd. 27 UserId Password John1001 [email protected] Jack1002 [email protected] Bob1003 [email protected] Allan1004 [email protected] Symon1005 [email protected] s y s For second row of outer Query inner query is executing NetPrice 150 10 5000 400 400 For second row of outer Query Inner query satisfies two rows hence EXISTS will return TRUE Confidential 27 Dry running the previous query Customer CustomerId C1 C2 C3 C4 C5 CustomerNAme John Jack Bob Allan Simon DateOfReg 1-Mar-09 10-Mar-09 12-Mar-09 13-Mar-09 CustomerPurchase o f In Custome rId ItemId C1 STN001 C2 C1 C2 C3 GRO001 ELC001 STN002 STN002 QtyPurchased 5 BillNo 1001 1 1 2 2 1002 1001 1002 1003 Copyright © 2008, Infosys Technologies Ltd. 28 UserId Password John1001 [email protected] Jack1002 [email protected] Bob1003 [email protected] Allan1004 [email protected] Symon1005 [email protected] s y s For third row of outer Query inner query is executing NetPrice 150 10 5000 400 400 Confidential For third row of outer Query Inner query satisfies one row hence EXISTS will return TRUE 28 Dry running the previous query Customer CustomerId C1 C2 C3 C4 C5 CustomerNAme John Jack Bob Allan Simon DateOfReg 1-Mar-09 10-Mar-09 12-Mar-09 13-Mar-09 CustomerPurchase o f In Custome rId ItemId C1 STN001 C2 C1 C2 C3 GRO001 ELC001 STN002 STN002 QtyPurchased 5 BillNo 1001 1 1 2 2 1002 1001 1002 1003 Copyright © 2008, Infosys Technologies Ltd. 29 UserId Password John1001 [email protected] Jack1002 [email protected] Bob1003 [email protected] Allan1004 [email protected] Symon1005 [email protected] s y s For Forth row of outer Query inner query is executing NetPrice 150 10 5000 400 400 For forth row of outer Query Inner query satisfies no row hence EXISTS will return FALSE Confidential 29 Dry running the previous query Customer CustomerId C1 C2 C3 C4 C5 CustomerNAme John Jack Bob Allan Simon DateOfReg 1-Mar-09 10-Mar-09 12-Mar-09 13-Mar-09 CustomerPurchase o f In Custome rId ItemId C1 STN001 C2 C1 C2 C3 GRO001 ELC001 STN002 STN002 QtyPurchased 5 BillNo 1001 1 1 2 2 1002 1001 1002 1003 Copyright © 2008, Infosys Technologies Ltd. 30 UserId Password John1001 [email protected] Jack1002 [email protected] Bob1003 [email protected] Allan1004 [email protected] row Symon1005 [email protected] Foroffifth outer s y s Query inner query is executing NetPrice 150 10 5000 400 400 For fifth row of outer Query Inner query satisfies no row hence EXISTS will return FALSE Confidential 30 Retrieval using EXISTS List the details of Item which has been ordered to the supplier. Item ItemId ItemOrder ItemId ItemName UnitPrice s y s SupplierId QtyOrdered OrderDate DeliveryStatus SELECT I.ItemId, I.ItemName o f In FROM Item I WHERE Class EXISTS DeliveryDate For each Item in the Item table the inner query executes to check whether such item record is present in the ItemOrder table (SELECT SELECT * FROM ItemOrder IO WHERE I.ItemId=IO.ItemId); Copyright © 2008, Infosys Technologies Ltd. 31 Confidential 31 Dry run the previous query Item ItemId ItemName UnitPrice STN001 Pen 30 BAK003 Bread 20 ELC001 Mobile 5000 Class A A C ItemOrder o f In ItemId SupplierId QtyOrdered s y s OrderDate STN001 SUP001 50 1-Mar-09 EL001 SUP004 10 STN001 EL001 SUP002 SUP005 40 15 Copyright © 2008, Infosys Technologies Ltd. DeliveryStatus DeliveryDate Delivered 5-Mar-09 10-Mar-09 Delivered 12-Mar-09 15-Mar-09 20-Mar-09 Not Delivered Not Delivered 32 Confidential 32 Retrieval using EXISTS List the supplier details who have supplied items more than one. Supplier SupplierId SupplierName SupplierContactNo ItemOrder ItemId SupplierId QtyOrdered OrderDate DeliveryStatus DeliveryDate s y s SELECT S.SupplierId,S.SupplierName FROM Supplier S o f In WHERE EXISTS (SELECT SELECT * FROM ItemOrder IO For each supplier in the supplier table the inner query executes to check whether that supplier has supplied more than one items. WHERE S.SupplierId=IO.SupplierId GROUP BY SupplierId HAVING COUNT COUNT(*)>1 ); Copyright © 2008, Infosys Technologies Ltd. 33 Confidential 33 Dry run the previous query Supplier SupplierId SUP001 SUP002 SUP003 SUP004 SUP005 ItemOrder SupplierName Allan Bob Hari Tom Marie o f n ItemId SupplierId QtyOrdered STN001 BAK003 EL001 STN001 ELC001 SupplierContactNo 9729399396 9602839274 9102020202 9289208283 9819393938 s y s OrderDate DeliveryStatus DeliveryDate SUP001 50 1-Mar-09 Delivered 5-Mar-09 SUP003 20 2-Mar-09 Delivered 2-Mar-09 SUP004 10 10-Mar-09 Delivered 12-Mar-09 I SUP001 40 15-Mar-09 Not Delivered SUP005 15 20-Mar-09 Not Delivered Copyright © 2008, Infosys Technologies Ltd. 34 Confidential 34 Retrieval using EXISTS List the customer details whose total purchase amount is highest compared to total purchase amount of other customers. SELECT C.CustomerId, C.CustomerName FROM Customer C WHERE EXISTS (SELECT * s y s FROM CustomerPurchase CP WHERE C.CustomerId=CP.customerId o f In GROUP BY CP. .CustomerId HAVING SUM SUM(NetPrice) = (SELECT MAX SUM(NetPrice)) SUM MAX(SUM FROM CustomerPurchase Copyright © 2008, Infosys Technologies Ltd. 35 GROUP BY CustomerId)); Confidential 35 Dry run the previous query Customer CustomerId C1 C2 C3 C4 C5 CustomerNAme John Jack Bob Allan Simon DateOfReg 1-Mar-09 10-Mar-09 12-Mar-09 13-Mar-09 o f In CustomerPurchase CustomerId C1 C2 C1 C2 C3 ItemId QtyPurchased STN001 5 GRO001 1 ELC001 1 STN002 2 STN002 2 Copyright © 2008, Infosys Technologies Ltd. 36 UserId Password John1001 [email protected] Jack1002 [email protected] Bo...
View Full Document

  • Left Quote Icon

    Student Picture

  • Left Quote Icon

    Student Picture

  • Left Quote Icon

    Student Picture