SQL Subqueries

You must use subqueries on this set of problems.

1. List each city, by name, that has received shipments from customers having less than $1.5 million in annual revenue. Order these alphabetically.

2. List the names of drivers who have delivered shipments weighing over 1100 pounds.

3. List the name and annual revenue of customers who have sent shipments weighing under 100 pounds.

4. List the name and annual revenue of customers whose shipments have been delivered by truck driver Ty Mosley.

5. Which drivers (ID and name) have not yet delivered anything? 6. Which customers (ID and name) have annual revenue greater than the average revenue of all customers (including themselves)?

SQL Group By Queries

1. List the total annual revenue for customers by their customer type (CustType).

2. List the average monthly revenue for customers by their customer type (CustType).

3. List the average shipment weight by truckid. Exclude average shipment weights less than 600 pounds.

4. List the average shipment weight by customerid.

AutoSave ON AU 0 - Demo 0418 -Saving... Home Insert Draw Design Layout References Mailings Review View Acrobat Share Comments Businessline PK SIC int Industryme varchar(su CUBUS PKFR Cust int PKFKI SIC Int Markets are decima.) ( 6 Spon Gustomer Truck PK Shipmenti in Punto int PK Truck int HO-OFK Cro int ---04 Ship'weight in CustName wachan Drwerkme varhar30 FK Turki int Annus Renue in FKI CD int CustType wachac30 cate City PK CID int CityName warchar(301 Dity Pop int Pagn 3 of 4 575 words E English (United States) Focus 3 AutoSave ON AU 0 - Demo 0418 -Saving... Home Insert Draw Design Layout References Mailings Review View Acrobat Share Comments Businessline PK SIC int Industryme varchar(su CUBUS PKFR Cust int PKFKI SIC Int Markets are decima.) ( 6 Spon Gustomer Truck PK Shipmenti in Punto int PK Truck int HO-OFK Cro int ---04 Ship'weight in CustName wachan Drwerkme varhar30 FK Turki int Annus Renue in FKI CD int CustType wachac30 cate City PK CID int CityName warchar(301 Dity Pop int Pagn 3 of 4 575 words E English (United States) Focus 3