10 Best MySQL Projects

-- Personal Contact Book Database
-- Demonstrates basic table creation, INSERT, SELECT, and UPDATE operations

-- Create the database
CREATE DATABASE IF NOT EXISTS contact_book;
USE contact_book;

-- Create contacts table
CREATE TABLE contacts (
    contact_id INT AUTO_INCREMENT PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    phone VARCHAR(20),
    email VARCHAR(100),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Insert sample contacts
INSERT INTO contacts (first_name, last_name, phone, email) VALUES
('John', 'Doe', '555-0101', 'john.doe@email.com'),
('Jane', 'Smith', '555-0102', 'jane.smith@email.com'),
('Bob', 'Johnson', '555-0103', 'bob.j@email.com'),
('Alice', 'Williams', '555-0104', 'alice.w@email.com');

-- SELECT: View all contacts
SELECT * FROM contacts;

-- SELECT: Search for specific contact
SELECT first_name, last_name, phone, email 
FROM contacts 
WHERE last_name = 'Smith';

-- UPDATE: Change phone number for a contact
UPDATE contacts 
SET phone = '555-9999' 
WHERE contact_id = 1;

-- Verify the update
SELECT * FROM contacts WHERE contact_id = 1;

-- DELETE: Remove a contact (optional)
DELETE FROM contacts WHERE contact_id = 4;

-- View final contact list
SELECT contact_id, CONCAT(first_name, ' ', last_name) AS full_name, phone, email 
FROM contacts
ORDER BY last_name;