본문 바로가기

Algorithm/SQL

 (9)
[Hacker Rank] BASIC JOIN - 1 👻 QUESTIONGiven the CITY and COUNTRY tables, query the sum of the populations of all cities where the CONTINENT is 'Asia'. Note: CITY.CountryCode and COUNTRY.Code are matching key columns. 🚀ANSWERSELECT SUM(CITY.POPULATION)FROM CITYJOIN COUNTRY ON CITY.COUNTRYCODE = COUNTRY.CODEWHERE COUNTRY.CONTINENT = 'Asia'; 👻 QUESTIONGiven the CITY and COUNTRY tables, query the names of all cities where ..
[Hacker Rank] BASIC SELECT - 2 👻 QUESTIONQuery the list of CITY names starting with vowels (i.e., a, e, i, o, or u) from STATION. Your result cannot contain duplicates. 🚀ANSWERSELECT DISTINCT CITYFROM STATIONWHERE LEFT(LOWER(CITY), 1) IN ('a', 'e', 'i', 'o', 'u'); 🔮 TIPSThe LEFT() function extracts a number of characters from a string (starting from left).LEFT(string, number_of_chars) 👻 QUESTIONQuery the list of CITY na..
[Hacker Rank] BASIC SELECT - 1 👻 QUESTIONQuery all columns for all American cities in the CITY table with populations larger than 100000. The CountryCode for America is USA. 🚀ANSWERSELECT * FROM CITYWHERE COUNTRYCODE='USA' AND POPULATION > 100000; 👻 QUESTIONQuery the NAME field for all American cities in the CITY table with populations larger than 120000. The CountryCode for America is USA. 🚀ANSWERSELECT NAME FROM CITYWH..
[Programmers/MySQL] STRING, DATE 문제 풀이 모음 1. 자동차 대여 기록에서 장기/단기 대여 구분하기 SELECT HISTORY_ID, CAR_ID, DATE_FORMAT(START_DATE, '%Y-%m-%d') AS START_DATE, DATE_FORMAT(END_DATE, '%Y-%m-%d') AS END_DATE, IF(DATEDIFF(END_DATE, START_DATE) >= 29, '장기 대여', '단기 대여') AS RENT_TYPE FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY WHERE START_DATE LIKE '2022-09%' ORDER BY HISTORY_ID DESC; 2. 루시와 엘라 찾기 SELECT ANIMAL_ID, NAME, SEX_UPON_INTAKE FROM ANIMAL_INS WHERE..
[Programmers/MySQL] JOIN 문제 풀이 모음 1. 상품 별 오프라인 매출 구하기 SELECT PRODUCT.PRODUCT_CODE, SUM(PRODUCT.PRICE * OFFLINE_SALE.SALES_AMOUNT) AS SALES FROM PRODUCT JOIN OFFLINE_SALE ON PRODUCT.PRODUCT_ID = OFFLINE_SALE.PRODUCT_ID GROUP BY PRODUCT.PRODUCT_ID ORDER BY SALES DESC, PRODUCT.PRODUCT_CODE; 2. 조건에 맞는 도서와 저자 리스트 출력하기 SELECT BOOK.BOOK_ID AS BOOK_ID, AUTHOR.AUTHOR_NAME AS AUTHOR_NAME, DATE_FORMAT(BOOK.PUBLISHED_DATE, '%Y-%m-%d') AS PU..
[Programmers/MySQL] IS NULL 문제 풀이 모음 1. 이름이 없는 동물의 아이디 SELECT ANIMAL_ID FROM ANIMAL_INS WHERE NAME IS NULL ORDER BY ANIMAL_ID ASC; 2. 이름이 있는 동물의 아이디 SELECT ANIMAL_ID FROM ANIMAL_INS WHERE NAME IS NOT NULL ORDER BY ANIMAL_ID ASC; 3. 나이 정보가 없는 회원 수 구하기 SELECT COUNT(USER_ID) AS USERS FROM USER_INFO WHERE AGE IS NULL; 4. 경기도에 위치한 식품창고 목록 출력하기 SELECT WAREHOUSE_ID, WAREHOUSE_NAME, ADDRESS, IF(FREEZER_YN IS NULL, 'N', FREEZER_YN) AS FREEZ..
[Programmers/MySQL] GROUP BY 문제 풀이 모음 1. 입양 시각 구하기(1) SELECT HOUR(DATETIME) AS HOUR, COUNT(*) FROM ANIMAL_OUTS GROUP BY HOUR HAVING HOUR BETWEEN 9 AND 19 ORDER BY HOUR; 2. 입양 시각 구하기(2) SET @hour := -1; SELECT (@hour := @hour + 1) as HOUR, (SELECT COUNT(*) FROM ANIMAL_OUTS WHERE HOUR(DATETIME) = @hour) as COUNT FROM ANIMAL_OUTS WHERE @hour < 23 3. 동명 동물 수 찾기 SELECT NAME, COUNT(*) FROM ANIMAL_INS WHERE NAME IS NOT NULL GROUP BY NAME H..
[Programmers/MySQL] SUM/MAX/MIN 문제 풀이 모음 1. 최댓값 구하기 SELECT DATETIME FROM ANIMAL_INS ORDER BY DATETIME DESC LIMIT 1; 2. 중복 제거하기 SELECT COUNT(DISTINCT NAME) AS count FROM ANIMAL_INS; 3. 동물 수 구하기 SELECT COUNT(*) FROM ANIMAL_INS; 4. 최솟값 구하기 SELECT DATETIME FROM ANIMAL_INS ORDER BY DATETIME ASC LIMIT 1; 5. 가장 비싼 상품 구하기 SELECT PRODUCT.PRICE AS MAX_PRICE FROM PRODUCT ORDER BY PRICE DESC LIMIT 1; 6. 가격이 제일 비싼 식품의 정보 출력하기 SELECT PRODUCT_ID, PRO..

티스토리툴바