What is a Database?

-- This is your first database table!
-- It's like creating a new spreadsheet
CREATE TABLE contacts (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    email TEXT,
    phone TEXT,
    favorite BOOLEAN DEFAULT FALSE
);

-- Add your first contact
INSERT INTO contacts (name, email, phone, favorite) 
VALUES ('Your Name', 'you@email.com', '123-456-7890', TRUE);

-- Create a more advanced contacts table
CREATE TABLE contacts (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL,
    email TEXT UNIQUE,
    phone TEXT,
    company TEXT,
    favorite BOOLEAN DEFAULT FALSE,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

-- Add multiple contacts at once
INSERT INTO contacts (name, email, phone, company, favorite) VALUES 
('Elon Musk', 'elon@tesla.com', '555-0001', 'Tesla', TRUE),
('Tim Cook', 'tim@apple.com', '555-0002', 'Apple', FALSE),
('Sundar Pichai', 'sundar@google.com', '555-0003', 'Google', FALSE),
('Your Best Friend', 'friend@email.com', '555-0004', NULL, TRUE);

-- Find all favorite contacts
SELECT * FROM contacts WHERE favorite = TRUE;

-- Find contacts from specific companies
SELECT name, email FROM contacts WHERE company = 'Apple';

-- Search by name (partial match)
SELECT * FROM contacts WHERE name LIKE '%Elon%';

-- Update a phone number
UPDATE contacts 
SET phone = '555-9999' 
WHERE name = 'Elon Musk';

-- Mark someone as favorite
UPDATE contacts 
SET favorite = TRUE 
WHERE email = 'tim@apple.com';

-- Add a category column to organize contacts
ALTER TABLE contacts ADD COLUMN category TEXT;

-- Update existing contacts with categories
UPDATE contacts SET category = 'Tech Leaders' 
WHERE company IN ('Tesla', 'Apple', 'Google');

UPDATE contacts SET category = 'Personal' 
WHERE company IS NULL;

-- Create a table to store notes about contacts
CREATE TABLE notes (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    contact_id INTEGER,
    note TEXT,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (contact_id) REFERENCES contacts(id)
);

-- Add a note about a contact
INSERT INTO notes (contact_id, note) 
VALUES (1, 'Met at tech conference, interested in AI projects');

-- Count contacts by company
SELECT company, COUNT(*) as employee_count 
FROM contacts 
GROUP BY company;

-- Find recently added contacts
SELECT name, created_at 
FROM contacts 
ORDER BY created_at DESC 
LIMIT 5;

-- ✅ GOOD: Separate tables for related data
CREATE TABLE contacts (id, name, email);
CREATE TABLE notes (id, contact_id, note_text);

-- ❌ AVOID: Repeating data in same table
-- Don't put multiple phone numbers in one field like "555-1234, 555-5678"

-- ✅ GOOD: Clear, descriptive names
CREATE TABLE user_profiles (
    user_id INTEGER PRIMARY KEY,
    first_name TEXT NOT NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

-- ❌ AVOID: Confusing abbreviations
-- usrs, fname, crtd_dt

-- ✅ GOOD: Constraints prevent bad data
CREATE TABLE contacts (
    id INTEGER PRIMARY KEY,
    email TEXT UNIQUE NOT NULL,  -- Must be unique and provided
    phone TEXT CHECK(LENGTH(phone) >= 10),  -- Must be valid length
    category TEXT CHECK(category IN ('Personal', 'Work', 'Family'))
);

-- ✅ GOOD: Track who accesses sensitive data
CREATE TABLE audit_log (
    log_id INTEGER PRIMARY KEY,
    user_id TEXT NOT NULL,
    action TEXT NOT NULL,  -- 'view', 'edit', 'delete'
    table_name TEXT NOT NULL,
    record_id INTEGER,
    timestamp DATETIME DEFAULT CURRENT_TIMESTAMP
);

-- Log when someone views contact information
INSERT INTO audit_log (user_id, action, table_name, record_id)
VALUES ('admin', 'view', 'contacts', 1);

-- ✅ GOOD: Index frequently searched columns
CREATE INDEX idx_contact_email ON contacts(email);
CREATE INDEX idx_contact_name ON contacts(name);

-- ✅ GOOD: Use LIMIT for large result sets
SELECT * FROM contacts ORDER BY name LIMIT 50;

-- Add social media handles
ALTER TABLE contacts ADD COLUMN twitter TEXT;
ALTER TABLE contacts ADD COLUMN linkedin TEXT;

-- Find all contacts with social media
SELECT name, twitter, linkedin 
FROM contacts 
WHERE twitter IS NOT NULL OR linkedin IS NOT NULL;

-- Track meetings with contacts
CREATE TABLE meetings (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    contact_id INTEGER,
    meeting_date DATE,
    meeting_notes TEXT,
    follow_up_required BOOLEAN DEFAULT FALSE,
    FOREIGN KEY (contact_id) REFERENCES contacts(id)
);

-- Export contacts to share with team
SELECT name || ',' || email || ',' || phone as contact_info
FROM contacts
WHERE category = 'Tech Leaders';

-- Create a user access log
CREATE TABLE access_log (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    action TEXT,
    user_id TEXT,
    timestamp DATETIME DEFAULT CURRENT_TIMESTAMP
);

-- Log when someone views sensitive data
INSERT INTO access_log (action, user_id) 
VALUES ('viewed_contacts', 'admin_user');

-- Create a simple permission system
CREATE TABLE user_permissions (
    user_id TEXT PRIMARY KEY,
    can_read BOOLEAN DEFAULT TRUE,
    can_write BOOLEAN DEFAULT FALSE,
    can_delete BOOLEAN DEFAULT FALSE
);

-- ❌ Error message: "table contacts already exists"
CREATE TABLE contacts (...);

-- ✅ Solution: Check if table exists first
DROP TABLE IF EXISTS contacts;
CREATE TABLE contacts (...);

-- Or create only if it doesn't exist:
CREATE TABLE IF NOT EXISTS contacts (...);

-- ❌ Error: "no such column: favourite"
SELECT * FROM contacts WHERE favourite = TRUE;

-- ✅ Solution: Check exact column name spelling
SELECT * FROM contacts WHERE favorite = TRUE;  -- Note: 'favorite' not 'favourite'

-- Check your table structure:
.schema contacts  -- In SQLite
-- or
DESCRIBE contacts;  -- In MySQL

-- ❌ Error: "UNIQUE constraint failed: contacts.email"
INSERT INTO contacts (name, email) VALUES ('John', 'john@email.com');
-- This fails if john@email.com already exists

-- ✅ Solution: Check for existing records first
SELECT * FROM contacts WHERE email = 'john@email.com';

-- Or use INSERT OR IGNORE
INSERT OR IGNORE INTO contacts (name, email) VALUES ('John', 'john@email.com');

-- Or use UPDATE if exists, INSERT if not:
INSERT OR REPLACE INTO contacts (name, email) VALUES ('John Updated', 'john@email.com');

-- ❌ Problem: Added data but SELECT returns nothing

-- ✅ Debug steps:
-- 1. Check if data was actually inserted
SELECT COUNT(*) FROM contacts;

-- 2. Check for typos in WHERE clause
SELECT * FROM contacts WHERE name = 'john';  -- Case sensitive!
SELECT * FROM contacts WHERE name LIKE '%john%';  -- Better for searching

-- 3. Look at all data to verify
SELECT * FROM contacts;

-- Test with one record before inserting many
INSERT INTO contacts (name, email) VALUES ('Test User', 'test@email.com');
SELECT * FROM contacts WHERE name = 'Test User';

BEGIN TRANSACTION;
-- Multiple INSERT/UPDATE operations here
-- If anything goes wrong, use: ROLLBACK;
COMMIT;  -- Only if everything worked

-- Export your data regularly
.backup contacts_backup.db  -- SQLite
-- or copy your database file

-- Table Creation
CREATE TABLE table_name (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    column_name DATA_TYPE CONSTRAINTS
);

-- Data Insertion
INSERT INTO table_name (column1, column2) 
VALUES ('value1', 'value2');

-- Multiple records
INSERT INTO table_name (column1, column2) VALUES 
('value1a', 'value2a'),
('value1b', 'value2b');

-- Data Retrieval
SELECT column1, column2 FROM table_name;
SELECT * FROM table_name WHERE condition;
SELECT * FROM table_name ORDER BY column_name;
SELECT * FROM table_name LIMIT 10;

-- Data Updates
UPDATE table_name 
SET column1 = 'new_value' 
WHERE condition;

-- Table Modifications
ALTER TABLE table_name ADD COLUMN column_name DATA_TYPE;

-- Data Deletion
DELETE FROM table_name WHERE condition;

-- Prevent empty values
column_name TEXT NOT NULL

-- Ensure uniqueness
column_name TEXT UNIQUE

-- Set default values
column_name BOOLEAN DEFAULT FALSE

-- Validate data
column_name TEXT CHECK(LENGTH(column_name) > 0)

-- Link to other tables
foreign_key_id INTEGER REFERENCES other_table(id)