Relational Databases and SQL Basics

This lesson introduces you to relational databases, the backbone of modern data management, and the Structured Query Language (SQL) used to interact with them. You'll learn fundamental SQL commands, enabling you to retrieve, manipulate, and modify data within a database.

Learning Objectives

  • Define what a relational database is and its key components.
  • Identify and explain the purpose of the basic SQL commands: `SELECT`, `FROM`, `WHERE`, `INSERT`, `UPDATE`, and `DELETE`.
  • Write simple SQL queries to retrieve data from a database.
  • Understand how to modify data using SQL commands.

Text-to-Speech

Listen to the lesson content

Lesson Content

Introduction to Relational Databases

A relational database organizes data into tables, similar to spreadsheets. Each table consists of rows (records) and columns (fields). These tables are related to each other based on shared data. This structure allows for efficient data storage, retrieval, and manipulation. Think of a library: it might have tables for 'Books', 'Authors', and 'Borrowers', with relationships defined (e.g., a book 'belongs to' an author). Key components include:

  • Tables: Collections of related data.
  • Columns: Attributes or fields within a table (e.g., 'Book Title', 'Author Name').
  • Rows: Individual records within a table (e.g., a single book entry).
  • Primary Key: A unique identifier for each row in a table (e.g., 'Book ID').
  • Foreign Key: A column in a table that references the primary key of another table, establishing a relationship (e.g., 'Author ID' in the 'Books' table referencing 'Author ID' in the 'Authors' table).

Example Database Schema (Simplified):

  • Customers Table: CustomerID (Primary Key), FirstName, LastName, Email
  • Orders Table: OrderID (Primary Key), CustomerID (Foreign Key), OrderDate, TotalAmount

Introduction to SQL: The Language of Databases

SQL (Structured Query Language) is the standard language for communicating with relational databases. It allows you to perform operations like:

  • Querying Data (SELECT): Retrieve data from one or more tables.
  • Inserting Data (INSERT): Add new data into a table.
  • Updating Data (UPDATE): Modify existing data in a table.
  • Deleting Data (DELETE): Remove data from a table.

Let's break down the basic commands with examples using our 'Customers' table and 'Orders' table.

SELECT Command: Retrieving Data

The SELECT command is used to retrieve data from one or more tables. The basic syntax is:

SELECT column1, column2, ...
FROM table_name;
  • column1, column2, ...: Specifies the columns you want to retrieve. Use * to select all columns.
  • FROM table_name: Specifies the table you're retrieving data from.

Examples:

  • Retrieve all columns and rows from the 'Customers' table:
    sql SELECT * FROM Customers;
  • Retrieve only the 'FirstName' and 'Email' columns from the 'Customers' table:
    sql SELECT FirstName, Email FROM Customers;
  • Retrieve all columns from the Orders table:
    sql SELECT * FROM Orders;

WHERE Clause: Filtering Data

The WHERE clause is used to filter the results of a query based on a specific condition. The basic syntax is:

SELECT column1, column2, ...
FROM table_name
WHERE condition;
  • condition: Specifies the filtering criteria. This can include comparisons (e.g., =, >, <), logical operators (e.g., AND, OR, NOT), and other operators.

Examples:

  • Retrieve customers with the 'LastName' Smith:
    sql SELECT * FROM Customers WHERE LastName = 'Smith';
  • Retrieve orders placed on or after a specific date:
    sql SELECT * FROM Orders WHERE OrderDate >= '2023-10-01'; -- Assuming the date format is YYYY-MM-DD
  • Retrieve customers with a specific email
    sql SELECT * FROM Customers WHERE Email = 'example@example.com';

INSERT Command: Adding Data

The INSERT command is used to add new data into a table. The basic syntax is:

INSERT INTO table_name (column1, column2, ...)
VALUES (value1, value2, ...);
  • table_name: Specifies the table you're inserting data into.
  • (column1, column2, ...): Specifies the columns you're providing values for. If you provide values for all columns in the correct order, you can omit this part.
  • VALUES (value1, value2, ...): Specifies the values to insert.

Examples:

  • Insert a new customer into the 'Customers' table:
    sql INSERT INTO Customers (FirstName, LastName, Email) VALUES ('Alice', 'Johnson', 'alice.johnson@example.com');
  • Insert a new order into the Orders table:
    sql INSERT INTO Orders (CustomerID, OrderDate, TotalAmount) VALUES (123, '2024-01-15', 50.00); -- Assuming CustomerID 123 exists

UPDATE Command: Modifying Data

The UPDATE command is used to modify existing data in a table. The basic syntax is:

UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;
  • table_name: Specifies the table you're updating.
  • SET column1 = value1, column2 = value2, ...: Specifies the columns to update and their new values.
  • WHERE condition: Specifies which rows to update. If you omit the WHERE clause, all rows in the table will be updated!

Examples:

  • Update the email address for a customer:
    sql UPDATE Customers SET Email = 'new.email@example.com' WHERE CustomerID = 1;
  • Update the price of the order in the Orders table:
    sql UPDATE Orders SET TotalAmount = 75.00 WHERE OrderID = 456;

DELETE Command: Removing Data

The DELETE command is used to remove data from a table. The basic syntax is:

DELETE FROM table_name
WHERE condition;
  • table_name: Specifies the table you're deleting data from.
  • WHERE condition: Specifies which rows to delete. Important: If you omit the WHERE clause, all rows in the table will be deleted!

Examples:

  • Delete a customer from the 'Customers' table:
    sql DELETE FROM Customers WHERE CustomerID = 2;
  • Delete an order from the Orders table:
    sql DELETE FROM Orders WHERE OrderID = 789;
Progress
0%