RDBMS_Day 4

1

RDBMS- Day 4

•Grouped results •Sub queries •Relational algebra

•Use of EXISTS and NOT EXISTS

2

3

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

• Example: For each part supplied get the part number and the total shipment quantity

SELECT

SELECT

PNO, SUM(QTY)

FROM

FROM

SP

GROUP BY

GROUP BY

PNO

In the output table all the rows with an identical value in the grouping column will be grouped together.

4

SELECT

SELECT

SNO, PNO, SUM(QTY)

FROM

FROM

SP

GROUP BY

GROUP BY

PNO

SELECT

SELECT

SNO, PNO, SUM(QTY)

FROM

FROM

SP

GROUP BY

GROUP BY

SNO, PNO

Retrieval using GROUP BY

Get SNO, PNO, total qty for each part

supplied

Why the first form is considered wrong is that, for each part , multiple suppliers would have supplied the part in different combinations. For e.g say for a part p1, s1 might have supplied twice, s2 once , s3 thrice etc. so we want the results to be grouped based on part number and within that by supplier number and take the count.

5

Get PNO for parts which have more than two shipments

SELECT

SELECT PNO,COUNT(*) FROMFROM SP

GROUP BY

GROUP BY PNO

HAVING

HAVING COUNT(*)>2

Retrieval using HAVING

• Used to specify condition on group

Get supplier numbers who have at least two shipments.

SELECT

SELECT SNO , COUNT(*) FROMFROM SP

GROUP BY

GROUP BY SNO

HAVING

6

Can you identify any error…?

SELECT

SELECT

SNO , COUNT(*)

FROM

FROM

SP

GROUP BY

GROUP BY

SNO

HAVING

HAVING

PNO=‘p2’;

Ans: The Having condition has to be based on some column that appears in the select list

7

8

Independent sub-queries

• Inner query is independent of outer query.

• Inner query is executed first and the results are stored. • Outer query then runs on the stored results.

These are queries where there are two parts to the query. We need to collect one type of information based on which other set of information has to be retrieved from the table.

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 nee dto find out what is the quota of seles 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 sakes reps exceeding this quota value. The solution would look like:

SELECT Rep FROM SalesReps WHERE Quota > SELECT Quota FROM SalesReps WHERE Empl_Num = 101;

9

Get supplier names for all suppliers who supply part P2

SELECT

SELECT

SNAME

FROM

FROM

S

WHERE

WHERE

SNO IN

(

SELECT SNO

SELECT

FROM

FROM SP

WHERE

WHERE PNO =‘P2’)

10

Get SNO for suppliers who are located in the same city as S1

SELECT

SELECT

SNO

FROM

FROM

S

WHERE

WHERE

CITY =

(SELECT

SELECT CITY

FROM

FROM S

WHERE

WHERE SNO=‘S1’)

11

Get SNO for suppliers who supply at least one part supplied by S2

SELECT

SELECT

SNO

FROM

FROM

SP

WHERE

WHERE

PNO IN

(

SELECT PNO

SELECT

FROM

FROM SP

WHERE

WHERE SNO=‘S2’)

12

SELECT

SELECT SNO

FROM

FROM

S

WHERE

WHERE

STATUS <

(

SELECT STATUS

SELECT

FROM

FROM S

WHERE

WHERE SNO=‘S1’)

Get SNO who have status less than the status of ‘S1’

Retrieval using SUB QUERIES

13

Get supplier numbers for suppliers with status less than the current maximum in the supplier table

SELECT

SELECT

SNO

FROM

FROM

S

WHERE

WHERE

STATUS <

(

SELECT MAX(STATUS)

SELECT MAX

FROM

FROM S)

14

Get SNO for suppliers who do not supply any part supplied by S2

SELECT

SELECT

SNO

FROM

FROM

SP

WHERE

WHERE

PNO

NOT IN

NOT IN

(SELECT PNO FROM SP

WHERE SNO=‘S2’))

15

For each part supplied, get the part number, maximum quantity, and minimum quantity supplied for that part

SELECT

SELECT

SP.P# ,

MAX (SP.QTY)

AS

AS

MXQ,

MIN (SP.QTY)

AS

AS

MNQ

FROM

FROM

SP

GROUP BY SP.P# ;

GROUP BY SP.P# ;

Write a query for the following ...

Let us take few more examples and see how to solve:

1. Who are the customers whose rep is 102 and have at least one order more than $1000? Select cust_name from customers

where cust_rep=102 and cust_num in

(select cust from orders where amount > 1000)

2. Which product has the maximum total order amount?

Select product from orders group by product having sum(amount) =(select max(sum(amount)) from orders group by product);

16

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.

17

Get PNO for all parts supplied by more than one supplier

SELECT

SELECT

PNO

FROM

FROM

SP

X

X

WHERE

WHERE

PNO

IN

(

SELECT PNO

SELECT

FROM

FROM SP

Y

Y

WHERE

WHERE

Y.SNO<>

Y

X.

X

SNO)

Correlated Sub Queries

In this query, clearly there is a case of comparison of each row with some set of rows of the table to determine whether the given rows qualifie to go into the result. So, the typical way to solve is to select each row and check it with some other rows selected based on a criteria iteratively. Typically, the same table will be involved in both the outer query and the inner query

To solve these type of correlated queries, we typically consider the same table as two copies each with a different name . This enables comparing the table with itself. To illustrate this, consider the above query. Let’s take a sample snapshot:

SNO PNO JNO Qty

S1 P1 J1 75

S2 P3 J1 40

S1 P1 J2 30

S4 P3 J1 55

Here, both p1 and p3 have been supplied twice. But p1 has been supplied by the same supplier and p3 by two different suppliers. So , when we consider the same table as two copies X and Y and compare, the processing would go something like this…

X Y

SNO PNO JNO Qty SNO

PNO JNO Qty

S1 P1 J1 75 S1 P1 J1 75 S2 P3 J1 40 S2 P3 J1 40 S1 P1 J2 30 S1 P1 J2 30 S4 P3 J1 55 S4 P3 J1 55

The inner query will be executed completely once for every row of the outer query. So, the result returned by the inner query would be

Iteration 1 :

Is p1 in (p3, p3) - not qualified -> not selected

Iteration 2 :

Is p3 in (p1, p1, p3) -> qualified -> selected

Iteration 3:

Is p1 in (p3,p3) -> not qualified -> not selected

Iteration 4:

Is p3 in (p1,p1,p3)-> qualified -> selected So the result will contain two rows displaying Pno

P3 P3

18

SELECT

SELECT

DISTINCT SNO

FROM

FROM

SPJ X

WHERE

WHERE

PNO=‘P1’

AND

AND

QTY> (SELECT AVG

SELECT AVG(QTY)

FROM

FROM SPJ Y

WHERE

WHERE PNO=‘P1’

AND

AND X.JNO=Y.JNO)

Get SNO for suppliers supplying some project with P1 in a quantity greater than the average qty of P1 supplied to that project

Correlated Sub Queries ...

Take a sample snapshot of the shipment tables as follows:

SNO PNO JNO Qty

S1 P1 J1 75

S2 P1 J1 40

S3 P2 J2 30

S4 P1 J1 55

Here, s1, s2 , s4 are supplying project j1 with part p1. The average quantity of p1 supplied to j1 is 56. 7. out of this, s1 supplies individually a quantity 75 of part p1 to the project j1.

So according to the query, s1 is the supplier supplying some project (here it is j1) with p1 in a quantity(75) which is greater than the average quantity of p1 supplied to that project(56.7). So the result should be s1.

19

Exercise

• List names of sales reps who have a higher target than their managers

Select

s.name

from

salesreps s where

s.quota > (select

m.quota

from

selesreps m

where

m.emplnum=s.manager);

20

Exercise

• Who are the customers whose rep is 102 and have at least one order more than $1000?

Select cust_name from customers

Where cust_rep=102 and cust_num in

(select cust from orders

where amount > 1000)

To solve this problem, we split the query as follows:

We have to find the list of customers having an order amount above 1000

Then we have to select customers whose rep is 102 and whose custno is in the list generated by the inner query.

Here there are two clear cut components which are independent so, this will be solved as an independent subquery.

21

22

23

Get a list of all the parts cities and supplier cities

SELECT

SELECT

CITY

FROM

FROM

P

UNION

UNION

SELECT

SELECT CITY

FROM

FROM S

Retrieval using UNION

Supplier _Parts

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

24

Get a list of all common parts and supplier cities

SELECT

SELECT

CITY

FROM

FROM

P

INTERSECT

INTERSECT

SELECT

SELECT CITY

FROM

FROM S

Retrieval using INTERSECT

An intersection is an AND operation. It retrieves those tuples which are present in both relation A and B

25

Minus

• Get the list of cities specific to suppliers which are not the cities of any parts

SELECT city FROM

supplier MINUS

SELECT city FROM

parts

26

Other RA operations

27

Restriction

• Restricts the rows that can be chosen from a relation using a where clause • Takes a horizontal subset of values from the original relation

• E.g select * from employee where salary>10000;

28

Projection

• Projection is projecting a set of attributes of a relation so that rows of values corresponding to those colums will figure in the output

• For e.g

• select empid, name, salary from employee; • This takes a vertical subset of the relation

29

30

JOIN

• Inner join

• Equi join

• Outer join

– Left-outer join

– Right-outer join

• Self join

In relational databses, data is spread over multiple tables. Sometimes we may want dta from two or more tables. A join is an operation which combines results from two or more tables.

31

Inner Joins

• Common type of join

• Combines records from two tables with matching values on a column.

In this type of join, rows from two different tables are selected based on a matching column. Which rows are selected is restricted by a where clause. For example,

Customers Orders

Custid Orderid

custname custid

cust_city amount

Here, custid of customer table and custid of order table correspond. Note:

pl note that there need not be any primary key foreign key relationship between these two columns which would be used in a join operation

32

Get all combinations of supplier and part information such that the supplier and part are co-located.

SELECT

SELECT

S.*, P.*

FROM

FROM

S, P

WHERE

WHERE

S.CITY=P.CITY

Retrieval from Multiple tables-Equi join

33

Get SNO,PNO combinations where the part’s city follows the supplier’s alphabetically

SELECT

SELECT

SNO, PNO

FROM

FROM

S, P

WHERE

WHERE

S.CITY<P.CITY

Retrieval from Multiple tables- Non Equi join

34

Get SNO and PNO for co-located suppliers and parts omitting suppliers with status<20

SELECT

SELECT

P.PNO, S.SNO

FROM

FROM

P, S

WHERE

WHERE

S.CITY = P.CITY

AND S.STATUS > = 20

Retrieval from Multiple tables

Retrieval from Multiple tables

35

Outer join

• Retrieve all rows that match the WHERE clause and those that have a NULL

The inner join takes into account only those non NULL rows from the tables involved. If you want the result to include even those rows having a NULL for a particular row in the selected column, then go for an outer join. The syntax for representing this is slightly different in each rdbms product. What follows in the next slide is the oracle style.

36

Left/Right-Outer join

• Left outer joins include all records from the first (left) of two tables, A=B +

Right outer joins include all records from the second (right) of two tables, A += B

37

Example of left-join

SELECT S.SNO, SP.QTY

FROM S, SP

WHERE S.SNO = SP.SNO (+);

All unmatched rows of S are also selected

List all SNO with QTY supplied or SNO which have not yet supplied any QTY

38

Example of right outer join

• Select the orders and coresponding customernames. Select all customer names, even if there are no orders placed by any.

select o.order_num, c.cust_name from orders o, customers c where o.cust (+) = c.cust_num;

The (+) symbol is next to the column which needs to be expanded to include null values also. In the example above, there may be some customers who have not made any orders, so if we select their names from the customers table(the second table based on int position in the query), the

corresponding order detail would be null. Eevne then such values have to be selected . That’s what is indicated. A typical output would look like:

ORDER_NUM CUST_NAME ---

---5 radha

first corp jcp inc.

39

Get all pairs of SNO who are co-located

SELECT

SELECT

FIRST.SNO, SECOND.SNO

FROM

FROM

S FIRST,

S SECOND

WHERE

WHERE

FIRST.CITY=SECOND.CITY

Self join-

Joining a table with itself

When you wish to join a table with itself based on some criteria, use the concept of synonyms. Treat the table as two different tables by giving synonyms

40

Cartesian Product (a) Cross Join

• A join without any conditions is called a Cartesian product

• It will be a collection of all possible combinations of rows from the tables involved

• Practically of little use

• Just included for conceptual completeness

• E.g select s.sno, sp.sno from supplier s , shipment sp;

Let us assume a snapshot of the supplier table and shipment table are as follows:

Supplier Shipment

SNO Name status SNO PNO qty

s1 Ram 20 s3 p1 56

s2 John 45 s4 p2 100

s3 Rafi 32 s3 p3 45

s4 Inder 34 s1 p2 34

The cartesian product would be like: SNO SNO s1 s3 s2 s3 s3 s3 s4 s3 s1 s4 s2 s4 s3 s4 s4 s4 s1 s3 s2 s3 s3 s3 s4 s3 s1 s1 s2 s1 s3 s1 s4 s1

41

42

Get PNO’s supplied in the same QTY by at least one different supplier

SELECT

SELECT PNO

FROM

FROM SP A

WHERE EXISTS

WHERE EXISTS

(SELECT

SELECT *

FROM

FROM SP B

WHERE

WHERE A.PNO=B.PNO

AND

AND A.QTY=B.QTY AND

AND

A.SNO<>B.SNO)

Retrieval using EXISTS

43

Get all part names from parts table which have been shipped

SELECT

SELECT

PNAME

FROM

FROM

P

WHERE EXISTS

WHERE EXISTS

(

SELECT *

SELECT

FROM

FROM SP

WHERE

WHERE SP.PNO=P.PNO)

Retrieval using EXISTS

Let us take another example:

Find the deptno of those departments having employees who can do some work done by employee in depertment d1.

The solution would look like: Select deptno

from department

where exists( select *

from employee x where x.dept= ‘d1’ and exists

(select * from employee y y.job=x.job and y.dept=department.deptno) ) and deptno <> ‘d1’;

44

Get the list of all prospective suppliers, i. e.,suppliers for whom no shipments exist yet.

SELECT

SELECT

SNAME

FROM

FROM

S

WHERE NOT EXISTS

WHERE NOT EXISTS

(

SELECT *

SELECT

FROM

FROM SP

WHERE

WHERE SP.SNO=S.SNO)

Retrieval using NOT EXISTS

A not exists checks for the opposite condition than an Exists. It checks for the non-existence of a condition.

Not exists can be used to arrive at a result which can’t otherwise be arrived at using exists.

The logic is as follows:

For all x there exists an y such that f(x,y) is true is the same as

There does not exist and x for which there does not exists a y so that f(x,y)is true

Taking a slight variant of the previous example:

Find the deptno of those departments having employees who can do all work done by employees in depertment d1.

Select deptno

from department

where not exists(

select * from employee x where x.dept= ‘d1’ and not exists(

select * from employee y

y.job=x.job and y.dept=department.deptno)

) and deptno <> ‘d1’

45

Exercise

• List cities that have received shipments from every customer.

SELECTA.CITY_NAME

FROMCITY ASA

WHERE NOT EXISTS(

SELECT *

FROMCUSTOMER B

WHERE NOT EXISTS(

SELECT *

FROMSHIPMENT C

WHEREA.CITY_NAME=C.DESTINATION

46

Summary

• The result of a query can be grouped based on a grouping column • While checking for conditions after grouping by a column , Having is used

instead of where

• Grouped queries help look at data category wise

• When the query consists of more than one component, it is implemented in the form of a nested query depending on the nature of the query.

• Sub queries help split a problem involving different levels of data

• Relational algebra operations like union, intersect, difference, restriction, projection and join help us get different combinations of data from more than one table

47

Thank You!