create table bruces_albums as
select album_id,
       album_name,
       producer_id,
       year as released
from albums
where (producer_id = 'BF1'
       or producer_id = 'BF2'
       or producer_id = 'BF3');


select album_name,
       producer_id,
       album_id
from albums
where album_name = 'Just Push Play';


create index location_index
on studio (city, state)
tablespace indx;


select studio_id,
       studio_name,
       city,
       state
from studio
where state = 'NY'
order by city;


alter table producer
add constraint fk_engineer_id
foreign key (engineer_id)
     references engineer (engineer_id);


alter table albums
add constraint min_id
check (album_id > 00);


select rtrim(city) || ' ' || state as location
from studio;


select max(year),
       max(album_id)
 from albums;


select count(*),
       count(album_id),
       count(album_name),
       count(year)
from albums;


select studio_id from studio
where not (studio_id = 'var')
group by studio_id
order by studio_id;


select studio_id, studio_name, city, state from studio
where not (studio_name = 'var')
order by state;


select studio_id, studio_name from studio
where not (studio_name = 'var')
group by studio_id, studio_name
order by studio_id;


select producer_id,
       count(album_id) as number_produced
from album_producer
group by producer_id
order by producer_id;


select p.producer_id,
       p.producer_name,
       e.engineer_id,
       e.engineer_name
from producer p,
     engineer e
where p.engineer_id = e.engineer_id
order by e.engineer_id;