Reference no: EM131310451

Make the menagerieGIS database using petGIS.sql

Look at the petGIS.png to see where the pets are.

1. Display the name, astext location, X and Y values of each pet

2. Display the pets with their location displayed in standard form of (X,Y)

3. Display the pets in order of X coordinate by Y coordinate

4. Pretend in the coordinate system that up/top is north, right is east, down/bottom is south, and left is west. Show the northernmost, southernmost, easternmost, and westernmost pets.

5. The centroid of the points, displayed as a point in standard form (X,Y) with each coordinate rounded to an integer. Alias: 'centroid'.
Hint: Use the concat function to build up a string concat(string1,string2,...)

6. The number of pets in the rectangle bounded by x=20 x=40 y=20 and y=60

7. The pets that are in the grid square whose X is between 100 and 150 and whose Y is between 300 and 350.

8. List the pets in order of distance from the origin.

9. The pet farthest from the origin (0,0)

10. The average distance from the orgin

11. The average distance from the orgin of each species

12. Use a variable (i.e. set command) for the centroid's X value. Use another variable (i.e. set command) for the centroid's Y value.
Display them.

13. Distance of each pet from the centroid. Hint: use the variables.

14. The pet closest to the centroid

15. The average distance of a pet from the centroid Centroid is the point that minimizes the sum of distances to all points.

16. Run the self-join that finds the distance between every pair of points. Modify it to find the pair of pets that is farthest apart.

17. Modify it to tell just the maximum distance.

18. Run the self-join that finds pets at same location.

b. What happens if in this self-join the < is replaced by a !=?

c. What happens if in this self-join the < clause is removed?

19. Using a Set variable, display the pets who are more than the average distance from the origin.