Showing posts with label SQL. Show all posts
Showing posts with label SQL. Show all posts

Saturday, 14 April 2018

Join operation in relational algebra and SQL solved exercises

Join operation in relational algebra and SQL solved exercises

Question:

Consider two relations R and S with instances as follows;

Relation R
Relation S
a b c
------
1 2 3
4 5 6
7 8 9
c d e
------
3 4 5
7 8 9
9 0 1
5 5 5

How many rows would the following operations yield for the given data?
(a) cartesian product of R and S.
(b) natural join of R and S
(c) left outer join of R and S
(d) right outer join of R and S
(e) full outer join of R and S

Solution:

Operation
No. of rows
How?
Result
Cartesian product
12
No. of records in R multiply No. of records in S
3 * 4 = 12
a b c c d e
--------------
1 2 3 3 4 5
1 2 3 7 8 9
1 2 3 9 0 1
1 2 3 5 5 5
4 5 6 3 4 5
4 5 6 7 8 9
4 5 6 9 0 1
4 5 6 5 5 5
7 8 9 3 4 5
7 8 9 7 8 9
7 8 9 9 0 1
7 8 9 5 5 5
Natural join
2
Compares the value of the common attribute between relations R and S. In our example, attribute C is the common attribute.
If C’s value is common for pair of tuples from both relations, then that pair will form a new tuple in the result.
Between R and S we have only 2 tuples with common C values.
a b c d e
-------------
1 2 3 4 5
7 8 9 0 1

Left outer join
3
It is the natural join that includes all the records from left side relation (in our case R) but not all records from right side relation (in our case S).
If there is no tuple in right side relation that are related to some records of left side relation, then the right side values will be NULL.
Number of records = Number of records in right relation R
a b c d e
--------------
1 2 3 4 5
4 5 6 n n
7 8 9 0 1

Right outer join
4
Same as above with inclusion of all records from right side relation (S) with NULL values of missing left side relation (R)
Number of records = Number of records in left relation S
a b c d e
-------------
1 2 3 4 5
n n 7 8 9
7 8 9 0 1
n n 5 5 5

Full outer join
5
Same as above. Now both sides the missing records will be replaced with NULL.
2 with common C values in R and S + 1 record in R with S values as null + 2 records in S with R values as null = 5 records
a b c d e
-------------
1 2 3 4 5
4 5 6 n n
n n 7 8 9
7 8 9 0 1
n n 5 5 5


**************
Go to Natural join in DBMS page (SQL)
Go to Equi-join in DBMS page (SQL)







result of join operation in sql
result of natural join in relational algebra
outer joins in sql and relational algebra
left and right outer joins examples
join solved exercises in dbms
how join operation is performed in sql
how join operation is performed in relational algebra
join operation exercises

Monday, 19 February 2018

What is correlated subquery in sql oracle

Correlated sub-query


Correlated sub-query is a sub-query that executes once for each outer query value (or record).
Let us assume a query with outer and inner queries. The inner query is co-related sub-query if the WHERE clause of the inner query is evaluated for each record of the outer query.
Let us suppose that the outer query have 100 records, then the inner sub-query has to evaluate for 100 times.

Example:

SELECT car_model, showroom, total_cars_sold FROM car_sales c WHERE total_cars_sold < (SELECT max(total_cars_sold) FROM car_sales WHERE car_model = c.car_model);

This query finds the car_model, showroom name, and total_cars_sold of all showrooms other than the one sold the maximum cars for each car model. The inner query is evaluated to ‘car_model = c.car_model’. So, the inner query has to be evaluated for each record of the outer query result. Hence, the inner query is correlated sub-query.

Car_model
Showroom
Total_cars
I20
ABC
200
I10
ABC
100
I20
XYZ
205
I10
XYZ
29

The query given above will produce the following result for the table instance given above;
Car_model
---------------
Showroom
----------------
Total_cars
---------------
I20
ABC
200
I10
ABC
100
I20
XYZ
205
I10
XYZ
29


Example for non-correlated sub-query:

SELECT Name, Weight FROM Student WHERE Weight > (SELECT Weight FROM Student WHERE Name = ‘Mathews’);
In this query, the sub-query evaluates only once and finds the weight of student ‘Mathews’. It does not find the weight of ‘Mathews’ for every outer query record.

On the other hand, the correlated sub-query explained above evaluates once for each outer query record.

Some notes:
Correlated sub-queries usually correspond to normal join queries, i.e. join queries that have no sub-queries, and can usually be expressed as such.
It is sometimes recommended that they are translated into such join queries, because most SQL DBMSs will execute them faster.
Certain correlated sub-queries can correspond to normal set queries rather than join queries, due to the comparator used with the correlated sub-query.
Note that whether a sub-query is correlated or not has solely to do with the tables referenced in the sub-query, and nothing to do with the kind of comparator used with .it

************






What is correlated sub-query
Correlated sub-query examples
Compare correlated subquery with non-correlated subquery
Drawback of correlated subquery
Define correlated sub-query

Important properties of two phase locking protocol and its variants

Important properties of two phase locking protocol and its variants Properties of     2PL Serializability  2PL ensures co...