예제 1

CREATE TABLE sales_record (
	num			INT			PRIMARY KEY			AUTO_INCREMENT,
	name		CHAR(10)	NOT NULL,
	sales		INT			NOT NULL,
	today		CHAR(10)	NOT NULL
);
INSERT INTO sales_record (name, sales, today)
	VALUES
	('IU', 2000, '2023-05-01'),
	('ailee', 4000, '2023-05-01'),
	('yoona', 1000, '2023-05-01'),
	('IU', 1500, '2023-04-31'),
	('ailee', 300, '2023-04-31'),
	('yoona', 4000, '2023-04-31'),
	('IU', 5000, '2023-04-30'),	
	('yoona', 2500, '2023-04-30'),
	('ailee', 2500, '2023-04-29'),
	('ailee', 4000, '2023-04-28'),
	('yoona', 3000, '2023-04-28'),
	('IU', 9000, '2023-04-27'),
	('ailee', 7000, '2023-04-27'),
	('IU', 6000, '2023-04-26'),
	('ailee', 2000, '2023-04-26'),
	('yoona', 4000, '2023-04-26');

-- 판매원 별 판매액 총액
SELECT name, SUM(sales) total
FROM sales_record
GROUP BY name;
	
-- 판매원 별 판매액 평균
SELECT name, AVG(sales) average
FROM sales_record
GROUP BY name;

-- 평균판매금액이 3000원 이상인 판매원들의 판매금액 합
SELECT name, SUM(sales) total
FROM sales_record
GROUP BY name HAVING AVG(sales) >= 3000;

-- 판매원 별 판매활동일 수
SELECT name, COUNT(today)
FROM sales_record
GROUP BY name;

-- 판매원 별 최고 판매금액
SELECT name, MAX(sales)
FROM sales_record
GROUP BY name;

-- 모든 판매일 구하기(중복제거)
SELECT DISTINCT today
FROM sales_record
ORDER BY today;

-- 전체 판매원이 판매한 일수  
SELECT COUNT(DISTINCT today) all_day
FROM sales_record;

예제 2

DROP TABLE professor;
DROP TABLE student;

CREATE TABLE professor(
	pid		INT		PRIMARY KEY		AUTO_INCREMENT,
	pname	VARCHAR(20)				NOT NULL,
	pmajor	VARCHAR(20)				NOT NULL
);

CREATE TABLE student (
	sid			INT		PRIMARY KEY		AUTO_INCREMENT,
	sname		VARCHAR(20)		NOT NULL,
	sgrade		INT		NOT NULL,
	pid			INT		NOT NULL
);


INSERT INTO professor(pname, pmajor) VALUES 
	('james', 'computer'),
	('jhone', 'math'),
	('jane', 'english'),
	('jason', 'kor');
	
INSERT INTO student(sname, sgrade, pid) VALUES
	('smith', 3, 1),
	('clock', 1, 2),
	('jonadan', 2, 3),
	('mike', 4, 1),
	('brown', 2, 2),
	('joe', 2, 3);


-- joe나 jonadan 의 담당교수 이름을 구하라.
SELECT pname FROM professor NATURAL JOIN student WHERE sname IN ('joe', 'jonadan');
	
-- 각 교수별 담당 학생을 구하라.
SELECT pname, sname FROM professor NATURAL JOIN student;

-- 각 교수별 담당 학생을 구하라. 담당학생이 없는 교수도 결과에 포함한다.
SELECT pname, sname FROM professor p LEFT OUTER JOIN student s ON p.pid = s.pid;

-- 각 교수별 담당학생 수 를 구하라. 담당학생이 없는 교수도 결과에 포함한다.
SELECT pname, COUNT(sname) FROM professor p LEFT OUTER JOIN student s ON p.pid = s.pid GROUP BY p.pid;

-- 담당 학생이 없는 교수를 구하라.
SELECT pname FROM professor p LEFT OUTER JOIN student s ON p.pid = s.pid WHERE s.sid IS NULL;
SELECT pname FROM professor p WHERE NOT EXISTS (SELECT * FROM student s WHERE p.pid = s.pid);
SELECT pname FROM professor p WHERE NOT p.pid IN (SELECT DISTINCT s.pid FROM student s);


-- computer 학과 학생을 구하라.
SELECT sname FROM student s NATURAL JOIN professor p WHERE pmajor = 'computer';
SELECT sname FROM student s WHERE EXISTS (SELECT * FROM professor p WHERE s.pid = p.pid AND pmajor = 'computer');

-- 각 학과별 모든 학생 수를 구하라. (모든 학과가 나와야 함 = OUTER JOIN!)
SELECT pmajor, COUNT(sname) FROM professor p LEFT OUTER JOIN student s ON p.pid = s.pid GROUP BY pmajor;