some SQL commands and their differences

4 min readFeb 2, 2020

Try to learn according to this picture there will be no confusion.

Difference Between Delete, Truncate and Drop

Delete Statement

The DELETE FROM statement deletes one or more rows from the table. You can use the statement when you want to delete existing records.

Syntax of SQL Delete statement

DELETE FROM table _name[WHERE CONDITION];

Example : DELETE FROM employee WHERE id =100;

So, seeing this Statement we can say that delete the row from employee table where employee id /id =100. You can think what need of where clause if you don't mention the WHERE it will delete the entire table.

Truncate statement

The SQL TRUNCATE command is used to delete all the rows from the table and free the space containing the table.

Syntax to TRUNCATE a table

TRUNCATE TABLE table_name;

To delete all the rows from the employee table, the query would be

Example: TRUNCATE TABLE employee;

Main and Most Important difference is that Delete statement will follow the where clause, deletes according to the condition but Truncate statement does not have the where clause and it will delete entire table without any concern and make the free space

Drop Statement

If you drop a table, all the rows in the table are deleted and the table structure is removed from the database. Once a table is dropped we cannot get it back, so be careful while using the DROP command. When a table is dropped all the references to the table will not be valid.

Syntax to drop Statement

DROP TABLE table_name;

Example: DROP TABLE' employee;

If a table is dropped, all the relationships with other tables will no longer be valid, the integrity constraints will be dropped, grant or access privileges on the table will also be dropped, if you want to use the table again it has to be recreated with the integrity constraints, access privileges and the relationships with other tables should be established again.

Differences between `ALTER` and UPDATE

ALTER Command

This command modifies the structure or definition of a relationship that already exists in the database. Modifying the structure of relation means, you can add columns, delete or drop columns, rename a column’s name, resize columns, or you can change the data type of the columns of a table (relation) that already exists in the database.

So, we can say that ALTER command operates on columns or attributes only, as columns in a relation refer to the attributes of that relation. Whenever ALTER command adds any new column or attributes in a relation then it by default initializes the value of that attribute for all tuple as NULL.

Syntax: ALTER TABLE table_name
ADD column_name datatype;

The following statement adds a column called the department to the employee table.

ALTER TABLEemployee ADD(department VARCHAR(30));

The following statement adds a column called the department to the employee table.

ALTER TABLE employee MODIFY(department VARCHAR(50));

The following statement modifies the name of the column date_of_birth as dob.

ALTER TABLE employee RENAME date_of_birth TO dob;

UPDATE command

UPDATE is a command in SQL that is used to modify the rows in a table of an RDBMS. WHERE clause is used along with the UPDATE command, it helps in selecting the tuples whose attribute values are to be modified.

Syntax : UPDATE table_name
SET column1 = val1, column2 = val2….
WHERE condition;

Thus, this is the fundamental difference between ALTER and UPDATE in SQL.

Difference between DISTINCT and GROUP BY

DISTINCT is used to filter unique records out of the records that satisfy the query criteria.

DISTINCT

Syntax:SELECT DISTINCT column1, column2, …
FROM table_name;

When you have a result set containing more than one duplicate records, then you can get unique results out of that by using DISTINCT.

For example, we have a Products table and there are some products with its price and get all the data from that which will contain duplication. The right column is having unique records by using the DISTINCT keyword.

The “GROUP BY” clause is used when you need to group the data and it should be used to apply aggregate operators to each group.

GROUP BY

Syntax: SELECT column_name(s)
FROM table_name
WHERE condition
GROUP BY column_name(s)
ORDER BY column_name(s);

GROUP BY is used when you want to group your data with some criteria or any aggregate function on grouped data. For example, Again, we are taking the same as an above table and getting unique records from the result set by using the GROUP BY clause.