Question
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
Get Instant Access to Expert-Tailored Solutions
See step-by-step solutions with expert insights and AI powered tools for academic success
Step: 2
Step: 3
Ace Your Homework with AI
Get the answers you need in no time with our AI-driven, step-by-step assistance
Get Started