Using the MySQLsakila test/sample database (available at: http://mysql-tools.com

Question

Using the MySQLsakila test/sample database (available at: http://mysql-tools.com/en/downloads/mysql-databases/4-sakila-db.html) please provide SQL statements for the questions in the attached picture.

Question 8: Write a SQL statement that lists the following information. No row should appear more than once in the output. Order your results by customer last name, then by customer first name. Include your results. customer id customer last name customer first name customer email average rental amount most recent rental date most recent return date Question 9: Write a SQL statement that gives the customer counts by country, district, and postal code. No row should appear more than once. Order your results by country then district, then postal code. Include your results. List the following information in your output: district city postal code customer count Question 10: Write a SQL statement that gives the total revenue by postal code. No row should appear more than once. Order your results by country, then district, then postal code. Include your results. List the following information in your output: district city postal code revenue

Explanation / Answer

Question 8: For this query table rental, customer and payment are joined. Aggregate function AVG(), MAX() are used with group by clause. Order by is used to sort the result in given manner.

SELECT c.customer_id, c.first_name, c.last_name, c.email, AVG(p.amount),
MAX(rental_date), MAX(return_date)
FROM customer AS c
INNER JOIN rental AS r
ON c.customer_id = r.customer_id
INNER JOIN payment AS p
ON p.rental_id = r.rental_id
GROUP BY c.customer_id, c.first_name, c.last_name, c.email
ORDER BY c.last_name, c.first_name;

Question 9: 4 table customer, address, city and country are joined on common attribute. Aggregate function COUNT() is used to count the number of customer.

SELECT co.country_id, a.district, ci.city, a.postal_code, COUNT(c.customer_id)
FROM customer AS c
INNER JOIN address AS a
ON c.address_id = a.address_id
INNER JOIN city AS ci
ON ci.city_id = a.city_id
INNER JOIN country As co
ON co.country_id = ci.country_id
GROUP BY co.country_id, a.district, ci.city, a.postal_code
ORDER BY co.country_id, a.district, ci.city, a.postal_code;

Question 10: As not much information amount revenue is given, so am just summing the the amount field of payment table to get the revenue.

SELECT co.country_id, a.district, ci.city, a.postal_code, SUM(p.amount) AS Revenue
FROM customer AS c
INNER JOIN address AS a
ON c.address_id = a.address_id
INNER JOIN city AS ci
ON ci.city_id = a.city_id
INNER JOIN country As co
ON co.country_id = ci.country_id
INNER JOIN payment AS
ON p.customer_id = c.customer_id
GROUP BY co.country_id, a.district, ci.city, a.postal_code

Related Questions

Navigate

• Browse (All)
• Browse U
• Subjects

---

---

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