老外的 oracle 的练习题求解～

来源：百度知道 编辑：UC知道 时间：2024/05/16 07:38:32
这是2个练习题有关一个DVD商店的数据库

问题：1）List the title of films which have actor ’TIM HACKMAN’ but not ’PENELOPE MONROE’.
注： 一个FILM可以有好几个ACTOR, 一个ACTOR也可以有好几个FILM

2) List the title and the number of actors appearing in films in the year 2006. Be sure that movie ’SLACKER LIAISONS’ is in the list.
这道题好像应该用到CHECK语句吧？

数据库：

CREATE TABLE Actor (
actor_id SMALLINT NOT NULL,
actor_name CHARACTER VARYING(45) NOT NULL,
last_update TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL
);

CREATE TABLE Category (
category_id SMALLINT NOT NULL,
name CHARACTER VARYING(25) NOT NULL
);

CREATE TABLE Language (
language_id SMALLINT NOT NULL,
name CHARACTER(20) NOT NULL
);

CREATE TABLE Film (
film_id SMALLINT NOT NULL,
title CHARACTER

1、
select Film.title
from Film,Film_Actor ,Actor
where Film.film_id = Film_Actor.film_id and
Film_Actor.actor_id = Actor.actor_id and
Actor.actor_name ='TIM HACKMAN’ and Actor.actor_name <>'PENELOPE MONROE’;

2、
select Film.title,count(distinct Actor.actor_name )
from Film,Film_Actor ,Actor
where Film.film_id = Film_Actor.film_id and
Film_Actor.actor_id = Actor.actor_id and
Film.release_year = 2006 and
Film.title='SLACKER LIAISONS'
group by Film.title;