SQL Introduction

SQL, which stands for Structured Query Language, is the standard programming language used to interact with and manage relational databases.

Unlike traditional programming languages that require step-by-step instructions (procedural), SQL is declarative.

This means you simply tell the database what data you want to retrieve or alter, and the system handles how to get it done.

Popular database systems that rely on SQL include MySQL, PostgreSQL, Microsoft SQL Server, and Oracle.

How Data is Organized

Before writing queries, it helps to understand how data is stored. In a relational database, data lives in tables, which look much like spreadsheets:

• Columns (Fields): Define the type of data being stored (e.g., Customer_ID, First_Name, Email).
• Rows (Records): Individual data entries (e.g., a single customer's specific details).

The 4 Core SQL Operations (CRUD)

Most activities in SQL revolve around creating, reading, updating, and deleting data. Here are the most fundamental commands you will use:

1. Retrieve Data (SELECT)

The SELECT statement is the most common command. It pulls data out of a table.

SELECT first_name, email FROM customers WHERE city = 'New York';

Try this code ❯

Tip: You can use SELECT * FROM customers; to pull every column from a table.

2. Insert New Data (INSERT)

Use INSERT INTO to add fresh rows of data to your table.

INSERT INTO customers (first_name, email, city) VALUES ('Alex', 'alex@email.com', 'New York');

Try this code ❯

3. Update Existing Data (UPDATE)

Use UPDATE to modify records that are already in the table.

UPDATE customers SET email = 'alex_new@email.com' WHERE customer_id = 4;

Try this code ❯

Warning: Always use a WHERE clause with UPDATE. If you omit it, every single row in your table will be updated!
*

4. Delete Data (DELETE)

Use DELETE to permanently remove rows.

DELETE FROM customers WHERE customer_id = 4;

Try this code ❯

Warning: Like UPDATE, forgetting the WHERE clause will erase all data inside the table.

Key Rules for Writing SQL

To keep your queries running smoothly, keep these basic syntax rules in mind:

• Semicolons: Ending your query with a semicolon ; tells the database system that your command is complete.
• Case Insensitivity: SQL keywords like SELECT or from are not case-sensitive. However, capitalizing keywords is a universal best practice because it makes code much easier to read.
• Text Formatting: Text values inside a query must be wrapped in single quotes (e.g., 'New York'), while numeric values do not need quotes (e.g., 4).