40+ TOP Database Interview Questions and Answers
1. Define Database.
A fixed collection of figures known as data is called database.
2. What is DBMS?
Database Management Systems (DBMS) are applications designed especially which enable user to influence with other applications.
3. What are the features of Database language?
A database language may also include features like:
DBMS-specific Configuration and management of storage engine.
Computations to modification of query results by computations, like summing, counting, averaging, grouping, sorting and cross-referencing Constraint enforcement Application Programming Interface.
4. Define database model.
A data model conditioned fundamentally how data can be stored, worked and organised and the structure of the database logically is called database model.
5. Define Normalization.
Organized data void of inconsistent dependency and sacking within a database is called normalization.
6. Enlist some commands of DDL.
CREATE:Create is used in the CREATE TABLE statement.
CREATE TABLE [column name] ( [column definitions] ) [ table parameters]
ALTER: It helps in modification of an existing object of database.
ALTER objecttype objectname parameters.
DROP: It destroys an existing database, index, table or view.
DROP objecttype objectname.
7. Why is group-clause used?
Group-clause uses aggregate values to be obtain by collecting similar data.
8. Define Aggregate functions.
Functions which operate against a collection of values and depart the single value is called aggregate functions
9. Define Scalar functions.
Scalar function is depended on the argument given and returns sole value.
10. What restrictions can you apply when you are creating views?
Following are the restrictions that are creating views:
Only the current database can have views.
You are not liable to change any computed value in any particular view.
Integrity constants decide the functionality of INSERT and DELETE.
Full-text index definitions cannot be applied.
Temporary views cannot be created.
Temporary tables cannot contain views.
No association with DEFAULT definitions.
Triggers such as INSTEAD OF is associated with views.
11. Define “correlated subqueries”.
A ‘correlated subquery’ is a sort of sub query but correlated subquery is reliant on another query for a value that is returned. In case of execution, the sub query is executed first and then the correlated query.
12. List the disadvantages of query.
Stored procedures are unnecessarily compiled.
Triggers and methods are without SET NOCOUNT ON.
Complicated joins making up insufficient written query.
Cursors and temporary tables showcase a bad presentation.
13. What is Executive Plan?
An executive plan is,Server (SQL) supply collected method or the plan of query execution and used thereafter by following calls.
An important feature in relation to presentation enhancement.
Data execution plan can be viewed textually or graphically.
Complicated joins making up insufficient written query.
Cursors and temporary tables showcase a bad presentation.
14. Define Fragmentation.
A database feature of server that develop control on data which is stored at table level by the user is called Fragmentation.
15. Differentiate Nested Loop, Hash Join and Merge Join.
Nested loop (loop over loop)
An outer loop within an inner loop is formed contains few entries and then for individual entry, inner loop is individually processed.
Select col1.*, col2.* from coll, col2 where coll.col1=col2.col2;
It’s processing takes place in this way:
For i in (select * from col1) loop
For j in (select * from col2 where col2=i.col1) loop
Results are displayed;
End of the loop;
End of the loop;
The Steps of nested loop are:
Identify outer (driving) table
Assign inner (driven) table to outer table.
For every row of outer table, access the rows of inner table.
Nested Loops is executed from the inner to the outer as:
While joining large tables, the use of Hash Join is preferred.
Algorithm of Hash Join is divided into:
Build: It is a hash table having in-memory which is present on the smaller table.
Probe: this hash value of the hash table is applicable for each second row element.
Sort merge join
Two independent sources of data are joined in sort merge join. They presentation is better as differentiate to nested loop when the data volume is big enough but normally it is not good as hash joins.
The full operation can be divided into parts of two:
Sort join operation :
Get first row R1 from input1
Get first row R2 from input2.
Merge join operation:
‘while’ is not present at either loop’s end.
if R1 joins with R2
next row is got R2 from the input 2
return (R1, R2)
else if R1 < style=””> next row is got from R1 from input 1
next row is got from R2 from input 2
end of the loop
16. What is Database partitioning?
Division of logical database into independent complete units for upgrade its management, availability and performance is called Database partitioning.
17. Define Atomicity and Aggregation.
Atomicity: It’s an all or none concept which permit the user to be possessed of incomplete transactions to be taken care of. The actions involving incomplete transactions are left undone in DBMS.
Aggregation: The collected entities and their relationship are aggregated in this model. It is mainly used in expressing relationships within relationships.
18. Define Entity.
It can be defined as being a ‘thing’ with an independent existence in the real world.
19. Define Entity Set.
Collection of all entries of any particular type of entry in the database is called Entity Set.
20. What do you mean by Entity type extension?
Collection of similar entity types into one particular type which is grouped together as an entity set is called Entity type extension.
21. 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.
22. 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.
23. 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.
24. What is DDL interpreter?
DDL Interpreter interprets the DDL statements and records the generated statements in the table containing metadata.
25. 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.
26. Explain the terms ‘Record’, ‘Field’ and ‘Table’ in terms of database.
Record: Record is a collection of values or fields of a specific entity. Example: An employee, Salary account, etc.
Field: A field refers to an area within a record that is reserved for a specific piece of data. Example: Employee ID.
Table: Table is the collection of records of specific types. Example: Employee table is a collection of record related to all the employees.
27. 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.
28. 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 is not reflected in views.
29. What are the advantages and disadvantages of views in the database?
Advantages of Views:
As there is no physical location where the data in views 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.
More memory is occupied when the view is created for large tables.
30. 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.
31. What is an Entity set?
The entity set specifies the collection of all entities of a particular entity type in the database. An entity set is known as the set of all the entities which share the same properties.
For example, a set of people, a set of students, a set of companies, etc.
32. What is an Extension of entity type?
An extension of an entity type is specified as a collection of entities of a particular entity type that are grouped into an entity set.
33. What is Weak Entity set?
An entity set that doesn’t have sufficient attributes to form a primary key is referred to as a weak entity set. The member of a weak entity set is known as a subordinate entity. Weak entity set does not have a primary key, but we need a mean to differentiate among all those entries in the entity set that depend on one particular strong entity set.
34. What is an attribute?
An attribute refers to a database component. It is used to describe the property of an entity. An attribute can be defined as the characteristics of the entity. Entities can be uniquely identified using the attributes. Attributes represent the instances in the row of the database.
For example: If a student is an entity in the table then age will be the attribute of that student.
35. What are the integrity rules in DBMS?
Data integrity is one significant aspect while maintaining the database. So, data integrity is enforced in the database system by imposing a series of rules. Those set of integrity is known as the integrity rules.
There are two integrity rules in DBMS:
Entity Integrity : It specifies that “Primary key cannot have a NULL value.”
Referential Integrity: It specifies that “Foreign Key can be either a NULL value or should be the Primary Key value of other relation
36. What do you mean by extension and intension?
Extension: The Extension is the number of tuples present in a table at any instance. It changes as the tuples are created, updated and destroyed. The actual data in the database change quite frequently. So, the data in the database at a particular moment in time is known as extension or database state or snapshot. It is time dependent.
Intension: Intension is also known as Data Schema and defined as the description of the database, which is specified during database design and is expected to remain unchanged. The Intension is a constant value that gives the name, structure of tables and the constraints laid on it.
37. What is System R? How many of its two major subsystems?
System R was designed and developed from 1974 to 1979 at IBM San Jose Research Centre. System R is the first implementation of SQL, which is the standard relational data query language, and it was also the first to demonstrate that RDBMS could provide better transaction processing performance. It is a prototype which is formed to show that it is possible to build a Relational System that can be used in a real-life environment to solve real-life problems.
Following are two major subsystems of System R:
System Relational Data System
38. What is Data Independence?
Data independence specifies that “the application is independent of the storage structure and access strategy of data.” It makes you able to modify the schema definition at one level without altering the schema definition in the next higher level.
It makes you able to modify the schema definition in one level should not affect the schema definition in the next higher level.
There are two types of Data Independence:
Physical Data Independence: Physical data is the data stored in the database. It is in the bit-format. Modification in physical level should not affect the logical level.
For example: If we want to manipulate the data inside any table that should not change the format of the table.
Logical Data Independence: Logical data in the data about the database. It basically defines the structure. Such as tables stored in the database. Modification in logical level should not affect the view level.
For example: If we need to modify the format of any table, that modification should not affect the data inside it.
NOTE: Logical Data Independence is more difficult to achieve.
39. What are the three levels of data abstraction?
Following are three levels of data abstraction:
Physical level: It is the lowest level of abstraction. It describes how data are stored.
Logical level: It is the next higher level of abstraction. It describes what data are stored in the database and what relationship among those data.
View level: It is the highest level of data abstraction. It describes only part of the entire database.
For example- User interact with the system using the GUI and fill the required details, but the user doesn’t have any idea how the data is being used. So, the abstraction level is absolutely high in VIEW LEVEL.
Then, the next level is for PROGRAMMERS as in this level the fields and records are visible and the programmer has the knowledge of this layer. So, the level of abstraction here is a little low in VIEW LEVEL.
And lastly, physical level in which storage blocks are described.
40. What is Join?
The Join operation is one of the most useful activities in relational algebra. It is most commonly used way to combine information from two or more relations. A Join is always performed on the basis of the same or related column. Most complex queries of SQL involve JOIN command.
There are following types of join:
Inner joins: Inner join is of 3 categories. They are:
Outer joins: Outer join have three types. They are:
Left outer join
Right outer join
Full outer join