Using SQL, how to solve??

We can now extract information from a single table. However in many cases we need to work with multiple data tables. In this section, we will merge corresponding data tables. In SQL this is known as table joining. To do that we first need to figure out a common data column between data tables. If you print the column names of the data tables you could see that column zip-code is common to both "zip' and 'tickets' tables. Note that the tickets' table also has a column called "zipcode (without the underscore) which is much longer than 5 digits. This is a more precise subdivision of zipcodes that we will not use today. names (dbReadTable (dbcon, "zip")) names (dbReadTable (dbcon, "tickets")) Print the last 8 rows of the zip-code column from each table and see why we call it the common data column. The data columns represent zipcodes of different regions in the US. If you print the entire column (ingnoring function head()) you could find common values in both columns. In SQL there are four types of merging tables. Here we will be using the simplest method which is INNER JOIN. The SQL INNER JOIN returns all rows from multiple tables where the join condition is met. This is the most commonly used method of joining tables. The next question is how to construct a SQL query to JOIN two tables. Let us assume there two tables 'A' and 'B' with a common column 'X'. The SQL query to join 'A' and 'B' using the common column 'X' would be as follows: SELECT * FROM A INNER JOIN B ON A.X=B.X In other words are interest about pulling all the data columns from 'A' (the meaning of the "*" is the same here as it was earlier) after merging with 'B', based on exact matches of column 'X' from both tables. Exercise: Build a SELECT query that performs an inner join on the tables tickets' and 'zip' on the zip code column. Exercise: Build a SELECT query that joins tables 'POP 2011 and 'POP 2006'. You'll need to find the common variable upon which to join. We've seen how to join tables. Next we want to extract specific information from joined table. Imagine we want to join the 2011 census data 'POP2011' with the additional geographical information 'CA' and then only look at records in the joined table wherein total private dwellings 2011 is equal to zero. We just need to introduce an additional term to the query to obtain this information from the table: sql_qry2 = "SELECT * FROM CA INNER JOIN POP2011 ON CA. Geographic_name = POP2011. Geographic_name WHERE Total_private_dwellings__2011 == 0" join_eg = dbGetQuery(dbcon, sql_qry2) Note that I've made the query string more readable through the use of whitespace. Question 3a, (2 points): Build an SQL query that joins both the 'zip' and the tickets' tables on the 'zip_code' field, and restricts the results to tickets where the fine_level_1 amount is more than $100. What is the sum of the latitudes of all of the records in this query? Note that this sum should not include records for which the latitude is not recorded. (Exercise: why aren't some latitudes recorded after the join?)