问题遇到的现象和发生背景
创了几个表然后需要用子查询达到要求。
一个是查找以所有可能的媒体格式出现的所有电影。
第二个是找到一个租来的电影都是“科幻”类的客户。
问题相关代码,请勿粘贴截图
CREATE TABLE Movie (
id INTEGER PRIMARY KEY,
title VARCHAR(60) NOT NULL,
category CHAR(10),
year DATE,
director VARCHAR(30),
pricePDay DECIMAL(4,2),
length INTEGER,
CONSTRAINT plausible_year
CHECK (year > TO_DATE('01.01.1900', 'DD.MM.YYYY')),
CONSTRAINT allowedPrice
CHECK((pricePDay >= 0) AND (pricePDay < 100.0))
);
CREATE TABLE Format (
name CHAR(6) PRIMARY KEY,
charge DECIMAL (3,2)
);
CREATE TABLE Media (
id INTEGER PRIMARY KEY,
format CHAR(6) NOT NULL,
movie_id INTEGER NOT NULL,
CONSTRAINT mediaNotEmpty
FOREIGN KEY (movie_id) REFERENCES Movie(id)
ON DELETE CASCADE,
CONSTRAINT formatCheck
FOREIGN KEY (format) REFERENCES Format(name)
ON DELETE SET NULL
);
select * from media;
CREATE TABLE Actor (
stage_name VARCHAR(30) NOT NULL UNIQUE,
real_name VARCHAR(30),
birthday DATE
);
CREATE TABLE Play (
movie_id INTEGER,
actor_name VARCHAR(30),
CONSTRAINT pkStarr
PRIMARY KEY (movie_id, actor_name),
CONSTRAINT foreignKeyMovieID
FOREIGN KEY (movie_id)
REFERENCES Movie (id),
CONSTRAINT foreignKeyStagename
FOREIGN KEY (actor_name)
REFERENCES Actor(stage_name)
);
CREATE TABLE Customer (
mem_no INTEGER PRIMARY KEY,
last_name VARCHAR (30) NOT NULL,
first_name VARCHAR(20),
address VARCHAR (60),
telephone VARCHAR (15)
);
CREATE TABLE Rental(
media_id INTEGER,
mem_no INTEGER,
from_date DATE NOT NULL,
until_date DATE,
PRIMARY KEY (media_id, mem_no, from_date),
CONSTRAINT fk_Media
FOREIGN KEY (media_id) REFERENCES Media(id),
CONSTRAINT fk_Customer
FOREIGN KEY (mem_no)
REFERENCES Customer(mem_no)
);
--inserting to tables
--format information (1)
insert into format values ('DVD', '2.00');
insert into format values ('BluRay', '0.00');
insert into format values ('VHS', '0.00');
--customer information (2)
insert into customer values (001, 'Muller', 'Tina', NULL, NULL);
insert into customer values (002, 'Maus', 'Carla', NULL, NULL);
insert into customer values (003, 'Wayne', 'Bruce', '1007 Mountain Drive', NULL);
insert into customer values (004, 'Mason', 'Mark', NULL, NULL);
insert into customer values (005, 'Pennyworth', 'Alfred', '1007 Mountain Drive', NULL);
insert into customer values (006, 'Fischer', 'Robert', '99 Inception Crescent', '528491');
insert into customer values (007, 'Katz', 'Anna', NULL, NULL);
insert into customer values (008, 'Borden', 'Alfred', '2 Prestige Lane', '9874563');
insert into customer values (009, 'Cazorla', 'Santi', '19 Channel Road', '188972');
insert into customer values (010, 'Wright', 'Thomas', '1901 Geneva Street', '8765435');
insert into customer values (011, 'Banks', 'Louise', '12 Arrival Place', '12345678');
insert into customer values (012, 'Ferguson', 'Alex', '19 Manchester Avenue', '9872164');
insert into customer values (013, 'Hackworth', 'Joseph', '45 Ridgeway Place', NULL);
insert into customer values (014, 'Einarsson', 'Rita', '8 December Street', '8755423');
insert into customer values (015, 'Chill', 'Joe', '13 Crime Alley', '4577952');
insert into customer values (023, 'Kunz', 'Anna', NULL, NULL);
--movie information (3)
insert into movie values (94, '2001: A Space Odyssey', 'scifi', to_date('1968', 'yyyy'), 'Kubrick', 2.00, 141);
insert into movie values (95, 'Psycho', 'suspense', to_date('1969', 'yyyy'), 'Hitchcock', 2.00, 109);
insert into movie values (100, 'Jaws', 'thriller', to_date('1975', 'yyyy'), 'Spielberg', 1.50, 130);
insert into movie values (112, 'ET', 'comedy', to_date('1982', 'yyyy'), 'Spielberg', 1.50, 121);
insert into movie values (120, 'Full Metal Jacket', 'war', to_date('1987', 'yyyy'), 'Kubrick', 1.50, 118);
insert into movie values (222, 'Psycho', 'horror', to_date('1998', 'yyyy'), 'Van Sant', 2.20, 109);
insert into movie values (290, 'Star Wars IV', 'fantasy', to_date('1977', 'yyyy'), 'Lucas', 2.00, 125);
insert into movie values (291, 'Star Wars V', 'fantasy', to_date('1980', 'yyyy'), 'Kershner', 2.00, 127);
insert into movie values (292, 'Star Wars VI', 'fantasy', to_date('1980', 'yyyy'), 'Marquand', 2.00, 136);
insert into movie values (300, 'Saving Private Ryan', 'war', to_date('1999', 'yyyy'), 'Spielberg', 2.00, 270);
insert into movie values (345, 'Star Wars I', 'fantasy', to_date('1999', 'yyyy'), 'Lucas', 2.00, 125);
insert into movie values (400, 'Arrival', 'scifi', to_date('2016', 'yyyy'), 'Villenueve', 2.00, 118);
--media information (4)
insert into media values (1, 'DVD', 95);
insert into media values (2, 'DVD', 112);
insert into media values (3, 'VHS', 222);
insert into media values (4, 'DVD', 345);
insert into media values (5, 'VHS', 345);
--
insert into media values (6, 'DVD', 94);
insert into media values (7, 'VHS', 94);
insert into media values (8, 'BluRay', 94);
insert into media values (9, 'DVD', 100);
insert into media values (10, 'VHS', 100);
insert into media values (11, 'VHS', 345);
insert into media values (12, 'DVD', 120);
insert into media values (13, 'DVD', 290);
insert into media values (14, 'DVD', 291);
insert into media values (15, 'DVD', 292);
insert into media values (16, 'BluRay', 300);
insert into media values (17, 'BluRay', 400);
--rental information (5)
insert into rental values (3, 1, to_date('2002-05-01', 'yyyy-mm-dd'), NULL);
insert into rental values (4, 1, to_date('2002-05-01', 'yyyy-mm-dd'), NULL);
insert into rental values (5, 3, to_date('2002-05-01', 'yyyy-mm-dd'), to_date('2002-05-02', 'yyyy-mm-dd'));
--
insert into rental values (2, 4, to_date('2002-05-01', 'yyyy-mm-dd'), to_date('2002-05-03', 'yyyy-mm-dd'));
insert into rental values (1, 4, to_date('2002-05-01', 'yyyy-mm-dd'), to_date('2002-05-03', 'yyyy-mm-dd'));
insert into rental values (10, 5, to_date('2002-05-01','yyyy-mm-dd'), to_date('2002-05-02', 'yyyy-mm-dd'));
insert into rental values (10, 1, to_date('2002-05-05', 'yyyy-mm-dd'), to_date('2002-05-06', 'yyyy-mm-dd'));
insert into rental values (1, 2, to_date('2002-05-02', 'yyyy-mm-dd'), to_date('2002-05-06', 'yyyy-mm-dd'));
insert into rental values (7, 2, to_date('2002-05-01', 'yyyy-mm-dd'), to_date('2002-05-02', 'yyyy-mm-dd'));
insert into rental values (8, 8, to_date('2002-05-01', 'yyyy-mm-dd'), to_date('2002-05-02', 'yyyy-mm-dd'));
insert into rental values (2, 23, to_date('2002-05-03', 'yyyy-mm-dd'), to_date('2002-05-04', 'yyyy-mm-dd'));
insert into rental values (2, 8, to_date('2002-05-04', 'yyyy-mm-dd'), to_date('2002-05-05', 'yyyy-mm-dd'));
insert into rental values (12, 8, to_date('2002-05-04', 'yyyy-mm-dd'), to_date('2002-05-06', 'yyyy-mm-dd'));
insert into rental values (3, 8, to_date('2002-05-06', 'yyyy-mm-dd'), to_date('2002-05-09', 'yyyy-mm-dd'));
insert into rental values (3, 10, to_date('2002-05-10', 'yyyy-mm-dd'), to_date('2002-05-11', 'yyyy-mm-dd'));
insert into rental values (11, 5, to_date('2002-05-10', 'yyyy-mm-dd'), to_date('2002-05-11', 'yyyy-mm-dd'));
insert into rental values (9, 8, to_date('2002-05-12', 'yyyy-mm-dd'), NULL);
insert into rental values (13, 8, to_date('2002-05-12', 'yyyy-mm-dd'), NULL);
insert into rental values (14, 8, to_date('2002-05-12', 'yyyy-mm-dd'), to_date('2002-05-13', 'yyyy-mm-dd'));
insert into rental values (6, 9, to_date('2002-05-19', 'yyyy-mm-dd'), to_date('2002-05-20', 'yyyy-mm-dd'));
insert into rental values (1, 8, to_date('2002-05-19', 'yyyy-mm-dd'), to_date('2002-05-20', 'yyyy-mm-dd'));
insert into rental values (15, 8, to_date('2002-05-19', 'yyyy-mm-dd'), to_date('2002-05-20', 'yyyy-mm-dd'));
insert into rental values (11, 8, to_date('2002-05-19', 'yyyy-mm-dd'), to_date('2002-05-21', 'yyyy-mm-dd'));
insert into rental values (14, 23, to_date('2002-05-10', 'yyyy-mm-dd'), to_date('2002-05-11', 'yyyy-mm-dd'));
insert into rental values (16, 8, to_date('2002-05-20', 'yyyy-mm-dd'), NULL);
insert into rental values (17, 8, to_date('2002-05-20', 'yyyy-mm-dd'), NULL);
--actor information (6)
insert into actor values ('Hitchcock', 'Hitchcock', to_date('1899-08-13','yyyy-mm-dd'));
insert into actor values ('Harrison Ford', 'Harrison Ford', to_date('1942-07-13','yyyy-mm-dd'));
--
insert into actor values ('Douglas Rain', 'Douglas Rain', to_date('1928-03-13', 'yyyy-mm-dd'));
insert into actor values ('Roy Schneider', 'Roy Schneider', to_date('1932-11-10', 'yyyy-mm-dd'));
insert into actor values ('Henry Thomas', 'Henry Thomas', to_date('1971-09-09', 'yyyy-mm-dd'));
insert into actor values ('Matthew Modine', 'Matthew Modine', to_date('1959-03-22', 'yyyy-mm-dd'));
insert into actor values ('Vince Vaughn', 'Vince Vaughn', to_date('1970-03-28', 'yyyy-mm-dd'));
insert into actor values ('Tom Hanks', 'Tom Hanks', to_date('1956-07-09', 'yyyy-mm-dd'));
insert into actor values ('Ewan McGregor', 'Ewan McGregor', to_date('1971-03-31', 'yyyy-mm-dd'));
insert into actor values ('Amy Adams', 'Amy Adams', to_date('1974-08-20', 'yyyy-mm-dd'));
--play information (7)
insert into play values(290,'Harrison Ford');
insert into play values(95,'Hitchcock');
--
insert into play values(94, 'Douglas Rain');
insert into play values(100, 'Roy Schneider');
insert into play values(112, 'Henry Thomas');
insert into play values(120, 'Matthew Modine');
insert into play values(222, 'Vince Vaughn');
insert into play values(291, 'Harrison Ford');
insert into play values(292, 'Harrison Ford');
insert into play values(300, 'Tom Hanks');
insert into play values(345, 'Ewan McGregor');
insert into play values(400, 'Amy Adams');
我的解答思路和尝试过的方法
我想到的一点思路是先通过count和groupby得出等于3的影片,(select count (media. format) from media group by media.movie_id) =3;然后加上了select * from movie where在前面但是行不通。还是说应该使用EXISTS?