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 a relational database to reduce data redundancy and improve data integrity.
Example: 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.
CREATE TABLE Students (roll_no NOT NULL number, name varchar(20));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 types of SQL that basically deal with the database schema or structure (i.e. how the data resides in the database).
Some common examples of DDL statements are:
- 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 of DML statements are:
- 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 maintain data integrity, it needs to comply with the ACID properties:
ATOMICITY: Either all operations of a transaction are executed or none.
CONSISTENCY: The database must remain consistent after the completion of the transaction.
DURABILITY: Any changes done by a transaction should be held permanently, even on system failure.
ISOLATION: On parallel transactions execution, one transaction should not get affected 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 that uniquely identifies any database record (means each key-value will give a single unique tuple or 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
INNER JOIN: returns only the matching rows between the table and non-matching rows are eliminated.
SELECT name, salary, dept_name FROM tblEmp INNER JOIN tblDept ON tblEmp.dept_id = tbl.Dept.id
LEFT JOIN: returns all the matching rows + non matching rows from the left table.
SELECT name, salary, dept_name FROM tblEmp LEFT JOIN tblDept ON tblEmp.dept_id = tbl.Dept.id
RIGHT JOIN: returns all the matching + non matching rows from the right table.
SELECT name, salary, dept_name FROM tblEmp RIGHT JOIN tblDept ON tblEmp.dept_id = tbl.Dept.id
FULL JOIN: returns all rows from both the left and right tables, including the non-matching row.
SELECT name, salary, dept_name FROM tblEmp
FULL JOIN tblDept
ON tblEmp.dept_id = tbl.Dept.id
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 certain events on a particular table or view in a database.
Example: 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:
SELECT DepartmentId AS deptId FROM tblEmployee;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:
SELECT DISTINCT column1, column2,....
FROM table_name;13. What are aggregate and scalar functions?
Aggregate Functions are the functions that 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.
TRUNCATE TABLE table_name;DELETE is a DML(Data Manipulation Language) command which is generally used to delete a particular row of a table.
DELETE FROM table_name WHERE condition;DELETE can also be used to delete all rows at once(similar to TRUNCATE) using the following syntax:
DELETE FROM table_name;15. Can we delete an entry from the 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.
If we do so and try to delete an entry from the reference table, the matching rows of both the tables will get deleted.
16. What do you mean by Schema?
Schema is the logical structure of the database that 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.
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
19. Explain 3 tier Structure.
In a 3 Tier Structure, the whole DBMS can be interpreted as 3 levels of architecture.
- The external also known as view level gives users access to data with the help of the underlying conceptual level.
- The conceptual level is also known as the logical level. The logical structure (schema) and relationships are defined at this level.
- The internal level is also known as the 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 key that comprises more than one attribute is known as a composite key.
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 ensures unique value for each row when applied to a particular column. A column that has a UNIQUE constraint is allowed to have a maximum of 1 NULL.
To add unique constraint in a table:
ALTER TABLE student ADD CONSTRAINT unique_roll UNIQUE (roll_no);
//Or
ALTER TABLE student ADD UNIQUE (roll_no);21. What is the difference between UNIQUE and PRIMARY key?
- A UNIQUE column can have a maximum of 1 NULL but a PRIMARY KEY can never be NULL.
- A table can have multiple UNIQUE KEY but only one PRIMARY KEY.
A primary key must be unique.
A unique key does not have to be the primary key.