No history yet

Introduction to SQL

What is SQL?

Think of a massive, well-organized library. This library holds all of your data: survey results, economic indicators, customer information, you name it. Now, how do you find a specific piece of information or add a new book to the right shelf? You need a language to communicate with the librarian. In the world of data, that language is SQL.

SQL (Structured Query Language) is a standard programming language used for managing and manipulating relational databases.

SQL, often pronounced "sequel," lets you talk to databases. It's not a general-purpose programming language like Python or R. You can't build a website with it. Its job is very specific: managing data stored in a relational database. A relational database organizes data into tables, similar to spreadsheets, with rows and columns. These tables can be linked, or related, to each other.

The Basic Commands

SQL syntax is designed to be readable, almost like plain English. Most operations revolve around a few key commands. Let's create a simple table to track different countries' GDP data.

-- This is a comment. Comments start with two dashes.
-- CREATE TABLE tells the database to make a new table.
CREATE TABLE Economics (
    Country VARCHAR(255),
    Year INT,
    GDP_Billions DECIMAL(10, 2)
);

In this snippet, we created a table named Economics. It has three columns:

  • Country: To hold text, like "United States". VARCHAR(255) means it can store a variable string of up to 255 characters.
  • Year: To hold an integer, like 2023.
  • GDP_Billions: To hold a decimal number, like 26854.60.

Now that we have an empty table, let's add some data to it. We use the INSERT INTO command.

INSERT INTO Economics (Country, Year, GDP_Billions)
VALUES
('United States', 2023, 26854.60),
('China', 2023, 19373.59),
('Japan', 2023, 4409.74);

Querying Your Data

Storing data is only useful if you can get it back out. This is where SQL truly shines. The workhorse command for fetching data is SELECT.

SELECT retrieves data. FROM specifies the table. WHERE filters the results.

To see everything in our table, we can use an asterisk (*) which is shorthand for "all columns."

SELECT * FROM Economics;

This query returns the entire Economics table. But what if we only want data for a specific year or country? We add a WHERE clause to filter the results. Let's find the GDP for China.

SELECT Country, GDP_Billions
FROM Economics
WHERE Country = 'China';

We can also sort the output using ORDER BY. Let's list all countries, but this time from highest GDP to lowest.

SELECT Country, GDP_Billions
FROM Economics
ORDER BY GDP_Billions DESC;

The DESC keyword stands for descending. For ascending order, you can use ASC or just omit it, as ascending is the default.

Managing Data

Data is rarely static. You'll often need to correct or update records. The UPDATE command modifies existing rows.

Imagine the GDP figure for Japan was revised. We can update it like this:

UPDATE Economics
SET GDP_Billions = 4425.50
WHERE Country = 'Japan' AND Year = 2023;

Notice the WHERE clause. It's crucial. Without it, you would update the GDP_Billions for every single row in the table. Always be careful with UPDATE.

Sometimes, you need to remove data. The DELETE command does exactly that.

DELETE FROM Economics
WHERE Country = 'Japan';

Just like UPDATE, the WHERE clause here is your safety net. If you forget it, you'll delete all the data in your table.

Now that you've got the basics, let's practice.

Quiz Questions 1/6

What is the primary purpose of SQL?

Quiz Questions 2/6

Which SQL command is used to retrieve data from a database?

With these fundamental commands, you can perform the majority of tasks needed for data analysis. You can create a home for your data, add to it, modify it, and ask it questions. As you get more comfortable, you'll find SQL is a powerful tool for working with datasets of any size.