USE example_db;
CREATE TABLE employees (
id INT PRIMARY KEY,
name VARCHAR(50),
position VARCHAR(50),
salary INT,
hire_date DATE
);
INSERT INTO employees (id, name, position, salary, hire_date)
VALUES
(1, '르탄이', '개발자', 30000, '2022-05-01'),
(2, '배캠이', 'PM', 40000, '2021-09-25'),
(3, '구구이', '파트장', 35000, '2023-06-01'),
(4, '이션이', '팀장', 50000, '2021-07-09');
-- Name 과 Position 선택
select e.name ,e.`position`
from employees e
-- 중복없이 position 선택
select distinct e.`position`
from employees e
-- 연봉이 40000과 60000 사이인 직원 선택
select *
from employees e
where e.salary between 40000 and 60000
-- 입사일이 2023-01-01 이전인 모든 직원 선택
select *
from employees e
where e.hire_date <="2023-01-01"
USE example_db;
CREATE TABLE products (
id INT PRIMARY KEY,
product_name VARCHAR(50),
price INT,
category VARCHAR(50)
);
INSERT INTO products (id, product_name, price, category)
VALUES
(1, '맥북 프로', 1200, '컴퓨터'),
(2, '다이슨 청소기', 300, '생활가전'),
(3, '갤럭시탭', 600, '컴퓨터'),
(4, '드롱기 커피머신', 200, '주방가전');
-- 제품 이름과 가격 만을 선택
select p.product_name ,p.price
from products p
-- 테이블에서 제품 이름에 프로가 포함된 모든 제품 선택
select *
from products p
where p.product_name like("%프로%")
-- 테이블에서 제품 이름이 갤 로 시작하는 모든 제품 선택
select *
from products p
where substr(p.product_name ,1,1) = "갤"
-- 모든 제품을 구매하기 위해 필요한 돈을 계산
select sum(p.price) as 'result_calc_price'
from products p
USE example_db;
CREATE TABLE orders (
id INT PRIMARY KEY,
customer_id INT,
product_id INT,
amount INT,
shipping_fee INT,
order_date DATE
);
INSERT INTO orders (id, customer_id, product_id, amount, shipping_fee, order_date)
VALUES
(1, 719,1, 3,50000, '2023-11-01'),
(2, 131,2, 1,10000, '2023-11-02'),
(3, 65,4, 1,20000, '2023-11-05'),
(4, 1008,3,2,25000, '2023-11-05'),
(5, 356,1,1,15000, '2023-11-09');
select *
from orders o
-- 주문 수량 2개 이상인 소비자 ID만 선택
select o.customer_id
from orders o
where o.amount >=2
-- 2023-11-02 이후에 주문된 주문 수량 2개 이상인 주문 선택
select *
from orders o
where o.amount >=2 and o.order_date >= '2023-11-02'
-- 주문 수량이 3개 미만이면서 배송비가 15000원 보다 비싼 주문을 선택
select *
from orders o
where o.amount <3 and o.shipping_fee >15000
-- 배송비가 높은 금액 순으로 정렬
select *
from orders o
order by o.shipping_fee desc
use example_db
create table sparta_students(
id int primary key,
name varchar(50),
track varchar(50),
grade varchar(50),
enrollment_year int);
insert into sparta_students (id,name,track,grade,enrollment_year)
values
(1,'르탄이','Node.js','A',2023),
(2,'배캠이','Spring','B',2022),
(3,'구구이','Unity','C',2021),
(4,'이션이','Node.js','B',2022);
select *
from sparta_students ss
-- 모든 학생의 이름과 트랙을 선택
select ss.name , ss.track
from sparta_students ss
-- 유니티 트랙 소속이 아닌 학생들을 선택
select *
from sparta_students ss
where ss.track !=('Unity')
-- 입학 년도가 2021년인 학생과 2023년인 학생을 선택
select *
from sparta_students ss
where ss.enrollment_year between '2021' and '2023'
-- Node.js트랙 소속이고 학점이 A인 학샌의 입학년도를 선택
select ss.enrollment_year
from sparta_students ss
where ss.track = 'Node.js' and grade ='A'
use example_db
create table team_projects(
id int primary key,
name varchar(50),
start_date date,
end_date date,
aws_cost int);
insert into team_projects(id,name,start_date ,end_date,aws_cost)
values
(1,'일조','2023-01-01','2023-01-07',30000),
(2,'꿈꾸는이조','2023-03-15','2023-03-22',50000),
(3,'보람삼조','2023-11-20','2023-11-30',80000),
(4,'사조참치','2022-07-01','2022-07-30',75000);
select *
from team_projects tp
-- 예산이 40000 이상 들어간 프로젝트들의 이름을 선택
select tp.name
from team_projects tp
where tp.aws_cost >=40000
-- 2022년에 시작된 프로젝트를 선택, 단 start_date < '2023-01-01' 조건을 사용하지 않고
select *
from team_projects tp
where year (tp.start_date)=2022
-- 현재 진행중인 프로젝트를 선택, 단 지금시점의 날짜를 하드코딩해서 하지 않고
select *
from team_projects tp
where curdate() between start_date and end_date
-- 각 프로젝트이 지속 기간을 일 수로 계산
select tp.name ,datediff(tp.end_date ,tp.start_date) as 'result_project_days'
from team_projects tp
use example_db
create table lol_users(
id int primary key,
name varchar(50),
region varchar(50),
rating int,
join_date date);
insert into lol_users(id,name,region,rating,join_date)
values
(1,'르탄이','한국',1300,'2019-06-15'),
(2,'배캠이','미국',1500,'2020-09-01'),
(3,'구구이','한국',1400,'2021-01-07'),
(4,'이션이','미국',1350,'2019-11-15');
select *
from lol_users lu
-- 각 유저의 레이팅 순위를 계산
select rank () over (order by lu.rating desc) as 'rank',lu.id ,lu.name ,lu.rating , lu.region
from lol_users lu
-- 가장 늦게 게임을 시작한 유저의 이름을 선택
select lu.name as 'last_join_user'
from lol_users lu
where lu.join_date = (select max(lu2.join_date) from lol_users lu2)
-- 지역 별로 레이팅이 높은 순으로 유저들을 정렬
select *
from lol_users lu
order by lu.region , lu.rating desc
-- 지역별로 평균 레이팅 계산
select lu.region , avg(rating) as 'avg_rating'
from lol_users lu
group by lu.region
use example_db
create table lol_feedbacks(
id int primary key,
user_name varchar(50),
satisfaction_score varchar(50),
feedback_date date
);
insert into lol_feedbacks(id,user_name,satisfaction_score,feedback_date)
values
(1,'르탄이',5,'2023-03-01'),
(2,'배캠이',4,'2023-03-02'),
(3,'구구이',3,'2023-03-01'),
(4,'이션이',5,'2023-03-04'),
(5,'구구이',4,'2023-03-04');
select *
from lol_feedbacks lf
-- 만족도 점수에 따라 피드백을 내림차순으로 정렬
select *
from lol_feedbacks lf
order by lf.satisfaction_score desc
-- 유저별로 최신 피드백을 찾아라 안타깝지만 정보가 하나뿐이다.
select lf.user_name ,lf.feedback_date
from lol_feedbacks lf
where lf.feedback_date = (
select max(lf2.feedback_date)
from lol_feedbacks lf2
where lf.id = lf2.id);
-- 만족도 점수가 5점인 피드백 수를 계산
select count(lf.user_name) as 'five_score_count'
from lol_feedbacks lf
where lf.satisfaction_score =5
-- 피드백을 많이 남긴 상위 3명
select lf.user_name ,count(*) as 'feedback_count'
from lol_feedbacks lf
group by lf.user_name
order by `feedback_count` desc
limit 3
-- 평균 만족도 점수가 가장 높은 날짜를 찾아라
select avg(lf.satisfaction_score) as 'date_of_avrage_score'
from lol_feedbacks lf
group by lf.feedback_date
order by `date_of_avrage_score` desc
limit 1
use example_db
create table doctors(
id int primary key,
name varchar(50),
major varchar(50),
hire_date date);
insert into doctors(id,name,major,hire_date)
values
(1,'르탄이','피부과','2018-05-10'),
(2,'배캠이','성형외과','2019-06-15'),
(3,'구구이','안과','2020-07-20');
select *
from doctors d
-- 전공이 성형외과인 의사의 이름
select d.name
from doctors d
where d.major ='성형외과'
-- 각 전공 별 의사 수
select d.major ,count(*) as 'major_group_count'
from doctors d
group by d.major
-- 현재 날짜 기준으로 5년 이상 근무 한 의사 수
select count(*) as 'five_year_hire_date_count'
from doctors d
where year(d.hire_date )>=5
-- 각 의사의 근무 기간
select d.name ,datediff(curdate() ,d.hire_date) as 'hire_date_of_day_count'
from doctors d
use example_db
create table patients(
id int primary key,
name varchar(50),
birth_date date,
gender varchar(50),
last_visit_date date);
insert into patients(id,name,birth_date,gender,last_visit_date)
values
(1,'르탄이','1985-04-12','남자','2023-03-15'),
(2,'배캠이','1990-08-05','여자','2023-03-20'),
(3,'구구이','1982-12-02','여자','2023-02-18'),
(4,'이션이','1999-03-02','남자','2023-03-17');
select *
from patients p
-- 각 성별에 따른 환자 수
select p.gender ,count(*) as 'gender_of_patients_count'
from patients p
group by p.gender
-- 현재 나이가 40세 이상인 환자들의 수
select count(*) as '40year_survivor_of_patients'
from patients p
where (year(curdate()) -year (p.birth_date)) >= 40
-- 마지막 방문 날짜가 1년 이상 된 환자들 선택
select *, year (curdate())-year (p.last_visit_date) as 'last_visit_date_of_year'
from patients p
where year (curdate())-year (p.last_visit_date) >=1
-- 생년월일이 1980년대인 환자들의 수
select count(*) as 'birth_date_of_1980'
from patients p
where substr(birth_date,1,3) = 198
use dual_table
create table employees(
id int primary key,
department_id int,
name varchar(50));
insert into employees(id,department_id,name)
values
(1, 101, '르탄이'),
(2, 102, '배캠이'),
(3, 103, '구구이'),
(4, 101, '이션이');
select *
from employees e
use dual_table
create table departments(
id int primary key,
name varchar(50));
insert into departments (id,name)
values
(101,'인사팀'),
(102,'마케팅팀'),
(103,'기술팀');
select *
from departments d
-- 총 부서의 수를 구하라
select count(d.name) as 'departments_name_of_count'
from departments d
-- 모든 직원과 그들이 속한 부서의 이름을 나열하라
select *
from employees e
join departments d
where e.department_id = d.id
-- 기술팀 부서에 속한 직원들의 이름을 나열하라
select *
from employees e
join departments d on d.name ='기술팀' and d.id = e.department_id
-- 부서별로 직원 수 계산
select d.name , count(*) as 'departments_name_of_count'
from employees e
join departments d on d.id = e.department_id
group by d.name
-- 직원이 없는 부서의 이름을 찾아라
select d.name as 'empty_department_name'
from employees e
join departments d
where e.department_id = d.id and e.id is null
-- 마케팅 팀에 속한 직원의 이름 나열
select e.name as 'marketing_team_employees'
from employees e
join departments d
where e.department_id = d.id and d.name ="마케팅팀"
use dual_table
create table products(
id int primary key,
name varchar(50),
price int);
insert into products(id,name,price)
values
(1,'랩톱',1200),
(2,'핸드폰',800),
(3,'타블렛',400);
use dual_table
create table orders(
id int primary key,
product_id int,
quantity int,
order_date date);
insert into orders(id,product_id,quantity,order_date)
values
(101,1,2,'2023-03-01'),
(102,2,1,'2023-03-02'),
(103,3,5,'2023-03-04');
select *
from products p
select *
from orders
-- 모든 주문의 ID와 주문된 상품의 이름을 나열
select o.id , p.name
from orders o , products p
where o.product_id =p.id
-- 총 매출 이 가장 높은 상품의 ID와 해당 상품의 총매출
select p.id ,(p.price * o.quantity) as 'top_of_result_price'
from orders o ,products p
where p.id = o.product_id
order by `top_of_result_price` desc
limit 1
-- 각 상품 ID 별로 판매된 총 수량
select p.id , o.quantity as 'result_of_quantity'
from orders o , products p
where o.product_id = p.id
-- 2023-03-03 이후에 주문된 모든 상품의 이름
select p.name
from orders o , products p
where o.product_id = p.id and o.order_date > '2023-03-03'
-- 가장 많이 판매된 상품의 이름
select p.name , o.quantity as 'result_quantity_count'
from orders o , products p
where o.product_id = p.id and o.quantity
order by o.quantity desc
limit 1
-- 각 상품 ID별로 평균 주문 수량 계산
select p.id, avg(o.quantity) as 'avrage_of_quantity'
from orders o , products p
where o.product_id = p.id
group by o.id
-- 판매 되지 않은 상품의 ID와 이름
select p.id, p.name
from orders o , products p
where o.product_id = p.id and o.quantity =0
댓글