Hello! MySQL - select - join.

Use the classicmodels database (shown below) to make queries that displays the data being asked.

Questions:

1.How many employees work in each city? List the city name.

2.List each employee first name and last name and the number of customers for each one.

3.List each employee first name and last name and the first and last name of the person that employee reports to.

4.For the first 25 customers, list the contact person (first name and last name) and the total amount of payments.

5.How many customers live in the same city as their sales rep works?

6.How many customers live in the same city as their sales rep works, list the name of the city and the number of customers.

7.Which customer (just the customer name) has ordered the most expensive product (based on the buyPrice)?

8.Which customer has made the largest payment? list just the customer name.

9.List all of the product descriptions for products from Min Lin Diecast and Exoto Designs.

10. Same as Question #9 but in a different way.

Extra Credit In order to receive extra credit points, you must have the basic assignment done completely and correctly.

1.For the first 10 orders, list the order number, the customer name and all of the product names on that order in ascending order of customer name. 2.What is the average dollar amount for each order?

mysql> show tables; +-------------------------+ | Tables_in_classicmodels | +-------------------------+ | Customers | | Employees | | Offices | | OrderDetails | | Orders | | Payments | | Products | +-------------------------+ 7 rows in set (0.00 sec)

mysql> describe Customers; +------------------------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +------------------------+-------------+------+-----+---------+-------+ | customerNumber | int(11) | NO | PRI | NULL | | | customerName | varchar(50) | NO | | NULL | | | contactLastName | varchar(50) | NO | | NULL | | | contactFirstName | varchar(50) | NO | | NULL | | | phone | varchar(50) | NO | | NULL | | | addressLine1 | varchar(50) | NO | | NULL | | | addressLine2 | varchar(50) | YES | | NULL | | | city | varchar(50) | NO | | NULL | | | state | varchar(50) | YES | | NULL | | | postalCode | varchar(15) | YES | | NULL | | | country | varchar(50) | NO | | NULL | | | salesRepEmployeeNumber | int(11) | YES | | NULL | | | creditLimit | double | YES | | NULL | | +------------------------+-------------+------+-----+---------+-------+ 13 rows in set (0.00 sec)

mysql> describe Employees; +----------------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +----------------+--------------+------+-----+---------+-------+ | employeeNumber | int(11) | NO | PRI | NULL | | | lastName | varchar(50) | NO | | NULL | | | firstName | varchar(50) | NO | | NULL | | | extension | varchar(10) | NO | | NULL | | | email | varchar(100) | NO | | NULL | | | officeCode | varchar(20) | NO | | NULL | | | reportsTo | int(11) | YES | | NULL | | | jobTitle | varchar(50) | NO | | NULL | | +----------------+--------------+------+-----+---------+-------+ 8 rows in set (0.00 sec)

mysql> describe Offices; +--------------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +--------------+-------------+------+-----+---------+-------+ | officeCode | varchar(50) | NO | PRI | NULL | | | city | varchar(50) | NO | | NULL | | | phone | varchar(50) | NO | | NULL | | | addressLine1 | varchar(50) | NO | | NULL | | | addressLine2 | varchar(50) | YES | | NULL | | | state | varchar(50) | YES | | NULL | | | country | varchar(50) | NO | | NULL | | | postalCode | varchar(10) | NO | | NULL | | | territory | varchar(10) | NO | | NULL | | +--------------+-------------+------+-----+---------+-------+ 9 rows in set (0.00 sec)

mysql> describe OrderDetails; +-----------------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-----------------+-------------+------+-----+---------+-------+ | orderNumber | int(11) | NO | PRI | NULL | | | productCode | varchar(50) | NO | PRI | NULL | | | quantityOrdered | int(11) | NO | | NULL | | | priceEach | double | NO | | NULL | | | orderLineNumber | smallint(6) | NO | | NULL | | +-----------------+-------------+------+-----+---------+-------+ 5 rows in set (0.00 sec)

mysql> describe Orders; +----------------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +----------------+-------------+------+-----+---------+-------+ | orderNumber | int(11) | NO | PRI | NULL | | | orderDate | datetime | NO | | NULL | | | requiredDate | datetime | NO | | NULL | | | shippedDate | datetime | YES | | NULL | | | status | varchar(15) | NO | | NULL | | | comments | text | YES | | NULL | | | customerNumber | int(11) | NO | | NULL | | +----------------+-------------+------+-----+---------+-------+ 7 rows in set (0.01 sec)

mysql> describe Payments; +----------------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +----------------+-------------+------+-----+---------+-------+ | customerNumber | int(11) | NO | PRI | NULL | | | checkNumber | varchar(50) | NO | PRI | NULL | | | paymentDate | datetime | NO | | NULL | | | amount | double | NO | | NULL | | +----------------+-------------+------+-----+---------+-------+ 4 rows in set (0.00 sec)

mysql> describe Products; +--------------------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +--------------------+-------------+------+-----+---------+-------+ | productCode | varchar(50) | NO | PRI | NULL | | | productName | varchar(70) | NO | | NULL | | | productLine | varchar(50) | NO | | NULL | | | productScale | varchar(10) | NO | | NULL | | | productVendor | varchar(50) | NO | | NULL | | | productDescription | text | NO | | NULL | | | quantityInStock | smallint(6) | NO | | NULL | | | buyPrice | double | NO | | NULL | | | MSRP | double | NO | | NULL | | +--------------------+-------------+------+-----+---------+-------+ 9 rows in set (0.01 sec)