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

Monday, February 19, 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

Sunday, February 18, 2018

Three valued logic

Three valued logic

It is a many-valued logic system. It is proposed to handle NULL values. For example, consider a condition ‘Age > 15’. This condition compares two values (one is stored in Age variable and the other is 15) and results in either TRUE or FALSE (Boolean logic). What if one of the operands is NULL? To handle such comparisons the three valued logic has been proposed. Three valued logic is sometimes called as Trivalent logic, Ternary logic or Trinary logic.

"SQL’s logic is an implementation of three valued logic".

Unlike Bivalent logic system (Boolean logic evaluates to TRUE or FALSE) there are three truth values such as TRUE, FALSE, and UNKNOWN in three valued logic system.

In three valued logic system, the logical operators are evaluated as follows;

  • NOT x – evaluates to 1 – x
  • x OR y – evaluates to max(x, y), ie., whichever is true then the result is true
  • x AND y – evaluates to min(x,y), ie., one of them is false then the result is false.
You can observe the results from the following table;
x
y
x AND y
x OR y
NOT x
True
True
True
True
False
True
False
False
True

True
Unknown
Unknown
True

False
True
False
True
True
False
False
False
False

False
Unknown
False
Unknown

Unknown
True
Unknown
True
Unknown
Unknown
False
False
Unknown

Unknown
Unknown
Unknown
Unknown


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



Three valued logic, the logic that evaluates to TRUE, FALSE, and UNKNOWN, How does SQL evaluates logic in its basic operations, SQL conditions and three valued logic, three valued logic, trivalent logic, ternary logic



Thursday, January 7, 2016

SQL Exercise 5

SQL Exercises for Beginners / Simple SQL Exercises with Answers / SQL Exercises for simple Table creation and SELECT queries / Solved SQL exercises

Consider a relation REPAYMENT with the following schema;

REPAYMENT(BORROWER_ID, NAME, ADDRESS, LOANAMOUNT, REQUESTDATE, REPAYMENT_DATE, REPAYMENT_AMOUNT)

Assume that this table records the repayment of loans by the borrowers. A borrower may have multiple entries if he/she has paid multiple installments.

Write SQL statements (queries) to achieve the following;

Question (a)

Find all the records with information on repayments from the borrower with id equal to 42, and where the lent amount exceeds 1000.

Answer (a)

SELECT *
FROM Repayment
WHERE borrower_id=42 AND loanamount>1000;

Question (b)

Find the total amount repaid for every address in the repayment table.

Answer (b)

SELECT address, SUM(repayment_amount)
FROM Repayment
GROUP BY address;

Question (c)

Delete all information on the completed loans. (Note: you can find the status of the loan by summing the total repaid amount. If the total repaid amount is equal to the loan amount, then you would say that the loan is ended.)

Answer (c)

DELETE FROM Repayment A
WHERE loanamount=
(SELECT SUM(repayment_amount)
FROM Repayment B
WHERE B.borrower_id=A.borrower_id AND B.requestdate=A.requestdate);

Question (d)

Find all the borrower names who has unique address. (ie., you should not count the borrowers who are from the same address)

Answer (d)

SELECT name
FROM Repayment A
WHERE 1=
(SELECT COUNT(DISTINCT name)
FROM Repayment B
WHERE A.address=B.address);

Question (e)

Find the total number of repayments made by every borrower.

Answer (e)

SELECT borrower_id, count(*)
FROM repayment
GROUP BY borrower_id;

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

Featured Content

Multiple choice questions in Natural Language Processing Home

MCQ in Natural Language Processing, Quiz questions with answers in NLP, Top interview questions in NLP with answers Multiple Choice Que...

All time most popular contents

data recovery