Choose ice cream made with fruit (with.MySQL)

This is an article about fruit ice cream selection (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

Please write a SQL statement that inquires about the taste of ice cream, which has a total order of more than 3,000 ice cream in the first half of the year and whose main ingredient is fruit, in the order of the total order.

The following are the FIRST_HALF table with order information for the first half of the ice cream shop and the ICECREAM_INFO table with information on ice cream ingredients.

The structure of the FIRST_HALF table is as follows, and SHIPMENT_ID, FLAVOR, and TOTAL_ORDER represent the shipping number from the ice cream factory to the ice cream store, the ice cream flavor, and the total order volume of ice cream in the first half of the year, respectively.

The default key in the FIRST_HALF table is FLAVOR.

FIRST_HALF Table Structure

The ICECREAM_INFO table structure is as follows, and FLAVOR and INGREDITENT_TYPE represent the ice cream flavor and the ingredient type of ice cream, respectively.

In INGREDIENT_TYPE, if the main ingredient of the ice cream is sugar, it is entered as sugar, and if the main ingredient of the ice cream is fruit, it is entered as fruit_based.

The default key for ICECREAM_INFO is FLAVOR.

The FLAVOR in the ICECREAM_INFO table is the foreign key in the FLAVOR in the FIRST_HALF table.

ICECREAM_INFO Table Structure

problem solving

SELECT ICECREAM_INFO.FLAVOR
FROM ICECREAM_INFO, FIRST_HALF
WHERE ICECREAM_INFO.FLAVOR = FIRST_HALF.FLAVOR AND FIRST_HALF.TOTAL_ORDER > 3000 AND ICECREAM_INFO.INGREDIENT_TYPE = 'fruit_based'
ORDER BY FIRST_HALF.TOTAL_ORDER DESC;

Solution Description

This SQL query queries ice cream flavors that meet certain conditions and returns ordered results in order of order.

The query extracts data from the ICECREAM_INFO table and the FIRST_HALF table, and the main components are as follows.

First, the SELECT section specifies the columns to look up.

ICECREAM_INFO.FLAVOR stands for ice cream flavor, which outputs as a result.

The FROM section then specifies the default table on which to run the query.

Use two tables, ICECREAM_INFO and FIRST_HALF.

Specifies explicit join conditions to query these two tables at the same time.

Next, in the WHERE section, you set specific conditions to filter the data you need.

ICECREAM_INFO.FLAVOR = FIRST_HALF.FLAVOR is a condition in which the FLAVOR columns of both tables join the same row.

FIRST_HALF.TOTAL_ORDER > 3000 is a condition for selecting ice cream flavors with orders exceeding 3000 in the first half of the year.

ICECREAM_INFO.INGREDIENT_TYPE = ‘fruit_based’ is a condition for selecting ice cream flavors using fruit-based ingredients.

These conditions allow you to select only ice cream flavors using fruit-based ingredients, with orders exceeding 3000 in the first half of the year.

Finally, sort the results through the ORDER BY clause.

Sort by First_HALF.TOTAL_ORDER DESC, and sort the results in order of high order.

This allows you to check the results in order from the high-order ice cream flavor.

Conclusion

This query allows you to view ice cream flavors using fruit-based ingredients in order, with orders exceeding 3000 during the first half of the year.

This makes it easy to identify popular fruit-based ice cream flavors.