MYSQL 연습하기 with w3school - 1

MYSQL 연습하기

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

MySQL Tryit Editor v1.0

오른쪽에 버튼 클릭하면 이렇게 나오며 블록 친 부분에 코드를 입력하면 된다. 

 

 

여기서 결과는 확인해 볼 수 있다. 고민하고 확인해봐도 되고 따라치면서 의미를 익히길 권장한다.

무슨 구문이 해석이 되는지 모른다면 chatgpt에 명령어를 넣고 물어보면 답을 잘 해준다.(인공지능을 잘 활용하자)

 

 

기본적으로 MYSQL의 명령어는 대소문자 상관없이 쳐도 된다. 하지만 테이블명은 제대로 쳐야된다.

 

Q1)

Customers에서 회원id(CustomerID), 거주도시(city), 거주국가(country)를 출력하시요

SELECT CustomerID, City, Country FROM Customers;

Q2)

주문정보(Orders)중에서 건별ID(orderID), 주문고객ID, 주문일자(OrderDate)를 10건만 출력하시요

->limit함수는 제한해서 출력

SELECT OrderID, CustomerID, OrderDate FROM Orders limit 10;

Q3)

Customers 테이블에서 Mexico에 사는 사람들 정보를 추출하시요 [홑따음표 쌍따음표 무관]

SELECT * FROM Customers where Country = 'Mexico';

Q4)

Customers 테이블에서 Mexico에 살지 않는 회원들의 정보를 추출하시요 --> !=

SELECT * FROM Customers where Country != 'Mexico';

Q5)

국가 이름이 'S'로 시작하는 국가에 거주하는 사람들 정보를 모두 추출하시요 --> S% , like

SELECT * FROM Customers where Country LIKE 'S%';

Q6)

국가 이름이 'A'로 끝나는 국가에 거주하는 사람들 정보를 모두 추출하시요 [country 알파벳 순으로 정렬]

SELECT * FROM Customers where Country like '%A';                  order by country

Q7)

국가 이름이 ‘S’로 시작하지 않는 국가에 거주하는 사람들 정보를 추출하시요

SELECT * FROM Customers where Country not like 'S%';

Q8)

Mexico에 거주 중인 사람과 이름(Contact Name)이 Yang Wang인 사람을 추출 ---> or where

SELECT * FROM Customers where Country = 'Mexico' or contactname = 'Yang Wang';

Q9)

Mexico에 사는 사람과 Sweden에 사는 사람들의 거주 정보 출력하기. 나라별로 묶어서 출력하시요. order by country

SELECT * FROM Customers where country = 'Mexico' or country = 'Sweden';

Q10)

Mexico에 사는 사람, Sweden에 사는 사람, USA에 사는 사람 정보 출력하기 [in 사용해서 추출하기]

SELECT * FROM Customers where country in ('Mexico', 'Sweden', 'USA') order by country;

Q11)

회원정보 테이블(Customers)에서 모든 컬럼 정보를 나라이름이 알파벳순으로 정렬 출력하시요. 역순도 출력하시요

SELECT * FROM Customers order by country asc;  <--- default asc 
SELECT * FROM Customers order by country desc;

Q12)

원정보 테이블(Customers)에서 모든 컬럼 정보를 5번째 컬럼(city)이 알파벳순이 되도록 정렬 출력하시요. 역순도 출력하시요

SELECT * FROM Customers order by 5 asc;  <--- default asc 
SELECT * FROM Customers order by 5 desc;

Q13)

제품정보(Products) 테이블에서 가격이 높은 순서부터 모든 컬럼값이 출력되도록 하시요

SELECT * FROM Products order by price desc;

Q14)

Customers 테이블에서 나라별로 몇 명이 가입했는지 집계하시요

SELECT country, count(*) from Customers group by country;

Q15)

위에서 추출한 결과를 많이 가입한 나라 순서대로 정렬하시요

SELECT country, count(*) from Customers group by country order by count(*) desc;

Q16)

위에서 추출한 결과의 컬럼명을 count(*)에서 cntID로 변경하시요

SELECT country, count(*) as cntID from Customers group by country;

Q17)

위에서 추출한 결과에서 나라별 가입한 사람이 5명 이상인 경우만 추출하시요

SELECT country, count(*) from Customers group by country having count(*) >=5 order by country;

Q18)

당사가 가지고 있는 모든 제품들의 가격을 합하면 얼마인지 계산해주세요 (Products, price)

SELECT sum(price) FROM Products;

 

Q19)

productID가 30번 이하인 제품들의 평균가격을 알려주세요. 추출될 컬럼명은 avgPrice로 만들어주세요

SELECT avg(price) as avgPrice FROM Products where productID <= 30;

Q20)****

employee들의 생일을 년도별 날짜로 그 해, 출력후그 월의 생일자는 각각 몇명이 있는지 출력하시요.

첫번째는 각각 한개가 있다고 출력한 것이고, 두번째는 년도는 다르나 월이 같은 것을 출력해준 것이다.

SELECT substr(birthdate, 1, 7) as Month, count(*) as cnt FROM Employees group by Month order by substr(birthdate, 6,7);

SELECT SUBSTR(birthdate, 1, 7) AS Month,(SELECT COUNT(*) FROM Employees t2 WHERE SUBSTR(t1.birthdate, 6, 2) = SUBSTR(t2.birthdate, 6, 2)) AS cnt
FROM Employees t1 GROUP BY Month ORDER BY substr(birthdate,6,2);

Q21)

위 문제에서 년도별로 내림차순 출력하세요. 생일 년도의 컬럼명은 Year

SELECT substr(birthdate, 1, 4) as Year, count(*) as cnt FROM Employees group by Year order by Year desc;

Q22)

제품의 공급자별로 판매가격의 평균을 구해주세요. (Products 테이블에서 SupplierID를 가지고)(컬럼명은 avgPrice)

SELECT supplierid, avg(price) as avgPrice FROM Products group by supplierid;

Q23)

productID가 20번이상인 제품들 중 가격이 가장 비싼 제품의 가격을 알려주세요

(products 테이블에서)(출력할 컬럼명은 maxPrice)

SELECT max(price) as maxPrice FROM Products where productid >=20;

Q24)

전체 제품 중 가격이 가장 낮은 제품 정보를 출력하시요

select * from Products where price = (select min(price) from Products);

Q25)

전체 제품 중 가격이 가장 높은 제품 정보를 출력하시요

select * from Products where price = (select max(price) from Products);

Q26)

일별(orderdate) 주문 건수를 집계해주세요. 단 정렬순서는 최신순(최근날짜순)으로 하세요.

이 경우에서는 주문건수 중 같은 주문자가 주문한 것이면 동일하게 계산했다.

select orderdate, count(distinct orderid) as cnt from Orders group by orderdate order by orderdate desc;

Q27)

월별 주문 건수를 집계하세요. 정렬순서는 최신순(최근날짜순)으로, 컬럼명은 Month와 cnt (substr과 as를 함께 사용하시요)

select substr(orderdate,1,7) as Month, count(distinct orderid) as cnt from Orders group by Month order by orderdate desc;

Q28)

가입한 고객들의 나라별의 중복되지 않는 도시 갯수를 각각 집계하여 추출하시요. 나라별로 도시가 몇 개씩 나오는지 집계하기. 출력되는 컬럼명 cityNum

select country, count(distinct city) as cityNum from Customers group by country;

Q29)

products 테이블에서 카테고리ID가 3이하인 제품을 ‘소비재’로 4-5인 제품을 ‘사치재’로, 그리고 그 외 모든 제품을 'etc'로 분류되게 하여 카테고리ID와 분류된 정보가 출력되도록 하세요. 이때 분류된 정보의 컬럼명은 분류

 

SELECT ProductID, ProductName,
		 CASE When CategoryID <= 3 then '소비재'
        	 When CategoryID >= 4 AND CategoryID <= 5 then '사치재'
        	 else 'etc'
    		 END AS 분류
FROM products order by 분류 desc;

Q30)

Customers 테이블과 Orders 테이블을 아래 조건별로 조인해서 출력하시요

SELECT *
FROM Orders o
INNER JOIN Customers c ON o.customerid = c.customerid;

Q31)

Customers테이블과 Orders테이블을 Inner join해서 추출하고, 이 중에 국적이 Mexico인 고객 정보만 출력하시요

select * from Customers c inner join Orders o on c.customerid = o.customerid   where c.country = 'Mexico' order by c.customerid;

Q32)

orders 테이블과 orderDetils 테이블을 inner join하고 Quantitiy가 50개 이상인 row만 오름차순으로 추출하시요

select * from Orders o inner join OrderDetails od on o.orderid = od.orderid where quantity >= 50 order by quantity;

Q33)

orders 테이블과 orderDetails 테이블을 inner join하고, customerid별로 구매수량(Quantity)의 합계를 구하시요

출력 컬럼명은 QuantitySum이고, 오름차순으로 정렬해서 출력시키기. 어떤 고객이 얼마큼 구매했는지 파악됨

select o.customerid, sum(od.quantity) as QuantitySum from Orders o inner join OrderDetails od on o.orderid = od.orderid  group by o.customerid order by QuantitySum;

Q34)

customers 테이블과 orders 테이블을 left join 하고 customerid, orderdate, orderid, customername 정보를 추출하세요. 단 1996년도 7월에 주문된 건만 추출하세요. 날짜는 내림차순으로

select c.customerid, o.orderdate, o.orderid, c.customername from Customers c left join Orders o on c.customerid = o.customerid where o.orderdate between '1996-07-01' and '1996-07-31' order by o.orderdate desc;

Q35)

Employees 테이블에서 fullname이라는 컬럼으로 first name과 last name을 사이 한칸 띄우고 출력하시요

SELECT CONCAT(FirstName, ' ', LastName) AS fullname FROM Employees;

SELECT CONCAT_ws(' ', FirstName, LastName) AS fullname FROM Employees;

Q36)

이와 같이 출력되게하시오.

SELECT orderdate,substr(orderdate,1,4) as year, substr(orderdate,6,2) as month , substr(orderdate,9,2) as day FROM Orders;

Q37)

사용자가 CategoryName 테이블에서 Beverages를 검색을 할 때, 앞뒤에 스페이스를 주고 검색을 하면, 내용이 출력되지 않는다. 예를 들어 앞에 한칸 띄우고 ' Beverages'로 검색하거나 앞뒤로 한칸씩 띄우고 검색하면 ‘ Beverages '

select * from Categories where CategoryName = Concat(trim(' Beverages '))

Q38)

Products 테이블에서 SupplierID와 Price의 값이 다음과 같은 단위로 출력되도록 하세요

SELECT LPAD(SupplierID, 3, 0) AS SupplierID, (SELECT FORMAT(ROUND(Price, 3), 2)) FROM Products;

Q39)

콤마를 and로 변경하려고 했는데, and and가 되었다. 이를 해결해보세요...

SELECT REPLACE(REPLACE(Description, ', and', ' and and'),' and and',' and') AS modified_description
FROM Categories;

Q40)

Customers 테이블에서 CustomerName의 first name이 5글자 이하이 사람의 이름을 출력하세요

hint. first name은 ' ' full name의 첫 스페이스 앞에 있다

SELECT substring_index(CustomerName, ' ', 1) AS FirstName
FROM Customers
WHERE LENGTH(SUBSTRING_INDEX(CustomerName, ' ', 1)) <= 5;

Q41)

Orders 테이블에서 OrderDate가 1997-1-1에서 1997-1-31 사이의 데이터를 가져오시요

SELECT * FROM Orders WHERE OrderDate BETWEEN '1997-01-01' AND '1997-01-31';

 

Q42)

Orders 테이블에서 월요일만 추출해보세요 [hint OrderDate가 0 이면 월요일]

SELECT * FROM Orders WHERE WEEKDAY(OrderDate) = 0;

Q43)

Orders 테이블의 OrderDate에 있는 날짜를 위에서 제시된 조건에 맞춰 출력하시요

SELECT

ADDDATE('2021-06-20', INTERVAL 1 YEAR)형식 

SELECT 
    OrderDate,
    ADDDATE(OrderDate, INTERVAL 1 YEAR),
    ADDDATE(OrderDate, INTERVAL -2 MONTH),
    ADDDATE(OrderDate, INTERVAL 3 WEEK),
    ADDDATE(OrderDate, INTERVAL -4 DAY)
FROM Orders;

 

Q44)

Orders 테이블에서 OrderDate가 1996년10월10일을 기준으로 5일 미만 차이가 나는 날짜를 출력하시요

SELECT *
FROM Orders
WHERE ABS(DATEDIFF(OrderDate, '1996-10-10')) < 5;

Q45)

현재 시간을 Y년 m월 d일 AM/PM h시 i분 s초로 출력하시요

위에서 출력한 날짜에서 AM은 오전으로 PM은 오후로 자동 변경되어 출력되게 하시요 [hint replace함수]

SELECT DATE_FORMAT(CONVERT_TZ(NOW(), 'UTC', 'Asia/Seoul'), '%Y년 %m월 %d일 %p %h시 %i분 %s초') AS formatted_time,
       REPLACE(REPLACE(DATE_FORMAT(CONVERT_TZ(NOW(), 'UTC', 'Asia/Seoul'), '%p'), 'AM', '오전'), 'PM', '오후') AS am_pm;

Q46)

Products 테이블에서 Price가 30보다 크면 Expensive, 작으면 Cheap 출력하고

Price가 20 미만이면 저가, 20과 30사이이면 일반, 그외는 고가로 출력하시요

select case when price>30 then 'Expensive'
            when price<20 then 'Cheap'
            when 20<price<30 then '일반'
            else '고가'
	    end
            as PriceClassification, productid,price
            from Products order by PriceClassification

Q47)

Orders 테이블에서 group by로 OrderDate를 묶은 다음, 동일 OrderDate의 갯수를 출력하시요. 아래와 같이

특정 날짜에 몇번의 주문이 있었는지 파악할 수 있다

select orderdate,'주문갯수', count(*) from Orders group by orderdate

Q48)

Customers 테이블에서 Country와 City를 콤마 , 로 묶고, CustomersID 수를 적으세요. 아래의 출력과 같이

hint] count함수

select concat(Country, ', ',City) as Location , count(CustomerID) as Numbers from Customers group by concat(Country, ', ',City)

Q49)

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 20 < AveragePrice < 30;

Q50)

Customers 테이블에서 Country를 가져오는데, City가 겹치지 않는 County는 몇개인지 출력하기. 출력시에 Country기준으로 알파벳 정렬. 옆의 출력처럼

select Country, count(distinct city) as numbers from Customers group by Country;