Employee Salaries,Type of Traingle,The PADS,Revising Aggregations - (The Count Function,The Sum Function)
Employee Salaries📝
https://www.hackerrank.com/challenges/salary-of-employees/problem?isFullScreen=true
➡️ Employee 테이블,조건 : salary>2000 & month<10 -> 출력: name column,ORDER BY employee_id ASC
Write a query that prints a list of employee names (i.e.: the name attribute) for employees in Employee having a salary greater than per month who have been employees for less than months. Sort your result by ascending employee_id.
Input Format
The Employee table containing employee data for a company is described as follows:
where employee_id is an employee's ID number, name is their name, months is the total number of months they've been working for the company, and salary is the their monthly salary.
Sample Input
Sample Output
Angela
Michael
Todd
Joe
Explanation
Angela has been an employee for month and earns per month.
Michael has been an employee for months and earns per month.
Todd has been an employee for months and earns per month.
Joe has been an employee for months and earns per month.
We order our output by ascending employee_id.
SELECT name
FROM employee
WHERE salary>2000 AND months<10
ORDER BY employee_id;
Type of Traingle📝
https://www.hackerrank.com/challenges/what-type-of-triangle/problem?isFullScreen=true
➡️ Employee 테이블
- a=b=c 'Equilateral'
- a+b<=c 'Not a Triangle'
참고 ) 삼각형이 아닌 조건(Not a Triangle)이 제일 먼저 나오고 정삼각형(Equilateral)이 나와도 된다. 하지만, 정삼각형을 조건을 만족할 경우 무조건 삼각형이기에 제일 먼저 나와도 됨 - a=b,a=c,b=c 'Isosceles'
- else 'Scalene'
- 위 조건에 따라 case를 나누고 'Equilateral','Not a Triangle','Isosceles','Scalene' 출력하기
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
The TRIANGLES table is described as follows:
Each row in the table denotes the lengths of each of a triangle's three sides.
Sample Input
Sample Output
Isosceles
Equilateral
Scalene
Not A Triangle
Explanation
Values in the tuple form an Isosceles triangle, because .
Values in the tuple form an Equilateral triangle, because . Values in the tuple form a Scalene triangle, because .
Values in the tuple cannot form a triangle because the combined value of sides and is not larger than that of side .
CASE
WHEN 조건1 THEN 반환값1
WHEN 조건2 THEN 반환값2
....
ELSE 반환값
END
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 a=c OR b=c THEN 'Isosceles'
ELSE 'Scalene'
END AS result
FROM triangles;
The PADS📝
https://www.hackerrank.com/challenges/the-pads/problem?isFullScreen=true
➡️ occupation 테이블
① 첫번째 SQL문
- name,'('occpuation의 첫글자')'을 바로 출력해라(띄어쓰기 없음)
참고 ) 띄어쓰기 없기에 name, CONCAT('(',LEFT(occupation,1),')')을 출력할 경우 틀리게 된다. - ORDER BY name
② 두번째 SQL문
- "There are a total of [occupation_count] [occupation]s." 형태로 출력하기
참고 ) 뒤에 s. 붙이는거 잊지 않기....⭐️ - occupation_count는 occupation 개수 -> GROUP BY로 occupation 개수 구하기
- occupation을 출력시에는 알파벳 소문자로 출력
문자열 이어 붙이기 함수 ⭐️
CONCAT(문자열1,문자열2)
※ 참고 : ORACLE에서는 매개변수 두개만 허용. 어떤 DBMS에 따라 매개변수 두개만 받기도 하고 여러개 받기도 한다 ※
※ ORACLE || 연산자 = CONCAT과 같은 기능 ,하지만 MYSQL에서는 OR 연산자를 의미, 실무에서는 ||을 더 많이 쓴다고 함 ※
Generate the following two result sets:
- 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).
- Query the number of ocurrences of each occupation in OCCUPATIONS. Sort the occurrences in ascending order, and output them in the following format:
where [occupation_count] is the number of occurrences of an occupation in OCCUPATIONS and [occupation] is the lowercaseoccupation name. If more than one Occupation has the same [occupation_count], they should be ordered alphabetically. - There are a total of [occupation_count] [occupation]s.
Note: There will be at least two entries in the table for each type of occupation.
Input Format
The OCCUPATIONS table is described as follows:
Occupation will only contain one of the following values: Doctor, Professor, Singer or Actor.
Sample Input
An OCCUPATIONS table that contains the following records:
Sample Output
Ashely(P)
Christeen(P)
Jane(A)
Jenny(D)
Julia(A)
Ketty(P)
Maria(A)
Meera(S)
Priya(S)
Samantha(D)
There are a total of 2 doctors.
There are a total of 2 singers.
There are a total of 3 actors.
There are a total of 3 professors.
Explanation
The results of the first query are formatted to the problem description's specifications.
The results of the second query are ascendingly ordered first by number of names corresponding to each profession (), and then alphabetically by profession (, and ).
-- MySQL
SELECT
CONCAT(name,'(',LEFT(occupation,1),')')
FROM occupations
ORDER BY name;
SELECT
CONCAT('There are a total of ',COUNT(occupation),' ',LOWER(occupation),'s.')
FROM occupations
GROUP BY occupation
ORDER BY COUNT(occupation),occupation;
-- Oracle
SELECT
name||'('||SUBSTR(occupation,1,1)||')' --1부터 시작해서 1글자 출력
FROM occupations
ORDER BY name;
SELECT
'There are a total of '|| COUNT(occupation)|| ' '||LOWER(occupation)||'s.'
FROM occupations
GROUP BY occupation
ORDER BY COUNT(occupation),occupation;
Revising Aggregations - The Count Function📝
➡️ city 테이블
- name의 개수 출력
- population이 100000보다 크다.
Query a count of the number of cities in CITY having a Population larger than .
Input Format
The CITY table is described as follows:
SELECT COUNT(name)
FROM city
WHERE population>100000;
Revising Aggregations - The Sum Function📝
➡️ city 테이블
- population 총합 구하기 -> SUM 함수 활용
- district 이 'california'
Query the total population of all cities in CITY where District is California.
Input Format
The CITY table is described as follows:
SELECT SUM(population)
FROM city
WHERE district='california'