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

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;

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

VARCHAR Data type in Oracle

VARCHAR and VARCHAR2 Datatypes in Oracle / Overview of VARCHAR Datatype / How to use VARCHAR type in Oracle to declare and use Attributes? / Oracle VARCHAR datatype How to?

VARCHAR2 and VARCHAR Datatypes

VARCHAR2 datatype is used to store Variable-length character strings.

Syntax

VARCHAR2[(size [BYTE | CHAR])]

where VARCHAR2 is the datatype,

          size is the maximum length of the string to be stored

          BYTE/CHAR would mean the same thing. That is, size in number of Bytes or Characters.

Overview

Datatype	VARCHAR2
Default Size	1 Byte/Character
Minimum Size	1 Byte/Character
Maximum Size	4000 Bytes/Characters
If the value to be stored is shorter than the declared size?	The memory needed to store the value will only be used. [not the size bytes]
If the value to be stored is longer than the declared size?	ERROR
Memory required to store 1 Character	Depends on the character set used. It may vary from 1 byte to 4 bytes. For example, if the character to be stored is English character then 1 byte, if it is Chinese character then 3 bytes
Permitted Values	Alphanumeric characters with various native character data set support

What is variable length? It uses the size number of Bytes to store any value of size length. If the value to be stored is less than size, then it occupies only the amount of memory required to store that value. For example, let us consider the declaration of variable RegNo;

Student_Name VARCHAR2(25);

For this declaration, if the value for any Student_Name is of length 25 characters then 25 bytes will be used. If any of the Student_Name value is only 10 characters then only 10 bytes will be used. The values that are to be stored and the actual storage along with the total amount of memory consumed are given in the table below for reference;

Declaration	Actual Data		Stored Data
	Value to be stored	Bytes needed	Data Stored	Bytes used	Bytes saved
VARCHAR2(5)	‘R101’	4	‘R101‘	4	1
VARCHAR2(5)	‘R1001’	5	‘R1001’	5	0
VARCHAR2(5)	‘R1’	2	‘R1‘	2	3
VARCHAR2(10)	‘ID1456’	6	‘ID1456‘	6	4
VARCHAR2(10)	‘ID’	2	‘ID‘	2	8

Note: Preceding blank spaces and intermediate blank spaces in an input will be considered as data. Only trailing blank spaces gets removed (eliminated) automatically.

Character based datatypes in Oracle - Contents