Hello! MySQL - select - join. Use the classicmodels database (shown below) to ma

Question

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)

Explanation / Answer

a) SELECT COUNT(offices. officescode), offices.city

FROM offices,Employees

WHERE offices. Officescode= Employees.officescode;

b)SELECT Employees.firstname, Employees.lastname, COUNT(Employees. Employeenumber)

FROM Employees,customers

WHERE Employees. Employeenumber= customers.salesrepemployeenumber;

c)SELECT Employees.firstname, Employees.lastname, customer.firstname, customer.lastname

FROM Employees,customers

WHERE Employees. reportsto= customers. customernumber;

d)SELECT customer.firstname, customer.lastname, payments.amount

FROM payments,customers

WHERE payments.customernumber = customers. customernumber

Limit 25;

Related Questions

Navigate

• Browse (All)
• Browse H
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.