Database - Assignment

Background

You will be working with a set of tables for a mobile. To access these tables in oracle create the following synonyms:

SQL> CREATE SYNONYM ANIMAL FOR VBRUNO.ANIMAL;

SQL> CREATE SYNONYM KEEPER FOR VBRUNO.KEEPER;

SQL> CREATE SYNONYM CAGE FOR VBRUNO.CAGE;

SQL> CREATE SYNONYM SPECIE FOR VBRUNO.SPECIE;

SQL> CREATE SYNONYM FED FOR VBRUNO.FED;

SQL> CREATE SYNONYM FOOD FOR VBRUNO.FOOD;

Assignment Exercise

You are to prepare 15 SQL query statements that will provide answers to the following 15 requests for information.

You must submit the syntax of the queries themselves, not all the output.

1. Show a list of all the "Female" animals, that are "White", and are located in a cage whose location starts with an "A", "B", or "V". Just show the name of the animal.

2. Create a list of animals that are "Brown" in colour and less than 2 years old. Show the animal name and species name.

3. Create a view that lists all the animals that eat "straw". Show the animal no, animal name and scientific name.

4. Show the detail for cage number 147, also show the name of the animals in this cage, along with the keeper that looks after the cage, and the keeper that looks after each animal in the Include the species name for each animal in cage.

5. List all keepers from a suburb that has "WEST" in the name, who look after White animals from the snakes species. Show the keeper's full name and the animal name.

6. List all keepers that earn more than $37,000 a year at the zoo. Show the keepers name, the total salary for a year (assume 38hrs week) and the number of years they have worked at the zoo.

7. List all the cages whose size is "S"mall. Show the cage details and the name of the keeper who cleans it.

8. List all the cages that do not have any animals in them. Show all the cage details.

9. List for each MONTH of the year, how many animals are having their birthday.

10. List each of the keepers who are both vets and cleaners (‘A'). Show the keeper's name, age, how long they have worked at the zoo and their supervisor's name.

11. Create a list of each food bought by the zoo and a sum of the total quantity of each food used per day.

12. Show all the cages with six animals or more. Show the cageno, cage size, cage cost and cage location along with the keepers full name who cleans the cage.

13. For each keeper, show the number of cages cleaned by the keeper and the total cost of the cages cleaned for each keeper. Make sure you show the keeper's name. Show the highest total cost first and the lowest last on the list.

14. Need to show all the details related to a particular cage, just like in question 12, except that it must be the cage(s) that has the MOST animals in it only.

15. Create your own query. It must include a nested query. Submit the following:

a) question your query is answering

b) the SQL query