Last active
April 25, 2019 22:27
-
-
Save arhoy/29b3a6574ee809e97728501a3f0c8ef2 to your computer and use it in GitHub Desktop.
SQL BASICS
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
The basics of SQL |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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 |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
-- 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 |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
-- 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 |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
-- 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 |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
-- 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 | |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
/* | |
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 |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
/* | |
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 |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
--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') | |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
-- Find the smallest invoice transaction | |
SELECT MIN(amount) FROM RevenueTable | |
-- Find the largest invoice transaction | |
SELECT MAX(amount) FROM RevenueTable |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
SELECT * FROM CustomerTable |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
SELECT CustomerNo,CustomerName FROM CustomerTable |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
SELECT DISTINCT City FROM CustomerTable |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
-- 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' |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
-- Select CustomerName and CustomerNumber WHERE the city is New York | |
SELECT CustomerNumber,CustomerName FROM CustomerTable | |
WHERE city = 'New York' |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
The Basics of SQL