Course 9.2Lesson 1 of 3
SQL is the language we use to ask a database for data. A script starts with SELECT, which names the fields to show, and FROM, which names the table. You write such scripts, read them, and work out their exact output.
Try the code and the simulations beside the lesson.
A librarian wants a list of the titles and the authors of all the books. She does not want the page counts or the book IDs. How does she ask?
She writes a short script in SQL (structured query language). SQL is the language that almost every database understands. A script that asks a database for data is called a query.
Here is the library's table, Books. We use it in all three lessons of this course.
BookID Title Author Genre Pages OnLoan
K1 Night Train Adeyemi Mystery 320 TRUE
K2 Green Hills Silva Nature 180 FALSE
K3 Lost Key Adeyemi Mystery 256 FALSE
K4 Deep Blue Haddad Nature 410 TRUE
K5 Iron Bridge Kowal History 288 TRUE
K6 Snow Fox Silva Nature 144 FALSEThe two words every query needs are SELECT and FROM.
SELECT is followed by the field names to show, with a comma between them.FROM is followed by the name of the table.; ends the script.SELECT Title, Author
FROM Books;The output:
Title Author
Night Train Adeyemi
Green Hills Silva
Lost Key Adeyemi
Deep Blue Haddad
Iron Bridge Kowal
Snow Fox SilvaRead the script out loud: "show the title and the author, from the table Books". The output has the two fields we asked for and every record, because nothing in the script leaves a record out.
The fields come out in the order they are written after SELECT, not in the order of the table.
SELECT Pages, Title
FROM Books;The output:
Pages Title
320 Night Train
180 Green Hills
256 Lost Key
410 Deep Blue
288 Iron Bridge
144 Snow FoxThe records come out in the order they are stored in the table. The third lesson shows how to sort them.
SELECT and FROM in capitals. It makes the script easy to read.Title, not Book title.The program builds the Books table with executemany. Then it runs a script, prints the names of the output fields and prints each output row. SQLite shows TRUE as 1 and FALSE as 0.
SELECT Title Author is not the list Title, Author.SELECT Title, Author, FROM Books is an error.SELECT chooses the fields (the columns). The next lesson chooses records.SELECT names the fields to output, in the order you want them.FROM names the table.Try each one on your own first. If you are stuck, open a hint. Then check with Show the answer.
Genre and the Title of every book, in that order.SELECT BookID, Pages, OnLoan FROM Books; output?SELECT Author, Pages FROM Books;?SELECT Title, FROM Books;Every lesson is open to read. Enrolling also lets you:
Log in to join the discussion.
Loading…
This lesson is free to read. Enroll free to keep your place and take the tests.