SQL Interview Questions with Answers

SQL is a one kind of programming language used in relational database. Here is some frequently asked important basic and advanced Top 50 + SQL Queries Interview Questions and Answers with Examples for Fresher’s and experienced. Hope it will help you to build successful carrier.

What is SQL?
Structured Query Language or SQL is a one kind of programming language used to handle data in Relational Database Management System (RDMS) like Oracle, SQL Server, MySQL, Sybase, DB2, DB/400 etc. It is designed specifically for communicating with databases. SQL is an ANSI (American National Standards Institute) standard.

When SQL invented?
SQL invented in 1974 by Donald D. Chamberlin and Raymond F. Boyce.

What are the Advantages of SQL?
SQL is not designed for a specific database vendor. Almost every major DBMS supports SQL. So learning this one language any programmer can interact with any database like ORACLE, SQL, MYSQL etc.
SQL is easy to learn. All the statements are all made up of descriptive English words.
SQL is a very powerful language and by using this anyone can perform very complex and sophisticated database operations.

What is a field in a database?
A field is an area within a record reserved for a specific type of data. Examples: Customer Name, Customer ID etc

What is a Record in a database?
A record is the collection of values / fields of a specific entity. For Customer table AB Barle is a record.

What is a Table in a database?
A table is a collection of records of a specific type. For example, customer table, salary table etc

What is Database Management System or DBMS
Database Management System or DMS is a process to store data in database and retrieve them according to our requirements. DBMS is a collection of programs that enables user to store, retrieve, update and delete information from a database.

What is Relational Database Management System (RDMS)?
Relational Database Management System (RDBMS) is Database Management System ( DBMS) that is based on Relational Model. Data from the Relational Database can be accessed in different ways like API, Structured Query Language (SQL).  At present there are so many RDMS. Like
MS SQL Server

• Oracle
• MySQL
• MS Access
• DB2(IBM)
• Sybase

What is Database Constraint?
Database constraint is user-defined structures that express the behaviors of column. It is used to protect wrong entry. There are five types of database constraints. Such

1. Primary Key Constraint
2. Foreign Key Constraint
3. Unique Key Constraint
4. Not null Constraint
5. Check Constraint

What is Primary Constraint?
A Primary Key Constraint checks that a column value will be unique among all the rows in a table. It disallows null values. That’s why a Primary Key Constraint has the behaviors of both Not Null and Unique Constraints. A table can have only one unique key.

What is Foreign Key Constraint?
A Foreign Key Constraint restricts the values that are acceptable in a column or group of columns to those values found in a listing of the column or group of columns used to define the primary key. Depending on the design, this may or may not impose a Not Null column constraint on all members of the foreign key.

What is Unique Key Constraint?
A Unique Constraint checks that a column value will be unique among all the rows in a table. It allows only a single Null value. A table may have more than one unique key.

What is Not null Constraint?
A Not Null Constraint checks that a column value will be mandatory. This means we can’t insert a row in the table without providing a valid data value for all Not Null constrained columns.

What is Check Constraint?
A Check Constraint is table-level constraint. We can only create table-level constraints as out-of-line constraints. We typically restrict a column value with a Check Constraint to a set of values defined by the constraint. A Check Constraint doesn’t make the column mandatory, which means the default is the ANSI standard for nullable columns.

What is Normalization?
Normalization is a process to remove data redundancy from the database.
Types:

• First Normal Form
• Second Normal Form
• Third Normal Form

What is SQL Injection?
SQL Injection is a one kind of attack where the attacker generates malicious code and send into SQL query to access database or system.

What is Database Schema?
A database schema is a way to logically group database objects such as tables, views, stored procedures etc. Schema works as a container of database objects. We can assigned user credential to a single schema so that the user can only access that objects they are authorized to access.
In a database, the schema can be created and altered. Users can be granted access to a schema. A schema can be owned by any user and the schema ownership is transferable.

What is Database Object?
In a relational database A database object is a data structure used to either store or reference data. The most common object is table. Other objects are indexes, stored procedures or SP, functions, views etc. Most of the major database engines offer the same set of major database object types:

• Tables
• Views
• Stored Procedure
• Synonyms
• Functions
• Indexes
• Sequences

What are the difference between clustered and a non-clustered index?
A clustered index physically order the data on the disk. A table can have only one clustered index. It is faster than non-clustered index to select data. Primary key is a clustered index. The leaf node of a clustered index contains the data pages.
Non-clustered indexes are stored separately, and we can create so many non-clustered indexes as we want. It is faster than clustered index for insert, updates operation. The leaf node of a non clustered index does not consist of the data pages

What are the different index configurations a table can have?
A table can have one of the following index configurations:

1. No indexes
2. A clustered index
3. A clustered index and many non-clustered indexes
4. A non-clustered index
5. Many non-clustered indexes

What’s the difference between a primary key and a unique key?
Unique Key

1. A table can have more than one unique key
2. A unique key have not implicit NOT NULL constraint
3. It may or may not contain NULL values

Primary Key

1. A table can have only one primary key
2. A primary key have an implicit NOT NULL constraint
3. It cannot contain NULL values

What is difference between DELETE and TRUNCATE commands?
Delete

1. It is slow
2. It keeps log for every row.
3. We can use where clause.
4. It delete all data based on where clause
5. DELETE can be rolled back.
6. DELETE is DML Command.
7. DELETE does not reset identity of the table.

Truncate

1. It is faster.
2. It does not keeps log.
3. We can’t use where clause.
4. It delete all data.
5. TRUNCATE cannot be rolled back.
6. TRUNCATE is DDL Command.
7. TRUNCATE Resets identity of the table.

Note: DELETE and TRUNCATE both can be rolled back when surrounded by TRANSACTION if the current session is not closed.

What is the difference between a HAVING CLAUSE and a WHERE CLAUSE?
Both WHERE & HAVING specifies a search condition used in SELECT statement. WHERE clause applies to individual rows.
HAVING clause applies only to groups as a whole.
A query can contain both a WHERE clause and a HAVING clause. The WHERE clause is applied first to the individual rows in the tables. The HAVING clause is then applied and make group with the rows that meet the conditions in the WHERE clause. Only the groups that meet the HAVING conditions appear in the query output.
HAVING clause is used for a group or an aggregate function used in SELECT statement.

JOIN
JOIN is the form of SELECT command that combines info from two or more tables.

Union
Union is the product of two or more tables.

Can a stored procedure call itself or recursive stored procedure?
Yes. Because Transact-SQL supports recursion, we can write stored procedures that call themselves. We can nest stored procedures and managed code references up to 32 levels.

What is Stored Procedure (SP)?
SP is a set of SQL statement which is written in database server or database. It can handle complex operation according to input. Its input is called parameter. Any SP may have multiple parameters or not. If we write code in SP rather than client end it is faster. Because SP stay & run inside database server.
Many database support SP but its code may differ according to database server. MSSQL Server, mySQL(vertion 5+), Oracle, DB2 etc support SP
EXECUTE SP_Name()

What are the advantages of stored procedure?

• It is faster
• Pre-compiled
• Reduce network traffic
• Reusable
• It can handle complex operation
• It support nested SP
• Security is high. Nobody can see the code. Because it stay in Database Server

What are the disadvantages of stored procedure?

• It is faster
• Need expert developer, Because it is so hard to write code
• Debugging is hard
• It is not database independent. Its code may vary based on database server

What are the difference between Function and Stored Procedure?
Function(User Defined)

1. It returns only one value
2. We can’t use transaction in function
3. Only have input parameter
4. We can’t called SP from function
5. We can’t use exception handling using Try-Catch block in function
6. We can use function in select/where/having statement

Stored Procedure (SP)

1. It returns zero, single or multiple values
2. We can use transaction in SP
3. Can have input/output parameter
4. We can called function from SP
5. We can use exception handling using Try-Catch block in SP
6. We can use function in select/where/having statement We can’t use SP in select/where/having statement

How we can store data in SQL?
There are basically four ways we can store this data. Like

1. Local temporary tables (#table_name)
2. Global temporary tables (##table_name)
3. Permanent tables (table_name)
4. Table variables (@table_name)

What is Local Temporary Tables (#table_name)?
Local temporary tables are visible only to the connection that creates it, and are deleted when the connection is closed.

What is Global Temporary Tables (##table_name)?
Global temporary tables are visible to everyone or all session, and are deleted when all connections that have referenced them are closed

What is Permanent table (table_name)?
Permanent tables are visible to everyone, and are deleted when the server is restarted.

What is Table Variable (@table_name)?
Table variables are visible only to the connection that creates it and are deleted when the batch or stored procedure ends.

What is Trigger?
It is a special kind of Stored Procedure (SP) that automatically executes when an event occurs in the database server. Trigger acts on the table not one the views. The basic difference between normal SP and Trigger is

• No need to call it for execution
• It can execute automatically
• No parameter support

Many database support SP but its code may differ according to database server. MSSQL Server, mySQL(vertion 5+), Oracle, DB2 etc support SP

What are the difference between #temp table and @table variable?
#temp table

1. Stored data in system hard disk
2. It is slower than @table variable
3. Suitable for large amount of data
4. Can have indexes

@table variable

1. Stored data in system ram
2. It is faster than #temp table
3. Suitable for small amount of data, it depends on system ram size
4. Can only have a primary index

What is the difference between UNION and UNION ALL?
UNION operation returns only the unique records from the resulting data set. UNION ALL will return all the rows even if one or more rows are duplicated to each other.

What is the difference among UNION, MINUS and INTERSECT?
UNION operator combines the results from 2 tables and eliminates duplicate records from the result set.
MINUS operator gives us all the rows from the first table except the rows which are present in the second table.
INTERSECT operator returns only the common rows between 2 result sets.

What is Self Join and why is it required?
In a self-join a table can be joined to itself. When we need to join a table with it, we use self-join.