SQL

JOIN

SELECT StudentCourse.COURSE_ID, Student.NAME, Student.AGE 
FROM Student
INNER JOIN StudentCourse
ON Student.ROLL_NO = StudentCourse.ROLL_NO;

• JOIN (or INNER JOIN)
• LEFT JOIN (or LEFT OUTER JOIN)
• RIGHT JOIN (or RIGHT OUTER JOIN)
• OUTER JOIN (or fULL OUTER JOIN)

COALESCE

COALESCE is an alternative of ISNULL

SELECT COALESCE(SUM(a.spend), 0) as sales
FROM orders a
INNER JOIN dates b
ON a.dateID = b.dateID

OVER PARTITION BY

SELECT Customercity, 
       AVG(Orderamount) AS AvgOrderAmount, 
       MIN(OrderAmount) AS MinOrderAmount, 
       SUM(Orderamount) TotalOrderAmount
FROM [dbo].[Orders]
GROUP BY Customercity;


SELECT Customercity, CustomerName ,OrderAmount,
       AVG(Orderamount) AS AvgOrderAmount, 
       MIN(OrderAmount) AS MinOrderAmount, 
       SUM(Orderamount) TotalOrderAmount
FROM [dbo].[Orders]
GROUP BY Customercity;

Error: Column CustomerName is invalid in the select list because it in not contained in either an aggregare function or the GROUP BY clause.

SELECT Customercity, 
       CustomerName, 
       OrderAmount, 
       AVG(Orderamount) OVER(PARTITION BY Customercity) AS AvgOrderAmount, 
       MIN(OrderAmount) OVER(PARTITION BY Customercity) AS MinOrderAmount, 
       SUM(Orderamount) OVER(PARTITION BY Customercity) TotalOrderAmount
FROM [dbo].[Orders];

SELECT seq, week, min_date, max_date
FROM
(
    SELECT rownum seq, week, min_date, max_date
    FROM
    (
        SELECT DISTINCT week,
        MIN(dateID) OVER (PARTITION BY week) min_date,
        MAX(dateID) OVER (PARTITION BY week) max_date
        FROM dates
        WHERE week <= '202201'
        ORDER BY week DESC
    )
)
WHERE seq <= 250

SELECT col1,
SUM(col2) OVER (PARTITION BY col3, col4 ORDER BY col1) AS col22
FROM table1

SELECT seq, week, epoch, min_date, max_date
FROM
(
    SELECT rownum seq, week, epoch, min_date, max_date
    FROM
    (
        SELECT DISTINCT week, epoch,
        MIN(dateID) OVER (PARTITION BY week) min_date,
        MAX(dateID) OVER (PARTITION BY week) max_date
        FROM dates
        WHERE epoch BETWEEN
            (SELECT DISTINCT epoch FROM dates WHERE week = 202201) - 12
            AND (SELECT DISTINCT epoch FROM dates WHERE week = 202201) + 12
        ORDER BY epoch
    )
)

CASE

SELECT col1, col2,
MAX(one) 'one', MAX(two) 'two', MAX(more) 'more'
FROM
(
    SELECT col1, col2, TR,
    (CASE WHEN TR = 'one' THEN COUNT(col4) end) 'one',
    (CASE WHEN TR = 'two' THEN COUNT(col4) end) 'two',
    (CASE WHEN TR = 'more' THEN COUNT(col4) end) 'more' 
    FROM
    (
        SELECT col1, col2,
        CASE
        WHEN col3 = 1 THEN 'one'
        WHEN col3 = 2 THEN 'two'
        WHEN col3 > 2 THEN 'more'
        END 'TR'
        FROM table1
    )
    GROUP BY col1, col2, TR
)
GROUP BY col1, col2
ORDER BY col2

SELECT col1
(CASE WHEN (col1 = 0 OR col1 IS NULL) THEN 0 ELSE col1 END) AS col1
FROM table1

CAST

SELECT col1,
CAST('T99999' AS VARCHAR2(20)) AS T99999,
CAST('ALL' AS VARCHAR2(20)) ALL
FROM table1
WHERE 1 = 0

UNPIVOT

SELECT *
FROM table1
UNPIVOT
(col1 FOR col2 IN(T11111, T99999))
ORDER BY col2

SELECT a.*,
RANK() OVER (PARTITION BY col1, col2
ORDER BY col3 desc) 'col3'
FROM table a

GROUPING SETS

SELECT
    aggregate_function(column_1)
    column_2,
    column_3,
FROM
    table_name
GROUP BY
    GROUPING SETS (
        (column_2, column_3),
        (column_2),
        (column_3),
        ()
);

UNION ALL

GROUPING SETS is equivalent to UNION ALL

SELECT SUM(column_1), column_2, column_3
FROM table_name
GROUP BY
    column_2,
    column_3

UNION ALL

SELECT SUM(column_1), column_2, NULL
FROM table_name
GROUP BY column_2

ROLLUP

Similar to GROUPING SETS, you can use the ROLLUP option

SELECT
  SUM(payment_amount),
  YEAR(payment_date) AS 'Payment Year',
  store_id AS 'Store'
FROM payment
GROUP BY ROLLUP (YEAR(payment_date), store_id)
ORDER BY YEAR(payment_date), store_id

CUBE

CUBE is like combining GROUPING SETS and ROLLUP

The main difference in this output from the ROLLUP example is that the grand total for each store is also shown

SELECT
  SUM(payment_amount),
  YEAR(payment_date) AS 'Payment Year',
  store_id AS 'Store'
FROM payment
GROUP BY CUBE (YEAR(payment_date), store_id)
ORDER BY YEAR(payment_date), store_id

PL/SQL

BEGIN
    FOR c IN (SELECT table_name FROM user_tables WHERE table_name LIKE '%MSTR_%')
        LOOP
        DBMS_OUTPUT.PUT_LINE(c.table_name);
        EXECUTE IMMEDIATE 'DROP TABLE' || c.table_name;
    END LOOP;
END;