SQL中是否可以同时调用具有不同数据的两个不同表

Question

因此，我基本上需要的是同时获得两个表，然后使用位于第一个表中的条件，并将其应用于第二个表如果不可能，我需要调用SELECT两次吗？这个代码对吗？确定2012年上映的所有电影的平均评分

SELECT AVG(rating),year FROM ratings , movies
WHERE year = 2012;
//these are the tables that i have 
CREATE TABLE movies (
id INTEGER,
title TEXT NOT NULL,
year NUMERIC,
PRIMARY KEY(id)
);
CREATE TABLE stars (
movie_id INTEGER NOT NULL,
person_id INTEGER NOT NULL,
FOREIGN KEY(movie_id) REFERENCES movies(id),
FOREIGN KEY(person_id) REFERENCES people(id)
);
CREATE TABLE directors (
movie_id INTEGER NOT NULL,
person_id INTEGER NOT NULL,
FOREIGN KEY(movie_id) REFERENCES movies(id),
FOREIGN KEY(person_id) REFERENCES people(id)
);
CREATE TABLE ratings (
movie_id INTEGER NOT NULL,
rating REAL NOT NULL,
votes INTEGER NOT NULL,
FOREIGN KEY(movie_id) REFERENCES movies(id)
);
CREATE TABLE people (
id INTEGER,
name TEXT NOT NULL,
birth NUMERIC,
PRIMARY KEY(id)
);

Answer 1

我想您想要一个JOIN和一个聚合函数：

SELECT AVG(r.rating) avg_rating_2012
FROM ratings r
INNER JOIN movies m on m.id = r.movie_id
WHERE m.year = 2012;

如果你想一次使用所有年份，那么使用GROUP BY:

SELECT m.year, AVG(r.rating) avg_rating
FROM ratings r
INNER JOIN movies m on m.id = r.movie_id
GROUP BY m.year;