What is the difference among DELETE, DROP and TRUNCATE. Explain
DELETE statement is used to delete the rows from the database tables. This will delete entire row from a table, not a single cell or column. DELETE statement deletes the rows from the table that matches the specified condition when used with WHERE clause. DELETE statement can be used with transaction i.e. Deleted rows can be rollback/ undo if used in the transaction block.
Triggers can also be defined and executed on DELETE.
BEGIN TRAN DELETE FROM tableName WHERE condition --Rollback, if you want to undo ROLLBACK TRAN --Commit if you want to save changes COMMIT TRAN
DROP statement completely removes the database objects from the database. DROP statement deletes columns from the table, table, database, Index.
DROP TABLE tableName
DROP Database databaseName
DROP INDEX table_name.index_name
TRUNCATE statement deletes all the data from the table, not the table definition itself. Delete rows can not be rollback. Triggers cannot work with TRUNCATE.
TRUNCATE TABLE tableName
What is the difference between HAVING and WHERE clause in SQL?
HAVING clause is also used to filter the data with the aggregate functions. Having clause is used to filter the data after the aggregate operation is performed.
Consider below table
Table name : Employees
Now management wants to know the total salaries for departments whose total salary is greater the 1 million
SELECT Department, SUM(Salary) AS TotalSalary FROM dbo.Employees GROUP BY Department HAVING TotalSalary > 1000000
Above query get the desired result by applying the HAVING clause on aggregated salary SUM (salary).
Now management wants to know the total salaries for departments whose total salary is greater the 1 million but employee age is less than 30.
SELECT Department, SUM(Salary) AS TotalSalary FROM dbo.Employees WHERE Age < 30 GROUP BY Department HAVING TotalSalary > 1000000
In the above query WHERE clause is applied to filter the employees whose age is <30, SQL will perform SUM after filtering the data. Finally, it will apply HAVING clause on aggregated data.
What is SQL?
SQL stands for
Structured Query Language. SQL is specifically designed to communicate to databases and used to perform CURD (Create, Update, Read, Delete) operations on the database.
What are the different types of SQL statement?
SQL statements are majorly categorized in 4 main categories
What is Data definition language?
Data Definition Language is used to define the structure of the database objects. Some of DDL commands are given below.
DROP(Database, Table, Index)
Alter(Database, Table, Index)
What is Data manipulation language?
Data manipulation statement is SQL used to perform insert, delete and update operations. Below are following DML statements
What is Data Control Language in SQL?
DCL is that type of commands which is tightly bound with the security of database objects.It is used to create roles, permissions and referential integrity as well as used to control access to the database by securing it. Some of DCL commands are given below
What is Transaction control language in SQL?
TCL is that type of commands which is tightly bound with the transactions. These commands ensure that data is correct.Some of TCL commands are given below
COMMIT- Commit mark transaction successful and all changes made during transaction will persist
ROLLBACK- Rollback any transaction and any changes made during the transaction also rollback
What are the properties of Relational Table in SQL?
Relational Tables are the table that contains relational data.
What is stored procedure in SQL?
Stored procedures are precompiled set of SQL statements that are used to perform any task or operation and stored in SQL Database. Stored procedure accepts parameters like a function.
Stored procedures are very useful to improve the performance as the instructions written in stored procedure are compiled previously and execution plan for stored procedure is saved.
There are two types of Stored procedures