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

SLIDES_RDBMS_4.pdf - Education and Research We enable you...

This preview shows page 1 out of 54 pages.

Unformatted text preview: Education and Research We enable you to leverage knowledge anytime, anywhere! RDBMS Part 4 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  Introduction to SQL  DDL Statements  Create Table  Alter Table  Drop Table  Truncate Table  Relational Operators  DML Statements  Insert into  Delete from  Select …  Update … o f In Copyright © 2008, Infosys Technologies Ltd. 4 s y s Confidential 4 Session Plan        Aggregate function Group By Relational Algebra Cartesian Product Inner Join Self Join Outer Join 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! AGGREGATE FUNCTIONS o f In Copyright © 2008, Infosys Technologies Ltd. 6 s y s SQL - Aggregate functions  Used when information you want to extract from a table has to do with the data in the entire table taken as a set.  Aggregate functions are used in place of column names in the SELECT statement  The aggregate functions in sql are : s y s SUM( ) , AVG( ) , MAX( ) , MIN( ), COUNT( ) o f In Copyright © 2008, Infosys Technologies Ltd. 7 Confidential Group functions do not process NULL values and do not return a NULL value, even when NULL are the only values evaluated. For example, a COUNT or SUM of NULL values will result in 0. 7 Aggregate function - MIN  Returns the smallest value that occurs in the specified column  Column need not be numeric type s y s List the minimum unit price from item table. SELECT MIN (UNITPRICE) FROM ITEM; o f In Copyright © 2008, Infosys Technologies Ltd. 8 Confidential MIN: This function has the syntax MIN([{DISTINCT | ALL }] <x>), where x is an expression. This function returns the lowest value in the expression x. •If the expression x is a date data type, it returns a DATE. For dates, the minimum is the earliest date. •If the expression x is a numeric data type, it returns a NUMBER. For numbers, the minimum is the smallest number. •If the expression is a character data type, it returns a VARCHAR2. for character strings, the minimum is the one that sorts lowest based on the database character set. 8 Aggregate function - MAX  Returns the largest value that occurs in the specified column  Column need not be numeric type  Example: s y s List the maximum unit price in the item table. o f In SELECT MAX (UNITPRICE) FROM ITEM; Copyright © 2008, Infosys Technologies Ltd. 9 Confidential MAX: This function has the syntax MAX([{DISTINCT | ALL }] <x>), where x is an expression. This function returns the highest value in the expression x. •If the expression x is a date data type, it returns a DATE. For dates, the maximum is the latest date. •If the expression x is a numeric data type, it returns a NUMBER. For numbers, the maximum is the largest number. •If the expression is a character data type, it returns a VARCHAR2. for character strings, the maximum is the one that sorts lhighest based on the database character set. 9 Aggregate function - AVG  Returns the average of all the values in the specified column  Column must be numeric data type List the average unit price of Class A items in the item table. s y s SELECT AVG(UNITPRICE) FROM ITEM; o f In Copyright © 2008, Infosys Technologies Ltd. 10 Confidential AVG: This function has the syntax AVG ( [DISTINCT | ALL] <n>), where n is a numeric expression. The AVG function returns the mean of the expression n. If neither DISTINCT nor ALL is specified in the function call, the default is ALL. 10 Aggregate function - SUM     Adds up the values in the specified column Column must be numeric data type Value of the sum must be within the range of that data type Example: s y s List the minimum and Sum of all the unit price of items in the item table. o f In SELECT SUM (UNITPRICE) FROM ITEM; Copyright © 2008, Infosys Technologies Ltd. 11 Confidential 11 Aggregate function - COUNT Returns the number of rows in the table List total number of items in the item table. s y s SELECT COUNT (*) FROM ITEM; List the total number of customer who have their date of registration information in the customer table. o f In SELECT COUNT (DATEOFREG ) FROM CUSTOMER; Count(*) Count(ColumnName) = = Copyright © 2008, Infosys Technologies Ltd. No of rows, regardless of NULLs No. of rows that do not have NULL Value 12 Confidential COUNT: The COUNT function returns the number of rows in the query. The asterisk (*) is a special quantity – it counts all rows in the result set, regardless of NULLs. 12 Aggregate function - COUNT List total number of unique dates on which bill has been generated. s y s SELECT COUNT (DISTINCT BillDate) BillDate) FROM Bill; o f In Copyright © 2008, Infosys Technologies Ltd. 13 Confidential 13 Education and Research We enable you to leverage knowledge anytime, anywhere! GROUPING DATA WITH GROUP BY o f In Copyright © 2008, Infosys Technologies Ltd. 14 s y s SQL - Using GROUP BY  Related rows can be grouped together by GROUP BY clause by specifying a column as a grouping column. s y s  GROUP BY is associated with an aggregate function To retrieve the average unit price of all class of items available in the item table. o f In SELECT Class, Class FROM Column name can come along with aggregate function only if it appears in Group by clause AVG(UnitPrice) AVG Item GROUP BY Copyright © 2008, Infosys Technologies Ltd. Class; 15 Confidential In the output table all the rows with an identical value in the grouping column will be grouped together. Note: If no GROUP BY clause is specified (only group functions and constants appear in the SELECT clause), the default grouping becomes the entire result set. 15 SQL – Group By SELECT Class, Class FROM AVG(UnitPrice) AVG Item GROUP BY Class; Item ItemId STN001 BAK003 GRO001 ELC001 ELC004 STN002 ItemName Pen Bread Poteto Mobile iPod Diary UnitPrice 30 20 10 5000 600 200 o f In Class AVG(UnitPrice) A B C Copyright © 2008, Infosys Technologies Ltd. s y s Class A A A C B B 16 20 400 5000 Confidential Grouping is done based on Class column. There are three different values in the class column so three groups will be formed and for every group one row will be displayed in the output. 16 Retrieval using GROUP BY What is the output? SELECT Class, ItemName, COUNT(ItemId) COUNT FROM Item GROUP BY Class; ERROR at line 1: o f In s y s ORA-00979: not a GROUP BY expression SELECT Class, ItemName, COUNT(ItemId) COUNT FROM Item GROUP BY Class, ItemName; Copyright © 2008, Infosys Technologies Ltd. 17 Confidential First query is wrong because you can not display ItemName if it has not been used for grouping. The vice versa is not TRUE.(There can be a column which is used in group by clause but not used in select clause) 17 SQL – Group By Get list the no of items present class and item name wise SELECT Class, ItemName, COUNT COUNT(ItemId) FROM Item GROUP BY Class, ItemName; Item ItemId STN001 BAK003 GRO001 ELC001 ELC004 ELC002 ItemName Pen Bread Potato Mobile iPod iPod UnitPrice 30 20 10 5000 600 200 o f In Class A A A B C ItemName Pen Bread Potato iPod Mobile Copyright © 2008, Infosys Technologies Ltd. 18 s y s Class A A A C B B Count(ItemId) 1 1 1 2 1 Confidential The grouping is done based on two column Class and ItemName. The query will first find the no of different values in Class column, in this case it is three. So three groups will be formed because of Class column. Now for every such group there will be subgrouping because of ItemName. In our example for group of Class A items there are three different ItemName so three subgroups will be formed. For group of Class B items there is only one ItemName so no subgrouping will happen For group of Class C items there is only one ItemName so no subgroup will be formed 18 Retrieval using HAVING List all the classes of item whose average unit price is greater than or equal to 400 SELECT Class,AVG AVG( AVG UnitPrice) FROM Item s y s GROUP BY Class HAVING AVG(UnitPrice) >= 400; AVG o f In Copyright © 2008, Infosys Technologies Ltd. 19 Confidential Limiting Grouped data with HAVING: Group functions cannot be used in the WHERE clause. A SQL statement can have both a WHERE clause and a HAVING clause. WHERE filters data before grouping; HAVING filters data after grouping. 19 Retrieval using HAVING SELECT Class, AVG AVG( UnitPrice) FROM Item GROUP BY Class HAVING AVG(UnitPrice) >= 400; AVG Item ItemId STN001 BAK003 GRO001 ELC001 ELC004 STN002 ItemName Pen Bread Poteto Mobile iPod Diary UnitPrice 30 20 10 5000 600 200 o f In After Grouping Class A B C AVG(UnitPrice) 20 400 5000 Copyright © 2008, Infosys Technologies Ltd. s y s After Having 20 Class A A A C B B Class B C AVG(UnitPrice) 400 5000 Confidential Limiting Grouped data with HAVING: Group functions cannot be used in the WHERE clause. A SQL statement can have both a WHERE clause and a HAVING clause. WHERE filters data before grouping; HAVING filters data after grouping. 20 Retrieval using HAVING What is the output ? SELECT Class, SUM(UnitPrice) SUM FROM Item GROUP BY Class HAVING SupplierId IN (‘S1’,’S2’); o f In s y s Ans: The Having condition has to be based on some column that appears in the group by list Copyright © 2008, Infosys Technologies Ltd. 21 Confidential Query is wrong because you cannot use SupplierId in Having Clause if it has not been used for grouping. The vice versa is not TRUE.(There can be a column which is used in group by clause but not used in Having clause) 21 Education and Research We enable you to leverage knowledge anytime, anywhere! RELATIONAL ALGEBRA OPERATIONS o f In Copyright © 2008, Infosys Technologies Ltd. 22 s y s Retrieval using UNION List all the ids of the customer who have either purchased an item or their date of registration is not available. SELECT CustomerId FROM Customer where DateOfReg is NULL UNION o f In SELECT CustomerId FROM CustomerPurchase; s y s Customer’s do not have date of registration Customers having purchased The UNION operation • Combines the rows from the results obtained from the participating queries. • By default, it eliminates duplicate rows from the final result. Copyright © 2008, Infosys Technologies Ltd. 23 Confidential 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. 23 Union (Contd…) Customer CustomerId CustomerNAme DateOfReg UserId Password C1 John 1-Mar-09 John1001 [email protected] C2 Jack 10-Mar-09 Jack1002 [email protected] C3 Bob 12-Mar-09 Bob1003 [email protected] C4 Allan 13-Mar-09 Allan1004 [email protected] C5 Simon Symon1005 [email protected] CustomerId C5 C1 STN001 5 1001 C2 GRO001 1 1002 s y s C1 ELC001 1 1001 5000 C2 STN002 2 1002 400 CustomerPurchase CustomerId QtyPurchase d BillNo NetPrice 150 o f In CustomerId ItemId Copyright © 2008, Infosys Technologies Ltd. 24 C1 C2 C1 C2 UNION 10 CustomerId C1 C2 C5 Confidential 24 Union All Union All returns all rows including duplicates selected by either query. SELECT CustomerId UNION ALL SELECT CustomerId s y s FROM Customer WHERE DateOfReg IS NULL FROM CustomerPurchase; ; o f In Copyright © 2008, Infosys Technologies Ltd. 25 Confidential Union all: Returns all rows including duplicates selected by either query. 25 Union All CustomerId CustomerNAme DateOfReg UserId Password C1 John 1-Mar-09 John1001 [email protected] C2 Jack 10-Mar-09 Jack1002 [email protected] C3 Bob 12-Mar-09 Bob1003 [email protected] C4 Allan 13-Mar-09 Allan1004 [email protected] C5 Simon Symon1005 [email protected] CustomerId ItemId BillNo NetPrice C1 STN001 5 1001 150 C2 GRO001 1 1002 10 C1 ELC001 1 1001 5000 C2 STN002 2 1002 400 Copyright © 2008, Infosys Technologies Ltd. 26 C5 s y s QtyPurchase d o f In CustomerId CustomerId C1 C2 C1 C2 UNION ALL CustomerId C1 C2 C1 C2 C5 Confidential Union all: Returns all rows including duplicates selected by either query. 26 Union - Restrictions  The SELECT statements must contain the same number of columns  Data type s y s  The data type of the corresponding columns in both the table must be the same.  Data width and column name can differ o f In  The component query can not be sorted using the ORDER BY clause.  Combined query results can be sorted Copyright © 2008, Infosys Technologies Ltd. 27 Confidential 27 Retrieval using INTERSECT List the id of customers who do not have date of registration but has purchased an item. SELECT CustomerId FROM Customer where DateOfReg IS NULL INTERSECT o f In SELECT CustomerId FROM CustomerPurchase; Copyright © 2008, Infosys Technologies Ltd. 28 s y s Customers who do not have dateOf registration Customerswho have purchased item Confidential An intersection is an AND operation. It retrieves those tuples which are present in both relation 28 Minus Get the Id of all customers who have not purchased any items. SELECT CustomerId FROM Customer MINUS o f In SELECT CustomerId FROM CustomerPurchase; Copyright © 2008, Infosys Technologies Ltd. 29 s y s Customers who have purchased an item Confidential This is the difference operation. It retrieves unique tuples which are present in relation 1 but not in relation 2. 29 Other Relational Algebra operations  Restriction  Projection  Join o f In Copyright © 2008, Infosys Technologies Ltd. 30 s y s Confidential 30 Restriction  Restricts the rows that can be chosen from a relation using a WHERE clause s y s  Takes a horizontal subset of values from the original relation Example: o f In SELECT * FROM Items WHERE UnitPrice > 100; Copyright © 2008, Infosys Technologies Ltd. 31 Confidential Where clause: This will retrieve only those rows of the table which satisfy the condition in the where clause. Any logical conditions of the where clause use the comparison operators. Rows are returned or operated upon where the data satisfies the logical condition (s) of the where clause. Column names or expressions can be used in the where clause but not column alias names. 31 Projection  Projection is projecting a set of attributes of a relation so that rows of values corresponding to those columns will figure in the output s y s  This takes a vertical subset of the relation Example: o f In SELECT ItemId, ItemName FROM Item; Copyright © 2008, Infosys Technologies Ltd. 32 Confidential 32 JOINS     Cartesian Product Inner join Equi join Outer join  Left-outer join  Right-outer join  Self join o f In Copyright © 2008, Infosys Technologies Ltd. 33 s y s Confidential In relational databases, data is spread over multiple tables. Sometimes we may want data from two or more tables. A join is an operation which combines results from two or more tables. 33 Cartesian Product Or Cross Join  Returns All rows from first table, Each row from the first table is combined with all rows from the second table Example SELECT * FROM Table1,Table2; o f In Copyright © 2008, Infosys Technologies Ltd. 34 s y s Confidential Cartesian Joins: A Cartesian join occurs when data is selected from two or more tables and there is no common relation specified in the WHERE clause. If you do not specify a join condition for the tables listed in the FROM clause, Oracle joins each row from the first table to every row in the second table. If the first table has 3 rows and the second table has 4 rows, the result will have 12 rows. 34 Inner Joins  Common type of join  We define inner join between as the Cartesian product which satisfies the join condition in the WHERE clause o f In Copyright © 2008, Infosys Technologies Ltd. 35 s y s Confidential Inner Joins: The most common operator used to relate two tables is the equality operator (=). If you relate two tables using an equality operator, it is an equality join, also known as equijoin. This type of join combines rows from two tables that have equivalent values for the specified columns. A simple join is also known as an inner join, because it returns only the rows that satisfy the join condition. 35 Retrieval from Multiple tables-Equi join Get all combinations of emp and cust information such that the emp and cust are co-located. SELECT Table1.Emp_ID, Table1.City, Table2.Cust_ID, Table2.City FROM Table1, Table2 s y s WHERE Table1.City = Table2.City; o f In Copyright © 2008, Infosys Technologies Ltd. 36 Confidential Here the where clause is based on the equality condition “=“. Hence it is called equi join 36 Retrieval from Multiple tables- Equi join Display the Employee number, Employee name and department name of the employees who are working for some department. SELECT EmpNo, EName,DName FROM Emp E , Dept D s y s WHERE E.DeptNo = D.DeptNo; o f In Copyright © 2008, Infosys Technologies Ltd. 37 Confidential 37 Retrieval from Multiple tables- Equi join Display the details of the customers who have purchased some items Customer CustomerId CustomerName DateOfReg CustomerPurchase CustomerId ItemId UserId s y s QtyPurchased o f In Password BillId NetPrice SELECT a.CustomerId,a.CustomerName FROM Customer a, CustomerPurchase b WHERE a.CustomerId = b.CustomerId; Copyright © 2008, Infosys Technologies Ltd. 38 Confidential 38 Retrieval from Multiple tables- Equi join Display the details of suppliers who have been ordered to supply item, but the delivery status is ‘Not Delivered’. Supplier SupplierId ItemOrder ItemId SupplierName o f In SupplierId QtyOrdered s y s SupplierContactNo OrderDate DeliveryStatus DeliveryDate SELECT s.SupplierId,s.SupplierName FROM Supplier s, ItemOrder io WHERE s.SupplierId = io.SupplierId AND io.DeliveryStatus =‘Not Delivered’; Copyright © 2008, Infosys Technologies Ltd. 39 Confidential 39 Retrieval from Multiple tables- Equi join Display the details of Supplier who has been ordered to supply more than one item Supplier SupplierId ItemOrder ItemId SupplierName o f In SupplierId QtyOrdered s y s SupplierContactNo OrderDate DeliveryStatus DeliveryDate SELECT s.SupplierId,s.SupplierName FROM Supplier s, ItemOrder io WHERE s.SupplierId = io...
View Full Document

  • Left Quote Icon

    Student Picture

  • Left Quote Icon

    Student Picture

  • Left Quote Icon

    Student Picture