Learn the basics of working with tables in SQL, including how to create, alter, and delete tables, and how to insert and update data. Understand how to use logic functions, perform mathematical calculations, and execute joins to manipulate and analyze your data efficiently.
Key Insights
- The content provides a walkthrough of how to work with tables in SQL, including creating a table with columns, deleting values from a table based on criteria, adding a column to a table, renaming a column, and inserting new values into a table.
- The guide demonstrates logic functions in SQL, including selecting entries based on multiple or single conditions, selecting entries that match a text pattern, and grouping entries based on column values.
- The article explains how to perform mathematical calculations in SQL, such as returning the number of values, finding the maximum and minimum values of a column, and summing or averaging the values in a column.
- The document discusses how to use joins in SQL, such as how to join two tables where values are present in both tables, or join two tables with all values in one table and only matches from another.
- The content provides practical examples of how to find the maximum revenue in one day, get total revenue by day, and generate a report of revenue by state and by day.
- The guide is a comprehensive cheat sheet for anyone looking to learn SQL or brush up on their SQL knowledge for their career in data analysis or database management.
Essential SQL Commands
-
Table Ops
CREATE TABLE, ALTER TABLE (add column), INSERT, UPDATE, DELETE.
-
Logic
BETWEEN, CASE WHEN, GROUP BY, HAVING, IS NULL, LIKE.
-
Aliases
SELECT column AS 'Alias' FROM table — rename a column in the result.
-
Data Limits
LIMIT N restricts rows returned — pair with ORDER BY for top-N.
For this cheat sheet, let’s use an example of sales reps in each state and their sales numbers each day. Our first table, the “rep” table, has each sales rep’s name, ID, and state. The second table, the daily_sales table, has every sale that was made with the corresponding sales rep ID and revenue amount for every day. First, we’ll write the syntax for each command and then we’ll walk through a couple of examples using our sales tables.
Syntax
Working with Tables
CREATE TABLE
Create a table with columns
CREATE TABLE table_name (
column_1 datatype,
column_2 datatype,
column_3 datatype);
DELETE
Delete values from a table based on given criteria
DELETE FROM table_name
WHERE column_name = xyz;
ALTER TABLE
Add a column to a table
ALTER TABLE table_name
ADD column_name datatype:
AS
Rename a column
SELECT column_name AS 'Alias'
FROM table_name;
INSERT
Insert new values into a table
INSERT INTO table_name (column_1, column_2, column_3)
VALUES (value_1, 'value_2', value_3);
LIMIT
Export a limited number of rows from your table
SELECT column_name(s)
FROM table_name
LIMIT number;
UPDATE
Update values in your table based on given criteria
UPDATE table_name
SET some_column = some_value
WHERE some_column = some_value;
LOGIC
BETWEEN
Select entries between two values
SELECT column_name(s)
FROM table_name
WHERE column_name BETWEEN value_1 AND value_2;
CASE WHEN
Set values of a column based on a set of conditions
SELECT column_name,
CASE
WHEN condition THEN 'Result_1'
WHEN condition THEN 'Result_2'
ELSE 'Result_3'
END AS new_column
FROM table_name;
GROUP BY
Group entries based on one or many column values (similar to Pivot grouping)
SELECT column_name, COUNT(*)
FROM table_name
GROUP BY column_name;
HAVING
Filter based on condition after performing a GROUP BY
SELECT column_name, COUNT(*)
FROM table_name
GROUP BY column_name
HAVING COUNT(*) > value;
IS NULL
Select entries where value is NULL
SELECT column_name(s)
FROM table_name
WHERE column_name IS NULL;
LIKE
Select entries that match a text pattern
SELECT column_name(s)
FROM table_name
WHERE column_name LIKE ‘xyz’;
AND
Select entries that meet multiple conditions
SELECT column_name
FROM table_name
WHERE column_1 = xyz
AND column_2 = abc;
OR
Select entries that meet one of multiple conditions
SELECT column_name
FROM table_name
WHERE column_name = value_1
OR column_name = value_2;
DISTINCT
Find the distinct values from a column (remove duplicates)
SELECT DISTINCT column_name
FROM table_name;
MATH
COUNT
Return the number of values
SELECT COUNT(column_name)
FROM table_name;
MAX
Return the maximum value of the column
SELECT MAX(column_name)
FROM table_name;
MIN
Return the minimum value of the column
SELECT MIN(column_name)
FROM table_name;
ORDER BY
Sort based on a column either in descending or ascending order
SELECT column_name
FROM table_name
ORDER BY column_name ASC | DESC;
ROUND
Round values in a column
SELECT ROUND(column_name, integer)
FROM table_name;
SUM
Add the values in a column
SELECT SUM(column_name)
FROM table_name;
AVG
Find the average of the values in a column
SELECT AVG(column_name)
FROM table_name;
JOINS
INNER JOIN
Join two tables only where values are present in both tables
SELECT column_name(s)
FROM table_1
JOIN table_2
ON table_1.column_name = table_2.column_name;
OUTER JOIN
Join two tables with all values in one table and only the matches from another
SELECT column_name(s)
FROM table_1
LEFT JOIN table_2
ON table_1.column_name = table_2.column_name;
Applications
Find the Name of Rep 555
SELECT name
FROM rep
WHERE ID = ‘555’;
Find the Maximum Revenue in One Day
SELECT MAX(revenue)
FROM daily_sales;
Get Total Revenue for by Day
SELECT date, SUM(revenue)
FROM daily_sales
GROUP BY date
ORDER BY date;
Revenue by State and by Day
SELECT a.state, b.date, sum(revenue)
FROM rep a
JOIN daily_sales b
ON a. ID = b. ID
GROUP BY state, date;
