Need guide on managing sqlite files

Question

I have to do the following:

In 1.sql, write a SQL query to list the titles of all movies released in 2008. Your query should output a table with a single column for the title of each movie.

my sql file is this:

TABLE movies (
                    id INTEGER,
                    title TEXT NOT NULL,
                    year NUMERIC,
                    PRIMARY KEY(id)
                );

And my code is:

sqlite> CREATE TABLE TITLES(titel)                                                                                                                                  
   ...> INSERT INTO TITLES(titel)                                                                                                                                   
   ...> VALUES (SELECT title FROM movies WHERE year = 2008);

Apparently it throws me this error:

Error: near "INSERT": syntax error

How can I solve this? thanks in advance

If you want to create a table from a query the syntax is `CREATE TABLE AS SELECT ;`. There's no `INSERT` or `VALUES` in it. (But I don't know if "table" in " Your query should output a table" means a physical table. I'd rather think they mean the result set when writing about a "table" in that context. Maybe you want to clarify this with your teacher.) — sticky bit, Mar 14 '20 at 04:35
This appears to be a question about using the sglite console and is unrelated to python. If so, remove that tag to avoid confusion. — tdelaney, Mar 14 '20 at 06:10
https://stackoverflow.com/questions/12741891/run-multiple-commands-in-sqlite-manager — Digvijay S, Mar 14 '20 at 09:49

score 0 · Answer 1 · answered Mar 14 '20 at 14:02

CREATE TABLE movies (
                    id INTEGER,
                    title TEXT NOT NULL,
                    year NUMERIC,
                    PRIMARY KEY(id)
                );

INSERT INTO MOVIES VALUES (1, 'The Dark Knight' , 2008);

CREATE TABLE TITLES(titel TEXT) ; 

INSERT INTO TITLES SELECT title from movies where year =2008;

SQL Fiddle

Need guide on managing sqlite files

1 Answers1