기록해! 정리해!

6-예제1(shopping) 본문

Oracle

6-예제1(shopping)

zsuling 2022. 5. 23. 11:21

5/23

 

1) 정규화 113p

 

create table userTBL(
  userName nchar(3) primary key,
  birthYear int not null,
  addr nchar(2) not null,
  mobile varchar2(12)
);

create table buyTBL(
userName nchar(3) not null REFERENCES userTBL(userName),
prodName nchar(3) not null,
price int not null,
amount int not null
);



insert into userTBL (userName, birthyear, addr, mobile) values ('이승기', 1987, '서울', '011-111-1111');
insert into userTBL (userName, birthyear, addr, mobile) values ('김경호', 1971, '전남', '019-333-3333');
insert into userTBL (userName, birthyear, addr, mobile) values ('윤종신', 1969, '경남', '안 남김');
insert into userTBL (userName, birthyear, addr, mobile) values ('임재범', 1963, '서울', '016-666-6666');
insert into userTBL (userName, birthyear, addr, mobile) values ('은지원', 1978, '경북', '011-888-8888');
insert into userTBL (userName, birthyear, addr, mobile) values ('조관우', 1965, '경기', '018-999-9999');
insert into userTBL (userName, birthyear, addr, mobile) values ('김범수', 1979, '경남', '011-222-2222');
insert into userTBL (userName, birthyear, addr, mobile) values ('조용필', 1950, '경기', '011-444-4444');
insert into userTBL (userName, birthyear, addr, mobile) values ('바비킴', 1973, '서울', '010-000-0000');
insert into userTBL (userName, birthyear, addr, mobile) values ('성시경', 1979, '경남', '안 남김');

insert into buyTBL(userName, prodName, price, amount ) values ('김범수', '운동화', 30, 2);
insert into buyTBL(userName, prodName, price, amount) values ('김범수', '노트북', 1000, 1);
insert into buyTBL(userName, prodName, price, amount) values ('조용필', '모니터', 200, 1);
insert into buyTBL(userName, prodName, price, amount) values ('바비킴', '모니터', 200, 5);
insert into buyTBL(userName, prodName, price, amount) values ('김범수', '청바지', 50, 3);
insert into buyTBL(userName, prodName, price, amount) values ('바비킴', '메모리', 80, 10);
insert into buyTBL(userName, prodName, price, amount) values ('성시경', '책', 15, 5);
insert into buyTBL(userName, prodName, price, amount) values ('은지원', '책', 15, 2);
insert into buyTBL(userName, prodName, price, amount) values ('은지원', '청바지', 50, 1);
insert into buyTBL(userName, prodName, price, amount) values ('바비킴', '운동화', 30, 2);
insert into buyTBL(userName, prodName, price, amount) values ('은지원', '책', 15, 1);
insert into buyTBL(userName, prodName, price, amount)  values ('바비킴', '운동화', 30, 2);

select * from userTBL;
select * from buyTBL;


1. userTBL 과 buyTBL 을 이용하여  고객이름,  연락처, 구매물건, 단가, 수량, 구매 금액을  구하시오.

 
select u.userName 고객이름, mobile 연락처, prodName 구매물건, price 단가, amount 수량,(price*amount) 구매금액
from usertbl u
join buytbl b
on u.username=b.username;

create view  v_userbuyTBL
as
select u.userName 고객이름, mobile 연락처, prodName 구매물건, price 단가, amount 수량,(price*amount) 구매금액
from usertbl u
join buytbl b
on u.username=b.username;

2. userTBL 과 buyTBL 을 이용하여 고객별  구매금액의 총합을 구하시오.

select 고객이름, sum(구매금액) as "구매금액 총합"
from v_userbuytbl
group by 고객이름;


3. 고객별 구매금액의 총합이 고객별 구매금액총합의 평균 보다 많은 사람만 출력하시오.

 

-- 단계 1.
select  price * amount  from  buyTBL;

-- 단계 2.
select   sum(price * amount)
from  buyTBL  

-- 단계3.
select    avg(sum(price * amount))
    from  buyTBL  
    group  by userName;
    
--단계4.    
select  username, sum(price*amount)
from buytbl
group by username
having sum(price*amount) >
(select avg(sum(price*amount))
from buytbl
group by username
);


4. 회원가입된 고객의 수는 몇명인가 ?

select count(*)"고객 수"
from usertbl;


5. 하나의 물건이라도 구매한 적인 있는 고객의 수는 몇명인가?

select count(distinct username) "고객 수"
from buytbl;


6. 서울과 경기도에 살고 있는 고객은 몇명인가?

select addr, count(*)  
from usertbl
group by addr
having addr='서울' or addr='경기';

select addr, count(*)  
from usertbl
where addr in('서울', '경기')
group by addr;

select count(*)  
from usertbl
where addr in('서울', '경기');

7. 운동화를 구매한 사람의 이름과 연락처를 출력하시오.

select distinct(u.username), mobile
from usertbl u
join buytbl b
on u.username=b.username
where prodname='운동화';

select u.username, mobile
from usertbl u
where username in(
select username from buytbl
where prodname='운동화');


'Oracle' 카테고리의 다른 글

5-SQL 기본문법 정리  (0) 2022.05.23
6-1  (0) 2022.05.23
5-예제2  (0) 2022.05.20
5-예제1(컬럼)  (0) 2022.05.20
5-3  (0) 2022.05.20
Comments