This week, the goal had been to level up my SQL skills on Codewars. In one week, I’ve gotten to 5 kyu for SQL. These are the notes I’ve noticed from solving problems.

Missing Values In SQL
Substitute Missing Data
SELECT GDP, Country, ISNULL(Country, 'Unknown') AS NewCountry FROM EconomicIndicators;
Substitute Missing Data With Values From Another Column
SELECT TradeGDPPercent, ImportGoodPercent, ISNULL(TradeGDPPercent, ImportGoodPercent) AS NewPercent FROM EconomicIndicators;
COALESCE
- Uses the first value in row to determine what is missing value in column.
- If all preceding columns in row are Null, then the last value in COALESCE become the value in cell
- NULLIF() turns empty row into null, use COALESCE to insert value
- COALESCE(NULLIF({col}, ”), {new_value for blank})
SELECT TradeGDPPercent, ImportGoodPercent, COALESCE(TradeGDPPercent, ImportGoodPercent, 'N/A') AS NewPercent FROM EconomicIndicators;
Dates
DATEDIFF()
- Syntax: DATEDIFF(interval, date1, date2
- Intervals:
- year, yyyy, yy = Year
- quarter, qq, q = Quarter
- month, mm, m = month
- dayofyear = Day of the year
- day, dy, y = Day
- week, ww, wk = Week
- weekday, dw, w = Weekday
- hour, hh = hour
- minute, mi, n = Minute
- second, ss, s = Second
- millisecond, ms = Milliseconds
SELECT OrderDate, ShipDate, DATEDIFF(DD, OrderDate, ShipDate) AS Duration FROM Shipments;
DATEADD()
- Syntax DATEADD(interval, number, date)
- Interval:
- year, yyyy, yy = Year
- quarter, qq, q = Quarter
- month, mm, m = month
- dayofyear, dy, y = Day of the year
- day, dd, d = Day
- week, ww, wk = Week
- weekday, dw, w = Weekday
- hour, hh = hour
- minute, mi, n = Minute
- second, ss, s = Second
- millisecond, ms = Millisecond
SELECT OrderDate, ShipDate, DATEADD(DD, 5, ShipDate) As DeliveryDate FROM Shipments;
Math Values
- absolute value: ABS()
- square: SQUARE()
- square root: SQRT()
- natural log: LOG(number, base)
Running Total
- Syntax: SUM({column}) OVER (ORDER BY {column1})
- SUM(COUNT(created_at)) OVER (ORDER BY created_at::DATE)::INT AS total
-- create running total from count SELECT CREATED_AT::DATE AS DATE, COUNT(CREATED_AT) AS COUNT, SUM(COUNT(CREATED_AT)) OVER (ORDER BY CREATED_AT::DATE ROWS UNBOUNDED PRECEDING)::INT AS TOTAL FROM POSTS GROUP BY CREATED_AT::DATE
Rounding Numbers
- Syntax: ROUND(number, decimals, operation])
- Positive decimals is to the right of decimal
- Negative decimals is rounding to the left of decimal
- Operation (truncate to decimal places)
- 0: rounds to the number of decimal
- n: truncates result to the number of decimals
SELECT Cost, ROUND(Cost, 0, 1) AS RoundCost -- trunncates to one decimal place FROM Shipments;
RANK()
- syntax: RANK() OVER (ORDER BY salary) salary_rank
Convert Columns To Type
Convert()
- Syntax: CONVERT(data_type(length), expression, style)
- data_type (required):
- bigint, int, smallint, tinyint, bit, decimal, numeric, money, smallmoney, float, real, datetime, smalldatetime, char, varchar, text, nchar, nvarchar, ntext, binary, varbinary, or image
- expression (required): The value to convert to another data type
Cast()
- Syntax: CAST(expression AS datatype(length))
- datatype:
- bigint, int, smallint, tinyint, bit, decimal, numeric, money, smallmoney, float, real, datetime, smalldatetime, char, varchar, text, nchar, nvarchar, ntext, binary, varbinary, or image
In Mathematical Format (::)
- sum(p.amount::float)
Strings
UPPER(), LOWER(), INITCAP()
- use INITCAP() similar to title() in python
- INITCAP() capitalizes each word separated by a separator
Concat With Space
- syntax: CONCAT_WS(separator, expression1, expression2, expression3,…)
- alternative to CONCAT():
- use || between columns
- syntax: {col1} || ‘ ‘ || {col2} AS {new_col}
CONCAT_WS(' ', prefix, first, last, suffix) AS title FROM names;
Extract Functions
EXISTS ()
- link: https://www.w3schools.com/sql/sql_exists.asp
- syntax: SELECT * FROM {table} WHERE EXISTS (SELECT {col} FROM {table} WHERE {condition});
- Used to test for existense of any record in subquery
- SELECT 1 FROM table can also be used instead of actually selecting columns in EXISTS table
- Similar to joining and getting the column from another table. Exists simply returns True for conditions that match, and the outer SELECT statement gets the information from the EXISTS table
-- syntax for exists SELECT column_name(s) FROM table_name WHERE EXISTS (SELECT column_name FROM table_name WHERE condition);
FILTER ()
- Simple filter function to aggregate functions on row
- COUNT(1) FILTER (WHERE staff_id=’1′) AS mike_count
- SUM(amount) FILTER (WHERE staff_id=’2′) AS jon_amount
Views
-- Syntax CREATE VIEW view_name AS SELECT column1, column2, ... FROM table_name WHERE condition SELECT * from view_name;
SQL Shortcuts
See All Columns From One Table When Joining
- Syntax: SELECT {table}.* FROM {table} JOIN {table2} ON {table.key}={table2.foreign_key}
Fun Code
-- using WITH to group 2 columns at different levels create view members_approved_for_voucher (id, name, email, total_spending) as with target_departments (id) as ( select d.id from departments as d inner join sales as s on s.department_id = d.id inner join products as p on p.id = s.product_id group by d.id having SUM(p.price) > 10000 ) select m.id, m.name, m.email, SUM(p.price) from members as m inner join sales as s on s.member_id = m.id inner join target_departments as td on td.id = s.department_id inner join products as p on p.id = s.product_id group by m.id, m.name, m.email having SUM(p.price) > 1000; select id, name, email, total_spending from members_approved_for_voucher order by id; -- Different Way To Join CREATE VIEW members_approved_for_voucher AS SELECT M.id, M.name, M.email, SUM(P.price) AS total_spending FROM members AS M, sales AS S, products AS P, departments as D WHERE M.id = S.member_id AND P.id = S.product_id AND D.id = S.department_id AND (SELECT SUM(products.price) FROM products, sales WHERE products.id=sales.product_id AND D.id=sales.department_id) > 10000 GROUP BY M.id HAVING total_spending > 1000; SELECT * FROM members_approved_for_voucher