Rewrite the following query so that the result set contains a single row with five columns...

  

Transcribed Image Text:

Rewrite the following query so that the result set contains a single row with five columns (one for each rating). Name the five columns (G, PG, FG_13, R, and MC 17). mysql> SELECT rating COUNT(*) -> FROM film ->GROUP BY rating: It should retrieve the following: | PG PG-13 | R T 178 1 row in set (0.28 sec) 194 | 223 195 1 NC-17 210 Write a query that returns the alphabetized first letter of the customer's last name and the count of active and inactive customers. Do not limit the results to only those first letters that occur in the last name colum of the customer table but return results that include any missing letters from the data set. (HINT: You will need to fabricate a table composed of the 26 letters of the alphabet and use an outer join to resolve this problem.) Label the columns as follows: starts with is the first column and the first letter of the customer's last_name. .active_count is the second column and the count of active customers (as defined in the textbook examples of Chapter 11). inactive_count is the third column and the count of inactive customers (as defined in the textbook examples of Chapter 11). . The output should look like the following: I starts with active_count | inactive_count I 18 1 55 1 49 1 17 1 11 1 251 42 1 49 1 A 13 IC ID LE 18 10 IM 1I 13 IX 12 IM IN 10 I P 10 IR 15 10 IV IW IX IY 12 4 26 rows in set (0.00 sec) 31 13 1 13 1 21 1 57 1 201 10 1 28 1 38 1 54 1 201 01 37 1 01 31 01 01 01 21 01 01 01 01 21 11 01 01 01 21 01 0 0 01 1 0 01 Rewrite the following query so that the result set contains a single row with five columns (one for each rating). Name the five columns (G, PG, FG_13, R, and MC 17). mysql> SELECT rating COUNT(*) -> FROM film ->GROUP BY rating: It should retrieve the following: | PG PG-13 | R T 178 1 row in set (0.28 sec) 194 | 223 195 1 NC-17 210 Write a query that returns the alphabetized first letter of the customer's last name and the count of active and inactive customers. Do not limit the results to only those first letters that occur in the last name colum of the customer table but return results that include any missing letters from the data set. (HINT: You will need to fabricate a table composed of the 26 letters of the alphabet and use an outer join to resolve this problem.) Label the columns as follows: starts with is the first column and the first letter of the customer's last_name. .active_count is the second column and the count of active customers (as defined in the textbook examples of Chapter 11). inactive_count is the third column and the count of inactive customers (as defined in the textbook examples of Chapter 11). . The output should look like the following: I starts with active_count | inactive_count I 18 1 55 1 49 1 17 1 11 1 251 42 1 49 1 A 13 IC ID LE 18 10 IM 1I 13 IX 12 IM IN 10 I P 10 IR 15 10 IV IW IX IY 12 4 26 rows in set (0.00 sec) 31 13 1 13 1 21 1 57 1 201 10 1 28 1 38 1 54 1 201 01 37 1 01 31 01 01 01 21 01 01 01 01 21 11 01 01 01 21 01 0 0 01 1 0 01