Brief Summary
This video presents 10 common SQL queries often asked during interviews. The author provides solutions to each query, emphasizing the importance of understanding the underlying concepts rather than memorizing specific solutions. The video also highlights LearnSQL.com as a valuable resource for learning SQL, offering a free SQL competency certificate.
- The video covers 10 SQL queries, providing solutions and explanations for each.
- LearnSQL.com is recommended as a platform for learning SQL, with a free SQL competency certificate available.
Intro
This video is sponsored by LearnSQL.com and aims to share 10 common SQL queries asked during interviews. The author emphasizes that understanding the concepts behind these queries is crucial for success in SQL interviews. The solutions and datasets for all queries are available on the author's blog, linked in the video description.
Query 1
This query focuses on deleting duplicate data from a table. The author provides three different solutions using:
- Group by: Grouping data based on specific columns and selecting the minimum value of a unique identifier.
- Not in: Using a subquery to fetch duplicate records and deleting them.
- Window function: Using the
row_numberwindow function to assign unique row numbers and deleting records with duplicate row numbers.
Query 2
This query aims to display the highest and lowest salaries for each department in an employee table. The solution involves using the max and min aggregate functions as window functions, partitioning the data based on the department. The author also explains the use of the frame clause in window functions.
LearnSQL
LearnSQL.com is presented as a platform for learning SQL. The author highlights the platform's high-quality content, practical exercises, and user-friendly interface. LearnSQL.com offers courses for various SQL learning levels, from basic concepts to advanced topics.
Get your Free SQL Competence Certificate
LearnSQL.com offers a free SQL competency certificate. Users can sign up for the platform and take the assessment. A score of 70% or higher earns the certificate. The test can be retaken after 30 days if unsuccessful.
Query 3
This query focuses on calculating the actual distance traveled by cars, given a table with cumulative distances. The solution uses the lag window function to access the cumulative distance from the previous day and subtract it from the current day's cumulative distance.
Query 4
This query aims to eliminate redundant data from a table showing distances between cities. The solution involves using a self-join to compare records and filter out duplicates based on row numbers assigned using the row_number window function.
Query 5
This query focuses on ungrouping data, transforming a table with aggregated counts into individual rows. The solution uses recursive SQL functionalities, employing a base query to fetch the initial data and a recursive query to iterate through the data, decrementing the count until it reaches zero.
Query 6
This query involves generating IPL match schedules, creating two queries: one where each team plays another team once and another where each team plays another team twice. The solution uses a self-join and filters the results based on row numbers assigned using the row_number window function.
Query 7
This query focuses on transforming row-level data into column-level data, similar to a pivot operation. The solution uses the crosstab function in PostgreSQL, which is similar to the pivot function in other databases. The author also mentions the use of the case statement as an alternative solution.
Query 8
This query aims to find the hierarchy of employees under a specific manager. The solution uses recursive SQL queries, starting with the manager's ID and recursively searching for employees with that manager ID in the manager_id column.
Query 9
This query focuses on finding the difference in average sales for each month between two years. The solution uses a self-join to compare average sales for each month in the two years, filtering the results based on the year and using the abs function to eliminate negative values.
Query 10
This query involves determining the final status of pizza orders based on specific rules. The solution uses four separate queries, each handling a different status rule. The queries use exists and not exists clauses to check for specific conditions within the data.
