Scribd
Upload
13 views2 pages

Random File

The document contains SQL commands for managing a database related to customer rentals in the Sakila schema. It includes the creation of a function to count customer rentals, modifications to the customer table, and the implementation of a trigger to update customer ranks based on rental counts. Additionally, it demonstrates indexing on the country table and exporting data about the top 20 actors to a text file.

Uploaded by

Hoa Nguyễn
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as TXT, PDF, TXT or read online on Scribd
13 views2 pages

Random File

The document contains SQL commands for managing a database related to customer rentals in the Sakila schema. It includes the creation of a function to count customer rentals, modifications to the customer table, and the implementation of a trigger to update customer ranks based on rental counts. Additionally, it demonstrates indexing on the country table and exporting data about the top 20 actors to a text file.

Uploaded by

Hoa Nguyễn
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as TXT, PDF, TXT or read online on Scribd
You are on page 1/ 2

use sakila;

-- cau 1
drop function if exists get_customer_rental;
delimiter //

create function get_customer_rental(


_customer_id int
)
returns int
deterministic
reads sql data
begin
declare result int;
select count(customer_id) into result
from rental where customer_id = _customer_id;
return result;
end //

delimiter ;
select get_customer_rental(2);

-- cau 2
select * from customer;
alter table customer
drop column customer_rank;
alter table customer
add customer_rank varchar(20) default null null;
-- -----
drop trigger if exists after_rental_insert;
delimiter //
create trigger after_rental_insert
after insert
on rental
for each row
begin
declare customer_rental int;
declare status varchar(20) default null;
set customer_rental = get_customer_rental(new.customer_id);
if customer_rental >= 30 then
set status = 'Platium';
elseif customer_rental >= 25 then
set status = 'Gold';
elseif customer_rental >= 20 then
set status = 'Silver';
end if;

update customer
set customer_rank = status
where customer_id = new.customer_id;
end //

delimiter ;

-- -----------------
-- cau 3
drop index idx_country_name on country;
create index idx_country_name
on country(country);
show index from country;

select c3.country, count(*) as total_customers


from customer c
join address a on c.address_id = a.address_id
join city c2 on a.city_id = c2.city_id
join country c3 on c2.country_id = c3.country_id
where c3.country like 'A%' or c3.country like 'B%'
group by c3.country;

-- ------------------------
-- cau 4
show variables like "secure_file_priv";
select fa.actor_id, a.first_name, a.last_name, count(*) as total_films
from film_actor fa
join actor a on fa.actor_id = a.actor_id
group by fa.actor_id
order by total_films desc
limit 20
into outfile 'C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/top_20_actor.txt'
fields terminated by ',' enclosed by '"'
lines terminated by '\n';

You might also like