SQL Delete records using subqueries

SQL Deleting records with subqueries

In this page, we are going to discuss, how SUBQUERIES (A SELECT statement within another SELECT statement can be used as a subquery )along with SQL DELETE command can be used to perform a deletion.

Sample tables associated with this page have shown bellow:

+-----------+-------------+-------------+--------------+--------------+-------+-------------+-------------+-------------+---------------+--------------+------------+  
|CUST_CODE  | CUST_NAME   | CUST_CITY   | WORKING_AREA | CUST_COUNTRY | GRADE | OPENING_AMT | RECEIVE_AMT | PAYMENT_AMT |OUTSTANDING_AMT| PHONE_NO     | AGENT_CODE |
+-----------+-------------+-------------+--------------+--------------+-------+-------------+-------------+-------------+---------------+--------------+------------+
| C00013    | Holmes      | London      | London       | UK           |     2 |     6000.00 |     5000.00 |     7000.00 |       4000.00 | BBBBBBB      | A003       |
| C00001    | Micheal     | New York    | New York     | USA          |     2 |     3000.00 |     5000.00 |     2000.00 |       6000.00 | CCCCCCC      | A008       |
| C00020    | Albert      | New York    | New York     | USA          |     3 |     5000.00 |     7000.00 |     6000.00 |       6000.00 | BBBBSBB      | A008       |
| C00025    | Ravindran   | Bangalore   | Bangalore    | India        |     2 |     5000.00 |     7000.00 |     4000.00 |       8000.00 | AVAVAVA      | A011       |
| C00024    | Cook        | London      | London       | UK           |     2 |     4000.00 |     9000.00 |     7000.00 |       6000.00 | FSDDSDF      | A006       |
| C00015    | Stuart      | London      | London       | UK           |     1 |     6000.00 |     8000.00 |     3000.00 |      11000.00 | GFSGERS      | A003       |
| C00002    | Bolt        | New York    | New York     | USA          |     3 |     5000.00 |     7000.00 |     9000.00 |       3000.00 | DDNRDRH      | A008       |
| C00018    | Fleming     | Brisban     | Brisban      | Australia    |     2 |     7000.00 |     7000.00 |     9000.00 |       5000.00 | NHBGVFC      | A005       |
| C00021    | Jacks       | Brisban     | Brisban      | Australia    |     1 |     7000.00 |     7000.00 |     7000.00 |       7000.00 | WERTGDF      | A005       |
| C00019    | Yearannaidu | Chennai     | Chennai      | India        |     1 |     8000.00 |     7000.00 |     7000.00 |       8000.00 | ZZZZBFV      | A010       |
| C00005    | Sasikant    | Mumbai      | Mumbai       | India        |     1 |     7000.00 |    11000.00 |     7000.00 |      11000.00 | 147-25896312 | A002       |
| C00007    | Ramanathan  | Chennai     | Chennai      | India        |     1 |     7000.00 |    11000.00 |     9000.00 |       9000.00 | GHRDWSD      | A010       |
| C00022    | Avinash     | Mumbai      | Mumbai       | India        |     2 |     7000.00 |    11000.00 |     9000.00 |       9000.00 | 113-12345678 | A002       |
| C00004    | Winston     | Brisban     | Brisban      | Australia    |     1 |     5000.00 |     8000.00 |     7000.00 |       6000.00 | AAAAAAA      | A005       |
| C00023    | Karl        | London      | London       | UK           |     0 |     4000.00 |     6000.00 |     7000.00 |       3000.00 | AAAABAA      | A006       |
| C00006    | Shilton     | Torento     | Torento      | Canada       |     1 |    10000.00 |     7000.00 |     6000.00 |      11000.00 | DDDDDDD      | A004       |
| C00010    | Charles     | Hampshair   | Hampshair    | UK           |     3 |     6000.00 |     4000.00 |     5000.00 |       5000.00 | MMMMMMM      | A009       |
| C00017    | Srinivas    | Bangalore   | Bangalore    | India        |     2 |     8000.00 |     4000.00 |     3000.00 |       9000.00 | AAAAAAB      | A007       |
| C00012    | Steven      | San Jose    | San Jose     | USA          |     1 |     5000.00 |     7000.00 |     9000.00 |       3000.00 | KRFYGJK      | A012       |
| C00008    | Karolina    | Torento     | Torento      | Canada       |     1 |     7000.00 |     7000.00 |     9000.00 |       5000.00 | HJKORED      | A004       |
| C00003    | Martin      | Torento     | Torento      | Canada       |     2 |     8000.00 |     7000.00 |     7000.00 |       8000.00 | MJYURFD      | A004       |
| C00009    | Ramesh      | Mumbai      | Mumbai       | India        |     3 |     8000.00 |     7000.00 |     3000.00 |      12000.00 | Phone No     | A002       |
| C00014    | Rangarappa  | Bangalore   | Bangalore    | India        |     2 |     8000.00 |    11000.00 |     7000.00 |      12000.00 | AAAATGF      | A001       |
| C00016    | Venkatpati  | Bangalore   | Bangalore    | India        |     2 |     8000.00 |    11000.00 |     7000.00 |      12000.00 | JRTVFDD      | A007       |
| C00011    | Sundariya   | Chennai     | Chennai      | India        |     3 |     7000.00 |    11000.00 |     7000.00 |      11000.00 | PPHGRTS      | A010       |
+-----------+-------------+-------------+--------------+--------------+-------+-------------+-------------+-------------+---------------+--------------+------------+

+------------+----------------------+--------------------+------------+-----------------+---------+
| AGENT_CODE | AGENT_NAME           | WORKING_AREA       | COMMISSION | PHONE_NO        | COUNTRY |
+------------+----------------------+--------------------+------------+-----------------+---------+
| A007       | Ramasundar           | Bangalore          |       0.15 | 077-25814763    |         |
| A003       | Alex                 | London             |       0.13 | 075-12458969    |         |
| A008       | Alford               | New York           |       0.12 | 044-25874365    |         |
| A011       | Ravi Kumar           | Bangalore          |       0.15 | 077-45625874    |         |
| A010       | Santakumar           | Chennai            |       0.14 | 007-22388644    |         |
| A012       | Lucida               | San Jose           |       0.12 | 044-52981425    |         |
| A005       | Anderson             | Brisban            |       0.13 | 045-21447739    |         |
| A001       | Subbarao             | Bangalore          |       0.14 | 077-12346674    |         |
| A002       | Mukesh               | Mumbai             |       0.11 | 029-12358964    |         |
| A006       | McDen                | London             |       0.15 | 078-22255588    |         |
| A004       | Ivan                 | Torento            |       0.15 | 008-22544166    |         |
| A009       | Benjamin             | Hampshair          |       0.11 | 008-22536178    |         |
+------------+----------------------+--------------------+------------+-----------------+---------+

+-----------+-------------+-------------+--------------+--------------+-------+-------------+-------------+-------------+---------------+--------------+------------+  
|CUST_CODE  | CUST_NAME   | CUST_CITY   | WORKING_AREA | CUST_COUNTRY | GRADE | OPENING_AMT | RECEIVE_AMT | PAYMENT_AMT |OUTSTANDING_AMT| PHONE_NO     | AGENT_CODE |
+-----------+-------------+-------------+--------------+--------------+-------+-------------+-------------+-------------+---------------+--------------+------------+
| C00013    | Holmes      | London      | London       | UK           |     2 |     6000.00 |     5000.00 |     7000.00 |       4000.00 | BBBBBBB      | A003       |
| C00001    | Micheal     | New York    | New York     | USA          |     2 |     3000.00 |     5000.00 |     2000.00 |       6000.00 | CCCCCCC      | A008       |
| C00020    | Albert      | New York    | New York     | USA          |     3 |     5000.00 |     7000.00 |     6000.00 |       6000.00 | BBBBSBB      | A008       |
| C00025    | Ravindran   | Bangalore   | Bangalore    | India        |     2 |     5000.00 |     7000.00 |     4000.00 |       8000.00 | AVAVAVA      | A011       |
| C00024    | Cook        | London      | London       | UK           |     2 |     4000.00 |     9000.00 |     7000.00 |       6000.00 | FSDDSDF      | A006       |
| C00015    | Stuart      | London      | London       | UK           |     1 |     6000.00 |     8000.00 |     3000.00 |      11000.00 | GFSGERS      | A003       |
| C00002    | Bolt        | New York    | New York     | USA          |     3 |     5000.00 |     7000.00 |     9000.00 |       3000.00 | DDNRDRH      | A008       |
| C00018    | Fleming     | Brisban     | Brisban      | Australia    |     2 |     7000.00 |     7000.00 |     9000.00 |       5000.00 | NHBGVFC      | A005       |
| C00021    | Jacks       | Brisban     | Brisban      | Australia    |     1 |     7000.00 |     7000.00 |     7000.00 |       7000.00 | WERTGDF      | A005       |
| C00019    | Yearannaidu | Chennai     | Chennai      | India        |     1 |     8000.00 |     7000.00 |     7000.00 |       8000.00 | ZZZZBFV      | A010       |
| C00005    | Sasikant    | Mumbai      | Mumbai       | India        |     1 |     7000.00 |    11000.00 |     7000.00 |      11000.00 | 147-25896312 | A002       |
| C00007    | Ramanathan  | Chennai     | Chennai      | India        |     1 |     7000.00 |    11000.00 |     9000.00 |       9000.00 | GHRDWSD      | A010       |
| C00022    | Avinash     | Mumbai      | Mumbai       | India        |     2 |     7000.00 |    11000.00 |     9000.00 |       9000.00 | 113-12345678 | A002       |
| C00004    | Winston     | Brisban     | Brisban      | Australia    |     1 |     5000.00 |     8000.00 |     7000.00 |       6000.00 | AAAAAAA      | A005       |
| C00023    | Karl        | London      | London       | UK           |     0 |     4000.00 |     6000.00 |     7000.00 |       3000.00 | AAAABAA      | A006       |
| C00006    | Shilton     | Torento     | Torento      | Canada       |     1 |    10000.00 |     7000.00 |     6000.00 |      11000.00 | DDDDDDD      | A004       |
| C00010    | Charles     | Hampshair   | Hampshair    | UK           |     3 |     6000.00 |     4000.00 |     5000.00 |       5000.00 | MMMMMMM      | A009       |
| C00017    | Srinivas    | Bangalore   | Bangalore    | India        |     2 |     8000.00 |     4000.00 |     3000.00 |       9000.00 | AAAAAAB      | A007       |
| C00012    | Steven      | San Jose    | San Jose     | USA          |     1 |     5000.00 |     7000.00 |     9000.00 |       3000.00 | KRFYGJK      | A012       |
| C00008    | Karolina    | Torento     | Torento      | Canada       |     1 |     7000.00 |     7000.00 |     9000.00 |       5000.00 | HJKORED      | A004       |
| C00003    | Martin      | Torento     | Torento      | Canada       |     2 |     8000.00 |     7000.00 |     7000.00 |       8000.00 | MJYURFD      | A004       |
| C00009    | Ramesh      | Mumbai      | Mumbai       | India        |     3 |     8000.00 |     7000.00 |     3000.00 |      12000.00 | Phone No     | A002       |
| C00014    | Rangarappa  | Bangalore   | Bangalore    | India        |     2 |     8000.00 |    11000.00 |     7000.00 |      12000.00 | AAAATGF      | A001       |
| C00016    | Venkatpati  | Bangalore   | Bangalore    | India        |     2 |     8000.00 |    11000.00 |     7000.00 |      12000.00 | JRTVFDD      | A007       |
| C00011    | Sundariya   | Chennai     | Chennai      | India        |     3 |     7000.00 |    11000.00 |     7000.00 |      11000.00 | PPHGRTS      | A010       |
+-----------+-------------+-------------+--------------+--------------+-------+-------------+-------------+-------------+---------------+--------------+------------+

+------------+----------------------+--------------------+------------+-----------------+---------+
| AGENT_CODE | AGENT_NAME           | WORKING_AREA       | COMMISSION | PHONE_NO        | COUNTRY |
+------------+----------------------+--------------------+------------+-----------------+---------+
| A007       | Ramasundar           | Bangalore          |       0.15 | 077-25814763    |         |
| A003       | Alex                 | London             |       0.13 | 075-12458969    |         |
| A008       | Alford               | New York           |       0.12 | 044-25874365    |         |
| A011       | Ravi Kumar           | Bangalore          |       0.15 | 077-45625874    |         |
| A010       | Santakumar           | Chennai            |       0.14 | 007-22388644    |         |
| A012       | Lucida               | San Jose           |       0.12 | 044-52981425    |         |
| A005       | Anderson             | Brisban            |       0.13 | 045-21447739    |         |
| A001       | Subbarao             | Bangalore          |       0.14 | 077-12346674    |         |
| A002       | Mukesh               | Mumbai             |       0.11 | 029-12358964    |         |
| A006       | McDen                | London             |       0.15 | 078-22255588    |         |
| A004       | Ivan                 | Torento            |       0.15 | 008-22544166    |         |
| A009       | Benjamin             | Hampshair          |       0.11 | 008-22536178    |         |
+------------+----------------------+--------------------+------------+-----------------+---------+

   ORD_NUM ORD_AMOUNT ADVANCE_AMOUNT ORD_DATE  CUST_CODE       AGENT_CODE      ORD_DESCRIPTION
---------- ---------- -------------- --------- --------------- --------------- -----------------
    200114       3500           2000 15-AUG-08 C00002          A008
    200122       2500            400 16-SEP-08 C00003          A004
    200118        500            100 20-JUL-08 C00023          A006
    200119       4000            700 16-SEP-08 C00007          A010
    200121       1500            600 23-SEP-08 C00008          A004
    200130       2500            400 30-JUL-08 C00025          A011
    200134       4200           1800 25-SEP-08 C00004          A005
    200108       4000            600 15-FEB-08 C00008          A004
    200103       1500            700 15-MAY-08 C00021          A005
    200105       2500            500 18-JUL-08 C00025          A011
    200109       3500            800 30-JUL-08 C00011          A010
    200101       3000           1000 15-JUL-08 C00001          A008
    200111       1000            300 10-JUL-08 C00020          A008
    200104       1500            500 13-MAR-08 C00006          A004
    200106       2500            700 20-APR-08 C00005          A002
    200125       2000            600 10-OCT-08 C00018          A005
    200117        800            200 20-OCT-08 C00014          A001
    200123        500            100 16-SEP-08 C00022          A002
    200120        500            100 20-JUL-08 C00009          A002
    200116        500            100 13-JUL-08 C00010          A009
    200124        500            100 20-JUN-08 C00017          A007
    200126        500            100 24-JUN-08 C00022          A002
    200129       2500            500 20-JUL-08 C00024          A006
    200127       2500            400 20-JUL-08 C00015          A003
    200128       3500           1500 20-JUL-08 C00009          A002
    200135       2000            800 16-SEP-08 C00007          A010
    200131        900            150 26-AUG-08 C00012          A012
    200133       1200            400 29-JUN-08 C00009          A002
    200100       1000            600 08-JAN-08 C00015          A003
    200110       3000            500 15-APR-08 C00019          A010
    200107       4500            900 30-AUG-08 C00007          A010
    200112       2000            400 30-MAY-08 C00016          A007
    200113       4000            600 10-JUN-08 C00022          A002
    200102       2000            300 25-MAY-08 C00012          A012

Example:

To remove rows from the table 'customer1' with following conditions -

1. 'agent_code' should be any 'agent_code' from 'agents' table which satisfies the condition bellow :

2. 'working_area' of 'agents' table must be 'London',

the following SQL statement can be used:

SQL Code:

-- This SQL code deletes records from the 'customer1' table where the 'agent_code' column value matches any agent code from a subquery result.
DELETE FROM customer1 -- This line specifies the action of deleting records from the 'customer1' table.
WHERE agent_code=ANY( -- This line specifies the condition for deletion, selecting only rows where the 'agent_code' column value matches any value returned by the subquery.
SELECT agent_code FROM agents -- This subquery selects the 'agent_code' column from the 'agents' table.
WHERE working_area='London'); -- This subquery filters the 'agents' table to only include rows where the 'working_area' column value is 'London'.

Explanation:

• This SQL code performs a deletion operation on a database table named 'customer1'.
• It removes records from the table where the 'agent_code' column value matches any agent code associated with agents working in London.
• The operation is based on a subquery that selects agent codes from the 'agents' table where the working area is 'London'. The main deletion query then deletes records where the 'agent_code' matches any of these selected codes.
• The 'DELETE FROM' statement is used to remove records from the specified table.
• The 'WHERE' clause is used to specify the condition that determines which records will be deleted. In this case, records where the 'agent_code' column value matches any agent code associated with agents working in London will be deleted. The 'ANY' keyword is used to compare the 'agent_code' column value with multiple values returned by the subquery.

Output:

SQL delete records using subqueries with alias

In this page, we are going to discuss, how table aliases( when two or more tables used in a query, then alias makes it easy to read and write with a short name which comes after the table name after the FROM keyword) can be used with SUBQUERIES (A SELECT statement within another SELECT statement can be used as a subquery ), and with the help of subqueries SQL DELETE command can be used to delete records.

Example:

To remove rows from the table 'agent1' with following conditions -

1. 'da' and 'cu' are the aliases for the table 'agent1' and 'customer'

2. check the existence of the subquery is true or false. which satisfies the condition bellow :

3. 'grade' of 'customer' table must be 3,

4. 'agent_code' of 'agent1' table and 'agent_code' of 'customer' table should not be same,

the following SQL statement can be used:

SQL Code:

-- This SQL code deletes records from the 'agent1' table based on a condition involving data from another table 'customer'.
DELETE FROM agent1 da -- This line specifies the action of deleting records from the 'agent1' table, aliasing it as 'da'.
WHERE EXISTS( -- This line specifies the condition for deletion, based on the existence of rows returned by the subquery.
SELECT * FROM customer cu -- This subquery selects all columns from the 'customer' table, aliasing it as 'cu'.
WHERE grade=3 -- This subquery filters the 'customer' table to only include rows where the 'grade' column value is 3.
AND da.agent_code<>cu.agent_code); -- This subquery further filters the result to include only rows where the 'agent_code' column value of 'da' is not equal to the 'agent_code' column value of 'cu'.

Explanation:

• This SQL code performs a deletion operation on the 'agent1' table based on a condition involving data from the 'customer' table.
• It removes records from 'agent1' where there exists a record in the 'customer' table with grade 3 and a different agent code.
• The operation uses a subquery to check for the existence of such records in the 'customer' table.
• The 'DELETE FROM' statement is used to remove records from the specified table ('agent1').
• The 'WHERE EXISTS' clause is used to specify the condition that determines which records will be deleted. In this case, records from 'agent1' will be deleted if there exists a record in 'customer' with grade 3 and a different agent code.

Output:

SQL delete records using subqueries with alias and IN

In this page we are going to discuss, how rows can be removed from a table by SQL DELETE statement with the use of IN operator and SUBQUERIES.

Example:

To remove rows from the table 'agent1' with following conditions -

1. 'da' and 'cu' are the aliases of 'agent1' and 'customer' table,

2. check the number 3 is in the result of the subquery which satisfies the condition bellow :

3. 'agent_code' of 'agent1' table and 'agent_code' of 'customer' table should not be same,

the following SQL statement can be used:

SQL Code:

-- This SQL code deletes records from the 'agent1' table based on a condition involving data from another table 'customer'.
DELETE FROM agent1 da -- This line specifies the action of deleting records from the 'agent1' table, aliasing it as 'da'.
WHERE 3 IN( -- This line specifies the condition for deletion, checking if the value 3 is present in the result set returned by the subquery.
SELECT grade FROM customer cu -- This subquery selects the 'grade' column from the 'customer' table, aliasing it as 'cu'.
WHERE agent1.agent_code<>customer.agent_code); -- This subquery filters the 'customer' table to only include rows where the 'agent_code' column value of 'agent1' is not equal to the 'agent_code' column value of 'customer'.

Explanation:

• This SQL code performs a deletion operation on the 'agent1' table based on a condition involving data from the 'customer' table.


• It removes records from 'agent1' where the value 3 is present in the 'grade' column of 'customer' with a different agent code.


• The operation uses a subquery to fetch 'grade' values from the 'customer' table.


• The 'DELETE FROM' statement is used to remove records from the specified table ('agent1').


• The 'WHERE IN' clause is used to specify the condition that determines which records will be deleted. In this case, records from 'agent1' will be deleted if the value 3 is present in the 'grade' column of 'customer' with a different agent code.

Output:

SQL delete records using subqueries with alias and MIN

In this page, we are going to discuss, how rows can be removed from a table by SQL DELETE statement along with the SQL MIN() function.

Example:

To remove rows from the table 'agent1' with following conditions -

1. 'orders' table used as alias 'a' and alias 'b',

2. 'agent_code' of 'agent1' should be within the 'agent_code' in alias 'a' which satisfies the condition bellow :

i) 'ord_amount' of alias 'a' must be equal to the minimum 'ord_amount' of alias 'b' which satisfies the condition bellow :

a) 'ord_date' of alias 'a' and alias 'b' must be equal,

the following SQL statement can be used :

SQL Code:

-- This SQL code deletes records from the 'agent1' table based on a condition involving data from another table 'orders'.
DELETE FROM agent1 -- This line specifies the action of deleting records from the 'agent1' table.
WHERE agent_code IN -- This line specifies the condition for deletion, checking if the 'agent_code' column value is present in the result set returned by the subquery.
(SELECT agent_code FROM orders a -- This subquery selects the 'agent_code' column from the 'orders' table, aliasing it as 'a'.
WHERE ord_amount= -- This subquery filters the 'orders' table to only include rows where the 'ord_amount' column value matches a specific condition.
(SELECT MIN(ord_amount) FROM orders b -- This nested subquery selects the minimum 'ord_amount' value from the 'orders' table, aliasing it as 'b'.
WHERE a.ord_date=b.ord_date)); -- This nested subquery further filters the result to only include rows where the 'ord_date' column values of 'a' and 'b' match.

Explanation:

• This SQL code performs a deletion operation on the 'agent1' table based on a condition involving data from the 'orders' table.


• It removes records from 'agent1' where the 'agent_code' column value is present in the result set of agents associated with orders having the minimum order amount on a specific date.


• The operation uses nested subqueries to determine the agents associated with the minimum order amount on a given date.


• The 'DELETE FROM' statement is used to remove records from the specified table ('agent1').


• The 'WHERE IN' clause is used to specify the condition that determines which records will be deleted. In this case, records from 'agent1' will be deleted if the 'agent_code' column value is present in the result set of agents associated with orders having the minimum order amount on a specific date.

Output:

SQL delete records using subqueries with alias and MIN and COUNT

In this page, we are going to discuss, how rows can be removed from a table by SQL DELETE statement along with the SQL MIN() and COUNT() function.

Example:

To remove rows from the table 'agent1' with following conditions -

1. 'orders' table used as alias 'a' and alias 'b'

2. 'agent_code' of 'agent1' should be within the 'agent_code' in alias 'a' which satisfies the condition bellow:

i) 'ord_amount' of alias 'a' must be equal to the minimum 'ord_amount' of alias 'b' which satisfies the condition bellow :

a) 'ord_date' of alias 'a' and alias 'b' must be equal

ii) the number 1 should be less than the number of 'ord_num' form alias 'b' which satisfies the condition bellow :

a) 'ord_date' of alias 'a' and alias 'b' must be equal,

the following SQL statement can be used :

SQL Code:

-- This SQL code deletes records from the 'agent1' table based on a complex condition involving data from another table 'orders'.
DELETE FROM agent1 -- This line specifies the action of deleting records from the 'agent1' table.
WHERE agent_code IN( -- This line specifies the condition for deletion, checking if the 'agent_code' column value is present in the result set returned by the subquery.
SELECT agent_code FROM orders a -- This subquery selects the 'agent_code' column from the 'orders' table, aliasing it as 'a'.
WHERE ord_amount= -- This subquery filters the 'orders' table to only include rows where the 'ord_amount' column value matches a specific condition.
(SELECT MIN(ord_amount) FROM orders b -- This nested subquery selects the minimum 'ord_amount' value from the 'orders' table, aliasing it as 'b'.
WHERE a.ord_date=b.ord_date) -- This nested subquery further filters the result to only include rows where the 'ord_date' column values of 'a' and 'b' match.
AND 1<( -- This condition checks if the count of orders with the same order date is greater than 1.
SELECT COUNT(ord_num) FROM orders b -- This subquery counts the number of orders with the same order date, aliasing it as 'b'.
WHERE a.ord_date=b.ord_date)); -- This subquery further filters the result to only include rows where the 'ord_date' column values of 'a' and 'b' match.

Explanation:

• This SQL code performs a deletion operation on the 'agent1' table based on a complex condition involving data from the 'orders' table.


• It removes records from 'agent1' where the 'agent_code' column value is present in the result set of agents associated with orders having the minimum order amount on a specific date and where there is more than one order on that date.


• The operation uses nested subqueries to determine the agents associated with the minimum order amount and where the count of orders on the same date is greater than 1.


• The 'DELETE FROM' statement is used to remove records from the specified table ('agent1').


• The 'WHERE IN' clause is used to specify the condition that determines which records will be deleted. In this case, records from 'agent1' will be deleted if the 'agent_code' column value is present in the result set of agents associated with orders having the minimum order amount on a specific date and where there is more than one order on that date.

Output:

Outputs of the said SQL statement shown here is taken by using Oracle Database 10g Express Edition.

See our Model Database

Check out our 1000+ SQL Exercises with solution and explanation to improve your skills.

Previous: SQL Delete
Next: SQL JOINING Introduction