with sub1 as(
select city, length(city) as length_city
from station)
(select city, length_city
from sub1
order by length_city, city
limit 1)
union
(select city, length_city
from sub1
order by length_city desc, city
limit 1)
SQL
복사
•
정규 표현식 사용
select distinct(city)
from station
where city regexp '^[aeiou]'; # aeiou 로 시작하는 문자들
SQL
복사
select distinct(city)
from station
where city regexp '[aeiou]$'; # aeiou 로 끝나는 문자들
SQL
복사
select distinct(city)
from station
where city regexp '^[aeiou]' # aeiou로 시작하거나
and city regexp '[aeiou]$'; # aeiou로 끝나는 문자들
SQL
복사
select distinct(city)
from station
where city not regexp '^[aeiou]';
SQL
복사
select distinct(city)
from station
where city not regexp '[aeiou]$';
SQL
복사
select distinct(city)
from station
where city not regexp '^[aeiou]'
or city not regexp '[aeiou]$';
SQL
복사
select distinct(city)
from station
where city not regexp '^[aeiou]'
and city not regexp '[aeiou]$';
SQL
복사
•
문자열 자르기(mid / substring / substr)
select name
from students
where marks > 75
order by mid(name,-3), id;
SQL
복사
다른 사람 풀이SELECT
/* Check is triangle */
IF(
(A + B > C) AND (A + C > B) AND (B + C > A),
/* Then Select triangle type */
CASE
WHEN (A = B) AND (A = C)
THEN "Equilateral"
WHEN (A = B) or (B = C) or (A = C)
THEN "Isosceles"
ELSE
"Scalene"
END,
/* else not triangle */
"Not A Triangle"
)
FROM
SQL
복사
# 재풀이
SELECT
CASE
WHEN A + B <= C OR B + C <= A OR A + C <= B THEN 'Not A Triangle'
WHEN A=B AND B=C THEN 'Equilateral'
WHEN A <> B AND B <> C AND A <> C THEN 'Scalene'
ELSE 'Isosceles'
END AS RESULT_NAME
FROM TRIANGLES;
SQL
복사
UNION 함수는 각 테이블의 select 결과 필드가 같아야지만 쓸 수 있다. (My SQL 의 경우) 대신 각 쿼리의 결과를 (); (); 이렇게 나눠서 작성하면 제시된 결과물과 동일하게 만들 수 있다. 사용한 함수 •
CONCAT() : 여러 문자열 합쳐주기
•
LOWER() : 모두 소문자로 변경해주기
•
SUBSTR() : 문자열 일부 추출하기
쿼리문
pivot 테이블 만들기•
ROW_NUMBER()
•
from 서브 쿼리
1.
max(), case 문 사용
쿼리문
2.
max() , if() 사용
먼저, 위의 from 서브 쿼리(temp)를 살펴보면,
이후에 RN 별로 다시 그룹핑해서(1인 것끼리 맨 윗줄, 2인 것끼리 두번째 줄..,) 집계하여 열로 나타내는 것이다. 이때, MAX() 를 해주는 이유는 단순히 group by 를 하려면 ‘집계함수’가 있어야 하기 때문에 사용해준 것이다. 어차피 case when 절의 값은 ‘문자’ 이기때문에 max() 를 해줘도 출력값에 영향을 미치지 않는다.
select
max(case when Occupation = 'Doctor' then Name end) as 'Doctor',
max(case when Occupation = 'Professor' then Name end) as 'Professor',
max(case when Occupation = 'Singer' then Name end) as 'Singer',
max(case when Occupation = 'Actor' then Name end) as 'Actor'
from (select *, row_number() over (partition by Occupation order by Name) RN
from OCCUPATIONS) TEMP
group by RN
SQL
복사
Binary Tree Nodes
SELECT N,
CASE
WHEN P IS NULL THEN 'Root'
WHEN N IN (SELECT DISTINCT P FROM BST) THEN 'Inner'
ELSE 'Leaf'
END AS RESULT_COLUMN
FROM BST
ORDER BY N;
SQL
복사
New Companies
계층 관계 테이블 이해하기
select
C.company_code,
C.founder,
count(distinct LM.lead_manager_code),
count(distinct SM.senior_manager_code),
count(distinct M.manager_code),
count(distinct E.employee_code)
from Company C
left join Lead_Manager LM on C.company_code = LM.company_code
left join Senior_Manager SM on LM.lead_manager_code = SM.lead_manager_code
left join Manager M on SM.senior_manager_code = M.senior_manager_code
left join Employee E on M.manager_code = E.manager_code
group by C.company_code, C.founder
order by C.company_code;
SQL
복사
Revising Aggregations - The Count Function
Query a count of the number of cities in CITY having a Population larger than .
Input Format
The CITY table is described as follows:
select count(distinct ID)
from CITY
where POPULATION > 100000;
SQL
복사
Revising Aggregations - The Sum Function
Query the total population of all cities in CITY where District is California.
Input Format
The CITY table is described as follows:
select sum(POPULATION)
from CITY
where DISTRICT = 'California';
SQL
복사
Revising Aggregations - Averages
Query the average population of all cities in CITY where District is California.
Input Format
The CITY table is described as follows:
select avg(POPULATION)
from CITY
where DISTRICT='California';
SQL
복사
Average Population
Query the average population for all cities in CITY, rounded down to the nearest integer.
Input Format
The CITY table is described as follows:
select floor(avg(POPULATION))
from CITY;
SQL
복사
•
CEIL() / CEILING() : 올림
•
ROUND() : 지정된 소수점 자리에서 반올림
•
TRUNCATE() : 지정한 소수점 자리에서 버림(절삭)
Japan Population
Query the sum of the populations for all Japanese cities in CITY. The COUNTRYCODE for Japan is JPN.
Input Format
The CITY table is described as follows:
select sum(POPULATION)
from CITY
where COUNTRYCODE = 'JPN';
SQL
복사
Population Density Difference
Query the difference between the maximum and minimum populations in CITY.
Input Format
The CITY table is described as follows:
select max(POPULATION) - min(POPULATION)
from CITY;
SQL
복사
The Blunder
내용
Top Earners
select (months * salary) as earnings,
count(*)
from Employee
group by earnings
order by earnings desc
limit 1;
SQL
복사
Weather Observation Station 15
select
round(LONG_W, 4)
from STATION
where LAT_N < 137.2345
order by LAT_N desc
limit 1;
SQL
복사
조건을 만족하는 값들 중, 가장 큰 값을 찾으려면
•
order by desc
•
limit 1
The PADS
-- 1.
select
concat(Name,'(',substr(Occupation,1,1), ')') as name_ocup
from OCCUPATIONS
order by Name;
-- 2.
select
concat('There are a total of ', count(Occupation), ' ', lower(Occupation), 's.')
from OCCUPATIONS
group by Occupation
order by count(Occupation), Occupation;
SQL
복사
•
concat
◦
문자를 연결할 때 사용. 띄어쓰기 수동으로 조정 필요