Here is the list of frequently asked DBMS interview questions for school as well as university students.

These questions have been chosen in accordance with their importance hence it will benefit school students for their via exams as well.

1. What is Normalization?
Normalization is a process of structuring relational database in order to reduce data redundancy and improve data integrity.

E.g Dividing larger tables with repeated data into smaller tables and linking them using relationships.

2. Write a SQL query to create a table to store roll_no(number) and name(varchar) of students in a class.

3. What is a Foreign Key?
Foreign Key is a set of one or more columns in a table that refers to the primary key of another table. The whole purpose of the primary key is to uniquely identify a row in the reference table.

4. What is DDL and DML?
DDL stands for Data Definition language.

These are the SQL which basically deals with the database schema(structure) i.e how the data reside in the database.

Examples:

  • CREATE
  • ALTER
  • DROP
  • TRUNCATE
  • RENAME

DML stands for Data Manipulation language.

It mainly deals with the manipulation of the data residing in the database.

Examples:

  • SELECT
  • INSERT
  • UPDATE
  • DELETE
  • MERGE

5. What aspects make a transaction consistent?
A transaction is a small unit of a program/task which itself perform different operations.

Example: T1 – Read(A), Write(A)

For a transaction to remain consistent and to maintain data integrity, it needs to maintain ACID properties which are:

ATOMICITY: Either all operations of a transaction is executed or none.
CONSISTENCY: The database must remain consistent after the completion of the transaction.
DURABILTY: Any changes done by a transaction should be held permanently, even on system failure.
ISOLATION: On parallel transactions execution, one transaction should not get effected by another running transaction.

6. What is the difference between primary key and candidate key?
A candidate key is a column or a set of columns which uniquely identifies any database record(Means each key value will give single unique tuple/records). Each table may have more than one candidate key.

Whereas a primary key is a candidate key that is selected based on the simplicity and efficiency of the key. There can be only one primary key for a single table.

7. Explain different types of JOINS in SQL.
JOINS in SQL server is used to retrieve data from 2 or more related tables. In general, tables are related to each other using foreign key constraint.

Different types of joins in SQL are:

  • INNER JOIN
  • OUTER JOIN
  • CROSS JOIN

OUTER JOIN is again divided into 2 sub categories:

  • LEFT JOIN or LEFT OUTER JOIN
  • RIGHT JOIN or RIGHT OUTER JOIN
  • FULL JOIN or FULL OUTER JOIN

Tables in DBMS

INNER JOIN: returns only the matching rows between the table and non-matching rows are eliminated.

Inner Join

LEFT JOIN: returns all the matching rows + non matching rows from the left table.

Left Join

RIGHT JOIN: returns all the matching + non matching rows from the right table.

Right join

FULL JOIN: returns all rows from both the left and right tables, including the non-matching row.

Full join

CROSS JOIN: produces the Cartesian product of the two tables involved in the join.

8. What is the trigger?
A trigger is a procedural code that is automatically executed in response to the certain events on a particular table or view in a database.

E.g A trigger can be invoked if some row is inserted into the database or some column is updated.

9. What are constraints in DBMS?
Constraints enforce a limit to the data or the type of data that can be inserted/deleted/updated from a table. The whole purpose of the constraints is to maintain data integrity during an update/delete/insert into a table.

Some examples of constraints are:

  • NOT NULL
  • UNIQUE
  • DEFAULT
  • PRIMARY KEY

10. What do you mean by Data-warehouse?
A Data-warehouse is a large collection of business data used to help an organization make decisions. A large amount of data in the data warehouse comes from different places such as internal applications, marketing sales, management, etc.

11. Explain SQL Alias with an Example.
An alias is a shorthand for a table or column name which only exists for the duration of the query. Alias reduces the amount of typing in queries and is generally useful with JOINS and aggregates.

Example:

Here deptId is an Alias.

12. What is the use of DISTINCT Command?
There are multiple duplicate records in a particular column of a table. To fetch only the unique records(a single record not more than one time), the DISTINCT keyword is used in SQL.

SYNTAX:

13. What are aggregate and scalar functions?
Aggregate Functions are the functions which perform the calculation on a group of values and return a single value.

Examples: AVG(), SUM(), COUNT() etc.

Scalar functions are the functions that perform some action on value and returns a single value.

Examples: UCASE(), LCASE() etc.

14. What is the difference between TRUNCATE and DELETE?
TRUNCATE is DDL(Data Definition Language) command which removes all rows from the table, without logging the individual row deletion.

DELETE is a DML(Data Manipulation Language) command which is generally used to delete a particular row of a table.

DELETE can also be used to delete all rows at once(similar to TRUNCATE) using the following syntax:

15. Can we delete an entry from reference table, if it is being referenced by some foreign key?
Yes, For this we need to change the foreign key constraint to Delete Cascade. By doing this, on the attempt to delete an entry from the reference table, the matching rows of both the tables get deleted.

16. What do you mean by Schema?
Schema is the logical structure of the database which defines how the data is organized and how the relation among them is associated.

In Short, Schema helps the programmer to understand the database and make it useful.

17. What is the weak entity?
The weak entity is an entity whose existence depends upon another entity.

Example: ROOM can only exist if there is a BUILDING.

Weak Entity Relation

Double rectangle represent a weak entity in the ER diagram and its corresponding relation is depicted by double polygon.

18. What is composite attribute?
A composite attribute is an attribute with more than one sub-attributes.

Example: Full Name = First Name + Last Name

Composite attribute

19. Explain 3 tier Structure.
In a 3 Tier Structure, the whole DBMS can be interpreted as 3 levels of architecture.

3 tier achitecture DBMS

  1. External also known as view level. This level gives users access to data with the help of underlying conceptual level.
  2. Conceptual level also known as Logical level. The logical structure(schema) and relationships is defined in this level.
  3. Internal level also known as physical level. This level describes how the data is actually stored in the storage devices.

20. What is stored procedure?
A stored procedure is a prepared SQL code that you can save so that the code can be used over and over again.

21. What is a composite key?
A composite key is a key which comprises of more than one attribute.

Example: For a ‘student’ table, roll number and student name as the combination are used as the primary key.

PK: {roll_no, name}

21. What is UNIQUE Key or Constraint?
Unique Constraint ensure unique value for each row when applied to a particular column. A coulumn which has UNIQUE constraint is allowed to have maximum of 1 NULL.

To add unique constraint in a table:

21. What is the difference between UNIQUE and PRIMARY key?

  • UNIQUE column can have a maximum of 1 NULL but the PRIMARY key can never be NULL.
  • THERE can be multiple UNIQUE KEY in one table but there can be only one PRIMARY KEY in a table.

A primary key must be unique.

A unique key does not have to be the primary key.

Leave a Reply

Close Menu