1 / 25

Advanced SQL Queries and Joins

This lecture discusses advanced SQL queries and joins, including the use of GROUP BY and HAVING clauses, column aliases, and outer joins. Examples and syntax are provided for better understanding.

salinasj
Download Presentation

Advanced SQL Queries and Joins

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Database Design Lecture 4 Advanced SQL References: Text Chapters 8 and 9 Oracle SQL Manual Database Design lecture 4Ses Slide 1

  2. Advanced queries • General Syntax of SELECT command SELECT [DISTINCT | ALL] {* | [columnExpression [AS newName]] [,...] } FROM TableName [alias] [, ...] [WHERE condition] [GROUP BY columnList] [HAVING condition] [ORDER BY columnList] • Order of the clauses cannot be changed. • Only SELECT and FROM are mandatory Database Design lecture 4Ses Slide 2

  3. Use of GROUP BY • Use GROUP BY clause to get sub-totals. • SELECT and GROUP BY closely integrated: each item in SELECT list must be single-valued per group, and SELECT clause may only contain: • Column names in the group by clause • Aggregate functions • Constants • Expression involving combinations of the above • If WHERE is used with GROUP BY, WHERE is applied first, then groups are formed from rows satisfying condition. Database Design lecture 4Ses Slide 3

  4. Example 1 List the quantity of each product ordered during Jan 2003. SELECT prodNo, sum(quantity) FROM orders WHERE ordDate>='01-jan-2003' AND ordDate<'01-Feb-2003' GROUP BY prodNo; Database Design lecture 4Ses Slide 4

  5. Example 1-continued Database Design lecture 4Ses Slide 5

  6. Use of HAVING • HAVING clause is designed for use with GROUP BY to restrict groups that appear in final result table. • Similar to WHERE, but WHERE filters individual rows whereas HAVING filters groups. • Column names in HAVING clause must also appear in the GROUP BY list or be contained within an aggregate function. Database Design lecture 4Ses Slide 6

  7. Example 2 • List the product number and the quantity ordered for each product which has a total order of more than 2 in Jan 2003. SELECT prodNo, sum(quantity) FROM orders WHERE ordDate>='01-jan-2003' AND ordDate<'01-Feb-2003' GROUP BY prodNo HAVING sum(quantity)>2; Database Design lecture 4Ses Slide 7

  8. Question • Are the the following statements legal? If yes do they generate the same result? SELECT prodNo, sum(quantity) FROM orders WHERE ordDate>='01-jan-2003' HAVING prodno=100 GROUP BY prodNo; SELECT prodNo, sum(quantity) FROM orders WHERE ordDate>='01-jan-2003' and prodno=100 GROUP BY prodNo; SELECT prodNo, sum(quantity) FROM orders WHERE ordDate>='01-jan-2003' and prodno=100; Database Design lecture 4Ses Slide 8

  9. Example 3 List the product number and the quantity ordered for each product which was ordered by two or more distinct customers since 01/01/2003. SELECT prodNo, sum(quantity) FROM orders WHERE ordDate>='01-jan-2003' HAVING count(distinct custNo)>=2 GROUP BY prodNo; Database Design lecture 4Ses Slide 9

  10. Example 3 -continued Database Design lecture 4Ses Slide 10

  11. Use of AS – column alias • AS is used to give a name (alias) to a column in the result table • Alias rather than original column name will be displayed • In Oracle, AS is optional • In Oracle, if the alias has spaces or special characters, it has to be double quoted SELECT prodNo, sum(quantity) AS sum, count(distinct custNo) AS noOfCust FROM orders WHERE ordDate>='01-jan-2003' HAVING count(distinct custNo)>=2 GROUP BY prodNo; Database Design lecture 4Ses Slide 11

  12. Alias example SQL> column "room number" format a20 SQL> column "hotel number" format a20 SQL> select RoomNo "room number", hotelno "hotel number" from room; room number hotel number -------------------- -------------------- 001 H01 002 H01 003 H01 004 H01 005 H01 006 H01 001 H02 002 H02 003 H02 004 H02 10 rows selected. Database Design lecture 4Ses Slide 12

  13. Selecting from Multiple Tables (Joins) • Often two or more tables are needed at the same time to find all required data • These tables must be "joined" together • The formal JOIN operation will be explained later, basically • it computes a new table from those to be joined, • the new table contains data in the matching rows of the individual tables. Database Design lecture 4Ses Slide 13

  14. SQL Examples of Joins • List customers (by customer number, name and address) who have ordered the product 100. SELECT c.custNo, custName, custSt, custCity FROM customer c, orders o WHERE c.custNo=o.custNo AND prodNo=100; Database Design lecture 4Ses Slide 14

  15. Example • Find the total price of the products ordered by customer 1. SELECT sum(price*quantity) FROM orders, product WHERE orders.prodNo = product.prodNo AND custNo = 1; Note: relation name or alias can be used to qualify column names when there is ambiguity. Database Design lecture 4Ses Slide 15

  16. Outer Joins • The outer join of two tables is a new table which not only contains the matching rows, but also contains non-matching rows. • Left outer Join of tables r and s • keeps every tuple in the left relation r, if no matching tuple in s, pad values with nulls • Right outer join • keeps every tuple in the right relation s, if no matching tuple in r, pad values with nulls Database Design lecture 4Ses Slide 16

  17. Outer Joins in Oracle SQL • Put an (+) on the potentially deficient side, ie the side where nulls may be padded. Example: List all customers, and the products ordered if they have ordered some products. SELECT c.custNo, o.prodNo, quantity FROM customer c, orders o WHERE c.custNo = o.custNo(+); Note: • a table may be outer joined with only one other table. • Which table column to use is important, eg, in above example, do not use o.custNo in place of c.custNo in the SELECT list. Database Design lecture 4Ses Slide 17

  18. Nested Queries (1) • Query results are tables, which can also be queried. SELECT * FROM (SELECT prodNo, sum(quantity) as sum FROM orders GROUP BY prodNo) WHERE sum>10; Equivalent to SELECT prodNo, sum(quantity) as sum FROM orders GROUP BY prodNo HAVING sum(quantity)>10; • The inner query is referred to as a subquery Database Design lecture 4Ses Slide 18

  19. Nested Queries (2) • If the query result is a single value, it can be treated as a value, and be compared with other values. Example: Find products with price more than average SELECT prodNo, price FROM product WHERE price > (SELECT AVG(price) FROM product); Database Design lecture 4Ses Slide 19

  20. Use of IN • List the products ordered by customers living in Brisbane. SELECT prodNo FROM orders WHERE custNo IN (SELECT custNo FROM customer WHERE custCity ='Brisbane'); This query is equivalent to SELECT prodNo FROM orders o, customer c WHERE o.custNo =c.custNo AND custCity = 'Brisbane'; Database Design lecture 4Ses Slide 20

  21. EXISTS • Find all customers who have ordered some products. SELECT * FROM customer c WHERE exists (SELECT * FROM orders o WHERE o.custNo =c.custNo); • If the subquery is not empty, then the exists condition is true. Database Design lecture 4Ses Slide 21

  22. NOT EXISTS • Find all customers such that no order made by them has a quantity less than 2. SELECT * FROM customer c WHERE NOT EXISTS (SELECT * FROM orders o WHERE o.custNo = c.custNo AND quantity <2); Database Design lecture 4Ses Slide 22

  23. Correlated and Uncorrelated Subqueries • A correlated subquery is one in which the inner query is referenced by the outer query such that the inner query may be thought being executed repeatedly SELECT * FROM customer c WHERE exists (SELECT * FROM orders o WHERE o.custNo =c.custNo); • A uncorrelated subquery is one where the subquery can be executed independently of the outer query. Database Design lecture 4Ses Slide 23

  24. Subqueries versus Joins • Subqueries can often be used in place of joins. Eg • The query SELECT distinct c.custNo, custName, custSt, custCity FROM customer c, orders o WHERE c.custNo=o.custNo AND prodNo=100; may be replaced with SELECT custNo, custName, custSt, custCity FROM customer WHERE custNo IN (SELECT custNo FROM orders WHERE prodNo=100); or SELECT custNo, custName, custSt, custCity FROM customer c WHERE EXISTS (SELECT * FROM orders WHERE prodNo=100 and custNo = c.custNo) Database Design lecture 4Ses Slide 24

  25. UNION, MINUS and INTERSECT SELECT prodNo FROM product MINUS SELECT prodNo FROM orders; //difference from the two queries SELECT custNo FROM customer WHERE custCity='Brisbane' UNION SELECT custNo FROM orders WHERE prodNo=102; // union of the two queries SELECT custNo FROM customer WHERE custCity='Brisbane' INTERSECT SELECT custNo FROM orders WHERE prodNo=102; // intersect of the two queries Database Design lecture 4Ses Slide 25

More Related