Hello! MySQL - select - join - quieries and subquieries. Use the classicmodels d
ID: 3596936 • Letter: H
Question
Hello! MySQL - select - join - quieries and subquieries.
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.
****** this query shows one employee's first and last name along with one city name, I need each employee with the city:
1) select count(Offices.officeCode),Offices.city from Offices,Employees where Offices.officeCode = Employees.officeCode; *************
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
1)
# Fetches the city and number of employees in each city.
SELECT o.city, COUNT(e.employeeNumber)
FROM Employees AS e INNER JOIN Offices AS o
ON e. officeCode = o.officeCode
GROUP BY o.city;
2)
# List employees name with number of customers belongs to each employee.
SELECT e.firstName, e.lastName, COUNT(c.customerNumber)
FROM Employees AS e INNER JOIN Customers AS c
ON e.employeeNumber = c.salesRepEmployeeNumber
GROUP BY e.firstName, e.lastName;
3)
# List the employee name and their corresponding manager whom employee reports. Since the employee number to manager number belongs to same table employee, self join on employee table is done.
SELECT e.firstName AS eFirstName, e.lastName AS elastName, r.firstName AS rFirstName, r.lastName AS elastName
FROM Employees AS e INNER JOIN Employees AS r
ON e.employeeNumber = r.reportsTo;
4)
# Cistomers contact person and corresponding payment amount is calculated. Onlu first 25 contact person name is displayed using limit clause.
SELECT c.contactFirstName, c.contactLastName, SUM(p.amount)
FROM Customers AS c INNER JOIN Payments AS p
ON c.customerNumber = p.customerNumber
GROUP BY c.contactFirstName, c.contactLastName
LIMIT 25;
5)
# Number of customers whose city is same as sales representative employees office city.
SELECT COUNT(c.customerNumber)
FROM Customers AS c INNER JOIN Employees AS e
ON c.salesRepEmployeeNumber = e.employeeNumber
INNER JOIN Offices AS o
ON o.officeCode = e.officeCode
WHERE c.city = o.city;
6)
# Listing city of customers and number of customer, whose city is same as sales representative employees office city.
SELECT c.city, COUNT(c.customerNumber)
FROM Customers AS c INNER JOIN Employees AS e
ON c.salesRepEmployeeNumber = e.employeeNumber
INNER JOIN Offices AS o
ON o.officeCode = e.officeCode
WHERE c.city = o.city
GROUP BY c.city ;
7)
# Listing the customers who bought the most expensive product.
SELECT c.customerName, od.priceEach
FROM Customers AS c INNER JOIN Orders AS o
ON c.customerNumber = o.customerNumber
INNER JOIN OrderDetails AS od
ON od.orderNumber = o.orderNumber
INNER JOIN Products AS p
ON p.productCode = od.productCode
ORDER BY od.priceEach DESC;
8)
# Customer who has made the largest payment
SELECT c.customerName, SUM(amount)
FROM Customers AS c INNER JOIN Payments AS p
ON c.customerNumber = p.customerNumber
GROUP BY c.customerName
ORDER BY SUM(amount) DESC
LIMIT 1;
Related Questions
drjack9650@gmail.com
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.