DBMS QUESTIONS

Top DBMS Interview Questions

1. What is meant by a transparent DBMS?

2. Define Relationship in a database context.

3. What is a stored procedure?

4. Explain query optimization in DBMS.

5. What integrity rules exist in a DBMS?

6. Explain the functionality of a DML compiler.

7. What is the purpose of the HAVING clause in SQL?

8. How is pattern matching performed in SQL?

9. Explain the various types of keys in a database.

10. Compare the DELETE and TRUNCATE commands in a DBMS.

Top DBMS Interview Questions for Freshers

Q1: What is the purpose of a Database Management System (DBMS)?

Ans. A DBMS is a collection of tools that simplify the creation and maintenance of databases.
It provides an interface for database creation, data entry, deletion, and updates. DBMS ensures
data security, reduces redundancy and inconsistency, enables quick access, and organizes data
effectively.

Q2: How would you define a database?

Ans. A database is a collection of data that is organized in a logical and consistent way for easy
access. This well-structured data is typically stored in files on secondary devices and enables
efficient production, insertion, and updating of information. Tables, comprised of records and
fields, form the basis of a database. Fields contain details about specific attributes, and data can
be retrieved using queries via a database management system (DBMS).

Q3: What are the limitations of traditional file-based systems make DBMS a preferable
choice?

Ans. Traditional file-based systems lack indexing, leading to time-consuming content retrieval.
Redundancy and inconsistency arise due to duplicate data, and updating one file causes
inconsistency across all duplicates. Disorganized data makes it difficult to access information.
Furthermore, unlike DBMS, traditional systems lack concurrency management, which allows
simultaneous operations. DBMS addresses integrity checks, data isolation, atomicity, and
security issues.
Q4: Enumerate some advantages of using a DBMS.

Ans. Here are a few benefits of utilizing a database management system (DBMS):

• Data sharing: Multiple users can access data from the same database simultaneously.
• Integrity restrictions: DBMS allows refined data storage with integrity constraints.
• Data redundancy control: It provides a system for managing data redundancy by
consolidating it into a single database.
• Data independence: The data structure can be changed without impacting running
applications.
• Backup and recovery: DBMS offers automatic data backup and recovery
capabilities.
• Data security: DBMS ensures secure data storage and transfers through
authentication and encryption techniques.

Q5: What are the different languages used in DBMS?

Ans. DBMS utilizes various languages for different purposes:

• Data Definition Language (DDL): It includes commands for defining databases,

such as CREATE, ALTER, DROP, TRUNCATE, and RENAME.
• Data Manipulation Language (DML): This language allows data manipulation in a
database using commands like SELECT, UPDATE, INSERT, DELETE, etc.
• Data Control Language (DCL): DCL commands manage user permissions and
controls within the database system, including GRANT and REVOKE.
• Transaction Control Language (TCL): TCL provides commands to manage
database transactions, such as COMMIT, ROLLBACK, and SAVEPOINT.

Q6: What do the ACID properties signify in DBMS?

Ans. The ACID properties in DBMS are fundamental principles that ensure data integrity. They
are as follows:

• Atomicity - It guarantees that a transaction is treated as a single unit of execution,

either completing entirely or not at all.
• Consistency - This property ensures that the database remains in a consistent state
before and after each transaction.
• Isolation - It allows multiple transactions to occur concurrently without interfering
with each other.
• Durability - Once a transaction is committed, its changes are permanently saved in
non-volatile memory.

Q7: Are NULL values in a database equivalent to zero or blank space?

Ans. No, NULL values are distinct from zero and blank space. A NULL value represents an
unknown, missing, or inapplicable value, whereas zero represents a numeric value and blank
space represents a character. For example, a NULL value in the "number of courses" field
indicates an unknown value, while a zero value indicates that no courses have been taken.
Q8: What are the definitions of super key, primary key, candidate key, and foreign key?

Ans.

• A super key is a set of attributes in a relation schema that uniquely identifies each
tuple, and no proper subset of the super key can have the same property.
• A candidate key in DBMS is a minimal super key, which means no subset of its
attributes can form a super key.
• The primary key is chosen from the candidate keys and serves as the main identifier
for a table. There can be only one primary key in a table.
• A foreign key is a field (or set of fields) in one table that refers to the primary key in
another table, establishing a relationship between the two tables.

Q9: What distinguishes a primary key from unique constraints?

Ans. While a primary key cannot contain NULL values, unique constraints can. A table can
have only one primary key, whereas it can have multiple unique constraints.

Q10: What is DBMS and what is its utility? Explain RDBMS with examples.

Ans. DBMS, or Database Management System, is a collection of software tools that enables
users to create and maintain databases. It provides an interface for various database operations
such as data insertion, deletion, and updates. DBMS ensures secure and efficient data storage
compared to file-based systems. It addresses challenges like data inconsistency and
redundancy, making data management more organized and user-friendly. Prominent examples
of DBMS include file systems, XML, and the Windows Registry.

RDBMS, or Relational Database Management System, was developed to facilitate easier data
access and storage compared to DBMS. In RDBMS, data is stored in tables consisting of rows
and columns. Storing data in this structured manner enables efficient retrieval of specific values
from the database. Examples of popular RDBMS systems are MySQL, Oracle DB, and others.

Q11: How would you define a checkpoint in DBMS?

Ans. A checkpoint in DBMS is a technique that permanently stores all previous logs onto the
storage drive. It ensures the recovery and maintenance of the ACID properties by preserving
transaction logs and maintaining shadow pages. Checkpoints serve as minimal recovery points
from which the database engine can recover after a crash, using the transaction log records to
restore all committed data up to the crash moment.

Q12: Explain the concept of a database system.

Ans. A database system is a combination of databases and database management system

software. It enables various tasks such as storing data in a database without concerns of
redundancy or inconsistency. The DBMS software allows for efficient retrieval of data from
the database, ensuring precise and secure data storage, retrieval, and access.
Q13: What is meant by a Data Model?

Ans. A data model consists of a set of tools for describing data, semantics, and constraints. It
helps in defining the relationship between data entities and their attributes. Some common data
models include hierarchical data models, network models, entity relationship models, and
relational models. Understanding data models in DBMS is essential in the field of data
modeling.

Q14: When does a checkpoint occur in DBMS?

Ans. A checkpoint occurs as a snapshot of the current state of the database management system.
It limits the amount of work needed during system restart after a subsequent crash. Checkpoints
are used for database recovery following a system crash. They are particularly employed in
log-based recovery solutions to restart the system without having to redo all transactions from
the beginning.

Q15: How do entities differ from attributes in a database?

Ans. In a database, an entity represents a real-world object, such as an employee, department,

or designation. On the other hand, an attribute describes a specific characteristic or property of
an entity. For example, an employee entity may have attributes like name, ID, and age, which
provide additional information about the entity.

Q16: What types of interactions can be handled by a DBMS?

Ans. A DBMS can handle various interactions, including data definition (creating and
modifying the structure of databases and tables), update (adding, modifying, or deleting data),
retrieval (retrieving specific data from databases), and administration (managing user
permissions, security, backups, etc.).

Q17: Explain query optimization in DBMS.

Ans. Query optimization is the process of identifying the most efficient plan for evaluating a
query, minimizing the estimated cost. It involves selecting the best algorithms and approaches
among multiple options to achieve the desired outcome. Query optimization aims to deliver
query results faster, handle a larger number of queries in less time, and reduce the complexity
of time and space requirements.

Q18: Are NULL values equivalent to zero or blank space?

Ans. No, NULL values are distinct from zero or blank space. While zero represents a numerical
value and blank space represents a character, NULL denotes a value that is unavailable,
unknown, assigned, or not applicable.

Q19: Define aggregation and atomicity.

Ans: Aggregation is a feature in the Entity-Relationship (E-R) model that allows one
relationship set to interact with another relationship set.
Atomicity is a property that specifies a database alteration must adhere to all rules or none at
all. If any part of a transaction fails, the entire transaction fails.

Q20: What are the different levels of abstraction in DBMS?

Ans. DBMS operates at three levels of data abstraction:

• Physical Level: This is the lowest level of abstraction that defines how data is stored
in the database system.
• Logical Level: After the physical level, the logical level defines what data is stored in
the database and how the data pieces relate to each other.
• View Level: The highest level of abstraction, the view level, describes a specific
portion of the entire database, focusing on a user's perspective or requirements.

Q21: How would you define an entity-relationship model?

Ans. An entity-relationship model is a graphical approach to database design where real-world

objects are represented as entities, and the relationships between them are depicted. It provides
a visual representation that allows the database administrators (DBAs) to quickly understand
the schema.

Q22: Explain the terms Entity, Entity Type, and Entity Set in DBMS.

Ans.

• Entity: An entity refers to a real-world object with attributes that represent its
characteristics. For example, an employee is an entity with attributes like empid and
empname.
• Entity Type: An entity type is a collection of entities that share the same attributes. It
represents one or more related tables in a database. For instance, an employee type
can have attributes such as empid, empname, and department.
• Entity Set: In a database, an entity set is a collection of all entities of a specific entity
type. It represents a group of related entities, such as a set of employees, companies,
or persons.

Q23: What is meant by a transparent DBMS?

Ans. A transparent DBMS is a type of database management system that hides its physical
structure from users. The physical structure, including the memory manager and how data is
stored on disk, is concealed from users. This abstraction allows users to focus on the logical
representation of the data without worrying about the internal storage details.

Q24: What are the unary operations in Relational Algebra?

Ans. In relational algebra, unary operations are operations that work on a single operand. The
two common unary operations are PROJECTION and SELECTION. These operations
manipulate and retrieve data from a single relation. Additionally, RENAME is another unary
operation used to rename attributes or relations in a relational algebra expression.
Q25: Define RDBMS.

Ans. RDBMS stands for Relational Database Management System. It is a type of database
management system that organizes and stores data in the form of tables, allowing for efficient
data retrieval and management. RDBMS uses a structured approach to identify and access data
by establishing relationships between different pieces of data within the database. Commonly,
RDBMS employs the SQL language for data manipulation and administration.

Q26: What are the different data models?

Ans. There are several different data models, including:

• Hierarchical data model

• Network model
• Relational model
• Entity-Relationship model

Q27: Explain Relation Schema and Relation.

Ans. A Relation Schema, also known as a table schema, defines the properties and structure of
a relation or table. It specifies the name of the table and provides a blueprint for organizing
data into tables. The relation schema does not contain any actual data.

A Relation represents a table in a relational database. It is a collection of tuples (rows) where

each tuple consists of an ordered list of values. The attributes in the relation are the connected
columns, and key attributes uniquely identify the tuples. In mathematical terms, a relation is a
set of tuples (t1, t2, t3, ..., tn) where each tuple contains an ordered list of n values (t=1) (v1,
v2, ..., vn).

Q28: What is the Degree of relation?

Ans. The degree of a relation is one of the attributes of its relation schema. It refers to the
number of attributes (columns) in the relation. The degree, also known as Cardinality, describes
how many times one entity occurs in relation to another entity. There are three degrees of
relation: one-to-one (1:1), one-to-many (1:M), and many-to-one (M:1).

Q29: Define Relationship in a database context.

Ans. A relationship in a database refers to an association between two or more entities. It

represents the way entities are related to each other. There are three types of relationships
commonly used in database management systems:

• One-to-One: A single record of one entity can be linked to a single record of another
entity.
• One-to-Many (Many-to-One): A single record of one entity can be linked to multiple
records of another entity, and vice versa.
• Many-to-Many: Multiple records of one entity can be linked to multiple records of
another entity.
•
Q30: What are the disadvantages of file processing systems?

Ans. File processing systems have several disadvantages, including:

• Data redundancy
• Lack of security
• Inconsistency
• Difficulty in accessing data
• Limited data sharing
• Data integrity issues
• Lack of concurrent access
• Data isolation problems
• Atomicity challenges

Q31: Define Data Abstraction in DBMS.

Ans. Data abstraction in DBMS refers to the process of hiding unnecessary details from users,
allowing for simplified user interaction with the complex data structures of a database system.
It involves presenting data in a simplified manner through layers of abstraction, ensuring that
users can easily access and manipulate the data while focusing on the relevant aspects of the
system.

Q32: Why is the use of DBMS recommended? List some of its major advantages.

Ans. The use of DBMS is recommended due to several significant advantages it offers:

• Controlled Redundancy: DBMS allows for controlled data redundancy by storing data
in a centralized database, reducing duplication and improving data consistency.
• Data Sharing: DBMS enables simultaneous data sharing among multiple users and
applications, as all users access the same shared database.
• Backup and Recovery Facility: DBMS includes a built-in 'backup and recovery'
feature, automating the process of creating data backups and restoring them when
needed.
• Enforced Integrity Constraints: DBMS enforces integrity constraints to maintain data
integrity and ensure that only valid and consistent data is stored in the database.
• Data Independence: DBMS provides data independence, allowing modifications to
the data structure without affecting the structure of existing applications.

Q33: What is the distinction between the HAVING and WHERE clause?

Ans. In a SELECT statement, the WHERE clause is used to filter rows before grouping occurs.
On the other hand, the HAVING clause is used to set conditions for groups or aggregate
functions after grouping. The WHERE clause cannot contain aggregate functions, while the
HAVING clause is specifically designed for such purposes.

Q34: Define a transaction and explain the ACID properties.

Ans. A transaction in a database refers to a collection of database operations that must be

treated as a single unit, ensuring that all operations are either fully executed or none of them
are executed at all. The ACID properties (Atomicity, Consistency, Isolation, and Durability)
ensure the reliability and integrity of database transactions. Atomicity guarantees that a
transaction is treated as a whole, Consistency ensures that the database remains in a valid state
before and after each transaction, Isolation allows multiple transactions to occur concurrently
without interference, and Durability ensures that once a transaction is committed, its changes
are permanently saved.

Q35: What is a Join?

Ans. In SQL, a join is a technique used to combine data from two or more tables based on a
common field. It allows for the retrieval of related data from multiple tables into a single result
set.

Q36: What does Identity represent in a database context?

Ans. Identity (or AutoNumber) is a column in a database that automatically generates numeric
values. It can be set with a starting value and an increment, typically set to 1. Another example
of an automatically generated value is a GUID column, which generates unique identifiers that
cannot be changed. Identity and GUID columns are often used as primary keys. Indexing these
columns is typically unnecessary.

Q37: Define a view in SQL.

Ans. In SQL, a view is a virtual table created from the result set of a SQL statement. It allows
users to query the view as if it were a regular table, providing a simplified and customized
representation of the data in the underlying tables.

Q38: What are the uses of views?

Ans. Views have several uses in a database:

1. Data Subset: Views can represent a subset of data from one or more tables, limiting the
exposure of underlying tables to users and allowing them to query specific portions of the data.

2. Simplification: Views can combine and simplify multiple tables into a single virtual table,
making complex data structures more manageable and easier to work with.

3. Aggregation: Views can be used as aggregated tables, where the database engine performs
aggregate functions (e.g., sum, average) and displays the results alongside the data.

4. Data Complexity Hiding: Views can hide the complexity of underlying tables by providing
a simplified and user-friendly representation of the data.

5. Minimal Storage: Views occupy minimal storage space since they only store the definition
of the view, not a copy of the entire data it displays.

6. Enhanced Security: Depending on the SQL engine used, views can provide additional
security by restricting access to specific columns or rows of the underlying tables.

Q39: What is a Trigger?

Ans. A trigger is a piece of code associated with insert, update, or delete operations on a table.
It is automatically executed when the associated query is run. Triggers are used to maintain
database integrity by enforcing specific actions or constraints whenever data changes occur.

Q40: What is a stored procedure?

Ans. A stored procedure is similar to a function in that it is a collection of operations grouped

together. It consists of a set of pre-defined procedures that are commonly used in applications
to perform database-related activities. Stored procedures can be executed with specific
parameters and provide a way to encapsulate and reuse code logic within the database.

Q41: How does a Trigger differ from a Stored Procedure?

Ans. Triggers cannot be directly called, unlike stored procedures. They are only associated with
specific queries or actions.

Q42: Explain the concept of database normalization.

Ans. Database normalization is a process that evaluates relation schemas based on their
functional dependencies and primary keys to achieve desirable properties such as minimizing
redundancy and reducing inconsistencies in data insertion, deletion, and updates. If a relation
schema does not meet these properties, it is decomposed into smaller relation schemas that do
meet the requirements.

Q43: What are indexes in a database?

Ans. Indexes in a database are data structures designed to improve the speed of data retrieval
operations on database tables. They trade off increased writes and storage space for faster
access to data. Indexes allow for quicker searches based on specific values by organizing and
storing data in a specific order.

Q44: Differentiate between clustered and non-clustered indexes.

Ans. Clustered indexes determine the physical order in which data is stored on a disk. Each
database table can have only one clustered index. Non-clustered indexes, on the other hand,
establish logical ordering of data rather than physical ordering. They often involve the creation
of tree structures, such as B-trees or B+ trees, where leaves point to disk records.

Q45: What is denormalization in a database?

Ans. Denormalization is a technique used for database optimization, where duplicate data is
intentionally introduced into one or more tables. This approach aims to improve performance
by reducing the need for complex joins and increasing data retrieval speed.

Q46: What is a clause in SQL?

Ans. In SQL, a clause is a component of a query that allows for filtering or customizing the
data being queried. It helps specify conditions or actions to be applied to the data.
Q47: Define Livelock.

Ans. Livelock occurs when two or more processes repeatedly engage in a futile interaction in
response to changes in other processes. In this situation, the processes are continuously
executing without making any progress. Unlike a deadlock, where processes are waiting,
livelock involves active execution without achieving a desirable outcome.

Q48: What is QBE in the context of databases?

Ans. Query-by-example (QBE) is a visual/graphical technique used to retrieve information

from a database by using skeleton tables as query templates. QBE allows users to express what
they want to achieve by entering example values into the template, without the need for
programming languages. It simplifies the process of accessing desired information by
employing a two-dimensional syntax that resembles tables.

Q49: Why are cursors necessary in embedded SQL?

Ans. Cursors are used in embedded SQL to store the result of a query, allowing application
programs to process the data row by row. SQL statements operate on sets of data and return
another set of data, while host language programs typically work with data one row at a time.
Cursors enable traversal through a set of rows generated by a SQL SELECT statement within
the code, acting as a pointer for efficient row-by-row processing.

Advanced DBMS Interview Questions and Answers

Q50: What is the purpose of normalization in DBMS?

Ans. Normalization in DBMS involves structuring the attributes of a database to minimize or

eliminate data redundancy. Its purpose is to achieve a clean and well-organized relational table,
reduce complexity, and prevent anomalies. Normalization helps break down large database
tables into smaller ones and establish relationships between them, ensuring data integrity and
reducing the likelihood of duplicate data or recurring groups.

Q51: How does a database schema differ from a database state?

Ans. A database state refers to the actual collection of data stored in a database at a specific
moment, while a database schema refers to the overall design and structure of the database.

Q52: What is the purpose of SQL?

Ans. SQL (Structured Query Language) serves as a language used to interact with relational
databases. Its primary purpose is to perform operations such as querying, updating, and
modifying data in the database.

Q53: Explain the concepts of a Primary Key and Foreign Key.

Ans. A Primary Key is a unique identifier for records in a database table, ensuring their
uniqueness and serving as a reference point for data retrieval. A Foreign Key, on the other
hand, establishes a relationship between two or more tables by referencing the Primary Key of
another table.
Q54: What are the main differences between a Primary Key and Unique Key?

Ans. Here are the key differences between a Primary Key and Unique Key:

- A Primary Key cannot contain a null value, whereas a Unique Key can.

- Each table can have only one Primary Key, while multiple Unique Keys can exist in a table.

Q55: What is the concept of a subquery in SQL?

Ans. A subquery, also known as an inner query, is a query that is nested within another query.
It is used to retrieve data that is based on the results of another query.

Q56: How is the DROP command used, and what are the differences between DROP,
TRUNCATE, and DELETE commands?

Ans. The DROP command is used in SQL to delete an existing table, database, index, or view
from a database. The main differences between DROP, TRUNCATE, and DELETE commands
are:

- DROP and TRUNCATE are DDL (Data Definition Language) commands used to delete
tables and their associated indexes, while DELETE is a DML (Data Manipulation Language)
command used to delete specific rows from a table.

- DROP removes the entire table structure, while TRUNCATE deletes all rows from a table
but keeps the structure intact.

- DELETE allows the operation to be rolled back (undo), while DROP and TRUNCATE cannot
be undone.

Q57: What is the main difference between UNION and UNION ALL?

Ans. UNION and UNION ALL are used to combine data from multiple tables, but they differ
in terms of duplicate row handling. UNION removes duplicate rows and selects only distinct
rows after merging the data, while UNION ALL does not remove duplicates and selects all
rows from the tables.

Q58: Define Correlated Subquery in DBMS.

Ans. A correlated subquery is a subquery that is executed for each row of the outer query. It is
a nested query where the inner query depends on the values from the outer query.

Q59: What integrity rules exist in a DBMS?

Ans. In a DBMS, two major integrity rules are:

- Entity Integrity: This rule ensures that the value of a primary key cannot be NULL.

- Referential Integrity: This rule is associated with foreign keys and ensures that the foreign
key value is either NULL or matches the primary key of another relation.
Q60: What is the E-R model in DBMS?

Ans. The E-R (Entity-Relationship) model is a conceptual model used in relational databases.
It represents entities, their attributes, and the relationships between them, providing a visual
representation of the database structure and data dependencies.

Q61: What is the significance of a functional dependency in a DBMS?

Ans. A functional dependency in a DBMS is a constraint that describes the relationship

between different attributes within a relation. For example, if a relation 'R1' has attributes Y
and Z, the functional dependency between them can be represented as Y->Z, indicating that Z
is functionally dependent on Y.

Q62: How is pattern matching performed in SQL?

Ans. Pattern matching in SQL is achieved using the LIKE operator. The 'percent' symbol (%)
is used to match zero or more characters, while the underscore symbol (_) is used to match a
single character.

Q63: What is Data Warehousing?

Ans. Data warehousing involves collecting, extracting, processing, and importing data from
various sources into a single database. It serves as a central repository for data analytics,
receiving data from transactional systems and other relational databases. Data warehousing
enables historical data storage and supports decision-making processes.

Q64: Explain the distinction between intension and extension in a database.

Ans. In a database, intension refers to the database schema or design, which remains relatively
unchanged. Extension, on the other hand, represents the actual data stored in the database at a
specific point in time, fluctuating as tuples are created, updated, or deleted.

Q65: Compare the DELETE and TRUNCATE commands in a DBMS.

Ans. The DELETE command is used to remove specific rows from a table based on conditions
specified in the WHERE clause. In contrast, the TRUNCATE command is used to delete all
data from a table without conditions.

Q66: What is a lock, and how does a shared lock differ from an exclusive lock during a
transaction in a database?

Ans. A lock in a database prevents multiple users or sessions from updating the same data
simultaneously. A shared lock allows multiple transactions to read data concurrently, while an
exclusive lock ensures only one transaction can perform write operations to maintain data
consistency.
Q67: Describe the different normalization forms in a DBMS.

Ans. The primary normalization forms in a DBMS include:

1. 1NF: Ensures atomicity of data and eliminates duplicate columns.

2. 2NF: Builds upon 1NF by ensuring non-key attributes are fully functionally dependent on
the primary key.

3. 3NF: Extends 2NF by eliminating transitive dependencies between non-key attributes.

4. BCNF: Further refines 3NF by ensuring that no non-prime attribute is functionally dependent
on another non-prime attribute.

Q68: Explain the various types of keys in a database.

Ans. In a database, there are several types of keys:

- Candidate Key: A set of attributes that uniquely identifies a table.

- Super Key: A superset of candidate keys that uniquely identifies a tuple.

- Primary Key: A chosen candidate key that uniquely identifies each tuple.

- Unique Key: Similar to a primary key but allows NULL values.

- Alternate Key: Candidate keys that are not selected as the primary key.

- Foreign Key: Connects attributes between tables, referencing a primary key from another
table.

- Composite Key: A key composed of multiple attributes that uniquely identifies a tuple.

Q69: Differentiate between a 2-tier and 3-tier architecture in a DBMS.

Ans. A 2-tier architecture involves direct interaction between client applications and the
database server. In contrast, a 3-tier architecture adds an intermediary layer, providing a
graphical user interface and enhanced security by separating the client-side application from
the server-side application.

Q70: Explain the distinction between logical database design and physical database
design and how this separation leads to data independence.

Ans. Logical database design involves transforming the conceptual schema into a relational
database structure, while physical database design focuses on storage, indexing, and
optimization. This separation leads to data independence because the logical design is
independent of the physical design, allowing changes to the physical implementation without
affecting the logical representation.
Q71: What are temporary tables and when are they beneficial?

Ans. Temporary tables are tables that are used for a single session or for the duration of a
transaction. They are commonly used to support unique rollups or specific application
processing needs. Unlike permanent tables, temporary tables do not have pre-allocated space
and dynamically allocate space as rows are added. In Oracle, you can create temporary tables
using the CREATE GLOBAL TEMPORARY TABLE command.

Q72: Define entity type extension.

Ans. Entity type extension involves combining similar entity types into a single type and
grouping them together as an entity set.

Q73: What is conceptual design in DBMS?

Ans. Conceptual design is the initial stage of the database design process. It aims to create a
database that is independent of database software and physical implementation details. During
conceptual design, a conceptual data model is developed, describing the primary data items,
properties, relationships, and constraints within a specific domain.

Q74: Explain the various types of failures that can occur in an Oracle database.

Ans. Different types of failures that can occur in an Oracle database include:

- Bad data type

- Insufficient space

- Instance failure

- Media failure

- User process failure

- User error

- Statement failure

- Insufficient privileges

Q75: What is the primary goal of RAID technology?

Ans. The main goal of RAID (Redundant Array of Inexpensive Disks) technology is to improve
fault tolerance and performance in storage systems. RAID combines multiple hard drives into
a single logical unit, providing greater fault tolerance and throughput compared to individual
drives. It is crucial for enhancing data reliability and system performance in various
client/server applications.
Q76: Define database partitioning and its significance.

Ans. Database partitioning is the process of dividing a logical database into independent
components to enhance availability, performance, and manageability. It allows for efficient
access to specific partitions, enables data storage on low-cost storage devices, and improves
query performance.

Q77: Explain the functionality of a DML compiler.

Ans. A DML (Data Manipulation Language) compiler is responsible for converting DML
statements into a query language that can be understood by the query evaluation engine. Since
DML has grammar elements similar to other programming languages, a DML compiler is
necessary to compile the code into a language understood by the query evaluation engine,
facilitating proper query execution.

Q78: What is Relational Algebra?

Ans. Relational Algebra is a procedural query language that consists of a set of operations
applied to one or two relations to produce a new relation as output. It represents the basic set
of operations in the relational model. Relational algebra resembles algebraic operations
performed on numbers and includes operations like set difference, projection, selection, union,
rename, and more.

Q79: What is Relational Calculus?

Ans. Relational Calculus is a non-procedural query language that utilizes mathematical

predicate calculus instead of algebraic operations. It is not concerned with mathematical
essentials like algebra, differential equations, integration, etc. Relational calculus is divided
into two types: tuple relational calculus and domain relational calculus.

Q80: Define durability in DBMS.

Ans. Durability, in the context of DBMS, refers to the property that ensures the effects of a
committed transaction persist even in the event of a system failure. Once a DBMS confirms
the successful completion of a transaction, its changes are stored in non-volatile memory,
providing durability by safeguarding the data against system failures.

Q81: What is the significance of System R and what are its two major subsystems?

Ans. System R was developed by IBM San Jose Research Centre from 1974 to 1979. It was
the first relational database management system (RDBMS) to demonstrate improved
transaction processing performance and implement SQL, the standard query language for
relational data. System R served as a working prototype to address real-world problems.

System R consists of two major subsystems:

- Relational Data System

- Research Storage System

Q82: How do you interact with an RDBMS?

Ans. To interact with an RDBMS, you use Structured Query Language (SQL). SQL queries
are used to provide input to the database, and the database processes these queries to generate
the desired output.

Q83. Explain the concepts of Proactive, Retroactive, and Simultaneous Update.

Ans. - Proactive Update: Changes made to the database before it is operational in the real world.

- Retroactive Update: Updates applied to a database after it has been in use.

- Simultaneous Update: Updates applied to the database at the same time they become effective
in the real world.

Q84: Define specialization and generalization in the context of database design.

Ans. - Specialization: The process of creating subclasses for a specific entity type. Each
subclass inherits all attributes and relationships of the parent entity and may have additional
unique attributes and relationships.

- Generalization: The process of identifying common attributes and relationships among a

group of entities and defining a common superclass for them.

Q85: What is the concept of Fill Factor in relation to indexes?

Ans. Fill Factor refers to the percentage of space left on each leaf-level page of an index that
is densely packed with data. The default value is typically 100, indicating full packing of index
data.

Q86: What is Index Tuning and how does it enhance query performance?

Ans. Index Tuning involves optimizing a set of indexes to improve query performance and
query processing time. It helps in query performance enhancement through the following steps:

- Suggesting optimal queries using query optimizers.

- Measuring the impact with indices, query distribution, and performance metrics.

- Optimizing databases for a small number of frequently executed queries.

Q87: Explain what a deadlock is and how it can be resolved.

Ans. A deadlock occurs when two transactions are waiting for resources that are mutually
unavailable or when one transaction is waiting for another operation to complete. Deadlocks
can be resolved by ensuring that all transactions acquire all required locks at the same time. If
a deadlock is detected, one of the transactions needs to be aborted to break the deadlock and
remove the incomplete work.
Q88: When should you use an index?

Ans. An index should be used when you want to enforce uniqueness in a database or when you
need to simplify sorting and retrieve data quickly. Columns that are frequently used in queries
are often good candidates for indexing.

Q89: What is the difference between a clustered and a non-clustered index?

Ans. A clustered index determines the physical order of data in a table, whereas a non-clustered
index is a separate structure that points to the data in the table.

Q90: What is a database transaction? Explain the concept of atomicity.

Ans. A database transaction is a logical unit of work that consists of one or more database
operations. Atomicity ensures that either all operations within a transaction are executed
successfully, or none of them are. It ensures the transaction's "all or nothing" property.

Q91: What is a foreign key constraint?

Ans. A foreign key constraint is a rule that ensures the referential integrity between two tables.
It establishes a relationship between a column in one table and the primary key column in
another table, enforcing data consistency and preventing orphaned records.

Q92: What is the difference between a primary key and a unique key?

Ans. A primary key is a column or set of columns that uniquely identifies each row in a table.
It enforces entity integrity and cannot contain null values. On the other hand, a unique key
ensures that the values in a column or set of columns are unique but allows null values.

Q93: What is the purpose of the GROUP BY clause in SQL?

Ans. The GROUP BY clause is used to group rows based on one or more columns in a SELECT
statement. It is typically used in conjunction with aggregate functions (e.g., SUM, COUNT,
AVG) to perform calculations on groups of data.

Q94: What is the difference between a view and a table?

Ans. A table is a storage structure that holds data in a relational database, while a view is a
virtual table derived from one or more tables or views. Unlike tables, views do not store data
physically but provide a way to query and manipulate data from multiple tables as a single
entity.

Q95: What is the purpose of the HAVING clause in SQL?

Ans. The HAVING clause is used to filter rows in a SELECT statement based on conditions
applied to groups defined by the GROUP BY clause. It is similar to the WHERE clause but
operates on grouped data rather than individual rows.
Q96: Explain the concept of referential integrity in database management.

Ans. Referential integrity is a rule that ensures the consistency and integrity of data between
related tables. It requires that foreign key values in one table match primary key values in
another table, preventing the creation of orphaned or invalid records.

Q97: What are the ACID properties in database transactions?

Ans. ACID stands for Atomicity, Consistency, Isolation, and Durability. Atomicity ensures
that a transaction is treated as a single unit of work. Consistency ensures that a transaction
brings the database from one valid state to another. Isolation ensures that concurrent
transactions do not interfere with each other. Durability ensures that once a transaction is
committed, its changes are permanent and can survive system failures.

Q98: What is the purpose of database normalization?

Ans. Database normalization is the process of organizing data in a database to minimize

redundancy and dependency issues. It aims to eliminate data anomalies and improve data
integrity by dividing tables into smaller, well-structured entities and establishing relationships
between them.

Q99: What is the role of a database administrator (DBA) in a DBMS?

Ans. A database administrator (DBA) is responsible for managing and maintaining a database
system. Their roles include designing and implementing database structures, ensuring data
security and integrity, optimizing database performance, managing user access and
permissions, and performing backups and recovery.

Q100: What is the difference between OLTP and OLAP databases?

Ans. OLTP (Online Transaction Processing) databases are optimized for transactional
processing, supporting day-to-day operations with a focus on fast and concurrent data
modifications. OLAP (Online Analytical Processing) databases, on the other hand, are
designed for analytical processing and reporting, providing complex querying capabilities and
aggregations for decision-making and data analysis purposes.

1. What is SQL?

It stands for Structured Query Language. A programming language used for interaction with
relational database management systems (RDBMS). This includes fetching, updating,
inserting, and removing data from tables.

2. What are SQL dialects? Give some examples.

The various versions of SQL, both free and paid, are also called SQL dialects. All the flavors
of SQL have a very similar syntax and vary insignificantly only in additional functionality.
Some examples are Microsoft SQL Server, PostgreSQL, MySQL, SQLite, T-SQL, Oracle,
and MongoDB.

3. What are the main applications of SQL?

Using SQL, we can:

• create, delete, and update tables in a database

• access, manipulate, and modify data in a table
• retrieve and summarize the necessary information from a table or several tables
• add or remove certain rows or columns from a table
All in all, SQL allows querying a database in multiple ways. In addition, SQL easily
integrates with other programming languages, such as Python or R, so we can use their
combined power.

4. What is an SQL statement? Give some examples.

Also known as an SQL command. It's a string of characters interpreted by the SQL engine as
a legal command and executed accordingly. Some examples of SQL statements
are SELECT, CREATE, DELETE, DROP, REVOKE, and so on.

5. What types of SQL commands (or SQL subsets) do you know?

• Data Definition Language (DDL) – to define and modify the structure of a database.
• Data Manipulation Language (DML) – to access, manipulate, and modify data in a
database.
• Data Control Language (DCL) – to control user access to the data in the database
and give or revoke privileges to a specific user or a group of users.
• Transaction Control Language (TCL) – to control transactions in a database.
• Data Query Language (DQL) – to perform queries on the data in a database to
retrieve the necessary information from it.

6. Give some examples of common SQL commands of each type.

• DDL: CREATE, ALTER TABLE, DROP, TRUNCATE, and ADD COLUMN

• DML: UPDATE, DELETE, and INSERT
• DCL: GRANT and REVOKE
• TCL: COMMIT, SET TRANSACTION, ROLLBACK, and SAVEPOINT
• DQL: – SELECT
7. What is a database?

A structured storage space where the data is kept in many tables and organized so that the
necessary information can be easily fetched, manipulated, and summarized.

8. What is DBMS, and what types of DBMS do you know?

It stands for Database Management System, a software package used to perform various
operations on the data stored in a database, such as accessing, updating, wrangling, inserting,
and removing data. There are various types of DBMS, such as relational, hierarchical,
network, graph, or object-oriented. These types are based on the way the data is organized,
structured, and stored in the system.

9. What is RDBMS? Give some examples of RDBMS.

It stands for Relational Database Management System. It's the most common type of DBMS
used for working with data stored in multiple tables related to each other by means of shared
keys. The SQL programming language is particularly designed to interact with RDBMS.
Some examples of RDBMS are MySQL, PostgreSQL, Oracle, MariaDB, etc.

10. What are tables and fields in SQL?

A table is an organized set of related data stored in a tabular form, i.e., in rows and columns.
A field is another term for a column of a table.

11. What is an SQL query, and what types of queries do you know?

A query is a piece of code written in SQL to access the data from a database or to modify the
data. Correspondingly, there are two types of SQL queries: select and action queries. The
first ones are used to retrieve the necessary data (this also includes limiting, grouping,
ordering the data, extracting the data from multiple tables, etc.), while the second ones are
used to create, add, delete, update, rename the data, etc.

12. What is a subquery?

Also called an inner query; a query placed inside another query, or an outer query. A
subquery may occur in the clauses such as SELECT, FROM, WHERE, UPDATE, etc. It's
also possible to have a subquery inside another subquery. The innermost subquery is run first,
and its result is passed to the containing query (or subquery).

13. What types of SQL subqueries do you know?

• Single-row – returns at most one row.

• Multi-row – returns at least two rows.
• Multi-column – returns at least two columns.
• Correlated – a subquery related to the information from the outer query.
• Nested – a subquery inside another subquery.
14. What is a constraint, and why use constraints?

A set of conditions defining the type of data that can be input into each column of a table.
Constraints ensure data integrity in a table and block undesired actions.

15. What SQL constraints do you know?

• DEFAULT – provides a default value for a column.

• UNIQUE – allows only unique values.
• NOT NULL – allows only non-null values.
• PRIMARY KEY – allows only unique and strictly non-null values (NOT
NULL and UNIQUE).
• FOREIGN KEY – provides shared keys between two and more tables.

16. What is a join?

A clause used to combine and retrieve records from two or multiple tables. SQL tables can be
joined based on the relationship between the columns of those tables. Check out our SQL
joins tutorial for more context.

17. What types of joins do you know?

• (INNER) JOIN – returns only those records that satisfy a defined join condition in
both (or all) tables. It's a default SQL join.
• LEFT (OUTER) JOIN – returns all records from the left table and those records from
the right table that satisfy a defined join condition.
• RIGHT (OUTER) JOIN – returns all records from the right table and those records
from the left table that satisfy a defined join condition.
• FULL (OUTER) JOIN – returns all records from both (or all) tables. It can be
considered as a combination of left and right joins.

18. What is a primary key?

A column (or multiple columns) of a table to which the PRIMARY KEY constraint was
imposed to ensure unique and non-null values in that column. In other words, a primary key
is a combination of the NOT NULL and UNIQUE constraints. The primary key uniquely
identifies each record of the table. Each table should contain a primary key and can't contain
more than one primary key.

19. What is a unique key?

A column (or multiple columns) of a table to which the UNIQUE constraint was imposed to
ensure unique values in that column, including a possible NULL value (the only one).
20. What is a foreign key?

A column (or multiple columns) of a table to which the FOREIGN KEY constraint was
imposed to link this column to the primary key in another table (or several tables). The
purpose of foreign keys is to keep connected various tables of a database.

21. What is an index?

A special data structure related to a database table and used for storing its important parts and
enabling faster data search and retrieval. Indexes are especially efficient for large databases,
where they significantly enhance query performance.

22. What types of indexes do you know?

• Unique index – doesn't allow duplicates in a table column and hence helps maintain
data integrity.
• Clustered index – defines the physical order of records of a database table and
performs data searching based on the key values. A table can have only one clustered
index.
• Non-clustered index – keeps the order of the table records that doesn't match the
physical order of the actual data on the disk. It means that the data is stored in one
place and a non-clustered index – in another one. A table can have multiple non-
clustered indexes.

23. What is a schema?

A collection of database structural elements such as tables, stored procedures, indexes,

functions, and triggers. It shows the overall database architecture, specifies the relationships
between various objects of a database, and defines different access permissions for them.

24. What is a SQL comment?

A human-readable clarification on what a particular piece of code does. SQL code comments
can be single-line (preceded by a double dash --) or span over multiple lines (as
follows: /*comment_text*/). When the SQL engine runs, it ignores code comments. The
purpose of adding SQL code comments is to make the code more comprehensive for those
people who will read it in the future.

25. What is a SQL operator?

A reserved character, a combination of characters, or a keyword used in SQL queries to

perform a specific operation. SQL operators are commonly used with the WHERE clause to
set a condition (or conditions) for filtering the data.

26. What types of SQL operators do you know?

• Arithmetic (+, -, *, /, etc.)

• Comparison (>, <, =, >=, etc.)
• Compound (+=, -=, *=, /=, etc.)
 • Logical (AND, OR, NOT, BETWEEN, etc.)
• String (%, _, +, ^, etc.)
• Set (UNION, UNION ALL, INTERSECT, and MINUS (or EXCEPT))

27. What is an alias?

A temporary name given to a table (or a column in a table) while executing a certain SQL
query. Aliases are used to improve the code readability and make the code more compact. An
alias is introduced with the AS keyword:

SELECT col_1 AS column

FROM table_name;

PO WE RE D B Y

28. What is a clause?

A condition imposed on a SQL query to filter the data to obtain the desired result. Some
examples are WHERE, LIMIT, HAVING, LIKE, AND, OR, ORDER BY, etc.

29. What are some common statements used with the SELECT query?

The most common ones are FROM, GROUP BY, JOIN, WHERE, ORDER BY, LIMIT,
and HAVING.

30. How to create a table?

Using the CREATE TABLE statement. For example, to create a table with three columns of
predefined datatypes, we apply the following syntax:

CREATE TABLE table_name (col_1 datatype,

col_2 datatype,

col_3 datatype);

PO WE RE D B Y

31. How to update a table?

Using the UPDATE statement. The syntax is:

UPDATE table_name

SET col_1 = value_1, column_2 = value_2

WHERE condition;

PO WE RE D B Y

32. How to delete a table from a database?

Using the DROP TABLE statement. The syntax is: DROP TABLE table_name;.

33. How to get the count of records in a table?

Using the COUNT() aggregate function with the asterisk passed as its argument: SELECT
COUNT(*) FROM table_name;.

34. How to sort records in a table?

Using the ORDER BY statement:

SELECT * FROM table_name

ORDER BY col_1;

PO WE RE D B Y

We can specify that we need a descending order using the DESC keyword; otherwise, the
order will be ascending by default. Also, we can sort by more than one column and specify
for each one, ascending or descending order separately. For example:

SELECT * FROM table_name

ORDER BY col_1 DESC, col_3, col_6 DESC;

PO WE RE D B Y

35. How to select all columns from a table?

Using the asterisk * with the SELECT statement. The syntax is: SELECT * FROM
table_name;.

36. How to select common records from two tables?

Using the INTERSECT statement:

SELECT * FROM table_1

INTERSECT

SELECT * FROM table_1;

PO WE RE D B Y

37. What is the DISTINCT statement and how do you use it?

This statement is used with the SELECT statement to filter out duplicates and return only
unique values from a column of a table. The syntax is:

SELECT DISTINCT col_1

FROM table_name;

PO WE RE D B Y

38. What are entities? Give some examples.

An entity is a real-world object, creature, place, or phenomenon for which the data can be
gathered and stored in a database table. Each entity corresponds to a row in a table, while the
table's columns describe its properties. Some examples of entities are bank transactions,
students in a school, cars sold, etc.

39. What are relationships? Give some examples.

Relationships are the connections and correlations between entities, basically meaning how
two or more tables of a database are related to one another. For example, we can find an ID
of the same client in a table on sales data and in a customer table.

40. What is NULL value? How is it different from zero or a blank space?

A NULL value indicates the absence of data for a certain cell of a table. Instead, zero is a
valid numeric value, and an empty string is a legal string of zero length.

40 Top Intermediate SQL Interview Questions and Answers

In this section, we take a look at the 40 most popular intermediate SQL questions and
answers, so that you'll know what to expect from your interviewer. These questions are more
suited to SQL practitioners with a few years of experience.

1. What is a function in SQL, and why use functions?

A database object representing a set of SQL statements frequently used for a certain task. A
function takes in some input parameters, performs calculations or other manipulations on
them, and returns the result. Functions help improve code readability and avoid repetition of
the same code snippets.

2. What types of SQL functions do you know?

• Aggregate functions – work on multiple, usually grouped records for the provided
columns of a table, and return a single value (usually by group).
• Scalar functions – work on each individual value and return a single value.
On the other hand, SQL functions can be built-in (defined by the system) or user-defined
(created by the user for their specific needs).

3. What aggregate functions do you know?

• AVG() – returns the average value

• SUM() – returns the sum of values
• MIN() – returns the minimum value
• MAX() – returns the maximum value
• COUNT() – returns the number of rows, including those with null values
• FIRST() – returns the first value from a column
• LAST()– returns the last value from a column

4. What scalar functions do you know?

• LEN() (in other SQL flavors – LENGTH()) – returns the length of a string, including
the blank spaces
• UCASE() (in other SQL flavors – UPPER()) – returns a string converted to the upper
case
• LCASE() (in other SQL flavors – LOWER()) – returns a string converted to the lower
case
• INITCAP() – returns a string converted to the title case (i.e., each word of the string
starts from a capital letter)
• MID() (in other SQL flavors – SUBSTR()) – extracts a substring from a string
• ROUND() – returns the numerical value rounded to a specified number of decimals
• NOW() – returns the current date and time

5. What are case manipulation functions? Give some examples.

Case manipulation functions represent a subset of character functions, and they're used to
change the case of the text data. With these functions, we can convert the data into the upper,
lower, or title case.

• UCASE() (in other SQL flavors – UPPER()) – returns a string converted to the upper
case
• LCASE() (in other SQL flavors – LOWER()) – returns a string converted to the lower
case
• INITCAP() – returns a string converted to the title case (i.e., each word of the string
starts from a capital letter)
6. What are character manipulation functions? Give some examples.

Character manipulation functions represent a subset of character functions, and they're used
to modify the text data.

• CONCAT() – joins two or more string values appending the second string to the end
of the first one
• SUBSTR() – returns a part of a string satisfying the provided start and end points
• LENGTH() (in other SQL flavors – LEN()) – returns the length of a string, including
the blank spaces
• REPLACE() – replaces all occurrences of a defined substring in a provided string
with another substring
• INSTR() – returns the numeric position of a defined substring in a provided string
• LPAD() and RPAD() – return the padding of the left-side/right-side character for
right-justified/left-justified value
• TRIM() – removes all the defined characters, as well as white spaces, from the left,
right, or both ends of a provided string

7. What is the difference between local and global variables?

Local variables can be accessed only inside the function in which they were declared. Instead,
global variables, being declared outside any function, are stored in fixed memory structures
and can be used throughout the entire program.

8. What is the default data ordering with the ORDER BY statement, and how do you change
it?

By default, the order is ascending. To change it to descending, we need to add

the DESC keyword as follows:

SELECT * FROM table_name

ORDER BY col_1 DESC;

PO WE RE D B Y

9. What set operators do you know?

• UNION – returns the records obtained by at least one of two queries (excluding
duplicates)
• UNION ALL – returns the records obtained by at least one of two queries (including
duplicates)
• INTERSECT – returns the records obtained by both queries
 • EXCEPT (called MINUS in MySQL and Oracle) – returns only the records obtained
by the first query but not the second one

10. What operator is used in the query for pattern matching?

The LIKE operator in combination with the % and _ wildcards. The % wildcard represents
any number of characters including zero, while _ – strictly one character.

11. What is the difference between a primary key and a unique key?

While both types of keys ensure unique values in a column of a table, the first one identifies
uniquely each record of the table, and the second one prevents duplicates in that column.

12. What is a composite primary key?

The primary key of a table, based on multiple columns.

13. What is the order of appearance of the common statements in the SELECT query?

SELECT – FROM – JOIN – ON – WHERE – GROUP BY – HAVING – ORDER

BY – LIMIT

14. In which order the interpreter executes the common statements in the SELECT query?

FROM – JOIN – ON – WHERE – GROUP BY – HAVING – SELECT – ORDER

BY – LIMIT

15. What is a view, and why use it?

A virtual table containing a subset of data retrieved from one or more database tables (or
other views). Views take very little space, simplify complex queries, limit access to the data
for security reasons, enable data independence, and summarize data from multiple tables.

16. Can we create a view based on another view?

Yes. This is also known as nested views. However, we should avoid nesting multiple views
since the code becomes difficult to read and debug.

17. Can we still use a view if the original table is deleted?

No. Any views based on that table will become invalid after deleting the base table. If we try
to use such a view anyway, we'll receive an error message.

18. What types of SQL relationships do you know?

• One-to-one – each record in one table corresponds to only one record in another table
• One-to-many – each record in one table corresponds to several records in another
table
• Many-to-many – each record in both tables corresponds to several records in another
table
19. What are the possible values of a BOOLEAN data field?

In some SQL flavors, such as PostgreSQL, the BOOLEAN data type exists explicitly and
takes values TRUE, FALSE, or NULL. In other flavors, such as Microsoft SQL Server,
the BIT datatype is used to store Boolean values as integers 1 (true) or 0 (false).

20. What is normalization in SQL, and why use it?

Normalization is a process of database design that includes organizing and restructuring data
in a way to reduce data redundancy, dependency, duplication, and inconsistency. This leads
to enhanced data integrity, more tables within the database, more efficient data access and
security control, and greater query flexibility.

21. What is denormalization in SQL, and why use it?

Denormalization is the process opposite of normalization: it introduces data redundancy and

combines data from multiple tables. Denormalization optimizes the performance of the
database infrastructure in situations when read operations are more important than write
operations since it helps avoid complex joins and reduces the time of query running.

22. What is the difference between renaming a column and giving an alias to it?

Renaming a column means permanently changing its actual name in the original table. Giving
an alias to a column means giving it a temporary name while executing an SQL query, with
the purpose to make the code more readable and compact.

23. What is the difference between nested and correlated subqueries?

A correlated subquery is an inner query nested in a bigger (outer) query that refers to the
values from the outer query for its execution, meaning that a correlated subquery depends on
its outer query. Instead, a non-correlated subquery doesn't rely on the data from the outer
query and can be run independently of it.

24. What is the difference between clustered and non-clustered indexes?

While a clustered index defines the physical order of records of a table and performs data
searching based on the key values, a non-clustered index keeps the order of records that
doesn't match the physical order of the actual data on the disk. A table can have only one
clustered index but many non-clustered ones.

25. What is the CASE() function?

The way to implement the if-then-else logic in SQL. This function sequentially checks the
provided conditions in the WHEN clauses and returns the value from the
corresponding THEN clause when the first condition is satisfied. If none of the conditions is
satisfied, the function returns the value from the ELSE clause in case it's provided, otherwise,
it returns NULL. The syntax is:

CASE

WHEN condition_1 THEN value_1

 WHEN condition_2 THEN value_2

WHEN condition_3 THEN value_3

...

ELSE value

END;

PO WE RE D B Y

26. What is the difference between the DELETE and TRUNCATE statements?

DELETE is a reversible DML (Data Manipulation Language) command used to delete one or
more rows from a table based on the conditions specified in the WHERE clause.
Instead, TRUNCATE is an irreversible DDL (Data Definition Language) command used to
delete all rows from a table. DELETE works slower than TRUNCATE. Also, we can't use
the TRUNCATE statement for a table containing a foreign key.

27. What is the difference between the DROP and TRUNCATE statements?

DROP deletes a table from the database completely, including the table structure and all the
associated constraints, relationships with other tables, and access
privileges. TRUNCATE deletes all rows from a table without affecting the table structure and
constraints. DROP works slower than TRUNCATE. Both are irreversible DDL (Data
Definition Language) commands.

28. What is the difference between the HAVING and WHERE statements?

The first one works on aggregated data after they are grouped, while the second one checks
each row individually. If both statements are present in a query, they appear in the following
order: WHERE – GROUP BY – HAVING. The SQL engine interprets them also in the same
order.

29. How do you add a record to a table?

Using the INSERT INTO statement in combination with VALUES. The syntax is:

INSERT INTO table_name

VALUES (value_1, value_2, ...);

PO WE RE D B Y

30. How to delete a record from a table?

Using the DELETE statement. The syntax is:

DELETE FROM table_name

WHERE condition;

PO WE RE D B Y

In this way, we can also delete multiple records if they satisfy the provided condition.

31. How to add a column to a table?

Using the ALTER TABLE statement in combination with ADD. The syntax is:

ALTER TABLE table_name

ADD column_name datatype;

PO WE RE D B Y

32. How to rename a column of a table?

Using the ALTER TABLE statement in combination with RENAME COLUMN ... TO
... The syntax is:

ALTER TABLE table_name

RENAME COLUMN old_column_name TO new_column_name;

PO WE RE D B Y

33. How to delete a column from a table?

Using the ALTER TABLE statement in combination with DROP COLUMN. The syntax is:

ALTER TABLE table_name

DROP COLUMN column_name;

PO WE RE D B Y

34. How to select all even or all odd records in a table?

By checking the remainder of the division by 2. In some SQL versions (e.g., PostgreSQL and
My SQL), we use the MOD function, in the others (Microsoft SQL Server and SQLite) – the
modulo operator (%). To select all even records using MOD:

SELECT * FROM table_name

WHERE MOD(ID_column, 2) = 0;

PO WE RE D B Y

To select all even records using %:

SELECT * FROM table_name

WHERE ID_column % 2 = 0;

PO WE RE D B Y

To select all odd records, the syntax is identical in both cases, only that we would use the
inequality operator <> instead of =.

35. How to prevent duplicate records when making a query?

Using the DISTINCT statement in combination with SELECT or creating a unique key for
that table.

36. How to insert many rows in a table?

Using the INSERT INTO statement in combination with VALUES. The syntax is:

INSERT INTO table_name

VALUES (value_1, value_2, ...),

(value_3, value_4, ...),

(value_5, value_6, ...),

...;

PO WE RE D B Y

37. How to find the nth highest value in a column of a table?

Using the OFFSET clause. For example, to find the 6th highest value from a column, we
would use the following syntax:

SELECT * FROM table_name

ORDER BY column_name DESC

LIMIT 1
OFFSET 5;

PO WE RE D B Y

38. How to find the values in a text column of a table that start with a certain letter?

Using the LIKE operator in combination with the % and _ wildcards. For example, we need
to find all surnames in a table that start with "A". The query is:

SELECT * FROM table_name

WHERE surname LIKE 'A_';

PO WE RE D B Y

Here, we assume that a surname must contain at least two letters. Without this assumption
(meaning that a surname can be just A), the query is as follows:

SELECT * FROM table_name

WHERE surname LIKE 'A%';

PO WE RE D B Y

39. How to find the last id in a table?

Using the MAX() function. Otherwise, in many SQL versions, we can use the following
syntax:

SELECT id

FROM table_name

ORDER BY id DESC

LIMIT 1;

PO WE RE D B Y

or in Microsoft SQL Server:

SELECT TOP 1 id

FROM table_name

ORDER BY id DESC
PO WE RE D B Y

40. How to select random rows from a table?

Using the RAND() function in combination with ORDER BY and LIMIT. In some SQL
flavors, such as PostgreSQL, it's called RANDOM(). For example, the following code will
return five random rows from a table in MySQL:

SELECT * FROM table_name

ORDER BY RAND()

LIMIT 5;