"Growth" Data를 쌓아가는 사람

182. Duplicate Emails Write an SQL query to report all the duplicate emails. Note that it's guaranteed that the email field is not NULL. select email from Person group by email having count(email) > 1 group by와 having절을 사용해 조건에 해당하는 결과를 추출한다. 1050. Actors and Directors Who Cooperated At Least Three Times Write a SQL query for a report that provides the pairs (actor_id, director_id) where the act..

SQL/SQL 문제풀이 스터디 2023. 3. 12. 18:05

36. Aggregation > Weather Observation Station 19 Consider P1(a,c) and P2(b,d) to be two points on a 2D plane where (a,b) are the respective minimum and maximum values of Northern Latitude (LAT_N) and (c,d)are the respective minimum and maximum values of Western Longitude (LONG_W) in STATION. Query the Euclidean Distance between points and and format your answer to display 4 decimal digits. SELEC..

SQL/SQL 문제풀이 스터디 2023. 3. 4. 20:54

26. Aggregation > Population Density Difference: MIN(), MAX() Query the difference between the maximum and minimum populations in CITY. SELECT MAX(POPULATION) - MIN(POPULATION) FROM CITY 27. Aggregation > The Blunder: CAST(), REPLACE(), AVG(), CEIL() Samantha was tasked with calculating the average monthly salaries for all employees in the EMPLOYEES table, but did not realize her keyboard's key ..

SQL/SQL 문제풀이 스터디 2023. 3. 3. 17:45

16. Weather Observation Station11 Query the list of CITY names from STATION that either do not start with vowels or do not end with vowels. Your result cannot contain duplicates. SELECT DISTINCT CITY FROM STATION WHERE LEFT(CITY,1) NOT IN ("A", "E", "I", "O", "U") OR RIGHT (CITY,1) NOT IN ("a", "e", "i", "o", "u") 17. Weather Observation Station12 Query the list of CITY names from STATION that e..

SQL/SQL 문제풀이 스터디 2023. 2. 12. 21:15

1. Basic Select > Revising the Select Query I select * from CITY where POPULATION > 100000 and COUNTRYCODE = "USA" 2. Basic Select > Revising the Select Query II select NAME from CITY where POPULATION > 120000 and COUNTRYCODE = "USA" 3. Basic Select > Select All SELECT * FROM CITY 4. Basic Select > Select By ID Query all columns for a city in CITY with the ID 1661. SELECT * FROM CITY WHERE ID = ..

SQL/SQL 문제풀이 스터디 2023. 2. 5. 21:19

문제1. (175)Combine Two Tables #leftjoin select p.firstName, p.lastName, a.city, a.state from Person p left join Address a on p.personId = a.personId left join을 하게 되면 공통 key가 없는 값들에 대해서는 null값이 채워지게 된다. 문제2. (1581)Customer Who Visited but Did Not Make Any Transactions #leftjoin select v.customer_id as customer_id, count(v.visit_id) as count_no_trans from Visits v left join Transactions t on v.visi..

SQL/SQL 문제풀이 스터디 2022. 12. 12. 21:51

문제1. (595)Big Countries select name, population, area from World where area >= 3000000 or population >= 25000000 문제2. (1757)Recyclable and Low Fat Products select product_id from Products where low_fats = 'Y' and recyclable = 'Y' 문제3. (584)Find Customer Referee select name from Customer where id not in (select id from Customer where referee_id = 2) 문제4. (183)Customers Who Never Order select name..

SQL/SQL 문제풀이 스터디 2022. 12. 8. 17:47

문제1. 프로그래머스 > JOIN > 상품별 온라인 매출 구하기 #JOIN #집계함수 #GROUPBY SELECT P.PRODUCT_CODE, SUM(O.SALES_AMOUNT) * P.PRICE AS SALES FROM PRODUCT P INNER JOIN OFFLINE_SALE O ON P.PRODUCT_ID = O.PRODUCT_ID GROUP BY 1 ORDER BY 2 DESC, 1 문제2. 프로그래머스 > JOIN > 상품을 구매한 회윈 비율 구하기 #날짜함수 #집계함수 SELECT YEAR(OS.SALES_DATE) AS YEAR, MONTH(OS.SALES_DATE) AS MONTH, COUNT(DISTINCT OS.USER_ID) AS PUCHASED_USERS, ROUND((COUNT(..

SQL/SQL 문제풀이 스터디 2022. 12. 8. 16:36