How do you write queries in SQL?

Question

I am currently taking CS50, an online introductory course in coding by Harvard. We have just covered SQL and I am trying to attempt the question "Movies" in the problem set now, a description of which can be found here: https://cs50.harvard.edu/x/2020/psets/7/movies/

However, I am not sure how to do this correctly.

For example, for 1.sql, my code is as follows:

SELECT title FROM movies WHERE year = 2008;

I literally wrote only that one line and nothing more in the file "1.sql".

But when I run

$ cat 1.sql | sqlite3 movies.db

in my terminal, nothing happens?

Is this how I am supposed to write code for SQL? Or am I missing some stuff that I should be including (e.g. as headers or what) above my query?

To be clear, I believe I know how to write a query itself but I do not know the "protocol" to write it, if I may. I mean, for example, I am positively sure that "SELECT title FROM movies WHERE year = 2008;" fulfils the question's first requirement.

Some enlightenment would be appreciated!

EDIT 1

Okay first I must apologise to everyone who so very kindly took the time to comment on my post. For some very odd reason, my query did not return any results the first time I ran it. However, when I tried it again, it worked perfectly! Not sure what went wrong honestly, but all is well now! So sorry for wasting everyone's time ):

EDIT 2

Okay I also figured out that the reason why I could not execute my query is that I was in "sqlite3" in my terminal. I was supposed to run the command to execute my query in the main terminal i.e. not when it says "sqlite3". Stupid. I know.

I can't tell you about the query tool or environment, but your query itself is fine. — TomC, Jun 15 '20 at 05:25
If you just use `sqlite3 movies.db`, do you get into a command line environment where you can type in and execute that query? Also, just to cover basics, does `cat 1.sql ` show that single query? — Hans Keﬆing, Jun 15 '20 at 06:29
@HansKeﬆing Thank you for your comment! Please see my edit :) — Ethan Mark, Jun 15 '20 at 06:51

score 0 · Answer 1 · answered Jul 18 '20 at 17:59

Option 1) you can run "$ cat 1.sql | sqlite3 movies.db" in your normal terminal (not in the sqlite3 mode)

Option 2) you can open sql environment by typing "sqlite3 movies.db" (it will open movies.db if it exists, or create a temporary one if it doesn't exist). Then you can type ".read 1.sql" after the "sqlite>"

score -1 · Answer 2 · answered Jun 15 '20 at 05:27

-1

Open Browser and test it as mention on your course site

Usage To test your queries on CS50 IDE, you can query the database by running

$ cat filename.sql | sqlite3 movies.db where filename.sql is the file containing your SQL query.

Or you can paste them into DB Browser for SQLite’s Execute SQL tab and click ▶.

answered Jun 15 '20 at 05:27

Muhammad Faisal

54
1
5

2

The OP _is already running that cat command_ it's stated in their question. The question is: why is there no output ?? – Nick.Mc Jun 15 '20 at 05:31

How do you write queries in SQL?

2 Answers2