SQL 총연습

https://www.w3schools.com/mysql/trymysql.asp?filename=trysql_select_all

위 링크에서 SQL문 총연습을 했다.

 

https://dev.mysql.com/doc/refman/8.0/en/string-functions.html

더 많은 문자열 함수를 참고할 수 있는 웹페이지이다.

 

각 유형에 대해 여러 SQL문을 직접 실행했다. 궁금한 것은 chat gpt의 도움을 받기도 했다. 간단한 문제가 중간마다 있는데 답은  한 줄 띄고 바로 밑에 적어봤다.

패딩
SELECT LPAD('ABC', 5, '-'), RPAD('ABC', 5, '-');
Products 테이블에서 SupplierID와 Price의 값이 다음과 같은 단위로 출력되도록 하세요
SELECT LPAD(SupplierID,3,'0') as SupplierID, Price from Products;
치환
SELECT REPLACE('맥도날드에서 맥도날드 햄버거를 먹었다.', '맥도날드', '버거킹');
SELECT REPLACE(Description, ', ', ' and ') FROM Categories;
콤마를 and로 변경하려고 했는데, and and가 되었다. 이를 해결하시오 SELECT REPLACE(REPLACE(Description,', and',','),',',' and ') FROM Categories; SELECT REPLACE(REPLACE(Description, ', ', ' and '),'and and',' and ') FROM Categories;
위치검색
INSTR(S, s) S중 s의 첫 위치 반환, 없을 시 0
SELECT INSTR('ABCDE', 'ABC'), INSTR('ABCDE', 'BCDE'), INSTR('ABCDE', 'C'), INSTR('ABCDE', 'DE'), INSTR('ABCDE', 'F');
Customers 테이블에서 CustomerName의 first name이 5글자 이하인 사람의 이름을 출력하시오 hint. first name은 ' ' full name의 첫 스페이스 앞에 있다 SELECT CustomerName FROM Customers where INSTR(CustomerName,' ') between 1 and 6;
자료형 변환
CAST(A AS T) A를 T 자료형으로 변환 CONVERT(A, T) A를 T 자료형으로 변환
SELECT '01' = '1', CAST('01' AS DECIMAL) = CAST('1' AS DECIMAL);
SELECT '01' = '1', CONVERT('01', DECIMAL) = CONVERT('1', DECIMAL);
시간 관련 함수
CURRENT_DATE, CURDATE 현재 날짜 반환 CURRENT_TIME, CURTIME 현재 시간 반환 CURRENT_TIMESTAMP, NOW 현재 시간과 날짜 반환
SELECT CURDATE(), CURTIME(), NOW();
문자열에 따른 시간 생성 함수
DATE 문자열에 따라 날짜 생성 TIME 문자열에 따라 시간 생성
SELECT '2021-6-1' = '2021-06-01', DATE('2021-6-1') = DATE('2021-06-01'), '1:2:3' = '01:02:03', TIME('1:2:3') = TIME('01:02:03');
SELECT '2021-6-1 1:2:3' = '2021-06-01 01:02:03', DATE('2021-6-1 1:2:3') = DATE('2021-06-01 01:02:03'), TIME('2021-6-1 1:2:3') = TIME('2021-06-01 01:02:03'), DATE('2021-6-1 1:2:3') = TIME('2021-06-01 01:02:03'), DATE('2021-6-1') = DATE('2021-06-01 01:02:03'), TIME('2021-6-1 1:2:3') = TIME('01:02:03');
Orders 테이블에서 OrderDate가 1997-1-1에서 1997-1-31 사이의 데이터를 가져오시오 SELECT * FROM Orders where OrderDate between DATE('1997-1-1') and DATE('1997-1-31');
시간 반환 함수
YEAR 주어진 DATETIME값의 년도 반환 MONTHNAME 주어진 DATETIME값의 월(영문) 반환 MONTH 주어진 DATETIME값의 월 반환 WEEKDAY 주어진 DATETIME값의 요일값 반환(월요일: 0) 숫자로 반환 DAYNAME 주어진 DATETIME값의 요일명 반환 DAYOFMONTH, DAY 주어진 DATETIME값의 날짜(일) 반환
SELECT OrderDate, YEAR(OrderDate) AS YEAR, MONTHNAME(OrderDate) AS MONTHNAME, MONTH(OrderDate) AS MONTH, WEEKDAY(OrderDate) AS WEEKDAY, DAYNAME(OrderDate) AS DAYNAME, DAY(OrderDate) AS DAY FROM Orders;
SELECT OrderDate, CONCAT(CONCAT_WS('/', YEAR(OrderDate), MONTH(OrderDate), DAY(OrderDate)), ' ', UPPER(LEFT(DAYNAME(OrderDate), 3))) FROM Orders;
Orders 테이블에서 월요일만 추출해보세요 [hint OrderDate가 0 이면 월요일] SELECT * FROM Orders where WEEKDAY(OrderDate)=0;
시분초 반환 함수
HOUR 주어진 DATETIME의 시 반환 MINUTE 주어진 DATETIME의 분 반환 SECOND 주어진 DATETIME의 초 반환
SELECT HOUR(NOW()), MINUTE(NOW()), SECOND(NOW());
날짜 연산 함수
ADDDATE, DATE_ADD 시간/날짜 더하기 SUBDATE, DATE_SUB 시간/날짜 빼기
SELECT ADDDATE('2021-06-20', INTERVAL 1 YEAR), ADDDATE('2021-06-20', INTERVAL -2 MONTH), ADDDATE('2021-06-20', INTERVAL 3 WEEK), ADDDATE('2021-06-20', INTERVAL -4 DAY), ADDDATE('2021-06-20', INTERVAL -5 MINUTE), - ADDDATE('2021-06-20 13:01:12', INTERVAL 6 SECOND);
Orders 테이블의 OrderDate에 있는 날짜를 위에서 제시된 조건에 맞춰 출력하시오 SELECT ADDDATE(OrderDate, INTERVAL 1 YEAR), ADDDATE(OrderDate, INTERVAL -2 MONTH), ADDDATE(OrderDate, INTERVAL 3 WEEK), ADDDATE(OrderDate, INTERVAL -4 DAY), ADDDATE(OrderDate, INTERVAL -5 MINUTE), ADDDATE(OrderDate, INTERVAL 6 SECOND) FROM Orders;
시간차 계산 함수
DATEDIFF 두 시간/날짜 간 일수차 TIMEDIFF 두 시간/날짜 간 시간차
SELECT OrderDate, NOW(), DATEDIFF(OrderDate, NOW()) FROM Orders;
SELECT TIMEDIFF('2021-06-21 15:20:35', '2021-06-21 16:34:41');
Orders 테이블에서 OrderDate가 1996년10월10일을 기준으로 5일 미만 차이가 나는 날짜를 출력하시오 SELECT OrderDate FROM Orders where ABS(DATEDIFF('1996-10-10',OrderDate))<5;
마지막 날짜 계산 함수
LAST_DAY 해당 달의 마지막 날짜
SELECT OrderDate, LAST_DAY(OrderDate), DAY(LAST_DAY(OrderDate)), DATEDIFF(LAST_DAY(OrderDate), OrderDate) FROM Orders;
시간 포맷 함수
DATE_FORMAT 시간/날짜를 지정한 형식으로 반환
%Y 년도 4자리 %y 년도 2자리 -> 끝의 2자리만 표시 %M 월 영문 %m 월 숫자 %D 일 영문(1st, 2nd, 3rd...) %d, %e 일 숫자 (01 ~ 31) %T hh:mm:ss %r hh:mm:ss AM/PM %H, %k 시 (~23) %h, %l 시 (~12) %i 분 %S, %s 초 %p AM/PM
SELECT DATE_FORMAT(NOW(), '%M %D, %Y %T'), DATE_FORMAT(NOW(), '%y-%m-%d %h:%i:%s %p'), DATE_FORMAT(NOW(), '%Y년 %m월 %d일 %p %h시 %i분 %s초');
현재 시간을 Y년 m월 d일 AM/PM h시 i분 s초로 출력하시오 위에서 출력한 날짜에서 AM은 오전으로 PM은 오후로 자동 변경되어 출력되게 하시오 [hint replace함수] SELECT DATE_FORMAT(NOW(), '%Y년 %m월 %d일 %p %h시 %i분 %s초'); SELECT REPLACE(REPLACE(DATE_FORMAT(NOW(), '%Y년 %m월 %d일 %p %h시 %i분 %s초'),'AM','오전'),'PM','오후');
진위함수
IF(조건, T, F) 조건이 참이라면 T, 거짓이면 F 반환
SELECT IF (1 > 2, '1는 2보다 크다.', '1은 2보다 작다.');
SELECT CASE WHEN -1 > 0 THEN '-1은 양수다.' WHEN -1 = 0 THEN '-1은 0이다.' ELSE '-1은 음수다.' END;
Products 테이블에서 Price가 30보다 크면 Expensive 나머지는 Cheap으로 출력하는 열을 따로 만들어서 Price가 20 미만이면 저가, 20과 30 사이이면 일반, 그 외는 고가로 출력하시오 SELECT Price, IF (Price>30, 'Expensive', 'Cheap') as '비교', CASE WHEN Price < 20 THEN '저가' WHEN Price between 20 and 30 THEN '일반' ELSE '고가' END AS '분류' FROM Products;
NULL 관련 함수
IFNULL(A, B) A가 NULL일 시 B 출력
SELECT IFNULL('A', 'B'), IFNULL(NULL, 'B');
집계함수: null값은 집계하지 않는다 MAX 가장 큰 값 MIN 가장 작은 값 COUNT 갯수 (NULL값 제외) SUM 총합 AVG 평균 값
select Country from Customers group by Country;
SELECT CategoryID FROM Products GROUP BY CategoryID;
SELECT Country, City, CONCAT_WS(', ', City, Country) FROM Customers GROUP BY Country, City;
Orders 테이블에서 group by로 OrderDate를 묶은 다음, 동일 OrderDate의 갯수를 출력하시오. SELECT COUNT(*), OrderDate FROM Orders group by OrderDate;
SELECT ProductID, SUM(Quantity) AS QuantitySum FROM OrderDetails GROUP BY ProductID ORDER BY QuantitySum DESC;
SELECT CategoryID, MAX(Price) AS MaxPrice, MIN(Price) AS MinPrice, TRUNCATE((MAX(Price) + MIN(Price)) / 2, 2) AS MedianPrice, TRUNCATE(AVG(Price), 2) AS AveragePrice FROM Products GROUP BY CategoryID;
Customers 테이블에서 Country와 City를 콤마(,)로 묶고 CustomersID 수를 적으세요 hint] count 함수 SELECT CONCAT_WS(',',Country,City) as Location,COUNT(CustomerID) as Numbers FROM Customers group by Location;
ROLLUP
SELECT Country, COUNT(*) FROM Suppliers GROUP BY Country WITH ROLLUP;
SELECT CategoryID, MAX(Price) AS MaxPrice, MIN(Price) AS MinPrice, TRUNCATE((MAX(Price) + MIN(Price)) / 2, 2) AS MedianPrice, TRUNCATE(AVG(Price), 2) AS AveragePrice FROM Products GROUP BY CategoryID with rollup;		위에서 출력했던 쿼리문 맨 뒤에 with rollup; 을 추가하면 각 항목의 총합을 알 수 있다
HAVING
HAVING - 그룹화된 데이터 걸러내기
SELECT Country, COUNT(*) AS Count FROM Suppliers GROUP BY Country HAVING Count >= 3;
SELECT COUNT(*) AS Count, OrderDate FROM Orders WHERE OrderDate > DATE('1996-12-31') GROUP BY OrderDate HAVING Count > 2;	WHERE는 그룹하기 전 데이터, HAVING은 그룹 후 집계에 사용한다.
SELECT CategoryID,MAX(Price) as MaxPrice,Min(Price) as MinPrice,truncate((MAX(Price)+Min(Price))/2,2) as MedianPrice, truncate(avg(Price),2) as AveragePrice FROM Products group by CategoryID HAVING CategoryID in (4,5,8);
Products 테이블에서 CategoryID를 가져오는데, MaxPrice이름으로 Price 최대값과 MinPrice이름으로 Price최소값을 가져오고, MedianPrice이름으로 중간값을 계산해서 가져오고, AveragePrice이름으로 평균값 계산하기 이때, CategoryID는 2보다 큰 값이고, AveragePrice 값은 20과 30사이인 것을 출력 SELECT CategoryID,MAX(Price) as MaxPrice,Min(Price) as MinPrice,truncate((MAX(Price)+Min(Price))/2,2) as MedianPrice, truncate(avg(Price),2) as AveragePrice FROM Products group by CategoryID HAVING CategoryID>2 and AveragePrice between 20 and 30;
DISTINCT
DISTINCT - 중복된 값들을 제거합니다. select 문에서 사용한다.
SELECT DISTINCT CategoryID FROM Products;
SELECT DISTINCT Country FROM Customers ORDER BY Country;
SELECT DISTINCT Country, City FROM Customers ORDER BY Country;
Customers 테이블에서 Country를 가져오는데, 각 County마다 겹치지 않는 도시는 몇 개인지 출력하기 출력 시 Country기준으로 알파벳 정렬 SELECT Country,COUNT(DISTINCT City) as CityNum FROM Customers GROUP BY Country order by Country;
서브쿼리
SELECT CategoryID, CategoryName, Description, (SELECT ProductName FROM Products WHERE ProductID = 1) FROM Categories;
SELECT * FROM Products WHERE Price < (SELECT AVG(Price) FROM Products);
Categories 테이블에서 CategoryID, CategoryName, Description을 가져온다 이때 (가져올때 조건, 서브쿼리 내용은) Products 테이블에서 Price가 50 초과인 CategoryID중에서 CategoryID를 가져온다 [CategoryID는 Price가 50 초과인 것들 중에서 가져온다. hint : IN 함수 ~중에서 가져오기] SELECT CategoryID,CategoryName,Description FROM Categories WHERE CategoryID in (select CategoryID from Products where Price>50);
서브쿼리 조건
~ ALL 서브쿼리의 모든 결과에 대해 ~하다 ~ ANY 서브쿼리의 하나 이상의 결과에 대해 ~하다
SELECT * FROM Products WHERE Price > ALL (SELECT Price FROM Products WHERE CategoryID = 2);
Categories 테이블에서 CategoryID, CategoryName, Description을 가져오는데 서브쿼리 내용 : CategoryID가 Products 테이블에서 Price가 50 초과인 CategorytID에 해당되는 [hint : any 하나 이상에 해당된다 = 어떤 값에 해당된다 = ..중에 하나에 해당된다] 여기에서는 IN을 사용해도 결과는 동일하다 SELECT CategoryID,CategoryName,Description FROM Categories where CategoryID=any(select CategoryID from Products where Price>50);
상관 서브쿼리
SELECT ProductID, ProductName, (SELECT CategoryName FROM Categories C WHERE C.CategoryID = P.CategoryID) AS CategoryName FROM Products P;
SELECT SupplierName, Country, City, (SELECT COUNT(*) FROM Customers C WHERE C.Country = S.Country) AS CustomersInTheCountry, (SELECT COUNT(*) FROM Customers C WHERE C.Country = S.Country AND C.City = S.City) AS CustomersInTheCity FROM Suppliers S;
Categories 테이블에서 CategoryID와 CategoryName을 가져온다 서브쿼리1. Products 테이블의 CategoryID와 Categories 테이블의 CategoryID가 같은 Produects 테이블에서의 최대(MAX) Price를 MaximumPrice 라는 이름으로 출력 서브쿼리2. Products 테이블의 CategoryID와 Categories 테이블의 CategoryID가 같은 Produects 테이블에서의 평균(AVG) Price를 AveragePrice 라는 이름으로 출력 SELECT CategoryID,CategoryName, (select MAX(Price) from Products P where P.CategoryID=C.CategoryID) as MaximumPrice, (select AVG(Price) from Products P where P.CategoryID=C.CategoryID) as AveragePrice FROM Categories C;
조인
JOIN(INNER JOIN) - 내부 조인
관계형 데이터베이스는 데이터의 중복을 피하기 위해서 테이블을 나눈다. join은 나눠진 테이블들에서 정보를 가져오는 작업
- 조인은 두 개의 테이블을 서로 묶어서 하나의 결과를 만들어 내는 것을 말한다. - INNER JOIN(내부 조인)은 두 테이블을 조인할 때, 두 테이블에 모두 지정한 열의 데이터가 있어야 한다. - OUTER JOIN(외부 조인)은 두 테이블을 조인할 때, 1개의 테이블에만 데이터가 있어도 결과가 나온다. - CROSS JOIN(상호 조인)은 한쪽 테이블의 모든 행과 다른 쪽 테이블의 모든 행을 조인하는 기능이다. - SELF JOIN(자체 조인)은 자신이 자신과 조인한다는 의미로, 1개의 테이블을 사용한다.
SELECT * FROM Categories C JOIN Products P ON C.CategoryID = P.CategoryID;
그냥 join이라고 쓰면 내부조인인 된다
SELECT C.CategoryID, C.CategoryName, P.ProductName FROM Categories C JOIN Products P ON C.CategoryID = P.CategoryID;
Products 테이블과 Suppliers 테이블을 JOIN, Products테이블의 SupplierID와 Suppliers테이블의 SupplierID가 같고 Products테이블에서 ProductName을 가져오고, Suppliers테이블에서 SupplierName을 가져와서 ‘by'로 연결하고 컬럼이름은 Product로 한다. Suppliers테이블에서 Phone을 가져오고, Products테이블에서 Price를 가져온다 Price는 50초과를 가져오고, ProductName으로 order by 한다 [order by default값은 asc 오름차순] SELECT CONCAT(P.ProductName,'by',S.SupplierName) as Product, S.Phone, P.Price FROM Products P Join Suppliers S on P.SupplierID=S.SupplierID where P.Price>50 order by P.ProductName;
2개가 아닌 여러 테이블 join하기
SELECT C.CategoryID, C.CategoryName, P.ProductName, O.OrderDate, D.Quantity FROM Categories C JOIN Products P ON C.CategoryID = P.CategoryID JOIN OrderDetails D ON P.ProductID = D.ProductID JOIN Orders O ON O.OrderID = D.OrderID;
조인 후 그룹핑하기
SELECT C.CategoryID, C.CategoryName, MIN(O.OrderDate) AS FirstOrder, MAX(O.OrderDate) AS LastOrder, SUM(D.Quantity) AS TotalQuantity FROM Categories C JOIN Products P ON C.CategoryID = P.CategoryID JOIN OrderDetails D ON P.ProductID = D.ProductID JOIN Orders O ON O.OrderID = D.OrderID GROUP BY C.CategoryID;
SELECT C.CategoryID, P.ProductID, C.CategoryName, MIN(O.OrderDate) AS FirstOrder, MAX(O.OrderDate) AS LastOrder, SUM(D.Quantity) AS TotalQuantity FROM Categories C JOIN Products P ON C.CategoryID = P.CategoryID JOIN OrderDetails D ON P.ProductID = D.ProductID JOIN Orders O ON O.OrderID = D.OrderID GROUP BY C.CategoryID, P.ProductID;
self Join 같은 테이블끼리 조인하기
SELECT E1.EmployeeID, CONCAT_WS(' ', E1.FirstName, E1.LastName) AS Employee, E2.EmployeeID, CONCAT_WS(' ', E2.FirstName, E2.LastName) AS NextEmployee FROM Employees E1 JOIN Employees E2 ON E1.EmployeeID + 1 = E2.EmployeeID;
LEFT/RIGHT OUTER JOIN - 외부 조인 [둘 중에 한쪽에만 있어도 가져오는 조인]
SELECT E1.EmployeeID, CONCAT_WS(' ', E1.FirstName, E1.LastName) AS Employee, ifnull(E2.EmployeeID,'N'), CONCAT_WS(' ', E2.FirstName, E2.LastName) AS NextEmployee FROM Employees E1 LEFT JOIN Employees E2 ON E1.EmployeeID + 1 = E2.EmployeeID ORDER BY E1.EmployeeID;
Customers 테이블에 Suppliers 테이블을 조인하라 조인조건: Customers.City=Suppliers.City and Customers.Country=Suppliers.Country 출력되는 컬럼명 Customers테이블의 CustomerName, City, Country Suppliers 테이블의 SupplierName Customers 테이블에는 있는데 Suppliers 테이블에는 없어도 출력시켜라 -> left or right join SELECT C.CustomerName,S.SupplierName,C.City,C.Country FROM Customers C Left join Suppliers S on C.City=S.city and C.Country=S.Country;
반대로 Suppliers 테이블에는 있는데 Customers테이블에는 없어도 출력시켜라 -> left or right join SELECT C.CustomerName,S.SupplierName,C.City,C.Country FROM Customers C Right join Suppliers S on C.City=S.city and C.Country=S.Country;
위의 쿼리문에서 left join이나 right join을 할 때 CustomerName이 없는 경우에는 No Customer 라고 출력되고, SupplierName이 없는 경우에는 No Supplier 가 출력되도록 하라 SELECT ifnull(C.CustomerName,'--NO CUSTOMER--') as CustomerName,S.SupplierName,C.City,C.Country FROM Customers C Right join Suppliers S on C.City=S.city and C.Country=S.Country; SELECT C.CustomerName,ifnull(S.SupplierName,'--NO SUPPLIER--') as SupplierName,C.City,C.Country FROM Customers C Left join Suppliers S on C.City=S.city and C.Country=S.Country;
CROSS JOIN - 교차 조인 : 조건 없이 모든 조합 반환(A * B) join을 할 때 on으로 조건을 붙이지 않고, 모든 조합을 하는 경우. A테이블의 행수*B테이블의 행수
SELECT E1.LastName, E2.FirstName FROM Employees E1 CROSS JOIN Employees E2 ORDER BY E1.EmployeeID;
Orders 테이블과 OrderDetails 테이블을 CROSS JOIN하라 Orders 테이블의 CustomerID와 OrderDetails 테이블의 ProductID를 가져와라 Orders 테이블의 OrderID로 order by 하라 SELECT O.CustomerID,D.ProductID FROM Orders O CROSS JOIN OrderDetails D order by O.OrderID;
UNION 중복을 제거한 집합 UNION ALL 중복을 제거하지 않은 집합
SELECT CustomerName AS Name, 'CUSTOMER' FROM Customers UNION SELECT SupplierName AS Name, 'SUPPLIER' FROM Suppliers ORDER BY Name;
SELECT CustomerName AS NAME,City,Country,'CUSTOMER' FROM Customers UNION SELECT SupplierName AS NAME,City,Country,'SUPPLIER' FROM Suppliers ORDER BY NAME;

 

많은 시간이 걸렸지만 다양한 예제를 접할수록 안목이 깊어지는 것 같다. 좀 더 힘내보자.