06

Use the sample database named sakila and its tables in MySQL Workbench for this week’s lab. Create views for the following queries. Name of the view should be in the format “V1_YourStudentID” for the first query. (For ex. V1_2019A7PS0001). You can view the list of tables in the sakila database using ‘Show tables’ as given in the following screenshot.

use sakila;
show tables;

Create a view to find all actors whose last name contain the letters GEN.

drop view if exists ActorGEN;

create view ActorGEN as
select first_name as 'First Name', last_name as 'Last Name'
from actor
where last_name like "%GEN%";

select * from ActorGEN;

Create a view to display the country_id and country columns of the following countries: Afghanistan, Bangladesh, and China using in.

drop view if exists countriesView;

create view countriesView as
select country_id, country
from country
where country in ("Afghanistan", "Bangladesh", "China");

select * from countriesView;

Create a view to List each film and the number of actors who are listed for that film. Use tables film_actor and film. Use inner join.

drop view if exists filmActors;

create view filmActors as
select title as 'Film', count(actor_id) as 'Number of Actors'
from film
 inner join film_actor
 on film.film_id = film_actor.film_id
group by film.film_id;

select * from filmActors;

Create a view to List the last names of actors, as well as how many actors have that last name.

drop view if exists actorLastNames;

create view actorLastNames as
select last_name as 'Last Name', count(last_name) as 'No of People'
from actor
group by last_name;

select * from actorLastNames;

Create a view to List last names of actors and the number of actors who have that last name, but only for names that are shared by at least two actors

drop view if exists actorLastNamesIDK;

create view actorLastNamesIDK as
select last_name as 'Last Name', count(last_name) as 'No of People'
from actor
group by last_name
having count(last_name) >= 2;

select * from actorLastNamesIDK;

Create a view to display the first and last names, as well as the address, of each staff member. Use the tables staff and address. Use Join.

drop view if exists staffStuff;

create view staffStuff as
select first_name as 'First Name', last_name as 'Last Name', 
address as 'Address'
from staff
 inner join address
 on staff.address_id = address.address_id;

select * from staffStuff;

Create a view to Use subqueries to display all actors who appear in the film Alone Trip.

drop view if exists AloneTripActors;

create view AloneTripActors as
select concat(first_name, " ", last_name) as Actor
from actor
where actor.actor_id in (
 select film_actor.actor_id
  from film_actor
  where film_actor.film_id = (
    select film.film_id
    from film
    where title = "Alone Trip"
  )
);

select * from AloneTripActors;

Create a view to Display the most frequently rented movies in descending order.

drop view if exists FreqRentedMovies;

create view FreqRentedMovies as
select title as 'Film', count(rental_id) as 'Number of Rentals'
from film
 inner join inventory
 on inventory.film_id = film.film_id
 inner join rental
 on rental.inventory_id = inventory.inventory_id
group by title
order by count(rental_id) desc;

select * from FreqRentedMovies;

Create a view to Write a query to display for each store its store ID, city, and country.

drop view if exists StoreView;

create view StoreView as
select store_id as 'Store ID', city as City, country as Country
from store
 inner join address
     on address.address_id = store.address_id
 inner join city
     on city.city_id = address.city_id
 inner join country
     on country.country_id = city.country_id;

select * from StoreView;

Drop any of the views you created.
```
drop view StoreView;
```

Last Updated: 2023-01-25 ; Contributors: AhmedThahir

06

Comments