How to Talk to Databases Using SQL.

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.

SELECT COUNT(*), 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 HAVING COUNT(*) > 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.

SELECT cust_frst_nm_01 AS "First Name" FROM Customers;

The ID Badge (PRIMARY KEY)

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.