SQL quick bite 01 (Getting Top entries, Filtering and UNION)

1) Using * in your query

* means all columns

if there is no filtering or where condition then it fetches all rows

2) Use of quotes

use single quote ' ' to add space for concatenation in the query

use double quote "Customer Name" for AS names else it cannot run with a space in the query. 

use concat function: SELECT concat(firstname, ' ' , lastname) as "Customer Name" from table

cannot concat char + int. we need to CAST datatype to be the same type. 

3) To get Top entries

SELECT TOP - This allows us to limit the number or percentage of rows returned by a query. 

eg. SELECT TOP n  where n is a number eg SELECT TOP 10

or TOP n Percent eg. SELECT TOP 10 Percent

eg. SELECT TOP n WITH TIES - retrieve duplicates where applicable (nondeterministic)

OFFSET-FETCH is an extension to the ORDER BY clause:
-allows filtering a requested range of rows
-provides a mechanism for paging through results
- specify a number of rows to skip, number of rows to retrieve.

eg. ORDER BY <order_by_list>
OFFSET <offset_value> ROW(S)
FETCH FIRST|NEXT <fetch_value> ROW(S) ONLY 

this skip/offset 10 rows then fetch next 10 rows and in this case, using FIRST or NEXT keyword does not matter. 

eg. SELECT name, weight from Table 
ORDER BY weight DESC 
OFFSET 10 ROWS FETCH NEXT 100 ROWS ONLY

4) Filtering with LIKE

Using % to denote anything in front or behind key letter/s of interest. 
eg A% for anything that starts with A or %D anything that ends with D
Using - and [to denote the characters inside]:
eg. 'FR-_[0-9][0-9]_-[0-9][0-9]' 

5) Using UNION, INTERSECT, EXCEPT

Unlike JOIN where we are extending horizontally by adding columns from many tables using the unique key, UNIONS, INTERSECT, EXCEPT works by finding rows in two tables and returning them into the same column with no unique key requirement. 

UNION returns a result set of distinct rows combined from all statements. It removes duplicates during the query processing and thus affecting its performance and this gets worse with every union added into the same query. As such it is better to use UNION ALL (retains the duplicates) if we know the data have no overlap. 

UNION guidelines:
Must have Alias in the first query as the bottom queries' alias will be ignored. The original column header does not describe it well anymore given the additional data from another table thus the need of alias.

The number of columns for each table must be the same.

The data types must be the same.

INTERSECT returns only distinct rows that appear in both result sets.

EXCEPT returns only distinct rows that appear in the first set but not the second and the order of what to be kept in each which sets matters.

UNION is useful for archiving data since the datasets are growing so it makes more sense to archive old past year data to keep the working set manageable. However, when we need the past data for trends or insights, we can use UNION ALL to get them back.