DBMS Interview Questions and Answers for Cognizant GenC
1. What is DBMS and what are its advantages over file systems?
A Database Management System (DBMS) is software designed to store, retrieve, define, and
manage data in a database.
Advantages over file systems:
- Data Independence
- Reduced Data Redundancy
- Data Consistency
- Data Security
- Concurrent Access
- Data Integrity
- Backup and Recovery
Example: Consider a college management system:
- File System: Separate files for students, courses, grades leading to redundancy
- DBMS: Integrated tables with relationships, ensuring data consistency and reduced
redundancy
2. Explain the different types of Database relationships with examples
There are three main types of relationships:
1. One-to-One (1:1)
One record in Table A matches exactly one record in Table B
Example: Person and Passport Number
```sql
Person(ID, Name, PassportID)
Passport(PassportID, IssueDate, ExpiryDate)
```
2. One-to-Many (1:N)
One record in Table A can match many records in Table B
Example: Department and Employees
```sql
Department(DeptID, DeptName)
Employee(EmpID, Name, DeptID)
```
3. Many-to-Many (M: N)
Multiple records in Table A can match multiple records in Table B
Example: Students and Courses
```sql
Student(StudentID, Name)
Course(CourseID, CourseName)
StudentCourse(StudentID, CourseID)
```
3. What are the different normalization forms? Explain with examples
In a database management system (DBMS), normalization is the process of organizing data
in a database to improve data integrity and reduce redundancy:
1. First Normal Form (1NF)
Each cell should contain a single value
Each record should be unique
Example:
Before 1NF:
```
Student(ID, Name, Subjects)
1, John, Math,Physics,Chemistry
```
After 1NF:
```
Student(ID, Name, Subject)
1, John, Math
1, John, Physics
1, John, Chemistry
```
2. Second Normal Form (2NF)
Should be in 1NF
No partial dependencies
3. Third Normal Form (3NF)
Should be in 2NF
No transitive dependencies
4. What is a primary key and foreign key? Explain with example
- Primary Key: Unique identifier for each record in a table
- Foreign Key: Field that links to primary key of another table
Example:
```sql
-- Primary Key
CREATE TABLE Department (
DeptID INT PRIMARY KEY,
DeptName VARCHAR(50)
);
-- Foreign Key
CREATE TABLE Employee (
EmpID INT PRIMARY KEY,
Name VARCHAR(50),
DeptID INT,
FOREIGN KEY (DeptID) REFERENCES Department(DeptID)
);
```
5. What are the different types of SQL commands?
1. DDL (Data Definition Language)
- CREATE, ALTER, DROP, TRUNCATE
```sql
CREATE TABLE Employee (ID INT, Name VARCHAR(50));
```
2. DML (Data Manipulation Language)
- INSERT, UPDATE, DELETE
```sql
INSERT INTO Employee VALUES (1, 'John');
```
3. DCL (Data Control Language)
- GRANT, REVOKE
```sql
GRANT SELECT ON Employee TO user1;
```
4. TCL (Transaction Control Language)
- COMMIT, ROLLBACK, SAVEPOINT
```sql
BEGIN TRANSACTION;
UPDATE Account SET balance = balance - 100;
COMMIT;
```
6. What is a transaction? Explain ACID properties
Answer: A transaction is a sequence of operations performed as a single logical unit of work.
ACID Properties:
1. Atomicity: All or nothing principle
2. Consistency: Database remains consistent before and after transaction
3. Isolation: Transactions are isolated from each other
4. Durability: Changes are permanent after commit
Example:
```sql
BEGIN TRANSACTION;
UPDATE Account SET balance = balance - 1000 WHERE ID = 1;
UPDATE Account SET balance = balance + 1000 WHERE ID = 2;
COMMIT;
7. What are joins in SQL? Explain different types with examples
Answer:
1. INNER JOIN
```sql
SELECT e.Name, d.DeptName
FROM Employee e
INNER JOIN Department d ON e.DeptID = d.DeptID;
```
2. LEFT JOIN
```sql
SELECT e.Name, d.DeptName
FROM Employee e
LEFT JOIN Department d ON e.DeptID = d.DeptID;
```
3. RIGHT JOIN
```sql
SELECT e.Name, d.DeptName
FROM Employee e
RIGHT JOIN Department d ON e.DeptID = d.DeptID;
```
4. FULL JOIN
```sql
SELECT e.Name, d.DeptName
FROM Employee e
FULL JOIN Department d ON e.DeptID = d.DeptID;
```
8. What is an index? What are its types?
Answer: An index is a data structure that improves the speed of data retrieval operations.
Types:
1. Single-Column Index
```sql
CREATE INDEX idx_name ON Employee(Name);
```
2. Composite Index
```sql
CREATE INDEX idx_name_dept ON Employee(Name, DeptID);
```
3. Unique Index
```sql
CREATE UNIQUE INDEX idx_email ON Employee(Email);
```
9. What is a view? How is it different from a table?
Answer: A view is a virtual table based on the result set of an SQL statement.
Example:
```sql
CREATE VIEW EmployeeDept AS
SELECT e.Name, d.DeptName
FROM Employee e
JOIN Department d ON e.DeptID = d.DeptID;
```
Differences:
- Views don't store data physically
- Views can provide data security
- Views can simplify complex queries
10. Explain the concept of triggers with an example
Answer: Triggers are special stored procedures that automatically execute when specific
events occur.
Example:
```sql
CREATE TRIGGER UpdateAudit
AFTER UPDATE ON Employee
FOR EACH ROW
BEGIN
INSERT INTO AuditLog(EmpID, Action, DateTime)
VALUES (NEW.ID, 'UPDATE', NOW());
END;
```
11. What is a stored procedure? How is it different from a function?
Answer: A stored procedure is a prepared SQL code that can be saved and reused.
Example:
```sql
CREATE PROCEDURE GetEmployeesByDept
@DeptID INT
AS
BEGIN
SELECT Name, Salary
FROM Employee
WHERE DeptID = @DeptID;
END;
```
12. What is a deadlock? How can it be prevented?
Answer: A deadlock occurs when two or more transactions wait indefinitely for each other
to release locks.
Prevention:
```sql
-- Set transaction isolation level
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
-- Use timeout
SET LOCK_TIMEOUT 10000;
```
13. Explain the difference between DELETE, TRUNCATE, and DROP
Answer:
```sql
-- DELETE: DML command, can be rolled back
DELETE FROM Employee WHERE DeptID = 10;
-- TRUNCATE: DDL command, cannot be rolled back
TRUNCATE TABLE Employee;
-- DROP: DDL command, removes table structure
DROP TABLE Employee;
```
14. What are aggregate functions? Give examples
Answer: Aggregate functions perform calculations on a set of values.
Examples:
```sql
SELECT
COUNT(*) as TotalEmployees,
AVG(Salary) as AvgSalary,
MAX(Salary) as MaxSalary,
MIN(Salary) as MinSalary,
SUM(Salary) as TotalSalary
FROM Employee;
```
15. What is a subquery? Explain with example
Answer: A subquery is a query nested inside another query.
Example:
```sql
SELECT Name
FROM Employee
WHERE Salary > (
SELECT AVG(Salary)
FROM Employee
);
```
16. What are constraints in SQL?
Answer: SQL constraints are used to specify rules for data in a table.
```sql
CREATE TABLE Employee (
ID INT PRIMARY KEY,
Name VARCHAR(50) NOT NULL,
Age INT CHECK (Age >= 18),
Salary DECIMAL(10,2) DEFAULT 0,
Email VARCHAR(50) UNIQUE
);
```
17. Explain the GROUP BY and HAVING clauses
Answer:
```sql
SELECT DeptID, COUNT(*) as EmpCount, AVG(Salary) as AvgSalary
FROM Employee
GROUP BY DeptID
HAVING AVG(Salary) > 50000;
```
18. What is database partitioning?
Answer: Partitioning divides large tables into smaller, manageable parts.
Example:
```sql
CREATE TABLE Sales (
ID INT,
SaleDate DATE,
Amount DECIMAL
PARTITION BY RANGE (YEAR(SaleDate)) (
PARTITION p0 VALUES LESS THAN (2022),
PARTITION p1 VALUES LESS THAN (2023),
PARTITION p2 VALUES LESS THAN MAXVALUE
);
```
19. What is the difference between UNION and UNION ALL?
Answer:
```sql
-- UNION removes duplicates
SELECT Name FROM Employee1
UNION
SELECT Name FROM Employee2;
-- UNION ALL keeps duplicates
SELECT Name FROM Employee1
UNION ALL
SELECT Name FROM Employee2;
```
20. Explain the concept of database backup and recovery
Answer:
Types of Backups:
1. Full Backup:
```sql
BACKUP DATABASE EmployeeDB
TO DISK = 'D:\Backups\EmployeeDB.bak';
```
2. Differential Backup:
```sql
BACKUP DATABASE EmployeeDB
TO DISK = 'D:\Backups\EmployeeDB_Diff.bak'
WITH DIFFERENTIAL;
```
Recovery:
```sql
RESTORE DATABASE EmployeeDB
FROM DISK = 'D:\Backups\EmployeeDB.bak';
```
