If you’re preparing for a job interview in the field of Database Management Systems (DBMS), you know that you’ll need to have a strong understanding of technology and be able to articulate your knowledge and experience effectively. With so many potential questions, it can be difficult to know where to focus your preparation. This article will provide an overview of some common DBMS interview questions, as well as tips and tricks for success.
Let us look at the interview questions for beginners.
Interview Questions for Beginners
1. What is a DBMS, and how useful is it? Use examples to explain RDBMS.
A database management system, or DBMS for short, is a collection of tools or programs that allow users to create and manage databases. A database management system (DBMS) provides a tool or interface for performing various actions on a database, such as inserting, removing, updating, and so on. The software allows data to be stored more securely and compactly than a file-based system. A DBMS system makes using a database more accessible and more structured by assisting a user in overcoming issues such as data redundancy and inconsistency.
RDBMS stands for Relational Database Management System and was introduced in the 1970s to access and store data more efficiently than DBMS. RDBMS stores data in the form of tables as compared to DBMS which stores data as files. Storing data as rows and columns makes it easier to locate specific values in the database and makes it more efficient as compared to DBMS.
Examples of popular RDBMS systems are MySQL, Oracle DB, etc.
2. What is the definition of a database?
A database is a well-organized, consistent, and logical collection of data. It is simple to modify, access, and manage. A database primarily comprises groups of tables or objects consisting of records and fields. An object is anything that is created with the “create” command. A tuple or a row is a single entry in a table. The primary data storage units are attributes or columns containing information about a specific table section. DBMS retrieves data from a database in response to user queries.
3. What are the drawbacks of file-based systems that make DBMS a better choice?
Because there is no indexing in a traditional file-based system, the only way to find content is to scan the entire page. This takes a long time and is very slow. The other issue is that files contain many duplicate and redundant data, and changing one of them changes them all. Because the files in traditional file-based systems need to be more organized, it is more difficult to access data.
Another issue is that there is no concurrency control, meaning a single operation can lock up the entire page. DBMS, on the other hand, allows multiple functions to run on the same file at the same time.
Traditional file-based systems also have issues with integrity checks, data isolation, atomicity, security, and other matters that DBMSs can solve.
4. Describe a few advantages of a database management system.
Here are a few reasons to use a database management system.
Users can share information from the same database at the same time. This type of sharing also allows end users to react quickly to changes in the database environment.
Integrity constraints: These enable data to be stored well-organized and accurately.
Controlling database redundancy: Consolidating all data into a single database eliminates redundancy.
Data Independence: This allows the data structure to be changed without affecting any of the running application programs.
Provides backup and recovery: It can be configured to automatically back up the data and restore it to the database as needed.
Data Security: DBMS provides the tools to make data storage and transmission easier and safer. Some of the most common methods for protecting data in a DBMS are authentication (granting a user restricted access) and encryption (encrypting sensitive data like OTP, credit card information, etc.).
5. Describe the various languages available in DBMS.
Some of the languages that can be used in DBMS are as follows:
DDL is an abbreviation for “Data Definition Language.” It is a collection of commands used to define a database.
Examples include CREATE, ALTER, DROP, TRUNCATE, RENAME, and so on.
DML is an abbreviation for “Data Manipulation Language.” It is a set of commands used to modify data in a database.
SELECT, UPDATE, INSERT, DELETE, and other commands.
DCL, “Data Control Language,” is a set of commands used to manage a database system’s permissions and controls.
For example, GRANT and REVOKE.
TCL is an abbreviation for “Transaction Control Language.” It is a set of commands used to handle database transactions.
Some examples include COMMIT, ROLLBACK, and SAVEPOINT.
6.What does “ACID properties” in DBMS mean?
ACID is an acronym that stands for Atomicity, Consistency, Isolation, and Durability. These are the components of a database management system (DBMS) that allow multiple users to safely and efficiently share data.
You Must Like: Discover the Most Common Software Testing Interview Questions and Answers
Atomicity: This property is based on the idea that the entire query or nothing is executed. This means that if you change a database, it should be reflected in the whole database.
Consistency ensures that the data in a database is always the same before and after a transaction.
Isolation: This property ensures that each transaction occurs independently of the others. This means that the state of one trade does not affect the form of another marketing that is still in progress.
Durability: This property ensures that data is not lost if the system crashes or restarts and remains in the same state as before the system crashed or restarted.
7. Are NULL values in a database the same as a blank space or zero?
No, a NULL value is not synonymous with zero or a blank space. A NULL value denotes a value that has been assigned, is unknown, is unavailable, or is irrelevant. A space represents a character, while zero represents a number.
An “a number courses” value of “NULL” indicates that the value is unknown, whereas a value of “0” means that the student has not taken any courses.
Intermediate DBMS Interview Questions
8.What exactly is a data warehouse?
Data warehousing is gathering data from various sources, extracting, transforming, and storing it in a single database. A data warehouse is a central storage area into which data flows from transactional systems and other relational databases and is used for data analytics. A data warehouse is an extensive collection of an organization’s historical data that aids decision-making.
9. Describe the various levels of data abstraction in a database management system.
Data abstraction is the process of concealing unnecessary information from users. Data abstraction is divided into three levels:
It is the most basic level and is handled by DBMS. Most system administrators, developers, and users need help seeing the details of this level, which contains descriptions of how data is stored.
Logical or conceptual level: Developers and system administrators work at this level. It determines what data is stored in the database and how the various data points are related.
External or View level: This level only describes a portion of the database and prevents users from seeing the table schema and how it is physically stored. A query result is an example of View level data abstraction. A view is a virtual table created by selecting fields from one or more database tables.
9.What exactly is the “entity-relationship” (E-R) model? Explain what “Entity,” “Entity Type,” and “Entity Set” mean to DBMS.
An entity-relationship model is a diagram-based approach to database design. Real-world objects are represented as “entities,” and the links between them are represented as lines.
Entity: A real-world object with attributes that describe what it is like. A student, employee, or teacher are all examples of commodities.
Entity Type: An entity type is a collection of entities with the same attributes. A database entity type is made up of one or more related tables. Entity type or attributes can be something that distinguishes the entity. A student, for example, is an entity with attributes such as student id, student name, and so on.
Entity Set: An entity set is a collection of all the entities in a database that are the same type. An entity set is a collection of all the students, employees, teachers, etc.
Interview Questions for Experienced
10. Describe the various types of normalization forms available in a DBMS.
The following are the primary normalization forms in a DBMS:
The first standard form, or 1NF, is the most fundamental way to normalize a database. The following conditions must be met for a table to be in its first standard form:
Each column must be atomic and have only one value.
If duplicate columns exist in the same table, they should be removed.
Each linked data set should have a table, with each row having a column.
Switch from Table-1 to Form 1NF.
The 2NF abbreviation stands for the second standard form. A table must meet the following requirements to be in its second common form:
To be in its 1NF, the table must meet all needs.
Every non-key attribute of the table must be dependent on the primary key in such a way that if a critical element is deleted, the non-key part remains in the database.
Table-1 will be divided into two tables and transferred to Table 2NF.
The 3NF abbreviation stands for the third standard form. If a table is in its third normal form, it must satisfy the following requirements:
To be in its 2NF state, the table must meet all 2NF needs.
No attribute in the same table is dependent on the function of any other feature in a way that is mutually exclusive.
Boyce-Codd Normal Form, or BCNF, is a more complicated version of 3NF. For the same reason, it is also abbreviated as 3.5NF. To be in its BCNF standard form, a table must follow the following rules:
To be in its 3NF state, the table must meet all 3NF requirements.
A should be the table’s super key for any functional relationship (A->B) between any attribute A and any other attribute B. It simply means that if B is a prime attribute, A cannot be non-prime.
11..Discuss the various types of database keys.
A database contains seven different types of keys:
A candidate key is a set of characteristics that can distinguish one table from another. A table may have multiple keys. The primary key can be any of the candidate keys. Because student and firstName can be used to find each tuple in the following example, they can be considered a candidate key.
The super key is a set of characteristics that can be used to identify a tuple uniquely. In other words, the super key results from combining the candidate and primary keys.
The primary key is a collection of properties that uniquely identify each tuple. In the following example, StudentId and firstName are possible keys, and either could be used as the primary key. The primary key of the student table in the example is studentId.
The unique and primary keys are very similar; however, unique keys allow NULL values in the column, while primary keys do not. Unique keys are primary keys with the value “NULL.”
All candidate keys that aren’t chosen as primary keys are considered alternate. The alternative keys in the database in the example below are the first and last names.
A foreign key is an attribute that can only accept values from the typical characteristics of another table. Because courseID is a property of both tables, it is used as a foreign key in the course table in the example below.
A composite key is a combination of two or more columns that can uniquely identify each tuple in a table. In the example below, you can find each tuple by combining the studentId and first name.
12.Distinguish between a 2-tier and a 3-tier DBMS architecture.
The term “two-tier architecture” refers to a client-server architecture in which applications on clients communicate directly with databases on servers without using any middleware.
Two examples are the Contact Management System, which uses MS Access, and the Train Reservation System.
13.How can you perform pattern matching in SQL?
You can perform pattern matching in SQL by using the LIKE operator. With the LIKE operator, you can use the following symbols:
- %(Percentage sign) – To match zero or more characters.
- _ (Underscore) –To match exactly one character.
Example:
SELECT * FROM Customers WHERE CustomerName LIKE ‘s%’
SELECT * FROM Customers WHERE CustomerName like ‘xyz_’
14. Mention few 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 the rest of the letters in lowercase.
Syntax: INITCAP(‘string’)
15. What are joins in SQL and what are the different types of joins?
A JOIN clause is used to combine rows from two or more tables, based on a related column between them. It is used to merge two tables or retrieve data from there. There are 4 joins in SQL namely:
- Inner Join
- Right Join
- Left Join
- Full Join
16.What do you understand by the view and mention the steps to create, update and drop a view?
A view in SQL is a single table, which is derived from other tables. So, a view contains rows and columns similar to a real table and has fields from one or more table.
To create a view, use the following syntax:
| 1234 | CREATE VIEW ViewName ASSELECT Column1, Column2, …, ColumnNFROM TableNameWHERE Condition; |
To update a view, use the following syntax:
| 1234 | CREATE VIEW OR REPLACE ViewName ASSELECT Column1, Column2, …, ColumnNFROM TableNameWHERE Condition; |
To drop a view, use the following syntax:
| 1 | DROP VIEW ViewName; |
Next, in this article on DBMS interview questions, let us discuss the most frequently asked queries about SQL.
SQL Query Based Interview Questions
17.Write a query to create a duplicate table with and without data present?
Consider you have a table named Customers, having details such as CustomerID, CustomerName and so on. Now, if you want to create a duplicate table named ‘DuplicateCustomer’ with the data present in it, you can mention the following query:
| 1 | CREATE TABLE DuplicateCustomer AS SELECT * FROM Customers; |
Similarly, if you want to create a duplicate table without the data present, mention the following query:
| 1 | CREATE TABLE DuplicateCustomer AS SELECT * FROM Customers WHERE 1=2; |
18.Mention a query to calculate the even and odd records from a table.
To write a query to calculate the even and odd records from a table, you can write two different queries by using the MOD function.
So, if you want to retrieve the even records from a table, you can write a query as follows:
| 1 | SELECT CustomerID FROM (SELECT rowno, CustomerID from Customers) where mod(rowno,2)=0; |
Similarly, if you want to retrieve the odd records from a table, you can write a query as follows:
| 1 | SELECT CustomerID FROM (SELECT rowno, CustomerID from Customers) where mod(rowno,2)=1; |
19. Write a query to remove duplicate rows from a table?
To remove duplicate rows from a table, you have to initially select the duplicate rows from the table without using the DISTINCT keyword. So, to select the duplicate rows from the table, you can write a query as follows:
| 1 | SELECT CustomerNumber FROM Customers WHERE ROWID (SELECT MAX (rowid) FROM Customers C WHERE CustomerNumber = C.CustomerNumber); |
Now, to delete the duplicate records from the Customers table, mention the following query:
| 1 | DELETE FROM Customers WHERE ROWID(SELECT MAX (rowid) FROM Customers C WHERE CustomerNumber = C.CustomerNumber); |
20. Mention a query to add email validation to your database.
Well, there are multiple ways to add email validation to your database, but one out the lot is as follows:ni
| 1 | SELECT Email FROM Customers WHERE NOT REGEXP_LIKE(Email, ‘[A-Z0-9._%+-]+@[A-Z0-9.-]+.[A-Z]{2,4}’, ‘i’); |
21. Write a query to retrieve the last day of next month in Oracle.
To write a query to retrieve the last day of the next month in Oracle, you can write a query as follows:
| 1 | SELECT LAST_DAY (ADD_MONTHS (SYSDATE,1)) from dual; |
