Allen's 데이터 맛집

1. SQL이란?SQL(Structured Query Language)은 데이터베이스와 상호작용하기 위해 사용하는 표준 언어입니다.주요 기능:데이터 검색(SELECT)데이터 삽입(INSERT)데이터 갱신(UPDATE)데이터 삭제(DELETE)2. 주요 SQL 명령어SELECT: 데이터 조회SELECT * FROM employees WHERE department = 'Sales';  2. INSERT: 데이터 삽입INSERT INTO employees (name, department, salary)VALUES ('John Doe', 'Engineering', 70000);  3. UPDATE: 데이터 수정UPDATE employeesSET salary = 80000WHERE name = 'John Doe'..

문제   풀이Julia가 주최한 최근 코딩 대회에서 우수한 성적을 거둔 뒤, 이번에도 그녀의 요청으로 새로운 챌린지에 참여하게 되었습니다! 이번 문제는 각 해커의 챌린지별 최고 점수를 합산하여 해커의 총점을 계산하고, 이를 기반으로 리더보드를 생성하는 SQL 쿼리를 작성하는 것입니다.  SELECT s.hacker_id, h.name, SUM(s.max_score) AS total_max_scoreFROM (SELECT hacker_id, challenge_id, MAX(score) AS max_score FROM Submissions GROUP BY hacker_id, challenge_id ) s JO..

문제   풀이 Julia는 그녀의 학생들에게 코딩 챌린지를 만들도록 요청했습니다. 이 문제에서는 각 학생이 생성한 챌린지의 수를 분석하고, 결과를 챌린지 수에 따라 내림차순으로 정렬하여 출력하는 SQL 쿼리를 작성해야 합니다. 또한, 같은 수의 챌린지를 생성한 학생이 여럿인 경우 최대 생성 수를 제외하고 해당 학생들을 결과에서 제외해야 합니다.  WITH data AS ( SELECT c.hacker_id AS id, h.name AS name, COUNT(c.hacker_id) AS counter FROM Hackers h JOIN Challenges c ON c.hacker_id = h.hacker_id GROUP BY..

문제  풀이SELECT w.id, p.age, w.coins_needed, w.power FROM Wands AS w JOIN Wands_Property AS p ON (w.code = p.code) WHERE p.is_evil = 0 AND w.coins_needed = (SELECT MIN(coins_needed) FROM Wands AS w1 JOIN Wands_Property AS p1 ON (w1.code = p1.code) WHERE w1.power = w.power AND p1.age = p.age ) ORDER BY w.power DESC, p.age DESC  1. FROM 절과 JOIN 사용: Wands 테이블과 ..