CREATE TABLE tabellnavn (
	prim?r int primary key,
	kandidat varchar unique not null,
	m?nedsnr int check (m?nedsnr > 0 and m?nedsnr <= 12)
);

CREATE TABLE tabell2 (
	a int references tabellnavn(prim?r),
	b int,
	c char(3),
	primary key(a),
	unique(b),
	foreign key (a, b) references annentabell(x, y)
);

INSERT INTO tabellnavn (prim?r, kandidat, m?nedsnr)
	VALUES (1, 'hei', 12);
INSERT INTO tabellnavn VALUES (2, 'asd', 12);

(SELECT filmid FROM film WHERE ..)
UNION
(SELECT filmid FROM film WHERE ..)

with r as
(SELECT ...)

CREATE VIEW r as
select ...


SELECT avg(rank)
FROM film f natural join filmparticipation fp
	natural join filmrating
WHERE fp.personid = 582198 and fp.parttype = 'director';

SELECT f.title, f.prodyear, r.rank
FROM film f natural join filmparticipation fp
	natural join filmrating r
WHERE fp.personid = 582198 and fp.parttype = 'director'
  AND r.rank > (
	SELECT avg(rank)
	FROM film f natural join filmparticipation fp
	natural join filmrating
	WHERE fp.personid = 582198 and fp.parttype = 'director');
-------------------
SELECT title, prodyear
FROM film natural join filmgenre
WHERE genre = 'Film-Noir'
  AND filmid IN (SELECT filmid FROM filmgenre WHERE genre = 'Comedy');

with comedyfilms as (
	SELECT filmid FROM filmgenre WHERE genre = 'Comedy'
)
SELECT title, prodyear
FROM film natural join filmgenre natural join comedyfilms
WHERE genre = 'Film-Noir';

SELECT title, prodyear
FROM film f natural join filmgenre g natural join (
	SELECT filmid FROM filmgenre WHERE genre = 'Comedy'
)
WHERE genre = 'Film-Noir';

SELECT filmid, title
FROM film natural join filmgenre
WHERE genre = 'Film-Noir'
INTERSECT
SELECT filmid, title
FROM film natural join filmgenre
WHERE genre = 'Comedy';
---------------
SELECT title, prodyear
FROM film natural join filmgenre
WHERE genre = 'Film-Noir'
  AND filmid IN (SELECT filmid FROM filmgenre WHERE genre = 'Comedy');

-- EXISTS, bruker korrelerte subsp?rringer (correlated subquery)
SELECT title, prodyear
FROM film f natural join filmgenre
WHERE genre = 'Film-Noir'
  AND EXISTS (
	SELECT filmid FROM filmgenre WHERE genre = 'Comedy'
	AND filmid = f.filmid);

SELECT title, prodyear, array_agg(genre)
FROM film natural join filmgenre
WHERE genre IN ('Comedy', 'Film-Noir')
GROUP BY filmid, title, prodyear
HAVING count(*) > 1;