Total number of fish caught (with.MySQL)

This is an article about the total number of fish caught (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 outputs the number of BASS and SNAPPER caught in the FISH_INFO table.

Please name the column ‘FISH_COUNT’.

Problem Description

The FISH_INFO table used by the fishing app contains information on fish caught.

The structure of the FISH_INFO table is as follows, and ID, FISH_TYPE, LENGTH, and TIME indicate the ID of the fish caught, the type of fish (number), the length of the fish caught (cm), and the date of the fish caught.

The FISH_NAME_INFO table contains information about the name of the fish.

The structure of the FISH_NAME_INFO table is as follows, where FISH_TYPE and FISH_NAME are fish types (numbers) and fish names (characters), respectively.

FISH_INFO Table

However, if the caught fish is less than 10 cm long, the LENGTH is NULL, and there is no NULL in the LENGTH.

problem solving

SELECT COUNT(FN.FISH_TYPE) AS FISH_COUNT
FROM FISH_INFO AS FI JOIN FISH_NAME_INFO AS FN ON FI.FISH_TYPE = FN.FISH_TYPE
WHERE FISH_NAME LIKE 'BASS' OR FISH_NAME LIKE 'SNAPPER';

Solution Description

This SQL query calculates and returns the number of fish of a particular kind (BASS or SNAPPER).

The query extracts the data by joining the FISH_INFO table and the FISH_NAME_INFO table, with key components as follows.

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

COUNT (FN.FISH_TYPE) AS FISH_COUNT counts the types of fish that meet the conditions and outputs the number as FISH_COUNT alias.

This refers to the total number of fish corresponding to BASS or SNAPPER.

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

Here, we use the FISH_INFO table, which we alias FI.

Next, use the JOIN clause to join the FISH_NAME_INFO table with the FISH_INFO table.

The condition of the join is FI.FISH_TYPE = FN.FISH_TYPE, which is linked based on the FISH_TYPE columns of both tables.

By doing so, you can combine fish information and fish name information to inquire.

In the WHERE section, you set specific conditions to filter the required data.

The FISH_NAME LIKE ‘BASS’ OR FISH_NAME LIKE ‘SNAPPER’ condition selects when the fish is named ‘BASS’ or ‘SNAPPER’.

Under these conditions, only fish named BASS and SNAPPER will be selected.

This query allows you to calculate the total number of fish named BASS or SNAPPER and return them to FISH_COUNT.

This makes it easy to identify a specific type of fish population and can be useful for analysis or statistics on a specific fish species.