Each owner owns one or more cats, and every cat has exactly one owner.
• The name and surname of each owner must be stored in the database.
• The name of each cat must be stored in the database.
• Each cat belongs to one specific breed, and many cats can belong to the same breed.
• The description of each breed must be stored in the database.
• A cat can enter many competitions, and many cats can enter the same competition.
• The description of each competition must be stored in the database.
Assume that the database has been populated with data. Write the SQL code to
generate the report shown below. This report lists the breeds in ascending
alphabetical order with the number of cats that belong to that breed.
BREED NUMBER OF
CATS
B 2
M 1
P2
S 1
Sp 3
SELECT breed, COUNT(cat_id) AS "NUMBER OF CATS"
FROM cats
GROUP BY breed
ORDER BY breed ASC;
To generate the desired report, you can use the following SQL query:
```
SELECT description AS BREED, COUNT(*) AS "NUMBER OF CATS"
FROM cats
INNER JOIN breeds ON cats.breed_id = breeds.breed_id
GROUP BY description
ORDER BY description ASC;
```
This query joins the `cats` table with the `breeds` table on the `breed_id` column. It then groups the results by the breed's description and counts the number of cats in each breed. Finally, the results are ordered in ascending alphabetical order based on the breed description.