Problem Given a table in MySQL, how do you remove duplicate rows? Input first_name last_name email John Doe [email protected] Jane Smith [email protected] John Doe [email protected] Bob Johnson [email protected] Alice Williams [email protected] Jane Smith [email protected] Try Hands-On: Fiddle Create Input Table: Gist Desired Output first_name last_name email John Doe [email protected] Jane Smith [email protected] Bob Johnson [email protected] …
Problem How to split values to multiple rows in SQL? Input To illustrate this problem, let’s create a sample input table named “Sales” with some data. The table will have two columns: “Date” and “Amount.” id vals 1 value1,value2,value3 2 value4,value5 3 value6 Try Hands-On: Fiddle Create Input Table: Gist Desired Output id split_value 1 …
Problem You need to calculate a running total in SQL Server and provide a solution that is also reproducible in MySQL. Input To illustrate this problem, let’s create a sample input table named “Sales” with some data. The table will have two columns: “Date” and “Amount.” Date Amount 2023-09-01 100.00 2023-09-02 75.50 2023-09-03 50.25 2023-09-04 …
Problem You need to calculate a running total in SQL Server and provide a solution that is also reproducible in MySQL. Input employee_id first_name last_name salary 1 John Doe 50000.00 2 Jane Smith 60000.00 3 Bob Johnson 55000.00 Try Hands-On: Fiddle Desired Output On inserting a new row, if the entry already exists, it should …
How to insert into a table or update if exists in MySQL? Read More »
Problem How to do a FULL OUTER JOIN in MySQL? Let’s create two example tables, employees and departments, and insert some data into them. Input select * from employees; employee_id employee_name department_id 1 John 1 2 Jane 2 3 Bob 4 Alice 1 select * from departments; department_id department_name 1 HR 2 Finance 3 Marketing …
Problem You want to find the rows that have the maximum value for a specific column within each group in another column. For example, you have a table with data about products and their prices, and you want to find the most expensive product in each category. Input product_id product_name category_id price 1 Product A …
Problem You need to concatenate text from multiple rows into a single text string, for every group present in another column using SQL. For example, from the employee_comments table, concatenate all comments from a given employee in one row. See the input and the desired output below. Input employee_id comment_text 1 John is a hard …
How to concatenate text from multiple rows into a single text string in MySQL? Read More »
Problem How to switch rows and columns in SQL so that the rows and columns are interchanged? Given a table named Students with columns Name, Math, and Science representing student names and their scores in Math and Science. We want to transpose this table such that subjects become rows and student names become columns with …
Problem You have a table with multiple rows of data for each group, and you want to select the last row from each group. From the orders table below, select the first row in each group. Input order_id customer_id order_date total_amount 1 1 2023-09-01 100.50 2 2 2023-09-02 75.20 3 1 2023-09-03 50.75 4 3 …
How to retrieve the last record in each group in SQL? Read More »
Problem From the test_scores table below, select the top 1 row in each group. For example, you have a table with data about ‘students’ test scores, and you want to find the highest-scoring student in each subject. Input student_id subject score 1 Math 90 2 Math 85 3 Math 95 1 Science 88 2 Science …
Problem You have a table with multiple rows of data for each group, and you want to select the first row from each group. From the orders table below, select the first row in each group. Input order_id customer_id order_date product_name quantity 1 1 2023-01-01 Laptop 1 2 1 2023-01-02 Mouse 2 3 2 2023-01-03 …
How to select first row in each ‘Group By’ group? Read More »
Problem How to transpose columns to rows in SQL? This essentially requires reshaping the table in such a way that certain columns of a table are moved to rows. Input ProductID Attribute1 Attribute2 Attribute3 1 Red Large Cotton 2 Blue Medium Silk 3 Green Small Wool Try Hands-Om: Fiddle Create Input Table: Gist Desired Output …
Problem How to select only rows with max value on a column for each distinct value in another categorical column in MySQL? From Items table, select rows with max value on the Price column for each distinct value in the Category column. Input id Category Price 1 Fruit 2.50 2 Fruit 3.00 3 Veg 1.00 …
How to select only rows with max value on a column? Read More »
SQL window functions exercises is designed to challenge your SQL muscle and help internalize data wrangling using window functions in SQL. The questions are designed like fun puzzles, take your time and try to solve. By the end of this set, you should feel confident in solving the hardest SQL Window function problems. If you …
SQL Window Function Exercises and Solutions – Set 2 Read More »
SQL window functions is considered a ‘hard’ concept in SQL. This set of exercises is designed to challenge your SQL muscle and help internalize data wrangling using window functions in SQL. The questions are of three levels of difficulty: Easy, Intermediate and Hard. If you haven’t read the tutorial yet, read SQL Window Functions – …
SQL Window Functions Exercises – Practice to perfection Read More »
SQL window functions is one of the advanced concepts, understanding which will give you the ability to do complex data wrangling and transformations in SQL. In this guide, we will intuitively understand how window functions work in a way you will never forget. Don’t memorize anything, just read through and you will clearly understand how …
SQL Window Functions – Made simple and intuitive Read More »
SQL Subquery, also known as inner query or nested query, are used to query data from one or more tables and then used in another SQL statement. They are an integral part of SQL since they allow complex database queries to be handled in a simpler way. A subquery can be used in the SELECT, …
SQL Subquery – How to write subqueries using simple examples Read More »
SQL, short for Structured Query Language is a programming language used to communicate with databases and do various types of Data wrangling operations. This is an essential skillset for any type of Data related jobs. In this tutorial, let’s get started with the basics of SQL. 1. Why you should learn SQL? First, let’s understand …
SQL Tutorial – A Simple and Intuitive Guide to the Structured Query Language Read More »
Cook’s distance is a measure computed to measure the influence exerted by each observation on the trained model. It is measured by building a regression model and therefore is impacted only by the X variables included in the model. What is Cooks Distance? Cook’s distance measures the influence exerted by each data point (row / …
Cook’s Distance for Detecting Influential Observations Read More »
Z score, also called as standard score, is used to scale the features in a dataset for machine learning model training. It can also be used to detect outliers. In this one, we will first see how to compute Z-scores and then use it to detect outliers. How is Z-score used in machine learning? Now, …
