테스트용 DB가 온라인에서 찾지를 못해서 MySQL Workbench에서 만들어 봅니다. (먼저 2개 customer와 employee)

create database SimpleDB;

create table simpledb.customer
(
    customer_number int auto_increment primary key,
    customer_name varchar(50),
    customer_company varchar(50),
    saleman_employee_number int
);

insert into simpledb.customer (customer_name, customer_company, saleman_employee_number) values ('Bill', 'Dell', 7499);
insert into simpledb.customer (customer_name, customer_company, saleman_employee_number) values ('Diana', 'Dell', 7499);
insert into simpledb.customer (customer_name, customer_company, saleman_employee_number) values ('Bob', 'HP', 7521);
insert into simpledb.customer (customer_name, customer_company, saleman_employee_number) values ('Jill', 'Asus', 7654);
insert into simpledb.customer (customer_name, customer_company, saleman_employee_number) values ('Jack', 'Best Buy', NULL);
insert into simpledb.customer (customer_name, customer_company, saleman_employee_number) values ('Erin', 'Walmart', NULL);

 

create table simpledb.employee
(
    employee_number int primary key,
    employee_name varchar(50),
    job varchar(50),
    manager int,
    hire_date datetime,
    salary int,
    commission int,
    department_number int
);

insert into simpledb.employee (employee_number, employee_name, job, manager, hire_date, salary, commission, department_number)
    values (7369, 'SMITH', 'CLERK', 7902, '2004-12-17', 800, NULL, 20);
insert into simpledb.employee (employee_number, employee_name, job, manager, hire_date, salary, commission, department_number)
    values (7499, 'ALLEN', 'SALESMAN', 7698, '2005-02-20', 1600, 300, 30);
insert into simpledb.employee (employee_number, employee_name, job, manager, hire_date, salary, commission, department_number)
    values (7521, 'WARD', 'SALESMAN', 7698, '2005-02-22', 1250, 500, 30);
insert into simpledb.employee (employee_number, employee_name, job, manager, hire_date, salary, commission, department_number)
    values (7566, 'JONES', 'MANAGER', 7839, '2005-04-02', 2975, NULL, 20);
insert into simpledb.employee (employee_number, employee_name, job, manager, hire_date, salary, commission, department_number)
    values (7654, 'MARTIN', 'SALESMAN', 7698, '2005-09-28', 1250, 1400, 30);
insert into simpledb.employee (employee_number, employee_name, job, manager, hire_date, salary, commission, department_number)
    values (7698, 'BLAKE', 'MANAGER', 7839, '2005-05-01', 2850, NULL, 30);
insert into simpledb.employee (employee_number, employee_name, job, manager, hire_date, salary, commission, department_number)
    values (7782, 'CLARK', 'MANAGER', 7839, '2005-06-09', 2450, NULL, 10);
insert into simpledb.employee (employee_number, employee_name, job, manager, hire_date, salary, commission, department_number)
    values (7788, 'SCOTT', 'ANALYST', 7566, '2011-04-19', 3000, NULL, 20);
insert into simpledb.employee (employee_number, employee_name, job, manager, hire_date, salary, commission, department_number)
    values (7839, 'KING', 'PRESIDENT', NULL, '2005-11-17', 5000, NULL, 10);
insert into simpledb.employee (employee_number, employee_name, job, manager, hire_date, salary, commission, department_number)
    values (7844, 'TURNER', 'SALESMAN', 7698, '2005-09-08', 1500, 0, 30);
insert into simpledb.employee (employee_number, employee_name, job, manager, hire_date, salary, commission, department_number)
    values (7876, 'ADAMS', 'CLERK', 7788, '2011-05-23', 1100, NULL, 20);
insert into simpledb.employee (employee_number, employee_name, job, manager, hire_date, salary, commission, department_number)
    values (7900, 'JAMES', 'CLERK', 7698, '2005-12-03', 950, NULL, 30);
insert into simpledb.employee (employee_number, employee_name, job, manager, hire_date, salary, commission, department_number)
    values (7902, 'FORD', 'ANALYST', 7566, '2005-12-03', 3000, NULL, 20);
insert into simpledb.employee (employee_number, employee_name, job, manager, hire_date, salary, commission, department_number)
    values (7934, 'MILLER', 'CLERK', 7782, '2006-01-23', 1300, NULL, 10);

 

SQL Distinct

select * from simpledb.customer;

select distinct customer_company from simpledb.customer;

https://www.youtube.com/watch?v=j__oNG7JvOw&list=PLD20298E653A970F8&index=4

 

SQL Where

select * from simpledb.customer;

select * from simpledb.customer
where customer_company = 'Dell';

select * from simpledb.customer
where customer_company != 'Dell';

select * from simpledb.customer
where customer_company <> 'Dell';

select * from simpledb.customer
where customer_name like 'J%';

https://www.youtube.com/watch?v=9BaWwyVGvTo&list=PLD20298E653A970F8&index=5

 

SQL AND OR Statements

select *
from simpledb.customer
where customer_company = 'Dell' or customer_company = 'HP' or customer_company = 'Walmart';

select *
from simpledb.customer
where customer_company in ('Dell', 'Hp', 'Walmart');

select *
from simpledb.customer
where customer_company = 'Dell' and customer_name ='Diana';

https://www.youtube.com/watch?v=p8eHLDcgCf8&list=PLD20298E653A970F8&index=6

 

SQL IN Filter

select *
from simpledb.customer
where customer_company = 'Dell' or customer_company = 'HP' or customer_company = 'Walmart';

select *
from simpledb.customer
where customer_company in ('Dell', 'Hp', 'Walmart', 'Asus');

https://www.youtube.com/watch?v=psiY3r6kv60&list=PLD20298E653A970F8&index=7

 

SQL Between

select *
from simpledb.employee
where salary=800;

select *
from simpledb.employee
where salary=800 or salary=1250;

select *
from simpledb.employee
where salary=800 or salary=1250 or salary=1100;

select *
from simpledb.employee
where salary between 800 and 1500;

https://www.youtube.com/watch?v=58-_A_RotfQ&list=PLD20298E653A970F8&index=8

 

SQL Wildcard

select * from simpledb.employee
where employee_name like 'A%';

select * from simpledb.employee
where employee_name like '%LL%';

select * from simpledb.employee
where employee_name like '_LL%';

select * from simpledb.employee
where employee_name like '__LL%';

select * from simpledb.employee
where employee_name like '%R';

https://www.youtube.com/watch?v=6GL9DYxeVWg&list=PLD20298E653A970F8&index=9

 

SQL Like Filter

https://www.youtube.com/watch?v=lAcQp3hXYC4&list=PLD20298E653A970F8&index=10

 

SQL Order By

order by job, employee_name;

select *
from simpledb.employee
order by 3, 2;

select employee_number, job, employee_name
from simpledb.employee
order by employee_number;

 

MySQL에서는 +가 안되네요..

 

select job, employee_name, concat(job,' ', employee_name)
from simpledb.employee
order by concat(job, employee_name);

https://www.youtube.com/watch?v=aUZ3E3bSl0k&list=PLD20298E653A970F8&index=11

 

SQL Functions

select avg(salary)
from simpledb.employee;

select avg(salary) as average_salary
from simpledb.employee;

select avg(salary) as average_salary,
max(salary) as max_salary,
min(salary) as min_salary,
count(salary) as count_salary
from simpledb.employee;

https://www.youtube.com/watch?v=UgLY97b6RQc&list=PLD20298E653A970F8&index=12