전산 교육 테스트 대비

논리연산자

Customers 데이터에서 고객번호가 10보다 작으면서 이름이 “B”로 시작하는 것을 출력하세요.

SELECT * FROM Customers

WHERE CustomerID < 10 AND CustomerName LIKE "B%";

SELECT * FROM Customers

WHERE (CustomerID BETWEEN 1 AND 9) AND (CustomerName LIKE "B%");

LIKE 연산자

SELECT * FROM testTable WHERE name LIKE "김%";

IN 연산자

SELECT *

FROM customers

WHERE country IN ("Mexico", Germany", "USA")

INSERT문

CREATE TABLE
testTable(
	id int,
    name text(20)
);

INSERT INTO testable VALUES
(1, "김덕수"),
(2, "김덕수"), 
(3, "박동수"),
(4, "김동");

INSERT INTO SELECT

INSERT INTO testTable SELECT * FROM testTable WHERE id <= 2;

DELETE문

testTable에서 사람의 이름 끝이 "수" 자인 사람을 삭제하기

DELETE FROM testTable WHERE name like "%수";

UPDATE문 형식

testTable에서 id 가 4인 사람의 이름을 "스티브 정"으로 바꾸기

UPDATE testTable SET name = "스티브 정" WHERE id = 4;

< 정리 >

데이터 검색, 추가, 삭제, 변경 정리

SELECT 컬럼명 FROM 테이블명 [WHERE 조건] ;

INSERT INTO 테이블명 (컬럼....) VALUES (값....);

DELETE FROM 테이블명 [WHERE 조건] ;

UPDATE 테이블명 SET 컬럼1=값1, 컬럼2=값2.... [WHERE 조건 ];

CREATE TABLE 진학반명단 ( 
id int,
s_name char(20) );

insert into 진학반명단 values 
(1, '홍길동'),
(2, '강감찬'),
(3, '김유신'),
(4, '홍범도'), 
(5, '곽재우'), 
(6, '이순신'), 
(7, '이철수');

CREATE TABLE 입시생 ( 
id int,
university char(20) );

insert into 입시생 values 
(1, '서일대' ),
(2, '고려대'),
(5, '고려대'),
(7, '숭실대');

CREATE TABLE 자격증응시생 ( 
id int,
certificate char(20) );

insert into 자격증응시생 values
(2, '정보처리'),
(4, '전자기기'),
(6, '정보처리'), 
(7, '컴활용');

create database order_manger;
use order_manger;


create table 영업사원(
영업사원번호 int,
영업사원명 char(10),
생년 char(8),
전화 char(15),
주소 char(100)
);


alter table 영업사원 add primary key pk_영업사원번호(영업사원번호);


-- SET SQL_SAFE_UPDATES = 0; UPDATE 옵션 설정

update 영업사원
set 주소 =  "서울 도봉동 455"
where 영업사원번호 = 1;

 INSERT  INTO 영업사원(영업사원번호,영업사원명, 생년, 전화, 주소) VALUES
  (1,"박수동", 1988, "010-325-1234", "서울 도봉동 455"),
  (2,"도인범", 1978, "010-222-5432", "서울 성북동 290"),
  (3, "천길호", 1984, "010-6767-8787", "서울 방배동 54"),
  (5, "김희철", 1990, "010-622-0001", "서울 홍제동 244");


create table 주문(
주문번호 int,
일시 datetime,
금액 char(20),
고객번호 int,
영업사원번호 int
);


alter table 주문 add primary key pk_주문번호(주문번호);
ALTER TABLE 주문 ADD FOREIGN KEY (영업사원번호) REFERENCES 영업사원(영업사원번호);
ALTER TABLE 주문 ADD FOREIGN KEY (고객번호) REFERENCES 고객(고객번호);


  INSERT  INTO  주문(주문번호, 일시, 금액, 고객번호, 영업사원번호) VALUES
  (1, "2021-03-02 10:11", 400000, 1, 1),
  (2, "2021-03-02 10:11", 1650000, 2, 2),
  (3, "2021-03-02 11:10", 1050000, 3, 3),
  (4, "2021-03-02 11:17", 1800000, 4, 4),
  (5, "2021-03-02 11:51", 2570000, 5, 2);


create table 고객(
고객번호 int primary key,
고객명 char(20),
전화 char(20),
주소 char(50)
);



INSERT  INTO  고객(고객번호,고객명, 전화, 주소) VALUES
 (1, "김원일", "02-335-2544", "서울 중곡동 311"),
 (2,"박동수", "02-277-7654", "서울 서초동 656"),
 (3, "최길원", "031-988-0987", "경기 안양 76"),
 (4, "박인수", "02-778-9888", "서울 은평동 87"),
 (5, "황철언", "02-490-9900", "서울 평창동 81");


create table 주문상품(
주문번호 int,
상품코드 int,
수량 char(20)
);

alter table 주문상품 add primary key pk_주문상품번호(주문번호,상품코드);

select * from 주문상품;

alter table 주문상품
add constraint idx1 foreign key(주문번호) References 주문(주문번호)
on delete cascade, 
add constraint idx2 foreign key(상품코드) References 상품(상품코드)
on delete cascade;


INSERT  INTO  주문상품(주문번호,상품코드,수량) VALUES
 (1, 1, 50),
 (2, 2, 10),
 (3, 3, 15),
 (4, 4, 20),
 (5, 5, 21);

create table 상품(
상품코드 int,
상품명 char(20),
단가 char(20)
);

alter table 상품 add primary key pk_상품코드(상품코드);

INSERT  INTO  상품(상품코드, 상품명, 단가) VALUES
  (1, "티엘론단열", 80000),
  (2, "경질우레탄", 55000),
  (3, "난연점착제", 70000),
  (4, "T동파이프", 90000),
  (5, "광폭점착제", 120000),
  (6, "열반사단열", 100000);

JOIN 예제 및 풀이

순서 : from -> on -> where

4) 주문의 상품별로 판매수량(Quantity)과 금액(Price)을 출력하세요. (제품 ID가 4보다 작은 경우만 선별)

SELECT orderdetailid, orderid, productid, productname, Price, Quantity, (Price * Quantity) as 금액

FROM orderdetails

INNER JOIN products USING (productID)

WHERE productID < 4

ORDER BY orderdetailid;

5) 주문별 제품의 판매금액을 기준으로 20%의 영업 수당을 산출해 보세요. + 영업사원 이름도 같이 출력

(Orders, OrderDetails, Prodcuts)

SELECT orderdate, orderid, employeeid, concat(lastname, "-", firstname) as 이름,

(quantity * price) as 금액, Round(quantity * price *0.2, 2) as 수당

FROM orders

INNER JOIN orderdetails USING(orderid)

INNER JOIN products USING(productid)

INNER JOIN employees USING(employeeid)

ORDER BY orderid;

7) 각 주문의 상품별 금액이 4000$이상인 제품을 출력해 보세요 (Orderdetails, Products)

SELECT OrderDetailID, OrderID, ProductID, ProductName, (Quantity * Price) AS 금액

FROM Orderdetails

INNER JOIN Products USING (ProductID)

WHERE (Quantity * Price) >= 4000

ORDER BY OrderDetailID;

8) 각 주문의 상품별 금액이 4000$이상 제품들이 어떤 류인지 카테고리가 궁금합니다. (Orderdetails, Products, Categorys)

SELECT OrderDetailID, OrderID, ProductID, ProductName, CategoryID, CategoryName,
(Quantity * Price) AS 금액

FROM Orderdetails
INNER JOIN Products USING (ProductID)
INNER JOIN Categories USING (Categoryid)

WHERE (Quantity * Price) >= 4000
ORDER BY OrderDetailID;

UNION

- 아무런 시험도 보지 않은 사람을 출력해 보세요.

SELECT * FROM 진학반명단

LEFT JOIN (SELECT * FROM 입시생

UNION

SELECT * FROM 자격증응시생) AS 응시

USING (id)

WHERE 응시.id IS NULL;

Group By

2) 영업사원별로 주문 성사금액과 그에 따른 20%의 수당을 산출해 보세요

영업사원별로(Group by) 주문 성과금액과 그에 따른 20%의 수당을 산출

SELECT OrderDate, EmployeeID, OrderID,

Sum(Quantity * Price) as 금액

Sum(Quantity * Price * 0.2) as 수당

FROM Orders

INNER JOIN OrderDetails Using (OrderID)

INNER JOIN Products Using (ProductID)

Group BY EmployeeID;

3) 영업사원별 주문 성사금액이 40,000$ 이상인 사람에 대해 25%의 특별수당을 산출해 보세요

영업사원별(Group by) 주문 성사금액이 40,000$ 이상(Having)인 사람에 대해 25%의 특별수당을 산출

SELECT EmployeeID,

Round(Sum(quantity * Price), 2) AS 금액,

Round(Sum(quantity * Price * 0.25), 2) AS 특별수당

FROM Orders

INNER JOIN Products Using (ProductID)

Group By EmployeeID

Having Sum(quantity * Price) >= 40000;

4) 영업사원 1번에 대해 주문성사 총금액과 20%의 수당을 산출해 보세요

영업사원 1번(Where)에 대해 주문성사 총금액과 20%의 수당을 산출해 보세요 (영업사원별)

SELECT EmployeeID,

Round(Sum(quantity * price), 2) as 총금액,

Round(Sum(quantity * price * 0.2), 2) as 수당

FROM Orders

INNER JOIN OrderDetails Using (OrderID)

INNER JOIN Products Using (ProductID)

WHERE EmployeeID = 1

GROUP BY EmployeeID;

8) 판매금액 기준으로 봤을 때 기여도가 높은 제품 10개만 출력해 보세요.

판매금액 기준, 기여도가 높은 제품 10개(Desc, 내림차순 / Limit)만 출력

SELECT O.productid, P.productname,

Sum(O.quantity), (P.price * O.quantity) AS 판매금액

FROM orderdetails O INNER JOIN products P Using (productid)Group by O.productidOrder by 판매금액 DescLimit 10;

1) 스칼라(Scalar) 서브쿼리

1) 주문별로 영업사원 이름 출력

SELECT *,
(select lastName from employees

where orders.employeeID= employees.employeeID) AS `Emp name` FROM orders ;

SELECT *,

(SELECT LastName,

FROM Employees

WHERE Orders.EmployeeID = Employees.EmployeeID) AS 'EMP NAME'

FROM Orders;

2) 인라인 뷰(Inline view)

2) 모든 주문중에 판매개수 상위 10개의 품명을 출력 (Products, Orderdetails)

모든 주문중에 판매개수 상위 10개의 품명 출력

SELECT PD.ProductID, PD.ProductName, Top_TEN.*

FROM Products PD

INNER JOIN (SELECT OrderdetailID, ProductID, Quantity

FROM Orderdetails

ORDER BY Quantity DESC

Limit 10) AS Top_TEN

Using (PD.ProductID);

3) 중첩(Nested) 서브쿼리

6) 멕시코에 거래이력(Customers)을 가지고 있는 영업사원 모두 출력 (Ordres, Employees, Customers)

SELECT *

FROM Orders O

INNER JOIN Employees E

Using (EmployeeID)

WHERE O.CustomerID IN (SELECT CustomerID

FROM Customers

WHERE Country = "Mexico");

Case When 표현식

Products 테이블에서 상품리스트를 출력하되, 가격에 따라 아래와 같이 구분하세요.

- 20$ 이하 : 초저가류
- 21$ ~ 40$ 이하 : 저가류
- 40$ ~ 70$ 이하 : 중저가류

- 70$ ~ 100$ 이하 : 고가류

- 101$이상 : 초고가류

SELECT 가격분류, Count(Price)

FROM

(SELECT Price,

CASE WHEN Price <= 20 THEN "1.초저가류"

WHEN Price > 20 and Price <= 40 THEN "2.저가류"

WHEN Price > 40 and Price <= 70 THEN "3.중저가류"

WHEN Price > 70 and Price <= 100 THEN "4.고가류"

ELSE "5.초고가류"

END AS 가격분류

FROM Products) AS P

GROUP BY 가격분류;

< 연산도우미 –함수(Function)사용 >

1) Insert("BEAUTIFUL", 2, 4, "KK") : BKKIFUL (문자열의 부분문자열 교체, 2번째부터 4개)

2) Instr("BEAUTIFUL", "AUT") : 3 (부분문자열 위치, 없으면 0)

3) dateDiff('2022-08-15', '2022-09-03') : -19 ( 날짜 차이를 반환)

Products 테이블에서 가격(Price)이 가장 큰 제품의 거래현황을 출력

< CREATE, FOREIGN KEY, REFERENCES >

CREATE TABLE 주문상품 (

주문번호 int ,

상품코드 int ,

수량 int not null,

PRIMARY KEY (주문번호, 상품코드) ,

FOREIGN KEY (주문번호) REFERENCES 주문 (주문번호) ,

FOREIGN KEY (상품코드) REFERENCES 상품 (상품코드)

);

 
CREATE TABLE 주문상품 (
주문번호 int ,
상품코드 int , 
수량 int not null,
PRIMARY KEY (주문번호, 상품코드) ,
FOREIGN KEY (주문번호) REFERENCES 주문 (주문번호) ,
FOREIGN KEY (상품코드) REFERENCES 상품 (상품코드)
);

ALTER TABLE 주문 ADD PRIMARY KEY PK_주문번호(주문번호);
ALTER TABLE 주문 ADD FOREIGN KEY (영업사원번호) REFERENCES 영업사원(영업사원번호);
ALTER TABLE 주문 ADD FOREIGN KEY (고객번호) REFERENCES 고객(고객번호);

alter table 주문상품
add constraint idx1 foreign key(주문번호) References 주문(주문번호)
on delete cascade, 
add constraint idx2 foreign key(상품코드) References 상품(상품코드)
on delete cascade;

FOREIGN KEY, REFERENCES, ON DELET CASCADE (예시)

ALTER TABLE 주문상품

ADD Constraint Idx1 FOREIGN KEY(주문번호) REFERENCES 주문(주문번호)

ON DELETE CASCADE

ALTER TABLE 주문상품

ADD Constraint Idx1 FOREIGN KEY(OrderID) REFERENCES Order(OrderID)

ON DELETE CASCADE

ALTER TABLE 주문

ADD PRIMARY KEY (영업사원번호) REFERENCES 영업사원(영업사원번호);

ALTER TABLE 주문

ADD PRIMARY KEY (EmployeeID) REFERENCES Employees(EmployeeID);

View(뷰)

-주문별로 어떤 제품인지와 단가, 수량, 금액, 어떤 고객의 주문인지 함께 보여주는 뷰를 order_status 라는 이름으로 작성해보기

CREATE VIEW order_status
AS
SELECT orderdetailid, orderid, productid, productname, 
	price, quantity, price*quantity as 금액, customerID, customerName
FROM orderdetails
	INNER JOIN products USING(productid)
    INNER JOIN orders USING(orderID)
    INNER JOIN customers USING(customerID)
ORDER BY orderdetailid;

Procedure VS. Function 비교

이슈	프로시저	함수
호출방법	Call로 호출, 결과값을 반환 X	SQL문에서 식으로 호출, 결과값을 반드시 반환
반환갯수	반환값(out, inout)이 있거나 없거나	반환값(return)이 반드시 하나
전달	입력(in)이 있거나 없거나	입력이 있거나 없거나
코드수용	안에 삽입, 삭제, 수정 등의 작업 가능 안에 동적쿼리, 트렌젝션 작업 가능 예외 처리 블록 가능	안에 선택질의만 가능 안에 동적, 트렌 불가 예외처리 코드 불가

'전산 관련 시험 > DB(SQLP, SQLD) 문제풀이' 카테고리의 다른 글

[DB] SQL 튜닝 힌트 (0)	2024.09.10
(DB, 데이터베이스) DBMS 구성 (0)	2024.03.03
전산 교육 정리_SQL(3) (0)	2023.05.12
전산 교육 정리_SQL(2) (0)	2023.05.11
전산 교육 정리_SQL(1) (0)	2023.05.10

Learning_EunBi

전산 교육 테스트 대비_SQL

Procedure VS. Function 비교

'전산 관련 시험 > DB(SQLP, SQLD) 문제풀이' 카테고리의 다른 글

댓글

티스토리툴바

전산 교육 테스트 대비_SQL

Procedure VS. Function 비교

'전산 관련 시험 > DB(SQLP, SQLD) 문제풀이' 카테고리의 다른 글

관련글

댓글

티스토리툴바