[멋사 SQL 1day] select, where, order by, 연산자

#SQL이란? 

SQL(Structured Query Language)은 관계형 데이터베이스를 조작하기 위한 언어

---

# select 

---

# 연산자

1. 비교 연산자

• 특정 컬럼이 특정 값을 가지는 데이터만 불러오기 위해 사용함
• =, <>, >, <, > = , < =
• 문자도 비교 가능
• 문자도 비교 가능

 

2. 논리연산자

• AND, OR
• 비교연산자를 이어줌, 조건을 이어줌

3. Where절에 들어가는 연산자

• like ‘% %’
• in ( )
• between A and B
• IS NULL, NOT NULL

 

+LIKE심화 : %는 와일드카드다, 어떤 값이 와도 상관없음을 의미

---

# 정렬하기: order by

• 맨 마지막줄에 order by 위치함
• 기본값은 오름차순(asc), 내림차순정렬은 (desc)
• select - from - where - orderby 순

---

# 데이터 베이스의 기능

1. 데이터 검색(select)과 데이터 갱신

• 새로운 데이터 등록 (INSERT)
• 기존 데이터 수정 (UPDATE)
• 기존 데이터 삭제 (DELETE)

2. 동시성 제어

3. 장애 대응

4. 보완

 

#관계형 데이터베이스: RDB(Relational Database), RDBMS(Relation Database Management System)

MySQL, Oracle, PostgreSQL 등이 모두 이 관계형 데이터베이스

---

해커랭크 문제풀이

# Revising the select query 1

select *
from city
where countrycode = 'USA' and 
      population > 100000

# select by id

select *
from city
where id = 1661

# weather observation station 6

select distinct city
from station
where city like 'a%' 
or city like 'e%'
or city like 'i%'
or city like 'o%'
or city like 'u%'

select distinct city
from station
where left(city, 1) in ('a', 'i', 'e', 'o', 'u')

select distinct city
from station
where substring(city, 1, 1) in ('a', 'i', 'e', 'o', 'u')

#weather observation station 12

select distinct city
from station
where left(city,1) not in ('a', 'i', 'e', 'o', 'u')
 and right(city, 1)  not in ('a', 'i', 'e', 'o', 'u')

select distinct city
from station
where city not like 'a%'
and city not like 'e%'
and city not like 'i%'
and city not like 'o%'
and city not like 'u%'
and city not like '%a'
and city not like '%e'
and city not like '%i'
and city not like '%o'
and city not like '%u'

# employee names

select name
from employee
order by name # 알파벳 순서대로 출력, alphabetical order

# employee salaries

select name
from employee
where months < 10 and salary > 2000
order by employee_id

# Higher Than 75 Marks

select name
from students
where marks > 75
order by right(name,3), id

select name
from students
where marks > 75
order by substr(name,-3), id

# weather observation station 15

select round(long_w,4)
from station
where lat_n in (
select max(lat_n)
from station
where lat_n < 137.2345
)

select round(long_w, 4)
from station
where lat_n < 137.2345 
order by lat_n desc
limit 1 -- order와 limit을 사용해서 최대 or 최소를 추출할 수 있음