Text, number and date functions

SELECT Name,
       UPPER(Name) AS Upper,
       LOWER(City) AS LowerCity,
       LENGTH(Name) AS Letters,
       SUBSTR(Phone, 1, 4) AS Prefix,
       SUBSTR(Phone, -3) AS Last3,
       REPLACE(Phone, '07', '+2547') AS Intl,
       Name || ' (' || City || ')' AS Label
FROM Customers;

SELECT Name FROM Customers WHERE Name LIKE 'K%';        -- starts with K
SELECT Name FROM Products WHERE Name LIKE '%USB%';       -- contains USB
SELECT Name FROM Customers WHERE Name LIKE '_a%';        -- second letter a

SELECT Name, Price,
       ROUND(Price * 1.16) AS WithVat,
       ROUND(Price * 0.9, 2) AS TenPercentOff,
       Price % 1000 AS Remainder,
       ABS(Price - 2000) AS DistanceFrom2000
FROM Products;

SELECT 7 / 2 AS IntDiv, 7 / 2.0 AS RealDiv, CAST('42' AS INTEGER) + 1 AS FromText;

SELECT DATE('now') AS Today,
       DATE('2026-08-02', '+30 days') AS DueDate,
       STRFTIME('%Y', '2026-08-02') AS Year,
       STRFTIME('%m', '2026-08-02') AS Month,
       JULIANDAY('2026-09-03') - JULIANDAY('2026-08-02') AS DaysBetween;

-- Orders per month
SELECT STRFTIME('%Y-%m', OrderDate) AS Month, COUNT(*) AS Orders, SUM(Quantity) AS Items
FROM Orders
GROUP BY Month
ORDER BY Month;

-- Orders in the first half of August
SELECT * FROM Orders WHERE OrderDate BETWEEN '2026-08-01' AND '2026-08-15';

SELECT UPPER(SUBSTR(Name, 1, INSTR(Name, ' ') - 1)) AS FirstName,
       City,
       '+254' || SUBSTR(Phone, 2) AS WhatsApp
FROM Customers
ORDER BY City, FirstName;

SELECT
  Name,
  UPPER(Name)                                   AS Upper,
  LENGTH(Name)                                  AS Chars,
  SUBSTR(Name, 1, INSTR(Name, ' ') - 1)         AS FirstName,
  SUBSTR(Name, INSTR(Name, ' ') + 1)            AS LastName,
  '254' || SUBSTR(Phone, 2)                     AS IntlPhone,
  SUBSTR(Phone, 1, 4) || '***' || SUBSTR(Phone, -3) AS Masked
FROM Customers;

WITH messy(raw) AS (
  VALUES ('  nairobi '), ('NAIROBI'), ('Nrb'), ('Kisumu  '), ('mombasa')
)
SELECT raw,
  TRIM(raw) AS Trimmed,
  UPPER(SUBSTR(TRIM(raw), 1, 1)) || LOWER(SUBSTR(TRIM(raw), 2)) AS Proper,
  CASE UPPER(TRIM(raw)) WHEN 'NRB' THEN 'Nairobi' ELSE UPPER(SUBSTR(TRIM(raw), 1, 1)) || LOWER(SUBSTR(TRIM(raw), 2)) END AS Clean
FROM messy;

SELECT Name FROM Products WHERE Name LIKE '%USB%' OR Name LIKE '%disk%';
SELECT Name FROM Customers WHERE Name LIKE '% A%';    -- surname starts with A

SELECT Name, Price,
  ROUND(Price * 1.16, 2)         AS WithVAT,
  ROUND(Price * 0.16 / 1.16, 2)  AS VATIncluded,
  printf('KSh %,d', Price)       AS Formatted,
  Price / 1000                   AS Thousands,
  Price / 1000.0                 AS ThousandsDecimal,
  Price % 1000                   AS Remainder
FROM Products;

SELECT strftime('%Y-%m', OrderDate) AS Month,
       COUNT(*) AS Orders
FROM Orders
GROUP BY Month
ORDER BY Month;

SELECT OrderID, OrderDate,
  CASE strftime('%w', OrderDate)
    WHEN '0' THEN 'Sunday' WHEN '1' THEN 'Monday' WHEN '2' THEN 'Tuesday'
    WHEN '3' THEN 'Wednesday' WHEN '4' THEN 'Thursday' WHEN '5' THEN 'Friday'
    ELSE 'Saturday' END AS Weekday
FROM Orders;

SELECT OrderID, OrderDate,
  date(OrderDate, '+14 days')                          AS DeliveryDue,
  date(OrderDate, 'start of month')                    AS MonthStart,
  date(OrderDate, 'start of month', '+1 month', '-1 day') AS MonthEnd,
  CAST(julianday('2026-09-30') - julianday(OrderDate) AS INTEGER) AS DaysBeforeSep30
FROM Orders;