Welcome to Structured Query Language (SQL)!

Hello future data scientists! This chapter is all about SQL (pronounced "sequel"), which stands for Structured Query Language. Don't worry if the name sounds complex—SQL is simply the universal language used to talk to databases.

Think of a database like a giant library, and SQL is the set of instructions (or questions) you use to find, organize, or update the books inside. If you want a career working with data (which is almost every modern job!), understanding SQL is essential!

Quick Review: Relational Databases

Before we dive into SQL, remember that relational databases organize data into tables.

  • A Table is like a spreadsheet (e.g., Students, Books).
  • A Record (or Row) is one complete entry (e.g., all the information about one specific student).
  • A Field (or Column) is a category of data (e.g., StudentName, DateOfBirth).

Section 1: What is SQL and Its Purpose?

The Role of SQL

SQL is a declarative language, meaning you tell the database what you want, and the database figures out how to get it. It is used to manage and manipulate data stored in a Relational Database Management System (RDBMS).

SQL is generally split into two main types of commands:

1. Data Definition Language (DDL)

DDL commands are used to define the database structure itself—creating, altering, or destroying tables and databases.

Memory Aid: Define the Data Layout.

2. Data Manipulation Language (DML)

DML commands are used to manipulate the data inside the established structure—adding, deleting, modifying, or retrieving records. This is where querying comes in!

Memory Aid: Manage and Move the Live data.

Key Takeaway (DDL vs DML): DDL builds the empty box (the structure), and DML puts things (the data) inside the box and works with them.

Section 2: Data Definition Language (DDL) Commands

These commands are typically run only once to set up the environment.

1. Creating a New Table: CREATE TABLE

This command establishes a new table, including defining the field names and their data types (e.g., Text, Integer, Date).

Analogy: You are drawing the blueprint for a new filing cabinet.

Syntax Example: Creating a 'Books' table
CREATE TABLE Books (
    BookID INTEGER,
    Title TEXT,
    Author TEXT,
    PublicationYear INTEGER
);

Key point: Each field must have a specific data type defined (e.g., INTEGER for whole numbers, TEXT for names/words).

2. Deleting an Entire Table: DROP TABLE

This command permanently removes an entire table and all the data within it from the database. Be careful!

Syntax Example:
DROP TABLE Books;

Did you know? In many database systems, once a table is DROPped, it is gone forever unless you have a backup!

Section 3: Data Manipulation Language (DML) Commands

This is the most common use of SQL—asking questions and changing the data.

1. Retrieving Data: SELECT and FROM (The Core Query)

Every time you want to look at data, you use the SELECT and FROM clauses.

  • SELECT specifies which fields (columns) you want to see.
  • FROM specifies which table the data is coming from.
Step-by-Step Example 1: Show all fields

If you want to see everything in the Students table: 

SELECT *
FROM Students;

The asterisk (*) is a wildcard, meaning "show me all columns."

Step-by-Step Example 2: Show specific fields

If you only want the student's name and email: 

SELECT StudentName, Email
FROM Students;

2. Filtering Data: The WHERE Clause

The WHERE clause is how you filter the results to only include records that meet a specific condition. This is what makes querying powerful!

Common Comparison Operators:
  • = (Equals)
  • > (Greater than)
  • < (Less than)
  • >= (Greater than or equals)
  • <= (Less than or equals)
  • <> or != (Not equals)
Example: Find students with an ID greater than 50
SELECT StudentName, StudentID
FROM Students
WHERE StudentID > 50;
Filtering Text Data:

When filtering text data (like names or addresses), the value must usually be wrapped in single quotes ('). 

SELECT *
FROM Students
WHERE Grade = 'A';

3. Combining Conditions: AND, OR, and NOT

You can use logical operators to combine multiple conditions in the WHERE clause.

  • AND: Both conditions must be true.
  • OR: At least one of the conditions must be true.
  • NOT: Reverses the meaning (e.g., NOT Grade = 'A' means any grade that is not A).
Example using AND: Find students who got an 'A' AND live in London
SELECT StudentName
FROM Students
WHERE Grade = 'A' AND City = 'London';

4. Sorting Results: ORDER BY

The ORDER BY clause is used to sort the final results of your query. This clause always comes at the very end of the SQL statement.

  • ASC (Ascending): Lowest to Highest (A-Z, 1-10). This is the default.
  • DESC (Descending): Highest to Lowest (Z-A, 10-1).
Example: Show all students sorted by their name (A to Z)
SELECT *
FROM Students
ORDER BY StudentName ASC;
Quick Tip for Query Structure:
1. SELECT (What columns do I need?)
2. FROM (Where is the data stored?)
3. WHERE (Do I need to filter the rows?)
4. ORDER BY (Do I need to sort the final list?)

Section 4: Data Modification (DML)

1. Adding New Data: INSERT INTO

This command adds a new record (a new row) into an existing table. You must provide values for the fields.

Syntax Example: Adding a new book record
INSERT INTO Books (BookID, Title, Author)
VALUES (101, 'The Great Adventure', 'A. N. Author');

Step 1: List the table name and the columns you are filling in parentheses.
Step 2: Use VALUES and list the corresponding data for those columns in the same order.

2. Changing Existing Data: UPDATE and SET

The UPDATE command is used to change data in one or more existing records.

  • UPDATE specifies the table.
  • SET specifies the column(s) to change and the new value.
  • WHERE is absolutely essential to specify which records to change.

Common Mistake Alert! If you forget the WHERE clause, the UPDATE command will change every single record in the table. Always check your WHERE clause!

Example: Change the grade of Student ID 42 to 'A+'
UPDATE Students
SET Grade = 'A+'
WHERE StudentID = 42;

3. Removing Specific Data: DELETE FROM

This command removes entire records (rows) from a table.

Example: Remove all students from the 'London' City
DELETE FROM Students
WHERE City = 'London';

Warning: Just like UPDATE, if you use DELETE FROM Students without a WHERE clause, you will delete all records in the table, leaving the table structure empty but intact.

Key Takeaway (DML): DML allows you to query (SELECT), insert (INSERT), modify (UPDATE), and remove (DELETE) data. The WHERE clause is your most powerful tool for specificity and safety!

Summary and Next Steps

You have now mastered the basics of SQL! Remember that SQL is the language that makes the digital world go round—allowing applications and users to interact efficiently with massive amounts of data.

Focus on practicing the structure of the SELECT... FROM... WHERE query, as this is the foundation for almost everything you will do in databases! Good luck!