Reading Files and Formatting Results3:26 with Ben Deitch
In this video we'll show how to read in SQL commands from a text file. Then we'll see how to format the results of a SELECT statement to make them more readable.
Now that we've got a table, let's start inserting some data. 0:00 Just like how we created the table, we can insert data right from the command line. 0:03 Let's try inserting a 2003 Ford Taurus as our first row. 0:08 Let's type INSERT INTO CARS, And 0:13 for the values, we can give an ID of one for 0:18 the first row, and we need Ford, 0:24 Taurus, and 2003. 0:29 And don't forget the semi colon. 0:33 Then we can hit Enter to run it. 0:35 And since we don't see an error message, that means that it worked. 0:38 And if we want to make sure it worked, we can use the Select statement. 0:42 Let's try selecting * from the CARS table to see what we've got. 0:45 Perfect, there's our car. 0:54 Though you don't always have to write your SQL statements at the command line. 0:57 If you happen to have a lot of statements to run or just want to write SQL using 1:02 a more modern editor, then you can always put your SQL in a separate file and 1:05 use the .read command to execute the SQL statements contained in that file. 1:10 In fact, if we scroll up a little bit, 1:15 we can see the .read command right here. 1:20 Okay, go ahead and 1:25 download the new cars that txt file from the Teacher's Note below. 1:26 Once you got the file, 1:30 let's put it on our database practice folder and see what we've got. 1:32 Looks like a few insert statements. 1:39 But if you wanted, you could include any kind of SQL here. 1:41 You could create tables, delete tables, whatever you want. 1:45 Back in the terminal, 1:50 let's try to run those insert statements by using the read command. 1:50 Let's type .read and then the file 1:56 name which is new_cars .txt. 2:02 Then hit enter and it looks like it worked. 2:07 Then let's use the up arrow to choose the select statement again and 2:10 see what we've got in the table. 2:14 Awesome, it ran the insert statements and added the new cars. 2:17 Though it's a little difficult to see what's going on here, everything's all 2:22 scrunched together and it would be nice if we could see the column headers. 2:25 Luckily, SQL Lite gives us tools to solve each of these problems. 2:30 To turn headers on, we just need to use the .headers command and set it to on. 2:35 Let's type .headers on. 2:39 Hit Enter, and 2:45 then run our Select statement again using the up arrow to choose it. 2:47 And there we go, we've got some headers. 2:52 Now to fix the column spacing, we can use the .mode command and 2:56 set the mode to column. 2:59 So let's type .mode column, then run that command, arrow up to our select statement. 3:03 And success! 3:11 We can finally read the results of our query without needing to squint. 3:13 There's so much you can do with SQL, even right from the command line. 3:18 So the next time you need to set up a database, give SQLite a try. 3:22
You need to sign up for Treehouse in order to download course files.Sign up