Is there a way to use a table value attribute in where clause in SQL

I’m very new to SQL and i feel like this is a dumb question because I couldn’t find an answer to it…

I have this table in a SQL query:

CREATE TABLE MOVIE 
(
    TITLE VARCHAR(255) NOT NULL,
    YEAR INTEGER NOT NULL,
    LENGTH INTEGER,
    INCOLOR CHAR(1),
    STUDIONAME CHAR(50),
    PRODUCERC# INTEGER
);

INSERT INTO MOVIE
VALUES ('Pretty Woman', 1990, 119, 'Y', 'Disney', 199);

INSERT INTO MOVIE
VALUES ('The Man Who Wasn''t There', 2001, 116, 'N', 'USA Entertainm.', 555);

INSERT INTO MOVIE
VALUES ('Logan''s run', 1976, NULL, 'Y', 'Fox', 333);

INSERT INTO MOVIE
VALUES ('Star Wars', 1977, 124, 'Y', 'Fox', 555);

I want to write a query where I get only the movies with length bigger than that of “Star Wars”. Is there another way to do it instead of “WHERE length > 124” where I put something like a variable after ‘>’ rather than a number

Answer

Probably easiest with a subquery:

where length > (select length from movie where title = 'Star Wars')

Advertisement

Answer