Problem Often when dealing with database records, you might encounter a situation where you want to group records based on a specific column but rather than aggregating the other columns using functions like SUM() or AVG(), you want to concatenate their strings. MySQL has the GROUP_CONCAT() function for this purpose. Input ID Class Name 1 …
How to use GROUP BY to concatenate strings in MySQL and SQL Server? Read More »
Problem You have a table with a column where some rows contain NULL values or empty strings. You want to retrieve rows where this column has a value (i.e., not NULL and not an empty string). Input product_id product_name product_description 1 Laptop High performance gaming laptop 2 Mouse 3 Keyboard 4 Monitor 27 inch 4K …
How to return results only if value exists in SQL? Read More »
Problem You have a table with multiple records. For each distinct value in one of the columns (i.e., each ‘group by’ group), you want to retrieve the top ‘n’ rows based on some criteria. In the below example, get the top 2 entries for each sales person based on ‘amount’ column. Input id salesperson amount …
How to get top n results in each ‘group by’ group in SQL? Read More »
Problem You have a table with multiple rows of data for each unique identifier and you want to concatenate the values of these rows into a single field for each identifier. Input user_id interest 1 Reading 1 Writing 2 Painting 2 Singing 3 Traveling Try Hands-On: Fiddle Create Table: Gist Desired Output user_id concatenated_interests 1 …
How to concatenate multiple rows into one field in MySQL? Read More »
Problem How to escape a single quote in SQL Server, with a solution that is also reproducible in MySQL? For example, let’s try to replace the single quote character with a hat (^) character. Input ID Name 1 John’s Pizza 2 Mary’s Cafe 3 Alice’s Diner Try Hands-On: Fiddle Create Input Table: Gist Desired Output …
Problem How to select a random row using SQL? Input employee_id first_name last_name salary 1 John Doe 50000 2 Jane Smith 60000 3 Michael Johnson 55000 4 Emily Brown 58000 5 David Davis 62000 Try Hands-On: Fiddle Create Input Table: Gist Desired Output employee_id first_name last_name salary 5 David Davis 62000 Solution 1: Using generated …
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 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 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 »
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 »
