Queries - Intermidiate functions

Question 1

What is a nested Query?

Question 2

Why use a nested Query?

Question 3

How to use a nested Query

Question 4

What is an Aggregator and where to use is?

Question 5

Example of aggregators

Question 6

ORDER BY()

Answer 6

ORDER BY() allows the sorting of the tuples resulting from the SELECT() command in a defined order. Aliases can be used in the ORDER BY() clause.
E.g.
SELECT ProductCat AS Category, ProductName
FROM Product
ORDER BY Category ASC, Price DESC;

The above will return the ProductCat and the ProductName (from the Product table) ordered the ascending order for the ProductCat column and descending for the ProductName column.

Question 7

TOP()

Answer 7

TOP() is not ANSI! TOP returns a given number of rows, either as a positive integer or as a percentage of all qualifying rows. The number of rows can be specified as a constant or as an expression. Requires an ORDER BY in order to make sense:
E.g.
SELECT TOP 10 Name, ListPrice
FROM Production.Product
ORDER BY ListPrice DESC;
The above returns the 10 most expensive (10 top ListPrice ind descending order) products together with the name of the product.

Question 8

WITH TIES

Answer 8

To be used together with “TOP” - WITH TIES returns all tuples qualifying for the “TOP” clause. NB! - How to use the function is not very clear…
E.g:
SELECT TOP 10 WITH TIES Name, ListPrice
FROM Production.Product
ORDER BY ListPrice DESC

Question 9

PERCENT

Answer 9

To be used together with TOP - Returns a percentage of the eligible rows instead of fixed number.
E.g.
SELECT TOP 10 PERCENT Name, ListPrice
FROM SalesLT.Product
ORDER BY ListPrice DESC;
Can also be used together with “WITH TIES”