Once you understand the basics of SQL, the next step is to learn intermediate-level concepts. These are the types of questions you might face in a job interview when applying for roles related to data analysis, backend development, or database management. These concepts go deeper into how databases are built, how data is retrieved efficiently, and how multiple queries are used together to solve real-world problems.
Let’s break down these topics in a simple way so you can build your confidence and prepare better for interviews or projects.
How to Find the Second Highest Salary Using SQL
To find the second highest salary in a table, you can use a subquery that filters out the highest salary and then returns the next one. For example, if your table is called employees, you can write a query that selects the maximum salary that is less than the highest one. This allows you to skip the top salary and get the second highest.
Another method is to use the LIMIT or OFFSET feature depending on the SQL version you are using. These approaches help sort the data in descending order and skip the first row to get the second.
Difference Between INNER JOIN and LEFT JOIN
INNER JOIN returns only the rows that match in both tables. If there is no match, the row will not appear in the result. LEFT JOIN returns all rows from the left table, even if there is no match in the right table. If no matching row is found, the result will contain NULL for columns from the right table.
INNER JOIN is best when you want results with related data only. LEFT JOIN is useful when you want to keep all rows from one table and add related data where possible.
Understanding Normalization and Its Types
Normalization is the process of organizing data in a database to reduce duplication and improve data integrity. It divides large tables into smaller related ones and sets up rules to link them.
The common types of normal forms include First Normal Form where each column holds unique and atomic data, Second Normal Form where non-key columns are fully dependent on the primary key, and Third Normal Form where there is no indirect dependency on the primary key.
Normalization helps make data storage more efficient and reduces errors during updates or deletions.
What Denormalization Means in a Database
Denormalization is the opposite of normalization. It is the process of combining related tables into one for faster data reading. While normalization focuses on reducing duplication, denormalization accepts some duplication to improve performance for certain read-heavy operations.
It is often used in reporting or analytics where speed is more important than storage. Denormalization makes queries faster but may increase data redundancy and complexity in updating records.
What Indexes Are and the Types Used in SQL
Indexes in SQL are used to speed up data retrieval. They work like a table of contents, helping the database find rows faster without scanning the entire table. When a table gets large, indexes become important for performance.
There are several types of indexes. A primary index is automatically created when a primary key is set. A unique index makes sure all values in a column are different. A composite index includes more than one column. Full-text indexes are used for searching long text fields. Each type helps in a specific way to make queries faster and more efficient.
What Is a Subquery and Its Types
A subquery is a query inside another query. It helps break a big query into smaller parts. Subqueries can be placed in the SELECT, WHERE, or FROM clause.
There are two types of subqueries. A simple subquery returns a single value or list of values that are passed to the main query. A correlated subquery depends on the main query and runs once for every row checked by the outer query. Subqueries are helpful in complex filtering and analysis.
Understanding Correlated Subqueries
A correlated subquery uses values from the outer query. This means the inner query depends on the outer query to run. It is executed repeatedly for each row in the main query.
For example, if you are comparing each employee’s salary to the average salary of their department, you might use a correlated subquery. While it is powerful, it can be slower than normal subqueries because it runs many times during the process.
Difference Between UNION and UNION ALL
UNION is used to combine the result of two queries and removes duplicate rows. UNION ALL also combines two query results but keeps all rows including duplicates.
If you are sure there will be no duplicates or you want to keep them, use UNION ALL as it is faster. If you want clean unique results, then UNION is a better choice. Both queries require the same number of columns and similar data types in both result sets.
What a View Is and How It Differs from a Table
A view is like a virtual table. It is created using a SQL query and does not store data itself. Instead, it shows the data from one or more real tables based on the query that defines it.
Views are used for security, simplification, and consistency. For example, a view can show only selected columns to users while hiding sensitive data. A table is a physical structure where data is stored. A view is just a saved query that acts like a table but without holding any actual data.
How to Find Duplicate Records in a Table
To find duplicates in a table, you can use a GROUP BY clause with the HAVING keyword. This allows you to group rows based on one or more columns and find those that appear more than once.
For example, if you want to find duplicate email addresses, you can group by email and use HAVING COUNT greater than one. This shows all emails that are repeated in the table. Finding duplicates is helpful in cleaning data and improving data quality.
Build Confidence by Understanding the Concepts
These intermediate SQL questions test your understanding of how databases work behind the scenes. They focus on how well you can handle data relationships, optimize performance, and write powerful queries. By mastering these concepts, you will be ready for interviews and real projects where deeper SQL skills are needed.
Keep practicing with different data sets, write queries regularly, and explore how small changes affect results. With time and effort, you can move from beginner to expert and use SQL as a strong tool in your career.