[Programmers / MySQL] Level 2 Find Lucy and Ella (59046)
[Programmers / MySQL] Level 2 Find Lucy and Ella (59046)
The ANIMAL_INS table holds information about animals that came into the animal shelter. The ANIMAL_INS table structure is as follows, where ANIMAL_ID, ANIMAL_TYPE, DATETIME, INTAKE_CONDITION, NAME, and SEX_UPON_INTAKE respectively represent the animal's ID, species, intake date, condition at intake, name, and sex/neuter status.
@RWBwritten at 2021-12-28 08:38:16
| Rank | Language Used |
|---|---|
| Level 1 | 🖼️ MySQL |
The ANIMAL_INS table holds information about animals that came into the animal shelter. The ANIMAL_INS table structure is as follows, where ANIMAL_ID, ANIMAL_TYPE, DATETIME, INTAKE_CONDITION, NAME, and SEX_UPON_INTAKE respectively represent the animal's ID, species, intake date, condition at intake, name, and sex/neuter status.
| NAME | TYPE | NULLABLE |
|---|---|---|
| ANIMAL_ID | VARCHAR(N) | FALSE |
| ANIMAL_TYPE | VARCHAR(N) | FALSE |
| DATETIME | DATETIME | FALSE |
| INTAKE_CONDITION | VARCHAR(N) | FALSE |
| NAME | VARCHAR(N) | TRUE |
| SEX_UPON_INTAKE | VARCHAR(N) | FALSE |
Write a SQL statement that retrieves the ID, name, and sex/neuter status of animals that came into the animal shelter with the names Lucy, Ella, Pickle, Rogan, Sabrina, or Mitty.
Please return the result ordered by ID. For example, if the ANIMAL_INS table is as follows
| ANIMAL_ID | ANIMAL_TYPE | DATETIME | INTAKE_CONDITION | NAME | SEX_UPON_INTAKE |
|---|---|---|---|---|---|
| A373219 | Cat | 2014-07-29 11:43:00 | Normal | Ella | Spayed Female |
| A377750 | Dog | 2017-10-25 17:17:00 | Normal | Lucy | Spayed Female |
| A353259 | Dog | 2016-05-08 12:57:00 | Injured | Bj | Neutered Male |
| A354540 | Cat | 2014-12-11 11:48:00 | Normal | Tux | Neutered Male |
| A354597 | Cat | 2014-05-02 12:16:00 | Normal | Ariel | Spayed Female |
Running the SQL statement should produce the following.
| ANIMAL_ID | NAME | SEX_UPON_INTAKE |
|---|---|---|
| A373219 | Ella | Spayed Female |
| A377750 | Lucy | Spayed Female |
- Retrieve animals whose NAME is Lucy, Ella, Pickle, Rogan, Sabrina, or Mitty.
- Retrieve ANIMAL_ID, NAME, and SEX_UPON_INTAKE.
- Retrieve them ordered alphabetically by ANIMAL_ID.
Using IN, you can retrieve data containing multiple elements at once.
SQL
SELECT ANIMAL_ID, NAME, SEX_UPON_INTAKE FROM ANIMAL_INS WHERE NAME IN ('Lucy', 'Ella', 'Pickle', 'Rogan', 'Sabrina', 'Mitty') ORDER BY ANIMAL_ID;
# Programmers# Algorithm# SQL# Level 2