Most Frequently Asked Database Interview Questions and Answers

This article will cover a list of all the several important and commonly asked Database interview questions and answers, which will help you to crack any interview, here we will cover basic to advance SQL interview Questions

Organizational experiences, great problem-solving inclination, attention to detail and a huge level of precision, are some of the necessary qualities a person should maintain to be in the “Database” domain. 

Basic database interview questions and Answers / SQL interview Questions:

Interview Question 1. What do you know about ‘Database’?

Database is an ordered collection of related data where the data is stored and organized to serve some particular purpose.

For Example, A librarian maintains a database of all the information related to the books that are available in the library.

Interview Question 2. Define DBMS.

DBMS stands for Database Management System. It is a set of application programs that allow the user to organize, recover and retrieve information about data efficiently and as productively as possible.

Some of the popular DBMS’s are MySql, Oracle, Sybase, etc.

Interview Question 3. Define RDBMS.

Relational Database Management System(RDBMS) is based on a relational model of data that is stored in databases in separate tables and they are related to the use of a common column. Data can be accessed easily from the relational database using Structured Query Language (SQL).

Interview Question 4. Enlist the advantages of DBMS.

The advantages of DBMS includes:

  • Data is stored in a structured way and hence redundancy is controlled.
  • Validates the data entered and provide restrictions on unauthorized access to the database.
  • Provides backup and recovery of the data when required.
  • It provides multiple user interfaces.

Interview Question 5. What do you understand by Data Redundancy?

Duplication of data in the database is known as data redundancy. As a result of data redundancy, duplicated data is present at multiple locations, hence it leads to wastage of the storage space and the integrity of the database is destroyed.

Interview Question 6. What are the various types of relationships in Database? Define them.

There are 3 types of relationships in Database:

  • One-to-one: One table has a relationship with another table having the similar kind of column. Each primary key relates to only one or no record in the related table.
  • One-to-many: One table has a relationship with another table that has primary and foreign key relations. The primary key table contains only one record that relates to none, one or many records in the related table.
  • Many-to-many: Each record in both the tables can relate to many numbers of records in another table.

Interview Question 8. Explain Normalization and De-Normalization.

Normalization is the process of removing redundant data from the database by splitting the table in a well-defined manner to maintain data integrity. This process saves much of the storage space.

De-normalization is the process of adding up redundant data on the table to speed up complex queries and thus achieve better performance

Different types of Normalization are:

  • First Normal Form (1NF): A relation is said to be in 1NF only when all the entities of the table contain unique or atomic values.
  • Second Normal Form (2NF): A relation is said to be in 2NF only if it is in 1NF and all the non-key attribute of the table is fully dependent on the primary key.
  • Third Normal Form (3NF): A relation is said to be in 3NF only if it is in 2NF and every non-key attribute of the table is not transitively dependent on the primary key.

Interview Question 9. What is BCNF?

BCNF is the Boyce Code Normal form. It is the higher version of 3Nf which does not have any multiple overlapping candidate keys.

Interview Question 10. What is SQL?

Structured Query language, SQL is an ANSI(American National Standard Institute) standard programming language that is designed specifically for storing and managing the data in the relational database management system (RDBMS) using all kinds of data operations.

Interview Question 11. How many SQL statements are used? Define them.

SQL statements are basically divided into three categories, DDL, DML, and DCL.

They can be defined as:

  • Data Definition Language (DDL) commands are used to define the structure that holds the data. These commands are auto-committed i.e. changes done by the DDL commands on the database are saved permanently.
  • Data Manipulation Language (DML) commands are used to manipulate the data of the database. These commands are not auto-committed and can be rolled back.
  • Data Control Language (DCL) commands are used to control the visibility of the data in the database like revoke access permission for using data in the database.

Interview Question 12. Enlist some commands of DDL, DML, and DCL.

Data Definition Language (DDL) commands:

  • CREATE to create a new table or database.
  • ALTER for alteration.
  • TRUNCATE to delete data from the table.
  • DROP to drop a table.
  • RENAME to rename a table.

Data Manipulation Language (DML) commands:

  • INSERT to insert a new row.
  • UPDATE to update an existing row.
  • DELETE to delete a row.
  • MERGE for merging two rows or two tables.

Data Control Language (DCL) commands:

  • COMMIT to permanently save.
  • ROLLBACK to undo the change.
  • SAVEPOINT to save temporarily.

Interview Question 13. Define DML Compiler.

DML compiler translates DML statements in a query language into a low-level instruction and the generated instruction can be understood by Query Evaluation Engine.

Interview Question 14. What is DDL interpreter?

DDL Interpreter interprets the DDL statements and records the generated statements in the table containing metadata.

Database Interview Questions and

Interview Question 15. Enlist the advantages of SQL.

Advantages of SQL are:

  • Simple SQL queries can be used to retrieve a large amount of data from the database very quickly and efficiently.
  • SQL is easy to learn and almost every DBMS supports SQL.
  • It is easier to manage the database using SQL as no large amount of coding is required.

Interview Question 16. Explain the terms ‘Record’, ‘Field’ and ‘Table’ in terms of database.

Record: Record is a collection of values or fields of a specific entity. For Example, An employee, Salary account, etc.

Field: A field refers to an area within a record that is reserved for specific data. For Example, Employee ID.

Table: Table is the collection of records of specific types. For Example, the Employee table is a collection of records related to all the employees.

Interview Question 17. What do you understand by Data Independence? What are its two types?

Data Independence refers to the ability to modify the schema definition in one level in such a way that it does not affect the schema definition in the next higher level.

The 2 types of Data Independence are:

  • Physical Data Independence: It modifies the schema at the physical level without affecting the schema at the conceptual level.
  • Logical Data Independence: It modifies the schema at the conceptual level without affecting or causing changes in the schema at the view level.

Interview Question 18. Define the relationship between ‘View’ and ‘Data Independence’.

View is a virtual table that does not have its data on its own rather the data is defined from one or more underlying base tables.

Views account for logical data independence as the growth and restructuring of base tables are not reflected in views.

Interview Question 19. What are the advantages and disadvantages of views in the database?

Advantages of Views:

  • As there is no physical location where the data in the view is stored, it generates output without wasting resources.
  • Data access is restricted as it does not allow commands like insertion, updation, and deletion.

Disadvantages of Views:

  • The view becomes irrelevant if we drop a table related to that view.
  • Much memory space is occupied when the view is created for large tables.

Interview Question 20. What do you understand by Functional dependency?

A relation is said to be in functional dependency when one attribute uniquely defines another attribute.

For Example, R is a Relation, X and Y are two attributes. T1 and T2 are two tuples. Then,

T1[X]=T2[X] and T1[Y]=T2[Y]

Means, the value of component X uniquely define the value of component Y.

Also, X->Y means Y is functionally dependent on X.

Interview Question 21. When is functional dependency said to be the fully functional dependent?

To fulfill the criteria of fully functional dependency, the relation must meet the requirement of functional dependency.

A functional dependency ‘A’ and ‘B’ are said to be fully functional dependent when removal of any attribute say ‘X’ from ‘A’ means the dependency does not hold anymore.

Interview Question 22. What do you understand by the E-R model?

E-R model is an Entity-Relationship model which defines the conceptual view of the database.

The E-R model basically shows the real-world entities and their association/relations. Entities here represent the set of attributes in the database.

Interview Question 23. Define Entity, Entity type, and Entity set.

Entity can be anything, be it a place, class or object which has an independent existence in the real world.

Entity Type represents a set of entities that have similar attributes.

Entity Set in the database represents a collection of entities having a particular entity type.

Interview Question 24. Define a Weak Entity set.

Weak Entity set is the one whose primary key comprises its partial key as well as the primary key of its parent entity. This is the case because the entity set may not have sufficient attributes to form a primary key.

Interview Question 25. Explain the terms ‘Attribute’ and ‘Relations’

Attribute is described as the properties or characteristics of an entity. For Example, Employee ID, Employee Name, Age, etc., can be attributes of the entity Employee.

Relation is a two-dimensional table containing a number of rows and columns where every row represents a record of the relation. Here, rows are also known as ‘Tuples’ and columns are known as ‘Attributes’.

Interview Question 26. What are VDL and SDL?

VDL is View Definition Language which represents user views and their mapping to the conceptual schema.

SDL is Storage Definition Language which specifies the mapping between two schemas.

Interview Question 27. Define Cursor and its types.

Cursor is a temporary work area that stores the data, as well as the result set, occurred after manipulation of data retrieved. A cursor can hold only one row at a time.

The 2 types of Cursor are:

  • Implicit cursors are declared automatically when DML statements like INSERT, UPDATE, DELETE is executed.
  • Explicit cursors have to be declared when SELECT statements that are returning more than one row are executed.

Interview Question 28 What is the Database transaction?

Sequence of operation performed which changes the consistent state of the database to another is known as the database transaction. After the completion of the transaction, either the successful completion is reflected in the system or the transaction fails and no change is reflected.

Interview Question 29. Define Database Lock and its types.

Database lock basically signifies the transaction about the current status of the data item i.e. whether that data is being used by other transactions or not at the present point of time.

There are two types of Database lock: Shared Lock and Exclusive Lock.

Interview Question 30. What is Data Warehousing?

The storage as well as access to data, that is being derived from the transactions and other sources, from a central location in order to perform the analysis is called Data Warehousing.

Interview Question 31. What do you understand by Join?

Join is the process of deriving the relationship between different tables by combining columns from one or more tables having common values in each. When a table joins with itself, it is known as Self Join.

Interview Question 32. What do you understand by Index hunting?

Index hunting is the process of boosting the collection of indexes which helps in improving the query performance as well as the speed of the database.

Interview Question 33. How to improve query performance using Index hunting?

Index hunting help in improving query performance by:

  • Using a query optimizer to coordinate queries with the workload.
  • Observing the performance and effect of index and query distribution.

Interview Question 34. Differentiate between ‘Cluster’ and ‘Non-cluster’ index.

Clustered index alters the table and re-order the way in which the records are stored in the table. Data retrieval is made faster by using the clustered index.

A Non-clustered index does alter the records that are stored in the table but creates a completely different object within the table.

To know more about Database Interview Questions and queries you can direct ask us from live chat window.

Interview Question 35. What are the disadvantages of a Query?

Disadvantages of a Query are:

  • Indexes are not present.
  • Stored procedures are excessively compiled.
  • Difficulty in interfacing.

Interview Question 36. What do you understand by Fragmentation?

Fragmentation is a feature that controls the logical data units, also known as fragments that are stored at different sites of a distributed database system.

Intermediate database interview questions and solutions

Interview Question 37. Define Join types. (Very Important SQL interview Questions)

Given below are the types of Join, which are explained with respect to the tables as an Example.

employee table:

employee_info table:

a) Inner JOIN: Inner JOIN is also known as a simple JOIN. This SQL query returns results from both the tables having a common value in rows.

SQL Query:

SELECT * from employee, employee_info WHERE employee.EmpID = employee_info.EmpID ;

b) Natural JOIN: This is a type of Inner JOIN that returns results from both the tables having the same data values in the columns of both the tables to be joined.

SQL Query:

SELECT * from employee NATURAL JOIN employee_info;

c) Cross JOIN: Cross JOIN returns the result as all the records where each row from the first table is combined with each row of the second table.

SQL Query:

SELECT * from employee CROSS JOIN employee_info;

a) Right JOIN: Right JOIN is also known as Right Outer JOIN. This returns all the rows as a result from the right table even if the JOIN condition does not match any records in the left table.

SQL Query:

SELECT * from employee RIGHT OUTER JOIN employee_info on (employee.EmpID = employee_info.EmpID);

b) Left JOIN: Left JOIN is also known as Left Outer JOIN. This returns all the rows as a result of the left table even if the JOIN condition does not match any records in the right table. This is exactly the opposite of Right JOIN.

SQL Query:

SELECT * from employee LEFT OUTER JOIN employee_info on (employee.EmpID = employee_info.EmpID);

c) Outer/Full JOIN: Full JOIN return results in combining the result of both the Left JOIN and Right JOIN.

SQL Query:

SELECT * from employee FULL OUTER JOIN employee_info on (employee.EmpID = employee_info.EmpID);

Interview Question 38. What do you understand by ‘Atomicity’ and ‘Aggregation’?

Atomicity is the condition where either all the actions of the transaction are performed or none. This means, when there is an incomplete transaction, the database management system itself will undo the effects done by the incomplete transaction.

Aggregation is the concept of expressing the relationship with the collection of entities and their relationships.

Interview Question 39. Define Phantom deadlock.

Phantom deadlock detection is the condition where the deadlock does not actually exist but due to a delay in propagating local information, deadlock detection algorithms identify the deadlocks.

Interview Question 40. Define checkpoint.

Checkpoint declares a point before which all the logs are stored permanently in the storage disk and is the inconsistent state. In the case of crashes, the amount of work and time is saved as the system can restart from the checkpoint.

Interview Question 41. What is Database partitioning?

Database partitioning is the process of partitioning tables, indexes into smaller pieces in order to manage and access the data at a finer level.

This process of partitioning reduces the cost of storing a large amount of data as well as enhances the performance and manageability.

Interview Question 42. Explain the importance of Database partitioning.

The importance of Database partitioning are:

  • Improves query performance and manageability.
  • Simplifies common administration tasks.
  • Acts as a key tool for building systems with extremely high availability requirements.
  • Allows accessing a large part of a single partition.

Interview Question 43. Explain the Data Dictionary.

Data dictionary is a set of information describing the content and structure of the tables and database objects. The job of the information stored in the data dictionary is to control, manipulate and access the relationship between database elements.

Interview Question 44. Explain the Primary Key and Composite Key.

Primary Key is that column of the table whose every row data is uniquely identified. Every row in the table must have a primary key and no two rows can have the same primary key. Primary key value can never be null nor can it be modified or updated.

Composite Key is a form of the candidate key where a set of columns will uniquely identify every row in the table.

Interview Question 45. What do you understand by the Unique key?

A Unique key is the same as the primary key whose every row data is uniquely identified with a difference of null value i.e. Unique key allows one value as a NULL value.

Interview Question 46. What do you understand by Database Triggers?

A set of commands that automatically get executed when an event like Before Insert, After Insert, On Update, On Delete of row occurs in a table is called as Database trigger.

Interview Question 47. Define Stored procedures.

A Stored procedure is a collection of pre-compiled SQL Queries, which when executed denotes a program taking input, process and gives the output.

Interview Question 48. What do you understand by B-Trees?

B-Tree represents the data structure in the form of a tree for external memory that reads and writes large blocks of data. It is commonly used in databases and file systems where all the insertions, deletions, sorting, etc., are done in logarithmic time.

Interview Question 49. Name the different data models that are available for database systems.

Different data models are:

  • Relational model
  • Network model
  • Hierarchical model

To know more about Database Interview Questions and answers you can direct ask us from live chat window.

Interview Question 50. Differentiate between ‘DELETE’, ‘TRUNCATE’ and ‘DROP’ commands.

After the execution of ‘DELETE’ operation, COMMIT and ROLLBACK statements can be performed to retrieve the lost data.

After the execution of ‘TRUNCATE’ operation, COMMIT, and ROLLBACK statements cannot be performed to retrieve the lost data.

‘DROP’ command is used to drop the table or key like the primary key/foreign key.

Interview Question 51. How to Open SQL Server?

Goto -> Start -> All Programms -> Microsoft SQL Server 2008 R2 -> SQL Server management Studio.

Interview Question 52. What is Join?

 An SQL Join is used to combine data from two or more tables, based on a common field between them. For example, consider the following two tables.

zd5kVhf FVCshyp24hWSHLmU 0mQSyJ7WEc6znC4zZH9 6AZ0cs7aXqJAllBq cq6uopwgXsD4BBN6rk26EAWHDMb478Rg2p8iewQtiW6nHHi7MNzJ1lyaa00quwig
A3sbdOakbyvtf LO2GpididKeKxm9geg7Mf9DRvA JB 3l N8lpVvjqZVm9C6jlCe84M4Nvn00KB6X65aXiB kvVGUTuijQ68 ggoJ1uPa2jlh

join query that shows names of students enrolled in different courseIDs:

SELECT StudentCourse.CourseID, Student.StudentName

FROM StudentCourse

INNER JOIN Student 

ON StudentCourse.EnrollNo = Student.EnrollNo

ORDER BY StudentCourse.CourseID;

Result: 

dhhjX4NoQIgA0GX33wL5NHvn38iR6M1Yk6Vk7y4SHBI8kVYwdzC9Yi4C9L4OYLjhc C ysbnCOV87s03tJ6ybJFFLlbq1nUzOv3KmFSbF4EeW6quhUfFAu 9DitW3A

Interview Question 53. What is Identity?

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. A GUID column also generates numbers; the value of this cannot be controlled. Identity/GUID columns do not need to be indexed.

Interview Question 54. What is a view in SQL? How to create one?

A view is a virtual table based on the result-set of an SQL statement. We can create using create view syntax.

CREATE VIEW view_name AS

SELECT column_name(s)

FROM table_name

WHERE condition

Interview Question 55. What are the uses of view?

1. Views can represent a subset of the data contained in a table; consequently, a view can limit the degree of exposure of the underlying tables to the outer world: a given user may have permission to query the view, while denied access to the rest of the base table.

2. Views can join and simplify multiple tables into a single virtual table

3. Views can act as aggregated tables, where the database engine aggregates data (sum, average etc.) and presents the calculated results as part of the data

4. Views can hide the complexity of data; for example a view could appear as Sales2000 or Sales2001, transparently partitioning the actual underlying table

5. Views take very little space to store; the database contains only the definition of a view, not a copy of all the data which it presents.

6. Depending on the SQL engine used, views can provide extra security

Source: Wiki Page

Interview Question 55. What is a stored procedure?

A stored procedure is like 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.

Interview Question 57. What is a transaction? What are ACID properties?

A Database Transaction is a set of database operations that must be treated as whole, means either all operations are executed or none of them.

An example can be bank transaction from one account to another account. Either both debit and credit operations must be executed or none of them.

ACID (Atomicity, Consistency, Isolation, Durability) is a set of properties that guarantee that database transactions are processed reliably.

Interview Question 58. Write a SQL query to find the names of employees that begin with ‘A’?

To display name of the employees that begin with ‘A’, type in the below command:

SELECT * FROM Table_name WHERE EmpName like 'A%

Interview Question 59. Write a SQL query to get the third highest salary of an employee from employee_table?

SELECT TOP 1 salary

FROM(

SELECT TOP 3 salary

FROM employee_table

ORDER BY salary DESC) AS emp

ORDER BY salary ASC;

Interview Question 60: What is the need for group functions in SQL? 

Group functions work on the set of rows and returns one result per group. Some of the commonly used group functions are: AVG, COUNT, MAX, MIN, SUM, VARIANCE.

Interview Question 61. What is a Relationship and what are they?

Relation or links are between entities that have something to do with each other. Relationships are defined as the connection between the tables in a database. There are various relationships, namely:

  • One to One Relationship.
  • One to Many Relationship.
  • Many to One Relationship.
  • Self-Referencing Relationship.

Interview Question 62. How can you insert NULL values in a column while inserting the data?

NULL values can be inserted in the following ways:

  • Implicitly by omitting column from column list.
  • Explicitly by specifying NULL keyword in the VALUES clause

Interview Question 63. What is the main difference between ‘BETWEEN’ and ‘IN’ condition operators?

BETWEEN operator is used to display rows based on a range of values in a row whereas the IN condition operator is used to check for values contained in a specific set of values.

Example of BETWEEN:

SELECT * FROM Students where ROLL_NO BETWEEN 10 AND 50;

Example of IN:

SELECT * FROM students where ROLL_NO IN (8,15,25);

Interview Question 64. What is the need of MERGE statement?

This statement allows conditional update or insertion of data into a table. It performs an UPDATE if a row exists, or an INSERT if the row does not exist.

Interview Question 65. What is CLAUSE in SQL?

SQL clause helps to limit the result set by providing a condition to the query. A clause helps to filter the rows from the entire set of records.

For example – WHERE, HAVING clause.

Interview Question 66. What is the difference between ‘HAVING’ CLAUSE and a ‘WHERE’ CLAUSE?

HAVING clause can be used only with SELECT statement. It is usually used in a GROUP BY clause and whenever GROUP BY is not used, HAVING behaves like a WHERE clause.

Having Clause is only used with the GROUP BY function in a query whereas WHERE Clause is applied to each row before they are a part of the GROUP BY function in a query.

Interview Question 67. What are the various levels of constraints?

Constraints are the representation of a column to enforce data entity and consistency. There are two levels  of a constraint, namely:

  • column level constraint
  • table level constraint

Interview Question 68. List some case manipulation functions in SQL?

There are three case manipulation functions in SQL, namely:

  • LOWER: This function returns the string in lowercase. It takes a string as an argument and returns it by converting it into lower case. Syntax:

LOWER(‘string’)

  • UPPER: This function returns the string in uppercase. It takes a string as an argument and returns it by converting it into uppercase. Syntax:

UPPER(‘string’)

  • INITCAP: This function returns the string with the first letter in uppercase and rest of the letters in lowercase. Syntax:

INITCAP(‘string’)

Interview Question 70. What is an ALIAS command?

ALIAS name can be given to any table or a column. This alias name can be referred in WHERE clause to identify a particular table or a column.

For example-

Select emp.empID, dept.Result from employee emp, department as dept where emp.empID=dept.empID

In the above example, emp refers to alias name for employee table and dept refers to alias name for department table.

Interview Question 71. What are aggregate and scalar functions?

Aggregate functions are used to evaluate mathematical calculation and returns a single value. These calculations are done from the columns in a table. For example- max(),count() are calculated with respect to numeric.

Scalar functions return a single value based on the input value. For example – UCASE(), NOW() are calculated with respect to string.

To know more about Database Interview Questions and queries you can direct ask us from live chat window.

Interview Question 72. How can you fetch alternate records from a table?

You can fetch alternate records i.e both odd and even row numbers. For example- To display even numbers, use the following command:

Select studentId from (Select rowno, studentId from student) where mod(rowno,2)=0

Now, to display odd numbers:

Select studentId from (Select rowno, studentId from student) where mod(rowno,2)=1

Interview Question 73. Name the operator which is used in the query for pattern matching?

LIKE operator is used for pattern matching, and it can be used as -.

  1. % – It matches zero or more characters.

For example- select * from students where studentname like ‘a%’

_ (Underscore) – it matches exactly one character.

For example- select * from student where studentname like ‘abc_’

Interview Question 74. How can you select unique records from a table?

 You can select unique records from a table by using the DISTINCT keyword.

Select DISTINCT studentID from Student

Using this command, it will print unique student id from the table Student.

Interview Question 75: How can you fetch first 5 characters of the string?

There are a lot of ways to fetch characters from a string. For example:

Select SUBSTRING(StudentName,1,5) as studentname from student

Interview Question 76: What is Auto Increment in SQL?

Auto increment keyword allows the user to create a unique number to get generated whenever a new record is inserted into the table.

This keyword is usually required whenever PRIMARY KEY is used.

AUTO INCREMENT keyword can be used in Oracle and IDENTITY keyword can be used in SQL SERVER.

Interview Question 77. What are the different authentication modes in SQL Server? How can it be changed?

Windows mode and Mixed Mode – SQL and Windows. You can go to the below steps to change authentication mode in SQL Server:

  • Click Start> Programs> Microsoft SQL Server and click SQL Enterprise Manager to run SQL Enterprise Manager from the Microsoft SQL Server program group.
  • Then select the server from the Tools menu.
  • Select SQL Server Configuration Properties, and choose the Security page.

Interview Question 78. What are STUFF and REPLACE functions?

STUFF Function: This function is used to overwrite an existing character or inserts a string into another string. Syntax:

STUFF(string_expression,start, length, replacement_characters)

where,

string_expression: it is the string that will have characters substituted

start: This refers to the starting position

length: It refers to the number of characters in the string which are substituted.

replacement_string: They are the new characters which are injected in the string.

REPLACE function: This function is used to replace the existing characters of all the occurrences. Syntax:

REPLACE (string_expression, search_string, replacement_string)

Advance database interview questions and Answers

Interview Question 79. You forgot your root password, what do you do ?

Start the database with the command of “skip-grants-table”.

After you set the new password, restart the database in normal mode and enter the new password.

Interview Question 80. Are NULL values equal to a zero ?
No, because a “zero” has a numerical manner and NULL represent the absence of a character. This happens when the character is unknown or unavailable. Additionally, NULL shouldn’t be confused with blank space because data record without any value assigned is not the same as a plain blank space, with no data records attached.

for more information and deep learning on database testing join our Advance Software Testing Corporate training programme

Interview Question 81. Data disk gets overloaded, what do you do ?

You should apply a soft link: these links create a location where you are able to store your .frm and .idb files. This will resolve the overload problem.

Interview Question 82. What are the most basic MySQL architecture components ?

There are three main components:

  1. Query optimizer;
  2. Connection manager;
  3. Pluggable engine.

Interview Question 83. How would you check your current SQL version ?

You can get the most current SQL version by issuing this command:

SELECT VERSION()

Interview Question 84. Get alternative odd records from the table

Select employeeId from (Select rowno, employeetId from employee) where mod(rowno, 2)=1

Interview Question 85. What command would select a unique record from the table ?

Select DISTINCT employeeID from Employee

Interview Question 86. What are variables of SQL ?

In SQL, there are two different variables:

  • Local – these variables can only exist in one single function
  • Global – are the opposite of local, which means they can be located through ought the entire program.

Interview Question 87. How would you retrieve common records from two tables ?

Select employeeID from employee. INTERSECT Select EmployeeID from WorkShift

Interview Question 88. What is Locking, Blocking and Deadlocking?

  • Locking: Locking occurs when a connection needs access to a piece of data in a database and it locks it for certain use so that no other transaction is able to access it.
  • Blocking: Blocking occurs when a transaction tries to acquire an incompatible lock on a resource that another transaction has already locked. The blocked transaction remains blocked until the blocking transaction releases the lock.
  • Deadlocking: Deadlocking occurs when two or more transactions have a resource locked, and each transaction requests a lock on the resource that another transaction has already locked. Neither of the transactions here can move forward, as each one is waiting for the other to release the lock.

Interview Question 89. What is BIN function?

It converts a decimal number to a binary number.

Example: SELECT BIN(18);

Result: 10010

Interview Question 90. What is Binary function?

It converts a value to a binary string

SELECT BINARY "Rohan";

Interview Question 91. What is COALESCE function ?

It returns the first non-null expression in a list

SELECT COALESCE(NULL,NULL,'Rajat',NULL,'Geeks');

Result : Rajat

Interview Question 92. How to get current user?

SELECT CURRENT_USER();

Interview Question 93. What “SELECT IF(200<500, “YES”, “NO”);” will return?

Yes

Interview Question 94. What “ SELECT NULLIF(25.11, 25); ” will return?

It returns the first expression if the two expressions are not equal. If the expressions are equal, NULLIF returns a null value of the type of the first expression. I.e, 25.11

Interview Question 95. How the Inner Join in SQL is different from that of Outer Join?

 An Inner join is the one that is useful for the purpose of returning the rows provided at least two tables are met critically. On the other hand, the outer join is the one that is useful for returning the value of rows and tables that generally include the records that must be same in all the tables. 

Interview Question 99. Tell something about the Temp Table?

 It is basically a structure in the SQL that is used for storing any sort of data that is not permanent or need to be stored for a specific time period. Depending on the needs, it is possible to extend the space up to any extend. Generally, limited space is kept reserved as the temp table.

Interview Question 100. Share some Important SQL Functions you use in day to day practice?

LOWER Function: (Column/Expression): 

  • It Converts Alpha Character Values to Lower Case.
  • The Return Value Has The Same Data Type as Argument CHAR Type (CHAR or VARCHAR2)

UPPER Function: 

  • It Converts the Alpha Character Values to Upper Case.
  • The Return Value Has The Same Data Type as Argument CHAR.

INITCAP Function:

  • It Converts Alpha Character Values into Upper Case For The First Letter of Each Word, keeping all Other Letter in Lower Case.
  • Words are Delimited by White Space or Characters That are Not Alphanumeric

LPAD Function:

  • Pads The Character Value Right Justified to a Total Width of ‘n’ Character Positions.
  • The Default Padding Character in Space.

RPAD Function:

  • Pads the Character Value Left Justified to a Total Width of ‘n’ Character positions.
  • The Default Padding Character is Space.

LTRIM Function:

  • It Enables to TRIM Heading Character From a Character String.
  • All The Leftmost Character That Appear in The SET are Removed.

RTRIM Function:

  • It Enables the Trimming of Trailing Character From a Character STRING.
  • All the Right Most Characters That Appear in The Set are Removed.

TRIM Function:

  • It Enables to TRIM Heading or Trailing Character or Both From a Character String.
  • If LEADING is Specified Concentrates On Leading Characters.
  • If TRAILING is Specified Concentrates on Trailing Characters.
  • If BOTH OR None is Specified Concentrates Both on LEADING and TRAILING.
  • Return the VARCHAR2 Type.

Interview Question 101. What is Bad File?

This file extension is .bad

Bad file stores rejected records based on

  1. Data type mismatch
  2. Business rule violation

The bad file is automatically created as the same name as Flat file, we can also create Bad file explicitly by using bad file clause within the control file.

This was the first updated set of Database Interview Questions and queries. If you have any doubt please do not hesitate to ask us on live chat we will be happy to discuss. Also next set of testing related interview questions comming soon so stay tuned with us on Facebook, twitter, instagram by liking our page.

Ask if you want any specific targeted Database Interview Questions and answers Set by live chat or emailing us on hello@deviop.com

How do I prepare for a database developer interview?

u003cstrongu003ePrepareu003c/strongu003e with all the basic DML and DDL commands. Learn the underlying concepts of procedures, views, joins, tables, aggregate functions, nested queries. A lot of times, the concepts and the reason why a command needs to be used are further important than the command itself

What are the questions asked in SQL interview?

What is DBMS? A Database Management System (DBMS) is a program that controls creation, maintenance and use of a database. …u003cbru003eWhat is RDBMS? …u003cbru003eWhat is SQL? …u003cbru003eWhat is a Database? …u003cbru003eWhat are tables and Fields? …u003cbru003eWhat is a primary key? …u003cbru003eWhat is a unique key? …u003cbru003eWhat is a foreign key?u003cbru003eu003ca href=u0022https://deviop.com/?p=48272u0022 class=u0022rank-math-linku0022u003eMore items…u003c/au003e

What is a * in SQL?

(*) in u003cstrongu003eSQLu003c/strongu003e represent u003cstrongu003etheu003c/strongu003e column names and it means all column. Sample table name students having columns. Id, Name, rollno etc. If you query data from table students.

Why * is used in SQL?

Derived from this mathematical u0022set-theoryu0022 concept, in u003cstrongu003eSQLu003c/strongu003e, * simply means to select all fields of the table or result set. The asterisk (*) is actually derived from the Kleene star of Discrete Mathematics, where * is a common notation.

Knowledge should be transmitted, Share this: