Zadanie 1a
------------

select DISTINCT nazwisko from Actor where nazwisko LIKE '%SKI'

Zadanie 1b
------------

select name from Movie where rank IS NULL

Zadanie 2a
------------

select year as Rok, count(*) as liczba from Movie 
group by year
having year between 1990 and 1999

Zadanie 2b
------------

select plec, count(*) as Liczba from Actor
where nazwisko like 'D%'
group by plec

Zadanie 3a
------------

select imie, nazwisko, LEFT(imie,1)+'.'+LEFT(nazwisko,1)+'.' as Inicjaly from Actor

Zadanie 3b
------------

select pid, mid, role from cast where CharIndex('James',role) = 2 

Zadanie 4a
------------

select 	Distinct x.id, x.imie, x.nazwisko
from 	Actor x, Movie y1, Movie y2, Cast z1, cast z2
		where     x.id = z1.pid and
              		  x.id = z2.pid and
			  z1.mid = y1.id And			  
			  y1.year between 1850 and 1899 and
			  z2.mid = y2.id And
			  y2.year between 1900 and 1949

-- use subquery
select	a.id, a.nazwisko, a.imie
from	Actor as a
where	exists (select	*
		from	Movie as m, Cast as c
		where	c.mid = m.id and c.pid = a.id and m.year between 1850 and 1899)
        and	
 	exists (select	*
		from	Movie as m, Cast as c
		where	c.mid = m.id and c.pid = a.id and m.year between 1900 and 1949) 
		
Zadanie 4b
------------

select	b.id, b.name
from	Movie a, Movie b
where	a.name = 'Shrek' and
	a.year = b.year and
	a.rank < ISNULL(b.rank,0)

Zadanie 5a
------------

select  a.id, a.imie, a.nazwisko
from	Actor a, Movie b, Cast c
where	a.id = c.pid	and
	c.mid = b.id	and
	b.name = 'Officer 444 (1926)'
	
--subquery
select	a.id, a.imie, a.nazwisko
from	Actor as a
where	a.id in (select distinct c.pid
		 from	Movie as m, Cast as c
		 where	m.id = c.mid and m.name = 'Officer 444 (1926)')
	
Zadanie 5b
------------

select	d.id, d.imie, d.nazwisko, count(md.mid) as num_movies
from	Directors as d left join Movie_Directors as md
on	d.id = md.did
group by 	d.id, d.imie, d.nazwisko
order by 	num_movies desc

Zadanie 6a
------------

select	a.id, a.imie, a.nazwisko
from	Actor a, Cast c, Movie m
where	a.id = c.pid	and
	c.mid = m.id
group by	a.id, a.imie, a.nazwisko
having		max (m.year) < 1960 

Zadanie 6b
------------

select	a.id, a.imie, a.nazwisko
from	Actor as a
where	10 <= (select	count(distinct md.did)
	       from	Movie as m, Cast as c, Movie_Directors as md
	       where	c.mid = m.id and md.mid = m.id and c.pid = a.id)
	       
Zadanie 7a
------------

select	m.id, m.name
from	Movie as m, Cast as c1, Actor as a
where	c1.mid = m.id and c1.pid = a.id and a.plec = 'F'
group by	m.id, m.name
having		count(*) > 	(select	count(*)
				from	Cast as c2, Actor as a
				where	c2.mid = m.id and c2.pid = a.id and a.plec = 'M')

Zadanie 7b
------------

select	a.imie, a.nazwisko, m.name 
from	Actor as a, Movie as m, Cast as c
where	a.id = c.pid 	and 
	m.id = c.mid	and
	m.year = 	(select		min(year)
			from		Movie as m1, Cast as c1
			where		m1.id = c1.mid and c1.pid = a.id)
order by 	a.nazwisko
	