[SQL] SELECT문 연습

USE sqldb;

CREATE TABLE userTBL(
	userID CHAR(8) PRIMARY KEY,
	NAME VARCHAR(10) NOT NULL,
	birthYear INT NOT NULL CHECK(birthyear between 1900 AND 2024),
	addr CHAR(2) NOT NULL,
	mobile1 CHAR(3) CHECK (mobile1 IN('011','016','018','019')),   -- 011,016,018,019
	mobile2 CHAR(8),
	height SMALLINT,
	mDate DATE,
	grade CHAR(1) DEFAULT 'A' check(grade IN('A','B','C'))  
);

CREATE TABLE buyTBL(
	num INT AUTO_INCREMENT PRIMARY KEY,
	userID CHAR(8) NOT NULL,
	prodName CHAR(6) NOT NULL,
	groupName CHAR(4),
	price INT NOT NULL,
	amount SMALLINT NOT NULL,
	FOREIGN KEY(userID) REFERENCES userTBL(userID)
);

INSERT INTO userTBL VALUES ('LSG','이승기',1987,'서울','011','1111111',182,'2008-8-8',default);
INSERT INTO userTBL VALUES ('KBS','김범수',1979,'경남','011','2222222',177,'2010-5-2',default);
INSERT INTO userTBL VALUES ('KKH','김경호',1971,'전남','019','3333333',179,'2012-4-8',default);
INSERT INTO userTBL VALUES ('JYP','조용필',1950,'경기','011','4444444',180,'2016-7-2',default);
INSERT INTO userTBL VALUES ('SSK','성시경',1979,'서울',NULL,NULL,181,'2020-8-16',default);
INSERT INTO userTBL VALUES ('LJB','임재범',1963,'서울','016','5555555',165,'2017-1-14',default);
INSERT INTO userTBL VALUES ('YJS','윤종신',1969,'경남',NULL,NULL,167,'2014-12-2',default);
INSERT INTO userTBL VALUES ('EJW','은지원',1972,'경북','011','6666666',179,'2009-11-11',default);
INSERT INTO userTBL VALUES ('JKW','조관우',1965,'경기','018','7777777',172,'2014-12-31',default);
INSERT INTO userTBL VALUES ('BBK','바비킴',1973,'서울','011','8888888',170,'2010-1-25',default);



INSERT INTO buyTBL VALUES(NULL,'KBS','운동화',NULL,30,2);
INSERT INTO buyTBL VALUES(NULL,'KBS','노트북','전자',1000,1);
INSERT INTO buyTBL VALUES(NULL,'JYP','모니터','전자',200,1);
INSERT INTO buyTBL VALUES(NULL,'BBK','모니터','전자',200,5);
INSERT INTO buyTBL VALUES(NULL,'KBS','청바지','의류',50,3);
INSERT INTO buyTBL VALUES(NULL,'BBK','메모리','전자',80,10);
INSERT INTO buyTBL VALUES(NULL,'SSK','책','서적',15,5);
INSERT INTO buyTBL VALUES(NULL,'EJW','책','서적',15,2);
INSERT INTO buyTBL VALUES(NULL,'KBS','청바지','의류',50,1);
INSERT INTO buyTBL VALUES(NULL,'KBS','운동화',NULL,30,2);
INSERT INTO buyTBL VALUES(NULL,'EJW','책','서적',15,1);
INSERT INTO buyTBL VALUES(NULL,'BBK','운동화',NULL,30,2);



SELECT userID,NAME,height
FROM userTBL
WHERE height >= 180;


-- 1970년 이후에 출생하고, 신장이 182인 사람의 아이디와 이름을 조회하시오
SELECT userId, NAME
FROM userTBL
WHERE birthyear >= 1970 AND height>=182;

-- 1970년 이후에 출생했거나, 신장이 182 이상인 사람의 아이디와 이름을 조회하시오
SELECT userID, NAME
FROM userTBL
WHERE birthyear>=1970 OR height>=182;

-- 키가 180~183인 사람을 조회하시오.
SELECT userID, NAME
FROM userTBL
WHERE between height>=180 AND height<=183;
 
-- 지역이 경남,전남,경북인 사람의 정보를 조회하시오.
SELECT userID, NAME
FROM userTBL
WHERE addr = '경남' or addr = '전남'OR addr ='경북';

--성이 '김'씨인 사람의 정보를 조회하시오.
SELECT userID,NAME
FROM userTBL
WHERE NAME LIKE '김%';

--이름의 두번째 글자가 '용'이고 그 이후 몇글자든 아무거나 오는 값을 추출하시오
SELECT userID, NAME
FROM userTBL
WHERE NAME LIKE '_용%';

-- 키가 177보다 큰 사람의 이름을 조회하시오.
SELECT NAME
FROM userTBL 
WHERE height >= 177;

-- 모바일 시작번호가 018인 사람의 이름을 조회하시오.
SELECT NAME
FROM userTBL
WHERE mobile1 = '018';

-- 김경호보다 키가 큰 사람의 이름을 조회하시오. 

SELECT NAME
FROM userTBL
WHERE height >= (SELECT height FROM userTBL WHERE NAME = '김경호');


-- 지역이 '경남'인 사람의 키보다 큰 사람의 정보를 추출하시오.

SELECT *
FROM userTBL
WHERE height > ALL (SELECT height FROM userTBL WHERE addr = '경남');

-- 가입한 순서대로 회원정보를 출력하시오.
SELECT userID, NAME
FROM userTBL
ORDER BY mdate ASC;

-- 키큰순서대로 정렬하되, 만약 키가 같을 경우에 이름순으로 정렬하시오.
SELECT NAME, height
FROM userTBL
ORDER BY height desc , NAME;

-- 회원테이블에서 회원들의 거주지역은 몇군데인지 조회하시오.
SELECT COUNT(b.addr) AS '회원거주지역 수'
FROM(SELECT DISTINCT addr
FROM usertbl) b;

-- 사용자별 구매액을 조회하시오.
SELECT userid, price*amount AS '구매액'
FROM buyTBL
GROUP BY userid;

-- 전체 구매자가 구매한 물품 개수의 평균을 구하시오.
SELECT amount/COUNT(prodName)
FROM buyTBL;
GROUP BY prodName;

-- 가장 큰 키의 회원과 가장 작은 키의 회원의 이름과 키를 출력하시오
SELECT NAME, height 
FROM userTBL
WHERE height = (SELECT MAX(height) FROM userTBL;)


-- 휴대폰이 있는 사용자수를 출력하시오.
SELECT count(mobile1)
FROM userTBL
WHERE mobile1 IS NOT NULL;

-- 사용자별 총 구매액이 1000이상인 사용자를 조회하시오.
SELECT userID
FROM buyTBL
WHERE price*amount >= 1000;