본문 바로가기

SQL/HackerRank

 (10)
[SQL] HackerRank: Weather Observation Station 7, 8, 9 Weather Observation Station 7 문제 Query the list of CITY names ending with vowels (a, e, i, o, u) from STATION. Your result cannot contain duplicates. 풀이(정규표현식 사용) [ ]$ : [ ]안에 있는 문자 중에 하나로 끝난다 Weather Observation Station 8 문제 Query the list of CITY names from STATION which have vowels (i.e., a, e, i, o, and u) as both their first and last characters. Your result cannot contain duplicates. 풀이(정규표..
[SQL] HackerRank: Weather Observation Station 6 Weather Observation Station 6 문제 Query the list of CITY names starting with vowels (i.e., a, e, i, o, or u) from STATION. Your result cannot contain duplicates. -중복없이 모음으로 시작하는 도시 이름 출력 풀이1) LIKE 활용 풀이2) 정규표현식 활용(Regular Expressions) ^ [aeiou] : [ ] 안 문자 중 하나로 시작하는 (^ 시작을 의미) .* : %와 같은 역할(와일드 카드)
[SQL] HackerLank : Challenges 문제 Julia asked her students to create some coding challenges. Write a query to print the hacker_id, name, and the total number of challenges created by each student. Sort your results by the total number of challenges in descending order. If more than one student created the same number of challenges, then sort the result by hacker_id. If more than one student created the same number of challeng..
[SQL] HarkerLank: Top Earners 문제 We define an employee's total earnings to be their monthly salary X months worked, and the maximum total earnings to be the maximum total earnings for any employee in the Employee table. Write a query to find the maximum total earnings for all employees as well as the total number of employees who have maximum total earnings. Then print these values as 2 space-separated integers. 풀이1) GROUP B..
[SQL] HackerRank: Symmetric Pairs 문제 You are given a table, Functions, containing two columns: X and Y. Two pairs (X1, Y1) and (X2, Y2) are said to be symmetric pairs if X1 = Y2 and X2 = Y1. Write a query to output all such symmetric pairs in ascending order by the value of X. List the rows such that X1 ≤ Y1. -> 대칭되는 쌍이 있는 (x,y) 찾기 풀이 SELECT f1.x ,f1.y FROM functions AS f1 INNER JOIN functions AS f2 ON f1.x = f2.y AND f1.y =f2.x..
[SQL] HackerRank: INNER JOIN 문제 1. African Cities 문제 Given the CITY and COUNTRY tables, query the names of all cities where the CONTINENT is 'Africa'. Note: CITY.CountryCode and COUNTRY.Code are matching key columns. 풀이 SELECT city.name FROM city INNER JOIN country ON CITY.CountryCode = COUNTRY.Code WHERE continent = 'Africa' # city 테이블, country 테이블 둘 다 name이라는 컬럼을 가지고 있음 -> city.name ; 어떤 테이블의 컬럼을 출력할 것인지 명확하게 해줘야 함! 2. Popul..
[SQL] HakerRank: The Report 문제 Ketty gives Eve a task to generate a report containing three columns: Name, Grade and Mark. Ketty doesn't want the NAMES of those students who received a grade lower than 8. The report must be in descending order by grade -- i.e. higher grades are entered first. If there is more than one student with the same grade (8-10) assigned to them, order those particular students by their name alphabe..
[SQL] HackerRank : Weather Observation Station 3 문제 Query a list of CITY names from STATION for cities that have an even ID number. Print the results in any order, but exclude duplicates from the answer. 실행코드 # cities that have an even ID number -> MOD 함수 이용MOD(id,2) : id 값을 2로 나눈 나머지 출력 -> 2로 나눈 나머지가 0이면 짝수 # exclude duplicates from the answer -> DISTINCT를 이용해 중복 값 제거 +다른 풀이 방법(feat. SQL스터디) #1 일의 자리 숫자가 0,2,4,6,8이면 짝수 -> RIGHT 함수로 ID의 일의 자리 ..

티스토리툴바