1. Write a query to list the title, type, and total revenue for all business books that had a revenue greater than $200. (7.5 points)

2. Show a list of employees who were hired in April, July, or November. (7.5 points)

3. Show a list of publisher ids and names, the number of books they published and the revenue they generated by selling their books. (7.5 points)

4. Show a list of employees whose first and last names start with the same letter. (7.5 points) The following query is based on the Northwind database.

5. Print a list of managers in the Northwind database together with the number of employees that report to them. (10 points) The following queries are based on the WORLD database.

6. Create a ranked list of languages spoken in each country. The ranking is in the descending order of the percentage of population that speaks the language. (10 points) The output should look like the following (abridged):

7. In which countries the languages spoken by the majority (i.e., more than 50%) of the population are not official? (10 points) MIS 664A Assignment 3 Read the notes at the end of the file.

8. In which countries 10 or more languages are spoken? (12.5 points)

9. Which countries have three or more official languages? (12.5 points)

10. In which countries a language that is spoken the least is an official language? (The language must have more than 0 percent speakers). (15 points) Top rows of the output are shown below: Bonus -The following query is based on the Hospital database.

11. Hospital is a database with one table, which is titled as Encounter. The table stores a series of hospital encounters by patients. Length of stay (LOS) is the difference between the discharge and admission dates of a patient (i.e., how long the patient stayed in the hospital). Note that LOS is not an attribute in the table but can be computed as said above. Write a single query that, for each record, shows a running average of LOS for all previous encounters in the database. The average must be in days (as the unit). (10 points) The output must look like this: For example, since no encounters existed before the first encounter, avgLOS will be null. For the second encounter, the avgLOS is equal to the LOS of the first encounter. For the third MIS 664A Assignment 3 Read the notes at the end of the file. 3 encounter, avgLOS is equal to the average of LOS for the first and second encounters and so on. Note that in the output shown above, records are ordered by their admission date; that is, EncounterID does not imply order of encounters.