Menu

CookBook

How to get records from one table that does not exist in another

How to get records from one table that does not exist in another?

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 »

How to access the _previous row_ value in a SELECT statement in SQL

How to access the “previous row” value in a SELECT statement in SQL?

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 »

How to exclude a column using select _ except column

How to exclude a column using select * except column?

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 »

How to use GROUP BY to concatenate strings in MySQL and SQL Server

How to use GROUP BY to concatenate strings in MySQL and SQL Server?

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 »

How to get top n results in each 'group by' group in SQL

How to get top n results in each ‘group by’ group in SQL?

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 »

How to concatenate multiple rows into one field in MySQL

How to concatenate multiple rows into one field in MySQL?

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 »

What is the difference between INNER JOIN, OUTER JOIN and FULL OUTER JOIN

What is the difference between INNER JOIN, OUTER JOIN and FULL OUTER JOIN?

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 »

How to randomly select rows from a large table in MySQL super fast

How to randomly select rows from a large table in MySQL super fast?

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 »

How to remove duplicate rows in MySQL

How to remove duplicate rows in MySQL?

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]

How to remove duplicate rows in MySQL? Read More »

How to split values to multiple rows in SQL

How to split values to multiple rows in SQL?

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 …

How to split values to multiple rows in SQL? Read More »

How to calculate running total in SQL Server

How to calculate running total in SQL Server?

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 …

How to calculate running total in SQL Server? Read More »

How to insert into a table or update if exists in MySQL

How to insert into a table or update if exists in MySQL?

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 »

Course Preview

Machine Learning A-Z™: Hands-On Python & R In Data Science

Free Sample Videos:

Machine Learning A-Z™: Hands-On Python & R In Data Science

Machine Learning A-Z™: Hands-On Python & R In Data Science

Machine Learning A-Z™: Hands-On Python & R In Data Science

Machine Learning A-Z™: Hands-On Python & R In Data Science

Machine Learning A-Z™: Hands-On Python & R In Data Science