SQL

What is SQL?

SQL is a simple language for talking to databases. It lets you ask questions, add new information, change existing information, or remove information from a database. Think of it like giving instructions to a librarian who knows exactly where everything is stored.

Simple Analogy

Think of a database as a digital library, and SQL as the language you use to talk to the librarian:

• Database: A digital library with many organized sections
• Tables: Different sections of the library (like fiction, non-fiction, children’s books)
• Rows: Individual books or records
• Columns: Information about each book (like title, author, year published)
• SQL Commands: Questions or instructions you give to the librarian

When you need information, instead of searching through the entire library yourself, you ask the librarian (using SQL):

• “Please find all books by Stephen King” becomes:

SELECT * FROM Books WHERE author = 'Stephen King';

• “Find all mystery books published after 2010” becomes:

SELECT title, author FROM Books 
WHERE genre = 'Mystery' AND year_published > 2010;

Key Concepts

• Tables: Where data is stored in rows and columns
• Queries: Questions you ask to get specific information
• Data Types: Different kinds of information (text, numbers, dates)
• Primary Keys: Unique identifiers for each row, like a book’s ISBN or a person’s social security number
• Foreign Keys: Connections between different tables, a book might have foreign keys to both authors and publishers
• Joins: Combining information from multiple tables, based on keys

Example

-- Create a table for storing books
CREATE TABLE Books (
  id INT PRIMARY KEY,
  title VARCHAR(100) NOT NULL,
  author VARCHAR(100) NOT NULL,
  year_published INT
);

-- Add a new book to the table
INSERT INTO Books (id, title, author, year_published) 
VALUES (1, 'The Shining', 'Stephen King', 1977);

-- Find all books by a specific author
SELECT title, year_published 
FROM Books 
WHERE author = 'Stephen King';