sql 汇总数据题目

1.
SELECT concat(c.last_name,’,’, c.first_name) ‘name’,email,payment_id FROM customer c LEFT JOIN payment p ON c.customer_id=p.customer_id;

2.
SELECT f.title,f.description,r.rental_id FROM inventory i INNER JOIN rental r ON i.inventory_id=r.inventory_id
INNER JOIN film f ON f.film_id =i.film_id
WHERE rental_id IN (100,107);
解法2：
SELECT f.title,f.description,r.rental_id FROM inventory i INNER JOIN rental r USING (inventory_id)
INNER JOIN film f USING (film_id)
WHERE r.rental_id IN (100,107);
3.

---

4.
SELECT c.customer_id,concat(c.first_name,’ ',c.last_name) ‘name’,c.address_id,c.email,sum(p.amount) ‘total_expense’
FROM customer c LEFT JOIN payment p ON c.customer_id=p.customer_id
GROUP BY c.customer_id;

SELECT r.rental_id,sum(p.amount) ‘Total Amount’,
p.customer_id,
DATEDIFF(r.return_date,r.rental_date) ‘Datediff’
FROM rental r
INNER JOIN payment p
ON r.rental_id=p.rental_id
GROUP BY r.rental_id
,customer_id
ORDER BY r.rental_id ASC;
6.

SELECT f.title,f.rental_duration ,i.inventory_id
FROM film f LEFT JOIN inventory i
ON f.film_id= i.film_id
WHERE f.rental_duration>3
AND f.title LIKE ‘B%’;

SELECT concat(first_name,last_name) ‘name’,count(p.rental_id) ‘COUNTS’,sum(p.amount) ‘TOTAL’ FROM customer c
LEFT JOIN payment p
ON c.customer_id=p.customer_id
WHERE p.rental_id BETWEEN 130 AND 160
GROUP BY c.customer_id ;

8.客户的花费大于所有客户的平均总花费

SELECT concat(c.first_name,’ ',c.last_name) ‘Name’
,COUNT(p.payment_id)counts
,SUM(p.amount) ‘Total amount’
FROM customer c
LEFT JOIN payment p ON c.customer_id=p.customer_id
WHERE p.rental_id in (SELECT rental_id FROM rental WHERE rental_id BETWEEN 130 AND 160)
AND p.amount >any (SELECT AVG(amount) FROM payment)
GROUP BY c.customer_id
ORDER BY SUM(p.amount),counts DESC;

SELECT c., p. FROM customer c CROSS JOIN payment p;