SQL Syntax: The Grammar of Databases

SQL Statements: Building Blocks of Database Communication

SQL Statements are complete instructions you give to the database. Each statement tells the database exactly what you want to do. Think of them as complete sentences in the database language.

Basic Structure of SQL Statements:

-- General format:
KEYWORD(s) what_to_do WITH what_data FROM where CONDITIONS;

-- Real example:
SELECT product_name, price 
FROM products 
WHERE category = 'Electronics' 
  AND price < 1000;

Common SQL Statement Categories:

Simple Statement Examples:

-- Creating a table (DDL)
CREATE TABLE employees (
    id INT,
    name VARCHAR(50),
    department VARCHAR(30)
);

-- Retrieving data (DML)
SELECT name, salary FROM employees WHERE department = 'Sales';

-- Inserting data (DML)
INSERT INTO employees VALUES (101, 'Sarah Chen', 'Marketing', 75000);

-- Updating data (DML)
UPDATE employees SET salary = 80000 WHERE id = 101;

Database Tables: The Heart of Relational Databases

What are Tables?

Tables are structured containers that hold your data in rows and columns, similar to a spreadsheet.

Analogy: If a database is a filing cabinet, then:

• Each drawer is a database
• Each folder is a table
• Each paper in the folder is a row
• Each field on the paper (name, date, amount) is a column

Table Structure Visualization:

-- A 'students' table might look like this:
+------------+------------+--------+----------+-------+
| student_id | first_name | last_name | major     | gpa  |
+------------+------------+-----------+-----------+------+
| 1001       | Maria      | Garcia    | Computer Science | 3.8 |
| 1002       | James      | Wilson    | Biology         | 3.5 |
| 1003       | Priya      | Patel     | Mathematics     | 3.9 |
+------------+------------+-----------+-----------+------+

-- Columns: student_id, first_name, last_name, major, gpa
-- Rows: Each student's complete record

Creating a Simple Table:

CREATE TABLE customers (
    -- Column definitions:
    customer_id INT PRIMARY KEY,          -- Unique identifier
    first_name VARCHAR(50) NOT NULL,      -- Text, max 50 chars, required
    last_name VARCHAR(50) NOT NULL,
    email VARCHAR(100) UNIQUE,            -- Must be unique
    signup_date DATE DEFAULT CURRENT_DATE,-- Default to today
    is_active BOOLEAN DEFAULT true,
    total_purchases DECIMAL(10,2)         -- Decimal number: 10 total digits, 2 after decimal
);

Common Data Types for Table Columns:

Semicolon after SQL Statements?

The Semicolon Rule:

A semicolon (;) separates multiple SQL statements in the same call or script. It's like a period at the end of a sentence.

When is it Required?

-- When executing MULTIPLE statements together:
CREATE TABLE users (id INT, name VARCHAR(50));
INSERT INTO users VALUES (1, 'Alice');
INSERT INTO users VALUES (2, 'Bob');
SELECT * FROM users;

-- Without semicolons, the database wouldn't know where one statement ends
-- and the next begins!

When is it Optional?

-- When executing a SINGLE statement (in most systems):
SELECT * FROM products  -- No semicolon needed if this is the only command

Best Practice:

Always use semicolons - it makes your SQL more readable, portable, and prevents errors.

Good:

UPDATE inventory SET quantity = quantity - 1 WHERE product_id = 5;
SELECT product_name FROM products WHERE product_id = 5;

Confusing (but might work):

UPDATE inventory SET quantity = quantity - 1 WHERE product_id = 5
SELECT product_name FROM products WHERE product_id = 5
-- Some systems might execute both, others might error

Some of The Most Important SQL Commands

Here are the essential SQL commands you'll use 90% of the time:

1. SELECT - Retrieve Data

-- Basic form: SELECT what FROM where
SELECT * FROM customers;                          -- Get ALL columns from customers
SELECT first_name, email FROM customers;          -- Get specific columns
SELECT product_name AS item, price AS cost FROM products;  -- Rename columns

2. INSERT - Add New Data

-- Insert complete row
INSERT INTO employees VALUES (101, 'John', 'Doe', 'Sales', 50000);

-- Insert specific columns only
INSERT INTO employees (employee_id, first_name, department) 
VALUES (102, 'Sarah', 'Marketing');

-- Insert multiple rows at once
INSERT INTO students (student_id, name, grade) 
VALUES 
    (201, 'Alice Johnson', 'A'),
    (202, 'Bob Smith', 'B'),
    (203, 'Charlie Brown', 'A');

3. UPDATE - Modify Existing Data

-- Update specific records
UPDATE products 
SET price = 29.99, 
    stock = stock - 1 
WHERE product_id = 15;

-- Update ALL records (use with caution!)
UPDATE users SET last_login = CURRENT_DATE;  -- Updates EVERY user!

4. DELETE - Remove Data

-- Delete specific records
DELETE FROM orders WHERE order_date < '2023-01-01';

-- Delete ALL records (DANGER!)
DELETE FROM temp_log;  -- Empties the entire table!

5. CREATE - Make New Database Objects

-- Create a database
CREATE DATABASE company_db;

-- Create a table
CREATE TABLE departments (
    dept_id INT PRIMARY KEY,
    dept_name VARCHAR(50) NOT NULL,
    manager_id INT,
    budget DECIMAL(12,2)
);

-- Create a view (virtual table)
CREATE VIEW active_customers AS
SELECT customer_id, first_name, last_name, email
FROM customers 
WHERE is_active = true;

6. ALTER - Modify Structure

-- Add a new column
ALTER TABLE employees ADD hire_date DATE;

-- Modify a column
ALTER TABLE employees MODIFY COLUMN salary DECIMAL(10,2);

-- Drop a column
ALTER TABLE employees DROP COLUMN middle_initial;

-- Add a constraint
ALTER TABLE orders ADD CONSTRAINT fk_customer 
FOREIGN KEY (customer_id) REFERENCES customers(customer_id);

7. DROP - Remove Objects

-- Delete a table (and all its data!)
DROP TABLE old_logs;

-- Delete a database (VERY DANGEROUS)
DROP DATABASE test_db;  -- Everything gone forever!

8. WHERE - Filter Results

-- Basic filtering
SELECT * FROM products WHERE price > 50;

-- Multiple conditions
SELECT * FROM employees 
WHERE department = 'Sales' 
  AND salary > 60000 
  AND hire_date > '2022-01-01';

-- Using OR
SELECT * FROM customers 
WHERE country = 'USA' OR country = 'Canada';

-- Pattern matching with LIKE
SELECT * FROM products WHERE name LIKE '%laptop%';  -- Contains "laptop"
SELECT * FROM customers WHERE email LIKE '%@gmail.com';  -- Ends with "@gmail.com"

9. ORDER BY - Sort Results

-- Single column sort
SELECT * FROM products ORDER BY price;          -- Ascending (default)
SELECT * FROM products ORDER BY price DESC;     -- Descending

-- Multiple column sort
SELECT * FROM employees 
ORDER BY department ASC, salary DESC;  -- Sort by department A-Z, then highest salary first

10. JOIN - Combine Tables

-- Basic INNER JOIN
SELECT orders.order_id, customers.name, orders.order_date
FROM orders
JOIN customers ON orders.customer_id = customers.customer_id;

-- LEFT JOIN (all from left table, matching from right)
SELECT employees.name, departments.dept_name
FROM employees
LEFT JOIN departments ON employees.dept_id = departments.dept_id;

Complete Working Example: Library System

-- Create database and tables
CREATE DATABASE library;
USE library;

CREATE TABLE books (
    book_id INT PRIMARY KEY,
    title VARCHAR(200) NOT NULL,
    author VARCHAR(100),
    year_published INT,
    is_available BOOLEAN DEFAULT true
);

CREATE TABLE members (
    member_id INT PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    join_date DATE DEFAULT CURRENT_DATE
);

CREATE TABLE loans (
    loan_id INT PRIMARY KEY,
    book_id INT,
    member_id INT,
    loan_date DATE,
    return_date DATE,
    FOREIGN KEY (book_id) REFERENCES books(book_id),
    FOREIGN KEY (member_id) REFERENCES members(member_id)
);

-- Insert sample data
INSERT INTO books VALUES 
(1, 'The Great Gatsby', 'F. Scott Fitzgerald', 1925, true),
(2, 'To Kill a Mockingbird', 'Harper Lee', 1960, false),
(3, '1984', 'George Orwell', 1949, true);

INSERT INTO members VALUES 
(101, 'Alice', 'Johnson', '2023-05-10'),
(102, 'Bob', 'Smith', '2023-06-15');

INSERT INTO loans VALUES 
(1001, 2, 101, '2024-01-10', NULL);  -- Book not returned yet

-- Query: Find available books
SELECT title, author, year_published 
FROM books 
WHERE is_available = true 
ORDER BY year_published DESC;

-- Query: See who borrowed which book
SELECT 
    m.first_name, 
    m.last_name, 
    b.title, 
    l.loan_date
FROM loans l
JOIN members m ON l.member_id = m.member_id
JOIN books b ON l.book_id = b.book_id
WHERE l.return_date IS NULL;  -- Still borrowed

Quick Syntax Reference Card

-- SELECT: Get data
SELECT column1, column2 FROM table WHERE condition;

-- INSERT: Add data  
INSERT INTO table (col1, col2) VALUES (val1, val2);

-- UPDATE: Change data
UPDATE table SET column = value WHERE condition;

-- DELETE: Remove data
DELETE FROM table WHERE condition;

-- CREATE: Make objects
CREATE TABLE name (column1 TYPE, column2 TYPE);

-- ALTER: Change structure
ALTER TABLE table ADD column TYPE;

-- DROP: Remove objects
DROP TABLE table_name;

Common Syntax Pitfalls to Avoid

❌ Missing commas between columns

-- WRONG:
SELECT first_name last_name FROM users;

-- CORRECT:
SELECT first_name, last_name FROM users;

❌ Using = for text with wildcards

-- WRONG:
SELECT * FROM products WHERE name = '%laptop%';

-- CORRECT:
SELECT * FROM products WHERE name LIKE '%laptop%';

❌ Forgetting quotes around text values

-- WRONG:
UPDATE users SET status = active WHERE id = 1;

-- CORRECT:
UPDATE users SET status = 'active' WHERE id = 1;

❌ Confusing single quotes (') with backticks (`)

-- WRONG:
SELECT * FROM `users` WHERE name = `John`;

-- CORRECT:
SELECT * FROM `users` WHERE name = 'John';  -- Backticks for identifiers, single quotes for values

Practice Tip: SQL syntax is best learned by doing. Start with simple SELECT statements, then gradually add WHERE clauses, then JOINs. Remember: every SQL statement answers a question or performs an action on your data!