Describe the use of subquery factoring clauses Describe the

Describe the use of subquery factoring clauses.

Describe the use of a hierarchical query.

Distinguish between the base table values and the calculated values in SELECT statements.

Describe the use of a column alias.

Describe the use of the concatenation operator in string expressions.

Describe the order of precedence and the use of parentheses for arithmetic expressions.

Describe the use of scalar functions.

Describe the use of the Dual table, the DISTINCT keyword, and the ROWNUM pseudo column.

Describe the use of comparison operators, logical operators, and parentheses in WHERE clauses.

Describe the use of the IN, BETWEEN, and LIKE operators in WHERE clauses.

Describe the use of IS NULL in a WHERE clause.

Describe the use of column names, column aliases, calculated values, and column numbers in ORDER BY clauses

Solution

a)The main purpose of subquery factoring is to simplify complex SQL.
Subquery factoring simplifies complex SQL by materializing complex SQL in much the same way as global temporary tables.

b)
A hierarchy is built upon a parent-child relationship within the same table or view.
A hierarchical query helps us to understand the complex queries easily.

c)
Both are used in select statement to select the intended values.
you need to add more conditions until you get only the values you intend to be included in the calculation.

d)
aliasing can be used as an obfuscation technique to protect the real names of database fields. In SQL, you can alias tables and columns. A table alias is also called a correlation name. A programmer can use an alias temporarily assign another name to a table or column for the duration of a SELECT query.

e)
concatenation operator is used to concatenate two character strings is another character string.

f)
In arithmetic expressions:
the use of parentheses is very vital because it decides which set of expression will execute first.
Parentheses used to control the order in which the operators are applied. If you don\'t use parentheses, operations with higher precedence are done first.
When two operators share an operand, the operator with the higher precedence goes first.

g)
scalar functions return a single value, based on the input value.

Useful scalar functions:

LEN() - Returns the length of a text field
UCASE() - Converts a field to upper case
MID() - Extract characters from a text field
LCASE() - Converts a field to lower case
LEN() - Returns the length of a text field

h)
A dual table is a dummy table with one element in it. It is useful because Oracle doesn\'t allow statements like :SELECT 3+4
You can work around this restriction by writing :SELECT 3+4 FROM DUAL.

In a table, a column may contain many duplicate values; and sometimes you only want to list the different (distinct) values. The DISTINCT keyword can be used to return only distinct (different) values.

ROWNUM returns a number indicating the order in which a row was selected from a table. The first row selected has a ROWNUM of 1, the second row has a ROWNUM of 2, and so on. If a SELECT statement includes an ORDER BY clause, ROWNUMs are assigned to the retrieved rows before the sort is done.

i)
The IN operator allows you to specify multiple values in a WHERE clause.
The BETWEEN operator is used to select values within a range.
The LIKE operator is used in a WHERE clause to search for a specified pattern in a column.

j)
the IS NULL in a where clause is used to check whether the value is null or not.
use the IS NULL operators in order to check for a NULL value.

k)
ORDER BY clauses used to order the records based on column names, column aliases, calculated values, and column numbers in either decreasing
or increasing order.