Drop a Query

# SQL

## 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 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 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 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 get a list of dates between two dates in SQL?

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 …

## What is the difference between CROSS JOIN and INNER JOIN in SQL?

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 …

## 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 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 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 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 …

## What is the difference between UNION and UNION ALL in SQL?

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 …

## How to find duplicate values in SQL Table?

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 …

## How to return results only if value exists in SQL?

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 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 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 escape a single quote in SQL Server?

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 …

## 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 …

## How to select a random row using SQL?

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 …

## 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 efficiently convert rows to columns in SQL?

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 …

Course Preview