[HackerRank/SQL] Advanced Select

---

Type of Triangle

Problem

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 sides of equal length.

• Isosceles: It's a triangle with sides of equal length.

• Scalene: It's a triangle with sides of differing lengths.

• Not A Triangle: The given values of A, B, and C don't form a triangle.

Input Format

Sample Input

Sample Output

Isosceles
Equilateral
Scalene
Not A Triangle

Explanation

solution

3변의 길이가 같다면, Euilateral
2변의 길이가 같다면, Isosceles
3변의 길이가 모두 다르다면,Scalene
3변의 길이로 삼각형이 형성되지 않는경우,
Not A Triangle

SELECT 
CASE 
	WHEN A = B AND B = C THEN 'Equilateral'
	WHEN A+B <= C OR A+C <= B OR B+C <= A THEN 'Not A Triangle'
	WHEN A = B OR B = C OR A = C THEN 'Isosceles'
	ELSE 'Scalene' 
    END
FROM TRIANGLES 

extra solution

SELECT IF(A+B>C AND A+C>B AND B+C>A, IF(A=B AND B=C, 'Equilateral', IF(A=B OR B=C OR A=C, 'Isosceles', 'Scalene')), 'Not A Triangle') 
FROM TRIANGLES;

The PADS

Problem

Generate the following two result sets:

1.Query an alphabetically ordered list of all names in OCCUPATIONS, immediately followed by the first letter of each profession as a parenthetical (i.e.: enclosed in parentheses). For example: AnActorName(A), ADoctorName(D), AProfessorName(P), and ASingerName(S).

2.Query the number of ocurrences of each occupation in OCCUPATIONS. Sort the occurrences in ascending order, and output them in the following format:

There are a total of [occupation_count] [occupation]s.

where [occupation_count] is the number of occurrences of an occupation in OCCUPATIONS and [occupation] is the lowercase occupation name. If more than one Occupation has the same [occupation_count], they should be ordered alphabetically.

Note: There will be at least two entries in the table for each type of occupation.

solution

1번

-occupation테이블의 모든 이름을 알파벳 순서로 정렬하고 각 occupation의 첫 글자를 (A)처럼 표기

-정렬순서는 name과 occupation의 첫글자순으로

2번

-occupation테이블에서 직업과 직업의 수를 오름차순으로 출력

-직업은 소문자로 출력하기

-정렬은 count(직업수),한개 이상의 직업수가 같을 경우 알파벳순 정렬

SELECT CONCAT(name, '(', SUBSTR(occupation, 1,1), ')')
FROM Occupations
ORDER BY name, SUBSTR(occupation,1,1);

SELECT CONCAT('There are a total of ', count(occupation), ' ', LOWER(occupation), 's.')
FROM Occupations
GROUP BY occupation
ORDER BY count(occupation), occupation

=>문자열을 합치는 CONCAT사용
=>occupation별 count집계를 위해,GROUP BY사용

Binary Tree Nodes

Problem

You are given a table, BST, containing two columns: N and P, where N represents the value of a node in Binary Tree, and P is the parent of N.

Write a query to find the node type of Binary Tree ordered by the value of the node. Output one of the following for each node:

• Root: If node is root node.

• Leaf: If node is leaf node.

• Inner: If node is neither root nor leaf node.

sample Input

sample Output

1 Leaf
2 Inner
3 Leaf
5 Root
6 Leaf
8 Inner
9 Leaf

solution

N은 노드값,P는 N의 상위항목
Root=>최상위 노드
Leaf=>최하위 노드
Inner=>root도 leaf도 아닌 경우

P가 null일경우, N은 ROOT
P 리스트안에 N이 없을경우, Leaf
나머지는 Inner

SELECT N,CASE WHEN P IS NULL THEN 'Root'
              WHEN N NOT IN (SELECT DISTINCT P FROM BST WHERE P IS NOT NULL) THEN 'Leaf'
              ELSE 'Inner' END
FROM BST
ORDER BY N

extra solution

SELECT N,IF (P IS NULL,'Root',IF((SELECT COUNT(*) FROM BST WHERE P=B.N)>0,'Inner','Leaf'))
FROM BST AS B
ORDER BY N

New Companies

Problem

Amber's conglomerate corporation just acquired some new companies. Each of the companies follows this hierarchy:

Given the table schemas below, write a query to print the company_code, founder name, total number of lead managers, total number of senior managers, total number of managers, and total number of employees. Order your output by ascending company_code.

Note:

• The tables may contain duplicate records.

• The company_code is string, so the sorting should not be numeric. For example, if the company_codes are C_1, C_2, and C_10, then the ascending company_codes will be C_1, C_10, and C_2.

solution

select c.company_code, c.founder,
       count(distinct l.lead_manager_code),
       count(distinct s.senior_manager_code),
       count(distinct m.manager_code),
       count(distinct e.employee_code)
from Company as c 
join Lead_Manager as l 
on c.company_code = l.company_code
join Senior_Manager as s
on l.lead_manager_code = s.lead_manager_code
join Manager as m 
on m.senior_manager_code = s.senior_manager_code
join Employee as e
on e.manager_code = m.manager_code
group by c.company_code, c.founder
order by c.company_code;