[DB] SQL - (2)

sql문 실행시 내부적으로 튜플이 움직이는 구조를 해석할 수 있어야한다. 

구조를 이해하는지 못하는지 테스트 할거다.

IN의 부속 질의를 보았다.

select s.sname from students s, enrol e where s.sno = e.sno and e.cno  = 'C413'; 

 

C413의 과목을 등록하지 않은 학생의 이름을 구하라.(JOIN으로 구할 수 없다.) 

 

IN, NOT IN , JOIN을 잘 구분하자 (** 중간고사 시험 **)

IN은 JOIN이랑 똑같은 의미이다.

 

정기태학생과 같은과 학생의 이름과 학과를 검색하라.

이것은 조인으로도 가능하다.

select s1.sname , s1.dept from students s1, students s2 where s1.dept = s2.dept and s2.sname = '정기태'; 

--> 이 결과는 정기태가 나온다. 

select s1.sname , s1.dept from students s1, students s2 where s1.dept = s2.dept and s2.sname = '정기태' and s1.sno <> s2.sno; 

--> 이 결과는 정기태가 안나온다. 

--> <> 연산자는 sql에서 != 이다. 

 

과목번호 DB를 수강하지만 'OS'를 수강하지 않는 학생들의 이름과 학과를 검색하는 검색 sql 질의문을 작성하여라. 

부속 질의로 만들어보자.  

SELECT s.sname , s.dept FROM students s WHERE sno NOT IN(

SELECT sno FROM enrol WHERE cno = 'OS' ) AND sno IN (SELECT sno FROM enrol WHERE cno='DB'); 

 

ALL연산 : 모든 튜플들을 말하는 것이다. 

학번이 500인 학생의 모든 기말 성적보다 좋은 학기말 성적을 받은 학생의 학번과 과목번호를 검색하라

SELECT Sno, Cno FROM ENROL WHERE Final > ALL (SELECT Final FROM ENROL WHERE Sno = 500);

 

DB를 수강한 학생보다 더 좋은 기말고사 성적을 받은 학생의 학번과 과목번호를 검색하라. 

SELECT Sno, Cno FROM ENROL WHERE Final > ALL (SELECT Final FROM ENROL WHERE Sno = 500);

 

OS를 수강하는 학생중에서 기말고사 성적이 DB를 수강한 학생의 기말고사 성적보다 큰 학생의 이름과 학과를 검색하라. 

SELECT   s.sname, s.dept FROM students  s WHERE final > ( SELECT final FROM enrol WHERE cno = 'DB') AND sno IN (SELECT cno FROM enrol WHERE cno = 'OS'); 

 

DB를 수강한 학생중에서 400번 학생의 기말고사 성적보다 더 좋은 성적을 받은 학생의 학생번호, 과목번호, 기말고사를 뽑아라. 

SELECT sno, cno, final FROM enrol WHERE Final > ALL ( SELECT final FROM enrol WHERE sno= 400) AND sno  IN (SELECT sno FROM enrol WHERE cno = 'DB');

--> DB를 수강한 이력이 있는 학생들에 대해서 400번학생보다 모든 과목의 기말고사보다 잘 맞은 경우에 골라낸다. 

--> DB과목에 대해서만 검색한다면,

SELECT sno, cno, final FROM enrol WHERE Final > ALL ( SELECT final FROM enrol WHERE sno= 400) AND sno  IN (SELECT sno FROM enrol WHERE cno = 'DB') AND cno= '95';

 

== SELECT sno, cno, final FROM enrol WHERE Final > ALL AND cno= '95';

 

** 과제를 꼭 중간고사 전에 풀어봐라. 

---

'DB'

WHERE sname like '김%'; --> 김~으로 시작하는 모든 이름을 출력한다. 

WHERE sname like '김_'; --> 외자 인경우 김0 씨를 검색하라. 

WHERE sname like '%김%'; --> 이름에 김이라는 글자를 포함한 사람을 검색하라. 

WHERE sname like '__김%'; --> 이름에 김이라는 글자를 포함한 사람을 검색하라. 

 

select * FROM students WHERE sname like '%2';

--> Mike2가 나온다. 

select * from course where dept like '%공학%;

--> 컴퓨터 공학과가 출력된다.

select * from course where cno like 'D_'; 

--> D로 시작하는 과목코드 두자리(D포함) 'DB'를 출력한다. 

 

NULL 을 사용한 검색 

- 값이 채워지지않았을때 NULL을 사용한다. 

WHERE dept IS NULL ; 

WHERE dept = '';  ==> 이 둘은 다르다.

---

EXISTS / NOT EXISTS 

과목번호가 'C413'에 등록한 학생이 있는지 검색하라. 

SELECT s.sname FROM students s WHERE EXISTS ( SELECT * FROM ENROL e WHERE s.sno = e.sno AND e.cno = 'C413');

괄호안에 하나라도 리턴이 되면 이 결과는 true이다 .

SELECT s.sname FROM students s WHERE EXISTS (SELECT 1  FROM enrol e WHERE e.sno = s.sno AND e.cno  = 'DB'); 

==> DB를 수강한 Mike1, Mike2, Mike3가 나온다. 

이 문장을 조인으로도 바꿔보자 . 

DB를 수강하는 학생의 이름을 검색하라는 쿼리문이다. EXISTS의 결과는 참또는 거짓이다.

이 학생이 DB를 들었느냐를 판별하는 쿼리이다.

== SELECT s.sname FROM  students s WHERE sno IN (select sno from enrol where cno = 'DB'); 

== SELECT s.sname FROM students s, enrol e WHERE s.sno = e.sno and e.cno = 'DB';

 

공집합일 때, 공집합을 만족하는 학생의 sname을 출력하라.

SELECT s.sname FROM STUDENT WHERE NOT EXISTS (SELECT * FROM enrol e WHERE e.sno = s.sno AND e.cno = 'C413'); 

 

SELECT s.sname FROM students s WHERE EXISTS (SELECT 1  FROM enrol e WHERE e.sno = s.sno AND e.cno  = 'DB'); 

==> DB를 수강하지 않은 Mike4가 나온다. 

 

*아닌 예

SELECT * FROM students , enrol e where s.sno = e.sno and e.cno <> 'DB'

DB가 아닌 과목을 수강한 학생을 검색하라이다. DB를 수강했는지 안했는지 여부는 검색하지 않음 

<>는 붙여써야된다. 

---

UNION 두 테이블의 결과를 합치지만 중복된 값을 제거 

SELECT sno FROM students where year = 2 union select sno from enrol where cno = 'DB';

SELECT sno from students s, enrol e  where ( s.year = 2 or e.cno = 'DB') and s.sno = e.sno ;

왜 괄호가 있는가? 괄호가 우선순위를 정한다. 

조인은 중복을 허용하기 때문에 중복을 제거하기 위해 distinct 를 붙인다. 

SELECT distinct  sno from students s, enrol e where ( s.year = 2 or e.cno = 'DB') and s.sno = e.sno ;

select distinct s.sno from students s join enrol e on (s.sno = e.sno) where s.year = 2 or e.cno = 'DB'; 

 

union은 편리할 때가 있다.

 

여기까지가 검색이였고 이제 갱신이다. 

UPDATE students set year = year + 1 where dept = 'SW'; 

UPDATE course SET credit = credit + 1 where dept = '컴퓨터';  

 

 

 

 

 

(시험문제에서 아래는 제외) 

---

join update (조인업데이트) 

소프트웨어학과 학생들의 기말고사 성적을 10점씩 올려라. 

update enrol e join students s on (e.sno = s.sno) set e.final  = e.final + 1  where s.dept = 'SW' ;

update enrol e join students s on (e.sno = s.sno) set e.final  = e.final + 1  where s.sno = 100 ; 

 

update e from enrol e, student s  set e.final = e.final + 10  where s.sno = 100 and e.sno = s.sno;