Python ⎜ haversine module 사용하여 좌표 사이 거리 구하기

create table artists
(
    id int not null auto_increment,
    name varchar(100) not null,
    primary key (id)
)


create table songs
(
    id int not null auto_increment,
    title varchar(100) not null,
    lyrics varchar(2000),
    artist_id int,
    primary key (id),
    foreign key (artist_id) references artists (id)
)

CAD DIUS

Insert

insert into artists (name) values ('BTS');
insert into songs (title, artist_id, lyrics) values ('all i need', 1, 'iam the next')
update artist set name='pink floyd' where id = 2;
delete from artists where name='새소년';
select title, lyrics from songs where lyrics like '%넘실넘실%'

JOIN

select artists.name, songs.title, songs.lyrics
from artists
join songs
on artists.id = songs.artist_id;

SELECT artists.id, artists.name, songs.title
FROM artists
JOIN songs
ON artists.id = songs.artist_id
WHERE artists.name = '새소년'; 

SELECT artists.id, artists.name, songs.title
FROM artists
JOIN songs
ON artists.id = songs.artist_id
WHERE songs.lyrics LIKE '%you%';

SELECT artists.id, artists.name, songs.title
FROM artists
JOIN songs
ON artists.id = songs.artist_id
WHERE songs.lyrics LIKE '%you%' OR songs.lyrics LIKE '%i%'; 

select

여러 기준으로 정렬하기

SELECT animal_id, name, datetime
from animal_ins
order by name asc, datetime desc

어린동물찾기

SELECT animal_id, name
from animal_ins
where intake_condition != 'Aged'

상위 n개 레코드

SELECT name from animal_ins
order by datetime
limit 1;

sum, max, min

최댓값 구하기

SELECT datetime from animal_ins
order by datetime desc
limit 1

최소값 구하기

SELECT datetime from animal_ins
order by datetime
limit 1

동물 수 구하기

정답1
select count(*) from animal_ins

정답2
select count(animal_id) from animal_ins

정답x
select count(name) from animal_ins
name에 null인 행은 제외하고 count하게 되서 정확하지 않다.

중복 제거하기

SELECT count(distinct name) from animal_ins

고양이와 개는 몇 마리 있을까

SELECT animal_type, count(animal_type) as count
from animal_ins
group by animal_type
order by animal_type

group by가 없으면 animal_type 별로 구분되지 않고 전체의 count가 출력된다.

동명 동물 수 찾기

SELECT name, count(name) as count
from animal_ins
group by name having count(name) > 1
order by name

입양 시각 구하기

#입양1

SELECT HOUR(DATETIME) HOUR, COUNT(DATETIME) COUNT
FROM ANIMAL_OUTS
GROUP BY HOUR(DATETIME)
HAVING HOUR >= 9 and HOUR < 20
order by hour(datetime)


# 입양2
SET @hour := -1;

SELECT (@hour := @hour + 1) as HOUR,
(SELECT COUNT(*) FROM ANIMAL_OUTS WHERE HOUR(DATETIME) = @hour) as COUNT
FROM ANIMAL_OUTS
WHERE @hour < 23

---

---

---