Database Interview Questions & Answers

Question 1. Explain What Is Sqlite Transactions?

Answer :

The transaction is referred as a unit of work that is performed against a database. It is the propagation of one or more changes to the database. Properties of transactions are determined by ACID.

• Atomicity: It ensures that all work unit are successfully completed.
• Consistency: It ensures that the database changes states upon a successfully committed transaction.
• Isolation: It enables transactions to operate independently of and transparent to each other.
• Durability: It ensures that the result or effect of a committed transaction persists in case of a system failure.

Question 2. What Does Sql Stand For, And What Is It Used For?

Answer :

SQL stands for structured query language, used with relational databases. It is used to query, update, and retrieve the contents of databases.

Question 3. List Out The Areas Where Sql Lite Works Well?

Answer :

SQL lite works well with :

• Embedded devices and the internet of things.
• Application file format.
• Data Analysis.
• Websites.
• Cache for enterprise data.
• Server side database.
• File archives.
• Internal or temporary databases.
• Replacement for ad hoc disk files.
• Experimental SQL language extensions.
• Stand-in for an enterprise database during demos or testing.

Question 4. What Are Attributes?

Answer :

An attribute is a column in a table.

Question 5. Differentiate Between A Having Clause And A Where Clause.

Answer :

HAVING CLAUSE :

• HAVING CLAUSE is used only with the SELECT statement.
• It is generally used in a GROUP BY clause in a query.
• If GROUP BY is not used, HAVING works like a WHERE clause.

WHERE Clause :

• It is applied to each row before they become a part of the GROUP BY function in a query.

Question 6. What Is Data Mining And How Can It Be Used?

Answer :

Data mining refers to any procedure for collecting, analyzing, and summarizing the contents of a database. It can be used to judge the success of a business, marketing campaigns, and to forecast future trends.

Question 7. Mention What Are The Sql Lite Storage Classes?

Answer :

SQL lite storage classes include :

• Null: The value is a NULL value.
• Integer: The value is a signed integer (1,2,3, etc.).
• Real: The value is a floating point value, stored as an 8 byte IEEE floating point number.
• Text: The value is a text string, stored using the database encoding ( UTF-8, UTF-16BE).
• BLOB (Binary Large Object): The value is a blob of data, exactly stored as it was input.

Question 8. What Is Database Normalization And Why Is It Done?

Answer :

This is the process of organizing data in a database efficiently, and it is done to make sure that connections and dependencies between data make sense, and to get rid of redundant data.

Question 9. What Do Constraints Do? What Are The Types Of Constraints?

Answer :

Constraints are used to prevent the database from losing internal and external integrity. The types are: check, not null, unique, primary key, and foreign key.

Question 10. What Are Fact Tables?

Answer :

Tables which track the progress of a certain process or activity, the primary tables of databases.

Question 11. Describe The Three Levels Of Data Abstraction?

Answer :

• Physical level: The lowest level of abstraction describes how data are stored.
• Logical level: The next higher level of abstraction, describes what data are stored in database and what relationship among those data.
• View level: The highest level of abstraction describes only part of entire database.

Question 12. Explain What Is The Use Of Sqlite Group By Clause?

Answer :

The SQLITE group by clause is used in collaboration with the SELECT statement to arrange identical data into groups.

Question 13. When Is The Update_statistics Command Used?

Answer :

• When the processing of large data is done, this command is used.
• Whenever large number of deletions, modification or copy takes place into the tables, the indexes need to be updated to take care of these changes. UPDATE_STATISTICS performs this job.

Question 14. Explain How Boolean Values In Sql Lite Are Stored?

Answer :

Boolean values in SQL lite are stored as integers 0 (false) and 1 (true). SQL Lite does not have a separate Boolean storage class.

Question 15. Explain What Is Sql Lite?

Answer :

SQL LITE is a mostly ACID compliant relational database management system contained in a relatively small C programming library.

Question 16. Give A Comment On Transaction?

Answer :

• Using transactions we can group all SQL commands into a single unit.
• The transaction begins with some task and finishes only when all tasks within it are over.
• The transaction gets over successfully only when all commands in it are successfully over. Even if one command fails, the whole transaction fails.
• The BEGIN TRANSACTION, ROLLBACK TRANSACTION, and COMMIT TRANSACTION statements are used to work with transactions.
• A group of tasks starts with the begin statement.
• In case of any problem, the rollback command is executed to abort the transaction.
• If all the tasks run successfully, all commands are executed through commit statement.

Question 17. List Out The Standard Sql Lite Commands?

Answer :

The standard SQL Lite commands interact with relational databases are similar to SQL. They are

• SELECT
• CREATE
• INSERT
• UPDATE
• DROP
• DELETE

Based on their operational nature these commands can be classified.

Question 18. Define The "integrity Rules"?

Answer :

There are two Integrity rules.

Entity Integrity: States that "Primary key cannot have NULL value".

Referential Integrity: States that "Foreign Key can be either a NULL value or should be Primary Key value of other relation.

Question 19. What Is Database?

Answer :

A database is a logically coherent collection of data with some inherent meaning, representing some aspect of real world and which is designed, built and populated with data for a specific purpose.

Question 20. Segregate Database Technology’s Development?

Answer :

The development of database technology is divided into:

• Structure or data model
• Navigational model
• SQL/ relational model

Question 21. What Are The Features Of Database Language?

Answer :

A database language may also incorporate 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.

Question 22. What Do Database Languages Do?

Answer :

As special-purpose languages, they have:

• Data definition language
• Data manipulation language
• Query language

Question 23. Define Database Model?

Answer :

A data model determining fundamentally how data can be stored, manipulated and organised and the structure of the database logically is called database model.

Question 24. Enlist The Various Relationships Of Database?

Answer :

The various relationships of database are:

• One-to-one: Single table having drawn relationship with another table having similar kind of columns.
• One-to-many: Two tables having primary and foreign key relation.
• Many-to-many: Junction table having many tables related to many tables.

Question 25. Define Normalization?

Answer :

Organized data void of inconsistent dependency and redundancy within a database is called normalization.

Question 26. Enlist The Advantages Of Normalizing Database?

Answer :

Advantages of normalizing database are:

• No duplicate entries
• Saves storage space
• Boasts the query performances.

Question 27. Define De Normalization?

Answer :

Boosting up database performance, adding of redundant data which in turn helps rid of complex data is called denormalization.

Question 28. Define Ddl And Dml?

Answer :

Managing properties and attributes of database is called Data Definition Language(DDL).

Manipulating data in a database such as inserting, updating, deleting is defined as Data Manipulation Language. (DML)

Question 29. Enlist Some Commands Of Ddl?

Answer :

CREATE:  Create is used in the CREATE TABLE statement.
Syntax is:     CREATE TABLE [column name] ( [column definitions] ) [ table parameters]

ALTER:  It helps in modification of an existing object of database.
Syntax is: ALTER objecttype objectname parameters.

DROP: It destroys an existing database, index, table or view.
Syntax is: DROP objecttype objectname.

Question 30. Define Union All Operator And Union?

Answer :

Full recordings of two tables is Union All operator.A distinct recording of two tables is Union.

Question 31. Define Cursor?

Answer :

A database object which helps in manipulating data row by row representing a result set is called cursor.

Question 32. Enlist The Cursor Types?

Answer :

They are:

Dynamic: it reflects changes while scrolling.

Static: doesn’t reflect changes while scrolling and works on recording of snapshot.

Keyset: data modification without reflection of new data is seen.

Question 33. Define Sub-query?

Answer :

A query contained by a query is called Sub-query.

Question 34. Why Is Group-clause Used?

Answer :

Group-clause uses aggregate values to be derived by collecting similar data.

Question 35. Compare Non-clustered And Clustered Index?

Answer :

Both having B-tree structure, non-clustered index has data pointers enabling one table many non-clustered indexes while clustered index is distinct for every table.

Question 36. Define Aggregate Functions?

Answer :

Functions which operate against a collection of values and returning single value is called aggregate functions.

Question 37. Define Scalar Functions?

Answer :

Scalar function is depended on the argument given and returns sole value.

Question 38. What Restrictions Can You Apply When You Are Creating Views?

Answer :

Restrictions that are applied are:

• 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.

Question 39. Define “correlated Sub Queries”?

Answer :

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.

Question 40. Define Data Warehousing?

Answer :

Storage and access of data from the central location in order to take some strategic decision is called Data Warehousing. Enterprise management is used for managing the information whose framework is known as Data Warehousing.

Question 41. Define Join And Enlist Its Types?

Answer :

Joins help in explaining the relation between different tables. They also enable you to select data with relation to data in another table.

The various types are:

INNER JOINs: Blank rows are left in the middle while more than equal to two tables are joined.

OUTER JOINs: Divided into Left Outer Join and Right Outer Join. Blank rows are left at the specified side by joining tables in other side.
Other joins are CROSS JOINs, NATURAL JOINs, EQUI JOIN and NON-EQUI JOIN.

Question 42. What Do You Mean By Index Hunting?

Answer :

Indexes help in improving the speed as well as the query performance of database. The procedure of boosting the collection of indexes is named as Index hunting.

Question 43. How Does Index Hunting Help In Improving Query Performance?

Answer :

Index hunting helps in improving the speed as well as the query performance of database. The followed measures are achieved to do that:

• The query optimizer is used to coordinate the study of queries with the workload and the best use of queries suggested based on this.
• Index, query distribution along with their performance is observed to check the effect.
• Tuning databases to a small collection of problem queries is also recommended.

Question 44. Enlist The Disadvantages Of Query?

Answer :

The disadvantages of query are:

• No indexes
• Stored procedures are excessively compiled.
• Triggers and procedures are without SET NOCOUNT ON.
• Complicated joins making up inadequately written query.
• Cursors and temporary tables showcase a bad presentation.

Question 45. Enlist Ways To Efficiently Code Transactions?

Answer :

Ways to efficiently code transactions:

• User input should not be allowed while transactions.
• While browsing, transactions must not be opened of data.
• Transactions must be kept as small as possible.
• Lower transaction segregation levels.
• Least information of data must be accessed while transacting.

Question 46. What Is Executive Plan?

Answer :

Executive plan can be defined as:

• SQL Server caches collected procedure or the plan of query execution and used thereafter by subsequent calls.
• An important feature in relation to performance enhancement.
• Data execution plan can be viewed textually or graphically.

Question 47. Define B-trees?

Answer :

A data structure in the form of tree which stores sorted data and searches, insertions, sequential access and deletions are allowed in logarithmic time.

Question 48. Differentiate Table Scan From Index Scan?

Answer :

Iterating over all the table rows is called Table Scan while iterating over all the index items is defined as Index Scan.

Question 49. What Do You Mean By Fill Factor Concept With Respect To Indexes?

Answer :

Fill Factor can be defined as being that value which defines the percentage of left space on every leaf-level page that is to be packed with data. 100 is the default value of Fill Factor.

Question 50. Define Fragmentation?

Answer :

Fragmentation can be defined as a database feature of server that promotes control on data which is stored at table level by the user.

Question 51. What Do You Mean By Query Evaluation Engine?

Answer :

Query Evaluation Engine executes the low-level instructions that are generated by the compiler.

Question 52. Define Ddl Interpreter?

Answer :

DDL statements are interpreted and recorded in tables called metadata.

Question 53. Define Database System?

Answer :

DBMS along with database is called Database system.

Question 54. Explain The Importance Of Partitioning?

Answer :

Splitting of one table which is large into smaller database entities logically is called database partitioning. Its benefits are:

• To improve query performance in situations dramatically when mostly rows which are heavily accessed are in one partition.
• Accessing large parts of a single partition.
• Slower and cheaper storage media can be used for data which is seldom used.

Question 55. What Is Database Partitioning?

Answer :

Division of logical database into independent complete units for improving its management, availability and performance is called Database partitioning.

Question 56. Define Atomicity And Aggregation?

Answer :

Atomicity: It’s an all or none concept which enables the user to be assured 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.

Question 57. Enlist The Various Transaction Phases?

Answer :

The various transaction phases are:

• Analysis Phase.
• Redo Phase.
• Undo Phase.

Question 58. Define Object-oriented Model?

Answer :

Compilations of objects make up this model in which values are stored within instance variables which is inside the object. The object itself comprises bodies of object for its operation which are called methods. Objects containing same kind of variables and methods are called classes.

Question 59. Define Entity?

Answer :

It can be defined as being a ‘thing’ with an independent existence in the real world.

Question 60. Define Entity Set ?

Answer :

Compilation of all entries of any particular type of entry in the database is called Entity Set.