CITY Name, State, Population) CROSSING(City, River) RIVERName, Length) Show name
ID: 3728587 • Letter: C
Question
CITY Name, State, Population) CROSSING(City, River) RIVERName, Length) Show name, state and population for cities with more than 20000 people and crossed by the river Rio Grande or the river Red River 1. Find cities crossed by at least two rivers, showing the name of the city and the name of the longest river crossing such city 2. Find min and max cardinality both in numbers and symbols for the following queries EXERCISE RI B, C), with foreign integrity constraint between C and R2, and having cardinality N1 = 300 R2D, E, F),, with foreign integrity constraint between E, F and R3, and having cardinality N2 = 100 R3G.H. 1), having cardinality N3= 200 R3 4. PROJ A,B (R1) Solution Example 7. PROJ B.c (RI) 8. R1 JOIN CR2 9. R3JOIN 1#ARI Ex. Min MaxMin (symb) (symb) (numb) (numb) Max N3 200Explanation / Answer
Please find the queries below:
Find the answer for first part of the question.
1) select distinct c.Name, c.State, c.Population from CITY c join CROSSING cr on c.Name = cr.City where c.population > 20000 and (cr.River = "Rio Grande" or cr.River = "Red River");
2) select c.City, max(r.Length) from CROSSING c JOIN RIVER r ON c.River = r.Name group by c.River having count(c.River)>=2;