DBMS Interview Questions Set -2

Q1. What is the E-R model in the DBMS?

E-R model is known as an Entity-Relationship model in the DBMS which is based on the concept of the Entities and the relationship that exists among these entities.It is a diagrammatic approach to database design, where you represent real-world objects as entities and mention relationships between them. This approach helps the team of DBAs’ to understand the schema easily.

Q2. What do you understand by query optimization?

Query optimization is the phase that identifies a plan for evaluation query that has the least estimated cost. This phase comes into the picture when there are a lot of algorithms and methods to execute the same task.

Q3. Mention the advantages of query optimization.

1). The output is provided faster

2). A larger number of queries can be executed in less time

3). Reduces time and space complexity

Q4. What do you understand by intention and extension?

Intension: Intension or most commonly known as Database schema defines the description of the database. This is specified during the database design and mostly remains unchanged.

Extension: Extension is the number of tuples available in the database at any instance of time. This value keeps changing as and when the tuples are created, updated, and destroyed. So, the data present in the database at a specific instance of time is known as the extension of the database or most commonly known the snapshot of the database.

Q5. What do you understand by cursor? 

A cursor is a database object which helps in manipulating data, row by row, and represents a result set.

Q6. Mention the different types of the cursor.

Implicit cursor: This type of cursor is declared automatically as soon as the execution of SQL takes place. Here, the user is not indicated about the declaration of the cursor.

Explicit cursor: This type of cursor is defined by the PL/ SQL, as it handles a query in more than a single row.

Q7. Explain the terms specialization and generalization

Specialization: Specialization is a process of defining a set of subclasses of the entity type. Here, each subclass will contain all the attributes and relationships of the parent entity. Apart from this, the subclasses may contain additional attributes and relationships specific to themselves.

Generalization: Generalization is a process of finding relations, and common attributes for a particular set of entities, and finally defining a common superclass for them.

Q8. What do you understand by Data Independence?

When you say an application has data independence, it implies that the application is independent of the storage structure and data access strategies of data.

Q9. What is Index hunting and how does it help in improving query performance?

The process of boosting a collection of indexes is known as Index hunting. This is done as indexes improve the query performance and the speed at which they are processed.

1). The best queries are suggested using the query optimizer.

2). Index, query distribution, and their performance are used as metrics to check the effect

3). Databases are tuned into a small collection of problem queries.

Q10. Explain what is a deadlock and mention how it can be resolved.

Deadlock is a situation that occurs when two transactions wait on a resource that is locked or another transaction holds.  Deadlocks can be prevented by making all the transactions acquire all the locks at the same instance of time. So, once deadlock occurs, the only way to cure is to abort one of the transactions and remove the partially completed work.

Q11. What are the differences between an exclusive lock and a shared lock?

Exclusive Lock: An exclusive lock is a lock on a data item when a transaction is about to perform the write operation.

Shared Lock: A shared lock allows more than one transaction to read the data items.

Q12. Mention the issues with traditional file-based systems that make DBMS a better choice.

The absence of indexing in a traditional file-based system leaves us with the only option of scanning the full page and hence making the access of content tedious and super slow. The other issue is redundancy and inconsistency as files have many duplicate and redundant data and changing one of them makes all of them inconsistent. Accessing data is harder in traditional file-based systems because data is unorganized in them.

Another issue is the lack of concurrency control, which leads to one operation locking the entire page, as compared to DBMS where multiple operations can work on a single file simultaneously.

Integrity check, data isolation, atomicity, security, etc. are some other issues with traditional file-based systems for which DBMSs have provided some good solutions.

Q13. Explain the difference between intension and extension in a database.

Intension: Intension or popularly known as database schema is used to define the description of the database and is specified during the design of the database and mostly remains unchanged.

Extension: Extension on the other hand is the measure of the number of tuples present in the database at any given point in time. The extension of a database is also referred to as the snapshot of the database and its value keeps changing as and when the tuples are created, updated, or destroyed in a database.

Q14. Describe the types of keys.

Primary key: The Primary key is an attribute in a table that can uniquely identify each record in a table. It is compulsory for every table.

Candidate key: The Candidate key is an attribute or set of an attribute that can uniquely identify a tuple. The Primary key can be selected from these attributes.

Super key: The Super key is a set of attributes that can uniquely identify a tuple. Super key is a superset of the candidate key.

Foreign key: The Foreign key is a primary key from one table, which has a relationship with another table. It acts as a cross-reference between tables.

Q15. What is System R?

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

Q16. 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 to the next higher level.

It makes you able to modify the schema definition in one level and should not affect the schema definition in the next higher level.

Q17. Mention types of Data Independence.

Physical Data Independence: Physical data is the data stored in the database. It is in the bit format. Modification at the physical level should not affect the logical level.

Logical Data Independence: Logical data in the data about the database. It basically defines the structure. Such as tables stored in the database. Modification at the logical level should not affect the view level.

Q18. What is Join?

The Join operation is one of the most useful activities in relational algebra. It is the most commonly used way to combine information from two or more relations.

Q19. Different types of Join.

Theta join

Natural join

Equi join

Left outer join

Right outer join

Full outer join

Q20. How do you communicate with an RDBMS?

You have to use Structured Query Language (SQL) to communicate with the RDBMS. Using queries of SQL, we can give the input to the database, and then after processing the queries database will provide us the required output.

Q21. What is 2-Tier architecture?

The 2-Tier architecture is the same as the basic client-server. In the two-tier architecture, applications on the client end can directly communicate with the database on the server side.

Q22. What is the 3-Tier architecture?

The 3-Tier architecture contains another layer between the client and server. The introduction of 3-tier architecture is for the ease of the users as it provides the GUI, which, makes the system secure and much more accessible. In this architecture, the application on the client-end interacts with an application on the server which further communicates with the database system.

Exception Handling Interview Questions

DBMS Interview Questions Set -1

DBMS Interview Questions Set -2

SQL Interview Question Set -1

SQL Interview Question Set -2

JPA Interview Questions Set -1

JPA Interview Question Set -2

Hibernate Interview Questions

Spring Boot Interview Questions Set 1

Spring Boot Interview Questions Set 2

GIT Interview Questions

Redis Interview Questions

Core Java Interview Questions Set -1

Docker interview question Set -1

Docker interview question Set -2

Kubernetes Interview Question Set -1

Kubernetes Interview Question Set -2

DBMS Interview Questions Set -1

Q1. What is DBMS used for?

DBMS, commonly known as Database Management System, is an application system whose main purpose revolves around the data. This is a system that allows its user to store the data, define it, retrieve it and update the information about the data inside the database.

Q2. What is a Database?

A Database is an organized, consistent, and logical collection of data that can easily be updated, accessed, and managed. Database mostly contains sets of tables or objects which consist of records and fields. A tuple or a row represents a single entry in a table.

Q3. Why is the use of DBMS recommended? Explain by listing some of its major advantages.

Controlled Redundancy: DBMS supports a mechanism to control the redundancy of data inside the database by integrating all the data into a single database and as data is stored in only one place, the duplicity of data does not happen.

Data Sharing: Sharing of data among multiple users simultaneously can also be done in DBMS as the same database will be shared among all the users and by different application programs.

Backup and Recovery Facility: DBMS minimizes the pain of creating the backup of data again and again by providing a feature of ‘backup and recovery which automatically creates the data backup and restores the data whenever required.

Enforcement of Integrity Constraints: Integrity Constraints are very important to be enforced on the data so that the refined data after putting some constraints are stored in the database and this is followed by DBMS.

Independence of data: It simply means that you can change the structure of the data without affecting the structure of any of the application programs.

Q4. What is the purpose of normalization in DBMS?

Normalization is the process of analyzing the relational schemas which are based on their respective functional dependencies and the primary keys in order to fulfill certain properties.

1) To minimize the redundancy of the data.

2) To minimize the Insert, Delete, and Update Anomalies.

Q5. What are the different types of languages that are available in the DBMS?

DDL: DDL is a Data Definition Language that is used to define the database and schema structure by using some set of SQL Queries like CREATE, ALTER, TRUNCATE, DROP and RENAME.

DCL: DCL is a Data Control Language that is used to control the access of the users inside the database by using some set of SQL Queries like GRANT and REVOKE.

DML: DML is a Data Manipulation Language that is used to do some manipulations in the database like Insertion, Deletion, etc. by using some set of SQL Queries like SELECT, INSERT, DELETE and UPDATE.

TCL: TCL Transaction Control Language consists of commands which deal with the transaction of the database.

Q6. What is meant by ACID properties in DBMS?

ACID stands for Atomicity, Consistency, Isolation, and Durability in a DBMS these are those properties that ensure a safe and secure way of sharing data among multiple users.

Atomicity: This property reflects the concept of either executing the whole query or executing nothing at all, which implies that if an update occurs in a database then that update should either be reflected in the whole database or should not be reflected at all.

Consistency: This property ensures that the data remains consistent before and after a transaction in a database.

Isolation: This property ensures that each transaction is occurring independently of the others. This implies that the state of an ongoing transaction doesn’t affect the state of another ongoing transaction.

Durability: This property ensures that the data is not lost in cases of a system failure or restart and is present in the same state as it was before the system failure or restart.

Q7. Are NULL values in a database the same as that of blank space or zero?

No, a NULL value is very different from that of zero and blank space as it represents a value that is assigned, unknown, unavailable, or not applicable as compared to blank space which represents a character and zero represents a number.

Q8. What is Data Warehousing?

The process of collecting, extracting, transforming, and loading data from multiple sources and storing them in one database is known as data warehousing. A data warehouse can be considered as a central repository where data flows from transactional systems and other relational databases and is used for data analytics. A data warehouse comprises a wide variety of an organization’s historical data that supports the decision-making process in an organization.

Q9. Explain different levels of data abstraction in a DBMS.

Physical Level:  it is the lowest level and is managed by DBMS. This level consists of data storage descriptions and the details of this level are typically hidden from system admins, developers, and users.

Conceptual or Logical level:  it is the level at which developers and system admins work and it determines what data is stored in the database and what is the relationship between the data points.

External or View level: it is the level that describes only part of the database and hides the details of the table schema and its physical storage from the users. The result of a query is an example of View level data abstraction.  A view is a virtual table created by selecting fields from one or more tables present in the database.

Q10. Explain Entity, Entity Type, and Entity Set in DBMS.

The Entity is an object, place, or thing which has its independent existence in the real world and about which data can be stored in a database. Entity: An entity is a real-world object having attributes, which are nothing but characteristics of that particular object. For Example, any person, book, etc.

Entity Type is a collection of entities that have the same attributes. For Example, the STUDENT table contains rows in which each row is an entity holding the attributes like name, age, and id of the students, hence STUDENT is an Entity Type that holds the entities having the same attributes.

Entity Set is a collection of entities of the same type. For Example, A collection of the employees of a firm.

Q11. What is a functional dependency in the DBMS?

This is basically a constraint that is useful in describing the relationship among the different attributes in a relation.

Q12. What is 1NF in the DBMS?

1NF is known as the First Normal Form. This is the easiest form of the normalization process which states that the domain of an attribute should have only atomic values. The objective of this is to remove the duplicate columns that are present in the table.

Q13. What is 2NF in the DBMS?

2NF is the Second Normal Form. Any table is said to have in the 2NF if it satisfies the following 2 conditions:

1). A table is in the 1NF.

2) Each non-prime attribute of a table is said to be functionally dependent in totality on the primary key.

Q14. What is 3NF in the DBMS?

3NF is the Third Normal Form.

Any table is said to have in the 3NF if it satisfies the following 2 conditions:

1). A table is in the 2NF.

2) Each non-prime attribute of a table is said to be non-transitively dependent on every key of the table.

Q15. What is BCNF in the DBMS?

BCNF is the Boyce Codd Normal Form which is stricter than the 3NF.

Any table is said to have in the BCNF if it satisfies the following 2 conditions:

1). A table is in the 3NF.

2). For each of the functional dependencies X->Y that exists, X is the super key of a table.

Q16. What is RDBMS?

RDBMS is the Relational Database Management System which contains data in the form of tables and data is accessed on the basis of the common fields among the tables.

Q17. What are the different types of relationships in the DBMS?

One-to-One: This basically states that there should be a one-to-one relationship between the tables i.e. there should be one record in both the tables. 

One-to-Many: This states that there can be many relationships for one i.e. a primary key table hold only one record which can have many, one, or no records in the related table. 

Many-to-Many: This states that both the tables can be related to many other tables.

Self-Referencing Relationship: This type of relationship is applied when a particular row in table X is associated with the same table.

Q18. What is concurrency control?

This is a process of managing simultaneous operations in a database so that database integrity is not compromised.

1). Optimistic approach – Involves versioning

2) Pessimistic approach – Involves locking

Q19. What is Correlated Subquery in DBMS?

Answer: A Subquery is also known as a nested query i.e. a query written inside some query. When a Subquery is executed for each of the rows of the outer query then it is termed as a Correlated Subquery.

Q20. What integrity rules exist in the DBMS?

Entity Integrity: This states a very important rule that the value of a Primary key can never have a NULL value.

Referential Integrity: This rule is related to the Foreign key which states that either the value of a Foreign key is a NULL value or it should be the primary key of any other relation.

Exception Handling Interview Questions

DBMS Interview Questions Set -1

DBMS Interview Questions Set -2

SQL Interview Question Set -1

SQL Interview Question Set -2

JPA Interview Questions Set -1

JPA Interview Question Set -2

Hibernate Interview Questions

Spring Boot Interview Questions Set 1

Spring Boot Interview Questions Set 2

GIT Interview Questions

Redis Interview Questions

Core Java Interview Questions Set -1

Docker interview question Set -1

Docker interview question Set -2

Kubernetes Interview Question Set -1

Kubernetes Interview Question Set -2

SQL Interview Question Set -2

Q1. What are the different types of SQL operators?

Operators are the special keywords or special characters reserved for performing particular operations. They are also used in SQL queries. We can primarily use these operators within the WHERE clause of SQL commands.

Arithmetic operators: These operators are used to perform mathematical operations on numerical data. The categories of this operators are addition (+), subtraction (-), multiplication (*), division (/), remainder/modulus (%), etc.

Logical operators: These operators evaluate the expressions and return their results in True or False. This operator includes ALL, AND, ANY, ISNULL, EXISTS, BETWEEN, IN, LIKE, NOT, OR, UNIQUE.

Comparison operators: These operators are used to perform comparisons of two values and check whether they are the same or not. It includes equal to (=), not equal to (!= or <>), less than (<), greater than (>), less than or equal to (<=), greater than or equal to (>=), not less than (!<), not greater than (!>), etc.

Bitwise operators: It is used to do bit manipulations between two expressions of integer type. It first performs the conversion of integers into binary bits and then applied operators such as AND (& symbol), OR (|, ^), NOT (~), etc.

Compound operators: These operators perform operations on a variable before setting the variable's result to the operation's result. It includes Add equals (+=), subtract equals (-=), multiply equals (*=), divide equals (/=), modulo equals (%=), etc.

String operators: These operators are primarily used to perform concatenation and pattern matching of strings. It includes + (String concatenation), += (String concatenation assignment), % (Wildcard), [] (Character(s) matches), [^] (Character(s) not to match), _ (Wildcard match one character), etc.

Q2. What is a view in SQL?

A view is a database object that has no values. It is a virtual table that contains a subset of data within a table. It looks like an actual table containing rows and columns, but it takes less space because it is not present physically. A view can have data from one or more tables. If any changes occur in the underlying table, the same changes are reflected in the views also.

Q3. Use of Views in SQL.

The primary use of a view is to implement the security mechanism. It is the searchable object where we can use a query to search the view as we use for the table. It only shows the data returned by the query that was declared when the view was created.

Q4. What is an Index in SQL?

An index is a disc structure associated with a table or view that speeds up row retrieval. It reduces the cost of the query because the query's high cost will lead to a fall in its performance. It is used to increase the performance and allow faster retrieval of records from the table. Indexing reduces the number of data pages we need to visit to find a particular data page.

Q5. What are the different types of indexes in SQL?

Unique Index

Clustered Index

Non-Clustered Index

Bit-Map Index

Normal Index

Composite Index

B-Tree Index

Function-Based Index

Q6. What is the unique index?

UNIQUE INDEX is used to enforce the uniqueness of values in single or multiple columns. We can create more than one unique index in a single table. A unique index can be applied automatically when a primary key is defined.

Q7. What is clustered index in SQL?

A clustered index is actually a table where the data for the rows are stored. It determines the order of the table data based on the key values that can sort in only one direction. It is the only index, which has been automatically created when the primary key is generated. 

Q8. What is the non-clustered index in SQL?

The indexes other than PRIMARY indexes (clustered indexes) are called non-clustered indexes. We know that clustered indexes are created automatically when primary keys are generated, and non-clustered indexes are created when multiple joins conditions and various filters are used in the query. The non-clustered index and table data are both stored in different places.

Q9. Is it possible to sort a column using a column alias?

Yes. We can use the alias method in the ORDER BY instead of the WHERE clause for sorting a column.

Q10. What is the difference between clustered and non-clustered indexes in SQL?

1). A clustered index is a table or view where the data for the rows are stored. In a relational database, if the table column contains a primary key, MySQL automatically creates a clustered index named PRIMARY. The indexes other than PRIMARY indexes (clustered indexes) are called non-clustered indexes. It has a structure separate from the data row. The non-clustered indexes are also known as secondary indexes.

2). Clustered indexes store the data information and the data itself. Non-clustered indexes stores only the information, and then it will refer you to the data stored in clustered data.

3). There can only be one clustered index per table. There can be one or more non-clustered indexes in a table.

4). A clustered index always contains an index id of 0. A non-clustered index always contains an index id>0.

Q11. Which are joins in SQL? 

SQL joins are used to retrieve data from multiple tables into a meaningful result set. It is performed whenever you need to fetch records from two or more tables. They are used with SELECT statement and join conditions.

Q12. What are the different types of joins in SQL?

INNER JOIN

SELF JOIN

LEFT OUTER JOIN

RIGHT OUTER JOIN

FULL OUTER JOIN

CROSS JOIN

Q13. What is INNER JOIN in SQL?

Inner join returns only those records from the tables that match the specified condition and hides other rows and columns. In simple words, it fetches rows when there is at least one match of rows between the tables found.

In the below image the region in red color is the output of the inner join

Q14. What is the Right JOIN in SQL?

The Right join is used to retrieve all rows from the right-hand table and only those rows from the other table that fulfilled the join condition. It returns all the rows from the right-hand side table even though there are no matches in the left-hand side table. If it finds unmatched records from the left side table, it returns a Null value. This join is also known as Right Outer Join.

In the below image the red color region is the output of the right join.

Q15. What is Left Join in SQL?

The Left Join is used to fetch all rows from the left-hand table and common records between the specified tables. It returns all the rows from the left-hand side table even though there are no matches on the right-hand side table. If it will not find any matching record from the right side table, then it returns null. This join can also be called a Left Outer Join.

In the below image the red color region is the output of the left join.

Q16. What is Full Join in SQL?

The Full Join results from a combination of both left and right join that contains all the records from both tables. It fetches rows when there are matching rows in any one of the tables. This means it returns all the rows from the left-hand side table and all the rows from the right-hand side tables. If a match is not found, it puts a NULL value. It is also known as FULL OUTER JOIN.

In the below image red color region is the output of the full join.

Q17. What is a "TRIGGER" in SQL?

A trigger is a set of SQL statements in a system catalog. It is a special type of stored procedure that is invoked automatically in response to an event. It allows us to execute a batch of code when an insert, update or delete command is run against a specific table because the trigger is the set of activated actions whenever DML commands are given to the system.

Q18. What is self-join and what is the requirement of self-join?

A SELF JOIN is used to join a table with itself. This join can be performed using table aliases, which allow us to avoid repeating the same table name in a single sentence. It will throw an error if we use the same table name more than once in a single query without using table aliases.

Q19. What are the set operators in SQL?

We use the set operators to merge data from one or more tables of the same kind. Although the set operators are like SQL joins, there is a significant distinction. SQL joins combine columns from separate tables, whereas SQL set operators combine rows from different queries.

1) UNION: It combines two or more results from multiple SELECT queries into a single result set. It has a default feature to remove the duplicate rows from the tables.

2) UNION ALL: This operator is similar to the Union operator, but it does not remove the duplicate rows from the output of the SELECT statements.

3) INTERSECT: This operator returns the common records from two or more SELECT statements. It always retrieves unique records and arranges them in ascending order by default. Here, the number of columns and data types should be the same.

4) MINUS: This operator returns the records from the first query, which is not found in the second query. It does not return duplicate values.

Q20. What is the difference between IN and BETWEEN operators?

1) BETWEEN Operator is used to select the range of data between two values. The values can be numbers, text, and dates as well. IN Operator to determine whether or not a specific value exists within a set of values. This operator reduces the use of multiple OR conditions with the query.

2) BETWEEN Operator returns records whose column value lies in between the defined range. IN Operator compares the specified column's value and returns the records when the match exists in the set of values.

Q21. What is a constraint?

The constraint is used to specify the rule and regulations that allows or restricts what values/data will be stored in the table. It ensures data accuracy and integrity inside the table. It enforces us to store valid data and prevents us from storing irrelevant data.

Q22. Constrainst levels.

1). Column Level Constraints: These constraints are only applied to a single column and limit the type of data that can be stored in that column.

2) Table Level Constraints: These constraints are applied to the entire table and limit the type of data that can be entered.

Q23. What is the ACID property in a database?

The ACID property is an acronym for Atomicity, Consistency, Isolation, and Durability. The ACID properties are meant for transaction that goes through different group of tasks. A transaction is a single logical order of data. It provides properties to maintain consistency before and after the transaction in a database. It also ensures that the data transactions are processed reliably in a database system.

Q24. What is atomicity?

It ensures that all statements or operations within the transaction unit must be executed successfully. If one part of the transaction fails, the entire transaction fails, and the database state is left unchanged. Its main features are COMMIT, ROLLBACK, and AUTO-COMMIT.

Q25. What is Consistency?

The database changes state only when a transaction will be committed successfully. It also protects data from crashes.

Q26. What is Isolation?

It guarantees that the concurrent property of execution in the transaction unit must be operated independently. It also ensures that statements are transparent to each other. The main goal of providing isolation is to control concurrency in a database.

Q27. What is Durability?

It guarantees that once a transaction has been committed, it persists permanently even if the system crashes, power loss, or failed.

Q28. What are functions and their usage in SQL?

SQL functions are simple code snippets that are frequently used and re-used in database systems for data processing and manipulation. Functions are the measured values. It always performs a specific task. 

A function should have a name, and the name cannot begin with a special character such as @, $, #, or other similar characters.

Functions can only work with the SELECT statements.

Every time a function is called, it compiles.

Functions must return value or result.

Functions are always used with input parameters.

Q29. Differenrt types of SQL functions.

1) User-Defined Function: Functions created by a user based on their needs are termed user-defined functions.

2) System Defined Function: Functions whose definition is defined by the system are termed system-defined functions. They are built-in database functions.

Q30. What are SQL comments?

Comments are explanations or annotations in SQL queries that are readable by programmers. It's used to make SQL statements easier to understand for humans. During the parsing of SQL code, it will be ignored. 

Single Line Comments: It starts with two consecutive hyphens (--).

Multi-line Comments: It starts with /* and ends with */.

Exception Handling Interview Questions

DBMS Interview Questions Set -1

DBMS Interview Questions Set -2

SQL Interview Question Set -1

SQL Interview Question Set -2

JPA Interview Questions Set -1

JPA Interview Question Set -2

Hibernate Interview Questions

Spring Boot Interview Questions Set 1

Spring Boot Interview Questions Set 2

GIT Interview Questions

Redis Interview Questions

Core Java Interview Questions Set -1

Docker interview question Set -1

Docker interview question Set -2

Kubernetes Interview Question Set -1

Kubernetes Interview Question Set -2

SQL Interview Questions Set -1

Q1. What is SQL?

SQL stands for the Structured Query Language. According to ANSI (American National Standards Institute), it is the standard language for relational database management systems. It is a database language used for database creation, deletion, fetching and modifying rows, etc. sometimes, it is pronounced as 'sequel.

Q2. What are the usages of SQL?

1) To retrieve data from a database

2) To inserts records in a database

3) To create views in a database

4) To updates records in a database

5) To delete records from a database

6) To create new databases

7) To create new tables in a database.

Q3. Does SQL support programming language features?

SQL refers to the Standard Query Language. Therefore, it is true that SQL is a language but does not actually support the programming language. It is a common language that doesn't have a loop, conditional statements, and logical operations. It cannot be used for anything other than data manipulation. It is a command language to perform database operations.

Q4. What are the components of SQL?

1) DDL: It stands for Data definition language. It defines the data structure that consists of commands like CREATE, ALTER, DROP, etc.

2) DML: It stands for Data manipulation language. It is used to manipulate existing data in the database. The commands in this category are SELECT, UPDATE, INSERT, etc.

3) DCL: It stands for Data control language. It controls access to the data stored in the database. The commands in this category include GRANT and REVOKE.

4) TCL: It stands for Transaction Control Language. It is used to deal with the transaction operations in the database. The commands in this category are COMMIT, ROLLBACK, SET TRANSACTION, SAVEPOINT, etc.

Q5. What is the purpose of DDL Language?

DDL stands for Data definition language. It is the subset of a database that defines the data structure of the database when the database is created. For example, we can use the DDL commands to add, remove, or modify tables. It consists of the commands: CREATE, ALTER and DELETE database objects such as schema, tables, indexes, view, sequence, etc.

Q6. What is the purpose of DML Language?

DML stands for Data manipulation language. It makes the user able to retrieve and manipulate data in a relational database. The DML commands can only perform read-only operations on data. It consists of the commands INSERT, SELECT, UPDATE and DELETE commands to perform the insertion, retrieval, updation and deletion of the records from the database.

Q7. What is the purpose of TCL Language?

TCL stands for Transaction Control Languages. These commands are used for maintaining consistency of the database and for the management of transactions made by the DML commands. A Transaction is a set of SQL statements that are executed on the data stored in DBMS

Q8. What is the purpose of DCL Language?

DCL stands for Data control language. It allows users to control access and permission management to the database. It includes two commands, GRANT and REVOKE.

GRANT: It enables system administrators to assign privileges and roles to the specific user accounts to perform specific tasks on the database.

REVOKE: It enables system administrators to revoke privileges and roles from the user accounts so that they cannot use the previously assigned permission on the database.

Q9. What are tables and fields in the database?

A table is a set of organized data in the form of rows and columns. It enables users to store and display records in the structure format. Columns can categorize as vertical, and Rows are horizontal.

Fields are the components to provide the structure for the table. It stores the same category of data in the same data type. It is also called a column in the table of the database.

Q10. What is a primary key?

A primary key is a field or the combination of fields that uniquely identify each record in the table. It is one of a special kind of unique key. If the column contains a primary key, it cannot be null or empty. It always stores unique values into a column.

Q11. What is a foreign key?

The foreign key is used to link one or more tables together. It is also known as the referencing key. A foreign key is specified as a key that is related to the primary key of another table. It means a foreign key field in one table refers to the primary key field of the other table.

Q12. What is a unique key?

A unique key is a single or combination of fields that ensure all values stores in the column will be unique. It means a column cannot stores duplicate values. This key provides uniqueness for the column or set of columns.

Q13. What is the difference between a primary key and a unique key?

1) We cannot store NULL values in the primary key column. We can store NULL value in the unique key column, but only one NULL is allowed.

2) We cannot change or delete the primary key column values. We can modify the unique key column values.

Q14. What is a Database?

A database is an organized collection of data that is structured into tables, rows, columns, and indexes. It helps the user to find the relevant information frequently. It is an electronic system that makes data access, data manipulation, data retrieval, data storing, and data management very easy.

Q15. Features of database.

Manages large amounts of data

Accurate

Easy to update

Security

Data integrity

Easy to research data

Q16. What is DBMS?

DBMS stands for Database Management System. It is a software program that primarily functions as an interface between the database and the end-user. It provides us the power such as managing the data, the database engine, and the database schema to facilitate the organization and manipulation of data using a simple query in almost no time.

Q17. Components of DBMS.

Software

Data

Procedures

Database Languages

Query Processor

Database Manager

Database Engine

Reporting

Q18. What are the different types of database management systems

Hierarchical databases (DBMS)

Network databases (IDMS)

Relational databases (RDBMS

Object-oriented databases

Document databases (Document DB)

Graph databases

ER model databases

NoSQL databases

Q19. What is RDBMS?

RDBMS stands for Relational Database Management System. It is a database management system based on a relational model. It facilitates you to manipulate the data stored in the tables by using relational operators. RDBMS stores the data into the collection of tables and links those tables using the relational operators easily whenever required.

Q20. What is Normalization in a Database?

Normalization is used to minimize redundancy and dependency by organizing fields and table of a database.

Q21. List the normalization form.

First normal form(1NF)

Second normal form(2NF)

Third normal form(3NF)

Boyce-Codd normal form(BCNF)

Q21. What is the use of Normalization?

Normalization is mainly used to add, delete or modify a field that can be made in a single table. The primary use of Normalization is to remove redundancy and remove the insert, delete and update anomalies. Normalization breaks the table into small partitions and then links them using different relationships to avoid the chances of redundancy.

Q22 What is 1NF form?

First normal form (1NF) is a property of a relation in a relational database. A relation is in first normal form if and only if no attribute domain has relations as elements. It ensuring that there are no repeating groups of data

Q23. What is (2NF) from?

A relation that is in First Normal Form and every non-primary-key attribute is fully functionally dependent on the primary key, then the relation is in Second Normal Form (2NF). A relation must be in first normal form and relation must not contain any partial dependency.  A relation with a single-attribute primary key is automatically in at least 2NF. A relation that is not in 2NF may suffer from the update anomalies.

Q24. What is (3NF) form? 

A relation is in third normal form (3nf), if there is no transitive dependency for non-prime attributes as well as it is in second normal form.

A relation is in 3NF if at least one of the following condition holds in every non-trivial function dependency X –> Y

X is a super key.

Y is a prime attribute (each element of Y is part of some candidate key).

A relation that is in First and Second Normal Form and in which no non-primary-key attribute is transitively dependent on the primary key, then it is in Third Normal Form (3NF).

Q25. Boyce-Codd Normal Form (BCNF).

Boyce-Codd Normal Form or BCNF is an extension to the third normal form. A relation is in BCNF iff, X is a superkey for every functional dependency (FD) X?Y in a given relation. A relation is in BCNF, if and only if, every determinant is a Form (BCNF) candidate key.

Exception Handling Interview Questions

DBMS Interview Questions Set -1

DBMS Interview Questions Set -2

SQL Interview Question Set -1

SQL Interview Question Set -2

JPA Interview Questions Set -1

JPA Interview Question Set -2

Hibernate Interview Questions

Spring Boot Interview Questions Set 1

Spring Boot Interview Questions Set 2

GIT Interview Questions

Redis Interview Questions

Core Java Interview Questions Set -1

Docker interview question Set -1

Docker interview question Set -2

Kubernetes Interview Question Set -1

Kubernetes Interview Question Set -2

Basic Redis Command Part -8

Redis is an open-source, BSD-licensed advanced key-value store. It is often referred to as a data structure server since the keys can contain strings, hashes, lists, sets, and sorted sets. Redis is written in C.

Redis is a NoSQL database that follows the principle of a key-value store.

Some commands of Redis.

ZLEXCOUNT: This command counts the number of members in a sorted set between a given lexicographical range.

ZRANGEBYLEX: Returns a range of members in a sorted set, by lexicographical range.

ZREMRANGEBYLEX: Removes all members in a sorted set between the given lexicographical range.

ZREMRANGEBYRANK: This command removes all members in a sorted set within the given indexes.

ZREVRANGEBYSCORE: Returns a range of members in a sorted set, by score, with scores ordered from high to low.

ZREVRANK: Determines the index of a member in a sorted set, with scores ordered from high to low.

ZUNIONSTORE: Adds multiple sorted sets and stores the resulting sorted set in a new key.

PFADD: This command adds the specified element to the specified HyperLogLog.

PFCOUNT: Returns the approximated cardinality of the set(s) observed by the HyperLogLog at key(s).

PFMERGE: Merges N different HyperLogLogs into a single one.

Basic Redis Command part -1

Basic Redis Command Part -2

Basic Redis Command part -3

Basic Redis Command Part -4

Basic Redis Command Part -5

Basic Redis Command Part -6

Basic Redis Command Part -7