Reference no: EM132059339

Given the attached physical schema in Assign2P18ERD undertake the following:

1. Using SQL Server create a database called Assign2P18

2. Define all primary keys, foreign keys and data

3. Populate the tables with sufficient data to adequately test all aspects of your queries.

4. Develop the SQL queries to satisfy the questions below.

No views should be employed here. While no question would require it you may use dynamic temporary tables as a technique.

1. Show for each species a count of the experts that are employed by zoos in the USA.

2. List the zoo name, employee name, salary and start date of each employee that is a world's best expert for a species. Sequence the output by employee name within zoo name.

3. Show the name of any country that has the more than 2 zoos.

4. Show for each zoo in Canada a count of animals held. Sequence the output by highest to lowest count.

5. Show for each zoo the number of different animal species it holds.

6. List the details for employees in Canadian zoos with a salary of at least 95000 or with an expertise in the Tiger species. Sequence the output by employee name.
7 List the details of zoos that hold more than 2 Lions born in captivity in 2016

8. List the details for the zoos in China that have more than 3 animals that belong to the Panda species.

9. List the names, gender, salaries and start dates of all male employees that are the world's best expert for an endangered species (status = E).

10. List the details for the zoo that has the employee with the highest salary in any zoo.

11. List the details for any species for which there are animals held in any zoo in China.

12. List the details for the zoos that have animals belonging to more than 4 different species.
Sequence the output alphabetically by zoo within city.

13. List details for the animals that have a mother that is held in a zoo that is different from their child's current zoo.

14. List the detail of animals that are in any zoo but whose mother is currently a Boston city zoo.

15. List the species details for the species that have a world's best expert working in a zoo in the USA that also holds animals of that same species. Show each species only once.

16. List the details for the tiger expert employee that has the highest salary for their expertise.

17. List the details for any endangered species which have 3 or more animals held in the "Metro Toronto" zoo.

18. List the details for all animals born in captivity in 2016 that belong to an endangered species (status = E).

19. Show the count of how many species experts are employed at the "Metro Toronto" zoo.

20. List the details of mothers that have more than 2 offspring that are held in Canadian zoos

Note: Queries that do not return sufficient data will lose marks. If no output is generated that query will receive a zero.