You can not select more than 25 topics
Topics must start with a letter or number, can include dashes ('-') and can be up to 35 characters long.
71 lines
2.9 KiB
71 lines
2.9 KiB
library(rJava)
|
|
library(RJDBC)
|
|
con <- dbConnect(JDBC(driverClass = "oracle.jdbc.OracleDriver",
|
|
classPath = "/home/ducj/설정파일/ojdbc11-full/ojdbc11.jar"),
|
|
"jdbc:oracle:thin:@ducj3.iptime.org:1521/xe",
|
|
"ducj", Sys.getenv('db_key'))
|
|
|
|
RJDBC::dbReadTable(con,'PLAYER')
|
|
|
|
data <- dbGetQuery(con, query)
|
|
|
|
####################
|
|
"
|
|
비교연산자
|
|
= , > , <
|
|
SQL 연산자
|
|
BETWEEN a AND b, IN, LIKE , IS NULL
|
|
논리연산자
|
|
AND, OR, NOT
|
|
부정비교연산자
|
|
!=, ^=, <> NOT 칼럼명 =, NOT 칼럼명 >
|
|
부정 SQL 연산자
|
|
NOT BETWEEN a AND b, NOT IN , IS NOT NULL
|
|
|
|
연산자 우선순위
|
|
괄호 > NOT > 비교연산자 > AND > OR
|
|
"
|
|
|
|
"Q1 : 소속팀이 삼성블루윙즈거나 전남드래곤즈에 소속된 선수들이어야하고
|
|
포지션이 미드필더이어야한다. 키는 170이상 180이하이어야한다."
|
|
|
|
#소속팀(TEAM_ID)이 삼성블루윙즈(K02)
|
|
dbGetQuery(con,"select * from player where TEAM_ID='K02'")
|
|
#소속팀이 전남드래곤즈(K07)
|
|
dbGetQuery(con,"select * from player where TEAM_ID='K07'")
|
|
#소속팀이 K02나 K07
|
|
dbGetQuery(con,"select * from player where TEAM_ID in ('K07','K02')")
|
|
|
|
#포지션(POSITION)이 미드필더(MF)
|
|
dbGetQuery(con,"select * from player where POSITION='MF'")
|
|
dbGetQuery(con,"select * from player where POSITION in 'MF'")
|
|
#소속팀이 K02나 K07이고 포지션(POSITION)이 미드필더(MF)
|
|
dbGetQuery(con,"select * from player where TEAM_ID in ('K07','K02') and POSITION in 'MF'")
|
|
|
|
#키는 170이상이고 180 이하
|
|
dbGetQuery(con,"select * from player where HEIGHT>=170 and HEIGHT<=180")
|
|
|
|
#소속팀이 K02나 K07이고 키는 170이상이고 180 이하이며 포지션(POSITION)이 미드필더(MF)
|
|
dbGetQuery(con,"select PLAYER_NAME 선수이름 from player where (
|
|
TEAM_ID in ('K07','K02') and POSITION LIKE 'MF')
|
|
and (HEIGHT>=170 and HEIGHT<=180)")
|
|
|
|
#소속팀이 K02나 K07이고 키는 170이상이고 180 이하이며 포지션(POSITION)이 미드필더(MF)인 장씨
|
|
dbGetQuery(con,"select * from player where (
|
|
(TEAM_ID in ('K07','K02') and POSITION ='MF')
|
|
and (HEIGHT>=170 and HEIGHT<=180))and PLAYER_NAME like '정%'")
|
|
|
|
dbGetQuery(con,"select * from player where (
|
|
(TEAM_ID in ('K07','K02') and POSITION ='MF') and
|
|
(HEIGHT between 170 and 180) and PLAYER_NAME like '정%')")
|
|
|
|
|
|
"Q2 : 사원 테이블에서 JOB이 MANAGER이면서 20번 부서에 속하거나, JOB이 CLERK이면서
|
|
30번 부서에 속하는 사원의 정보를 IN 연산자의 다중 리스트를 이용해 출력하여라
|
|
-> 문제 수정 : JOB이 MANAGER이거나 CLERK이고 DEPTNO가 20 혹은 30
|
|
"
|
|
dbGetQuery(con,'select * from emp where ROWNUM = 1')
|
|
dbGetQuery(con,"select * from emp where (job='MANAGER' and DEPTNO=20)
|
|
or (job=='CLERK' and DEPTNO=30)")
|
|
dbGetQuery(con,"select * from emp where job in ('MANAGER','CLERK') and DEPTNO in (20,30)")
|
|
|
|
|