SQL - [Practice]

How many payment transactions were greater than $5.00?

select count (amount) from payment where amount > 5.00 ;

How many actors have a first name that starts with the letter P?

select count(*) from actor where first_name like 'P%';

​How many unique districts are our customers from?

select count (distinct(district)) from address;

Retrieve the list of names for those distinct districts from the previous question.

select distinct(district) from address;

 

How many films have a rating of R and a replacement cost between $5 and $15?

select count(*) from film where rating = 'R' and replacement_cost between 5 and 15;

 

How many films have the word Truman somewhere in the title?

select count(*) from film where title Like '%Truman%';

How many payments with amount 0.99, 1.98, 1.99?

select count(*) from payment where amount in (0.99, 1.98,1.99);

Sort the customers whose first name is 'M%' and whose last name is 'B%' by there last name.

 

select * from customer WHERE first_name Like 'M%' and last_name not like 'B%'
order by last_name;

Use the aggregate

/*aggregate*/
/*min*/
select min(replacement_cost) from film;
/*min & max*/
select min(replacement_cost),max(replacement_cost) from film;
/*avg & round*/
select round(avg(replacement_cost),3) from film;
/*sum*/
select sum(replacement_cost) from film;

Sort the total amount by customer in descending order by total amount. and show only 5 rows of customer ID and total amount columns.

select customer_id,sum(amount) from payment
group by customer_id order by sum(amount) desc limit 5;

Sort the total amount by customer and staff in descending order by total amount. And show staff ID, customer ID and total amount columns at the same time.

select staff_id,customer_id,sum(amount) from payment group by staff_id,customer_id
order by sum(amount) desc;

Show the columns where customer_id does not contain 184,87,477. And bring the customer who's paid 100 amount over at the same time.

*having*/
/*집계가 이미 수행된 '이후에' 자료를 필터링, group by 뒤에 온다., group by 절에서의 필터링*/
select customer_id, sum(amount) from payment
where customer_id not in (184,87,477)
group by customer_id having sum(amount) > 100;

output :