RDBMS_Day4 - RDBMS- Day 4 Grouped results Relational...

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

View Full Document Right Arrow Icon
RDBMS- Day 4 • Grouped results • Relational algebra •Jo ins • Sub queries
Background image of page 1

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

View Full DocumentRight Arrow Icon
Grouped results
Background image of page 2
ER/CORP/CRS/DB07/003 Version No: 2.0 3 Copyright © 2004, Infosys Technologies Ltd SQL - Using GROUP BY Related rows can be grouped together by GROUP BY clause by specifying a column as a grouping column. GROUP BY is associated with an aggregate function To retrieve the total loan-amount of all loans taken by each Customer. SELECT Cust_ID, SUM (Amount_in_Dollars) FROM Customer_Loan GROUP BY Cust_ID; In the output table all the rows with an identical value in the grouping column will be grouped together.
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 SQL – Group By
Background image of page 4
ER/CORP/CRS/DB07/003 Version No: 2.0 5 Copyright © 2004, Infosys Technologies Ltd SQL – Group BY To retrieve Number of Employees in each Department SELECT Department, COUNT (Employee_ID) FROM Employee_Manager GROUP BY Department
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 Example: Invalid SQL statement SELECT Department, Manager_ID, COUNT(Employee_ID) FROM Employee_Manager GROUP BY Manager_ID; Valid SQL Statement SELECT Department, Manager_ID, COUNT(Employee_ID) FROM Employee_Manager GROUP BY Manager_ID, Department; Retrieval using GROUP BY
Background image of page 6
ER/CORP/CRS/DB07/003 Version No: 2.0 7 Copyright © 2004, Infosys Technologies Ltd SQL – Group By
Background image of page 7

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 8 Copyright © 2004, Infosys Technologies Ltd List all customers who are having loans greater than 4000 Select Cust_ID,SUM(Amount_in_Dollars) From Customer_Loan Group By Cust_ID Having SUM(Amount_in_Dollars) > 4000.00; Retrieval using HAVING Used to specify condition on group
Background image of page 8
ER/CORP/CRS/DB07/003 Version No: 2.0 9 Copyright © 2004, Infosys Technologies Ltd Can you identify any error…? Select Cust_ID,SUM(Amount_in_Dollars) From Customer_Loan Group By Cust_ID Having LOAN_NO > 4000.00; Ans: The Having condition has to be based on some column that appears in the select list
Background image of page 9

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

View Full DocumentRight Arrow Icon
Relational algebra operations
Background image of page 10
SET operations
Background image of page 11

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 12 Copyright © 2004, Infosys Technologies Ltd List all the customer who has either Fixed Deposit or Loan or Both Retrieval using UNION The UNION operation • Combines the rows from two sets of query results. • By default, the UNION operation eliminates duplicate rows as part of its processing. SELECT Cust_ID FROM Customer_Fixed_Deposit UNION SELECT Cust_ID FROM Customer_Loan; Customer_Fixed _Deposit Customer_ Loan The results of two independent SELECT statements can be worked with using the SET operation – UNION. By default, UNION returns only distinct values. Union is like an “OR” operation. If the tuple occurs in relation 1 or relation 2, it is selected. Set theoretic notation indicates union as indicated in the slide
Background image of page 12
ER/CORP/CRS/DB07/003 Version No: 2.0 13 Copyright © 2004, Infosys Technologies Ltd Union (Contd…)
Background image of page 13

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

View Full DocumentRight Arrow Icon
Image of page 14
This is the end of the preview. Sign up to access the rest of the document.

Page1 / 43

RDBMS_Day4 - RDBMS- Day 4 Grouped results Relational...

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

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