Answered step by step
Verified Expert Solution
Link Copied!

Question

1 Approved Answer

You were hired to do some data analysis for a local zoo. Below is the data table, including the necessary constraints and all the insert

You were hired to do some data analysis for a local zoo. Below is the data table, including the necessary constraints and all the insert statements to populate the database. --

Drop all the tables to clean up DROP TABLE Animal; -- ACategory: Animal category 'common', 'rare', 'exotic'. May be NULL -- TimeToFeed: Time it takes to feed the animal (hours) CREATE TABLE Animal ( AID NUMBER(3, 0), AName VARCHAR2(30) NOT NULL, ACategory VARCHAR2(18), TimeToFeed NUMBER(4,2), CONSTRAINT Animal_PK PRIMARY KEY(AID) ); INSERT INTO Animal VALUES(1, 'Galapagos Penguin', 'exotic', 0.5); INSERT INTO Animal VALUES(2, 'Emperor Penguin', 'rare', 0.75); INSERT INTO Animal VALUES(3, 'Sri Lankan sloth bear', 'exotic', 2.5); INSERT INTO Animal VALUES(4, 'Grizzly bear', 'common', 3.0); INSERT INTO Animal VALUES(5, 'Giant Panda bear', 'exotic', 1.5); INSERT INTO Animal VALUES(6, 'Florida black bear', 'rare', 1.75); INSERT INTO Animal VALUES(7, 'Siberian tiger', 'rare', 3.5); INSERT INTO Animal VALUES(8, 'Bengal tiger', 'common', 2.75); INSERT INTO Animal VALUES(9, 'South China tiger', 'exotic', 2.25); INSERT INTO Animal VALUES(10, 'Alpaca', 'common', 0.25); INSERT INTO Animal VALUES(11, 'Llama', NULL, 3.5); Since none of the managers in the zoo know SQL, it is up to you to write the queries to answer the following list of questions. 1. Find all the animals (their names) that take less than 2 hours to feed. 2. Find all the rare animals and sort the query output by feeding time (any direction) 3. Find the animal names and categories for the animals that are related to a bear (hint: remember the LIKE operator) 4. Return the listings for all animals whose rarity is not available in the database 5. Find the rarity rating of all animals that require between 1 and 2.2 hours to be fed 6. Find the names of the animals that are related to the tiger and are not common 7. Find the minimum and maximum feeding time amongst all the animals in the zoo 8. Find the average feeding time for the rare animals I am also including two more challenging queries that you may attempt to test your knowledge of SQL. They arent mandatory you will get 100% credit for the homework if you correctly answer queries 1-8. Find the listing of the animal that requires the longest feeding time Find the names of the animals that can be fed in less than the average overall time + 20% (i.e. in less than 1.2 * average feeding time)

Step by Step Solution

There are 3 Steps involved in it

Step: 1

blur-text-image

Get Instant Access to Expert-Tailored Solutions

See step-by-step solutions with expert insights and AI powered tools for academic success

Step: 2

blur-text-image

Step: 3

blur-text-image

Ace Your Homework with AI

Get the answers you need in no time with our AI-driven, step-by-step assistance

Get Started

Recommended Textbook for

Select Healthcare Classification Systems And Databases

Authors: Katherine S. Rowell, Ann Cutrell

1st Edition

0615909760, 978-0615909769

More Books

Students also viewed these Databases questions

Question

19.2. Explain why SOAs should be based on standards.

Answered: 1 week ago

Question

=+7. What is the big message you want them to know?

Answered: 1 week ago