HackerRank: [SQL Advanced Select] (1/5) TYPE OF TRIANGLE | case when in SQL

HackerRank: [Advanced Select - 1/5] Type of Triangle |  CASE WHEN in SQL
I started studying SQL from a very famous site - HackerRank. Here I will try to provide multiple approaches & solutions to the same problem. It will help you learn and understand SQL in a better way.

Please make use of my blog posts for learning purpose only and feel free to ask your questions in the comment box below in case of any doubt.

Click Here for the previous blog-post in the series.


SQL Problem Statement:

Write a query identifying the type of each record in the TRIANGLES table using its three side lengths. Output one of the following statements for each record in the table:

  • Equilateral: It's a triangle with 3 sides of equal length.
  • Isosceles: It's a triangle with 2 sides of equal length.
  • Scalene: It's a triangle with 3 sides of differing lengths.
  • Not A Triangle: The given values of A, B, and C don't form a triangle.



Input Format:

The TRIANGLES table is described as follows:

TRIANGLES Columns

Each row in the table denotes the lengths of each of a triangle's three sides.

Sample Input:
TRIANGLES Sample Data

Sample Output:
Isosceles 
Equilateral
Scalene
Not A Triangle

Explanation:
  • Values in the tuple (20,20,23) form an Isosceles triangle, because A=B.
  • Values in the tuple (20,20,20) form an Equilateral triangle, because A=B=C. 
  • Values in the tuple (20,21,22) form a Scalene triangle, because A!=B!=C.
  • Values in the tuple (13,14,30) cannot form a triangle because the combined value of sides A and B is not larger than that of side C.

Solution-1: Using 'AND', 'OR' Clauses(MySQL Query):

SELECT
CASE
WHEN (A + B <= C)
OR (B + C <= A)
OR (A + C <= B) THEN 'Not A Triangle'
WHEN (A = B)
AND (B = C) THEN 'Equilateral'
WHEN ((A = B) &(A != C))
OR ((B = C) &(B != A))
OR ((A = C) &(A != B)) THEN 'Isosceles'
WHEN (A != B)
AND (B != C)
AND (A != C) THEN 'Scalene'
END AS Triangle_Type
FROM
TRIANGLES;

NOTE: 
  1. Here, the Sequence of When conditions are very important. If you move down the When condition corresponding to 'Not A Trangle' at the bottom, the query will not work as expected.



Solution-2: Using '&', '|' Operators (MySQL Query):

SELECT
CASE
WHEN (A + B <= C) | (B + C <= A) | (A + C <= B) THEN 'Not A Triangle'
WHEN (A = B) & (B = C) THEN 'Equilateral'
WHEN ((A = B) & (A != C)) | ((B = C) & (B != A)) | ((A = C) & (A != B)) THEN 'Isosceles'
WHEN (A != B) & (B != C) & (A != C) THEN 'Scalene'
END AS Triangle_Type
FROM
TRIANGLES;

NOTE: 
  1. Here, the Sequence of When conditions are very important. If you move down the When condition corresponding to 'Not A Trangle' at the bottom, the query will not work as expected.

Sample Output:

Equilateral
Equilateral
Isosceles
Equilateral
Isosceles
Equilateral
Scalene
Not A Triangle
Scalene
Scalene
Scalene
Not A Triangle
Not A Triangle



--------------------------------------------------------------------------------
Click here to see solutions for all Machine Learning Coursera Assignments.
&
Click here to see more codes for Raspberry Pi 3 and similar Family.
&
Click here to see more codes for NodeMCU ESP8266 and similar Family.
&
Click here to see more codes for Arduino Mega (ATMega 2560) and similar Family.
Feel free to ask doubts in the comment section. I will try my best to answer it.
If you find this helpful by any mean like, comment and share the post.
This is the simplest way to encourage me to keep doing such work.

Thanks & Regards,
-Akshay P Daga
إرسال تعليق (0)
أحدث أقدم