SQL Program to Demonstrate Creating Your First Database - Building Your Data Home
Learn SQL step by step.
All about SQL - Creating Your First Database - Building Your Data Home
Jan 30, 2026
## Chapter 2: Creating Your First Database - Building Your Data Home
**Creating a database is like building a house for your data - everything needs a proper place!**
**Think of it as organizing your kitchen - plates go here, spoons go there, everything has its spot!**
### Real-World Analogy - The Restaurant Kitchen
```
Imagine you're opening a restaurant called "Apna Dhaba":
You need different sections:
- Spice storage = Your database
- Vegetable rack = One table
- Spice boxes = Another table
- Customer register = Third table
Everything organized so you can find things quickly!
```
### Creating Your Database - The Foundation
**Step 1: Create the Database (Build Your Kitchen)**
```sql
CREATE DATABASE apna_dhaba;
```
**What this means:** "I'm building a new kitchen called 'apna_dhaba'"
**Step 2: Start Using It (Enter Your Kitchen)**
```sql
USE apna_dhaba;
```
**What this means:** "Okay, now I'm working inside my kitchen"
### Creating Tables - Your Storage Shelves
**Customer Table - Your Guest Register**
```sql
CREATE TABLE customers (
customer_id INT PRIMARY KEY AUTO_INCREMENT,
first_name VARCHAR(50) NOT NULL,
last_name VARCHAR(50) NOT NULL,
phone VARCHAR(15),
email VARCHAR(100),
city VARCHAR(50),
created_date DATE
);
```
**Let's break this down like explaining to your mother:**
```
Customer Register Explanation:
βββββββββββββββββββββββββββββββββββββββββββ
β CUSTOMERS TABLE - Your Guest Book β
βββββββββββββββββββββββββββββββββββββββββββ€
β β
β customer_id = Guest number (auto) β
β first_name = Guest's first name β
β last_name = Guest's last name β
β phone = Mobile number β
β email = Email address β
β city = Which city they're from β
β created_date = When they first came β
β β
βββββββββββββββββββββββββββββββββββββββββββ
```
### Data Types - Your Storage Containers
**Think of these as different types of containers in your kitchen:**
| SQL Data Type | Real Kitchen Item | What It Holds | Example |
|---------------|-------------------|---------------|---------|
| **INT** | Numbered containers | Whole numbers | 1, 2, 150, 999 |
| **VARCHAR(50)** | Medium-sized boxes | Text up to 50 letters | "Ramesh", "Priyanka" |
| **DATE** | Calendar on wall | Dates | 2024-01-15 |
| **DECIMAL(10,2)** | Price tags | Money amounts | 250.50 |
| **BOOLEAN** | On/Off switch | True or False | Yes/No |
### Creating Menu Table - Your Food Catalog
```sql
CREATE TABLE menu_items (
item_id INT PRIMARY KEY AUTO_INCREMENT,
item_name VARCHAR(100) NOT NULL,
category VARCHAR(50) NOT NULL,
price DECIMAL(10,2) NOT NULL,
description TEXT,
is_available BOOLEAN DEFAULT TRUE,
spice_level VARCHAR(20)
);
```
**Real-World Explanation:**
```
Menu Book Explanation:
βββββββββββββββββββββββββββββββββββββββββββ
β MENU_ITEMS TABLE - Your Food Catalog β
βββββββββββββββββββββββββββββββββββββββββββ€
β β
β item_id = Dish number (auto) β
β item_name = "Butter Chicken" β
β category = "Main Course" β
β price = βΉ250.00 β
β description = "Creamy tomato gravy" β
β is_available = Yes/No β
β spice_level = "Medium" β
β β
βββββββββββββββββββββββββββββββββββββββββββ
```
### Visual Example - Your Database Kitchen
```
βββββββββββββββββββββββββββββββββββββββββββ
β Apna Dhaba Database Kitchen β
βββββββββββββββββββββββββββββββββββββββββββ€
β β
β DATABASE: apna_dhaba β
β βββββββββββββββββββββββββββββββββββ β
β β CUSTOMERS TABLE β β
β β βββββ¬βββββββββββ¬βββββββββββ β β
β β βID βFirst NameβLast Name β β β
β β βββββΌβββββββββββΌβββββββββββ€ β β
β β β1 βRamesh βSharma β β β
β β β2 βPriya βPatel β β β
β β β3 βAmit βSingh β β β
β β βββββ΄βββββββββββ΄βββββββββββ β β
β βββββββββββββββββββββββββββββββββββ β
β βββββββββββββββββββββββββββββββββββ β
β β MENU_ITEMS TABLE β β
β β βββββ¬βββββββββββ¬ββββββββ¬βββββββ β β
β β βID βItem Name βPrice βAvailβ β β
β β βββββΌβββββββββββΌββββββββΌβββββββ€ β β
β β β1 βButter Chkβ250.00βYes β β β
β β β2 βBiryani β180.00βYes β β β
β β β3 βNaan β40.00 βNo β β β
β β βββββ΄βββββββββββ΄ββββββββ΄βββββββ β β
β βββββββββββββββββββββββββββββββββββ β
β β
βββββββββββββββββββββββββββββββββββββββββββ
```
### Practice Exercise - Build Your Own Database
**Let's create a simple student database for a tuition center:**
```sql
-- Step 1: Create the database
CREATE DATABASE smart_tuition;
USE smart_tuition;
-- Step 2: Create students table
CREATE TABLE students (
student_id INT PRIMARY KEY AUTO_INCREMENT,
first_name VARCHAR(50) NOT NULL,
last_name VARCHAR(50) NOT NULL,
class VARCHAR(20) NOT NULL,
phone VARCHAR(15),
parent_name VARCHAR(100),
fees DECIMAL(10,2),
joining_date DATE
);
-- Step 3: Create subjects table
CREATE TABLE subjects (
subject_id INT PRIMARY KEY AUTO_INCREMENT,
subject_name VARCHAR(50) NOT NULL,
teacher_name VARCHAR(100),
difficulty_level VARCHAR(20)
);
```
### Common Mistakes When Creating Tables
| Mistake | What Happens | How to Fix |
|---------|---------------|------------|
| **Forgetting semicolon** | SQL gets confused | Always end with `;` |
| **Wrong data type** | Can't store your data | Choose appropriate type |
| **No primary key** | Can't identify rows | Always have a unique ID |
| **Typos in column names** | Hard to remember | Use clear, simple names |
| **Too many columns** | Gets confusing | Keep it simple and organized |
### Your Database Naming Guide - Indian Style Examples
**Good Names (Easy to Understand):**
```sql
-- People-related tables
customers, students, teachers, employees
-- Product-related tables
products, orders, inventory, suppliers
-- Simple and clear
phone, email, city, price, quantity
```
**Bad Names (Confusing):**
```sql
-- Too complicated
cust_tbl_2024, stdnt_info_v2, prod_master_list
-- Unclear abbreviations
cphn, eml, cty, prc, qty
-- Mixed languages
customer_nam, student_ka_mobile
```
### Practice Exercise - Create Your First Table
**Create a simple books table for a library:**
```sql
-- Create database first
CREATE DATABASE my_library;
USE my_library;
-- Now create books table
CREATE TABLE books (
book_id INT PRIMARY KEY AUTO_INCREMENT,
title VARCHAR(200) NOT NULL,
author VARCHAR(100) NOT NULL,
isbn VARCHAR(20),
published_year YEAR,
genre VARCHAR(50),
available_copies INT DEFAULT 0,
total_copies INT NOT NULL
);
```
**Test your table:**
```sql
-- See what tables you have
SHOW TABLES;
-- See the structure of your table
DESCRIBE books;
```
### What You Just Learned - Quick Recap
```
- CREATE DATABASE = Build your data kitchen
- USE database = Enter your kitchen
- CREATE TABLE = Build storage shelves
- Data types = Choose right containers
- Primary key = Unique identifier for each item
- Good naming = Make life easier for everyone
# SQL Tutorial - Chapter 3: Adding Data to Your Database
**Think of INSERT statements like writing in your notebook - you're adding new information to your collection!**
**Adding data to SQL is like filling out forms at a government office - you need to put the right information in the right boxes!**
## What is INSERT? - The Notebook Story
**INSERT is like adding a new page to your notebook. You decide what information goes on that page!**
**Real-world analogy:**
```
Your Database Notebook:
- You have empty pages (tables) waiting for information
- INSERT is your pen that writes new data
- Each new row is like a new page in your notebook
- You can write one page at a time or multiple pages together
```
## The Basic INSERT Statement - Your First Data Entry
**The simplest way to add data is like filling out a single form:**
```sql
-- Adding one customer to our Apna Dhaba restaurant
INSERT INTO customers (first_name, last_name, phone, email, city)
VALUES ('Ramesh', 'Sharma', '9876543210', 'ramesh@email.com', 'Mumbai');
```
**What's happening here?**
```
βββββββββββββββββββββββββββββββββββββββββββ
β INSERT Statement Breakdown β
βββββββββββββββββββββββββββββββββββββββββββ€
β β
β INSERT INTO customers β β "I want to add to customers table"
β (first_name, last_name, phone, email, β β "These are the columns I want to fill"
β city) β
β VALUES β β "Here comes the actual data"
β ('Ramesh', 'Sharma', '9876543210', β β "One value for each column listed"
β 'ramesh@email.com', 'Mumbai'); β
β β
βββββββββββββββββββββββββββββββββββββββββββ
```
## INSERT with All Columns - The Complete Form
**When you want to fill EVERY column in your table:**
```sql
-- Adding a complete customer record
INSERT INTO customers
VALUES (NULL, 'Priya', 'Patel', '9123456789', 'priya@email.com', 'Ahmedabad', '2024-01-15');
```
** Important:** When you don't specify column names, you MUST provide values for ALL columns in the EXACT order they appear in the table!
**Think of it like:** Filling out a complete application form where every field must be filled, and you can't skip any boxes.
## INSERT Multiple Rows - Adding Many Customers at Once
**Adding multiple customers is like processing a batch of forms:**
```sql
-- Adding three customers in one go (much faster!)
INSERT INTO customers (first_name, last_name, phone, email, city)
VALUES
('Amit', 'Verma', '9988776655', 'amit@email.com', 'Delhi'),
('Sunita', 'Reddy', '9876543211', 'sunita@email.com', 'Hyderabad'),
('Rajesh', 'Kumar', '9123456788', 'rajesh@email.com', 'Chennai');
```
**Why is this better?**
- Faster than three separate INSERT statements
- Less work for the database
- Like submitting a stack of forms instead of going to the counter three times!
## INSERT with Only Some Columns - The Optional Form
**Sometimes you don't have all the information - and that's okay!**
```sql
-- Adding a customer without email (maybe they don't have one yet)
INSERT INTO customers (first_name, last_name, phone, city)
VALUES ('Suresh', 'Gupta', '9876543212', 'Kolkata');
```
**What happens to missing columns?**
- If the column has a DEFAULT value, it uses that
- If the column allows NULL values, it stays empty
- If the column requires a value, you'll get an error
**Think of it like:** A form where some fields say "Optional" - you can leave them blank!
## Real-World Example: Apna Dhaba Restaurant
**Let's add some real restaurant data to make this practical:**
```sql
-- Adding menu items to our restaurant
INSERT INTO menu_items (item_name, price, category, description)
VALUES
('Butter Chicken', 280.00, 'Main Course', 'Creamy chicken in tomato butter sauce'),
('Palak Paneer', 220.00, 'Main Course', 'Fresh spinach with cottage cheese'),
('Naan', 40.00, 'Bread', 'Soft Indian bread baked in tandoor'),
('Jeera Rice', 120.00, 'Rice', 'Basmati rice with cumin seeds'),
('Gulab Jamun', 80.00, 'Dessert', 'Sweet milk balls in sugar syrup');
```
**Adding some orders:**
```sql
-- Customer Ramesh orders Butter Chicken and Naan
INSERT INTO orders (customer_id, order_date, total_amount, payment_method)
VALUES (1, '2024-01-20', 320.00, 'Cash');
-- Adding the individual items in that order
INSERT INTO order_items (order_id, item_id, quantity, price)
VALUES
(1, 1, 1, 280.00), -- Butter Chicken
(1, 3, 1, 40.00); -- Naan
```
## INSERT with SELECT - Copying Data Smartly
**Sometimes you want to copy data from one place to another:**
```sql
-- Creating a backup of VIP customers
INSERT INTO vip_customers (first_name, last_name, phone, email)
SELECT first_name, last_name, phone, email
FROM customers
WHERE city = 'Mumbai';
```
**Think of it like:** Copying names from one notebook to another, but only copying the Mumbai customers!
## Common INSERT Mistakes and How to Fix Them
| Mistake | What Happens | How to Fix |
|---------|---------------|------------|
| **Wrong number of values** | "Column count doesn't match value count" | Count your columns and values - they must match! |
| **Wrong data type** | "Incorrect integer value" | Put numbers in NUMBER columns, text in VARCHAR columns |
| **Duplicate primary key** | "Duplicate entry" | Use AUTO_INCREMENT or provide unique values |
| **Missing required column** | "Field 'column_name' doesn't have a default value" | Either provide the value or make the column allow NULL |
| **Text too long** | "Data too long for column" | Make your text shorter or increase column size |
## Practice Exercise: Smart Tuition Center Database
**Let's create a database for a tuition center in India:**
```sql
-- Create the database
CREATE DATABASE smart_tuition_center;
USE smart_tuition_center;
-- Create students table
CREATE TABLE students (
student_id INT PRIMARY KEY AUTO_INCREMENT,
first_name VARCHAR(50) NOT NULL,
last_name VARCHAR(50) NOT NULL,
phone VARCHAR(15),
email VARCHAR(100),
class VARCHAR(20),
subjects VARCHAR(200),
monthly_fee DECIMAL(10,2),
joining_date DATE
);
-- Add some students (try this yourself!)
INSERT INTO students (first_name, last_name, phone, email, class, subjects, monthly_fee, joining_date)
VALUES
('Ankit', 'Sharma', '9876543210', 'ankit@email.com', '10th', 'Maths, Science, English', 2500.00, '2024-01-15'),
('Pooja', 'Verma', '9123456789', 'pooja@email.com', '12th', 'Physics, Chemistry, Maths', 3000.00, '2024-01-10'),
('Rahul', 'Gupta', '9988776655', NULL, '8th', 'All Subjects', 1800.00, '2024-01-20');
-- Check your work
SELECT * FROM students;
```
**Your turn! Add these students:**
1. Neha Singh, phone: 9876543211, email: neha@email.com, class: 9th, subjects: Maths, Science, monthly fee: 2200, joining date: 2024-01-25
2. Vikas Kumar, phone: 9123456788, no email, class: 11th, subjects: Physics, Chemistry, monthly fee: 2800, joining date: 2024-01-18
**Solution:**
```sql
INSERT INTO students (first_name, last_name, phone, email, class, subjects, monthly_fee, joining_date)
VALUES
('Neha', 'Singh', '9876543211', 'neha@email.com', '9th', 'Maths, Science', 2200.00, '2024-01-25'),
('Vikas', 'Kumar', '9123456788', NULL, '11th', 'Physics, Chemistry', 2800.00, '2024-01-18');
```
## INSERT Tips from the Pros
**1. Always use column names:**
```sql
-- Good practice - specify columns
INSERT INTO customers (first_name, last_name, phone) VALUES ('Ramesh', 'Sharma', '9876543210');
-- Avoid this - might break if table structure changes
INSERT INTO customers VALUES (NULL, 'Ramesh', 'Sharma', '9876543210', NULL, NULL, NULL);
```
**2. Use transactions for multiple inserts:**
```sql
START TRANSACTION;
INSERT INTO orders (customer_id, total) VALUES (1, 500.00);
INSERT INTO order_items (order_id, item_id, quantity) VALUES (1, 1, 2);
COMMIT;
```
**3. Check your data types:**
```sql
-- Correct: price is a number (no quotes)
INSERT INTO items (name, price) VALUES ('Book', 299.99);
-- Wrong: putting quotes around numbers
INSERT INTO items (name, price) VALUES ('Book', '299.99');
```
## Summary - Your INSERT Checklist
**Before running INSERT:**
- Do I have the right table name?
- Did I list the correct columns?
- Do I have the right number of values?
- Are my data types correct (numbers vs text)?
- Did I handle NULL values properly?
**Remember:** INSERT is like adding new pages to your database notebook. Be careful, but don't be afraid - databases are designed to handle mistakes gracefully!
```
# SQL Tutorial - Chapter 4: Reading Data with SELECT
**SELECT is like asking questions to your database - you tell it what you want to know, and it finds the answers!**
**Think of SELECT as your personal assistant who can quickly find any information you need from your filing cabinet!**
## What is SELECT? - The Filing Cabinet Story
**SELECT is like opening your filing cabinet and pulling out exactly the files you need - no more, no less!**
**Real-world analogy:**
```
Your Database Filing Cabinet:
- You have hundreds of customer files
- SELECT is your assistant who knows exactly where everything is
- You ask: "Show me all customers from Mumbai"
- Assistant pulls out only those files instantly
- You can ask for specific information: "Just their names and phone numbers"
```
## The Basic SELECT Statement - Your First Question
**The simplest SELECT is like asking to see everything in a drawer:**
```sql
-- Show me everything in the customers table
SELECT * FROM customers;
```
**The asterisk (*) means "everything" - like saying "show me the complete file!"**
**Visual breakdown:**
```
βββββββββββββββββββββββββββββββββββββββββββ
β SELECT * FROM customers β
βββββββββββββββββββββββββββββββββββββββββββ€
β β
β SELECT = "Show me" β
β * = "Everything" β
β FROM = "From this table" β
β customers = "The customers table" β
β β
βββββββββββββββββββββββββββββββββββββββββββ
```
## SELECT Specific Columns - Asking for Specific Information
**Instead of seeing complete files, you can ask for specific details:**
```sql
-- Show me only names and phone numbers
SELECT first_name, last_name, phone FROM customers;
-- Show me only email addresses
SELECT email FROM customers;
```
**Think of it like:** Instead of getting complete customer files, you just want their business cards!
## SELECT with WHERE - Finding Specific Records
**WHERE is like telling your assistant exactly what to look for:**
```sql
-- Find customers who live in Mumbai
SELECT first_name, last_name, phone
FROM customers
WHERE city = 'Mumbai';
-- Find customers named Ramesh
SELECT * FROM customers
WHERE first_name = 'Ramesh';
```
**Visual example - Finding Mumbai customers:**
```
βββββββββββββββββββββββββββββββββββββββββββ
β Finding Mumbai Customers β
βββββββββββββββββββββββββββββββββββββββββββ€
β β
β Complete Customer Files: β
β Ramesh Sharma - Mumbai β β
β Priya Patel - Ahmedabad β
β Amit Verma - Mumbai β β
β Sunita Reddy - Hyderabad β
β Rajesh Kumar - Mumbai β β
β β
β WHERE city = 'Mumbai' β
β β β
β Result: Only Mumbai customers! β
β β
βββββββββββββββββββββββββββββββββββββββββββ
```
## Different Types of WHERE Conditions
### 1. Text Comparisons - Finding Names and Cities
```sql
-- Find customers from specific cities
SELECT * FROM customers WHERE city = 'Delhi';
SELECT * FROM customers WHERE city != 'Delhi'; -- NOT Delhi
-- Find customers with specific names
SELECT * FROM customers WHERE first_name = 'Amit';
SELECT * FROM customers WHERE last_name LIKE 'Sharma';
```
### 2. Number Comparisons - Finding Fees and Quantities
```sql
-- Find students who pay more than 2500 rupees
SELECT * FROM students WHERE monthly_fee > 2500;
-- Find students who pay less than or equal to 2000
SELECT * FROM students WHERE monthly_fee <= 2000;
-- Find students who pay exactly 2500 rupees
SELECT * FROM students WHERE monthly_fee = 2500;
```
### 3. Date Comparisons - Finding Recent Records
```sql
-- Find students who joined after January 15, 2024
SELECT * FROM students WHERE joining_date > '2024-01-15';
-- Find orders from today
SELECT * FROM orders WHERE order_date = '2024-01-20';
```
## SELECT with Multiple Conditions - AND and OR
### AND - All conditions must be true
```sql
-- Find Mumbai customers named Amit
SELECT * FROM customers
WHERE city = 'Mumbai' AND first_name = 'Amit';
-- Find 10th class students who pay more than 2000 rupees
SELECT * FROM students
WHERE class = '10th' AND monthly_fee > 2000;
```
**Think of AND like:** "I want customers who are BOTH from Mumbai AND named Amit"
### OR - Any condition can be true
```sql
-- Find customers from Mumbai OR Delhi
SELECT * FROM customers
WHERE city = 'Mumbai' OR city = 'Delhi';
-- Find students in 10th OR 12th class
SELECT * FROM students
WHERE class = '10th' OR class = '12th';
```
**Think of OR like:** "I want customers who are EITHER from Mumbai OR from Delhi"
## SELECT with IN - Multiple Choices Made Simple
**Instead of writing many OR conditions:**
```sql
-- Instead of this long query:
SELECT * FROM customers
WHERE city = 'Mumbai' OR city = 'Delhi' OR city = 'Chennai' OR city = 'Kolkata';
-- Use this simple version:
SELECT * FROM customers
WHERE city IN ('Mumbai', 'Delhi', 'Chennai', 'Kolkata');
```
**IN is like saying:** "Find customers from any of these cities"
## SELECT with BETWEEN - Finding Ranges
```sql
-- Find students who pay between 2000 and 3000 rupees
SELECT * FROM students
WHERE monthly_fee BETWEEN 2000 AND 3000;
-- Find orders from last week
SELECT * FROM orders
WHERE order_date BETWEEN '2024-01-15' AND '2024-01-22';
```
**BETWEEN is like:** "Find records in this range, including the edges"
## SELECT with LIKE - Pattern Matching
**LIKE is like searching with wildcards:**
```sql
-- Find customers whose name starts with 'A'
SELECT * FROM customers WHERE first_name LIKE 'A%';
-- Find customers whose email contains 'gmail'
SELECT * FROM customers WHERE email LIKE '%gmail%';
-- Find customers whose phone ends with '210'
SELECT * FROM customers WHERE phone LIKE '%210';
```
**LIKE patterns:**
- `%` means "any number of characters"
- `_` means "exactly one character"
**Think of % like:** A blank that can fill any text
## SELECT with IS NULL - Finding Empty Fields
```sql
-- Find customers who don't have email addresses
SELECT * FROM customers WHERE email IS NULL;
-- Find customers who DO have email addresses
SELECT * FROM customers WHERE email IS NOT NULL;
```
**Remember:** You can't use `=` with NULL - always use `IS NULL` or `IS NOT NULL`
## Real-World Example: Apna Dhaba Restaurant Queries
**Let's practice with real restaurant data:**
```sql
-- Find all vegetarian main courses
SELECT item_name, price FROM menu_items
WHERE category = 'Main Course' AND description LIKE '%paneer%';
-- Find items priced between 100 and 200 rupees
SELECT item_name, price FROM menu_items
WHERE price BETWEEN 100 AND 200;
-- Find all bread items (Naan, Roti, etc.)
SELECT item_name, price FROM menu_items
WHERE category = 'Bread';
-- Find expensive items (more than 250 rupees)
SELECT item_name, price FROM menu_items
WHERE price > 250;
```
## Practice Exercise: Smart Tuition Center Queries
**Let's practice with our tuition center database:**
```sql
-- Create the database and table (if not exists)
CREATE DATABASE IF NOT EXISTS smart_tuition_center;
USE smart_tuition_center;
-- Show all students
SELECT * FROM students;
-- Show only names and classes
SELECT first_name, last_name, class FROM students;
-- Find 10th class students
SELECT * FROM students WHERE class = '10th';
-- Find students who pay more than 2500 rupees
SELECT first_name, last_name, monthly_fee FROM students
WHERE monthly_fee > 2500;
-- Find students from Mumbai or Delhi
SELECT * FROM students
WHERE city IN ('Mumbai', 'Delhi');
-- Find students who joined after January 15, 2024
SELECT * FROM students WHERE joining_date > '2024-01-15';
```
**Your turn! Write queries for:**
1. Find students whose name starts with 'A'
2. Find students who pay between 2000 and 3000 rupees
3. Find students who don't have email addresses
4. Find 12th class students who joined after January 10, 2024
**Solutions:**
```sql
-- 1. Students whose name starts with 'A'
SELECT * FROM students WHERE first_name LIKE 'A%';
-- 2. Students who pay between 2000 and 3000 rupees
SELECT * FROM students WHERE monthly_fee BETWEEN 2000 AND 3000;
-- 3. Students without email addresses
SELECT * FROM students WHERE email IS NULL;
-- 4. 12th class students who joined after January 10, 2024
SELECT * FROM students
WHERE class = '12th' AND joining_date > '2024-01-10';
```
## Common SELECT Mistakes and How to Fix Them
| Mistake | What Happens | How to Fix |
|---------|---------------|------------|
| **Wrong table name** | "Table doesn't exist" | Check your spelling and case sensitivity |
| **Wrong column name** | "Unknown column" | Check column names with `DESCRIBE tablename` |
| **Missing quotes around text** | "Unknown column 'Mumbai'" | Put quotes around text values: `'Mumbai'` |
| **Using = with NULL** | "Unknown column 'NULL'" | Use `IS NULL` instead of `= NULL` |
| **Wrong date format** | "Incorrect date value" | Use YYYY-MM-DD format: `'2024-01-20'` |
| **Missing semicolon** | Query doesn't end | Always end SQL statements with `;` |
## SELECT Tips from the Pros
**1. Always test with SELECT * first:**
```sql
-- First, see what the data looks like
SELECT * FROM customers LIMIT 5;
-- Then write your specific query
SELECT first_name, phone FROM customers WHERE city = 'Mumbai';
```
**2. Use LIMIT to avoid overwhelming results:**
```sql
-- Show only first 10 results
SELECT * FROM customers LIMIT 10;
-- Show results 11-20 (useful for pagination)
SELECT * FROM customers LIMIT 10 OFFSET 10;
```
**3. Check your data types:**
```sql
-- Numbers don't need quotes
SELECT * FROM students WHERE monthly_fee > 2000;
-- Text needs quotes
SELECT * FROM students WHERE class = '10th';
```
**Remember:** SELECT is like asking questions to your database. Start simple, then add conditions. Your database is always ready to answer!
```
# SQL Chapter 5: Updating Data - Editing Your Information Without Starting Over
**Updating data is like editing a document - you change specific parts without throwing away the whole thing!**
**Think of UPDATE as your digital eraser and pen - you can fix mistakes and keep your information current!**
## What is UPDATE? - The Filing Cabinet Story
**UPDATE is like having a magical filing cabinet where you can change papers without taking them out!**
**Real-world analogy:**
```
Smart Filing Cabinet (UPDATE):
- "I want to change Ramesh's phone number"
- "Find Ramesh's file, update just the phone number"
- "Keep everything else exactly the same"
- "No need to throw away the whole file!"
```
**Without UPDATE (The Hard Way):**
```sql
-- Delete the old record and recreate it (DON'T DO THIS!)
DELETE FROM customers WHERE id = 1;
INSERT INTO customers (name, email, phone) VALUES ('Ramesh', 'new_email@email.com', '9999999999');
-- This is like throwing away a whole file just to change one phone number!
```
**With UPDATE (The Smart Way):**
```sql
-- Just change what needs changing!
UPDATE customers
SET email = 'new_email@email.com', phone = '9999999999'
WHERE id = 1;
-- This is like using a pen to edit just the parts that need changing!
```
## The UPDATE Command - Your Digital Editing Tool
**The UPDATE command has three main parts, just like editing a form:**
```sql
UPDATE table_name -- Which filing cabinet?
SET column1 = value1, -- What do you want to change?
column2 = value2 -- You can change multiple things!
WHERE condition; -- Which files should be changed?
```
**Think of it as:** "Go to this filing cabinet, change these specific fields, but only in files that match these conditions!"
## Practice with Apna Dhaba - Updating Customer Information
Let's help Ramesh update his restaurant's customer records:
### Step 1: See What We Have First
```sql
-- Let's see all our customers
SELECT * FROM customers;
```
**Result:**
```
+----+----------------+----------------+-------------+--------+
| id | name | email | phone | city |
+----+----------------+----------------+-------------+--------+
| 1 | Ramesh Kumar | ramesh@dhaba.com | 9876543210 | Mumbai |
| 2 | Priya Sharma | priya@email.com | 9123456789 | Delhi |
| 3 | Suresh Patel | suresh@gmail.com | 9234567890 | Mumbai |
+----+----------------+----------------+-------------+--------+
```
### Step 2: Update One Customer's Phone Number
```sql
-- Ramesh changed his phone number
UPDATE customers
SET phone = '9999888877'
WHERE name = 'Ramesh Kumar';
```
**What just happened?**
- Found Ramesh Kumar's record
- Changed only his phone number
- Kept his name, email, and city the same
- Only one row was affected
### Step 3: Update Multiple Fields at Once
```sql
-- Priya moved to Mumbai and got a new phone
UPDATE customers
SET phone = '9988776655',
city = 'Mumbai'
WHERE name = 'Priya Sharma';
```
**Think of it as:** "Find Priya's file and update both her phone number AND her city in one go!"
## Smart Tuition Center - Updating Student Records
Let's help the tuition center update their student database:
### Current Students Table:
```sql
SELECT * FROM students;
```
**Result:**
```
+----+----------------+--------+----------------+--------+
| id | name | grade | email | fees |
+----+----------------+--------+----------------+--------+
| 1 | Anjali Verma | 10th | anjali@email.com | 5000 |
| 2 | Rohan Singh | 12th | rohan@email.com | 7000 |
| 3 | Neha Gupta | 11th | neha@email.com | 6000 |
+----+----------------+--------+----------------+--------+
```
### Update Student Grades
```sql
-- Anjali moved to 11th grade
UPDATE students
SET grade = '11th'
WHERE name = 'Anjali Verma';
```
### Update Fees for Multiple Students
```sql
-- All students get a 10% fee increase
UPDATE students
SET fees = fees * 1.10;
-- This updates ALL students because there's no WHERE clause!
```
** WARNING: No WHERE clause means UPDATE EVERYTHING!**
## The Golden Rule: Always Use WHERE with UPDATE
**The WHERE clause is like telling your assistant exactly which files to edit:**
```sql
-- Good: Update specific customers
UPDATE customers
SET city = 'Pune'
WHERE city = 'Mumbai';
-- Only Mumbai customers become Pune customers
-- Dangerous: Update ALL customers!
UPDATE customers
SET city = 'Pune';
-- Now EVERY customer lives in Pune! (Probably not what you wanted!)
```
## Common UPDATE Patterns - Your Editing Toolkit
### Pattern 1: Update One Specific Record
```sql
-- Update one customer by ID (safest method!)
UPDATE customers
SET email = 'newemail@email.com'
WHERE id = 5;
```
### Pattern 2: Update Multiple Records with Same Condition
```sql
-- Update all customers in a specific city
UPDATE customers
SET city = 'New Mumbai'
WHERE city = 'Mumbai';
```
### Pattern 3: Update Based on Calculation
```sql
-- Increase all fees by 500 rupees
UPDATE students
SET fees = fees + 500;
```
### Pattern 4: Update with Multiple Conditions
```sql
-- Update only 12th grade students with high fees
UPDATE students
SET fees = fees - 1000
WHERE grade = '12th' AND fees > 6500;
```
## Visual Example - How UPDATE Works
```
βββββββββββββββββββββββββββββββββββββββββββ
β UPDATE Process - Like Editing Forms β
βββββββββββββββββββββββββββββββββββββββββββ€
β β
β Step 1: Find the records β
β WHERE name = 'Ramesh Kumar' β
β β β
β Step 2: Check current values β
β Name: Ramesh Kumar β
β Phone: 9876543210 β Old value β
β β β
β Step 3: Apply changes β
β SET phone = '9999888877' β
β β β
β Step 4: Save new values β
β Name: Ramesh Kumar β
β Phone: 9999888877 β New value! β
β β
βββββββββββββββββββββββββββββββββββββββββββ
```
## Practice Exercises - Let's Get Hands Dirty!
### Exercise 1: Update Customer Information
```sql
-- Priya got married and changed her name to Priya Verma
UPDATE customers
SET name = 'Priya Verma'
WHERE name = 'Priya Sharma';
```
### Exercise 2: Update Multiple Fields
```sql
-- Suresh moved to Delhi and got a new phone
UPDATE customers
SET city = 'Delhi',
phone = '9111111111'
WHERE name = 'Suresh Patel';
```
### Exercise 3: Update Based on Condition
```sql
-- All Mumbai customers get a special Mumbai number prefix
UPDATE customers
SET phone = '022' || phone
WHERE city = 'Mumbai';
-- || means concatenate in many SQL databases
```
## Common UPDATE Mistakes and How to Fix Them
| Mistake | What Happens | How to Fix |
|---------|---------------|------------|
| **Forgetting WHERE clause** | Updates ALL records! | Always double-check your WHERE condition |
| **Wrong column name** | Error: column not found | Check your table structure first |
| **Wrong data type** | Error: type mismatch | Make sure your value matches the column type |
| **Updating too many records** | More changes than expected | Use SELECT first to see what will be updated |
## Safety First: Check Before You Update!
**Always run a SELECT first to see what you're about to change:**
```sql
-- Step 1: See what will be updated
SELECT * FROM customers
WHERE city = 'Mumbai';
-- Step 2: If that looks right, then update
UPDATE customers
SET city = 'Mumbai City'
WHERE city = 'Mumbai';
```
**Think of it as:** "Let me see which files I'm about to edit before I actually make the changes!"
## Real-World Examples from Indian Businesses
### Example 1: Restaurant Menu Updates
```sql
-- Apna Dhaba increased prices by 10% for all vegetarian items
UPDATE menu_items
SET price = price * 1.10
WHERE category = 'Vegetarian';
```
### Example 2: Tuition Center Fee Updates
```sql
-- Smart Tuition Center gives 500 rupees discount to 10th grade students
UPDATE students
SET fees = fees - 500
WHERE grade = '10th';
```
### Example 3: Customer Status Updates
```sql
-- Mark all customers who joined before 2023 as 'Loyal'
UPDATE customers
SET status = 'Loyal Customer'
WHERE joined_date < '2023-01-01';
```
**Remember:** UPDATE is like using a pen to edit your filing cabinet - precise, efficient, and much better than throwing everything away and starting over!
```
# SQL Tutorial - Chapter 3: Adding Data to Your Database
**Think of INSERT statements like writing in your notebook -
you're adding new information to your collection!**
**Adding data to SQL is like filling out forms at a
government office - you need to put the right information in the right boxes!**
## What is INSERT? - The Notebook Story
**INSERT is like adding a new page to your notebook. You
decide what information goes on that page!**
**Real-world analogy:**
```
Your Database
Notebook:
- You have empty pages (tables) waiting for information
- INSERT is your pen that writes new data
- Each new row is like a new page in your notebook
- You can write one page at a time or multiple pages
together
```
## The Basic INSERT Statement - Your First Data Entry
**The simplest way to add data is like filling out a single
form:**
```sql
-- Adding one customer to our Apna Dhaba restaurant
INSERT INTO customers (first_name, last_name, phone, email,
city)
VALUES ('Ramesh', 'Sharma', '9876543210',
'ramesh@email.com', 'Mumbai');
```
**What's happening here?**
```
βββββββββββββββββββββββββββββββββββββββββββ
β INSERT Statement BreakdownΒ Β Β Β Β Β Β Β Β Β Β Β Β β
βββββββββββββββββββββββββββββββββββββββββββ€
βΒ Β Β Β Β Β Β Β Β Β Β Β Β Β Β Β Β Β Β Β Β Β Β Β Β Β Β Β Β Β Β Β Β Β Β Β Β Β Β Β β
βΒ INSERT INTO
customersΒ Β Β Β Β Β Β Β Β Β Β Β Β Β Β Β Β β β "I
want to add to customers table"
βΒ (first_name,
last_name, phone, email, β β "These are the columns I want to fill"
βΒ Β city)Β Β Β Β Β Β Β Β Β Β Β Β Β Β Β Β Β Β Β Β Β Β Β Β Β Β Β Β Β Β Β Β β
βΒ VALUESΒ Β Β Β Β Β Β Β Β Β Β Β Β Β Β Β Β Β Β Β Β Β Β Β Β Β Β Β Β Β Β Β β β "Here
comes the actual data"
βΒ ('Ramesh',
'Sharma', '9876543210',Β Β Β β β "One
value for each column listed"
βΒ Β
'ramesh@email.com', 'Mumbai');Β Β Β Β Β Β Β
β
βΒ Β Β Β Β Β Β Β Β Β Β Β Β Β Β Β Β Β Β Β Β Β Β Β Β Β Β Β Β Β Β Β Β Β Β Β Β Β Β Β β
βββββββββββββββββββββββββββββββββββββββββββ
```
## INSERT with All Columns - The Complete Form
**When you want to fill EVERY column in your table:**
```sql
-- Adding a complete customer record
INSERT INTO customers
VALUES (NULL, 'Priya', 'Patel', '9123456789',
'priya@email.com', 'Ahmedabad', '2024-01-15');
```
** Important:** When you don't specify column names, you
MUST provide values for ALL columns in the EXACT order they appear in the
table!
**Think of it like:** Filling out a complete application
form where every field must be filled, and you can't skip any boxes.
## INSERT Multiple Rows - Adding Many Customers at Once
**Adding multiple customers is like processing a batch of
forms:**
```sql
-- Adding three customers in one go (much faster!)
INSERT INTO customers (first_name, last_name, phone, email,
city)
VALUES
('Amit', 'Verma',
'9988776655', 'amit@email.com', 'Delhi'),
('Sunita',
'Reddy', '9876543211', 'sunita@email.com', 'Hyderabad'),
('Rajesh',
'Kumar', '9123456788', 'rajesh@email.com', 'Chennai');
```
**Why is this better?**
- Faster than three separate INSERT statements
- Less work for the database
- Like submitting a stack of forms instead of going to the
counter three times!
## INSERT with Only Some Columns - The Optional Form
**Sometimes you don't have all the information - and that's
okay!**
```sql
-- Adding a customer without email (maybe they don't have
one yet)
INSERT INTO customers (first_name, last_name, phone, city)
VALUES ('Suresh', 'Gupta', '9876543212', 'Kolkata');
```
**What happens to missing columns?**
- If the column has a DEFAULT value, it uses that
- If the column allows NULL values, it stays empty
- If the column requires a value, you'll get an error
**Think of it like:** A form where some fields say
"Optional" - you can leave them blank!
## Real-World Example: Apna Dhaba Restaurant
**Let's add some real restaurant data to make this
practical:**
```sql
-- Adding menu items to our restaurant
INSERT INTO menu_items (item_name, price, category,
description)
VALUES
('Butter Chicken',
280.00, 'Main Course', 'Creamy chicken in tomato butter sauce'),
('Palak Paneer',
220.00, 'Main Course', 'Fresh spinach with cottage cheese'),
('Naan', 40.00,
'Bread', 'Soft Indian bread baked in tandoor'),
('Jeera Rice',
120.00, 'Rice', 'Basmati rice with cumin seeds'),
('Gulab Jamun',
80.00, 'Dessert', 'Sweet milk balls in sugar syrup');
```
**Adding some orders:**
```sql
-- Customer Ramesh orders Butter Chicken and Naan
INSERT INTO orders (customer_id, order_date, total_amount,
payment_method)
VALUES (1, '2024-01-20', 320.00, 'Cash');
-- Adding the individual items in that order
INSERT INTO order_items (order_id, item_id, quantity, price)
VALUES
(1, 1, 1,
280.00),Β -- Butter Chicken
(1, 3, 1,
40.00);Β Β -- Naan
```
## INSERT with SELECT - Copying Data Smartly
**Sometimes you want to copy data from one place to
another:**
```sql
-- Creating a backup of VIP customers
INSERT INTO vip_customers (first_name, last_name, phone,
email)
SELECT first_name, last_name, phone, email
FROM customers
WHERE city = 'Mumbai';
```
**Think of it like:** Copying names from one notebook to
another, but only copying the Mumbai customers!
## Common INSERT Mistakes and How to Fix Them
| Mistake | What Happens | How to Fix |
|---------|---------------|------------|
| **Wrong number of values** | "Column count doesn't
match value count" | Count your columns and values - they must match! |
| **Wrong data type** | "Incorrect integer value"
| Put numbers in NUMBER columns, text in VARCHAR columns |
| **Duplicate primary key** | "Duplicate entry" |
Use AUTO_INCREMENT or provide unique values |
| **Missing required column** | "Field 'column_name'
doesn't have a default value" | Either provide the value or make the
column allow NULL |
| **Text too long** | "Data too long for column" |
Make your text shorter or increase column size |
## Practice Exercise: Smart Tuition Center Database
**Let's create a database for a tuition center in India:**
```sql
-- Create the database
CREATE DATABASE smart_tuition_center;
USE smart_tuition_center;
-- Create students table
CREATE TABLE students (
student_id INT
PRIMARY KEY AUTO_INCREMENT,
first_name
VARCHAR(50) NOT NULL,
last_name
VARCHAR(50) NOT NULL,
phone VARCHAR(15),
email
VARCHAR(100),
class VARCHAR(20),
subjects
VARCHAR(200),
monthly_fee
DECIMAL(10,2),
joining_date DATE
);
-- Add some students (try this yourself!)
INSERT INTO students (first_name, last_name, phone, email,
class, subjects, monthly_fee, joining_date)
VALUES
('Ankit',
'Sharma', '9876543210', 'ankit@email.com', '10th', 'Maths, Science, English',
2500.00, '2024-01-15'),
('Pooja', 'Verma',
'9123456789', 'pooja@email.com', '12th', 'Physics, Chemistry, Maths', 3000.00,
'2024-01-10'),
('Rahul', 'Gupta',
'9988776655', NULL, '8th', 'All Subjects', 1800.00, '2024-01-20');
-- Check your work
SELECT * FROM students;
```
**Your turn! Add these students:**
1. Neha Singh, phone: 9876543211, email: neha@email.com,
class: 9th, subjects: Maths, Science, monthly fee: 2200, joining date:
2024-01-25
2. Vikas Kumar, phone: 9123456788, no email, class: 11th,
subjects: Physics, Chemistry, monthly fee: 2800, joining date: 2024-01-18
**Solution:**
```sql
INSERT INTO students (first_name, last_name, phone, email,
class, subjects, monthly_fee, joining_date)
VALUES
('Neha', 'Singh',
'9876543211', 'neha@email.com', '9th', 'Maths, Science', 2200.00,
'2024-01-25'),
('Vikas', 'Kumar',
'9123456788', NULL, '11th', 'Physics, Chemistry', 2800.00, '2024-01-18');
```
## INSERT Tips from the Pros
**1. Always use column names:**
```sql
-- Good practice - specify columns
INSERT INTO customers (first_name, last_name, phone) VALUES
('Ramesh', 'Sharma', '9876543210');
-- Avoid this - might break if table structure changes
INSERT INTO customers VALUES (NULL, 'Ramesh', 'Sharma',
'9876543210', NULL, NULL, NULL);
```
**2. Use transactions for multiple inserts:**
```sql
START TRANSACTION;
INSERT INTO orders (customer_id, total) VALUES (1, 500.00);
INSERT INTO order_items (order_id, item_id, quantity) VALUES
(1, 1, 2);
COMMIT;
```
**3. Check your data types:**
```sql
-- Correct: price is a number (no quotes)
INSERT INTO items (name, price) VALUES ('Book', 299.99);
-- Wrong: putting quotes around numbers
INSERT INTO items (name, price) VALUES ('Book', '299.99');
```
## Summary - Your INSERT Checklist
**Before running INSERT:**
- Do I have the right table name?
- Did I list the correct columns?
- Do I have the right number of values?
- Are my data types correct (numbers vs text)?
- Did I handle NULL values properly?
**Remember:** INSERT is like adding new pages to your
database notebook. Be careful, but don't be afraid - databases are designed to
handle mistakes gracefully!
```
# SQL Tutorial - Chapter 4: Reading Data with SELECT
**SELECT is like asking questions to your database - you
tell it what you want to know, and it finds the answers!**
**Think of SELECT as your personal assistant who can quickly
find any information you need from your filing cabinet!**
## What is SELECT? - The Filing Cabinet Story
**SELECT is like opening your filing cabinet and pulling out
exactly the files you need - no more, no less!**
**Real-world analogy:**
```
Your Database Filing
Cabinet:
- You have hundreds of customer files
- SELECT is your assistant who knows exactly where
everything is
- You ask: "Show me all customers from Mumbai"
- Assistant pulls out only those files instantly
- You can ask for specific information: "Just their
names and phone numbers"
```
## The Basic SELECT Statement - Your First Question
**The simplest SELECT is like asking to see everything in a
drawer:**
```sql
-- Show me everything in the customers table
SELECT * FROM customers;
```
**The asterisk (*) means "everything" - like
saying "show me the complete file!"**
**Visual breakdown:**
```
βββββββββββββββββββββββββββββββββββββββββββ
β SELECT * FROM customersΒ Β Β Β Β Β Β Β Β Β Β Β Β Β Β Β β
βββββββββββββββββββββββββββββββββββββββββββ€
βΒ Β Β Β Β Β Β Β Β Β Β Β Β Β Β Β Β Β Β Β Β Β Β Β Β Β Β Β Β Β Β Β Β Β Β Β Β Β Β Β β
βΒ SELECTΒ = "Show me"Β Β Β Β Β Β Β Β Β Β Β Β Β Β Β Β Β Β Β β
βΒ *Β Β Β Β Β Β = "Everything"Β Β Β Β Β Β Β Β Β Β Β Β Β Β Β Β β
βΒ FROMΒ Β Β = "From this table"Β Β Β Β Β Β Β Β Β Β Β β
βΒ customers =
"The customers table"Β Β Β Β Β β
βΒ Β Β Β Β Β Β Β Β Β Β Β Β Β Β Β Β Β Β Β Β Β Β Β Β Β Β Β Β Β Β Β Β Β Β Β Β Β Β Β β
βββββββββββββββββββββββββββββββββββββββββββ
```
## SELECT Specific Columns - Asking for Specific Information
**Instead of seeing complete files, you can ask for specific
details:**
```sql
-- Show me only names and phone numbers
SELECT first_name, last_name, phone FROM customers;
-- Show me only email addresses
SELECT email FROM customers;
```
**Think of it like:** Instead of getting complete customer
files, you just want their business cards!
## SELECT with WHERE - Finding Specific Records
**WHERE is like telling your assistant exactly what to look
for:**
```sql
-- Find customers who live in Mumbai
SELECT first_name, last_name, phone
FROM customers
WHERE city = 'Mumbai';
-- Find customers named Ramesh
SELECT * FROM customers
WHERE first_name = 'Ramesh';
```
**Visual example - Finding Mumbai customers:**
```
βββββββββββββββββββββββββββββββββββββββββββ
β Finding Mumbai CustomersΒ Β Β Β Β Β Β Β Β Β Β Β Β Β Β β
βββββββββββββββββββββββββββββββββββββββββββ€
βΒ Β Β Β Β Β Β Β Β Β Β Β Β Β Β Β Β Β Β Β Β Β Β Β Β Β Β Β Β Β Β Β Β Β Β Β Β Β Β Β β
βΒ Complete Customer
Files:Β Β Β Β Β Β Β Β Β Β Β Β Β Β β
βΒ Β Ramesh Sharma -
Mumbai βΒ Β Β Β Β Β Β Β Β Β β
βΒ Β Priya Patel -
AhmedabadΒ Β Β Β Β Β Β Β Β Β Β β
βΒ Β Amit Verma -
Mumbai βΒ Β Β Β Β Β Β Β Β Β Β Β Β β
βΒ Β Sunita Reddy -
HyderabadΒ Β Β Β Β Β Β Β Β β
βΒ Β Rajesh Kumar -
Mumbai βΒ Β Β Β Β Β Β Β Β Β Β β
βΒ Β Β Β Β Β Β Β Β Β Β Β Β Β Β Β Β Β Β Β Β Β Β Β Β Β Β Β Β Β Β Β Β Β Β Β Β Β Β Β β
βΒ WHERE city =
'Mumbai'Β Β Β Β Β Β Β Β Β Β Β Β Β Β Β β
βΒ βΒ Β Β Β Β Β Β Β Β Β Β Β Β Β Β Β Β Β Β Β Β Β Β Β Β Β Β Β Β Β Β Β Β Β Β Β Β β
βΒ Result: Only Mumbai
customers!Β Β Β Β Β Β Β Β β
βΒ Β Β Β Β Β Β Β Β Β Β Β Β Β Β Β Β Β Β Β Β Β Β Β Β Β Β Β Β Β Β Β Β Β Β Β Β Β Β Β β
βββββββββββββββββββββββββββββββββββββββββββ
```
## Different Types of WHERE Conditions
### 1. Text Comparisons - Finding Names and Cities
```sql
-- Find customers from specific cities
SELECT * FROM customers WHERE city = 'Delhi';
SELECT * FROM customers WHERE city != 'Delhi';Β -- NOT Delhi
-- Find customers with specific names
SELECT * FROM customers WHERE first_name = 'Amit';
SELECT * FROM customers WHERE last_name LIKE 'Sharma';
```
### 2. Number Comparisons - Finding Fees and Quantities
```sql
-- Find students who pay more than 2500 rupees
SELECT * FROM students WHERE monthly_fee > 2500;
-- Find students who pay less than or equal to 2000
SELECT * FROM students WHERE monthly_fee <= 2000;
-- Find students who pay exactly 2500 rupees
SELECT * FROM students WHERE monthly_fee = 2500;
```
### 3. Date Comparisons - Finding Recent Records
```sql
-- Find students who joined after January 15, 2024
SELECT * FROM students WHERE joining_date > '2024-01-15';
-- Find orders from today
SELECT * FROM orders WHERE order_date = '2024-01-20';
```
## SELECT with Multiple Conditions - AND and OR
### AND - All conditions must be true
```sql
-- Find Mumbai customers named Amit
SELECT * FROM customers
WHERE city = 'Mumbai' AND first_name = 'Amit';
-- Find 10th class students who pay more than 2000 rupees
SELECT * FROM students
WHERE class = '10th' AND monthly_fee > 2000;
```
**Think of AND like:** "I want customers who are BOTH
from Mumbai AND named Amit"
### OR - Any condition can be true
```sql
-- Find customers from Mumbai OR Delhi
SELECT * FROM customers
WHERE city = 'Mumbai' OR city = 'Delhi';
-- Find students in 10th OR 12th class
SELECT * FROM students
WHERE class = '10th' OR class = '12th';
```
**Think of OR like:** "I want customers who are EITHER
from Mumbai OR from Delhi"
## SELECT with IN - Multiple Choices Made Simple
**Instead of writing many OR conditions:**
```sql
-- Instead of this long query:
SELECT * FROM customers
WHERE city = 'Mumbai' OR city = 'Delhi' OR city = 'Chennai'
OR city = 'Kolkata';
-- Use this simple version:
SELECT * FROM customers
WHERE city IN ('Mumbai', 'Delhi', 'Chennai', 'Kolkata');
```
**IN is like saying:** "Find customers from any of
these cities"
## SELECT with BETWEEN - Finding Ranges
```sql
-- Find students who pay between 2000 and 3000 rupees
SELECT * FROM students
WHERE monthly_fee BETWEEN 2000 AND 3000;
-- Find orders from last week
SELECT * FROM orders
WHERE order_date BETWEEN '2024-01-15' AND '2024-01-22';
```
**BETWEEN is like:** "Find records in this range,
including the edges"
## SELECT with LIKE - Pattern Matching
**LIKE is like searching with wildcards:**
```sql
-- Find customers whose name starts with 'A'
SELECT * FROM customers WHERE first_name LIKE 'A%';
-- Find customers whose email contains 'gmail'
SELECT * FROM customers WHERE email LIKE '%gmail%';
-- Find customers whose phone ends with '210'
SELECT * FROM customers WHERE phone LIKE '%210';
```
**LIKE patterns:**
- `%` means "any number of characters"
- `_` means "exactly one character"
**Think of % like:** A blank that can fill any text
## SELECT with IS NULL - Finding Empty Fields
```sql
-- Find customers who don't have email addresses
SELECT * FROM customers WHERE email IS NULL;
-- Find customers who DO have email addresses
SELECT * FROM customers WHERE email IS NOT NULL;
```
**Remember:** You can't use `=` with NULL - always use `IS
NULL` or `IS NOT NULL`
## Real-World Example: Apna Dhaba Restaurant Queries
**Let's practice with real restaurant data:**
```sql
-- Find all vegetarian main courses
SELECT item_name, price FROM menu_items
WHERE category = 'Main Course' AND description LIKE
'%paneer%';
-- Find items priced between 100 and 200 rupees
SELECT item_name, price FROM menu_items
WHERE price BETWEEN 100 AND 200;
-- Find all bread items (Naan, Roti, etc.)
SELECT item_name, price FROM menu_items
WHERE category = 'Bread';
-- Find expensive items (more than 250 rupees)
SELECT item_name, price FROM menu_items
WHERE price > 250;
```
## Practice Exercise: Smart Tuition Center Queries
**Let's practice with our tuition center database:**
```sql
-- Create the database and table (if not exists)
CREATE DATABASE IF NOT EXISTS smart_tuition_center;
USE smart_tuition_center;
-- Show all students
SELECT * FROM students;
-- Show only names and classes
SELECT first_name, last_name, class FROM students;
-- Find 10th class students
SELECT * FROM students WHERE class = '10th';
-- Find students who pay more than 2500 rupees
SELECT first_name, last_name, monthly_fee FROM students
WHERE monthly_fee > 2500;
-- Find students from Mumbai or Delhi
SELECT * FROM students
WHERE city IN ('Mumbai', 'Delhi');
-- Find students who joined after January 15, 2024
SELECT * FROM students WHERE joining_date > '2024-01-15';
```
**Your turn! Write queries for:**
1. Find students whose name starts with 'A'
2. Find students who pay between 2000 and 3000 rupees
3. Find students who don't have email addresses
4. Find 12th class students who joined after January 10,
2024
**Solutions:**
```sql
-- 1. Students whose name starts with 'A'
SELECT * FROM students WHERE first_name LIKE 'A%';
-- 2. Students who pay between 2000 and 3000 rupees
SELECT * FROM students WHERE monthly_fee BETWEEN 2000 AND
3000;
-- 3. Students without email addresses
SELECT * FROM students WHERE email IS NULL;
-- 4. 12th class students who joined after January 10, 2024
SELECT * FROM students
WHERE class = '12th' AND joining_date > '2024-01-10';
```
## Common SELECT Mistakes and How to Fix Them
| Mistake | What Happens | How to Fix |
|---------|---------------|------------|
| **Wrong table name** | "Table doesn't exist" |
Check your spelling and case sensitivity |
| **Wrong column name** | "Unknown column" | Check
column names with `DESCRIBE tablename` |
| **Missing quotes around text** | "Unknown column
'Mumbai'" | Put quotes around text values: `'Mumbai'` |
| **Using = with NULL** | "Unknown column 'NULL'"
| Use `IS NULL` instead of `= NULL` |
| **Wrong date format** | "Incorrect date value" |
Use YYYY-MM-DD format: `'2024-01-20'` |
| **Missing semicolon** | Query doesn't end | Always end SQL
statements with `;` |
## SELECT Tips from the Pros
**1. Always test with SELECT * first:**
```sql
-- First, see what the data looks like
SELECT * FROM customers LIMIT 5;
-- Then write your specific query
SELECT first_name, phone FROM customers WHERE city =
'Mumbai';
```
**2. Use LIMIT to avoid overwhelming results:**
```sql
-- Show only first 10 results
SELECT * FROM customers LIMIT 10;
-- Show results 11-20 (useful for pagination)
SELECT * FROM customers LIMIT 10 OFFSET 10;
```
**3. Check your data types:**
```sql
-- Numbers don't need quotes
SELECT * FROM students WHERE monthly_fee > 2000;
-- Text needs quotes
SELECT * FROM students WHERE class = '10th';
```
**Remember:** SELECT is like asking questions to your
database. Start simple, then add conditions. Your database is always ready to
answer!
```
# SQL Chapter 5: Updating Data - Editing Your Information
Without Starting Over
**Updating data is like editing a document - you change
specific parts without throwing away the whole thing!**
**Think of UPDATE as your digital eraser and pen - you can
fix mistakes and keep your information current!**
## What is UPDATE? - The Filing Cabinet Story
**UPDATE is like having a magical filing cabinet where you
can change papers without taking them out!**
**Real-world analogy:**
```
Smart Filing Cabinet
(UPDATE):
- "I want to change Ramesh's phone number"
- "Find Ramesh's file, update just the phone
number"
- "Keep everything else exactly the same"
- "No need to throw away the whole file!"
```
**Without UPDATE (The Hard Way):**
```sql
--Β Delete the old
record and recreate it (DON'T DO THIS!)
DELETE FROM customers WHERE id = 1;
INSERT INTO customers (name, email, phone) VALUES ('Ramesh',
'new_email@email.com', '9999999999');
-- This is like throwing away a whole file just to change
one phone number!
```
**With UPDATE (The Smart Way):**
```sql
--Β Just change what
needs changing!
UPDATE customers
SET email = 'new_email@email.com', phone = '9999999999'
WHERE id = 1;
-- This is like using a pen to edit just the parts that need
changing!
```
## The UPDATE Command - Your Digital Editing Tool
**The UPDATE command has three main parts, just like editing
a form:**
```sql
UPDATE table_nameΒ Β Β Β Β
-- Which filing cabinet?
SET column1 = value1, -- What do you want to change?
column2 =
value2Β Β -- You can change multiple
things!
WHERE condition;Β Β Β Β Β Β
-- Which files should be changed?
```
**Think of it as:** "Go to this filing cabinet, change
these specific fields, but only in files that match these conditions!"
## Practice with Apna Dhaba - Updating Customer Information
Let's help Ramesh update his restaurant's customer records:
### Step 1: See What We Have First
```sql
-- Let's see all our customers
SELECT * FROM customers;
```
**Result:**
```
+----+----------------+----------------+-------------+--------+
| id | nameΒ Β Β Β Β Β Β Β Β Β
| emailΒ Β Β Β Β Β Β Β Β | phoneΒ Β Β Β Β Β | cityΒ Β
|
+----+----------------+----------------+-------------+--------+
|Β 1 | Ramesh
KumarΒ Β | ramesh@dhaba.com | 9876543210 |
Mumbai |
|Β 2 | Priya
SharmaΒ Β | priya@email.comΒ | 9123456789 | DelhiΒ |
|Β 3 | Suresh
PatelΒ Β | suresh@gmail.com | 9234567890 |
Mumbai |
+----+----------------+----------------+-------------+--------+
```
### Step 2: Update One Customer's Phone Number
```sql
-- Ramesh changed his phone number
UPDATE customers
SET phone = '9999888877'
WHERE name = 'Ramesh Kumar';
```
**What just happened?**
-Β Found Ramesh
Kumar's record
-Β Changed only his
phone number
-Β Kept his name,
email, and city the same
-Β Only one row was
affected
### Step 3: Update Multiple Fields at Once
```sql
-- Priya moved to Mumbai and got a new phone
UPDATE customers
SET phone = '9988776655',
city = 'Mumbai'
WHERE name = 'Priya Sharma';
```
**Think of it as:** "Find Priya's file and update both
her phone number AND her city in one go!"
## Smart Tuition Center - Updating Student Records
Let's help the tuition center update their student database:
### Current Students Table:
```sql
SELECT * FROM students;
```
**Result:**
```
+----+----------------+--------+----------------+--------+
| id | nameΒ Β Β Β Β Β Β Β Β Β
| gradeΒ | emailΒ Β Β Β Β Β Β Β Β | feesΒ Β |
+----+----------------+--------+----------------+--------+
|Β 1 | Anjali
VermaΒ Β | 10thΒ Β | anjali@email.com | 5000Β Β |
|Β 2 | Rohan
SinghΒ Β Β | 12thΒ Β | rohan@email.comΒ | 7000Β Β
|
|Β 3 | Neha GuptaΒ Β Β Β | 11thΒ Β
| neha@email.comΒ Β | 6000Β Β |
+----+----------------+--------+----------------+--------+
```
### Update Student Grades
```sql
-- Anjali moved to 11th grade
UPDATE students
SET grade = '11th'
WHERE name = 'Anjali Verma';
```
### Update Fees for Multiple Students
```sql
-- All students get a 10% fee increase
UPDATE students
SET fees = fees * 1.10;
-- This updates ALL students because there's no WHERE
clause!
```
** WARNING: No WHERE clause means UPDATE EVERYTHING!**
## The Golden Rule: Always Use WHERE with UPDATE
**The WHERE clause is like telling your assistant exactly
which files to edit:**
```sql
--Β Good: Update
specific customers
UPDATE customers
SET city = 'Pune'
WHERE city = 'Mumbai';
-- Only Mumbai customers become Pune customers
--Β Dangerous: Update
ALL customers!
UPDATE customers
SET city = 'Pune';
-- Now EVERY customer lives in Pune! (Probably not what you
wanted!)
```
## Common UPDATE Patterns - Your Editing Toolkit
### Pattern 1: Update One Specific Record
```sql
-- Update one customer by ID (safest method!)
UPDATE customers
SET email = 'newemail@email.com'
WHERE id = 5;
```
### Pattern 2: Update Multiple Records with Same Condition
```sql
-- Update all customers in a specific city
UPDATE customers
SET city = 'New Mumbai'
WHERE city = 'Mumbai';
```
### Pattern 3: Update Based on Calculation
```sql
-- Increase all fees by 500 rupees
UPDATE students
SET fees = fees + 500;
```
### Pattern 4: Update with Multiple Conditions
```sql
-- Update only 12th grade students with high fees
UPDATE students
SET fees = fees - 1000
WHERE grade = '12th' AND fees > 6500;
```
## Visual Example - How UPDATE Works
```
βββββββββββββββββββββββββββββββββββββββββββ
β UPDATE Process - Like Editing FormsΒ Β Β Β β
βββββββββββββββββββββββββββββββββββββββββββ€
βΒ Β Β Β Β Β Β Β Β Β Β Β Β Β Β Β Β Β Β Β Β Β Β Β Β Β Β Β Β Β Β Β Β Β Β Β Β Β Β Β β
βΒ Step 1: Find the
recordsΒ Β Β Β Β Β Β Β Β Β Β Β Β β
βΒ WHERE name =
'Ramesh Kumar'Β Β Β Β Β Β Β Β Β Β β
βΒ Β Β Β Β βΒ Β Β Β Β Β Β Β Β Β Β Β Β Β Β Β Β Β Β Β Β Β Β Β Β Β Β Β Β Β Β Β Β β
βΒ Step 2: Check
current valuesΒ Β Β Β Β Β Β Β Β β
βΒ Name: Ramesh
KumarΒ Β Β Β Β Β Β Β Β Β Β Β Β Β Β Β Β Β Β β
βΒ Phone: 9876543210 β
Old valueΒ Β Β Β Β Β Β Β β
βΒ Β Β Β Β βΒ Β Β Β Β Β Β Β Β Β Β Β Β Β Β Β Β Β Β Β Β Β Β Β Β Β Β Β Β Β Β Β Β β
βΒ Step 3: Apply
changesΒ Β Β Β Β Β Β Β Β Β Β Β Β Β Β Β β
βΒ SET phone =
'9999888877'Β Β Β Β Β Β Β Β Β Β Β Β Β β
βΒ Β Β Β Β βΒ Β Β Β Β Β Β Β Β Β Β Β Β Β Β Β Β Β Β Β Β Β Β Β Β Β Β Β Β Β Β Β Β β
βΒ Step 4: Save new
valuesΒ Β Β Β Β Β Β Β Β Β Β Β Β Β β
βΒ Name: Ramesh
KumarΒ Β Β Β Β Β Β Β Β Β Β Β Β Β Β Β Β Β Β β
βΒ Phone: 9999888877 β
New value!Β Β Β Β Β Β Β β
βΒ Β Β Β Β Β Β Β Β Β Β Β Β Β Β Β Β Β Β Β Β Β Β Β Β Β Β Β Β Β Β Β Β Β Β Β Β Β Β Β β
βββββββββββββββββββββββββββββββββββββββββββ
```
## Practice Exercises - Let's Get Hands Dirty!
### Exercise 1: Update Customer Information
```sql
-- Priya got married and changed her name to Priya Verma
UPDATE customers
SET name = 'Priya Verma'
WHERE name = 'Priya Sharma';
```
### Exercise 2: Update Multiple Fields
```sql
-- Suresh moved to Delhi and got a new phone
UPDATE customers
SET city = 'Delhi',
phone =
'9111111111'
WHERE name = 'Suresh Patel';
```
### Exercise 3: Update Based on Condition
```sql
-- All Mumbai customers get a special Mumbai number prefix
UPDATE customers
SET phone = '022' || phone
WHERE city = 'Mumbai';
-- || means concatenate in many SQL databases
```
## Common UPDATE Mistakes and How to Fix Them
| Mistake | What Happens | How to Fix |
|---------|---------------|------------|
| **Forgetting WHERE clause** | Updates ALL records! |
Always double-check your WHERE condition |
| **Wrong column name** | Error: column not found | Check
your table structure first |
| **Wrong data type** | Error: type mismatch | Make sure
your value matches the column type |
| **Updating too many records** | More changes than expected
| Use SELECT first to see what will be updated |
## Safety First: Check Before You Update!
**Always run a SELECT first to see what you're about to
change:**
```sql
-- Step 1: See what will be updated
SELECT * FROM customers
WHERE city = 'Mumbai';
-- Step 2: If that looks right, then update
UPDATE customers
SET city = 'Mumbai City'
WHERE city = 'Mumbai';
```
**Think of it as:** "Let me see which files I'm about
to edit before I actually make the changes!"
## Real-World Examples from Indian Businesses
### Example 1: Restaurant Menu Updates
```sql
-- Apna Dhaba increased prices by 10% for all vegetarian
items
UPDATE menu_items
SET price = price * 1.10
WHERE category = 'Vegetarian';
```
### Example 2: Tuition Center Fee Updates
```sql
-- Smart Tuition Center gives 500 rupees discount to 10th
grade students
UPDATE students
SET fees = fees - 500
WHERE grade = '10th';
```
### Example 3: Customer Status Updates
```sql
-- Mark all customers who joined before 2023 as 'Loyal'
UPDATE customers
SET status = 'Loyal Customer'
WHERE joined_date < '2023-01-01';
```
**Remember:** UPDATE is like using a pen to edit your filing
cabinet - precise, efficient, and much better than throwing everything away and
starting over!
```