PostgreSQL

In PostgreSQL, the DELETE statement is used to remove one or more rows from a table. You can specify which rows to delete by using the WHERE clause. If you omit the WHERE clause, all the rows in the table will be deleted. In this guide, we'll explore how to use the DELETE statement, with examples based on a banking system consisting of customers, accounts, and transactions.

Steps to use DELETE in PostgreSQL:

1. Basic Syntax: The basic syntax of a DELETE statement in PostgreSQL is as follows:

   DELETE FROM table_name WHERE condition;
   

2. Delete Rows Based on a Condition: Suppose we have a table named customers in a banking system, and we want to delete a customer with a specific ID. The query would be:

   DELETE FROM customers WHERE customer_id = 101;
   

   This deletes the row where customer_id equals 101.

3. Deleting Multiple Rows: You can also delete multiple rows that meet certain conditions. For example, if you want to delete all customers from a specific city:

   DELETE FROM customers WHERE city = 'New York';
   

   This deletes all customers located in New York.

4. Delete All Rows: If you need to delete all rows from a table (effectively emptying the table), you can omit the WHERE clause:

   DELETE FROM accounts;
   

   Be careful when using this command, as it will remove all rows from the accounts table.

5. Deleting Related Records: Suppose you want to delete all transactions related to a specific account. This can be done as follows:

   DELETE FROM transactions WHERE account_id = 5001;
   

   This query removes all transaction records for account ID 5001.

6. Returning Deleted Records: PostgreSQL allows you to return the rows that were deleted using the RETURNING clause. For instance, to get information about deleted customers:

   DELETE FROM customers WHERE status = 'inactive' RETURNING customer_id, name;
   

   This query deletes all inactive customers and returns their customer_id and name.

Example Table Definitions:

CREATE TABLE customers (
  customer_id SERIAL PRIMARY KEY,
  name VARCHAR(100),
  city VARCHAR(50),
  status VARCHAR(20)
);

CREATE TABLE accounts (
  account_id SERIAL PRIMARY KEY,
  customer_id INT REFERENCES customers(customer_id),
  balance NUMERIC(10, 2)
);

CREATE TABLE transactions (
  transaction_id SERIAL PRIMARY KEY,
  account_id INT REFERENCES accounts(account_id),
  amount NUMERIC (10, 2),
  transaction_date DATE
);

These tables represent a simplified banking system where you can practice using the DELETE statement.

This content is ready for integration into your React application with the Markdown structure, making it easier for students to follow the steps and examples.

To gain complete access, login with gmail or outlook, no need of signup. click here