2-Reformat-extract-string.sql

/*
This data comes from a LinkedIn Learning training called Intermediate SQL for Data Scientists
This script refers to the 'Data Munging with SQL' module
*/

/******************************************************************
--- String Functions
*******************************************************************/

-- Retrieve all columns and rows from the company_departments table
SELECT *
FROM data_sci.company_departments;

-- Convert all department names to uppercase
SELECT UPPER(department_name)
FROM data_sci.company_departments;

-- Convert department names to title case (first letter of each word capitalised)
SELECT INITCAP(department_name)
FROM data_sci.company_departments;

-- Convert title-cased department names back to lowercase
-- Note: nesting LOWER(INITCAP()) here produces the same result as just LOWER()
-- INITCAP is redundant in this context
SELECT LOWER(INITCAP(department_name))
FROM data_sci.company_departments;

-- Return the string literal ' kelly' (note the leading space)
SELECT ' kelly';

-- Compare ' kelly' (with leading space) to 'kelly' — will return FALSE
-- Demonstrates that whitespace affects string equality
SELECT ' kelly' = 'kelly';

-- Remove the leading space from ' kelly' using LTRIM
SELECT LTRIM(' kelly');

-- Confirm that LTRIM removes the leading space — will return TRUE
SELECT LTRIM(' kelly') = 'kelly';

-- LTRIM only removes leading (left) spaces, not trailing (right) spaces
-- This will return FALSE as the trailing spaces remain
SELECT LTRIM(' kelly  ') = 'kelly';

-- RTRIM removes trailing spaces; nesting RTRIM(LTRIM()) removes both ends
-- This will return TRUE — equivalent to using TRIM() which does both in one function
SELECT RTRIM(LTRIM(' kelly  ')) = 'kelly';

-- Concatenate job_title and last_name using the || operator
-- Note: no separator so values will run together (e.g. 'ManagerSmith')
SELECT job_title || last_name
FROM data_sci.employees;

-- Concatenate job_title and last_name with a '-' separator using ||
SELECT job_title || '-' || last_name
FROM data_sci.employees;

-- Concatenate job_title with NULL using ||
-- In most databases || with NULL returns NULL for the whole expression
-- All results will be NULL
SELECT job_title || '-' || NULL
FROM data_sci.employees;

-- Concatenate job_title and last_name using CONCAT() function with '-' separator
-- CONCAT() is more readable than || for multiple values
SELECT CONCAT(job_title, '-', last_name)
FROM data_sci.employees;

-- CONCAT() with NULL — unlike ||, CONCAT() treats NULL as an empty string
-- Results will still show job_title with '-' rather than returning NULL
SELECT CONCAT(job_title, '-', NULL)
FROM data_sci.employees;

-- CONCAT_WS (Concat With Separator) joins values with '-' between each
-- Cleaner than repeating the separator manually between each value
SELECT CONCAT_WS('-', job_title, last_name)
FROM data_sci.employees;

-- Extend CONCAT_WS to include email as a third value
-- The '-' separator is automatically applied between all three fields
SELECT CONCAT_WS('-', job_title, last_name, email)
FROM data_sci.employees;

-- Return the literal string 'abcdefghijk' labelled as test_string
-- Useful for testing string functions without needing a table
SELECT 'abcdefghijk' AS test_string;

-- Extract the first 3 characters starting at position 1
-- SUBSTRING(string, start_position, length)
-- Returns 'abc'
SELECT SUBSTRING('abcdefghijk', 1, 3) AS test_string;

-- Alternative SQL-standard syntax for the same operation
-- SUBSTRING(string FROM start_position FOR length)
-- Returns 'abc' — identical result to the query above
SELECT SUBSTRING('abcdefghijk' FROM 1 FOR 3) AS test_string;

-- Extract all characters from position 5 to the end of the string
-- Omitting FOR length returns everything from the start position onwards
-- Returns 'efghijk'
SELECT SUBSTRING('abcdefghijk' FROM 5) AS test_string;

-- Retrieve all columns and rows from the employees table
SELECT *
FROM data_sci.employees;

-- Filter employees whose job title contains the word 'assistant' anywhere
-- % wildcards on both sides match any characters before and after 'assistant'
-- Note: LIKE is case-sensitive in most databases — consider ILIKE for 
--       case-insensitive matching in PostgreSQL
SELECT *
FROM data_sci.employees
WHERE job_title LIKE '%assistant%';

-- Return job title alongside a boolean column flagging whether the
-- employee is an assistant. LIKE returns TRUE/FALSE which is aliased
-- as is_assistant for readability.
-- Note: WHERE clause still filters to assistants only, so is_assistant
--       will always be TRUE here — consider removing the WHERE clause
--       to make the boolean flag useful across all employees
SELECT job_title, (job_title LIKE '%assistant%') AS is_assistant
FROM data_sci.employees
WHERE job_title LIKE '%assistant%';

-- Retrieve all job titles from employees (including duplicates)
SELECT job_title
FROM data_sci.employees;

-- Retrieve unique job titles starting with 'vp'
-- DISTINCT removes duplicate values from the result set
SELECT DISTINCT job_title
FROM data_sci.employees
WHERE job_title LIKE 'vp%';

-- Retrieve unique job titles starting with either 'vp' or 'web'
-- Using two LIKE conditions joined with OR
SELECT DISTINCT job_title
FROM data_sci.employees
WHERE job_title LIKE 'vp%' 
OR job_title LIKE 'web%';

-- Equivalent to the query above but using SIMILAR TO with alternation (|)
-- SIMILAR TO supports regex-like syntax: (pattern1|pattern2)
-- More concise than multiple LIKE conditions for matching several patterns
SELECT DISTINCT job_title
FROM data_sci.employees
WHERE job_title SIMILAR TO '(vp%|web%)';

-- LOGIC WARNING: space inside the pattern ' web%' will not match 'web...'
-- SIMILAR TO patterns are literal — the space is included in the match
-- This will miss any job titles starting with 'web' due to the extra space
SELECT DISTINCT job_title
FROM data_sci.employees
WHERE job_title SIMILAR TO '(vp% | web%)';

-- Correct version of the above — no spaces inside the alternation pattern
SELECT DISTINCT job_title
FROM data_sci.employees
WHERE job_title SIMILAR TO '(vp%|web%)';

-- Retrieve unique job titles starting with 'vp'
-- Demonstrates SIMILAR TO with a single pattern (equivalent to LIKE 'vp%')
SELECT DISTINCT job_title
FROM data_sci.employees
WHERE job_title SIMILAR TO 'vp%';

-- Retrieve unique job titles starting with 'vp' and ending with 'a'
-- % matches any characters in between, but the title must end in 'a'
SELECT DISTINCT job_title
FROM data_sci.employees
WHERE job_title SIMILAR TO 'vp%a';

-- Retrieve unique job titles starting with 'vp' and containing 'a' anywhere after
-- The trailing % means the title can have any characters after the 'a'
SELECT DISTINCT job_title
FROM data_sci.employees
WHERE job_title SIMILAR TO 'vp%a%';

-- Match job titles that are literally 'vp a...' with a space before 'a'
-- This is an explicit literal space match, not a wildcard
SELECT DISTINCT job_title
FROM data_sci.employees
WHERE job_title SIMILAR TO 'vp a%';

-- Match job titles where 'vp' is followed by exactly one character then 'a'
-- _ is a single character wildcard (equivalent to . in regex)
-- e.g. would match 'vp of accounting' if the pattern continues correctly
SELECT DISTINCT job_title
FROM data_sci.employees
WHERE job_title SIMILAR TO 'vp_a%';

-- Match job titles that are exactly 'vp a' or 'vp m' with nothing after
-- No trailing % so this is a full string match — unlikely to return results
-- as most titles will have more characters after 'a' or 'm'
SELECT DISTINCT job_title
FROM data_sci.employees
WHERE job_title SIMILAR TO 'vp (a|m)';

-- Corrected version: adds trailing % to match anything after 'vp a' or 'vp m'
-- e.g. matches 'vp accounting', 'vp marketing' etc.
SELECT DISTINCT job_title
FROM data_sci.employees
WHERE job_title SIMILAR TO 'vp (a|m)%';

-- Retrieve the raw salary values for all employees
SELECT salary
FROM data_sci.employees;

-- Calculate the average salary across all employees (returns many decimal places)
SELECT AVG(salary)
FROM data_sci.employees;

-- Truncate the average salary to a whole number (no decimal places)
-- TRUNC removes decimal places without rounding — e.g. 98765.9 becomes 98765
SELECT TRUNC(AVG(salary))
FROM data_sci.employees;

-- Truncate the average salary to 2 decimal places
-- TRUNC(value, 2) keeps exactly 2 decimal places without rounding
-- e.g. 98765.9876 becomes 98765.98 (not 98765.99)
SELECT TRUNC(AVG(salary), 2)
FROM data_sci.employees;

-- Round the average salary UP to the nearest whole number using CEIL
-- CEIL always rounds up regardless of the decimal value
-- e.g. 98765.01 becomes 98766 (compare to FLOOR which always rounds down)
SELECT CEIL(AVG(salary))
FROM data_sci.employees;

-- Truncate the average salary to 4 decimal places for higher precision
-- Useful when small decimal differences are meaningful
SELECT TRUNC(AVG(salary), 4)
FROM data_sci.employees;

/******************************************************************
--- Fuzzy String Matching: Soundex, Difference and Levenshtein
*******************************************************************/

-- Install the fuzzystrmatch extension which provides phonetic and 
-- fuzzy matching functions (soundex, difference, levenshtein)
-- IF NOT EXISTS prevents an error if the extension is already installed
CREATE EXTENSION IF NOT EXISTS fuzzystrmatch;

-- Generate the soundex code for 'Postgres'
-- Soundex encodes a string into a phonetic code based on how it sounds
-- e.g. 'Postgres' returns 'P362'
SELECT 
	SOUNDEX('Postgres');

-- Compare soundex codes for 'Postgres' and 'Postgresss' (extra letters)
-- Demonstrates that soundex is tolerant of minor spelling differences
-- Both should return the same code as they sound the same
SELECT 
    SOUNDEX('Postgres') p1,
    SOUNDEX('Postgresss') p2;

-- Four-way comparison showing the difference between exact and phonetic matching:
-- 1. Soundex code for 'Postgres'
-- 2. Soundex code for 'Postgresss'
-- 3. Exact string equality — returns FALSE (different strings)
-- 4. Soundex equality — returns TRUE (sound the same)
-- Highlights the core value of fuzzy matching over exact matching
SELECT
    SOUNDEX('Postgres'),
    SOUNDEX('Postgresss'),
    ('Postgres' = 'Postgresss') AS exact_match,
    SOUNDEX('Postgres') = SOUNDEX('Postgresss') AS sounds_alike;

-- DIFFERENCE() returns a score from 0-4 comparing two soundex codes
-- 4 = very similar sounding, 0 = very different
-- 'Postgres' vs 'Postgresss' should return 4 (sounds identical)
SELECT
    DIFFERENCE('Postgres', 'Postgresss');

-- Compare 'Postgres' and 'Kostgres' (different starting letter)
-- Shows how soundex and difference handle phonetically similar 
-- but visually different strings
-- Soundex codes will differ due to the K vs P, and difference score
-- will reflect the reduced phonetic similarity
SELECT
    SOUNDEX('Postgres'),
    SOUNDEX('Kostgres'),
    DIFFERENCE('Postgres', 'Kostgres');

-- LEVENSHTEIN measures edit distance — the number of insertions, deletions
-- or substitutions needed to transform one string into another
-- Comparing identical strings returns 0 (no edits needed)
SELECT
    LEVENSHTEIN('Postgres', 'Postgres');

-- 'Kostgres' vs 'Postgres' — differs only in the first letter (K vs P)
-- Returns 1 (one substitution needed)
SELECT
    LEVENSHTEIN('Postgres', 'Kostgres');

-- 'Postgres' vs 'mySQL' — very different strings
-- Returns a high edit distance reflecting how many changes are needed
-- to transform one into the other
SELECT
    LEVENSHTEIN('Postgres', 'mySQL');

/******************************************************************
--- Challenge
1. What functions could you use to reformat a number having eight decimal places to two decimal places?
R.: ROUND or TRUNC, specifying '2' as the second parameter

2. What operator would you use to filter using regular expressions?
R.: SIMILAR TO

3. What function would you use to measure the difference in characters between two strings?
R.: Levenshtein
*******************************************************************/