DATABASE Section 7

[What are the MSSQL database functions?]

[What are the PostgresSQL database functions?]

Remember the HAVING keyword only applies to groups. The WHERE keyword only applies to rows.

Queries can use alias on table names and column names. The AS keyword is optional.

SELECT p.ProductName AS "Product Name",
    c.CategoryDescription AS "Category Description"
FROM products.products AS p
    JOIN products.categories AS c
        ON p.CategoryID = c.CategoryId
;

Strings can be converted to a result set.

SELECT 'Adam' AS "Name";

Some common math functions in SQL

SELECT
    MAX(Price) AS "Maximum Price",
    MIN(Price) AS "Minimum Price",
    AVG(Price) AS "Average Price"
FROM products.products;

SQL columns can use math operators.

SELECT SKU,
    ProductName,
    CategoryID,
    Size,
    Price,
    '8.5%' AS TaxRate,
    Price * 0.085 AS SalesTax,
    Price + (Price * 0.085) AS TotalPrice,
    ROUND((Price * 1.085), 2) AS TotalPrice
FROM products.products;

Select queries can be nested.

SELECT ProductName, Size, Price
FROM products.products
WHERE Price =
    (SELECT MAX(Price)
    FROM products.products)
;

Make a query show unique rows.

SELECT DISTINCT
    categories.CategoryDescription
FROM products.products
    JOIN products.categories
        ON products.CategoryID = categories.CategoryID
;

Use aggregates to count the number of records.

SELECT
    categories.CategoryDescription,
    COUNT(products.SKU) as "Number of Skus"
FROM products.products
    JOIN products.categories
        ON products.CategoryID = categories.CategoryID
GROUP BY CategoryDescription
ORDER BY "Number of Skus" DESC
;

Aggregate functions operate on each group individually.

SELECT
    categories.CategoryDescription,
    COUNT(products.SKU) as "Number of Skus",
    MAX(products.Price) AS "Maximum Price",
    MIN(products.Price) AS "Minimum Price",
    AVG(products.Price) AS "Average Price"
FROM products.products
    JOIN products.categories
        ON products.CategoryID = categories.CategoryID
GROUP BY CategoryDescription
ORDER BY "Number of Skus" DESC
;

The HAVING keyword filters aggregate results.

SELECT categories.CategoryDescription,
    COUNT(products.SKU) AS "Number of SKUs",
    MAX(products.Price) AS "Maximum Price",
    MIN(products.Price) AS "Minimum Price",
    AVG(products.Price) AS "Average Price"
    FROM products.products
    JOIN products.categories
    ON products.CategoryID = categories.CategoryID
WHERE products.Price > 15
GROUP BY CategoryDescription
HAVING CategoryDescription = 'Olive Oils'
--HAVING NOT CategoryDescription = 'Bath and Beauty'
--HAVING COUNT(products.SKU) < 10
ORDER BY COUNT(products.SKU) DESC
;

Leave a Reply

Your email address will not be published. Required fields are marked *