1. Define a database. Mention its two real-life applications. 9. State the use of the ALTER TABLE command.

of the ALTER TABLE command. Mention one situation where it is

A database is an organized collection of structured data that can be easily accessed, used.
managed, and updated. ALTER TABLE is used to modify the structure of an existing table.
Applications: Example: To add a new column:
• Banking systems to store customer account details. ALTER TABLE Students ADD Email VARCHAR(100);
• School management systems to store student records.
10. What is the function of the DROP TABLE command in SQL?
2. What is DBMS? How is it different from a file system? The DROP TABLE command permanently deletes the entire table, along with all its data
A DBMS (Database Management System) is software that helps in creating, storing, and structure.
modifying, and retrieving data from databases.
Difference: 11. Differentiate between DELETE and DROP SQL commands in terms of use and
• DBMS reduces data redundancy and ensures data integrity, while file systems effect.
are prone to duplication and inconsistency. Feature DELETE DROP
Use Removes rows Removes entire table
3. What is SQL? Name any three categories of SQL commands.
SQL (Structured Query Language) is used to interact with and manage relational Table remains Yes No (table is removed)
databases. Rollback Possible (with transactions) Not possible
Three categories of SQL commands:
• DDL (Data Definition Language): CREATE, DROP 12. What is a primary key in a database table? Why is it important?
• DML (Data Manipulation Language): INSERT, UPDATE A primary key is a field or combination of fields that uniquely identifies each row in a
• DQL (Data Query Language): SELECT table.
Importance: Ensures uniqueness and prevents duplicate records.
4. What is the purpose of the CREATE TABLE command in SQL?
The CREATE TABLE command is used to define a new table in the database by specifying 13. What is the purpose of using a foreign key in a relational database?
its columns and data types. A foreign key links two tables and ensures referential integrity, i.e., it prevents invalid
data from being inserted into the related table.
5. Explain the use of the INSERT INTO command in SQL.
The INSERT INTO command adds new records (rows) to an existing table. 14. Mention the use of the WHERE clause in SQL with any SQL command.
The WHERE clause filters rows that meet a condition.
6. What does the SELECT command do in SQL? When is it used? Example:
The SELECT command retrieves data from one or more tables. It is used when we want SELECT * FROM Students WHERE Marks > 80;
to display or query existing data.
15. State the use of the ORDER BY clause in a SELECT query.
7. Write the use of the UPDATE command with respect to records in a table. ORDER BY is used to sort the result set in ascending (ASC) or descending (DESC) order.
The UPDATE command is used to modify existing records in a table. It can update one or Example:
more columns for one or more rows. SELECT Name FROM Students ORDER BY Marks DESC;

8. Explain the purpose of the DELETE command in SQL. 16. What is the GROUP BY clause used for in SQL?
The DELETE command is used to remove specific rows from a table based on a GROUP BY is used to group rows that have the same values in specified columns, often
condition. with aggregate functions.
Example:
SELECT Dept, COUNT(*) FROM Employees GROUP BY Dept;
17. Explain the role of aggregate functions like COUNT(), SUM(), AVG() in SQL. A relational database stores data in tables with rows and columns.
Aggregate functions perform calculations on column data: Examples:
• COUNT() – returns the number of rows • MySQL
• SUM() – returns the total of values • PostgreSQL
• AVG() – returns the average of values
5. What are the differences between a relational and non-relational database?
18. Why is normalization used in databases? Mention its benefit. Feature Relational DB Non-Relational DB
Normalization is the process of organizing data to minimize redundancy and improve Structure Tables (Rows, Columns) Key-value, Document, etc.
consistency.
Benefit: Reduces duplicate data and ensures data integrity. Schema Fixed schema Dynamic schema
Example MySQL, Oracle MongoDB, Cassandra
19. State the use of the DISTINCT keyword in a SELECT statement.
DISTINCT is used to remove duplicate values from the result set. 6. What is a schema in a database?
Example: A schema is the structure that defines how data is organized in the database —
SELECT DISTINCT Dept FROM Employees; including tables, fields, types, relationships, constraints.

20. Mention the purpose of the HAVING clause in SQL and how it differs from WHERE. 7. What is metadata in the context of databases?
HAVING filters grouped data (used with GROUP BY), while WHERE filters individual rows Metadata is “data about data”. It describes structure, format, constraints of the data,
before grouping. e.g., data type of a column, table name, etc.
Example:
SELECT Dept, COUNT(*) FROM Employees GROUP BY Dept HAVING COUNT(*) > 2; 8. What is the role of normalization? Name the first three normal forms.
Normalization reduces redundancy and organizes data efficiently.
DBMS Conceptual Questions with Answers First 3 normal forms:
• 1NF: Atomic values in each cell
1. What are the key features of a DBMS? • 2NF: Remove partial dependencies
• Data storage and retrieval • 3NF: Remove transitive dependencies
• Data integrity and security
• Concurrency control 9. What is data consistency and how is it maintained in DBMS?
• Data abstraction Data consistency means all users see the same data at the same time.
• Backup and recovery Maintained through:
• Transactions
2. What is data integrity and how does a DBMS enforce it? • Constraints
Data integrity ensures the accuracy and consistency of data. • Concurrency control
DBMS enforces it using:
• Primary and foreign keys 10. Explain the ACID properties of a database transaction.
• Constraints like NOT NULL, UNIQUE, CHECK, DEFAULT • Atomicity: All-or-nothing execution
• Consistency: Data must remain valid
3. Explain the concept of data redundancy and how DBMS helps reduce it. • Isolation: Concurrent transactions don’t affect each other
Data redundancy is the unnecessary repetition of data. • Durability: Changes are permanent after commit
DBMS uses normalization, referential integrity, and centralized data storage to
eliminate redundancy.
SQL Theory Questions with Answers
4. What is a relational database? Give two examples.
11. What is a subquery? Provide an example.
A subquery is a query inside another query. 18. Explain the concept of transactions in SQL. What commands control transactions?
Example: A transaction is a unit of work performed on a database.
SELECT Name FROM Employee Commands:
WHERE Salary = (SELECT MAX(Salary) FROM Employee); • BEGIN or START TRANSACTION
• COMMIT (save changes)
12. What are joins in SQL? Name different types of joins. • ROLLBACK (undo changes)
Joins are used to combine rows from two or more tables based on a related column.
Types: 19. What is indexing in SQL? Why is it used?
• INNER JOIN An index improves the speed of data retrieval operations.
• LEFT JOIN Used for faster searching, especially on large datasets.
• RIGHT JOIN
• FULL OUTER JOIN 20. How does a DBMS handle concurrent access to the database?
DBMS uses locking, transactions, and isolation levels to prevent conflicts when
13. What is the difference between INNER JOIN and OUTER JOIN? multiple users access data simultaneously.
INNER JOIN OUTER JOIN
Returns only matching rows Returns matching + unmatched rows (NULLs) 1. DDL (Data Definition Language)
Used to define and modify the structure of a database (tables, schemas, etc.)
Less data More data
Command Use
14. Write an SQL query to retrieve the second highest salary from an Employee table. CREATE TABLE Creates a new table in the database.
SELECT MAX(Salary) FROM Employee ALTER TABLE Modifies an existing table (e.g., add/remove a column).
WHERE Salary < (SELECT MAX(Salary) FROM Employee); DROP TABLE Deletes an existing table and its data permanently.
Example:
15. What is a view in SQL? How is it different from a table?
CREATE TABLE students (
A view is a virtual table created from a SELECT query.
id INT,
Difference: Views do not store data; they show data from tables dynamically.
name VARCHAR(50)
);
16. What are constraints in SQL? Give examples.
Constraints enforce rules at column level.
2. DML (Data Manipulation Language)
Examples:
Used to manipulate data stored in database tables.
• PRIMARY KEY
• FOREIGN KEY Command Use
• NOT NULL INSERT Adds new data (records) into a table.
• UNIQUE UPDATE Modifies existing records in a table.
• CHECK
DELETE Removes records from a table.
• DEFAULT
Example:
17. What is the use of DEFAULT and CHECK constraints? INSERT INTO students (id, name) VALUES (1, 'Rahul');
• DEFAULT: Provides a default value for a column if none is given.
• CHECK: Restricts values allowed in a column. 3. DCL (Data Control Language)
Example: Used to control access to data in the database.
Age INT DEFAULT 18 CHECK (Age >= 18)
Command Use
GRANT Gives user access privileges to the database.
REVOKE Removes user access privileges.
Example:
GRANT SELECT ON students TO user1;

Primary Key (in a Relational Database)

• A primary key is a column or a set of columns in a table that uniquely
identifies each record (row) in that table.
• It cannot have NULL values and must contain unique values.
• It ensures that each row is distinct and helps in efficiently accessing data.
Example:
In a Students table, the student_id column can be the primary key because each
student has a unique ID.

Degree of a Relation
• The degree of a relation refers to the number of attributes (columns) in a table
(relation).
• Simply put, it is the number of fields or columns in the table schema.
Example:
If a table Employees has columns: EmployeeID, Name, Age, Department, the degree of
this relation is 4.

Summary:
Term Meaning
Primary Key Unique identifier for table records
Degree of Relation Number of columns (attributes) in a table