Menu

SQL

How to sort multiple columns in SQL and in different directions

How to sort multiple columns in SQL and in different directions?

Problem How to sort multiple columns in SQL and in different directions? Input Let’s create a table named Employees with columns: id, first_name, last_name, and salary. CREATE TABLE Employees ( id INT PRIMARY KEY AUTO_INCREMENT, first_name VARCHAR(100), last_name VARCHAR(100), salary DECIMAL(10, 2) ); — Insert data into the table INSERT INTO Employees (first_name, last_name, salary) …

How to sort multiple columns in SQL and in different directions? Read More »

How to count the number of work days between two dates

How to count the number of work days between two dates?

Problem To count the number of work days between two dates, taking into account weekends (Saturdays and Sundays) and assuming no holidays. Input We will create a table named DateRange with two columns: StartDate and EndDate. Here’s an example input: CREATE TABLE DateRange ( StartDate DATE, EndDate DATE ); INSERT INTO DateRange(StartDate, EndDate) VALUES (‘2023-10-01’, …

How to count the number of work days between two dates? Read More »

How to compute maximum of multiple columns, aks row wise max

How to compute maximum of multiple columns, aks row wise max?

Problem Given a table with multiple columns, compute the maximum value for each row across the specified columns. Input — Create an example table CREATE TABLE ExampleTable ( ID INT AUTO_INCREMENT PRIMARY KEY, ColumnA INT, ColumnB INT, ColumnC INT ); — Insert data into the table INSERT INTO ExampleTable (ColumnA, ColumnB, ColumnC) VALUES (10, 20, …

How to compute maximum of multiple columns, aks row wise max? Read More »

How to use the GROUP BY clause on multiple columns in SQL

How to use the GROUP BY clause on multiple columns in SQL?

Problem: How to use the GROUP BY clause on multiple columns in SQL? Input: Let’s assume we have a table called Orders with columns OrderID, CustomerID, ProductID, and OrderDate. CREATE TABLE Orders ( OrderID INT PRIMARY KEY, CustomerID INT, ProductID INT, OrderDate DATE ); — Inserting sample data INSERT INTO Orders (OrderID, CustomerID, ProductID, OrderDate) …

How to use the GROUP BY clause on multiple columns in SQL? Read More »

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 »

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