Step17. 기본 SQL

--테이블 작성

create table person(

name varchar2(20),

age number

);

--데이터 삽입

insert into PERSON values('남상규',29);

insert into PERSON(name,age) values('철수1',21);

insert into PERSON(name,age) values('철수2',22);

insert into PERSON(name,age) values('철수3',23);

insert into PERSON(name,age) values('철수4',34);

insert into PERSON(name,age) values('철수5',35);

insert into PERSON(name,age) values('철수6',46);

--데이터 출력

select * from PERSON;

select name from PERSON;

--업데이트

update person set name='바꿈',age=0 where age >= 40;

--삭제

delete from PERSON where age >= 40

--학생 테이블 관련 SQL문

create table student(

no number primary key,

name varchar2(20),

major varchar2(40),

score number

)

delete from STUDENT;

insert into STUDENT values(20071010,'철일','경영',99);

insert into STUDENT values(20071011,'철이','경제',89);

insert into STUDENT values(20071012,'철삼','게임',88);

insert into STUDENT values(20071013,'철사','교육',76);

insert into STUDENT values(20071014,'철오','경영',78);

insert into STUDENT values(20071015,'철육','경제',76);

insert into STUDENT values(20071016,'철칠','게임',45);

insert into STUDENT values(20071017,'철팔','교육',35);

insert into STUDENT values(20071018,'철구','경영',88);

insert into STUDENT values(20071019,'철십','경제',97);

insert into STUDENT values(20071020,'영일','게임',64);

insert into STUDENT values(20071021,'영이','교육',24);

insert into STUDENT values(20071022,'영삼','경영',80);

insert into STUDENT values(20071023,'영사','경제',70);

insert into STUDENT values(20071024,'영오','게임',66);

insert into STUDENT values(20071025,'영육','교육',96);

insert into STUDENT values(20071026,'영칠','경영',95);

insert into STUDENT values(20071027,'영팔','경제',50);

insert into STUDENT values(20071028,'영구','게임',90);

insert into STUDENT values(20071029,'영영','게임',100);

select * from student where score between 90 and 100

select * from student where score >= 90 and score <= 100

select * from student where name like '%구'

select * from student where name like '철%'

--그룹함수

select major,avg(score) as 평균 from STUDENT group by major;

select major,sum(score) as 총합 from STUDENT group by major;

select major,count(*) as 총인원 from STUDENT group by major;

select major,avg(score) from STUDENT where major = '경영' group by major

--정렬

select no,name,major,score from student order by score desc; --내림차순

select * from student order by score asc; -- 오름차순

--레코드에 행번호 표시하기

select rownum as 행번호, no, name,major,score from student order by score desc;

--바꾼후

select rownum as 행번호, no, name,major,score from (select * from student order by score desc);

--성적 순위로 표시하기

select  RANK() OVER (ORDER BY score DESC ) as 순위, no,name,major,score from student order by score desc;

** 제품 프로젝트 **

create table product(

no number primary key,

name varchar2(30) not null,

price number not null,

maker varchar2(30) not null,

ea number

);

create table deal(

no number primary key,

pno number,

ea number,

price number

);

drop sequence dealno;

drop sequence pno;

--시퀀스 : 자동으로 번호 매겨줌

create sequence dealno start with 0 increment by 1 minvalue 0 maxvalue 200000

create sequence pno start with 0 increment by 1 minvalue 0 maxvalue 200000

insert into PRODUCT values(pno.nextval,'OptimusG2',2500,'LG',20); --0

insert into PRODUCT values(pno.nextval,'OptimusGPro',3000,'LG',20);--1

insert into PRODUCT values(pno.nextval,'Nexus5',2900,'LG',20);--2

insert into PRODUCT values(pno.nextval,'OptimusG',2000,'LG',20);--3

insert into PRODUCT values(pno.nextval,'GaraxyNote',1500,'SAMSUNG',20);--4

insert into PRODUCT values(pno.nextval,'GaraxyNote2',2500,'SAMSUNG',20);--5

insert into PRODUCT values(pno.nextval,'GaraxyS3',2500,'SAMSUNG',20);--6

insert into PRODUCT values(pno.nextval,'GaraxyS4',3500,'SAMSUNG',20);--7

insert into PRODUCT values(pno.nextval,'VegaIron',3000,'SKY',20);--8

insert into PRODUCT values(pno.nextval,'VegaNote',3500,'SKY',20);--9

insert into PRODUCT values(pno.nextval,'VegaM6',3000,'SKY',20);--10

insert into PRODUCT values(pno.nextval,'VegaSecretNote',3500,'SKY',20);--11

insert into deal values(dealno.nextval,2,3,3*(select price from product where no = 2));

insert into deal values(dealno.nextval,1,4,4*(select price from product where no = 1));

insert into deal values(dealno.nextval,2,3,3*(select price from product where no = 2));

insert into deal values(dealno.nextval,10,2,3*(select price from product where no = 10));

insert into deal values(dealno.nextval,11,3,3*(select price from product where no = 11));

insert into deal values(dealno.nextval,2,3,3*(select price from product where no = 2));

insert into deal values(dealno.nextval,6,3,3*(select price from product where no = 6));

select * from PRODUCT

select * from deal

--제조사별 판매액

select maker, sum(deal.price) from deal, PRODUCT where DEAL.pno = PRODUCT.no  group by maker

select * from deal, PRODUCT where DEAL.pno = PRODUCT.no order by DEAL.no asc;

delete from deal

delete from product 

------

--table 생성

create table member(

name varchar2(20),

age number,

location varchar2(20)

);

--table 삭제

drop table member;

--데이터 삽입

insert into member(name, age, location) values('홍길동',20,'서울');

insert into member(name, age, location) values('영수',82,'경기');

insert into member(name, age, location) values('철수',88,'경남');

insert into member(name, age, location) values('영희',77,'전남');

insert into member(name, age, location) values('샘',44,'충주');

insert into member(name, age, location) values('토니',23,'대구');

insert into member(name, age, location) values('철일',15,'서울');

insert into member(name, age, location) values('철이',16,'강원');

insert into member(name, age, location) values('철삼',20,'춘천');

insert into member(name, age, location) values('철사',35,'부산');

insert into member(name, age, location) values('철오',34,'부산');

insert into member values('철육',25,'충주');

insert into member values('철칠',30,'충주');

insert into member values('육철육',25,'충주');

insert into member values('육철',25,'충주');

--전체 데이터 조회

select name, age, location from member;

select * from member;

--서울에 사는 회원만 검색

select * from member where location = '서울';

--서울이나 부산에 사는 회원만 검색

select * from member where location = '서울' or location = '부산';

select * from member where location in('서울','부산');

--이름에 앞글자가 '철'인 회원만 검색

select * from member where name like '철%';

--이름에 마지막글자가 '철'인 회원만 검색

select * from member where name like '%철';

--이름에 글자가 '철'이 포함된 회원만 검색

select * from member where name like '%철%';

--나이가 20대인 회원만 검색

select * from member where age >= 20 and age < 30;

select * from member where age between 20 and 29;

--테이블에 있는 모든 레코드 삭제

delete from member;

--사는 곳이 충주인 회원만 삭제

delete from member where location = '충주';

--사는 곳이 서울인 회원을 사는 곳을 Seoul로 업데이트

update member set location='Seoul' where location = '서울';