DBMS Interview Questions

DBMS stands for Database Management System, is a set of applications or programs that enable users to create and maintain a database. DBMS provides a tool or an interface for performing various operations such as inserting, deleting, updating, etc. into a database. It is software that enables the storage of data more compactly and securely as compared to a file-based system. A DBMS system helps a user to overcome problems like data inconsistency, data redundancy, etc. in a database and makes it more convenient and organized to use it. Check this DBMS Tutorial by Scaler Topics.

Examples of popular DBMS systems are file systems, XML, Windows Registry, etc.

RDBMS stands for Relational Database Management System and was introduced in the 1970s to access and store data more efficiently than DBMS. RDBMS stores data in the form of tables as compared to DBMS which stores data as files. Storing data as rows and columns makes it easier to locate specific values in the database and makes it more efficient as compared to DBMS.

Examples of popular RDBMS systems are MySQL, Oracle DB, etc.

Learn More

A Database is an organized, consistent, and logical collection of data that can easily be updated, accessed, and managed. Database mostly contains sets of tables or objects (anything created using create command is a database object) which consist of records and fields. A tuple or a row represents a single entry in a table. An attribute or a column represents the basic units of data storage, which contain information about a particular aspect of the table. DBMS extracts data from a database in the form of queries given by the user. 

The absence of indexing in a traditional file-based system leaves us with the only option of scanning the full page and hence making the access of content tedious and super slow. The other issue is redundancy and inconsistency as files have many duplicate and redundant data and changing one of them makes all of them inconsistent. Accessing data is harder in traditional file-based systems because data is unorganized in them.

Another issue is the lack of concurrency control, which leads to one operation locking the entire page, as compared to DBMS where multiple operations can work on a single file simultaneously.

Integrity check, data isolation, atomicity, security, etc. are some other issues with traditional file-based systems for which DBMSs have provided some good solutions.

Following are the few advantages of using a DBMS. 

• Data Sharing: Data from a single database can be simultaneously shared by multiple users. Such sharing also enables end-users to react to changes quickly in the database environment.
• Integrity constraints: The existence of such constraints allows storing of data in an organized and refined manner.
• Controlling redundancy in a database: Eliminates redundancy in a database by providing a mechanism that integrates all the data in a single database.
• Data Independence: This allows changing the data structure without altering the composition of any of the executing application programs.
• Provides backup and recovery facility: It can be configured to automatically create the backup of the data and restore the data in the database whenever required.
• Data Security: DBMS provides the necessary tools to make the storage and transfer of data more reliable and secure. Authentication (the process of giving restricted access to a user) and encryption (encrypting sensitive data such as OTP, credit card information, etc.) are some popular tools used to secure data in a DBMS.

Following are various languages present in DBMS:

• DDL(Data Definition Language):  It contains commands which are required to define the database.
  E.g., CREATE, ALTER, DROP, TRUNCATE, RENAME, etc.
• DML(Data Manipulation Language): It contains commands which are required to manipulate the data present in the database.
  E.g., SELECT, UPDATE, INSERT, DELETE, etc.
• DCL(Data Control Language):  It contains commands which are required to deal with the user permissions and controls of the database system.
  E.g., GRANT and REVOKE.
• TCL(Transaction Control Language):  It contains commands which are required to deal with the transaction of the database.
  E.g., COMMIT, ROLLBACK, and SAVEPOINT.

ACID stands for Atomicity, Consistency, Isolation, and Durability in a DBMS these are those properties that ensure a safe and secure way of sharing data among multiple users.

• Atomicity: This property reflects the concept of either executing the whole query or executing nothing at all, which implies that if an update occurs in a database then that update should either be reflected in the whole database or should not be reflected at all.

• Consistency: This property ensures that the data remains consistent before and after a transaction in a database.

• Isolation: This property ensures that each transaction is occurring independently of the others. This implies that the state of an ongoing transaction doesn’t affect the state of another ongoing transaction.

• Durability: This property ensures that the data is not lost in cases of a system failure or restart and is present in the same state as it was before the system failure or restart.

No, a NULL value is very different from that of zero and blank space as it represents a value that is assigned, unknown, unavailable, or not applicable as compared to blank space which represents a character and zero represents a number.

Example: NULL value in “number_of_courses” taken by a student represents that its value is unknown whereas 0 in it means that the student hasn’t taken any courses.

Normalization is a process of reducing redundancy by organizing the data into multiple tables. Normalization leads to better usage of disk spaces and makes it easier to maintain the integrity of the database.  

Denormalization is the reverse process of normalization as it combines the tables which have been normalized into a single table so that data retrieval becomes faster. JOIN operation allows us to create a denormalized form of the data by reversing the normalization. 

A database lock is a mechanism to protect a shared piece of data from getting updated by two or more database users at the same time. When a single database user or session has acquired a lock then no other database user or session can modify that data until the lock is released.

• Shared Lock: A shared lock is required for reading a data item and many transactions may hold a lock on the same data item in a shared lock. Multiple transactions are allowed to read the data items in a shared lock.
• Exclusive lock: An exclusive lock is a lock on any transaction that is about to perform a write operation. This type of lock doesn’t allow more than one transaction and hence prevents any inconsistency in the database. 

The process of collecting, extracting, transforming, and loading data from multiple sources and storing them in one database is known as data warehousing. A data warehouse can be considered as a central repository where data flows from transactional systems and other relational databases and is used for data analytics. A data warehouse comprises a wide variety of an organization’s historical data that supports the decision-making process in an organization.

The process of hiding irrelevant details from users is known as data abstraction. Data abstraction can be divided into 3 levels:

• Physical Level:  it is the lowest level and is managed by DBMS. This level consists of data storage descriptions and the details of this level are typically hidden from system admins, developers, and users.
• Conceptual or Logical level:  it is the level on which developers and system admins work and it determines what data is stored in the database and what is the relationship between the data points.
• External or View level: it is the level that describes only part of the database and hides the details of the table schema and its physical storage from the users. The result of a query is an example of View level data abstraction.  A view is a virtual table created by selecting fields from one or more tables present in the database.

An entity-relationship model is a diagrammatic approach to a database design where real-world objects are represented as entities and relationships between them are mentioned.

• Entity: An entity is defined as a real-world object having attributes that represent characteristics of that particular object. For example, a student, an employee, or a teacher represents an entity.
• Entity Type: An entity type is defined as a collection of entities that have the same attributes. One or more related tables in a database represent an entity type. Entity type or attributes can be understood as a characteristic which uniquely identifies the entity.  For example, a student represents an entity that has attributes such as student_id, student_name, etc.
• Entity Set: An entity set can be defined as a set of all the entities present in a specific entity type in a database. For example, a set of all the students, employees, teachers, etc. represent an entity set.

Following are different types of relationship amongst tables in a DBMS system:

• One to One Relationship:  This type of relationship is applied when a particular row in table X is linked to a singular row in table Y.

• One to Many Relationship: This type of relationship is applied when a single row in table X is related to many rows in table Y.

• Many to Many Relationship: This type of relationship is applied when multiple rows in table X can be linked to multiple rows in table Y.

• Self Referencing Relationship: This type of relationship is applied when a particular row in table X is associated with the same table.

Following is the major difference between intension and extension in a database:

• Intension: Intension or popularly known as database schema is used to define the description of the database and is specified during the design of the database and mostly remains unchanged.
• Extension: Extension on the other hand is the measure of the number of tuples present in the database at any given point in time. The extension of a database is also referred to as the snapshot of the database and its value keeps changing as and when the tuples are created, updated, or destroyed in a database.

DELETE command: this command is needed to delete rows from a table based on the condition provided by the WHERE clause.

• It deletes only the rows which are specified by the WHERE clause.
• It can be rolled back if required.
• It maintains a log to lock the row of the table before deleting it and hence it’s slow.

TRUNCATE command: this command is needed to remove complete data from a table in a database. It is like a DELETE command which has no WHERE clause.

• It removes complete data from a table in a database.
• It can't be rolled back even if required. ( truncate can be rolled back in some databases depending on their version but it can be tricky and can lead to data loss). Check this link for more details
• It doesn’t maintain a log and deletes the whole table at once and hence it’s fast.

The 2-tier architecture refers to the client-server architecture in which applications at the client end directly communicate with the database at the server end without any middleware involved.
Example – Contact Management System created using MS-Access or Railway Reservation System, etc.

The above picture represents a 2-tier architecture in a DBMS.

The 3-tier architecture contains another layer between the client and the server to provide GUI to the users and make the system much more secure and accessible. In this type of architecture, the application present on the client end interacts with an application on the server end which further communicates with the database system.

Example – Designing registration form which contains a text box, label, button or a large website on the Internet, etc.

The above picture represents a 3-tier architecture in a DBMS.

Recommended Tutorials:

• DBMS MCQ with Answers
• DBMS Architecture
• Top Applications of DBMS
• SQL Interview Questions
• SQL Server Interview Questions
• SQL Queries Interview Questions
• MySQL Interview Questions
• MongoDB Interview Questions
• PL SQL Interview Questions
• Database Testing Interview Questions
• File System vs DBMS
• Interview Preparation Guide

There are mainly 7 types of keys in a database:

• Candidate Key: The candidate key represents a set of properties that can uniquely identify a table. Each table may have multiple candidate keys. One key amongst all candidate keys can be chosen as a primary key. In the below example since studentId and firstName can be considered as a Candidate Key since they can uniquely identify every tuple.
• Super Key: The super key defines a set of attributes that can uniquely identify a tuple. Candidate key and primary key are subsets of the super key, in other words, the super key is their superset.

• Primary Key: The primary key defines a set of attributes that are used to uniquely identify every tuple. In the below example studentId and firstName are candidate keys and any one of them can be chosen as a Primary Key. In the given example studentId is chosen as the primary key for the student table.
• Unique Key: The unique key is very similar to the primary key except that primary keys don’t allow NULL values in the column but unique keys allow them. So essentially unique keys are primary keys with NULL values.
• Alternate Key: All the candidate keys which are not chosen as primary keys are considered as alternate Keys. In the below example, firstname and lastname are alternate keys in the database.
• Foreign Key:  The foreign key defines an attribute that can only take the values present in one table common to the attribute present in another table. In the below example courseId from the Student table is a foreign key to the Course table, as both, the tables contain courseId as one of their attributes.
• Composite Key:  A composite key refers to a combination of two or more columns that can uniquely identify each tuple in a table. In the below example the studentId and firstname can be grouped to uniquely identify every tuple in the table.

Following are the major normalization forms in a DBMS:

Considering the above Table-1 as the reference example for understanding different normalization forms.

• 1NF: It is known as the first normal form and is the simplest type of normalization that you can implement in a database. A table to be in its first normal form should satisfy the following conditions:
  • Every column must have a single value and should be atomic.
  • Duplicate columns from the same table should be removed.
  • Separate tables should be created for each group of related data and each row should be identified with a unique column.

Table-1 converted to 1NF form

• 2NF: It is known as the second normal form. A table to be in its second normal form should satisfy the following conditions:
  • The table should be in its 1NF i.e. satisfy all the conditions of 1NF.
  • Every non-prime attribute of the table should be fully functionally dependent on the primary key i.e. every non-key attribute should be dependent on the primary key in such a way that if any key element is deleted then even the non_key element will be saved in the database.

Breaking Table-1 into 2 different tables to move it to 2NF.

• 3NF: It is known as the third normal form. A table to be in its third normal form should satisfy the following conditions:
  • The table should be in its 2NF i.e. satisfy all the conditions of 2NF.
  • There is no transitive functional dependency of one attribute on any attribute in the same table.

Breaking Table-1 into 3 different tables to move it to 3NF. 

• BCNF: BCNF stands for Boyce-Codd Normal Form and is an advanced form of 3NF. It is also referred to as 3.5NF for the same reason. A table to be in its BCNF normal form should satisfy the following conditions:
  • The table should be in its 3NF i.e. satisfy all the conditions of 3NF.
  • For every functional dependency of any attribute A on B
    (A->B), A should be the super key of the table. It simply implies that A can’t be a non-prime attribute if B is a prime attribute.

A materialized view stores the actual result of a query instead of recalculating it every time. This makes read operations very fast and is useful for reporting and analytics. The main drawback is that the data can become stale. Materialized views need to be refreshed, either manually or on a schedule. Refreshing them can be expensive and impact write performance. They are best used when fast reads are more important than real-time accuracy.

A view is a stored SQL query that behaves like a virtual table. It is used to simplify complex queries and provide consistent access to data. Views do not store data, so the underlying query runs every time the view is accessed. This can cause performance issues for large or complex queries. Views can also hide inefficient SQL, making debugging harder. Avoid using views for heavy calculations or frequently accessed large datasets.

A CTE, written using the WITH clause, is a temporary named result set used within a query. It makes complex queries easier to read and understand compared to nested subqueries. CTEs can be referenced multiple times in the same query, which improves clarity. Recursive CTEs are used to handle hierarchical data like employee reporting structures. Some databases materialize CTEs, which can impact performance. CTEs are mainly a readability and maintainability feature.

One common approach is using window functions like ROW_NUMBER() with PARTITION BY. This allows ranking rows within each group and then filtering the top N rows. Another approach is using a correlated subquery that compares values within the same group. Older databases often rely on subqueries, while modern systems prefer window functions. Window functions are clearer, faster, and easier to maintain. The best approach depends on database support and data size.

UNION combines results from multiple queries and removes duplicate rows from the final output. To do this, the database has to sort and compare rows, which takes extra time. UNION ALL simply appends the results without checking for duplicates. Because of this, UNION ALL is faster and uses fewer resources. If you are sure that the datasets do not overlap, UNION ALL should always be preferred. In real-world systems, UNION ALL is commonly used for better performance.

A correlated subquery is a subquery that depends on values from the outer query. It runs once for each row returned by the main query. Because of this repeated execution, it can be slow on large datasets. In many cases, the same logic can be written using a JOIN, which executes more efficiently. Correlated subqueries are easier to write for simple logic, but can cause performance issues. They are best avoided in high-volume production queries.

Duplicate rows usually appear when one row in a table matches multiple rows in another table. This often happens in one-to-many relationships, like customers and orders. SQL is doing the correct thing, but the result may look wrong if you are not expecting multiple matches. To fix this, you can aggregate the data before joining or ensure you are joining on the correct keys. Sometimes DISTINCT helps, but it can hide real data issues. Understanding the data structure is more important than forcing uniqueness.

An INNER JOIN returns only rows that exist in both tables. A LEFT JOIN returns all rows from the left table, even if there is no matching row on the right side. However, a LEFT JOIN can accidentally behave like an INNER JOIN if you add conditions on the right table in the WHERE clause. This removes rows where the right table values are NULL. To avoid this issue, conditions related to the right table should be written inside the ON clause. This mistake is very common in real production queries.

WHERE is used to filter rows before any grouping or aggregation happens in the query. It works on individual records and cannot use aggregate functions like COUNT, SUM, or AVG. HAVING is applied after GROUP BY and is meant for filtering aggregated results. For example, filtering orders by status goes in WHERE, but filtering customers with total orders greater than 5 goes in HAVING. Using HAVING instead of WHERE usually makes queries slower and harder to read. A good rule is to filter early using WHERE whenever possible.

An index in DBMS is used to speed up data retrieval. B-Trees or Hash Tables DS is used. A very popular and accurate example sums it up well where you can see just like how index is used on the books to directly move to the page you want instead of reading through the previous pages.

Here are the types of Index that you must remember during interviews:

• Primary Index -It is created on a sorted data file, usually on the primary key
• Clustered Index - This one determines the physical order of data in the table, only one allowed.
• Secondary Index - Created on non-primary key columns for faster access
• Non-clustered Index - Separate structure storing pointers to actual data

And here are more such Index types that you can check out:

• B-Tree Index - Balanced tree structure, most commonly used
• B+ Tree Index - An improved version of the B-Tree, stores data only in leaf nodes
• Hash Index - Uses hash functions, very fast for equality searches
• Bitmap Index - Uses bitmaps, efficient for columns with low cardinality
• Full-Text Index - Used for searching text data (like keywords in documents)
• Single-Column Index - Created on one column
• Composite (Compound) Index - Created on multiple columns
• Dense Index - Has an index entry for every record
• Sparse Index - Has entries for only some records (typically one per block)
• Unique Index - Ensures all values in the column are unique
• Covering Index - Includes all required columns, so the query doesn’t need to access the table
• Partial Index - Created only on a subset of rows (used in some databases like PostgreSQL)

You can read more at: Indexing in DBMS - Scaler Topics

A view in DBMS is a virtual table that is created using a SQL query. It does not store data itself, but instead shows data from one or more tables based on the query defined.

So whenever you access a view, the database runs the underlying query and displays the result. So if you take an example, instead of repeatedly writing a complex query, you can create a view and use it like a table.

There are 4 types of views: Single, Complex, Read-only, and Material. - Keep these in mind!

You can write something like -

CREATE VIEW CustomerOrders AS
SELECT customer_id, order_id
FROM Orders;

Now you can simply query the view instead of writing the full query again.

What you should understand carefully is that views are mainly used for:

• Simplifying complex queries
• Providing a security layer by showing only specific columns or rows
• Maintaining a consistent interface for users

Also, remember that views do not improve performance, since the query runs every time. Also, in some cases, you may not be able to directly insert or update data through a view.

If you wish to learn more about this, check out - DBMS SQL View - Scaler Topics

A common problem that is faced in DBMS is that when multiple transactions happen at the same time, it can lead to concurrency problems. This simply means that data either becomes inconsistent or incorrect.

To avoid this problem, transaction isolation levels are used.

First, make sure to understand the kind of problems you may encounter:

1. Dirty read - It happens when a transaction reads data that has been modified by another transaction but not yet committed.
2. Non-repeatable read - This occurs when a transaction reads the same data twice and gets different results because another transaction updated it in between.
3. Phantom read - This one happens when new rows are inserted by another transaction, so the result of a query changes when it is executed again.

Now, to resolve these issues, databases provide different isolation levels:

• Read Uncommitted is placed at the lowest level because, as fast as it works, it becomes unreliable too. You might have to look out for all three here: the dirty, non-repeatable, and phantom read.
• Read Committed can be found as the default in many systems, like PostgreSQL. It prevents dirty reads for sure, but still struggles with non-repeatable and phantom reads.
• Repeatable Read successfully prevents dirty reads and non-repeatable reads, but you must be careful with phantom reads here. Also, this is the default in SQL.
• Serializable is placed at the highest level since it tackles with ALL the problems. It provides the highest consistency but is the slowest because it restricts concurrency the most.

So here is what you should remember: as you increase the isolation level, data consistency improves, but concurrency and performance decrease.

Constraints in SQL are rules applied to table columns. It is used to ensure the accuracy and integrity of data stored in the database.

Here are some constraints that are commonly used:

• NOT NULL - Ensures that a column cannot have NULL values
• UNIQUE - Ensures that all values in a column are distinct
• PRIMARY KEY - Uniquely identifies each row (combines NOT NULL + UNIQUE)
• FOREIGN KEY - Maintains relationships between tables by referencing another table
• CHECK - Ensures that values satisfy a specific condition
• DEFAULT - Assigns a default value if no value is provided

Please note - The FOREIGN KEY constraint is especially important because it enforces referential integrity, which means that the value in one table must match a value in another.

For example, if a row is deleted in the parent table, actions like CASCADE, SET NULL, or RESTRICT define what happens to the related rows.

The difference between SQL and NoSQL comes down to its functionality.

In SQL databases require strict schema that must be defined before adding data. The data is stored in fixed, predefined tables with rows and columns. It typically scales vertically and also integrates ACID properties for reliable functioning for applications where data consistency is important.

Eg: MySQL and PostgreSQL.

When we talk about NoSQL databases. They are stored in a rather flexible/dynamic formats such as key-value, document, column, or graph. It scales horizontally by adding more servers to the database clusters, which is helpful for massive datasets, and it follows the CAP theorem.

Eg: MongoDB, Redis, and Cassandra.

Now, if asked about usage, SQL databases are preferred when:

• data is structured
• relationships between tables are important
• strong consistency is required, like in financial systems

And if asked for NoSQL databases, then they are preferred when:

• data is large and rapidly changing
• flexible schema is needed
• high scalability and performance are required

If they ask during the interview which one is better or which one you choose to work, then explain the following to them -

SQL works well with complex queries and JOINs, while NoSQL is generally optimized for faster reads and writes at scale.

So looking at it practically, both can be used together depending on the use case.

Store procedures and functions help in reusing logic and reducing repeated queries. They are predefined sets of SQL statements and can be used whenever needed.

Now, when we look into the difference between these two.

A stored procedure is a block of SQL code is precompiled which can perform operations like inserting, updating, or deleting data. It is usually executed using commands like CALL or EXEC. It provides security by protecting sensitive data through its confidentiality means and helps in maintenance by breaking complex logicinto simpler modules. The parameters here are IN (input), OUT (output), INOUT (Input/Output).

Now when we look into function -

A function is similar but is mainly used to return a value. It can be used inside SQL queries, such as in SELECT or WHERE clauses.

The main difference between the two is in how they are used.

Stored procedures are generally used when we want to perform actions or operations on data, while functions are used when we want to compute and return a value.

Pleas note - functions can usually be used inside queries, but stored procedures cannot be directly used in SELECT statements.

The good thing is that using stored procedures and functions help in reducing network traffic because the logic is executed inside the database, and they also improve code reuse and security.

At the same time, they can be slightly harder to debug and may tie the application logic closely to the database.

A deadlock occurs when two or more transactions are waiting for each other to release resources, so neither transaction ends up getting processed.

Imagine Transaction A locks Row 1 and is waiting for Row 2, and at the same time, Transaction B locks Row 2 and is waiting for Row 1. In this case, both are stuck waiting for each other, and this situation is called a deadlock.

DBMS uses detection and resolution techniques to handle such deadlocks.

One common method is deadlock detection, where the system checks for cycles using something called a wait-for graph. If a cycle is found, it means a deadlock exists.

Once detected, the DBMS resolves it by selecting one transaction as a victim and rolling it back. This frees the resources so that the other transaction can continue.

There are also ways to prevent deadlocks.

For example,

If transactions acquire locks in a consistent order, deadlocks can often be avoided. Keeping transactions short and using proper isolation levels also helps reduce the chances of deadlocks.

A cursor in DBMS is used to retrieve and process rows one at a time from a result set. Normally, SQL works in a set-based manner, which means that it processes multiple rows together. But sometimes, we may need to handle rows individually, and that’s where cursors are used.

There are two types of cursors:

1. Implicit cursor - automatically created by the database

2. Explicit cursor - defined and controlled by the user

However, you should keep one thing in mind, and that is that cursors are generally slower compared to normal SQL queries, because they process data row by row instead of in sets.

So in most cases, set-based operations are preferred over cursors.

Here are the differences between all three.

1. The DELETE command is used to remove specific rows from a table using a WHERE condition. It is a slower operation because it deletes rows one by one, and it can be rolled back. It also triggers any associated triggers.

2. The TRUNCATE command removes all rows from a table at once. It is much faster than DELETE because it does not delete rows individually. However, it usually cannot be rolled back, and triggers are not fired.

3. The DROP command removes the entire table, including its structure and data. Once a table is dropped, it cannot be recovered easily.

Both primary key and unique key are used to ensure that values in a column are unique, but they differ in a few important ways.

1. A primary key is used to uniquely identify each row in a table. It cannot have NULL values, and a table can have only one primary key. It is usually used for the main identifier.

2. A unique key also ensures that values are unique, but it can allow NULL values (depending on the database). Also, a table can have multiple unique keys.

A trigger in DBMS is a set of SQL statements that automatically executes in response to certain events on a table, such as INSERT, UPDATE, or DELETE.

Also remember that triggers don’t run manually. They run automatically when a specific action happens.

Triggers are usually defined based on timing:

• BEFORE trigger - executes before the operation is performed, often used for validation
• AFTER trigger - executes after the operation, commonly used for logging or maintaining related data

They are commonly used for enforcing business rules, maintaining derived or related data, and auditing changes in tables.

However, one thing to keep in mind is that triggers can sometimes make the system harder to debug, because they run automatically and may not be immediately visible in the flow of execution.

Also, if multiple triggers are chained together, it can lead to unexpected behavior.

A schema in DBMS defines the structure of a database, including tables, columns, data types, relationships, and constraints. It acts like a blueprint that shows how the data is organized, while the actual data stored is called the instance, which can change over time.

Also, remember: Schema is defined and modified using DDL commands like CREATE, ALTER, and DROP.

SQL subquery is nothing but a query ritten inside another SQL query. It is mainly used when we want to use the result of one query within another, or simplify complex conditions. Subqueries can be used in clauses like SELECT, WHERE, or FROM.

Now, a subquery can return either a single value or multiple rows.

A scalar subquery returns a single value and is often used in conditions.

For example, if we want to find orders that are greater than the average order value:

SELECT order_id
FROM Orders
WHERE amount > (SELECT AVG(amount) FROM Orders);

So after running this query, the inner query gives one value, and the outer query compares against it.

Subqueries can also return multiple rows and are used with operators like IN or EXISTS, depending on the condition.

You can mention this much for subqueries; now, coming to the correlated subqueries.

A correlated subquery depends on the outer query and is executed once for every row. Because of this, it is generally slower than a normal subquery. So instead of running once, it keeps running again and again for each row being processed.

In SQL, there are aggregate functions such as COUNT(), SUM(), AVG(), MAX(), and MIN(). They are used to perform various calculations.

Now, when you are actually working with these, it would become tedious to apply all of them again and again, right? And for that reason, GROUP BY is used.

Remember: GROUP BY is used to group rows based on a column, so that aggregate functions can be applied to each group separately.

Now, if we take an example, let’s say there is an Orders table and we want to find how many orders each customer has placed, we can group the data by customer_id and then apply COUNT.

It should look like this -

SELECT customer_id, COUNT(*) 
FROM Orders
GROUP BY customer_id;

Now, the question asks both about GROUP BY and HAVING. Since you are familiar with GROUP BY now, you will have to understand HAVING in comparison with WHERE because these both can be confusing when put together.

So, talking about the clear difference:

• The WHERE clause filters rows before grouping happens
• The HAVING clause filters data after grouping is done

So, as an answer, you can say that WHERE works on individual rows and HAVING works on grouped or aggregated results.

For example, if we want to find customers who have placed more than 5 orders, we cannot use WHERE with COUNT, because the grouping has not happened yet.

Instead, we use HAVING in this case like this:

SELECT customer_id, COUNT(*) 
FROM Orders
GROUP BY customer_id
HAVING COUNT(*) > 5;

If we try to write WHERE COUNT(*) > 5, it won’t work, because at that stage SQL is still dealing with individual rows and not the grouped data.

The best way to understand this is to keep the execution order in mind which is:

FROM/JOIN > WHERE > GROUP BY> HAVING > SELECT > ORDER BY > LIMIT/OFFSET

Through this order you can see how WHERE is placed before GROUP BY and because of that it won;t be able to use the aggregate functions, and HAVING comes after grouping and can use them.

One mistake I’ve seen happening quite often is using HAVING even when the condition can be applied using WHERE. So what happens here is that this makes the query less efficient, because unnecessary rows are grouped first and filtered later.

You may come across this question in interviews, and that is why I will explain the definition along with the types in the case of SQL JOINs.

So, SQL JOINs are used to combine data from multiple tables, essentially 2 or more from the related columns. Now, imagine you have 2 tables for customers and orders. If you want to know how many customers have placed the order, or any data that correlates with one another, you can use JOINs to do so.

Just like that, there are different types of JOINs, such as INNER JOIN, LEFT JOIN, RIGHT JOIN, FULL OUTER JOIN, CROSS JOIN, and SELF JOIN.

I’ll explain each briefly -

1. INNER JOIN: You can use this when you only want the rows that match in the selected columns, so, taking the example from above, if the customer has placed the order, then they will appear, and the ones who haven’t, won’t.

2. LEFT JOIN/RIGHT JOIN: This one is used all the time! It basically helps to return all the records from the left table, and only the matching ones from the right table. If no match is found, the value from the right side is shown as NULL. And if you want the opposite, use RIGHT JOIN.

3. FULL OUTER JOIN: If you want all the records from the selected tables, then you can use this one. Again, taking the example from above, in this case, customers without orders and even orders without customers will appear.

4. CROSS JOIN: Want all POSSIBLE combinations of rows from each table? CROSS JOIN will help to do so, so if there are 3 customers and 2 orders, the result will have 6 rows. This is called a Cartesian product and is generally used only in specific cases.

5. SELF JOIN: This will help you join the table with itself. It’s basically used to see the internal relationship within the table. For eg, if you have a student table, and under that you have student_id, mentor_id, name, etc. Hence, by joining the table with itself, we can figure out the relationship between the mentors and students.

This is all for SQL JOINs for now. You can read more at SQL JOINS - Scaler Topics