use pubs

--cross join
select titles.*, authors.*
	from titles, authors

select titles.*,authors.*
	from titles
			cross join authors

--geef de uitgevers met hun boeken

select *
	from publishers, titles
	where publishers.pub_id = titles.pub_id
	order by publishers.pub_id
--of

select *
	from publishers
		join titles on publishers.pub_id = titles.pub_id
	order by publishers.pub_id

--geef de boeken met de authors
select titles.title_id, titles.title, authors.au_id, authors.au_fname, authors.au_lname
	from titles
			join titleauthor on titles.title_id = titleauthor.title_id
			join authors on titleauthor.au_id = authors.au_id
	order by titles.title_id

--geef van de boeken met een prijs <20 de auteurs

select titles.title, titles.price, authors.au_fname, authors.au_lname
	from titles
		join titleauthor on titleauthor.title_id = titles.title_id
		join authors on titleauthor.au_id = authors.au_id
	where titles.price < 20
	order by titles.title_id

--geef alle uitgevers met hun boeken

select *
	from publishers
		left join titles on publishers.pub_id = titles.pub_id
	order by publishers.pub_id

--of

select *
	from titles
		right join publishers on publishers.pub_id = titles.pub_id
	order by publishers.pub_id

-- geef alle boeken vand e auteurs

select titles.title_id, titles.title, authors.au_id, authors.au_fname, authors.au_lname
	from titles
		left join titleauthor on titles.title_id = titleauthor.title_id
		left join authors on titleauthor.au_id = authors.au_id
	order by titles.title_id

--geef voor elke uitgeverij de werknemers ùmet hun jobbeschrijving

select publishers.pub_id, publishers.pub_name, employee.emp_id, employee.fname, employee.lname, jobs.job_id, jobs.job_desc
	from publishers
		left join employee on publishers.pub_id = employee.pub_id
		left join jobs on employee.job_id = jobs.job_id
	order by publishers.pub_id, employee.emp_id
-- geef alle boeken met de winkels waar het boek wordt aangeboden
select titles.title_id, titles.title, stores.stor_id, stores.stor_name
	from titles
		join sales on titles.title_id = sales.title_id
		join stores on sales.stor_id = stores.stor_id
	order by titles.title_id

-- geef de auteurs met een naamgenoot
select *
	from authors a1
		join authors a2 on a1.au_lname = a2.au_lname and a1.au_id != a2.au_id


use Family

--geef de kinderen van audrey halloway
select *
	from Person p
		join Person pm on p.MotherID = pm.PersonID
	where pm.LastName = 'Halloway' and pm.FirstName = 'Audry'
	order by p.PersonID


-- geef de moeder en de vader van elke persoon

select p.PersonID, p.FirstName,  p.LastName,pm.PersonID, pm.FirstName,  pm.LastName,pf.PersonID, pf.FirstName,  pf.LastName
	from Person p
		left join Person pm on p.MotherID = pm.PersonID
		left join Person pf on p.FatherID = pm.PersonID
	order by p.PersonID

use pubs
--geef de boeken met dezelfde prijs als de prijs van het boek 'straight talk about computers'

select *
	from titles
	where price = (select price
						from titles
						where titles.title = 'Straight talk about computers')

--geef de uitgevers die boeken van het type business uitgeven
	select publishers.*, titles.title_id, titles.title, titles.pub_id
		from publishers
			join titles on  publishers.pub_id = titles.pub_id
		where titles.type = 'business'
		order by publishers.pub_id

--of
select publishers.*
	from publishers
	where pub_id IN ( select pub_id
							from titles
							where type ='business')
--geef de uitgervers die geen boeken van het type business uitgeven

select publishers.*
	from publishers
	where pub_id not IN ( select pub_id
							from titles
							where type ='business')