/* Assignment 3
   Philip Bock */

use Pubs;

-- Question 1
select title from titles where title_id in
	(select title_id from titleauthor where royaltyper > 40) order by title;

-- Question 2
select au_lname, au_fname from authors where city in
	(select city from publishers);

-- Question 3
select pub_name from publishers where not exists
	(select * from titles where pub_id = publishers.pub_id);

-- Question 4
select pub_name from publishers where pub_id in
	(select pub_id from titles where type = 'business');

-- Question 5
select pub_name from publishers where pub_id in
	(select pub_id from titles where advance is null or advance = 0);

-- Question 6
select lname, fname from employee where DATEPART(yyyy, hire_date) = '1991';

-- Question 7
select fname + ' ' + lname 'Name', SUBSTRING(emp_id, 9, 1) 'Gender' from employee
order by 'Gender', 'Name';

-- Question 8
select CONVERT(char (20), fname + ' ' + lname) 'Name', SUBSTRING(emp_id, 9, 1) 'Gender' from employee
order by 'Gender', 'Name';

-- Question 9
select * from sales where DATEPART(mm, ord_date) = 5 and DATEPART(yyyy, ord_date) = '1993';
