SQL
Interviews,15 Days of Learning SQL
Interviews,15 Days of Learning SQL
2022.12.27Interviews 📝 https://www.hackerrank.com/challenges/interviews/problem?isFullScreen=true ➡️ 단계 1️⃣ : contests,colleges,challenges INNERJOIN 단계 2️⃣ : view_status,submission_status -> challenge_id로 SUM(total) 값을 구하기 단계 3️⃣ : 단계 2에서 SUM한 테이블들과 단계 1에서 JOIN 한 값들 OUTER JOIN -> GROUP BY contest_id,hacker_id,name Samantha interviews many candidates from different colleges using coding challenges and cont..
Draw The Triangle 1,Draw The Triangle 2,Print Prime Numbers
Draw The Triangle 1,Draw The Triangle 2,Print Prime Numbers
2022.12.20Draw The Triangle 1 📝 https://www.hackerrank.com/challenges/draw-the-triangle-1/problem?isFullScreen=true information.table : 데이터베이스에 존재하는 테이블에 대한 정보 제공. P(R) represents a pattern drawn by Julia in R rows. The following pattern represents P(5): * * * * * * * * * * * * * * * Write a query to print the pattern P(20). set @number = 21; -- number 21로 지정 select repeat('* ', @number := @number - 1) fr..
SQL Project Planning,Placements,Symmetric Pairs
SQL Project Planning,Placements,Symmetric Pairs
2022.12.06SQL Project Planning 📝 https://www.hackerrank.com/challenges/sql-projects/problem?isFullScreen=true ➡️ projects 테이블 출력 : start_date,end_date 출력 단계 1️⃣ : end_date가 연이어 있을 경우에는 똑같은 프로젝트라고 간주한다는 조건 start_date 중 end_date에 없는 개별 프로젝트의 시작 날짜 구하기 end_date 중 start_date에 없는 개별 프로젝트의 끝나는 날짜 구하기 단계 2️⃣ : end_date 날짜들은 start_date 날짜보다는 앞에 있을 수 없으므로 조건 넣어서 나열 단계 3️⃣ : start_date로 그룹핑 시킨 후에 end_date의 min 값 출력..
Ollivander's Inventory, Challenges,Contest Leaderboard
Ollivander's Inventory, Challenges,Contest Leaderboard
2022.11.22Ollivander's Inventory 📝 https://www.hackerrank.com/challenges/harry-potter-and-wands/problem?isFullScreen=true ➡️ wands 테이블, wands_property 테이블 출력 : wands의 code, wands_property의 age,wands의 coins_needed,wands의 power 조건 1️⃣ : wands, wands_property JOIN하기 -> code 같음 조건 2️⃣ : non-evil을 뽑아야하므로 is_evil = 0 조건 3️⃣ : age,power가 같은 경우 coins_needed가 작은 값을 찾기 (galleons을 적게 들게 ) 조건 4️⃣ : ORDER BY power -> ..
African Cities, The Report, Top Competitors
African Cities, The Report, Top Competitors
2022.11.15African Cities 📝 ⭐️ https://www.hackerrank.com/challenges/weather-observation-station-20/problem?isFullScreen=true ➡️ station 테이블 출력 : lat_n의 중앙값 찾기 ( 소수점 4째자리에서 반올림) MySQL은 중앙값을 찾을 수 없기 때문에 PERCENT_RANK 함수를 활용하여 0.5인 lat_n 출력 되도록 하기 A median is defined as a number separating the higher half of a data set from the lower half. Query the median of the Northern Latitudes(LAT_N) from STATION and rou..
African Cities, Average Population of Each Continent, Weather Observation Station 5, Binary Tree Nodes, New Companies
African Cities, Average Population of Each Continent, Weather Observation Station 5, Binary Tree Nodes, New Companies
2022.10.31African Cities 📝 https://www.hackerrank.com/challenges/african-cities/problem?isFullScreen=true ➡️ city,country 테이블 출력 : name 출력 (city.name or country.name) 조건 1 : 테이블 JOIN city.countrycode = country.code 조건 2 : continent = 'Africa' 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. I..
Weather Observation Station 16,17,18,19 | Population Census
Weather Observation Station 16,17,18,19 | Population Census
2022.10.24Weather Observation Station 16 📝 https://www.hackerrank.com/challenges/weather-observation-station-16/problem?isFullScreen=true ➡️ station 테이블 38.7780보다 큰 lat_n 중 가장 작은 값 ( 단, 소숫점 5자리에서 반올림) => min,round 함수 사용 Query the smallest Northern Latitude (LAT_N) from STATION that is greater than 38.7780. Round your answer to 4 decimal places. Input Format The STATION table is described as follows: where..
HackerRank - Top Earners,Weather Observation Station 2, 13, 14, 15
HackerRank - Top Earners,Weather Observation Station 2, 13, 14, 15
2022.10.03Top Earners 📝 ⭐️ https://www.hackerrank.com/challenges/earnings-of-employees/problem?isFullScreen=true ➡️ employee 테이블 salary * months 의 최댓값과 이 최댓값을 가진 사람 수 구하기 참고 1) 필드명 alias 할 경우 HAVING은 alias을 사용하여야 한다, GROUP BY 안에는 alias 필드명 사용해도 안사용해도 무관(MYSQL 기준), ORACLE에서는 GROUPBY alias 사용하면 안된다(salary*months) ⭐️ 참고 2) 상위 뽑기 할 때, MYSQL - LIMT 사용, ORACLE - rownum ⭐️ We define an employee's total earnings ..
Revising Aggregations - Averages,Average Population,Japan Population,Population Density Difference,The Blunder
Revising Aggregations - Averages,Average Population,Japan Population,Population Density Difference,The Blunder
2022.09.17Revising Aggregations - Averages📝 https://www.hackerrank.com/challenges/revising-aggregations-the-average-function/problem?isFullScreen=true ➡️ city 테이블 population 평균 구하기 -> AVG 함수 활용 district 이 'california' Query the average population of all cities in CITY where District is California. Input Format The CITY table is described as follows: SELECT AVG(population) FROM city WHERE district='califor..
Employee Salaries,Type of Traingle,The PADS,Revising Aggregations - (The Count Function,The Sum Function)
Employee Salaries,Type of Traingle,The PADS,Revising Aggregations - (The Count Function,The Sum Function)
2022.09.12Employee Salaries📝 https://www.hackerrank.com/challenges/salary-of-employees/problem?isFullScreen=true ➡️ Employee 테이블,조건 : salary>2000 & month 출력: 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 asce..
HackerRank- Weather Observation Station 10,11,12,Higher Than 75 Marks,Employee Names
HackerRank- Weather Observation Station 10,11,12,Higher Than 75 Marks,Employee Names
2022.09.12Weather Observation Station 10📝 https://www.hackerrank.com/challenges/weather-observation-station-10/problem?isFullScreen=true ➡️ STATION 테이블, 조건 : a,e,i,o,u로 끝나지 않는 city 이름 -> 출력 : 중복되지 않는 city -> Weather Observation Station 7번(이전게시물) 에 not 또는 ^을 붙인다. Query the list of CITY names from STATION that do not end with vowels. Your result cannot contain duplicates. Input Format The STATION table is d..
REGEXP,REGEXP_LIKE,REGEXP_INSTR,REGEXP_SUBSTR,REGEXP_REPLACE()
REGEXP,REGEXP_LIKE,REGEXP_INSTR,REGEXP_SUBSTR,REGEXP_REPLACE()
2022.09.05참고 블로그 : https://goodteacher.tistory.com/232 MySQL :: MySQL 8.0 Reference Manual :: 12.8.2 Regular Expressions 12.8.2 Regular Expressions Table 12.14 Regular Expression Functions and Operators Name Description NOT REGEXP Negation of REGEXP REGEXP Whether string matches regular expression REGEXP_INSTR() Starting index of substring matching regular expression REGE dev.mysql.com * 모든 쿼리는 match_type..