Difference Between DROP DELETE and TRUNCATE in SQL

 

SQL Data Manipulation Methods: Delete, Truncate, and Drop Explained

Today we will discuss about CREATE, DELETE, DROP , TRUNCATE, ALTER and SELECT with real-time interview questions and hands-on execution with different set of data.

CREATE

Create is a DDL Command. Creates a new table or using existing table in the database.

Ways to create table

·         Using the CREATE TABLE statement:

This is the most common way to create a table, and it allows you to specify the name of the table, the names and data types of the columns, and any constraints on the columns.

 

CREATE TABLE customers (

  customer_id INT NOT NULL AUTO_INCREMENT,

  name VARCHAR(255) NOT NULL,

  email VARCHAR(255) NOT NULL,

  PRIMARY KEY (customer_id)

);

Using existing table

This is another method to create table using existing table.

Here we have to use either 1=1 or 1=2

 

·         create table new_table as select * from existing_table;

·         create table new_table as select * from existing_table where 1=1;

·         create table new_table as select * from existing_table where 1=2;

 

·         Then what is the difference between 1=1 and 1=2?

·         if 1=1 used while creating table, it will create table and data also loaded automatically.

·         if 1=2 used only create table structure, sometimes we dont required data then used this option.

 

DELETE.

·         Delete is DML command.

·         Removes specific rows from a table based on a condition

·         Slower than TRUNCATE

·         Reversible using ROLLBACK

·         Does not affect table structure

·         Indexes are updated

·         DELETE permission on the table

·         Deleting rows that meet specific criteria.

 

Syntax : DELETE FROM table_name WHERE condition;

DROP

·         Drop is a Data Definition Language (DDL)

·         Removes a table and its structure

·         Fastest

·         Not reversible

·         Removes table structure

·         Indexes are dropped and not recreated

·         DROP permission on the table

·         Removing a table that is no longer needed

 

Syntax: DROP TABLE table_name;

 

 

TRUNCATE

·         Data Definition Language (DDL)

·         Removes all rows from a table

·         Faster than DELETE and DROP

·         Not reversible

·         Does not affect table structure

·         Indexes are dropped and recreated

·         TRUNCATE permission on the table

·         Removing all rows from a table quickly

 

Syntax: TRUNCATE TABLE table_name;

 

ALTER

·         Alter is DDL command.

·         Alter is used to modify the structure of an existing database object, such as a table, index, or view. It allows you to add, modify, or delete columns, constraints, indexes, and other attributes of a database object.

·          

Here is the basic syntax of the ALTER statement in SQL:

1.     Add a new column to a table:

ALTER TABLE customers ADD phone varchar(10);

 

2.      Modify an existing column in a table:(with data and without data)

ALTER TABLE customers MODIFY COLUMN name varchar(255);

 

3.      Drop a column from a table:

ALTER TABLE customers DROP COLUMN country;

 

4.      Rename a column in a table:

ALTER TABLE customers RENAME COLUMN customer_id TO c_id;

 

5.      Add a primary key constraint to a table:

ALTER TABLE customers ADD PRIMARY KEY (c_id);

 

6.      Add a foreign key constraint to a table:

ALTER TABLE orders ADD FOREIGN KEY (customer_id) REFERENCES customers (c_id);

 

7.      Change the storage engine for a table:

ALTER TABLE customers ENGINE = MyISAM;

 

More complex ALTER command:

 

SQL

ALTER TABLE customers

  MODIFY COLUMN name varchar(255),

  ADD COLUMN country varchar(100),

  DROP COLUMN phone,

  RENAME COLUMN c_id TO customer_id,

  ADD PRIMARY KEY (customer_id),

  ADD FOREIGN KEY (country) REFERENCES countries (code);

 

SELECT

·         Select is a DML command.

·         Select statement is key component of SQL language and is used to retrieve data.

 

The basic syntax for the SELECT statement is as follows:

 

Select col,col1,col2 from table;

Select count(1) from table;

Select count(*) from table;

Select count(a) from table;

Select count(‘a’) from table;

```

 

We can add optional clauses to the select statement if needed like

Where

Group by

Having

Order by

 ------------------------------------------

-- create table 

CREATE TABLE T1(ID NUMBER(4), NAME CHAR(10));

CREATE TABLE T2 AS SELECT * FROM EMP;

CREATE TABLE T3 AS SELECT * FROM EMP WHERE 1=1;

CREATE TABLE T4 AS SELECT * FROM EMP WHERE 1=2;

CREATE TABLE TPK(

empid number(5) NOT NULL,ename varchar(50),location varchar(50) ,

PRIMARY KEY(emp_id));

CREATE TABLE TFK(

location_id varchar(5) NOT NULL,location varchar(50) NOT NULL,

PRIMARY KEY(location_id),

FOREIGN KEY(location) REFERENCES TPK(location));

===================================

-- INSERT

INSERT INTO T1 VALUES(1,'SRINU');

INSERT INTO T1(ID,NAME) VALUES(2,'VASU');

INSERT INTO T1 VALUES(&ID,&NAME);

INSERT INTO TPK (empid,ename,location) VALUES(1,'A','HYD');

INSERT INTO TFK (location_id,location) VALUES('L1','HYD');

===========================

DELETE FROM EMP;

DELETE FROM EMP WHERE DEPTNO=10;

DELETE FROM TPK WHERE empid=1;

DELETE FROM TPK WHERE location_id='l1';

===============================

--TRUNCATE

TRUNCATE TABLE T1;

TRUNCATE TABLE TPK;

TRUNCATE TABLE TFK;

===============

--DROP

DROP TABLE T1;

DROP TABLE TPK;

DROP TABLE TFK;

===========

CREATE TABLE AEMP AS SELECT * FROM EMP;

ALTER TABLE AEMP ADD  STATUS CHAR(10);

ALTER TABLE AEMP MODIFY  STATUS NUMBER(10);-- WITHOUT DATA

ALTER TABLE AEMP MODIFY  STATUS NUMBER(10);-- WITH DATA

ALTER TABLE AEMP MODIFY  EMPNO NUMBER(2);-- DECREASE LENGTH WITHOUT DATA

ALTER TABLE AEMP DROP COLUMN STATUS;

ALTER TABLE AEMP RENAME COLUMN SAL TO SALARY; 

RENAME AEMP TO AAEMP;