Print the list of restaurants located in Seoul - SELECT
Coding test practice - Print the list of restaurants located in Seoul
SELECT I.REST_ID, I.REST_NAME, I.FOOD_TYPE, I.FAVORITES, I.ADDRESS, ROUND(AVG(R.REVIEW_SCORE), 2) AS SCORE FROM REST_INFO I JOIN REST_REVIEW R ON I.REST_ID = R.REST_ID GROUP BY I.REST_ID HAVING I.ADDRESS LIKE '서울%' ORDER BY SCORE DESC, I.FAVORITES DESC
It's in the SELECT category, but ORDER BY, GROUP BY HAVING, JOIN ON and so on all show up.
Looking at the parts:
AVG(row): the average of the row
ROUND(number, digits): returns the value rounded to the given digits
LIKE '서울%': returns only the columns that start with the text '서울' (Seoul)
Looking at the whole:
JOIN REST_REVIEW R ON I.REST_ID = R.REST_ID
With this join command, all the columns of R whose R.REST_ID matches I's REST_ID are combined.
This is a good example for understanding the result of just JOIN ON.
Call the top one table 1.
Doing JOIN table 2 ON 1. empID = 2.empID
on table 1 and table 2 gives table 3.
You can see that when table 2 has several columns with empId 1, table 3 ends up with several of table 1's empId.
So if you GROUP BY on the joined table and do AVG(column name), you get the average for each empId.
(If you don't know GROUP BY well, this explanation probably won't make sense )
Finally, just sort with ORDER BY to fit the given situation.