Think of a database as a giant digital filing cabinet. SQL is the special set of words you use to talk
to that cabinet. You use it to ask for papers, put new papers in, fix mistakes, or throw papers away.
SQL stands for Structured Query Language. It is the most common way companies store
and find their information. If a business has a list of customers, products, or sales, they are
probably using SQL.
The Core Idea: Tables
Inside the filing cabinet, data is stored in Tables. A table looks exactly like a
spreadsheet. It has rows (going across) and columns (going down).
Table Name: Customers
ID (Column)
Name (Column)
City (Column)
1
Alex
New York
2
Sam
London
3
Jordan
Tokyo
^ Each line across is called a Row or a Record.
1. Read (SELECT)
Used to look at data that is already there. It does not change anything.
2. Add (INSERT)
Used to put brand new rows into your table.
3. Fix (UPDATE)
Used to change data that is already in the table.
4. Remove (DELETE)
Used to throw away rows you no longer need.
SELECT: How to Ask for Data
To get data out of the cabinet, we use the word SELECT. We also have to tell the computer which table
to look in using the word FROM.
Code Editor
SELECT Name, City
FROM Customers;
What this means: "Please show me the Name column and the City
column from the Customers table."
If you want to see everything in the table, you use a star symbol (*).
SELECT * FROM Customers;
WHERE: Being Specific
Usually, you don't want to see every single customer. You only want to see specific ones. We use the
word WHERE to filter the results.
Code Editor
SELECT Name
FROM Customers
WHERE City = 'London';
Result: The computer will look through all the rows and only hand back the row where the city is London
(which is Sam).
INSERT: Adding New Data
When a new customer joins, we need to add them to the table. We use INSERT INTO followed by the table name, and then
VALUES to list the actual data we are adding.
Code Editor
INSERT INTO Customers (ID, Name, City)
VALUES (4, 'Taylor',
'Paris');
Always make sure the order of your words in the first brackets matches the order of your values in
the second brackets. ID gets 4, Name gets Taylor, City gets Paris.
UPDATE: Fixing Mistakes
What if Alex moves from New York to Miami? We need to change the data. We use UPDATE.
Code Editor
UPDATE Customers
SET City = 'Miami'WHERE Name = 'Alex';
Crucial Warning
If you forget the WHERE line, the computer
will change the city to Miami for every single customer in the whole table!
Always double-check your WHERE.
DELETE: Throwing Things Away
If a customer leaves and we want to remove them completely, we use DELETE.
Code Editor
DELETE FROM Customers
WHERE ID = 2;
What this means: "Look in the Customers table. Find the row
where the ID is 2 (Sam). Delete that entire row." Just like UPDATE, if you forget WHERE, it deletes
the whole table.
Sorting Your Answers
Databases don't promise to give you answers in any specific order. If you want them sorted
alphabetically, you have to ask nicely using ORDER BY.
Code Editor
SELECT Name, City
FROM Customers
ORDER BY Name ASC;
ASC means Ascending (A to Z). You can use DESC for Descending (Z to A).
Connecting Tables (JOIN)
In real life, businesses use many tables. They might have a "Customers" table and an "Orders" table. A
JOIN is how you glue two tables together to get
answers that require facts from both.
Customers Table
Has Names and IDs
Orders Table
Has Items and Customer IDs
Code Editor
SELECT Customers.Name, Orders.Item
FROM Customers
JOIN Orders
ON Customers.ID = Orders.CustomerID;
This tells the computer: "Match the records from both tables wherever the Customer ID number is exactly
the same."
Doing Math in SQL
You don't have to export data to Excel to do basic math. SQL can count rows, add numbers up, and find
the average for you instantly.
COUNT()
Counts how many rows exist.
SUM()
Adds up a column of numbers.
AVG()
Finds the average number.
SELECTCOUNT(*), SUM(Price)
FROM Orders;
Bucketing Data (GROUP BY)
What if you don't want the total of all orders, but instead you want the total sales for
each city? You use GROUP BY to put data
into buckets first.
SELECT City, COUNT(*)
FROM Customers
GROUP BY City;
Result: "Show me a list of cities, and next to each city, tell
me how many customers live there."
Filtering Buckets (HAVING)
You know WHERE filters normal rows. But what if you
only want to see the cities that have more than 5 customers? You cannot use WHERE for grouped
math. You must use HAVING.
SELECT City, COUNT(*)
FROM Customers
GROUP BY City
HAVINGCOUNT(*) > 5;
Pattern Searching (LIKE)
Sometimes you don't know the exact spelling. You just want to find anyone whose name starts with "A". We
use LIKE along with a percent sign % which acts as a wildcard (meaning "any letters can go here").
SELECT Name
FROM Customers
WHERE Name LIKE'A%';
This will find Alex, Amanda, Andrew, etc. If you used '%A', it would find names that end in A.
Fast Filters (IN & BETWEEN)
Instead of typing "City = 'London' OR City = 'Tokyo' OR City = 'Paris'", you can just give SQL a simple
list using IN. For numbers or dates, you can use
BETWEEN.
SELECT Name, Age
FROM Customers
WHERE City IN ('London', 'Tokyo')
AND Age BETWEEN 18 AND
30;
The Blank Space (NULL)
In a database, missing data is not zero, and it is not an empty text box. It is a special state called
NULL. You cannot use the equals sign (=) to look
for it. You must say IS NULL.
SELECT Name
FROM Customers
WHERE PhoneNumber IS NULL;
This query finds all customers who never gave us their phone number.
Nicknames (AS)
Sometimes database columns have ugly names like "cust_frst_nm_01". When you ask for the data, you can
rename the column in your final report to make it readable using AS.
How does a database tell the difference between two customers who are both named "John Smith"? It uses a
Primary Key. This is a unique ID badge (like a fingerprint) assigned to every single
row. No two rows can have the same Primary Key.
ID: 1045
John Smith (London)
ID: 1046
John Smith (New York)
If you want to update the New York John Smith, you don't say `WHERE Name = 'John Smith'`. You say
`WHERE ID = 1046`.
Strict Links (FOREIGN KEY)
A Foreign Key is a rule that says "You cannot put data in Table B unless it already
exists in Table A." It stops bad data from entering the database.
Example: You cannot add an Order for "Customer ID 999" into
the Orders table if Customer 999 does not exist in the Customers table. The Foreign Key blocks the
mistake.
Speed Boosts (INDEX)
If your table has 10 million rows, finding one specific customer takes a long time because the computer
reads every single row. You can add an Index to a column to make it instantly
searchable.
Think of it like the index at the back of a large book. Instead of flipping through every page to
find a topic, you go to the index, find the word, and jump straight to the correct page number.
The Golden Rules of SQL
1. Test with SELECT
Before you DELETE or UPDATE anything, run a SELECT query first to
make sure you are targeting the exact rows you want to change.
2. Never Forget WHERE
If you write UPDATE or DELETE without a WHERE rule, you will ruin
the entire table. Always double-check.
3. Readability Counts
SQL doesn't care about new lines, but humans do. Put new commands
like FROM and WHERE on their own line so it's easy to read.
More in this series
Master more skills with other tutorials from the Data Management series.