Which command in SQL is used to delete a tables entire data and structure?

Procedure

To delete one or more rows in a table:

• Use the DELETE statement with a WHERE clause to specify a search condition.

  The DELETE statement removes zero or more rows of a table, depending on how many rows satisfy the search condition that you specify in the WHERE clause.

  You can use DELETE with a WHERE clause to remove only selected rows from a declared temporary table, but not from a created temporary table.

  The following DELETE statement deletes each row in the YEMP table that has an employee number '000060'.

  DELETE FROM YEMP
    WHERE EMPNO = '000060';

  When this statement executes, Db2 deletes any row from the YEMP table that meets the search condition.

  If Db2 finds an error while executing your DELETE statement, it stops deleting data and returns error codes in the SQLCODE and SQLSTATE variables or related fields in the SQLCA. The data in the table does not change.

  If the DELETE is successful, SQLERRD(3) in the SQLCA contains the number of deleted rows. This number includes only the number of deleted rows in the table that is specified in the DELETE statement. Rows that are deleted (in other tables) according to the CASCADE rule are not included in SQLERRD(3).

To delete every row in a table:

• Use the DELETE statement without specifying a WHERE clause.

  With segmented table spaces, deleting all rows of a table is very fast.

  The following DELETE statement deletes every row in the YDEPT table:

  DELETE FROM YDEPT;

  If the statement executes, the table continues to exist (that is, you can insert rows into it), but it is empty. All existing views and authorizations on the table remain intact when using DELETE.

• Use the TRUNCATE statement.

  The TRUNCATE statement can provide the following advantages over a DELETE statement:

  • The TRUNCATE statement can ignore delete triggers
  • The TRUNCATE statement can perform an immediate commit
  • The TRUNCATE statement can keep storage allocated for the table

  The TRUNCATE statement does not, however, reset the count for an automatically generated value for an identity column on the table. If 14872 was the next identity column value to be generated before a TRUNCATE statement, 14872 would be the next value generated after the TRUNCATE statement.

  Suppose that you need to empty the data from an old inventory table, regardless of any existing delete triggers, and you need to make the space that is allocated for the table available for other uses. Use the following TRUNCATE statement.

  TRUNCATE INVENTORY_TABLE
     IGNORE DELETE TRIGGERS
     DROP STORAGE;

  Suppose that you need to empty the data from an old inventory table permanently, regardless of any existing delete triggers, and you need to preserve the space that is allocated for the table. You need the emptied data to be completely unavailable, so that a ROLLBACK statement cannot return the data. Use the following TRUNCATE statement.

  TRUNCATE INVENTORY_TABLE
     REUSE STORAGE
     IGNORE DELETE TRIGGERS
     IMMEDIATE;

• Use the DROP TABLE statement.

  DROP TABLE drops the specified table and all related views and authorizations, which can invalidate plans and packages.

To drop columns from a table:

• Use the ALTER TABLE statement with the DROP COLUMN clause.

  Because dropping a column from a table is a pending change to the definition of the table, the table space is placed in advisory REORG-pending status (AREOR). When the pending change is applied (by running the REORG utility with the SHRLEVEL CHANGE or REFERENCE options), the column is dropped from the table, and any dependent packages and statements in the dynamic statement cache are invalidated.

  The DELETE statement is used to delete rows from a table. If you want to remove a specific row from a table you should use WHERE condition.

  But if you do not specify the WHERE condition it will remove all the rows from the table.

  There are some more terms similar to DELETE statement like as DROP statement and TRUNCATE statement but they are not exactly same there are some differences between them.

  ---

  Difference between DELETE and TRUNCATE statements

  There is a slight difference b/w delete and truncate statement. The DELETE statement only deletes the rows from the table based on the condition defined by WHERE clause or delete all the rows from the table when condition is not specified.

  But it does not free the space containing by the table.

  The TRUNCATE statement: it is used to delete all the rows from the table and free the containing space.

  Let's see an "employee" table.

  Emp_idNameAddressSalary1AryanAllahabad220002ShurabhiVaranasi130003PappuDelhi24000

  Execute the following query to truncate the table:

  ---

  Difference b/w DROP and TRUNCATE statements

  When you use the drop statement it deletes the table's row together with the table's definition so all the relationships of that table with other tables will no longer be valid.

  When you drop a table:

  • Table structure will be dropped
  • Relationship will be dropped
  • Integrity constraints will be dropped
  • Access privileges will also be dropped

  On the other hand when we TRUNCATE a table, the table structure remains the same, so you will not face any of the above problems.

  Which command in SQL is used to delete a table entire data and structure Mcq?

  Explanation: TRUNCATE command is used to delete all rows and free up space from a table.

  Which SQL statement is used to delete entire data and structure?

  The DELETE statement is used to delete existing records in a table.