question-15~27.sql
-- 문제 15
-- 만들어놓은 mydb에 아래와 같이 테이블을 만들어 보시오.
-- 테이블 명 : emp (기존에 존재하는 것은 drop 합니다.)
-- 열이름 데이터형식 NULL허용 pk 기타
-- empno int x(not null) o
-- ename varchar(20) x x
-- job varchar(8) x x
-- mgr varchar(10) o x
-- hiredate date x x
-- sal int x x
-- comm int o x
-- deptno int x x FK설정
create database mydb;
use mydb;
drop table if exists emp;
create table emp(
empno int not null primary key,
ename varchar(20) not null,
job varchar(8) not null,
mgr varchar(10),
hiredate date not null,
sal int not null,
comm int,
deptno int not null,
foreign key(deptno) references dept(deptno) -- question-1~2 dept 테이블 있음.
);
-- 문제 16
-- 만들어진 emp테이블에 아래의 데이터를 입력하라.
-- 1001, '김사랑', '사원', 1013, '2007-03-01', 300, null, 20
-- 1002, '한예슬', '대리', 1005, '2008-10-01', 250, 80, 30
-- 1003, '오지호', '과장', 1005, '2005-02-10', 590, 100, 30
-- 1004, '이병헌', '부장', 1008, '2003-09-01', 600, null, 20
-- 1005, '신동협', '과장', 1005, '2010-02-09', 450, 200, 30
-- 1006, '장동건', '부장', 1008, '2003-10-09', 480, null, 30
-- 1007, '이문세', '부장', 1008, '2004-01-08', 520, null, 10
-- 1008, '김우성', '차장', 1003, '2004-03-08', 500, 0, 30
-- 1009, '안성기', '사장', null, '1996-10-01', 1000, null, 20
-- 1010, '이병헌', '과장', 1003, '2005-04-07', 500, null, 10
-- 1011, '조형기', '사원', 1007, '2007-03-01', 280, null, 30
-- 1012, '김혜정', '사원', 1006, '2007-08-09', 300, null, 20
-- 1013, '박종훈', '부장', 1003, '2002-10-09', 560, null, 20
-- 1014, '조인성', '사원', 1006, '2007-11-09', 250, null, 10
insert into emp values
(1001, '김사랑', '사원', 1013, '2007-03-01', 300, null, 20),
(1002, '한예슬', '대리', 1005, '2008-10-01', 250, 80, 30),
(1003, '오지호', '과장', 1005, '2005-02-10', 590, 100, 30),
(1004, '이병헌', '부장', 1008, '2003-09-01', 600, null, 20),
(1005, '신동협', '과장', 1005, '2010-02-09', 450, 200, 30),
(1006, '장동건', '부장', 1008, '2003-10-09', 480, null, 30),
(1007, '이문세', '부장', 1008, '2004-01-08', 520, null, 10),
(1008, '김우성', '차장', 1003, '2004-03-08', 500, 0, 30),
(1009, '안성기', '사장', null, '1996-10-01', 1000, null, 20),
(1010, '이병헌', '과장', 1003, '2005-04-07', 500, null, 10),
(1011, '조형기', '사원', 1007, '2007-03-01', 280, null, 30),
(1012, '김혜정', '사원', 1006, '2007-08-09', 300, null, 20),
(1013, '박종훈', '부장', 1003, '2002-10-09', 560, null, 20),
(1014, '조인성', '사원', 1006, '2007-11-09', 250, null, 10)
;
select *
from emp;
-- 문제 17
-- 만들어 놓은 mydb에 아래와 같이 테이블을 만드시오.
-- 테이블명 : salgrade
-- 열이름 데이터형식 NULL허용 PK 기타
-- grade int x(not null) o 자동증가(auto_increment)
-- losal int x
-- hisal int x
drop table if exists salgrade;
create table salgrade(
grade int not null auto_increment primary key,
losal int not null,
hisal int not null
);
-- 문제 18
-- salgrade테이블에 아래와 같이 데이터를 입력하시오
-- null, 700, 1200
-- null, 1200, 1400
-- null, 1700, 2000
-- null, 2000, 3000
-- null, 3000, 9999
insert into salgrade values
(null, 700, 1200),
(null, 1200, 1400),
(null, 1700, 2000),
(null, 2000, 3000),
(null, 3000, 9999)
;
select *
from salgrade;
truncate salgrade;
-- 아래는 서버변수(시스템 변수).
-- set 구문 다음 @@붙으면 서버변수이다.
-- 증가를 1씩 증가 하게끔 설정하는 것임.
set @@auto_increment_increment = 1;
-- 문제 19
-- dept테이블을 이용하여 사원의 이름과 급여와 입사일자만 출력하시오.
-- ANY (in과 유사)
-- 다수의 비교값 중 한개라도 만족하면 true 이다.
select ename, sal, hiredate
from emp
where deptno = any(
select deptno
from dept
);
-- 문제 20
-- dept 테이블의 컬럼중에서 deptno를 "부서번호", dname를 "부서명"으로 출력하는 SQL문을 작성하시오.
select deptno as "부서번호", dname as "부서명"
from dept;
-- 문제 21
-- emp테이블의 직급이 중복되지 않고 한번씩 나열하는 SQL문을 작성하시오,
select distinct job
from emp;
-- 문제 22
-- emp테이블을 이용하여 급여가 300이하인 사원의 사원번호, 사원이름, 급여을 출력하시오.
select empno, ename, sal
from emp
where sal < 300;
-- 문제 23
-- emp테이블을 이용하여 이름이 '오지호'인 사원의 사원번호, 사원명, 급여를 출력하시오.
select empno, ename, sal
from emp
where ename = '오지호';
-- 문제 24
-- 급여가 250이거나 300이거나 500인 사원들의 사원번호와 사원명과 급여를 출력하시오.
-- 단, 2가지 표현으로 다 표현하시오.
-- in()이용하는 표현, 관계연산자를 이용하는 방법으로 하시오,
select empno, ename, sal
from emp
where sal in(250, 300, 500);
select empno, ename, sal
from emp
where sal = 250 or sal = 300 or sal = 500;
-- 문제 25
-- 급여가 250이거나 300이거나 500도 아닌 사원들을 출력하시오.
select empno, ename, sal
from emp
where sal not in(250, 300, 500);
select empno, ename, sal
from emp
where sal != 250 and sal != 300 and sal != 500;
-- 문제 26
-- 사원들 중에서 이름이 '김'으로 시작하는 사람이거나 이름 중에 '기'를 포함하는
-- 사원의 사원번호와 사원이륾을 출력하시오.
select empno, ename
from emp
where ename like '김%' or ename like '%기%';
-- 문제 27
-- 상급자가 없는 사원(사장)을 출력하시오.
select *
from emp
where mgr is null;query-7(대용량데이터, 수정, 삭제, 조건부 입력).sql
query-2(sqlDB초기화코드).sql
0.00MB
#------------------------------------------------------------20210105
-- 쿼리결과를 새테이블을 만든다.
-- CREATE TABLE 테이블명 SELECT * FROM 테이블명
-- 기존에 존재하지 않는 테이블이 새로 생성됨.(일종의 뷰와 동일한 효과)
-- city 테이블의 내용에서 국가코드가 'KOR'인 도시를 찾아 city_new테이블에 넣으시오.
use world;
create table city_new
select * from city where countrycode = 'KOR';
select * from city_new;
#---------------------------------------------------------------------
select *
from employees.employees;
use sqldb;
drop table if exists test4;
create table test4 (
id int,
fname varchar(20),
lname varchar(20)
);
-- test4에 대용량 데이터를 바로 입력.
insert into test4
select emp_no, first_name, last_name
from employees.employees;
select *
from test4;
-- 테이블을 만들면서 바로 select를 이용할수 있음 (test5)
drop table if exists test5;
create table test5(
select emp_no, first_name, last_name
from employees.employees
);
select *
from test5;
-- update 구문
-- update ... set ... where 형태로 사용
-- 만약 where(조건절)가 없다면, 모든 데이터를 수정해버림. 주의필요함.
-- update를 할때는 먼저 수정할 대상을 select문으로 확인하고 실행해야 안전함.
select *
from test4
where fname = 'kyoichi';
update test4
set lname='없음'
where fname = 'kyoichi';
-- 전체를 대상으로 update를 하는 경우도 있다.
-- 제품의 단가가 올린다던지, 월급이 5%인상 된다던지 등등
-- where 절이 필요 없음.
desc buy;
select *
from buy;
update buy
set price = price*1.5;
-- delete 문에서도 역시 where가 없다면 모든 데이터를 다 지우게 됨.
-- where절을 반드시 넣도록 함.
select *
from test4
where fname = 'Aamer';
delete from test4
where fname = 'Aamer';
# 대용량 데이터 불러오기하고 삭제
create table big1 (
select *
from employees.employees
);
create table big2 (
select *
from employees.employees
);
create table big3 (
select *
from employees.employees
);
-- 아래와 같이 데이터를 지우는 3가지 방법이 있음.
-- 속도적인 면을 살펴보면 drop -> trancate -> delete순
-- 으로 빠름.
-- delete구문은 트렌잭션으로 인하여 지우기 때문에 느릴수 밖에 없다.
-- ==>테이블과 함깨 데이터를 다 지우고 싶다면 drop을 권하고
-- 테이블 구조는 남아있게 할려면 truncate(DDL)구문 쓰기를 권장함.
# 제일 빠름
drop table big1;
# 트랜젝션 단위로 삭제가 되어 실행이 느림
delete from big2;
# DDL
truncate table big3;
select *
from big1;
select *
from big2;
select *
from big3;
#------------------------------------------------------------20210105
-- 참고 C:\workspace-query\query-2(sqlDB초기화코드).sql user테이블
use sqldb;
drop table if exists member;
create table member(
select userid, name, addr
from user
limit 3
);
-- 테이블을 위와 같이 만들면, 제약조건은 따로 복사가 안됨.
-- ==> alter구문 이용해서 직접 제약조건을 설정함.
select *
from member;
desc member;
desc user;
-- alter 라는 DDL구문은 테이블을 수정할때 사용함.
-- 아래는 member테이블에 PK를 추가하는 구문이다.
alter table member
add constraint pk_member primary key(userid);
-- 데이터를 입력하기 위해 아래코드 작성
-- 입력안됨. 그이유는 PK 중복때문.
-- PK는 데이터 무결성을 지키는 제약조건이다.
insert into member values
('BBK', '바비코', 'LA'),
('SKJ', '신국주', '서울'),
('CHUNLI', '춘리', '상해');
-- insert ignore into 구문은 PK가 등록되는 것을 무시하고 아래 2개의
-- 입력은 실행된다.
insert ignore member values('BBK', '바비코', 'LA');
insert ignore member values('SKJ', '신국주', '서울');
insert ignore member values('CHUNLI', '춘리', '상해');
select *
from member;
-- on duplicate key update구문은 현업에서 사용하지 않는다.
-- OCP, SQLD자격증 시험에 한번씩 나오므로 이론적으로 알아둘 필요는 있음.
insert into member values('BBK', '바비코', 'LA')
on duplicate key update name = '바비코', addr = 'LA';
insert into member values('SKS', '신기석', '대구')
on duplicate key update name = '신기석', addr = '대구';
