[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 ;