SQL Interview Questions and Answers

1. What is SQL ?

SQL stands for Structured Query Language.It is a database language used for database creation, deletion, fetching rows and modifying rows etc.

2. What are the usages of SQL ?

• To execute queries against a database
• To retrieve data from a database
• To inserts records in a database
• To updates records in a database
• To delete records from a database
• To create new databases
• To create new tables in a database
• To create views in a database

3. What are the different types of SQL statements ?

SQL statement are broadly classified into three types:

• Data Definition Language
• Data Manipulation Language
• Data Control Language

4. What is DDL ?

Data Definition Language (DDL) is set of commands used to create and destroy database and database objects. DDL commands are primarily  used by database administrators during the setup and removal phases of a database project. Some of the  important DDL commands are as below :

• CREATE
• USE
• ALTER
• DROP

5. What is DML ?

Data Manipulation Language (DML) is  collection of SQL commands used to manipulate data in database in some form.This manipulation involves inserting data into database tables, retrieving existing data, deleting data from existing tables and modifying existing data. DML is mostly incorporated in SQL databases. Some of the  important DML commands are as below :

• SELECT
• INSERT
• UPDATE
• DELETE

6. What is DCL ?

Data Control Language (DCL) is set of SQL commands used by database administrator to configure security access to relational databases. DCL contains only three commands which are as below :

• GRANT
• REVOKE
• DENY

7. Define SQL Select Statement

SQL SELECT statement is a DML (Data Manipulation Language) statement.The SELECT statement is an SQL statement that specifies which rows and columns to fetch from one or more tables or views. The SELECT statement is very helpful in finding filtered records from a database.

8. Define SQL Insert Statement 

SQL INSERT statement is a DML (Data Manipulation Language) statement.SQL INSERT statement is used to add rows to a table. For a full row insert, SQL Query should start with “insert into “ statement followed by table name and values command, followed by the values that need to be inserted into the table. The insert can be used in several ways:

• To insert a single complete row.
• To insert a single partial row.

9. Define SQL Update Statement

SQL Update is used to update data in a row or set of rows specified in the filter condition. The basic format of an SQL UPDATE statement is, Update command followed by table to be updated and SET command followed by column names and their new values followed by filter condition that determines which rows should be updated.

10. Define SQL Delete Statement

SQL Delete is used to delete a row or set of rows specified in the filter condition. The basic format of an SQL DELETE statement is, DELETE FROM command followed by table name followed by filter condition that determines which rows should be updated.

11. What are the types of Operators available in SQL ?

Operators available in SQL are as below :

• Arithmetic Operators
• Comparison Operators
• Logical Operators
• Concatenation Operators
• Assignment Operators
• Bitwise Operators

12. What is a Primary key ?

A primary key is a combination of fields which uniquely specify a row. This is a special kind of unique key, and it has implicit NOT NULL constraint. It means, Primary key values cannot be NULL.

13. What is a unique key ?

A Unique key constraint uniquely identified each record in the database. This provides uniqueness for the column or set of columns.
A Primary key constraint has automatic unique constraint defined on it. But not, in the case of Unique Key.There can be many unique constraint defined per table, but only one Primary key constraint defined per table.

 14. What is a foreign key?

A foreign key is one table which can be related to the primary key of another table. Relationship needs to be created between two tables by referencing foreign key with the primary key of another table.

15. What is a join ?

A SQL Join clause is used to combine rows from two or more tables, views, or materialized views based on a common field between them. A SQL join condition is used in the where clause of select, update, delete statements.

16. What are the types of join ?

There are different kinds of joins, which have different rules for the results they create. Listed below are the different types of  SQL join :

• Inner Join
• Left Join
• Right Join
• Full Join
• Self Join
• Cartesian Join

17. What is Inner Join ?

selects all rows from both tables as long as there is a match between the columns in both tables.

18. What is Left Join ?

Returns all rows from the left table, even if there are no matches in the right table.

19. What is Right Join ?

Returns all rows from the right table, even if there are no matches in the left table.

20. What is Full Join ?

Combines the result of both LEFT and RIGHT joins.

21. What is Self Join ?

Self Join is used to join a table to itself as if the table were two tables, temporarily renaming at least one table in the SQL statement.

22. What is a Cross Join ?

Returns the Cartesian product of the sets of records from the two or more joined tables.

23. What is subquery ?

A subquery is a query within another query. The outer query is called as main query, and inner query is called subquery. SubQuery is always executed first, and the result of subquery is passed on to the main query.

24. What are the types of subquery?

There are two types of subquery – Correlated and Non-Correlated.
A correlated subquery cannot be considered as independent query, but it can refer the column in a table listed in the FROM the list of the main query.
A Non-Correlated sub query can be considered as independent query and the output of subquery are substituted in the main query.

25. What is the difference between Drop, Delete and Truncate commands ?

DELETE :
The DELETE command is used to remove rows from a table. A WHERE clause can be used to only remove some rows. If no WHERE condition is specified, all rows will be removed. After performing a DELETE operation you need to COMMIT or ROLLBACK the transaction to make the change permanent or to undo it. Note that this operation will cause all DELETE triggers on the table to fire.
TRUNCATE :
TRUNCATE removes all rows from a table. The operation cannot be rolled back and no triggers will be fired. As such, TRUCATE is faster and doesn’t use as much undo space as a DELETE.
DROP :
The DROP command removes a table from the database. All the tables’ rows, indexes and privileges will also be removed. No DML triggers will be fired. The operation cannot be rolled back.

26. What is Union, minus and Interact commands?

UNION operator is used to combine the results of two tables, and it eliminates duplicate rows from the tables.
MINUS operator is used to return rows from the first query but not from the second query. Matching records of first and second query and other rows from the first query will be displayed as a result set.
INTERSECT operator is used to return rows returned by both the queries.

27. What is difference  betwn union and union all ?

Both UNION and UNION ALL concatenate the result of two different SQLs. They differ in the way they handle duplicates.

• UNION performs a DISTINCT on the result set, eliminating any duplicate rows.
• UNION ALL does not remove duplicates, and it therefore faster than UNION.

28. What is difference  between having and where clause ?

Where Clause :
1.Where Clause can be used other than Select statement also
2.Where applies to each and single row
3.In where clause the data that fetched from memory according
to condition
4.Where is used before GROUP BY clause
Ex:Using Condition for the data in the memory.
Having Clause :
1.Having is used only with the SELECT statement.
2.Having applies to summarized rows (summarized with GROUP BY)
3.In having the completed data firstly fetched and then separated according to condition.
4.HAVING clause is used to impose condition on GROUP Function and is used after GROUP BY clause in the query
Ex: when using the avg function and then filter the data like ava(Sales)>0
Summary:
Having works like Where clause with out Group By Clause