Simple CRUD

1. Open the PostgreSQL CLI

• Launch psql in your terminal:

  Copy

  psql -U postgres

• Connect to your database (replace your_database with the name of your database):

  Copy

  \c your_database

---

2. Create the City Table

Execute the following SQL to create the table:

Copy

CREATE TABLE City (
    id SERIAL PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    country VARCHAR(255) NOT NULL,
    population INTEGER
);

---

3. Insert Data (CREATE)

Insert some sample data into the City table:

Copy

INSERT INTO City (name, country, population) VALUES 
('Toronto', 'Canada', 2731571),
('New York', 'USA', 8419600),
('Tokyo', 'Japan', 13929286);

---

4. Read Data (READ)

• Retrieve all records:

  Copy

  SELECT * FROM City;

• Retrieve specific records:

  Copy

  SELECT * FROM City WHERE country = 'Canada';

---

5. Update Data (UPDATE)

• Update the population of a city:

  Copy

  UPDATE City 
  SET population = 2800000 
  WHERE name = 'Toronto';

• Verify the update:

  Copy

  SELECT * FROM City WHERE name = 'Toronto';

---

6. Delete Data (DELETE)

• Delete a record:

  Copy

  DELETE FROM City 
  WHERE name = 'Tokyo';

• Verify the deletion:

  Copy

  SELECT * FROM City WHERE name = 'Tokyo';

---

7. Drop the Table (Optional)

If you want to remove the City table:

Copy

DROP TABLE City;

---