1. Solve the following Questions.

a) Using the below two SQL queries and comments below, provide explanation on why the output count varies and which SQL yield correct result.

(20 points)

• Both SQLs are joining two tables
• Both SQLs are syntactically correct
• Query #1 returns about 19614 rows versus Query #2 returns 3550134.

Query #1:

select * from person.Address addr inner join person.StateProvince prov on addr.StateProvinceID = prov.StateProvinceID

Query #2:

select * from person.Address addr,person.StateProvince prov

b). A business user is analyzing most travelled flight routes in Mid-west area. He is coming up with the count of passengers who flew from one city to another city and total amount (as part of revenue). There are about 120 routes that he is analyzing. He is analyzing flight data month wise for past 6 years.

Assuming that the above requirements are complete and will be used for creating a report, identify all the Dimensions and Facts. (Hint: There is 2 fact.) (20 points)