Intro to Database (4) : Oracle SQL developer 2

2019-07-03

1) 새 사용자 만들기 

윈도 검색 -> cmd(도스 창) -> sqlplus -> 먼저 시스템 계정으로 로그인 -> 새 사용자 생성 by '비밀번호'까지 입력하면 아래 캡처와 같이 새로운 유저가 생성됩니다. 

 

2) 새 유저에게 권한 할당하기 

새 유저를 활용해서 작업을 하기 위해서는 새 유저에게도 시스템 권한을 할당해줘야 합니다 그 작업을 두 가지 방법으로 할 수 있는데 크게 (1) SQL Developer 에서 하기 (2) cmd에서 하기 두 가지 방법 존재합니다만 일단 여기서는 쉽고 빠른 (1) 번 방법을 사용해 권한을 할당하도록 하겠습니다.  

 

(1) Developer 에서 권한 부여하기 : 일단 시스템 유저로 접속합시다 

사용자 편집 - 시스템 권한 

 

두 개의 권한 옵션은 체크 빼주자 ! 

좌측에 탭 눌러서 다른 사용자에 방금 만든 유저 'jumo'의 시스템 권한에서 모두 체크한 후 사진에 체크한 2개의 옵션만 빼주도록 하겠습니다. ( SYSOPER, SYSDBA  ; 시스템 유저의 고유권한이기 때문에 필시 생성될 오류를 피하고자 함) 

3) 새 사용자에서 새로운 작업하기 : '학사 관리 시스템 만들기'

새로운 유저 Jumo가 생성되었다.

새로 생성된 유저 jumo에서 학사 관리 데이터 베이스를 만들려고 합니다. 일단 테이블 형식을 만들어주도록 하겠습니다. 테이블을 생성하고, 그 형식을 데이터들로 채워주고, SQL문을 작성해서 원하는 데이터들을 출력하는 코딩을 하는 순서로 진행하도록 하겠습니다. 

 

1) 학생 테이블 생성

 

 

 

 

 

 

 

 

 

아래와 같은 경로에서 만들어진 테이블을 확인할 수 있었습니다. 

생성된 STUDENT 테이블 

*SQL문을 작성할때, 소-대문자, ""기호 등에 대해서 엄격하진 않지만 타입이나 스페이스, 콤마 등에는 엄격하다.

 

 

2) 같은 방식으로 SUBJECT과 ENROL 테이블을 추가로 생성했습니다. 그런데 ENROL 테이블에서는 다른 테이블의 기본키를 참조하였다는 점이 앞의 두 테이블과 다른데, 그에 대한 코드를 확인하길 바랍니다. 

 

3) 데이터 입력하기 

먼저 STUDENT 테이블에 데이터를 넣어보았습니다. 

INSERT INTO STUDENT VALUES(20153075, '옥한빛', '기계', 1, 'C', 'M', 177, 80);
INSERT INTO STUDENT VALUES(20153088, '이태연', '기계', 1, 'C', 'F', 162, 50);
INSERT INTO STUDENT VALUES(20143054, '유가인', '기계', 2, 'C', 'F', 154, 47);
INSERT INTO STUDENT VALUES(20152088, '조민우', '전기전자', 1, 'C', 'M', 188, 90);
INSERT INTO STUDENT VALUES(20142021, '심수정', '전기전자', 2, 'A', 'F', 168, 45);
INSERT INTO STUDENT VALUES(20132003, '박희철', '전기전자', 3, 'B', 'M', NULL, 63);
INSERT INTO STUDENT VALUES(20151062, '김인중', '컴퓨터정보', 1, 'B', 'M', 166, 67);
INSERT INTO STUDENT VALUES(20141007, '진현무', '컴퓨터정보', 2, 'A', 'M', 174, 64);
INSERT INTO STUDENT VALUES(20131001, '김종헌', '컴퓨터정보', 3, 'C', 'M', NULL, 72);
INSERT INTO STUDENT VALUES(20131025, '옥성우', '컴퓨터정보', 3, 'A', 'F', 172, 63);

잘 들어가는것을 확인했다! 

#다른 두 테이블에도 데이터 삽입 진행 
INSERT INTO SUBJECT values('111', '데이터베이스', '이재영', 2, '컴퓨터정보');
INSERT INTO SUBJECT values('110', '자동제어', '정순정', 2, '전기전자');
INSERT INTO SUBJECT values('109', '자동차설계', '박민영', 3, '기계');
INSERT INTO SUBJECT values('101', '컴퓨터개론', '강종영', 3, '컴퓨터정보');
INSERT INTO SUBJECT values('102', '기계공작법', '김태영', 1, '기계');
INSERT INTO SUBJECT values('103', '기초전자실험', '김유석', 1, '전기전자');
INSERT INTO SUBJECT values('104', '시스템분석설계', '강석현', 3, '컴퓨터정보');
INSERT INTO SUBJECT values('105', '기계요소설계', '김명성', 1, '기계');
INSERT INTO SUBJECT values('106', '전자회로실험', '최영민', 3, '전기전자');
INSERT INTO SUBJECT values('107', 'CAD응용실습', '구봉규', 2, '기계');
INSERT INTO SUBJECT values('108', '소프트웨어공학', '권민성', 1, '컴퓨터정보');

INSERT INTO ENROL values('101', '20131001', 80);
INSERT INTO ENROL values('104', '20131001', 56);
INSERT INTO ENROL values('106', '20132003', 72);
INSERT INTO ENROL values('103', '20152088', 45);
INSERT INTO ENROL values('101', '20131025', 65);
INSERT INTO ENROL values('104', '20131025', 65);
INSERT INTO ENROL values('108', '20151062', 81);
INSERT INTO ENROL values('107', '20153054', 41);
INSERT INTO ENROL values('102', '20153075', 66);
INSERT INTO ENROL values('105', '20153075', 56);
INSERT INTO ENROL values('102', '20153088', 61);
INSERT INTO ENROL values('105', '20153088', 78);

 

4) 여러 가지 기본 SQL문을 작성하고 출력 결과를 확인하기 

(ctrl + enter 로 실행 가능합니다)

-- 전체 드래그 하고 Ctrl + / 하면 전체 주석처리가 됩니다! 
-- 1. 학생테이블의 구조를 검색하시요
desc STUDENT;
--2. 과목테이블의 구조를 검색하시요
DESC SUBJECT;
--3. 학생테이블의 모든 데이터를 검색하시요
SELECT * FROM STUDENT; 
--4. 수강테이블의 모든 데이터를 검색하시요
SELECT * FROM SUBJECT;
--5. 학생들의 학번과 이름을 검색하시요
SELECT STU_NO, STU_NAME FROM STUDENT;
--6. 과목들의 과목번호와 과목이름을 검색하시요
SELECT SUB_NO,SUB_NAME FROM SUBJECT;
--7. 학생들의 학번, 이름, 성별을 검색하시요
SELECT STU_NO, STU_NAME, STU_GENDER FROM STUDENT;
--8. 학생들의 모든 정보를 검색하시요
SELECT * FROM STUDENT;
--9. 학생들의 학번, 이름, 학년, 반을 검색하시요
SELECT STU_NO, STU_NAME, STU_GRADE,STU_CLASS FROM STUDENT;
--10. 과목들의 과목이름과 교수이름을 검색하시요
SELECT SUB_NAME, SUB_PROF FROM SUBJECT;
--11. 과목번호, 학번, 점수를 검색
SELECT * FROM ENROL;
--12. 학생들의 체중, 신장, 학번, 이름을 검색
SELECT STU_WEIGHT, STU_HEIGHT, STU_NO, STU_NAME FROM STUDENT;
--13. 학생들의 학과중복을 제거하고 검색 (distinct)
SELECT DISTINCT STU_DEPT FROM STUDENT; 
--14. 학생들의 성별중복을 제거하고 검색
SELECT DISTINCT STU_gender FROM STUDENT;
--15. 학생들의 학년중복을 제거하고 검색
SELECT DISTINCT STU_grade FROM STUDENT;
--16. 학생들의 학과, 학년 중복을 제거하고 검색
SELECT DISTINCT STU_DEPT, STU_grade FROM STUDENT;
--17. 학생들의 학과, 반 중복을 제거하고 검색
SELECT DISTINCT STU_DEPT, STU_CLASS FROM STUDENT;
--20. 학생들의 학번, 이름에 별칭 ‘학번’과 ‘이름’을 부여하고 검색
SELECT STU_NO 학번, STU_NAME 이름 FROM STUDENT;
--21. 수강테이블의 모든 정보를 검색. 별칭을 한글로 부여하기
select SUB_NO AS 수업번호, SUB_NAME AS 과목이름, SUB_PROF AS 이름, SUB_GRADE AS 성적,SUB_DEPT AS 학과 from SUBJECT;
--25. 컴퓨터정보학과 학생들을 검색
SELECT * from STUDENT where STU_DEPT = '컴퓨터정보';
--26. 기계과 학생들을 검색
SELECT * from STUDENT where STU_DEPT = '기계'; 
--27. 전기전자과 학생들을 검색
SELECT * from STUDENT where STU_DEPT = '전기전자'; 
--28. 신장이 170이상인 학생들을 검색
SELECT STU_NAME from STUDENT where STU_HEIGHT >= '170';
--29. 체중이 65이하인 학생들을 검색
SELECT STU_NAME from STUDENT where STU_WEIGHT <= '65';
--30. 기계과 학생들의 학번, 이름 검색
SELECT STU_NO, STU_NAME from STUDENT where STU_DEPT = '기계';
--31. 컴퓨터정보학과 학생들의 학번, 이름검색
SELECT STU_NO, STU_NAME from STUDENT where STU_DEPT = '컴퓨터정보'; 
--32. 전기전자과 학생들의 학번, 이름 검색
SELECT STU_NO, STU_NAME from STUDENT where STU_DEPT = '전기전자'; 
--33. 남학생들의 이름 검색
SELECT STU_NAME from STUDENT where STU_GENDER = 'M'; 
--34. 여학생들의 이름 검색
SELECT STU_NAME from STUDENT where STU_GENDER = 'F'; 
--35. 전기전자과 이외 학생들의 모든 정보 검색 
SELECT * from STUDENT where STU_DEPT NOT IN '전기전자'; 
SELECT * from STUDENT where STU_DEPT <> '전기전자'; 
SELECT * from STUDENT where STU_DEPT != '전기전자'; 
--36. 기계과 이외 학생들의 모든 정보 검색
SELECT * from STUDENT where STU_DEPT NOT IN '기계'; 
SELECT * from STUDENT where STU_DEPT <> '기계'; 
SELECT * from STUDENT where STU_DEPT != '기계'; 
--37. 점수가 80점 이상인 학생들의 학번 검색
SELECT STU_NO from ENROL where ENR_GRADE >= '80'; 
--38. ‘김인중‘ 학생의 모든 정보 검색
SELECT * from STUDENT where STU_NAME = '김인중'; 
--39. ‘컴퓨터정보’과 학생중 1학년 학생 검색
SELECT * from STUDENT where STU_DEPT = '컴퓨터정보'
AND STU_GRADE = '1';
--40. ‘기계＇과 이면서 2학년 학생들의 모든 정보 검색
SELECT * from STUDENT where STU_DEPT = '기계'
AND STU_GRADE = '2';
--45. 신장 160이상이며, 170이하인 학생들 학번과 이름검색
SELECT * from STUDENT where STU_HEIGHT <= '170' AND STU_HEIGHT >= '160';
--46!. 2013학번인 학생의 학번, 이름, 학과 검색
SELECT STU_NO ,STU_NAME, STU_DEPT FROM STUDENT WHERE STU_NO LIKE '2013%';
--48. 김씨성을 가진 학생들의 학번과 이름 검색
SELECT STU_NO ,STU_NAME FROM STUDENT WHERE STU_NAME LIKE '김%' 
--49. 학번이 xxxx20xx인 학생들의 모든 정보 검색
SELECT * FROM STUDENT WHERE STU_NO LIKE '____20%%' /*언더바쓰면 한글자씩 와일드문자*/
--51. 이름에 ‘옥＇이 들어있는 학생이름 
SELECT STU_NAME FROM STUDENT WHERE STU_NAME LIKE '%옥%' 
--52. 신장이 null인 학생이름 검색
SELECT STU_NAME from STUDENT where STU_HEIGHT IS NULL;
--53. 신장이 null이 아닌 학생들의 학번, 이름 검색
SELECT STU_NAME, STU_NO from STUDENT where STU_HEIGHT IS NOT NULL;
--54. 컴퓨터정보 또는 기계과인 학생들의 학번, 이름 검색
SELECT STU_NO ,STU_NAME FROM STUDENT WHERE STU_DEPT = '컴퓨터정보' 
OR STU_DEPT = '기계';  
--56. 학번, 이름을 학번순으로 검색
SELECT STU_NO, STU_NAME FROM STUDENT ORDER BY STU_NO;
--59. 학과별 학년이 높은 순으로 학생들의 정보를 검색
SELECT * FROM STUDENT ORDER BY STU_DEPT, STU_GRADE DESC;
-- 전체 드래그 하고 Ctrl + / 하면 전체 주석처리가 됩니다!