Poena Poena - 22 days ago 5
SQL Question

SQL Query prints 4 times/Advice

I started learning SQL this semester and I've missed most of the classes so I'm not amazing but from I looked at today I created a table for a project I have to do over the break. It's really basic the tables I'm supposed to create, I'm just have to run some queries the data it holds after the data is inserted, but just one of the queries is printing out 4 time for some reason and I'm not that familiar with SQL so I can't figure it out but hopefully one of you can tell me what's the problem and I'd appreciate it if you could tell me what in the table I could improve on/do better.

The query is:

SELECT d.DIRECTOR_FNAME, d.Director_lname, s.studio_name
FROM DIRECTOR d, STUDIO s, FILM f, CASTING c
WHERE s.STUDIO_ID = f.STUDIO_ID
AND f.FILM_ID = c.FILM_ID
AND d.DIRECTOR_ID = c.DIRECTOR_ID
AND f.FILM_TITLE = 'The Wolf Of Wall Street';


And here's the table, I probably didn't need to put the entire table in but it's done now.

drop table casting;
drop table film;
drop table studio;
drop table actor;
drop table director;

CREATE TABLE studio(
studio_ID NUMBER NOT NULL,
studio_Name VARCHAR2(30),
PRIMARY KEY(studio_ID));

CREATE TABLE film(
film_ID NUMBER NOT NULL,
studio_ID NUMBER NOT NULL,
genre VARCHAR2(30),
genre_ID NUMBER(1),
film_Len NUMBER(3),
film_Title VARCHAR2(30) NOT NULL,
year_Released NUMBER NOT NULL,
PRIMARY KEY(film_ID),
FOREIGN KEY (studio_ID) REFERENCES studio);

CREATE TABLE director(
director_ID NUMBER NOT NULL,
director_fname VARCHAR2(30),
director_lname VARCHAR2(30),
PRIMARY KEY(director_ID));

CREATE TABLE actor(
actor_ID NUMBER NOT NULL,
actor_fname VARCHAR2(15),
actor_lname VARCHAR2(15),
PRIMARY KEY(actor_ID));

CREATE TABLE casting(
film_ID NUMBER NOT NULL,
actor_ID NUMBER NOT NULL,
director_ID NUMBER NOT NULL,
PRIMARY KEY(film_ID, actor_ID, director_ID),
FOREIGN KEY(director_ID) REFERENCES director(director_ID),
FOREIGN KEY(film_ID) REFERENCES film(film_ID),
FOREIGN KEY(actor_ID) REFERENCES actor(actor_ID));

INSERT INTO studio (studio_ID, studio_Name) VALUES (1, 'Paramount');
INSERT INTO studio (studio_ID, studio_Name) VALUES (2, 'Warner Bros');
INSERT INTO studio (studio_ID, studio_Name) VALUES (3, 'Film4');
INSERT INTO studio (studio_ID, studio_Name) VALUES (4, 'Working Title Films');

INSERT INTO film (film_ID, studio_ID, genre, genre_ID, film_Len, film_Title, year_Released) VALUES (1, 1, 'Comedy', 1, 180, 'The Wolf Of Wall Street', 2013);
INSERT INTO film (film_ID, studio_ID, genre, genre_ID, film_Len, film_Title, year_Released) VALUES (2, 2, 'Romance', 2, 143, 'The Great Gatsby', 2013);
INSERT INTO film (film_ID, studio_ID, genre, genre_ID, film_Len, film_Title, year_Released) VALUES (3, 3, 'Science Fiction', 3, 103, 'Never Let Me Go', 2008);
INSERT INTO film (film_ID, studio_ID, genre, genre_ID, film_Len, film_Title, year_Released) VALUES (4, 4, 'Romance', 4, 127, 'Pride and Prejudice', 2005);

INSERT INTO director (director_ID, director_fname, director_lname) VALUES (1, 'Martin', 'Scorcese');
INSERT INTO director (director_ID, director_fname, director_lname) VALUES (2, 'Baz', 'Luhrmann');
INSERT INTO director (director_ID, director_fname, director_lname) VALUES (3, 'Mark', 'Romanek');
INSERT INTO director (director_ID, director_fname, director_lname) VALUES (4, 'Joe', 'Wright');

INSERT INTO actor (actor_ID, actor_fname, actor_lname) VALUES (1, 'Matthew', 'McConnaughy');
INSERT INTO actor (actor_ID, actor_fname, actor_lname) VALUES (2, 'Leonardo', 'DiCaprio');
INSERT INTO actor (actor_ID, actor_fname, actor_lname) VALUES (3, 'Margot', 'Robbie');
INSERT INTO actor (actor_ID, actor_fname, actor_lname) VALUES (4, 'Joanna', 'Lumley');
INSERT INTO actor (actor_ID, actor_fname, actor_lname) VALUES (5, 'Carey', 'Mulligan');
INSERT INTO actor (actor_ID, actor_fname, actor_lname) VALUES (6, 'Tobey', 'Maguire');
INSERT INTO actor (actor_ID, actor_fname, actor_lname) VALUES (7, 'Joel', 'Edgerton');
INSERT INTO actor (actor_ID, actor_fname, actor_lname) VALUES (8, 'Keira', 'Knightly');
INSERT INTO actor (actor_ID, actor_fname, actor_lname) VALUES (9, 'Andrew', 'Garfield');
INSERT INTO actor (actor_ID, actor_fname, actor_lname) VALUES (10, 'Sally', 'Hawkins');
INSERT INTO actor (actor_ID, actor_fname, actor_lname) VALUES (11, 'Judi', 'Dench');
INSERT INTO actor (actor_ID, actor_fname, actor_lname) VALUES (12, 'Matthew', 'Macfadyen');

INSERT INTO casting (film_ID, actor_ID, director_ID) VALUES (1, 1, 1);
INSERT INTO casting (film_ID, actor_ID, director_ID) VALUES (1, 2, 1);
INSERT INTO casting (film_ID, actor_ID, director_ID) VALUES (1, 3, 1);
INSERT INTO casting (film_ID, actor_ID, director_ID) VALUES (1, 4, 1);
INSERT INTO casting (film_ID, actor_ID, director_ID) VALUES (2, 2, 2);
INSERT INTO casting (film_ID, actor_ID, director_ID) VALUES (2, 5, 2);
INSERT INTO casting (film_ID, actor_ID, director_ID) VALUES (2, 6, 2);
INSERT INTO casting (film_ID, actor_ID, director_ID) VALUES (2, 7, 2);
INSERT INTO casting (film_ID, actor_ID, director_ID) VALUES (3, 5, 3);
INSERT INTO casting (film_ID, actor_ID, director_ID) VALUES (3, 8, 3);
INSERT INTO casting (film_ID, actor_ID, director_ID) VALUES (3, 9, 3);
INSERT INTO casting (film_ID, actor_ID, director_ID) VALUES (3, 10, 3);
INSERT INTO casting (film_ID, actor_ID, director_ID) VALUES (4, 5, 4);
INSERT INTO casting (film_ID, actor_ID, director_ID) VALUES (4, 8, 4);
INSERT INTO casting (film_ID, actor_ID, director_ID) VALUES (4, 11, 4);
INSERT INTO casting (film_ID, actor_ID, director_ID) VALUES (4, 12, 4);

Answer

First question you should be asking is "What Result Set Am I Looking For?". Your query is returning The Director First Name, Director Last Name, and Studio Name where the Film Title is "The Wolf of Wall Street", for each cast that belongs to that Film. You get 4 records because you get a record for each casting where FilmId = 1 in this case. Include the ActorId column and you will see what I am talking about. Hope that helps... if not I would study harder earlier.

Comments