본문 바로가기

전체 글93

[LeetCode] CASE 테이블 피봇 : 1179. Reformat Department Table 1. 문제 Table: Department +---------------+---------+ | Column Name | Type | +---------------+---------+ | id | int | | revenue | int | | month | varchar | +---------------+---------+ (id, month) is the primary key of this table. The table has information about the revenue of each department per month. The month has values in ["Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec".. 2021. 3. 15.
[HackerRank] Self JOIN: Symmetric Pairs 1. 문제 You are given a table, Functions, containing two columns: X and Y. Two pairs (X1, Y1) and (X2, Y2) are said to be symmetric pairs if X1 = Y2 and X2 = Y1. Write a query to output all such symmetric pairs in ascending order by the value of X. List the rows such that X1 ≤ Y1. Sample Input Sample Output 20 20 20 21 22 23 2. 답 SELECT x, y FROM functions WHERE x = y GROUP BY x, y HAVING count(*).. 2021. 3. 15.
[LeetCode] Self JOIN : 197. Rising Temperature 1. 문제 Table: Weather +---------------+---------+ | Column Name | Type | +---------------+---------+ | id | int | | recordDate | date | | temperature | int | +---------------+---------+ id is the primary key for this table. This table contains information about the temperature in a certain day. Write an SQL query to find all dates' id with higher temperature compared to its previous dates (yester.. 2021. 3. 15.
[LeetCode] Self JOIN : 181. Employees Earning More Than Their Managers 1. 문제 The Employee table holds all employees including their managers. Every employee has an Id, and there is also a column for the manager Id. +----+-------+--------+-----------+ | Id | Name | Salary | ManagerId | +----+-------+--------+-----------+ | 1 | Joe | 70000 | 3 | | 2 | Henry | 80000 | 4 | | 3 | Sam | 60000 | NULL | | 4 | Max | 90000 | NULL | +----+-------+--------+-----------+ Given t.. 2021. 3. 15.
[LeetCode] LEFT JOIN : 183. Customers Who Never Order 1. 문제 Suppose that a website contains two tables, the Customers table and the Orders table. Write a SQL query to find all customers who never order anything. Table: Customers. +----+-------+ | Id | Name | +----+-------+ | 1 | Joe | | 2 | Henry | | 3 | Sam | | 4 | Max | +----+-------+ Table: Orders. +----+------------+ | Id | CustomerId | +----+------------+ | 1 | 3 | | 2 | 1 | +----+------------.. 2021. 3. 15.
[HackerRank] INNER JOIN : Average Population of Each Continent 1. 문제 Given the CITY and COUNTRY tables, query the names of all the continents (COUNTRY.Continent) and their respective average city populations (CITY.Population) rounded down to the nearest integer. Note: CITY.CountryCode and COUNTRY.Code are matching key columns. Input Format The CITY and COUNTRY tables are described as follows: 2. 답 SELECT country.continent, FLOOR(AVG(city.population)) FROM c.. 2021. 3. 15.

티스토리툴바