---
title: "How to Drop [or] Alter Table: Sample SQL Queries"
description: "Explained SQL queries and syntax to drop a table or modify a table. And added SQL queries helpful to rename a table and truncate a table here."
url: https://srinimf.com/2022/07/03/2-effective-drop-a-table-or-modify-sql-queries/
date: 2022-07-03
modified: 2026-10-10
author: "Srini"
image: https://i0.wp.com/srinimf.com/wp-content/uploads/2022/07/pexels-photo-10041253.jpeg?fit=1880%2C1256&ssl=1
categories: ["SQL"]
tags: ["Drop or modify table"]
type: post
lang: en
---

# How to Drop [or] Alter Table: Sample SQL Queries

Here are two SQL queries to drop or modify a table.

IN THIS PAGE

1. [Drop a Table](https://srinimf.com/2022/07/03/2-effective-drop-a-table-or-modify-sql-queries/#drop-a-table)
2. [ALTER a Table](https://srinimf.com/2022/07/03/2-effective-drop-a-table-or-modify-sql-queries/#alter-a-table)
3. [RENAME & TRUNCATE a Table](https://srinimf.com/2022/07/03/2-effective-drop-a-table-or-modify-sql-queries/#rename-truncate-a-table)

Related Alter Index in DB2](https://srinimf.com/2022/03/19/everything-you-need-to-know-about-alter-index-in-db2/)

Don’t Miss

- Selectively you can fetch odd and even records from table. Here’s **[SQL query](https://srinimf.com/2022/04/30/how-to-write-sql-query-to-get-even-and-odd-records/)**helpful.
- SQL data types CHAR Vs VARCHAR the top **[differences.](https://srinimf.com/2022/01/03/exclusive-char-vs-varchar-in-db2-sql/)**
- Sub-queries helpful to fetch data from more than one table. Check out [how to](https://srinimf.com/2016/01/30/how-to-change-table-column-data-type-in-db2/) write complex **[SQL sub-query](https://srinimf.com/2021/11/29/how-to-write-complex-sql-sub-query/)**

## Drop a Table

The widely used SQL query to drop a table syntax is as below.

```
DROP TABLE [<Schema>.] <Table_Name> [CASCADE CONSTRAINTS];
```

**Explanation**

- In place of TABLE_NAME, use your table name
- In place of schema, use your schema
- The Cascade constraints drop all referential integrity constraints that refer to primary and unique keys in the dropped table

**Example**

I am here deleting the HR_OPER table from the database. I used CASCADE constraints, which deletes all the constraints.

Related[3 Must-know Approaches to Delete a Table](https://srinimf.com/2023/01/02/3-approaches-to-delete-table-in-sql/)

```
DROP TABLE HR_OPER CASCADE CONSTRAINTS;
```

[![Drop a table](https://i0.wp.com/srinimf.com/wp-content/uploads/2022/07/drop-modify-rename-truncate.jpg?resize=500%2C500&ssl=1)](2%20Effective%20SQL%20Queries%20to%20Drop%20or%20Modify%20a%20Table)*Drop a table*

## ALTER a Table

The statement ALTER TABLE you can use to modify a table. Below are the possible ALTER TABLE operations you can apply to a Table.

Basically, the alter table modifies a table.

![Syntax for alter table](https://i0.wp.com/srinimf.com/wp-content/uploads/2022/07/image.png?resize=642%2C274&ssl=1)*Syntax to alter a table*

**Example**

```
ALTER TABLE Student ADD StudHall# CHAR (4) ADD FOREIGN KEY (StudHall#) REFERENCES Hall (Hall#);
```

## RENAME & TRUNCATE a Table

As part of modifying a table, you can rename an existing table. In addition to that, you can truncate all the records in a table. We will see with syntax how you can do those.

**The syntax for renaming and truncating**

![Syntax for rename and truncate](https://i0.wp.com/srinimf.com/wp-content/uploads/2022/07/image-1.png?resize=734%2C157&ssl=1)*The syntax to rename and truncate a table*

**Example**

```
RENAME HR1 TO HR2;
TRUNCATE TABLE HR_OPER;
```

Related posts

- [How to Alter Table: Top SQL Queries](https://srinimf.com/2018/10/25/sql-alter-statement-top-examples-useful-to-write-any-complex-query/)DateOctober 25, 2018
- [DB2 SQL: Error Code +100 and Resolution](https://srinimf.com/2018/09/21/sql-query-complex-where-condition-you-need-to-modify-a-row/)DateSeptember 21, 2018
- [SQL Make Sure These in Your Course Curriculum](https://srinimf.com/2012/06/26/sql-topics-to-learn-mainframe-and-dwh-professionals/)DateJune 26, 2012

### Share this:

-
- [Tweet](https://twitter.com/share)
- [![](https://i0.wp.com/assets.pinterest.com/images/pidgets/pinit_fg_en_rect_gray_20.png)](https://www.pinterest.com/pin/create/link/?url=https%3A%2F%2Fsrinimf.com%2F2022%2F07%2F03%2F2-effective-drop-a-table-or-modify-sql-queries%2F&media=https%3A%2F%2Fi0.wp.com%2Fsrinimf.com%2Fwp-content%2Fuploads%2F2022%2F07%2Fpexels-photo-10041253.jpeg%3Ffit%3D1200%252C802%26ssl%3D1&description=How%20to%20Drop%20%5Bor%5D%20Alter%20Table%3A%20Sample%20SQL%20Queries)
-
- [Share on Tumblr](https://www.tumblr.com/share)
- [Share on WhatsApp (Opens in new window) WhatsApp](https://srinimf.com/2022/07/03/2-effective-drop-a-table-or-modify-sql-queries/?share=jetpack-whatsapp)
- [More](#)
-

- [Print (Opens in new window) Print](https://srinimf.com/2022/07/03/2-effective-drop-a-table-or-modify-sql-queries/?share=print)
- [Share on Reddit (Opens in new window) Reddit](https://srinimf.com/2022/07/03/2-effective-drop-a-table-or-modify-sql-queries/?share=reddit)
- [Share on Telegram (Opens in new window) Telegram](https://srinimf.com/2022/07/03/2-effective-drop-a-table-or-modify-sql-queries/?share=telegram)
- [Email a link to a friend (Opens in new window) Email](mailto:?subject=%5BShared%20Post%5D%20How%20to%20Drop%20%5Bor%5D%20Alter%20Table%3A%20Sample%20SQL%20Queries&body=https%3A%2F%2Fsrinimf.com%2F2022%2F07%2F03%2F2-effective-drop-a-table-or-modify-sql-queries%2F&share=email)
-

### Like this:

Like Loading…