SQL기초

4월 9~11일 문제 풀이

use market_db;

-- 문1) memTBL 테이블에서 mem_name, addr, height 만 검색
select mem_name, addr, height from memTBL;

-- 문2) memTBL 테이블에서 멤버 인원수가 6명이하인 자료들의 
-- mem_name, mem_number,  addr만 검색
select mem_name, mem_number, addr from memTBL where mem_number<6 ; 

-- 문3) memTBL 테이블에서 멤버 인원수가 6명이하인 자료들의 
-- mem_name(그룹명), mem_number(멤버수),  addr(주소)만 검색
select mem_name 그룹명, mem_number 멤버수, addr 주소  from memTBL where mem_number<6 ; 

-- 문4 buyTBL테이블에서 가격(price)이 50보다 크고 130보다 작은 자료들의 mem_id(멤버id), pro_name(제품명)
select mem_id 멤버id,prod_namw 제품명  from buyTBL where 50<price and price<130; 

select * from buyTBL;
-- 문5 buyTBL테이블 에서 가격이 50이상 150이하인 자료들의 mem_id(멤버id), prod_namw(제품명)
select mem_id 멤버id, prod_namw 제품명, price 가격 from buyTBL where price between 50 and 150;

-- 문6 buyTBL 테이블에서 prod_namw(제품명)이 '아이폰, 청바지, 지갑'인 자료들의
--  mem_id(멤머id), price(가격), amount(주문수량) 만 조회
select prod_namw 제품명,mem_id 멤머id, price 가격, amount 주문수량 from buyTBL where prod_namw="아이폰" or prod_namw="청바지" or prod_namw="지갑";


-- 문7 buyTBL테이블 에서 prod_namw이 '맥' 또는 '아'로 시작하는 모든 자료를 검색
select * from buyTBL where prod_namw like "맥%" or prod_namw like "아%";

-- =============================================================================================

-- 문1 memTBL 테이블에서 mem_name(그룹이름), addr(주소)
-- 조건 addr이 '경기' 또는 '서울'인 자료
select mem_name 그룹이름, addr 주소 from memTBL where addr="경기" or addr="seoul";  

-- 문2 memTBL 테이블에서 mem_name(그룹이름), addr(주소)
-- 조건 addr이 '경기' 또는 '서울'인 자료를 검색한 후 mem_name 내림차순 정렬(desc)
select mem_name 그룹이름,addr 주소 from memTBL where addr="경기" or addr="seoul" order by mem_name desc;

-- 문3 memTBL 테이블에서 전체를 검색하되 주소를 기준을 오름 차순 정렬
select * from memTBL order by addr;

-- 문4 addr 기준 오름차순, addr이 같은 자료들은 mem_number 기준으로 내림차순 정렬
 select * from memTBL order by addr asc,mem_number desc;
 
 -- 문5 memTBL 테이블에서 addr를 검색
 select addr from memTBL; 
 
-- 문6 memTBL 테이블에서 addr를 검색하되 중복된 자료는 하나만 출력
-- 중복된 자료를 하나만 검색(중복된 결과를 제거) : distinct

 select distinct addr from memTBL; 
 
 -- 문7 buyTBL 테이블에서 mem_id(회원id), price(가격), amount(수량)를 검색하되,mem_id별로 오름 차순 정렬
 select mem_id as "회원id", price as "가격", amount as "수량" from buyTBL order by mem_id ;

-- 문8 buyTBL 테이블에서 mem_id별로 amount의 합계와 평균 구하기
select mem_id 회원id,sum(amount), avg(amount) from buyTBL group by mem_id;

-- 문9 buyTBL 테이블에서 mem_id별로 구매건수 구하기

select mem_id, count(mem_id) 구매건수 from buyTBL group by mem_id;

-- 문10 buyTBL 테이블에서 mem_id별로 주문수량(amount) 수량의 
-- 합계, 평균, 최댓값, 최솟값,  건수 구하기

select mem_id, sum(amount) 합계, avg(amount) 평균, max(amount) 최대값, min(amount) 최소값, count(amount) 건수 from buyTBL group by mem_id;

-- 문11 buyTBL 테이블에서 mem_id별로 주문수량(amount) 수량의 합계와 평균을 구하고 평균은 소수이하 첫째자리까지만 구하시오. 
select mem_id, sum(amount) 합계, format((amount),1) 평균 from buyTBL group by mem_id;

-- 문12 buyTBL 테이블에서 mem_id별로 주문금액(price * amount)의 합계

select mem_id, format(sum(price*amount),0) 총합계금액 from buyTBL group by mem_id;

-- 문13 buyTBL 테이블에서 mem_id별로 주문금액이 1000 이상인 자료
select mem_id, format(sum(price*amount),0) 총주문금액 from buyTBL group by mem_id having 1000<=sum(price*amount);

-- 문14 memTBL테이블에서 phone1이 널인 자료들의, mem_name, addr를 조회
select mem_name 이름, addr 주소, phone1 번호 from memTBL where phone1 is null;	

-- 문15 memTBL테이블에서 phone1이 널이 아닌 자료들의, mem_name, addr를 조회
select  mem_name 이름, addr 주소, phone1 번호 from memTBL where phone1 is not null;	

-- 문16 memTBL테이블에서 phone1과 phone2가 null인 경우 '미입력'이라고 표시하고
-- mem_name, phone1, phone2를 조회
select mem_name, if(phone1 is null,"미입력",phone1) 폰1, if(phone2 is null,"미입력",phone2) 폰2 from memTBL;

-- ================================================================================================
-- 문1 memTBL 테이블에서 mem_number를
-- 현재 인원에 3명씩 증가시키기, 3명씩 감소시키기   - 전체를 변경
update memTBL set mem_number=mem_number+3;
update memTBL set mem_number=mem_number-3;

-- 문2 memTBL 테이블에서 mem_id가 apn, itz인 자료들의 mem_number를
-- 현재 인원에 3명씩 증가시키기
update memTBL set mem_number=mem_number+3 where mem_id="apn" or mem_id="itz";

-- 문3 memTBL 테이블에서 addr이 '서울'인 자료들을  'seoul'로 변경
update memTBL set addr="서울" where addr="seoul";

select * from buyTBL;

-- 문4 buyTBL 테이블을 복사해서 bCopy라는 테이블을 생성
create table bCopy (select *from buyTBL);

-- 문5 buyTBL 테이블에서 group_name '디지털','서적'인 자료만 검색해서
-- digBook 테이블을 만들기
create table digBook (select * from buyTBL where group_name in ("디지털", "서적"));
select * from digBook;

-- 문6 memTBL 테이블에서 mem_id, mem_name,mem_number만 검색해서
-- memCopy라는 테이블을 생성
create table memCopy (select mem_id, mem_name,mem_number from memTBL);
select * from memCopy;

-- 문7 bCopy 테이블에서 amount 중 상위(많은 값) 3개인 자료만 검색해서
-- top3라는 테이블 생성
-- 많다 : 정렬에서 내림차순,  위에서 3개를 뽑기
drop table top3;
create table top3(select * from bCopy order by amount desc limit 3);
select * from top3;

-- 문8 buyTBL 테이블에서 price가 높은 것을 기준으로 상위 2번째부터 3개의 자료를 검색
-- 해서 priceTop이라는 테이블 생성

drop table priceTop;
create table priceTop(select*from buyTBL order by price desc limit 2,3);
select * from priceTop;

-- 문9 bCopy테이블의 내용을 모두 삭제하시오.
delete from bCopy;
select * from bCopy;

-- 문10 digBook 테이블을 제거하시오.
drop table digBook;
select * from digBook; 

-- 문11 memCopy테이블에서 mem_id가 a으로 시작하거나 'u'로 끝나는 자료를 제거
delete from memCopy where mem_id like 'a%' or mem_id like "%u";
select * from memCopy; 

-- 문12 memTBL테이블에서 height가 itz보다 큰 값들만 검색
select mem_id, height from memTBL where height>(select height from memTBL where mem_id="itz");

-- 문13 memTBL테이블에서 height가 apn보다 큰 값들만 검색
select mem_id, height from memTBL where height>(select height from memTBL where mem_id="apn");


-- 문14 memTBL테이블에서 height가 addr이 경남이 자료들의 
-- height 보다 큰 값들만 검색
select mem_id, height, addr from memTBL where height >any (select height from memTBL where addr='경남');