SQL BASICS

-- SQL Basics

The basics of SQL

case.sql

SELECT 
  SUM(CASE WHEN Category = 'foo' THEN Amount ELSE 0 END ) as totalAmountFoo,
  SUM(CASE WHEN Category = 'bar' THEN Amount ELSE 0 END ) as totalAmountBar
FROM RevenueTable

count.sql

-- COUNT the number of customers from the CustomerTable. 
SELECT COUNT(CustomerNumber) FROM CustomerTable


-- COUNT the number of invoices from the RevenueTable. 
SELECT COUNT(InvoiceID) FROM RevenueTable

groupby.sql

-- Find the total spend grouped by each customerNumber
SELECT SUM(amount) FROM RevenueTable
GROUP BY CustomerNumber


-- Find the total spend grouped by each customerNumber where the invoice date is after Jan 1 2019.
-- Note: `WHERE` must always come before `GROUP BY`. This is standard SQL syntax.
SELECT SUM(amount) FROM RevenueTable
WHERE InvoiceDate >= '2019-01-01'
GROUP BY CustomerNumber

join-1.sql

-- The revenueTable has CustomerNo but no CustomerName. Bring in CustomerName to the revenue Table
-- Give RevenueTable the alias of r and CustomerTable the alias of c.
SELECT * FROM RevenueTable r
LEFT JOIN CustomerTable c ON c.CustomerNumber = r.CustomerNumber

like-1.sql

-- Using the LIKE keyword
/* 
  Select Customer Name and Number where
  CustomerName contains the characters `Abe`
*/
SELECT CustomerNumber,CustomerName FROM CustomerTable
WHERE CustomerName LIKE '%ABE%'

-- Expected Sample Return: Cabel, Abel, Abe, Abe Lincoln

like-2.sql

/* 
  Select Customer Name and Number where
  CustomerName starts with the characters `Abe`
*/

SELECT CustomerNumber,CustomerName FROM CustomerTable
WHERE CustomerName LIKE 'ABE%'
-- Expected Sample Return: Abel, Abe, Abe Lincoln

like-3.sql

/* 
  Select Customer Name and Number where
  CustomerName ends with the characters `Abe`
*/

SELECT CustomerNumber,CustomerName FROM CustomerTable
WHERE CustomerName LIKE '%ABE'
-- Expected Sample Return: Cabel, Abe

logic.sql

--Using the AND keyword

/* 
  Select Customer Name and Number where
  the city is New York and customer name is 'Ann Smith'
*/
SELECT CustomerNumber,CustomerName FROM CustomerTable
WHERE city = 'New York' AND customerName = 'Ann Smith'


-- Using the OR keyword
/* 
 Select Customer Name and Number where
  the city is New York or Los Angeles
*/
SELECT CustomerNumber,CustomerName FROM CustomerTable
WHERE city = 'New York' OR city = 'Los Angeles'

-- Using the IN keyword
/* 
  Select Customer Name and Number where
  CustomerNumber is `C-123456` or 'C-123457'
*/
SELECT CustomerNumber,CustomerName FROM CustomerTable
WHERE CustomerNumber IN ('C-123456','C-123457')

minmax.sql

-- Find the smallest invoice transaction
SELECT MIN(amount) FROM RevenueTable

-- Find the largest invoice transaction
SELECT MAX(amount) FROM RevenueTable

select1.sql

SELECT * FROM CustomerTable

select2.sql

SELECT CustomerNo,CustomerName FROM CustomerTable

selectdistinct.sql

SELECT DISTINCT City FROM CustomerTable

sum.sql

-- Select All Revenue from the RevenueTable
SELECT SUM(amount) FROM RevenueTable

-- Select All Revenue from the RevenueTable where Invoice Date on Jan 1 2019 or later.
SELECT SUM(amount) FROM RevenueTable
WHERE InvoiceDate >= '2019-01-01'

-- Select All Revenue from the RevenueTable where Invoice Date on Jan 1 2019 or later.
SELECT SUM(amount) FROM RevenueTable
WHERE InvoiceDate >= '2019-01-01'

where.sql

-- Select CustomerName and CustomerNumber WHERE the city is New York 
SELECT CustomerNumber,CustomerName FROM CustomerTable
WHERE city = 'New York'

The Basics of SQL