SQL Interview Questions


Question 1:

What is the difference among DELETE, DROP and TRUNCATE. Explain

DELETE

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.

Syntax

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

DROP statement completely removes the database objects from the database. DROP statement deletes columns from the table, table, database, Index.

Syntax

Drop table

DROP TABLE tableName

Drop Database

DROP Database databaseName

Drop Index

DROP INDEX table_name.index_name

TRUNCATE

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.

Syntax

TRUNCATE TABLE tableName
Question 2:

What is the difference between HAVING and WHERE clause in SQL?

WHERE clause is used in SQL with the SELECT statement to filter the data on the basis of provided condition.

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

Name Salary Age Department
Ashish 2000000 24 IT
Amit 5000000 31 IT
Saurabh 4000000 43 Finance
Andrew 20000 35 Admin
Allis 300000 35 IT
Bob 10000 23 Finance
Suresh 70000 43 Finance
Russle 4500000 46 HR

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

Output

Department TotalSalary
IT 7300000
Finance 4800000

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

Output

Department TotalSalary
IT 2000000

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.

Question 3:

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.

Question 4:

What are the different types of SQL statement?

SQL statements are majorly categorized in 4 main categories

  • DDL - Data Definition Language
  • DML - Data Manipulation Language
  • DCL - Data Control Language
  • TCL  - Transactional Control language
Question 5:

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.

  • CREATE (Database,Table,Index)
  • DROP (Database, Table, Index)
  • Alter (Database, Table, Index)

Question 6:

What is Data manipulation language?

Data manipulation statement is SQL used to perform insert, delete and update operations. Below are following DML statements

  • INSERT
  • DELETE
  • UPDATE
Question 7:

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

  • GRANT
  • REVOKE
Question 8:

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
Question 9:

What are the properties of Relational Table in SQL?

Relational Tables are the table that contains relational data.

Properties:-

  • Each row should be unique.
  • Each column in table must contain the same type of data (i.e. a decimal value column should have only decimal values)
  • Each column must have a unique name.
  • Column ordering does not matter on relationship
  • Row ordering does not matter on relationship

 

Question 10:

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

  • System defined stored procedure - Inbuilt stored procedure e.g. sp_helptext, sp_who
  • User defined stored procedure - defined by user
Share interview questions

Comments

Comments
comments powered by Disqus

Navigation

Social Media