# Step 1: Create a New SQLite Database
sqlite3 library.db

# Step 2: Create a Table
CREATE TABLE books (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    title TEXT NOT NULL,
    author TEXT NOT NULL,
    year INTEGER,
    genre TEXT
);

# Verify the table schema
.schema books

# Step 3: Insert Data into the Table
INSERT INTO books (title, author, year, genre) VALUES ('To Kill a Mockingbird', 'Harper Lee', 1960, 'Fiction');
INSERT INTO books (title, author, year, genre) VALUES ('1984', 'George Orwell', 1949, 'Dystopian');
INSERT INTO books (title, author, year, genre) VALUES ('The Great Gatsby', 'F. Scott Fitzgerald', 1925, 'Classic');
INSERT INTO books (title, author, year, genre) VALUES ('Pride and Prejudice', 'Jane Austen', 1813, 'Romance');

# Verify the inserted data
SELECT * FROM books;

# Step 4: Query the Data
# Select all books
SELECT * FROM books;

# Select books published after 1900
SELECT * FROM books WHERE year > 1900;

# Select books by genre
SELECT * FROM books WHERE genre = 'Fiction';

# Step 5: Update Data
# Update the genre of "1984" to "Science Fiction"
UPDATE books SET genre = 'Science Fiction' WHERE title = '1984';

# Verify the update
SELECT * FROM books WHERE title = '1984';

# Step 6: Delete Data
# Delete "Pride and Prejudice"
DELETE FROM books WHERE title = 'Pride and Prejudice';

# Verify the deletion
SELECT * FROM books;

# Step 7: Drop the Table (Optional)
DROP TABLE books;

# Verify that the table is dropped
.tables

# Step 8: Exit SQLite
.exit