A1-Create Transact-SQL Select queries

Question 1

What is the difference between UNION and UNION ALL and what are it’s rules?

Answer 1

UNION appends only distinct rows while UNION ALL appends all rows

The following are basic rules for combining the result sets of two queries by using UNION:

• The number and the order of the columns must be the same in all queries.
• The data types must be compatible.

Question 2

What are the Characteristics of OFFSET-FETCH syntax

Answer 2

1-It is a form of filtering based on the order of rows and the number of rows it has been specified to filter;

2-The OFFSET-FETCH filter is a standard feature designed similar to TOP but with an extra element. You can specify how many rows you want to skip before specifying how many rows you want to filter;

3-The OFFSET-FETCH filter requires an ORDER BY clause to exist and is specified right after it;

4-You can use the keyword FIRST instead of NEXT if you like, though the meaning is the same.

5-Even if you don’t want to skip any rows, T-SQL still makes it mandatory to specify the OFFSET clause (with 0 ROWS) to avoid parsing ambiguity;

6-With ONLY keyword the syntax will not include any ties.Standard SQL defines the alternetive WITH TIES however T-SQL does not support it yet;

7-Similarly, standard SQL defines the PERCENT option, but T-SQL doesn’t support it yet either

8-The FETCH clause is optional

Question 3

OFFSET FETCH def, syntax

Answer 3

def:The OFFSET-FETCH clause provides you with an option to fetch only a window or page of results from the result set. OFFSET-FETCH can be used only with the ORDER BY clause.

syntax:

[ORDER BY { order_by_expression [ASC | DESC] } [,…n][] ]

::= {OFFSET { integer_constant | offset_row_count_expression } { ROW | ROWS } [FETCH { FIRST | NEXT } {integer_constant | fetch_row_count_expression } { ROW | ROWS } ONLY]}

Question 4

OFFSET FETCH syntax, arguments

Answer 4

syntax:

[ORDER BY { order_by_expression [ASC | DESC] } [,…n][] ]

::= {OFFSET { integer_constant | offset_row_count_expression } { ROW | ROWS } [FETCH { FIRST | NEXT } {integer_constant | fetch_row_count_expression } { ROW | ROWS } ONLY]}

Arguments:

OFFSET { integer_constant | offset_row_count_expression } { ROW | ROWS }
Specifies the number of rows to skip, before starting to return rows from the query expression. The argument for the OFFSET clause can be an integer or expression that is greater than or equal to zero. You can use ROW and ROWS interchangeably.

FETCH { FIRST|NEXT } { ROW|ROWS } ONLY
Specifies the number of rows to return, after processing the OFFSET clause. The argument for the FETCH clause can be an integer or expression that is greater than or equal to one. You can use ROW and ROWS interchangeably. Similarly, FIRST and NEXT can be used interchangeably.

Question 5

OFFSET-FETCH limitations

Answer 5

• ORDER BY is mandatory to use OFFSET and FETCH clause.
• OFFSET clause is mandatory with FETCH. You can never use, ORDER BY … FETCH.
• TOP cannot be combined with OFFSET and FETCH in the same query expression.
• The OFFSET/FETCH rowcount expression can be any arithmetic, constant, or parameter expression that will return an integer value. The rowcount expression does not support scalar sub-queries.

Question 6

What is /= (Division Assignment)?

Answer 6

Divides one number by another and sets a value to the result of the operation. For example, if a variable @x equals 34, then @x /= 2 takes the original value of @x, divides by 2 and sets @x to that new value (17)

Question 7

What are EXCEPT and INTERSECT statements and what are their syntaxes?

Answer 7

DEF:

Returns distinct rows by comparing the results of two queries.

EXCEPT returns distinct rows from the left input query that aren’t output by the right input query.

INTERSECT returns distinct rows that are output by both the left and right input queries operator.

To combine the result sets of two queries that use EXCEPT or INTERSECT, the basic rules are:

• The number and the order of the columns must be the same in all queries.
• The data types must be compatible.

OR:

• EXCEPT-Allows you to return the results of one query with the exception of the rows which are present in another query;
• INTERSECT-returns common rows of two tables;

syntaxes:

{ | ( ) }

{ EXCEPT | INTERSECT }

{ | ( ) }

Question 8

What is SELECT-ORDER BY?

Answer 8

DEF:Sorts data returned by a query in SQL Server. Use this clause to:

• Order the result set of a query by the specified column list and, optionally, limit the rows returned to a specified range. The order in which rows are returned in a result set are not guaranteed unless an ORDER BY clause is specified.
• Determine the order in which ranking function values are applied to the result set.

​systaxsql:

ORDER BY order_by_expression

[COLLATE collation_name]

[ASC | DESC] [,…n]

[] ::=

{ OFFSET { integer_constant | offset_row_count_expression } { ROW | ROWS } [FETCH { FIRST | NEXT } {integer_constant | fetch_row_count_expression } { ROW | ROWS } ONLY] }

Question 9

MAX-DEF, syntax, remarks

Answer 9

DEF: Returns the maximum value in the expression.

– Aggregation Function syntax: MAX( [ALL | DISTINCT] expression )

– Analytic Function syntax: MAX ([ALL] expression) OVER ( [] [] )

Remarks:

• MAX ignores any null values.
• MAX returns NULL when there is no row to select.
• For character columns, MAX finds the highest value in the collating sequence.
• MAX is a deterministic function when used without the OVER and ORDER BY clauses. It is nondeterministic when specified with the OVER and ORDER BY clauses.

Question 10

CASE-def, syntax, remarks

Answer 10

def: Evaluates a list of conditions and returns one of multiple possible result expressions.

The CASE expression has two formats:

• The simple CASE expression compares an expression to a set of simple expressions to determine the result.
• The searched CASE expression evaluates a set of Boolean expressions to determine the result.

Both formats support an optional ELSE argument.

CASE can be used in any statement or clause that allows a valid expression. For example, you can use CASE in statements such as SELECT, UPDATE, DELETE and SET, and in clauses such as select_list, IN, WHERE, ORDER BY, and HAVING.

syntaxsql:

• CASE input_expression
• WHEN when_expression THEN result_expression […n]
• [ELSE else_result_expression] END ​

Question 11

What is Search Condition?

Answer 11

def: Is a combination of one or more predicates that use the logical operators AND, OR, and NOT.
remarks: The order of precedence for the logical operators is NOT (highest), followed by AND, followed by OR. Parentheses can be used to override this precedence in a search condition. The order of evaluation of logical operators can vary depending on choices made by the query optimizer.

Question 12

FIRST_VALUE def, syntax, remarks

Answer 12

def:Returns the first value in an ordered set of values

syntax:

FIRST_VALUE ( [scalar_expression] ) [IGNORE NULLS | RESPECT NULLS] OVER ( [partition_by_clause] order_by_clause [rows_range_clause] )

General Remarks:

FIRST_VALUE is nondeterministic.

Question 13

LAST_VALUE def, syntax, remarks

Answer 13

def: Returns the last value in an ordered set of values.

syntax:

LAST_VALUE ( [scalar_expression] ) [IGNORE NULLS | RESPECT NULLS]

OVER ( [partition_by_clause] order_by_clause rows_range_clause )

remarks:

LAST_VALUE is nondeterministic

Question 14

SELECT def, syntax

Answer 14

Retrieves rows from the database and enables the selection of one or many rows or columns from one or many tables in SQL Server. The full syntax of the SELECT statement is complex, but the main clauses can be summarized as:

[WITH { [ XMLNAMESPACES ,] [] } ]

SELECT select_list [INTO new_table]

[FROM table_source] [WHERE search_condition]

[GROUP BY group_by_expression]

[HAVING search_condition]

[ORDER BY order_expression [ ASC | DESC] ]

The UNION, EXCEPT, and INTERSECT operators can be used between queries to combine or compare their results into one result set.

Question 15

What is the Logical Processing Order of the SELECT statement

Answer 15

The following steps show the logical processing order, or binding order, for a SELECT statement. This order determines when the objects defined in one step are made available to the clauses in subsequent steps. For example, if the query processor can bind to (access) the tables or views defined in the FROM clause, these objects and their columns are made available to all subsequent steps. Conversely, because the SELECT clause is step 8, any column aliases or derived columns defined in that clause cannot be referenced by preceding clauses. However, they can be referenced by subsequent clauses such as the ORDER BY clause. The actual physical execution of the statement is determined by the query processor and the order may vary from this list.

• FROM
• ON
• JOIN
• WHERE
• GROUP BY
• WITH CUBE or WITH ROLLUP
• HAVING
• SELECT
• DISTINCT
• ORDER BY
• TOP

Question 16

WHERE and HAVING Filters?

Answer 16

WHERE filters unaggragated data while HAVING filters only aggregated date.

Question 17

ALL-def syntax, remarks

Answer 17

def: compares a scalar value with a single-column set of values.
syntax: scalar_expression { = | <> | != | > | >= | !> | < | <= | !< } ALL ( subquery );

remarks:

• ALL requires the scalar_expression to compare positively to every value that is returned by the subquery. For instance, if the subquery returns values of 2 and 3, scalar_expression <= ALL (subquery) would evaluate as TRUE for a scalar_expression of 2. If the subquery returns values of 2 and 3, scalar_expression = ALL (subquery) would evaluate as FALSE, because some of the values of the subquery (the value of 3) wouldn’t meet the criteria of the expression.
• ALL compares a value to a set of values in the subquery and requires all values to be return true.