Outputting a list of books that meet the conditions (with.MySQL)
This article is about printing a list of books that meet the conditions (with.MySQL).
I want to solve the coding test problem, find out how to solve it differently from the retrospective of the problem I solved, and get to know.
Let’s get to the problem first.
Problem
In the BOOK table, please find a list of books belonging to the ‘Humanities’ category published in 2021 and write a SQL statement that outputs the book ID (BOOK_ID) and the date of publication (PUBLISHED_DATE). Please arrange the results in ascending order based on the publication date.
Problem Description The following is a book table of books sold at a bookstore.
The Book Table is a table containing information on each book and has the following structure.
BOOK TABLE
| Column name | Type | Nullable | Description |
|---|---|---|---|
| BOOK_ID | INTEGER | FALSE | 도서ID |
| CATEGORY | VARCHAR(N) | FALSE | Category (Economy, Humanities, Novels, Life, Technology) |
| AUTHOR_ID | INTEGER | FALSE | 저자ID |
| PRICE | INTEGER | FALSE | Sale Price (KRW) |
| PUBLISHED_DATE | DATE | FALSE | 출판일 |
problem solving
SELECT BOOK_ID, DATE_FORMAT(PUBLISHED_DATE, '%Y-%m-%d') AS PUBLISHED_DATE
FROM BOOK
WHERE SUBSTR(PUBLISHED_DATE, 1, 4) = '2021' AND CATEGORY = '인문'
Solution Description
Use the DATE_FORMAT function to format the date in the required format.
After filtering 2021 using SUBSTR to see if it meets the conditions, check whether the category is humanities and print it out with AND.