SQL Queries 2

create table actor_info(actor_id int , actor_name varchar(15), movie_name varchar(20), no_of_cds int )
sp_help actor_info
insert into actor_info values(101, 'abc', 'tuw', 23)
select * from actor_info

insert into actor_info values(102, 'xyz', 'qrs', 11)


insert into actor_info values(101, 'abc', 'def', 34)
insert into actor_info values(103, 'saf', 'rst', 46)
insert into actor_info values(101, 'abc', 'zwy', 47)
insert into actor_info values(102, 'xyz', 'qmz', 67)
insert into actor_info values(103, 'saf', 'rvw', 28)

select actor_id ,count(movie_name) "Total no. of Movies", sum (no_of_cds) "Total no. of CDS" ,min(no_of_cds) "Min No. of CDS", max(no_of_cds)"Max No. of CDS" , avg(no_of_cds)"Average no. of CDs" from actor_info group by actor_id

select actor_id ,count(movie_name) "Total no. of Movies", sum (no_of_cds) "Total no. of CDS" ,min(no_of_cds) "Min No. of CDS", max(no_of_cds)"Max No. of CDS" , avg(no_of_cds)"Average no. of CDs" from actor_info where actor_id >101group by actor_id
select actor_id ,count(movie_name) "Total no. of Movies", sum (no_of_cds) "Total no. of CDS" ,min(no_of_cds) "Min No. of CDS", max(no_of_cds)"Max No. of CDS" , avg(no_of_cds)"Average no. of CDs" from actor_info where no_of_cds >11group by actor_id


select actor_id ,count(movie_name) "Total no. of Movies", sum (no_of_cds) "Total no. of CDS" , max(no_of_cds)"Max No. of CDS"  from actor_info group by actor_id having count(movie_name) > 2

select actor_id ,count(movie_name) "Total no. of Movies", sum (no_of_cds) "Total no. of CDS" ,min(no_of_cds) "Min No. of CDS", max(no_of_cds)"Max No. of CDS" , avg(no_of_cds)"Average no. of CDs" from actor_info where actor_name like 'a%' group by actor_id