ADVANCE SQL QUERIES

Advance SQL

What is Logical  operators

à Logical operators (such as AND and OR) allow you to relate numbers of conditions in various ways. Reduce to either true (1) or false (0).

Logical operators are mainly used to control program flow. Usually, you will find them as part of an if, a while, or some other control statement.

The Logical operators are:

op1 && op2

-- Performs a logical AND of the two operands.

op1 || op2

-- Performs a logical OR of the two operands.

!op1

-- Performs a logical NOT of the operand.

The concept of logical operators is simple. They allow a program to make a decision based on multiple conditions. Each operand is considered a condition that can be evaluated to a true or false value. Then the value of the conditions is used to determine the overall value of the op1 operator op2 or !op1 grouping. The following examples demonstrate different ways that logical conditions can be used.

Arithmetic Operators

Here are the most common arithmetic operators *, / and % will be performed before + or - in any expression. Brackets can be used to force a different order of evaluation to this. Where division is performed between two integers, the result will be an integer, with remainder discarded. Modulo reduction is only meaningful between integers. If a program is ever required to divide a number by zero, this will cause an error, usually causing the program to crash.

Here are some arithmetic expressions used within assignment statements.

velocity = distance / time;

force = mass * acceleration;

count = count + 1;

Comparison operators
 

Comparison operators, as their name implies, allow you to compare two values.

Comparison Operators

Example	Name	Result
$a == $b	Equal	TRUE if $a is equal to $b.
$a === $b	Identical	TRUE if $a is equal to $b, and they are of the same type. (PHP 4 only)
$a != $b	Not equal	TRUE if $a is not equal to $b.
$a <> $b	Not equal	TRUE if $a is not equal to $b.
$a !== $b	Not identical	TRUE if $a is not equal to $b, or they are not of the same type. (PHP 4 only)
$a < $b	Less than	TRUE if $a is strictly less than $b.
$a > $b	Greater than	TRUE if $a is strictly greater than $b.
$a <= $b	Less than or equal to	TRUE if $a is less than or equal to $b.
$a >= $b	Greater than or equal to	TRUE if $a is greater than or equal to $b.

Another conditional operator is the "?:" (or ternary) operator, which operates as in C and many other languages.

(expr1) ? (expr2) : (expr3);

This expression evaluates to expr2 if expr1 evaluates to TRUE, and expr3 if expr1 evaluates to FALSE.

What is Database View

By definition, a database view or view is a virtual or logical table which is composed of result set of a SELECT query.

VIEW ADVANTAGES

1. Simplify complex query.

2. Limited access data to the specific users.

3. Provide extra security.

4. Computed column.

5. Backward compatibility.