/* Lab 8
   Philip Bock */

use Pubs;

-- 1
select stor_name, sum(sales.qty) from stores
	join sales on stores.stor_id = sales.stor_id
group by stor_name;

-- 2
select stor_name, sum(qty) from stores
	join sales on stores.stor_id = sales.stor_id
where datepart(yy, ord_date) in ('1992', '1993')
group by stor_name;

-- 3
select stor_name, sum(qty) from stores
	join sales on stores.stor_id = sales.stor_id
where datepart(yy, ord_date) in ('1992', '1993')
group by stor_name
having avg(qty) >= 26;

-- 4
select au_lname, sum(((ytd_sales * price * royalty) / 100) - advance) from titles
	join titleauthor on titles.title_id = titleauthor.title_id
		join authors on titleauthor.au_id = authors.au_id
group by au_lname;

-- 5
select au_lname, sum(((ytd_sales * price * royalty) / 100) - advance) from titles
	join titleauthor on titles.title_id = titleauthor.title_id
		join authors on titleauthor.au_id = authors.au_id
group by au_lname
having sum(((ytd_sales * price * royalty) / 100) - advance) < 0;

-- 6
select type, avg(price), max(price), min(price), sum(ytd_sales) from titles
where datepart(yy, pubdate) = '1991'
group by type
order by avg(price) desc;

-- 7
select stor_id, ord_date, sum(qty) from sales
group by stor_id, ord_date;

-- 8
select titles.title_id, count(*) from titles
	join sales on titles.title_id = sales.title_id
group by titles.title_id
order by count(*) desc;

-- 9
select titles.title_id, sum(qty) from titles
	join sales on titles.title_id = sales.title_id
group by titles.title_id
order by sum(qty) desc;

-- 10
select city, state, count(*) from authors
group by city, state
having count(*) > 1
order by count(*) desc;

-- 11
select type, avg(price) from titles
where title not like 'Computer%'
group by type
having avg(price) >= 10 and avg(price) <= 15;
