SQL Intermediate-Level Interview Questions – Day 9

SQL Intermediate-Level Interview Questions
Topics to be covered at an intermediate level

1. Joins: Understanding various types of joins like INNER JOIN, LEFT JOIN, RIGHT JOIN, and FULL JOIN, as well as their differences and use cases.
2. Subqueries: Grasping the concept of subqueries, their types, and when to use them to solve complex problems.
3. Aggregation: Using aggregation functions such as COUNT, SUM, MIN, MAX, and AVG with GROUP BY to perform calculations on groups of data.
4. Indexes: Understanding what indexes are, their types (e.g., clustered and non-clustered), and how they can improve the performance of database operations.
5. Normalization: Understanding the concept of normalization and its various forms (e.g., 1NF, 2NF, 3NF) to eliminate data redundancy and improve data integrity.
6. Transactions: Understanding the basics of transactions, their properties (ACID), and how to ensure data integrity and consistency within a database.
7. Constraints: Learning about constraints such as PRIMARY KEY, FOREIGN KEY, UNIQUE, and CHECK to enforce data integrity rules and maintain consistency.
8. Views: Understanding the creation and use of views to simplify complex queries and provide an additional layer of security.
9. Stored Procedures: Understanding the concept of stored procedures, their creation, and how to use them to execute sets of SQL statements.
10. Triggers: Understanding the purpose of triggers, their types, and how to use them to automatically perform actions when certain database events occur.
11. Advanced SQL functions: Familiarizing yourself with advanced SQL functions such as CASE, COALESCE, NULLIF, and DATE functions for handling complex data manipulations and transformations.
12. Data Integrity: Understanding the concept of data integrity and how to maintain it using various SQL techniques such as constraints and normalization.

SQL Intermediate-Level Interview Questions

Joins are important

INNER JOIN: Returns all rows when there is at least one match in both tables.

LEFT JOIN: Returns all rows from the left table, and the matched rows from the right table. The result is NULL from the right side if there is no match.

RIGHT JOIN: Returns all rows from the right table, and the matched rows from the left table. The result is NULL from the left side when there is no match.

FULL JOIN: Returns all rows when there is a match in one of the tables. If there is no match, NULL values are used.

Left Join vs Left Outer Join

In SQL, there is no actual difference between LEFT JOIN and LEFT OUTER JOIN. Both LEFT JOIN and LEFT OUTER JOIN are the same, and they both return all records from the left table (table1), and the matched records from the right table (table2). If there are no matches, NULL values are returned for the columns of the right table.

The usage of LEFT JOIN and LEFT OUTER JOIN is interchangeable, and both syntaxes are widely accepted in SQL implementations. The same holds for RIGHT JOIN and RIGHT OUTER JOIN. Similarly, FULL JOIN is the same as FULL OUTER JOIN.

Here is an example of using a LEFT JOIN and LEFT OUTER JOIN:

Both queries will produce the same result set, which includes all records from Table1 and matching records from Table2 based on the condition specified in the ON clause.

Subquery in SQL

Consider a scenario where you want to find all employees whose salary is above the average salary of their respective departments. You can achieve this with a subquery as follows:

In this query, the subquery calculates the average salary for each department and compares it to the salary of each employee in that department. The main query then retrieves all the employees whose salary is greater than the average salary of their respective departments. This type of subquery involves nesting and referencing the outer query in the subquery. Understanding and utilizing complex subqueries can be crucial when dealing with sophisticated data retrieval or analysis requirements.

Difference between HAVING and WHERE

WHERE clause: Filters rows before any groups are created.
HAVING clause: Filters rows after the grouping is performed.

UNION vs UNION ALL

• UNION: Combines the result sets of two or more SELECT statements and removes duplicate rows.
• UNION ALL: Combines the result sets of two or more SELECT statements without removing duplicate rows.

Indexes in SQL

Indexes are data structures that improve the speed of data retrieval operations on a database table. They are used to quickly locate data without having to search every row in a database table. Indexes should be used on columns that are frequently used in WHERE clauses or joins.

Clustered vs Non-Clustered Indexes

• Clustered Index: A clustered index defines the order in which data is physically stored in a table. There can be only one clustered index per table.
• Non-Clustered Index: A non-clustered index does not sort the physical data inside the table. In fact, a non-clustered index is stored at one place and table data is stored in another place.

Self-Join in SQL

A self-join is a join that is used to join a table with itself. It is particularly useful when dealing with hierarchical data or comparing rows within the same table. For example:

Stored Procedures in SQL

A stored procedure is a prepared SQL code that you can save, so the code can be reused over and over again. They are beneficial for improving performance and security, as well as for managing complex operations. They are particularly useful when you have to perform a series of operations in the database, as you can simply call the stored procedure instead of writing the code again

Our services

1. YouTube channel covering all the interview-related important topics in SQL, Python, MS Excel, Machine Learning Algorithm, Statistics, and Direct Interview Questions
   Link – The Data Monk Youtube Channel
   
2. Website – ~2000 completed solved Interview questions in SQL, Python, ML, and Case Study
   Link – The Data Monk website
   
3. E-book shop – We have 70+ e-books available on our website and 3 bundles covering 2000+ solved interview questions
   Link – The Data E-shop Page
   
4. Instagram Page – It covers only Most asked Questions and concepts (100+ posts)
   Link – The Data Monk Instagram page
   
5. Mock Interviews
   Book a slot on Top Mate
   
6. Career Guidance/Mentorship
   Book a slot on Top Mate
   
7. Resume-making and review
   Book a slot on Top Mate 

The Data Monk e-books

We know that each domain requires a different type of preparation, so we have divided our books in the same way:

✅ Data Analyst and Product Analyst -> 1100+ Most Asked Interview Questions

✅ Business Analyst -> 1250+ Most Asked Interview Questions

✅ Data Scientist and Machine Learning Engineer -> 23 e-books covering all the ML Algorithms Interview Questions

✅ Full Stack Analytics Professional – 2200 Most Asked Interview Questions

The Data Monk – 30 Days Mentorship program

We are a group of 30+ people with ~8 years of Analytics experience in product-based companies. We take interviews on a daily basis for our organization and we very well know what is asked in the interviews.
Other skill enhancer websites charge 2lakh+ GST for courses ranging from 10 to 15 months.

We only focus on making you a clear interview with ease. We have released our Become a Full Stack Analytics Professional for anyone in 2nd year of graduation to 8-10 YOE. This book contains 23 topics and each topic is divided into 50/100/200/250 questions and answers. Pick the book and read
it thrice, learn it, and appear in the interview.

We also have a complete Analytics interview package
– 2200 questions ebook (Rs.1999) + 23 ebook bundle for Data Science and Analyst role (Rs.1999)
– 4 one-hour mock interviews, every Saturday (top mate – Rs.1000 per interview)
– 4 career guidance sessions, 30 mins each on every Sunday (top mate – Rs.500 per session)
– Resume review and improvement (Top mate – Rs.500 per review)

Total cost – Rs.10500
Discounted price – Rs. 9000

How to avail of this offer?
Send a mail to [email protected]