HAZEL

- 문제 설명 ANIMAL_OUTS 테이블은 동물 보호소에서 입양 보낸 동물의 정보를 담은 테이블입니다. ANIMAL_OUTS 테이블 구조는 다음과 같으며, ANIMAL_ID, ANIMAL_TYPE, DATETIME, NAME, SEX_UPON_OUTCOME는 각각 동물의 아이디, 생물 종, 입양일, 이름, 성별 및 중성화 여부를 나타냅니다. NAMETYPENULLABLE ANIMAL_ID VARCHAR(N) FALSE ANIMAL_TYPE VARCHAR(N) FALSE DATETIME DATETIME FALSE NAME VARCHAR(N) TRUE SEX_UPON_OUTCOME VARCHAR(N) FALSE 보호소에서는 몇 시에 입양이 가장 활발하게 일어나는지 알아보려 합니다. 0시부터 23시까지, 각..

>> 문제 You did such a great job helping Julia with her last coding contest challenge that she wants you to work on this one, too! The total score of a hacker is the sum of their maximum scores for all of the challenges. Write a query to print the hacker_id, name, and total score of the hackers ordered by the descending score. If more than one hacker achieved the same total score, then sort the re..

>> 문제 Weather Observation Station 17 Query the Western Longitude (LONG_W)where the smallest Northern Latitude (LAT_N) in STATION is greater than 38.7780. Round your answer to decimal places. -> LONG_W 을 소수점 4자리까지 가져와라. 조건 1, LAT_N이 38.7780 보다 큰 것 조건 2 . 조건 1중에 가장 작은 LAT_N이다. Input Format The STATION table is described as follows: where LAT_N is the northern latitude and LONG_W is the western lon..

>> 문제 Write an SQL query to report the nth highest salary from the Employee table. If there is no nth highest salary, the query should report null. ex, 4번째로 높은 셀러리가 없을 경우 null 을 나온다 The query result format is in the following example. 동점인 점수는 다 1등 그 다음은 2등으로, dense rank 를 기준으로 생각해주면 된다. https://leetcode.com/problems/nth-highest-salary/submissions/ >> 문제 풀이 방법 * 사용자 정의 함수의 방법 https://hazel01.tist..

Weather Observation Station 5 >> 문제 Query the two cities in STATION with the shortest and longest CITY names, as well as their respective lengths (i.e.: number of characters in the name). If there is more than one smallest or largest city, choose the one that comes first when ordered alphabetically. The STATION table is described as follows: where LAT_N is the northern latitude and LONG_W is the..

User - defined Function - Mysql 01. 기본 구조 CREATE FUNCTION '함수이름 function name' ('parameter name', 'datatype' ) RETURN '출력될 결과의 datatype' ( DETERMINISTIC ) BEGIN DECLARE 'variable name' 'datatype'; SET; RETURN ( Query ) / 'variabel name'; END -- 사용 방법 SELECT 'function name' (parameter) ( DETERMINISTIC ) : 은 NOT DETERMINISTIC 이 defalt 임. 인풋값은 똑같고, 아웃풋값이 호출할 때마다 같을때, DETERMINISTIC 을 적어주고, 아니면 defal..

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. Input Format The STATION table is described as follows: where LAT_N is the northern latitude and LONG_W is the western longitude. >> 기본적으로 푸는 방법 SELECT DISTINCT city FROM station WHERE city LIKE '%a' OR city LIKE '%e' OR city LIKE '%i' OR city L..

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. Input Format The STATION table is described as follows: where LAT_N is the northern latitude and LONG_W is the western longitude. >> 기본적으로 푸는 방법 select DISTINCT city FROM station WHERE city LIKE 'a%' OR city LIKE 'e%' OR city LIKE 'i%..

185. Department Top Three Salaries >> 문제 The Employee table holds all employees. Every employee has an Id, and there is also a column for the department Id. +----+-------+--------+--------------+ | Id | Name | Salary | DepartmentId | +----+-------+--------+--------------+ | 1 | Joe | 85000 | 1 | | 2 | Henry | 80000 | 2 | | 3 | Sam | 60000 | 2 | | 4 | Max | 90000 | 1 | | 5 | Janet | 69000 | 1 | |..

180. Consecutive Numbers >> 문제 +-------------+---------+ | Column Name | Type | +-------------+---------+ | id | int | | num | varchar | +-------------+---------+ id is the primary key for this table. Write an SQL query to find all numbers that appear at least three times consecutively. Return the result table in any order. The query result format is in the following example: Logs table: +----+-..