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);

​x
 
CREATE TABLE MOVIE (    TITLE VARCHAR(255) NOT NULL,    YEAR INTEGER NOT NULL,    LENGTH INTEGER,    INCOLOR CHAR(1),    STUDIONAME CHAR(50),    PRODUCERC# INTEGER);​INSERT INTO MOVIEVALUES ('Pretty Woman', 1990, 119, 'Y', 'Disney', 199);​INSERT INTO MOVIEVALUES ('The Man Who Wasn''t There', 2001, 116, 'N', 'USA Entertainm.', 555);​INSERT INTO MOVIEVALUES ('Logan''s run', 1976, NULL, 'Y', 'Fox', 333);​INSERT INTO MOVIEVALUES ('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')

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

Advertisement

Answer