Introduction to SQL

Talking to Databases

Imagine a library filled with filing cabinets. Each cabinet is labeled, each drawer holds specific files, and every file is neatly organized. A relational database is a lot like that, but for digital information. It stores data in tables, which are like spreadsheets with rows and columns.

SQL

noun

Stands for Structured Query Language. It's the standard language used to communicate with and manage data in relational databases.

Instead of manually opening drawers, you use a special language to ask for exactly what you need. That language is SQL (often pronounced "sequel"). It lets you ask the database to find, add, change, or remove information. Think of it as the librarian who can instantly fetch any piece of information you request.

Relational databases are powerful because they can link tables together. For instance, one table might hold customer information, and another might hold their orders. By linking them, you can easily see all the orders placed by a specific customer.

In the example above, the CustomerID connects the two tables. This link allows you to ask questions like, "What products did Bob buy?" without having to manually sift through files. SQL is the tool you use to ask that question.

The Four Core Commands

Most of what you'll do in SQL boils down to four basic operations. These are often called CRUD operations: Create, Read, Update, and Delete. In SQL, they have slightly different names: INSERT, SELECT, UPDATE, and DELETE.

SELECT: Retrieves data from a database. This is how you "read" information. INSERT: Adds new data into a database. This is how you "create" a new record. UPDATE: Modifies existing data in a database. DELETE: Removes data from a database.

Let's look at how each one works.

Reading and Adding Data

The SELECT command is the most common. You use it to fetch data from one or more tables. The basic syntax is SELECT followed by the columns you want, and FROM followed by the table you want them from. An asterisk (*) is a wildcard that means "all columns."

-- This gets every column from the Customers table.
SELECT * FROM Customers;

If you only want specific columns, you list them by name. You can also add a WHERE clause to filter the results and only get the rows that match a certain condition.

-- This gets the Name and City for customers from Canada.
SELECT Name, City 
FROM Customers 
WHERE Country = 'Canada';

To add a new row of data, you use the INSERT INTO command. You specify the table, the columns you're adding data to, and then the VALUES for those columns. The order of the values must match the order of the columns.

-- This adds a new customer to the Customers table.
INSERT INTO Customers (Name, City, Country)
VALUES ('New Customer Inc.', 'Toronto', 'Canada');

Changing and Removing Data

Sometimes you need to change data that's already in the database. The UPDATE command lets you do this. You specify the table to UPDATE, SET the new value for a column, and—this is very important—use a WHERE clause to specify which row to change.

Warning: If you forget the WHERE clause in an UPDATE statement, you will update every single row in the table. Always double-check your WHERE clause!

-- Changes the city for the customer named 'New Customer Inc.'
UPDATE Customers
SET City = 'Montreal'
WHERE Name = 'New Customer Inc.';

Finally, to remove data, you use the DELETE command. Just like with UPDATE, the WHERE clause is critical. It tells the database which specific row (or rows) to remove.

-- Deletes the customer named 'New Customer Inc.'
DELETE FROM Customers
WHERE Name = 'New Customer Inc.';

Forgetting the WHERE clause here would delete all the data from your table. These four commands are the foundation of working with data in SQL. Mastering them is the first step to managing databases effectively.

Quiz Questions 1/5

What is the main purpose of a relational database, as described by the filing cabinet analogy?

Quiz Questions 2/5

Which SQL command is used to retrieve data from one or more tables?

With these commands, you can perform the most essential tasks required for database management. They are the building blocks for more complex queries and operations you'll learn later.

SQL Stored Procedures Explained