Here is a compilation of some of the most frequently asked database and SQL interview questions in by different leading Software development companies around the globe. Also note that a thorough practice of SQL query questions is necessary before going forward for any Database interview.
What is the difference between Drop, Truncate and Delete ?
DROP: Drop is a DDL command that is used to remove objects form a database. DROP along with the data also deletes the entire schema of the table in a database.
TRUNCATE: Truncate is another DDL command that is used to remove the data of the table along with space allocated to each record.
DELETE: Delete command can be used with conditional statements in SQL to delete specific columns or rows or simply records from the table in a database. It is a DML command.
What is NoSQL? Which companies use it?
NoSql or Non Relational SQL Database is a type of databases management system that does not need a fixed schema for storing and manipulating data. NoSQL can store a wide range of different data such as data in the form of key-value pairs, graph, document etc. It is easy to use and is very scalable. NoSQL is prominently used for Big data. It also reduces the response time of data processing capabilities when compared to RDBMS.
The most popular one is MongoDB. Some other examples include Apache Cassandra, Couchbase etc.
Companies such as Netflix, LinkedIn, Google, Facebook, Adobe etc use NoSQL to deal with big data.
What do you mean by Triggers?
PL/SQL Trigger is block of code attached to an event that may occur on a data table. Read complete details of triggers here .
What is the use of index in SQL?
An index in SQL is used for faster retrieval of data. Indexes are used to speed up the search operation in tables.
The CREATE INDEX statement is used to create an index in SQL. Here is an example of index in SQL:
CREATE INDEX name_index ON Students (FirstName, LastName);
What is the use of view in SQL?
A view is a section of data created for specific user(s) and given a name to be used just like a database table. Users can use the data of a view without accessing or modifying the base table(s) that you may not want then to access. Read all about views here.
What is Data Integrity?
Data integrity in DBMS relates to the maintenance of accuracy and consistency throughout the data. It also includes the safety related to the storage of data in a database. It also keeps completeness of data into check. DBMS data integrity constrains help in making sure that the integrity is maintained.
These are the 4 types of data integrities:
- Entity Integrity
This integrity states that a table must have its own primary key and it must be unique and not null.
- Referential Integrity
This rule refers to the foreign keys in a table. It checks for consistency of data. It states that a foreign key must refer to a primary key of another table.
- Domain Integrity
It ensures that the data that is present in the database is of the valid format. In general, it check for accuracy of data. It states that the data must be in a valid format, amount or type. For example some of the domain data types include integer, time, string, character and so on.
- User-defined Integrity
An user defined integrity is used to safeguard the data which is beyond the scope of the above three data integrity rules. According to the data rules outlined by any business, these integrity rules can be created by the user.
What are the major data structures used in the following areas: RDBMS, Network data model, and Hierarchical data model?
RDBMS: Arrays data structure to store/access data.
Network Data Model: Graph data structure
Hierarchical Data Model: Trees data structure
What do you mean by Correlated subquery?
In a correlated subquery the outer query is dependent on the data that is received by running an inner query. In other words the inner query feeds the data to the outer query. To get the results, database must run the subquery for each evaluation. Also, A correlated subquery’s execution time and frequency is dependent on the data passed from the inner query. Example:
SELECT eid, deptid, salary FROM empdata e WHERE salary>(SELECT avg(salary) FROM empdata WHERE eid=e.eid);
Given a table (EmpID, Salary) make a generic sql query to get the nth highest salary.
select min(Salary) from (WHERE DISTINCT e.Salary FROM employee e ORDER BY Salary DESC) WHERE rownum< n;
Explanation: Here in the above query n can be any integer and this will return the (n-1)th highest salary.
Why do we perform normalization of a database?
Normalization is done to reduce data redundancy and remove anomalies from the data. It also promotes integrity. The data must conform to the normal forms. A normal form in normalization is achieved by implementing various rules relating to functional dependencies of a table. Some normal forms are: 1NF, 2NF, 3NF, BCNF etc. Normalization provides the best way to store data in a database. It is very crucial step in any database management as it also makes the relational database free from any undesirable DML operations.
So these are the top 10 Database and SQL interview questions to get prepared for that placement interview you are looking ahead to.