Find all number pairs whose first number is smaller than the second one and the product of two numbers is larger than 11

Find all number pairs whose first number is smaller than the second one and the product of two numbers is larger than 11.

Output both numbers in the combination.

table 

In Relational database we store records of different category in different tables why? Because we cannot store whole data in a single table.

So, we create different table based to store same kind of data in a table. But tables can be connected through each other based on a field named as primary key.

For example, in a student table we have basic details of a student (roll_no,name,age etc.) but to store class details of a student we will create a different table named class (class_no,roll_no). Now, if we want to get details of class of students how can we get that? Very simple right? We need to join these two tables (student,class) based on column roll_no which is unique for each student.

You might be thinking why am I explaining you these simple join concept. Hold on your horses and let me explain you one more concept named self join after that you might get to know how this concept is useful to  solve this problem.

Self Join

When we join a table to itself it is termed as self join. For example, customer table we have following details (id,name,age,city).

Now, let's suppose we want to find the customers who belong to same city. What we are going to do ? Simple join customer table with itself based on city column and also make sure same customer won't be joined with itself.

SELECT A.name AS CustomerName1, B.name AS CustomerName2, A.city
FROM Customers as A 
join Customers as B
WHERE A.id != B.id
AND A.city = B.city 

Okay, Now I am assuming you might have understood the concept of self join very well. So, move on to our main question which is to find the number of pairs where 1st number should be less than to 2nd number and their multiplication should be greater than 11. Here, we have also only one table name transportation_numbers (index1,number). To find the pair of numbers we are going to apply self join based on number column.

select * from [dbo].[transportation_numbers] as t1
join transportation_numbers as t2 on t1.number = t2.number

But wait we didn't get what we are looking for right?


Because when we have applied the condition t1.number = t2.number on join it basically means it will get the records from both the tables where number1 will be equal to number2. But we need exactly opposite of it. Simply means from the above example number 5 from table1 should be paired with numbers (6,4,11,10). To get this we slightly need to change the condition from = to !=.

select  t1.number,t2.number  from [dbo].[transportation_numbers] as t1
join transportation_numbers as t2 on t1.number != t2.number 
order by t1.number,t2.number

Now apply one by one condition and get your result.

  1. Apply distinct to get the unique pair of numbers.
  2. Apply where condition t1.number < t2.number to select only those pairs where number1 is less than number2
  3. Apply where (t1.number * t2.number) >11 to select only those pairs whose multiplication is greater than 11.


Final Solution:

select distinct t1.number, t2.number from [dbo].[transportation_numbers] as t1
join transportation_numbers as t2 on t1.number!= t2.number 
where t1.number < t2.number and (t1.number * t2.number) >11

Output:


Wow you are great you have just solved one sql interview query. I am really happy for you. Looking forward to solve more such questions next time.


SQL Script:
USE [StraScratch]
GO
/****** Object:  Table [dbo].[transportation_numbers]    Script Date: 07-10-2024 15:39:09 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[transportation_numbers](
	[index1] [int] NULL,
	[number] [int] NULL
) ON [PRIMARY]
GO
INSERT [dbo].[transportation_numbers] ([index1], [number]) VALUES (1, 5)
GO
INSERT [dbo].[transportation_numbers] ([index1], [number]) VALUES (2, 3)
GO
INSERT [dbo].[transportation_numbers] ([index1], [number]) VALUES (3, 7)
GO
INSERT [dbo].[transportation_numbers] ([index1], [number]) VALUES (4, 1)
GO
INSERT [dbo].[transportation_numbers] ([index1], [number]) VALUES (5, 0)
GO
INSERT [dbo].[transportation_numbers] ([index1], [number]) VALUES (6, 8)
GO
INSERT [dbo].[transportation_numbers] ([index1], [number]) VALUES (7, 2)
GO
INSERT [dbo].[transportation_numbers] ([index1], [number]) VALUES (8, 4)
GO
INSERT [dbo].[transportation_numbers] ([index1], [number]) VALUES (9, 2)
GO

Comments (0)
Questions List