본문 바로가기

SQL

 (40)
[SQL] UNION # UNION: 중복된 값은 제외하여 위아래 합쳐주는 집합연산(DISTINCT가 디폴트값) #UNION ALL: 중복된 값까지 모두 합쳐서 출력 ex) products 테이블에서 Price가 5이하 또는 200 이상인 상품들만 출력하세요 SELECT * FROM products WHERE price =200 SELECT * FROM products WHERE price =200 같은 결과 출력 ex) 회원 주문 정보와 비회원 주문 정보를 모두 보고 싶은 경우 SELECT * FROM customers WHERE LEFT JOIN orders ON customers.customerID = orders.customerID UNION SELECT * FROM customers WHERE RIGHT JOIN o..
[SQL] LeetCode: SELF JOIN 문제 181. Employees Earning More Than Their Managers 문제 Write an SQL query to find the employees who earn more than their managers. Return the result table in any order. 풀이 SELECT a.name AS Employee FROM employee AS a LEFT JOIN employee AS b ON a.managerid = b.id WHERE a.salary > b.salary 197. Rising Temperature 문제 Write an SQL query to find all dates' Id with higher temperatures compared to its prev..
[SQL] LeetCode : LEFT JOIN 문제 183. Customers Who Never Order 문제 Write an SQL query to report all customers who never order anything. Return the result table in any order. 풀이 SELECT customers.name AS Customers FROM customers LEFT JOIN orders ON customers.id = orders.customerid WHERE orders.id IS NULL
[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] JOIN INNER JOIN : 교집합 A와 B 모두에 정보가 있어야 조인해서 출력 SELECT * FROM Orders INNER JOIN Customers ON Orders.CustomerID = Customers.CustomerID INNER JOIN Shippers ON Orders.ShipperID = Shippers.ShipperID -> 한번이라도 주문을 한 사람들만 출력 OUTER JOIN(LEFT, RIGHT) # 대부분 LEFT JOIN을 사용함(방향만 다른 것임) # JOIN 2개 이상 가능 참고 사이트: SQL Joins Visualizer (leopard.in.ua) *본 내용은 데이터리안 'SQL 데이터 분석 캠프 입문반' 을 수강하며 작성한 내용입니다.
[SQL] LeetCode : 1193. Monthly Transactions I Monthly Transactions I - LeetCode 문제 Write an SQL query to find for each month and country, the number of transactions and their total amount, the number of approved transactions and their total amount. Return the result table in any order. 풀이 SELECT SUBSTR(trans_date,1,7) AS month ,country ,COUNT(trans_date) AS trans_count ,COUNT(IF(state='approved',1,NULL)) AS approved_count ,SUM(amount) AS tr..
[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로 EDA해보기 US E-Commerce Records 2020 데이터 셋 1. records 테이블에 들어있는 'JP-15520' 유저의 데이터를 확인해봅시다. 해당 유저가 주문을 한 횟수는 몇 번인가요? SELECT count(*) FROM records WHERE customer_id = 'JP-15520' 6번(오답) SELECT count(DISTINCT order_id) FROM records WHERE customer_id = 'JP-15520' 주문 횟수는 물어봤으니까 order_id를 카운트해야 함 한번 주문할 때 여러 물품을 주문할 수 있으므로 DISTINCT로 중복 계수 방지 2. records 테이블에 들어있는 'JP-15520' 유저가 주문한 총 금액은 얼마인가요? SELECT SUM(sales)..

티스토리툴바