댕기

MS SQL에서 여러 테이블을 여러번 조회할 때 조회 조건을 변수로 선언하면 변수 값만 바꾸면 쉽게 조회 할 수 있는데 이때 declare를 사용한다. 

declare @c_id1 varcha(20)
set @c_id='id34'
select * from table where id =@c_id

그런데 오라클을 어떻게 되는 지 궁금해서 찾아보아 변경해보았다. 

DECLARE
    c_id1 VARCHAR2(20);
BEGIN
     c_id1 := 'id34';
     SELECT * FROM table_name WHERE id = c_id1;
END; /

그런데 서브 쿼리에 넣어서 해보니 안되고 구글 검색도 잘 안되어서 

그래서 chatGPT에 물어보았다

https://thepin.tistory.com/106

그분이 교육 전략을 잡기 위해서 직원들의 근속년수(년단위 소수점형태로 )을 달라고 하셨다. 그런데 자주사용하던 게 아니라 인터넷 서치해보니 datediff를 사용하면 된다고.. 생각해보면 날짜차이값 함수 찾고, 소수점 되는 방안 찾고, 1개 데이터 쿼리해보고, 전체 직원으로 조건 확대하는 순을 하면 될 것 같다. 

1) 날짜 차이값함수 : datediff  (함수 보기)

그런데 소수점이 안되네. 이런 기준을 월로 바꾸어 보면 

이를 12개월로 나누면..

앗 똑같네.  

2) 소숫점 방안 : convert (함수 설명)

평소 하던 convert로 씌워주었더니 

왜 소수점이 아니지..!!😕😕😕😕😕😕😕😕😕😕😕

아 형변환후 나누면 ??

참고로 int로 하면 결과 동일함

그런데 2년 직전 (즉 오늘 기준 20년 7월 12일)으로 보면 반올림해버리네 안되는데...

찾아보니 decimal이 낫다고 하시네 

아직 5일 남았는데 반올림 하네

3) 반올림 방지 및 버림 처리 (함수 설명)

4 ) 최종 쿼리

참조 

https://docs.microsoft.com/KO-kr/sql/t-sql/functions/datediff-transact-sql?view=sql-server-ver15 

https://docs.microsoft.com/ko-kr/sql/t-sql/functions/cast-and-convert-transact-sql?view=sql-server-ver15 

https://docs.microsoft.com/ko-kr/sql/t-sql/functions/round-transact-sql?view=sql-server-ver15 

7.1) 집합연산 (7-31강 P.274) : UNION, UNION ALL

두개이상 테이블 같이 볼때  --실행안됨

7.2) 테이블결합 (7-32강 P.284) : JOIN, LEFT JOIN

SELECT * FROM FILMS JOIN reviews ON FILMS.ID=REVIEWS.film_id

SELECT * FROM FILMS LEFT JOIN reviews ON FILMS.ID=REVIEWS.film_id

5.1) 집계 (5-20강 P.182) : COUNT, DISTINCT

테이블에 2000년 영화가 몇개나 되는지 , 영화 데이터에 몇개 나라가 있을까?

SELECT count(ID) from films where release_year >= 2000    select DISTINCT country from films 

5.2) 합평균최소최대갑 (5-21강 P.190) : SUM, AVG, MIN, MAX

영화데이터의 평균/최소/최대 평점은?

select sum(facebook_likes),avg(facebook_likes),min(facebook_likes),max(facebook_likes) from reviews

5.3) 집계 (5-22강 P.194) : GROUP BY, HAVING

영화데이터에 언어별 영화수는, 나라별 영화수는

select  language,count(id) from films group by language;  select  country,count(id) from films group by country;

집계함수에 조건 거는 방법 Having

select  release_year,count(id) from films group by release_year; select  release_year,count(id) from films group by release_year HAVING count(id) >3

5.4) 서브쿼리 (5-23강 P.204) : 

페이스북 likes 최대 받은 영화는?  -

5.4.1) facebook likes 최대 받은 영화의 film_id 찾기 : select film_id from reviews where facebook_likes = (select max(facebook_likes) from reviews

5.4.2) film_id로 영화찾기 : select * from films  where id = ()

등급 보류중인 영화의 나라별 갯수는?

select 
(SELECT count(*) from films) as films1,
(SELECT count(*) from reviews) as reviews1

5.5) 상관 서브쿼리 (5-24강 P.216) : EXISTS, NOT EXISTS, IN

SELECT country,count(*) from films
 where EXISTS (select country from films where country is not null) group by country

SELECT country,count(*) from films
 where not EXISTS (select country from films where country is  null) group by country;

페이스북 라이크 평균이상인 영화는 ?

SELECT * from films
where id IN ( 
select film_id from reviews where facebook_likes > (
SELECT avg(facebook_likes) from reviews));

4.1) 데이터 추가(4-16강 P.152) : INSERT INTO 테이블명 VALUES (값1,값2);

INSERT INTO FILMS VALUES ( 501,"기생충",2019,"KOREA",131,"KOREAN","15세관람가",15000000000,168163654*1190);

INSERT INTO FILMS VALUES ( 502,"Marriage Story",2019,"UK",137,"ENGLISH","15세관람가",323382,18600000);

INSERT INTO reviews VALUES ( 4969,,502,1022,295,8.0,174104,0);

4.2) DELETE (4-17강 P.160) : DELETE FROM 테이블명 WHERE 조건

DELETE FROM reviews WHERE ID =600;

4.3) UPDATE (4-18강 P.164) : UPDATE 테이블명 SET 컬럼=값 WHERE 조건

UPDATE REVIEWS SET imdb_score=8.6 WHERE film_id=501;

UPDATE REVIEWS SET num_user=40000000, num_critic=0, num_votes=62413,facebook_likes=62240 WHERE film_id=501;

데이터를 내맘대로 정렬한다.

3.1) 정렬 (3-9강 P94) : ORDER BY 열명 [ASC] DESC

  SELECT * FROM films  ORDER BY id

  SELECT * FROM  films ORDER BY id DESC

3.2) 다수 정렬 (3-10강 P.102)

  SELECT * FROM  films ORDER BY id , release_year;

  SELECT * FROM  films ORDER BY id DESC , release_year;

3.3) 특정 수만 조회(3-11강 P.108) :  LIMIT 숫자;

  SELECT * FROM films WHERE country="USA" LIMIT 2;

  SELECT * FROM films WHERE country<>'USA" ORDER BY id DESC LIMIT 4;

  SELECT * FROM films WHERE country<>'USA' ORDER BY id DESC LIMIT 4 OFFSET 2;

3.4) 사치연산 (3-12강 P.114) : + - / * %

  SELECT num_user + num_critic AS Fit1  FROM reviews ;

  SELECT num_user + num_critic AS Fit1 FROM reviews WHERE num_user > 600;

  SELECT num_user + num_critic AS fit1 FROM reviews WHERE num_user > 600 ORDER BY id DESC;

  SELECT num_user + num_critic AS fit1 FROM reviews WHERE num_user < 600 ORDER BY id DESC;

  SELECT imdb_score1 FROM reviews;

SELECT * FROM films WHERE duration /60 > 4;

SELECT duration /60,* FROM films WHERE duration /60 > 3 ORDER BY duration /60  DESC

3.5) 문자열 결합 (3-13강 P.130) : ||

SELECT title||country FROM films;

  SELECT substr(title,3) ||  " OF "||country FROM films;

  SELECT substr(title,3) ,trim(title) FROM films;

3.6) 날짜연산 (3-14강 P.136) : CURRENT_DATE,CURRENT_TIME,CURRENT_TIMESTAMP

SELECT CURRENT_DATE,CURRENT_TIME,CURRENT_TIMESTAMP

  쿼리 실행안됨 --SELECT CURRENT_DATE + INTERVAL 1 DAY

3.7) CASE문(3-15강 P.140) : CASE WHEN  조건 THEN 결과 ELSE 결과 END

SELECT CASE WHEN DURATION/60 > 3 THEN "LING MOVIE" END AS TYPE1,* FROM films;

데이터 조회는 from table에서 select로 조회한다.

SELECT * FROM Table명;

1.1) 단순조회(2-4강 P.52) : SELECT * FROM films

1.2) 테이블조회  DESC (안됨,2-5강 P.61) : DESC films

1.2.2) 자료형 : INTEGER형, CHAR형, VARCHAR형, DATE형, TIME형

1.3) 검색조건 넣고 조회(2-6강 P.65) : WHERE 조건

1.3.1) 조건과 일치 : SELECT * FROM films WHERE country="USA";

1.3.2) 조건과 다른 것 : SELECT * FROM films WHERE country<>"USA"

1.3.3) 널값인것과 아닌 것 : SELECT * FROM films WHERE  language IS NULL / IS NOT NULL

1.4) 조건 조합(7강 P.75) : WHERE 조건 AND 조건 , WHERE 조건 OR 조건 

  SELECT * FROM films WHERE country="USA" and language IS NULL

  SELECT * FROM films WHERE country='USA' or language IS NULL

1.5) 유사값 조회 (8강 P.83) : WHERE LIKE

SELECT * FROM films WHERE title like "REB%"                  SELECT * FROM films WHERE title NOT like "REB%"

SELECT * FROM films WHERE title like "%THE"                     SELECT * FROM films WHERE title like "%THE%" 

SELECT * FROM films WHERE title NOT like "%THE"              SELECT * FROM films WHERE title NOT like "%THE%" 

책에 나온 데이터를 다운 받았으나 에러가 나서 테이블 몇 개 만들고 포기

어차피 데이터 작아서 쿼리하기 불편하니 연습사이트 데이터를 사용하기로 결정

그런데 연습하던 사이트에서 데이터를 꺼낼 수 없어서 브라우저에 나온 결과값을 보고 테이블 생성하고 인서트문으로 데이터를 하나씩 넣다가, 가져오기 기능을 발견하고 드래그앤드랍하여 엑셀에 저장하고 다시 csv로 저장

게다가 데이터 가져올때 데이터 보고 테이블까지 만들어주네.

데이터 저장하고 공유도 가능

첫 실행화면

데이터 가져오기 - 메뉴: 데이터베이스 열기> 파일선택

데이터 보기

집에서 쿼리연습하려고 여러 군데 찾아보았지만 무료는 없고 교육사이트에서 SQL 쿼리를 할 수 있으나 insert등 특정쿼리는 유료수강해야 가능하고...

그러다 발견한 도구-DB Browser for SQLite

아래 설명에 있듯이 가상쉘이란다. 진짜 DBMS가 아니고 ..(하지만 간단한 테이블 생성/관리 및 각종 쿼리를 할수 있다) This program is not a visual shell for the sqlite command line tool, and does not require familiarity with SQL commands. It is a tool to be used by both developers and end users, and must remain as simple to use as possible in order to achieve these goals.

아래 사이트에서 최신 버전 다운 받아 설치하면 되며 윈도우는 설치이후 컴퓨터 리부팅한다. 맥은 없던데. (2020-2-16현재 3.11.2 최신). 각자 컴버전에 맞게 설치

설치하기

설치하면 선택한데로 메뉴와 바탕화면에 추가됨

삭제하기- 설치파일 재실행 또는 제어판 프로그램 기능/삭제