sqlite3 실습 - SELECT 활용

지난번에, sqlite3를 설치 한 후, 메모장을 이용하여 DB table을 구성하도록 하였습니다.

(http://jwprogramming.tistory.com/90)

오늘은 DB의 여러 질의문(검색, 삽입, 삭제, 수정) 중에 '검색(SELECT)' 부분을 다루도록 하겠습니다.

1. 학생(STUDENT) 테이블에 어떤 학과(Dept)들이 있는지 검색하라.

->먼저, test.db라는 Sqlite3 데이터베이스 파일명으로 지정하여 sqlite3를 실행합니다.

그 후, 사용자가 보기 편하도록 하기 위하여 아래의 명령을 입력해 주면 기존 Oracle의 인터페이스와 유사하게 표현됩니다.

>.header on

>.mode column

그 후, SELECT DISTINCT Dept FROM STUDENT; 명령을 통하여 중복된 결과는 제거하고(DISTINCT) STUDENT테이블로부터 Dept를 전부 출력해줍니다.

결과로 보면 컴퓨터, 전기, 산공이 표현되는 것을 알 수 있습니다.

2. 학생(STUDENT) 테이블 전부를 검색하라.

전부라는 키워드는 ' * '로써 표현할 수 있습니다. 

따라서 SELECT * FROM STUDENT; 

를 통하여 STUDENT테이블의 전부를 확인할 수 있습니다.

  

3. 학생(STUDENT) 테이블에서 학과(Dept)가 ‘컴퓨터’이고 학년(Year)이 4인 학생의 학번(Sno)과 이름(Sname)을 검색하라.

- DB의 검색에서 조건에 대한 부분은 WHERE 절을 통해 설정할 수 있습니다.

Dept = '컴퓨터' -> 학과가 컴퓨터를 전부 조회

Year = 4  -> 학년이 4 를 전부 조회

-> AND조건이므로 둘 다 만족하는 데이터를 검색하게 되면, 결과로 나수영과 송병길이 해당되고,

그에 대한 학번과 이름을 조회해야하므로 

SELECT Sno, Sname FROM STUDENT WHERE Dept='컴퓨터' AND Year=4; 

로 학번과 이름만을 출력하게 합니다.

 

4. 등록(ENROL) 테이블에서 중간 성적(Midterm)이 90점 이상인 학생의 학번(Sno)과 과목번호 (Cno)를 검색하되 학번(Sno)에 대해서는 내림차순으로, 또 같은 학번에 대해서는 과목 번호 (Cno)의 오름차순으로 검색하라.

- WHERE절까지는 위의 부분과 유사하므로 생략하도록 하겠습니다. 추가적으로 살펴볼 부분은 ORDER BY 를 통하여 내림차순으로 볼 것인지, 오름차순으로 볼 것인지에 대한 부분입니다.

Sno는 내림차순으로, Cno는 오름차순으로 검색할 것이므로

ORDER BY Sno DESC, Cno ASC;

(DESC -> 내림차순,  ASC -> 오름차순)

을 통하여 데이터를 보여주게 됩니다.

>SELECT Sno, Cno FROM ENROL WHERE Midterm >= 90 ORDER BY Sno DESC, Cno ASC;

5. 등록(ENROL) 테이블에서 과목 번호(Cno)가 ‘C312'인 중간 성적(Midterm)에 3점을 더한 점수를 ’학번‘, ’중간성적 = ‘이란 텍스트 내용을 ’시험‘, 그리고 ’점수‘ 라는 열 이름으로 검색하라.

- AS란 앨리어스를 사용할 때 이용하게 됩니다.

SELECT 필드 AS A FROM 테이블; 과 같은식으로 사용하게 되며

이때, 문제에서 Cno가 'C312'이라는 것이 조건이기 때문에,

WHERE Cno='C312' 가 됩니다.

Sno를 학번으로, 바꿔준 후, 두번째 칼럼에는 시험, 세번째 칼럼은 점수로 AS를 통해 설정을 해줍니다.

그 후 각각의 값은 Sno는 그대로, 시험 칼럼에는 '중간시험 =' 을, 점수칼럼에는 Midterm의 값 + 3 을 해준 상태로 검색을 하게 해주는 부분입니다.

SELECT Sno AS 학번, '중간시험 =' AS 시험, Midterm+3 AS 점수 FROM ENROL WHERE Cno='C312';

6. 과목번호(Cno) 'C413'에 등록한 학생의 이름(Sname), 학과(Dept), 성적(Grade)를 검색하라.

- 이번에는 테이블에 대한 제한이 없습니다. 즉 과목번호 'C413'에 해당한다면 STUDENT건, ENROL이건 관계없이 전부 출력해주어야 하는 부분입니다.

따라서 FROM STUDENT S, ENROL E 라 하여 STUDENT테이블의 데이터에 대해서는 S.xxx로, ENROL테이블의 데이터에 대해서는 E.xxx 로 접근하게 됩니다.

이름,학과,성적에 대한 검색이므로 SELECT S.Sname, S.Dept, E.Grade 이며,

조건으로 설정된 부분 S.Sno = E.Sno AND E.Cno='C413'; 을 살펴보겠습니다.

WHERE 조건절을 이용하여 STUDENT테이블과 ENROL테이블 사이에 S.Sno = E.Sno 의 관계를 맺어주고, 

ENROL테이블 내에 과목번호가 있으므로( STUDENT테이블에는 없습니다!), E.Cno = 'C413'; 부분을 추가해줍니다.

>SELECT S.Sname, S.Dept, E.Grade FROM STUDENT S, ENROL E WHERE S.Sno = E.Sno AND E.Cno='C413';

 

7. 같은 학과 학생들의 학번을 쌍으로 검색하라. 단, 첫 번째 학번은 두 번째 학번보다 작게 하라.

- 어려운 내용이 아니므로 위의 부분들을 참고하면 이해할 수 있을것이라 생각되어 설명은 생략하도록 하겠습니다.

 

8-1. 학생 테이블에 학생 수가 얼마인가를 검색하라.

- 단순히 COUNT 키워드를 이용하여 학생수가 얼마인지를 검색하는 부분입니다.

 8-2. 학번(Sno)이 300인 학생이 등록한 과목(Cno)은 몇 개인가?

 

- 위의 내용들과 중복되므로 생략합니다.

 

 

8-3. 과목 ‘C413'에 대한 중간 성적의 평균은 얼마인가?

- AVG키워드를 이용하여 중간성적의 평균을 계산해주는 부분입니다.

9. 과목별 기말성적(Final)의 평균을 검색하라.

-  과목별에 대하여 GROUP BY 절을 이용하여 검색하는 부분입니다.

GROUP BY절은 데이터들을 원하는 그룹으로 나눌 수 있고,

나누고자 하는 그룹의 컬럼명을 SELECT절과 GROUP BY절 뒤에 추가하면 됩니다.

집계함수와 함께 사용되는 상수는 GROUP BY절에 추가하지 않아도 됩니다.

 

10. 3명 이상 등록한 과목의 기말 평균 성적을 검색하라.

- HAVING 절을 이용하여 3명 이상 등록한 과목에 대하여 기말평균 성적을 검색하는 부분입니다.

HAVING절은 WHERE절에서는 집계함수를 사용할 수 없으므로, GROUP BY절과 함께 사용이 되며 집계함수를 가지고 조건비교를 할 때 사용하게 됩니다. 

 

 

11-1. 과목 번호(Cno) 'C413' 등록한 학생이름(Sname)을 검색하라.

- 조건절안에 SELECT가 삽입된 중첩 SELECT 문입니다. 복잡해보이지만 해석하면 단순한 내용이므로 생략하도록 하겠습니다.

이때, 조건절에 SELECT문을 추가로 해주기 위해서 WHERE Sno IN 을 사용하는 것에 주의하시면 되겠습니다.

 

11-2. 과목 번호 ‘C413'에 등록하지 않은 학생의 이름을 검색하라.

- 위와 유사한 내용입니다. 생략합니다.

11-3. 학생 ‘정기태’와 같은 학과에 속하는 학생의 이름과 학과를 검색하라.

- 위와 유사한 내용이므로 생략합니다.

 

11-4. 등록(ENROL) 테이블에서 학번이 500인 학생의 모든 기말 성적보다 좋은 학기말 성적을 받은 학생의 학번과 과목번호를 검색하라.

 

12. 과목번호(Cno)가 C로 시작하는 과목의 과목번호와 과목이름(Cname)을 검색하라.

- C로 시작하는 부분에 대하여 LIKE 키워드를 이용하여 'Cx' 와 같이 설정해주면, x에 대한 부분에 관계없이 과목번호(Cno)중에 첫글자가 C로 시작되는 과목번호는 전부 검색하게 됩니다.

 

13. 학과(Dept)가 NULL인 학생의 학번과 이름을 검색하라.

------> Dept(학과)가 NULL인 학생이 없으므로 아무 값도 검색되지 않습니다.(아래 그림은 참고)

따라서 학생테이블에 학번이 600, 이름이 ‘김길동’, 학과가 NULL인 투플을 삽입하고 다시 검색하면,

아래와 같이 원하는 화면을 검색할 수 있게 됩니다.

 

 

14-1. 과목 ‘C413'에 등록한 학생의 이름을 검색하라.

- 과목번호 'C413'에 등록한 학생을 검색하기 위한 조건으로 EXISTS를 사용하였으며 

WHERE조건절 안의 SELECT문에서, Sno = STUDENT.Sno 를 통해 밖의 STUDENT테이블과 ENROL테이블의 관계를 맺어주고, Cno='C413'과 AND조건으로 묶어주면 됩니다.

 

14-2. 과목 ‘C413'에 등록하지 않은 학생의 이름을 검색하라.

- 등록되지 않은 부분은 EXITSTS앞에 NOT을 붙여주어 NOT EXISTS로 조건을 정해주면 됩니다.

 

 

15. 3학년이거나 또는 과목 'C324'에 등록한 학생의 학번을 검색하라.

- UNION을 이용하여 SELECT문 두 개를 '또는' 으로 이어주게 됩니다.

 

다음에는 UPDATE에 대해 다뤄보겠습니다.