SQL & Database Interview Questions and Answers for Freshers, Experienced

SQL is the standard language for Relation Database System. All relational database management systems like MySQL, MS Access, Oracle, Sybase, Informix, Postgres and SQL Server use SQL as standard database language.SQL and DBMS Interview Questions for freshers,Experienced covers major concepts like SQL Commands, SQL Joins, Normalisation, Transactions, Triggers, ACID Properties, Query Optimizations etc.,These topics cover frequently asked SQL Interview Questions in technical interviews of TCS,CTS,Infosys,Wipro,Tech mahindra. These questions are frequently asked in the interviews.

Top 30 SQL Interview Questions & Answers

1.What is DBMS?

Ans:The database management system is a collection of programs that enables the user to store, retrieve, update and delete information from a database.

2.What is RDBMS?

Ans:Relational Database Management system (RDBMS) is a database management system (DBMS), based on the relational model. Data from the relational database can be accessed or reassembled in many different ways without having to reorganize the database tables.

3.What is SQL?

Ans: SQL-Structural Query Language is a language specially designed for communicating with the database. It is used to perform the task, such as updating, retrieval, insertion and deletion of data from a database.

sql-database-interview-questions-and-answers-for-freshers-experienced

4.What are the different type of SQL’s statements?

Ans: DDL- Data Definition Language

DML-Data Manipulation Language

DCL-Data Control Language

5.Advantages of DBMS over traditional file-based systems.

Data redundancy and inconsistency
Difﬁculty in accessing data
Data isolation
Integrity problems
Concurrent access by multiple users
Security issues

6.What is a field in Database?

Ans: A field in a database is defined as the area within a record reserved for a specific piece of data.

7.What is a database transaction?

Ans: Database transaction takes database from one consistent state to another. At the end of the transaction, the system must be in the prior state if the transaction fails or the status of the system should reflect the successful completion if the transaction goes through.

8.What are properties of a transaction?

Atomicity
Isolation
Consistency
Durability

9.What are the types of Database locks?

Ans:Database lock tells, whether the data item is currently being used by other transactions.

10.What is database normalization?

Ans: The process of analyzing relation schemas based on their functional dependencies and primary keys.Its main aim is to minimize redundancy.

11.What are the differences between DDL,DML, and DCL in SQL?

DDL stands for Data Definition Language. SQL queries like CREATE, ALTER, DROP and RENAME come under this.
DML stands for Data Manipulation Language. SQL queries like SELECT, INSERT and UPDATE come under this.
DCL stands for Data Control Language. SQL queries like GRANT and REVOKE come under this.

12.What is the difference between having and where clause?

Ans:HAVING is used to specify a condition for a group or an aggregate function used in select statement. The WHERE clause selects before grouping. The HAVING clause selects rows after grouping. Unlike HAVING clause, the WHERE clause cannot contain aggregate functions.

13.What is Identity?

Ans:Identity (or AutoNumber) is a column that automatically generates numeric values. A start and increment value can be set, but most DBA leave these at 1.

14.What is a Trigger?

Ans:A Trigger is a code that associated with insert, update or delete operations. The code is executed automatically whenever the associated query is executed on a table.

15.What is a stored procedure?

Ans: Stored Procedure is a function which contains a collection of SQL Queries. The procedure can take inputs , process them and send back output.

16.What are Indexes?

Ans:A database index is a data structure that improves the speed of data retrieval operations on a database table at the cost of additional writes and the use of more storage space to maintain the extra copy of data.
Data can be stored only in one order on disk.

17.What is a primary key?

Ans:A Primary Key is one of the candidate keys. One of the candidate keys is selected as most important and becomes the primary key. There cannot be more than one primary keys in a table.

Only one in a row(tuple).
Never allows null value(only key field).
Unique key identifier and can not be null and must be unique.

18.The difference between primary key and unique key.

Ans:Primary key cannot have NULL value, the unique constraints can have NULL values. There is only one primary key in a table, but there can be multiple unique constraints.

19.What is a composite key?

Ans:A Composite primary key is a type of candidate key, which represents a set of columns whose values uniquely identify every row in a table.

Example : If “Student_ID” and “Student Name” in a table is combined to uniquely identify a row it’s called a Composite key.

20.What is a Composite Primary Key?

Ans:A composite Primary Key is a set of columns whose values uniquely identify every row in a table.A table which contains composite primary key will be indexed based on the columns specified in the primary key.

Example : If the combined columns, “Student_ID” and “Student Name” in a table is required to uniquely identify a row, it’s known as Composite Primary Key.

21.What is a Foreign Key?

Ans:When a “Table-1” table’s primary key field is added to a related “Table-2” table in order to create the common field which relates the two tables, it is called a foreign key in the “Table-2” table.

22.What is Self Join?

Ans:Self-Join is query used to join a table to itself ,Aliases should be used for the same table comparison.

23.What is Cross Join?

Ans:Cross Join will return all records where each row from the first table is combined with each row from the second table.

24.What is the difference between DELETE, TRUNCATE and DROP commands?

Ans:Once delete operation is performed, Commit and Rollback can be performed to retrieve data.

Once truncate statement is executed, Commit and Rollback statement cannot be performed.

Drop command is used to drop the tables or keys.

25.What is a stored procedure?

Ans: A stored procedure is a function that contains a set of operations compiled together. It contains a set of operations that are commonly used in an application to do some common database tasks.

26. What is a query?

Ans: A DB query is a code written in order to get the information back from the database. Query can be designed in such a way that it matched with our expectation of the result set. Simply, a question to the Database.

27. What is subquery?

Ans :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.

28. What is collation?

Ans: Collation is defined as set of rules that determine how character data can be sorted and compared. This can be used to compare A and, other language characters and also depends on the width of the characters.

ASCII value can be used to compare these character data.

29. What are all different types of collation sensitivity?

Ans: Following are different types of collation sensitivity -.