sql - SQL查询练习Oracle 18c
问题描述
我正在尝试解决此练习:
想象一下那些获得了同胞媒体两倍以上报酬的演员。
- FILM (CODFILM,TITLE,YEAR,GENRE) --Year 是 NUMBER,它只包含 Year
- 演员(导演、姓名、姓氏、国籍)
- 演员(CODFILM,CODACTOR,补偿)。
我已经尝试过这个解决方案,但它不起作用
SELECT CODACTOR
FROM ACTOR NATURAL JOIN CAST T
WHERE COMPENSATION >(
SELECT AVG(COMPENSATION) * 2
FROM ACTOR NATURAL JOIN CAST
WHERE NAZIONALITY = T.NAZIONALITY)
通过根据后一个值按降序查看结果,为每个演员选择他/她播放第一部电影和最后一部电影的年份。
SELECT CODACTOR, MIN(YEAR), MAX(YEAR)
FROM FILM NATURAL JOIN CAST T
GROUP BY CODFILM, CODACTOR
HAVING CODACTOR IN (
SELECT CODACTOR
FROM CAST
WHERE CODFILM = T.CODFILM)
最后一个练习我不知道如何解决,因为我没有“出生日期”列。
想象一下 1990 年以前出生的法国演员从未扮演过的角色。(假设存在列 BIRTH,请键入 DATE。)
解决方案
请尝试下面的第一个查询
SELECT A.codactor
FROM actor A
inner join cast C
ON A.codactor = C.codactor
WHERE compensation > (SELECT trunc(Avg(compensation)) * 2
FROM actor A1
inner join cast C1
ON A1.codactor = C1.codactor
WHERE A1.nazionality = A.nazionality
)
对于您的第二个查询,请尝试以下
WITH films
AS (SELECT c.codfilm,
c.codactor,
F.title,
F.year,
F.genre
FROM film F
inner join cast c
ON c.codfilm = F.codfilm)
SELECT A.codactor,
Min(F.year) FIRST_YEAR,
Max(F.year) LAST_YEAR
FROM actor A
inner join films F
ON A.codactor = F.codactor
GROUP BY A.codactor