sql_example.txt

-- The correct SQL operation order is FROM (and JOIN), WHERE, GROUP BY, HAVING, SELECT and ORDER BY

-- The HAVING clause was added to SQL because the WHERE keyword cannot be used with aggregate functions. To use HAVING it must be preceded by a GROUP BY clause
-- AGGREGATE FUNCTIONS ARE e.g. MIN, MAX, SUM, AVG, COUNT

-- FACT tables expand vertically (more rows than columns, concatenated key) DIMENSION tables expand horizontally (more columns than rows, primary key)

-- any SELECT of columns should be followed by a comma,

-- COMMON TABLE EXPRESSION CTE better performance than subqueries
WITH cte_table AS(SELECT item, amount, customer_id
                  FROM Orders)
                  
SELECT c.first_name, c.age, o.order_id, cc.item,-- SELECT * for all, SELECT DISTINCT for unique
-- SELECT *, CASE WHEN customer_id = 1 THEN 'ABC' ELSE 'DEF' END AS xyz -- to add on extra column to select all
LEAD(c.first_name,1,"N/A") OVER(ORDER BY o.order_id) as next_first_name, -- window function to show next entry
LAG(c.age,1,0) OVER(ORDER BY o.order_id) as prv_age, -- window function to show previous entry

-- partition by and over to calculate based on table sets (faster calculations)
COUNT(c.first_name) OVER (PARTITION BY cc.item) as count_per_item, -- can also have empty over clause

-- get total age by condition as subquery
(SELECT SUM(age) FROM Customers WHERE country = (SELECT country FROM Customers WHERE customer_id IN (3,4))) AS tot_age,
-- (SELECT SUM(age) FROM Customers WHERE country IN ('UK', 'USA')) AS tot_age,

-- get decimal percentage as subquery
CAST(age/(SELECT SUM(age) FROM Customers WHERE country = (SELECT country FROM Customers WHERE customer_id IN (3,4))) AS DECIMAL(10,2)) as pct,

-- cumulative age by row
SUM(age) OVER(ORDER BY c.customer_id ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS c_age

-- AGGREGATE SQL functions are those that perform calculations e.g. MIN, MAX, SUM, AVG, COUNT across multiple values to return a single value

FROM Customers c

JOIN Orders o ON o.customer_id = c.customer_id

JOIN Shippings s ON s.customer = c.customer_id

-- CTE JOIN
JOIN cte_table cc
ON c.customer_id = cc.customer_id

WHERE country = (SELECT country from Customers WHERE customer_id IN (3,4)) -- 'UK'

-- in some SQL verisons, GLOB may be needed for certain operations instead of REGEXP
-- WHERE c.first_name GLOB '*[^0-9]*' -- where first_name does not contain any standalone numbers
-- WHERE c.first_name NOT GLOB '*[^a-zA-Z0-9]*' -- where first_name is standalone characters or numbers only e.g. characters and numbers are not joined together
-- WHERE CAST(c.first_name AS VARCHAR) GLOB '*[0-9]*' -- where first_name contains standalone numbers or numbers and text
-- WHERE LOWER(c.first_name) GLOB '*[j]*' -- where first_name contains J -- LOWER to capture John, Alejandro

-- AND c.first_name REGEXP '%[^a-zA-Z0-9]%' -- where first name contains characters and numbers

AND LOWER(c.first_name) LIKE '%j%' -- where first_name contains J -- LOWER to capture John, Alejandro


-- filter by tot_age as subquery
AND (SELECT SUM(age) FROM Customers WHERE country = (SELECT country FROM Customers WHERE customer_id IN (3,4))) < 50

AND LOWER(s.status) = 'pending'

GROUP BY o.order_id
-- GROUP BY 3 -- as 3 is the column o.order_id

HAVING COUNT(c.first_name) >= 1 AND SUM(o.amount) > 200 -- AGGREGATE function needing HAVING as no result is stored in the data table (which could use WHERE if stored in data) and after a GROUP BY clause

ORDER BY o.order_id ASC; --DESC

-- You cannot join on unique primary key when selecting more than 1 row of returned rows from SELECTION, this will cause an error

-- If specifying a type e.g. c.age as integer, age must be a number otherwise the execution of the query will error

-- TEMPORARY TABLE CREATION
--IF OBJECT_ID('tempdb..TempTable') IS NOT NULL DROP TABLE TempTable;
DROP TABLE IF EXISTS TempTable;
--DROP TABLE TempTable; -- Delete TempTable if it exists to avoid error on execution

CREATE TABLE TempTable(
ID int,
Number int,
NameChar CHAR(10));
  
INSERT INTO TempTable (ID, Number, NameChar)
SELECT customer_id, amount, item
FROM Orders
ORDER BY customer_id DESC;

-- END OF TEMPORARY TABLE