Introduction
Database Management Systems is the most important subject of Computer Science. In this blog, we are covering the most asked DBMS Interview Questions. So here, we will be converting the most important questions related to this subject so that you can ace your interview after going through this blog.
DBMS Interview Questions
Ques 1. What are the different levels of abstraction in DBMS?
Ans 1. Abstraction is the process of hiding irrelevant information from the user. There are three levels of abstraction in DBMS:
Physical Level: Physical level is the lowest level of data abstraction and it describes the storage pattern of data in the database.
Logical Level: It determines the data stored and the relationships between data in a database. A Developer works at this level.
View Level: The View level hides the details of the table schema and its physical storage from the users, and it views only part of the database.
This is the most asked DBMS Interview Question.
Ques 2. What is RDBMS?
Ans 2. RDBMS stands for Relational Database Management System. It stores data in the form of a collection of tables and can define relations between the common fields of these tables. Most modern database management systems like MySQL, Oracle, and IBM are based on RDBMS.
Ques 3. What is SQL?
Ans 3. SQL stands for Structured Query Language. It is the standard language for relational database management systems.
Ques 4. Explain the different Constraints in SQL?
Ans 4. The different constraints are:
NOT NULL: It is used so that a user can’t enter a NULL value in the column.
CHECK: It is used to verify that all values in a column must follow a specific condition.
DEFAULT: It is used to assign a default value to a column in case no value is specified.
UNIQUE: It ensures that only unique values get inserted into a column.
INDEX: It helps in the faster retrieval of records.
PRIMARY KEY: It helps in identifying each record uniquely in a table.
FOREIGN KEY: It helps in ensuring referential integrity.
This is the most popular DBMS Interview Question.
Ques 5. Explain the SELECT statement in SQL?
Ans 5. This statement is used to select data from a database. For example:
SELECT * FROM codingNinjas;
This query will select all the records from the codingNinjas table and will return a result table.
Ques 6. What is an alias in SQL?
Ans 6. A temporary name assigned to the table or table column for the purpose of a particular SQL query is called alias in SQL AS keyword is used to represent an alias explicitly.For example: Suppose we are having a codingNinjas table in the database then:
SELECT student AS ninja FROM codingNinjas;
Ques 7. What is a View?
Ans 7. A virtual table based on the result-set of an SQL statement is known as view in SQL. It consists of rows and columns, just like a real table. The fields in a view are fields from one or more real tables in the database.
Ques 8. What are Aggregate functions?
Ans 8. The Function that performs operations on a collection of values to return a single scalar value is called an aggregate function. They are often used with the GROUP BY and HAVING clauses of the SELECT statement. These are the widely used SQL aggregate functions:
- AVG() – It returns the mean of a collection of values.
- COUNT() – This function counts the total number of records in a table.
- MIN() – This aggregate function is used to find minimum value from a collection of values.
- MAX() – This aggregate function is used to find maximum value from a collection of values.
- SUM() – It calculates the sum of a collection of values.
- FIRST() – It is used to fetch the first element in a collection of values.
- LAST() – It is used to fetch the last element in a collection of values.
Ques 9. What is the difference between SQL and MySQL?
Ans 9. The differences are as follows:
| SQL | MySQL |
| SQL stands for Structured Query Language and it is based on the English language. | This is a database management system |
| SQL is used for accessing and managing databases. | MySQL is an RDMS (Relational Database Management System). |
Ques 10. Explain table and field in SQL.
Ans 10. The collection of data in an organized manner in the form of rows and columns is known as a table. The column of a table is known as a field.
Ques 11. What is meant by data integrity in a database?
Ans 11. Data Integrity is used to define the accuracy as well as the consistency of the data stored in a database. It also defines integrity constraints to enforce rules on the data when it is entered into a database.
Ques 12. What are the different types of operators available in SQL?
Ans12. There are three types of operators available in SQL:-
- Arithmetic Operators
- Logical Operators
- Comparison Operators
Ques 13. Write a SQL query to get the names of all the students starting with ‘C’?
Ans 13. SELECT * FROM Table_Name WHERE Student likes ‘C%’.
Ques 14. Why do we use the MERGE statement in SQL?
Ans 14. MERGE statement is used to make a conditional update or for the insertion of data into a table. It performs an UPDATE if a row exists or an INSERT if the row does not exist.
Ques 15. Mention the various levels of constraints.
Ans 15. There are two levels:
- Column level Constraints
- Table level Constraints.
Ques 16. Write a SQL Query to fetch even-numbered alternate records from a table.
Ans 16. To display even numbered records:
SELECT student FROM (SELECT rowNo, studentId FROM Coding) WHERE mod(rowNo, 2) = 0;
Ques 17. Write a SQL Query to fetch odd-numbered alternate records from a table.
Ans 17. To display odd numbered records:
SELECT student FROM (SELECT rowNo, studentId FROM Coding) WHERE mod(rowNo, 2) = 1;
Ques 18. Write a SQL Query to fetch unique records from a table.
Ans 18. DISTINCT keyword is used to fetch unique records.
Select DISTINCT ninja from CodingNinja;
Ques 19. What is a View in SQL?
Ans 19. A Virtual Table consisting of a subset of data of a table is known as View. Since views are not present, it takes less space to store. We can have data of one or more tables combined in a view.
Ques 20. Which function is used to get the current date in SQL.
Ans 20. Current date can be displayed using the built-in function GetDate().
Ques 21. Write an SQL Query to fetch ninjaId and FullName of all the ninjas working under professor with id-’100’.
Ans 21.
SELECT ninjaId, FullName FROM CodingNinja WHERE ProfessorId = 100;
Ques 22. Explain the unique constraint.
Ans 22. This constraint ensures that a column must only contain unique values. For example, if we assign a unique constraint to the ninja Name column in the table, then it’s mandatory that every entry in this column should have a unique value.
Ques 23 Explain the difference between BETWEEN and IN operators in SQL.
Ans 23. BETWEEN operator is used to representing rows based on a set of values. It returns the total number of values that exist within the ranges.
The IN condition operator is used to search for values within a given range of values.
Ques 24. Mention the difference between HAVING and WHERE clauses in SQL.
Ans 24. WHERE clause is used while fetching data from a row in a table and the HAVING clause is used with aggregated data. We cannot use WHERE with aggregated data.
Ques 25. What is the difference between DELETE and TRUNCATE commands in SQL?
Ans 25. The DELETE command is used to delete a specific row from a table, whereas the TRUNCATE command is used to delete all the rows. Delete command can be rolled back, but the TRUNCATE command, once executed, can’t be rolled back.
Ques 26. What are the different types of Transaction Controls?
Ans26. There are four types of Transaction Controls:-
- COMMIT: Saves all changes made through the transaction.
- ROLLBACK: It reverts all the changes made by the transaction.
- SET TRANSACTION: It is used to set the name of the transaction.
- SAVEPOINT: Sets the point where the transaction is to be rolled back.
Ques 27. Mention the Scalar functions available in SQL.
Ans27. Scalar functions in SQL include:
| UCASE() | Converts the field in the upper case. |
| LCASE() | Converts the field in lower case. |
| MID() | Extracts and returns character from the text field |
| FORMAT() | Specifies the display format. |
| LEN() | Specifies the length of the text field. |
| ROUND() | Rounds up the decimal field value to a number. |
Ques 28. What are Triggers in SQL?
Ans 28. Stored procedures which are used to create a response to a specific action performed on the table such as INSERT or DELETE are known as Triggers in SQL.
Ques 29 Explain SQL Injection.
Ans 29. It is a database attack technique where malicious SQL statements are inserted into the database. Once such SQL statement is executed on the database then the database becomes exposed to an attacker for the attack.
Ques 30. What is OLTP?
Ans 30. OLTP stands for Online Transaction Processing. It is a class of software applications that is capable of supporting transaction-oriented programs.
Key Takeaways
In this article, we discussed the most asked DBMS Interview questions for freshers and experienced developers. The article covered the most heated topics in DBMS.
By: Deeksha Sharma
