일 | 월 | 화 | 수 | 목 | 금 | 토 |
---|---|---|---|---|---|---|
1 | 2 | 3 | 4 | |||
5 | 6 | 7 | 8 | 9 | 10 | 11 |
12 | 13 | 14 | 15 | 16 | 17 | 18 |
19 | 20 | 21 | 22 | 23 | 24 | 25 |
26 | 27 | 28 | 29 | 30 | 31 |
- 이것이 자바다
- 버추얼머신
- autoset
- 자바
- 정보처리기사
- 정처기필기
- 원형그래프
- NCS
- 리눅스
- rinux
- html
- VLAN
- ospf
- javaee
- jsp
- jsp연결
- sql
- w3school
- Oracle
- 라우터
- 네트워크관리사
- 오라클
- ciscopacket
- cisco packet
- php
- Cisco
- 네트워크
- 데이터베이스
- Java
- 참조타입
- Today
- Total
기록해! 정리해!
6-예제1(shopping) 본문
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='운동화');