Python
  • Understand the purpose and structure of SQL
  • Learn basic SQL commands: SELECT, INSERT, UPDATE, DELETE
  • Comprehend table relationships and keys
  • Apply filtering and sorting with WHERE and ORDER BY

SQL Basics

What is SQL?

SQL (Structured Query Language) is the standard language for communicating with relational databases. Think of it as the universal translator that allows you to:

  • Ask questions about your data
  • Add new information
  • Modify existing data
  • Remove outdated information

Basic SQL Commands

SELECT: Retrieving Data

The SELECT command is like asking "Show me..." It's the most commonly used SQL command.

SELECT name, age FROM users;

This is like saying "Show me the name and age of everyone in the users table."

INSERT: Adding Data

INSERT is like adding a new book to your library catalog.

INSERT INTO users (name, age, email) VALUES ('John Doe', 30, 'john@example.com');

UPDATE: Modifying Data

UPDATE allows you to change existing information, like updating a book's publication date.

UPDATE users SET age = 31 WHERE name = 'John Doe';

DELETE: Removing Data

DELETE removes data, but use it carefully - it's like permanently removing a book from the library.

DELETE FROM users WHERE age < 18;

Understanding Tables and Relationships

Primary Keys

A primary key is like a book's ISBN - it uniquely identifies each record in a table.

Foreign Keys

Foreign keys create relationships between tables, like linking a book to its author.

WHERE Clause: Filtering Data

The WHERE clause acts like a filter, helping you find specific information.

SELECT * FROM books WHERE author = 'Jane Austen';

This finds all books by Jane Austen.

ORDER BY: Sorting Results

ORDER BY organizes your results, like arranging books alphabetically by title.

SELECT * FROM books ORDER BY title ASC;

Building a Blog Database Example

Imagine you're managing a bookstore database. You might use SQL to:

  1. Find all books published in 2023
  2. List customers who haven't purchased in the last year
  3. Calculate total sales for each author
  4. Update inventory when new books arrive

SQL gives you the power to ask complex questions about your data and get precise answers.

Best Practices

  • Always backup your data before making changes
  • Use meaningful table and column names
  • Test your queries on small datasets first
  • Comment your complex queries for future reference

In the next lesson, we'll see how to use SQL with Python's SQLite database.