Problem You need to retrieve a list of dates between two specified dates. Input First, let’s create a table and insert some sample data into it. startDate endDate 2023-01-01 2023-01-05 2023-02-01 2023-02-03 For this example, the table contains two date ranges: Jan 1 to Jan 5 and Feb 1 to Feb 3 of 2023. Try …
How to get a list of dates between two dates in SQL? Read More »
The difference between CROSS JOIN and INNER JOIN in SQL The question here is about the difference between a CROSS JOIN and an INNER JOIN in SQL. While both can be used to combine rows from two or more tables, they do so in distinct ways: CROSS JOIN: Combines each row of the first table …
What is the difference between CROSS JOIN and INNER JOIN in SQL? Read More »
Problem You have two tables, tableA and tableB. You want to retrieve all records from tableA that do not have a matching record in tableB based on a specific column. Input Let’s start with creating two tables and populating them with data. tableA – This will be our primary table where we’ll select data from. …
How to get records from one table that does not exist in another? Read More »
Problem How to access the “previous row” value in a SELECT statement in SQL? Input id sale_date units_sold 1 2023-01-01 10 2 2023-01-02 15 3 2023-01-03 12 4 2023-01-04 20 Try Hands-On: HERE Source Tables: Gist Desired Solution sale_date current_day_sales previous_day_sales 2023-01-01 10 2023-01-02 15 10 2023-01-03 12 15 2023-01-04 20 12 Solution 1: To …
How to access the “previous row” value in a SELECT statement in SQL? Read More »
Problem You have a database table, how to select all columns except specific columns mentioned by the user? Example Syntax: SELECT * [except columnA] FROM tabA; Input id first_name last_name email salary 1 John Doe [email protected] 50000 2 Jane Smith [email protected] 60000 3 Bob Brown [email protected] 55000 This is not possbile in native SQL databases. …
How to exclude a column using select * except column? Read More »
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 Let’s understand the difference between UNION and UNION ALL in SQL with an example. Input Let’s create a table named fruits with columns id, name, and type. We will insert some rows into this table: id name type 1 Apple Fresh 2 Banana Fresh 3 Apple Dried 4 Banana Dried 5 Cherry Fresh Try …
What is the difference between UNION and UNION ALL in SQL? Read More »
Problem Let’s see how to find duplicate values in an SQL table. Input id name enrollment_number 1 Alice ENR123 2 Bob ENR124 3 Charlie ENR125 4 David ENR123 5 Edward ENR126 6 Frank ENR124 Try Hands-On: Fiddle Create Source tables: Code Gist Desired Output enrollment_number count ENR123 2 ENR124 2 Solution 1: Using GROUP BY …
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 What is the difference between INNER JOIN, OUTER JOIN and FULL OUTER JOIN? Input Let’s create three example tables, employees, departments, and projects, and insert some sample data into them. SELECT * FROM EMPLOYEES; employee_id first_name last_name department_id 1 John Doe 1 2 Jane Smith 2 3 Bob Johnson 1 4 Alice Williams SELECT …
What is the difference between INNER JOIN, OUTER JOIN and FULL OUTER JOIN? Read More »
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 How to randomly select rows quickly from a large table in MySQL? Input employee_id first_name last_name department salary 1 John Doe HR 50000.00 2 Jane Smith Engineering 60000.00 3 Alice Johnson Finance 55000.00 4 Bob Brown Sales 52000.00 5 Eva Williams Marketing 48000.00 6 Chris Davis Engineering 62000.00 7 Sarah Wilson Finance 56000.00 8 …
How to randomly select rows from a large table in MySQL super fast? Read More »
Problem Given a table, how to efficiently convert rows to columns? So that, the records in a given row become column names and the values of the respective columns are taken from another column from the original table? Input employee_id skill_name skill_level 1 Java 4 1 SQL 3 1 Python 5 2 Java 3 2 …
How to efficiently convert rows to columns in SQL? Read More »
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 »
