Q#6. Formulate the following queries, using SQL and SDT operations. (16 pts) The schema of the database is shown below and an underlined attribute is a primary key. Country (Name: varchar (35), Cont: varchar (35), Pop: integer, GDP: integer, Life-Exp: integer, Shape: polygon) City (Name: varchar (35), Country: varchar (35), Pop: integer, Capital: char(1), Shape: point) River (Name: varchar (35), Origin: varchar (35), Length: integer, Shape: line) List of field descriptions: > Cont (Continent) > Pop (Population) (millions) > GDP (Gross Domestic Product) is the total value of goods and services produced in a country in one fiscal year. (billions) > Life-Exp attribute represents the life expectancy in years (rounded to the nearest integer) for residents of a country. > Capital is a fixed character type of length one; a city is a capital of a country, or it is not. (Y', 'N') > Origin attribute specifies the country name where the river originates. * List of sample OGIS Spatial Data Model Operations (you can require to use): Buffer. returns a geometry that consists of all points whose distance from the given geometry is less than or equal to the specified distance. Cross: returns true if the interior of a surface intersects with a curve. Distance: returns the shortest distance between two geometries. Intersection: is a topological predicate to determine if two geometries intersect. Length: returns non-zero length of the river in each country it passes through. Overlap: returns true if the interiors of two geometries have nonempty intersection. Touch: returns true if the boundaries of two surfaces intersect but the interiors do not. a. List the length of the rivers in each of the countries they pass through. b. List all countries, ordered by number of neighboring countries. C. The Kzlrmak, also known as the Halys River is the longest river entirely within Turkey and can supply water to cities that are within 100 km. List the cities that can use water from the Kzlrmak. d. List the GDP and the distance of the country's capital city to Ankara for all countries. Q#6. Formulate the following queries, using SQL and SDT operations. (16 pts) The schema of the database is shown below and an underlined attribute is a primary key. Country (Name: varchar (35), Cont: varchar (35), Pop: integer, GDP: integer, Life-Exp: integer, Shape: polygon) City (Name: varchar (35), Country: varchar (35), Pop: integer, Capital: char(1), Shape: point) River (Name: varchar (35), Origin: varchar (35), Length: integer, Shape: line) List of field descriptions: > Cont (Continent) > Pop (Population) (millions) > GDP (Gross Domestic Product) is the total value of goods and services produced in a country in one fiscal year. (billions) > Life-Exp attribute represents the life expectancy in years (rounded to the nearest integer) for residents of a country. > Capital is a fixed character type of length one; a city is a capital of a country, or it is not. (Y', 'N') > Origin attribute specifies the country name where the river originates. * List of sample OGIS Spatial Data Model Operations (you can require to use): Buffer. returns a geometry that consists of all points whose distance from the given geometry is less than or equal to the specified distance. Cross: returns true if the interior of a surface intersects with a curve. Distance: returns the shortest distance between two geometries. Intersection: is a topological predicate to determine if two geometries intersect. Length: returns non-zero length of the river in each country it passes through. Overlap: returns true if the interiors of two geometries have nonempty intersection. Touch: returns true if the boundaries of two surfaces intersect but the interiors do not. a. List the length of the rivers in each of the countries they pass through. b. List all countries, ordered by number of neighboring countries. C. The Kzlrmak, also known as the Halys River is the longest river entirely within Turkey and can supply water to cities that are within 100 km. List the cities that can use water from the Kzlrmak. d. List the GDP and the distance of the country's capital city to Ankara for all countries
