/* Lab 5
   Philip Bock */

use Pubs;

-- Question 1

select lname, fname, pub_name from employee
	join publishers on (employee.pub_id = publishers.pub_id);

-- Question 2

select lname, fname, pub_name from employee, publishers
where (employee.pub_id = publishers.pub_id);

-- Question 3

select stor_name, city, qty from sales
	join stores on (sales.stor_id = stores.stor_id)
where state in ('WA', 'CA');

-- Question 4

select stor_name, city, qty from sales, stores
where state in ('WA', 'CA') and (sales.stor_id = stores.stor_id);

-- Question 5

select lname, fname, job_desc, hire_date from employee
	join jobs on (employee.job_id = jobs.job_id)
where SUBSTRING(emp_id, 9, 1) = 'M' and job_lvl = max_lvl;

-- Question 6

select type, price, qty from titles
	join sales on (titles.title_id = sales.title_id)
where advance >= 1000 and advance <= 2000;

-- Question 7

select stor_name, state, ord_date, qty from sales
	join stores on (sales.stor_id = stores.stor_id)
where (qty < 15 or qty > 25) and SUBSTRING(zip, 1, 1) = '8';

-- Question 8

select title, titles.royalty, roysched.royalty, lorange, hirange
from titles, roysched
where (titles.title_id = roysched.title_id) and ytd_sales > 8000 and ytd_sales < 16000;

-- Question 9

select title, titles.royalty, roysched.royalty, lorange, hirange
from titles, roysched
where (titles.title_id = roysched.title_id) and
	ytd_sales > 8000 and ytd_sales < 16000 and ytd_sales >= lorange and ytd_sales <= hirange;

-- Question 10

select type, CONVERT(char(40), title), CONVERT(char(18), au_lname + ', ' + au_fname) from titles
	join titleauthor on (titles.title_id = titleauthor.title_id)
		join authors on (titleauthor.au_id = authors.au_id);

-- Question 11

select fname + ' ' + lname from employee
	join publishers on (employee.pub_id = publishers.pub_id)
		join titles on (publishers.pub_id = titles.pub_id)
where state = 'DC' and type = 'UNDECIDED';
