CMIS 320
Name:________________________
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
****** SKIP *************
Using Set variables, the pets who are above average in both weight and cuteness
Of the pets that are of above average weight, the one that is farthest from the origin