in this video we will continue looking at querying the data in a sql lite
database now we've seen a basic sql queries using the Select statement in
the previous video and it's time to look at that in more detail now once we've
used some more queries we're going to look at how to store commonly
used queries in what's called a view now the idea of views is common in most
relational databases and also going to introduce the sql join clause and show
how that's used to link tables together and that can result in quite complicated
queries so we'll also use views as a way to store a query so that we can reuse it
to whatever we need to do our previous contacts example only had a few records
and using a database to store a handful of row is probably overkill
it's probably quickly to scan three rows manual to find tim's phone number
than it is to type in a query but once you get a lot of rows though our database
becomes really useful now to save typing what I've done is I've created a database
containing details of a music collection so download the file music.zip from
the resources section for this video and you can see that I've downloaded that
zip file onto my desktop
once you've done that you want to extract the database file which is music
.DB and save it to a suitable location on your computer's hard drive so I'm just
going to double-click this which will extract the file and you can see that
i've got a file music.DB and what I'm going to do is I'm going to
change the directory to go to that folder which in this case is my desktop so
that we can access that database so the command for me is....
....and you can do a similar thing on linux to navigate to the folder and
on Windows you can do a CD space and just navigate to the folder typing in
whatever the directory structures is and on the windows you generally have to use
backslashes instead of forward slashes in any event moved to that folder on my case
i can do now an LS and I can see that I've got a couple of files in their
music.db is the one I want
and that command also work on linux and windows you type dir to see the files
alright so let's now go ahead and open that file remember we've put the sql
lite in the path so with a command prompt or terminal session already
opened as you can see I've got mine open on the screen I've change into the
directory and now going to type...now
incidentally I've given the file a DB extension but sql lite doesn't
actually care how your name the database file its usual to use something like .
DB or . SQLite but it really doesn't matter it is a good idea though to avoid
using . SQL that's usually used to indicate that the file contains as a
sql script we will talk about sql scripts a little later okay so we should now be
running sql lite with the music database loaded as I've got on the
screen now so let's start by reviewing the structure of the database
so the mini challenge is to remember what the appropriate sql lite command is
to display the structure of the database so type that in so you can actually see
what the structure of this particular databases so pause the video now and i
will come back and I'll show you what it is
ok so the solution to the challenges is the command to type is . schema....
....and you can see that gives us a list of all the tables and
the sql source code that was used to create them now you may have used the .
dump as well that's a fine but there's quite a lot of data in the table so you
have to scroll up a long way to see the structure of the tables if you did that
so generally speaking . schema is a better command to use here when we just
interested in the tables rather than their contents so I won't use the .dump
but you could do that if you want to now incidentally you're not too
familiar with command lines you can repeat previous commands using the up
and down arrow keys to recall them but with that said it doesn't always work it
depends on your version because with a mac I can't actually use an up arrow
here but I can use it when i'm outside of sql lite but sql lite for some
reason is mapping my arrow keys and not allowing me to actually use a previous
command but if your on
linux that will certainly work or will certainly work on the arrow keys and
it's also work on windows as well basically how you normally do just press
the up and down arrow keys to get to the command you want
and you can even use the left and right arrow keys to move around on the line if you
need to to edit the command before pressing enter again to executed now the
left and right arrow keys may not work if you're using ssh to connect on a
remote computer if you're doing that then you probably already know how to
move around the terminal so i'll have to be typing in the commands but just
bear in mind that you can probably use the up and down arrow keys to navigate
to a command to save you having to typing it multiple times
alright so looking at the schema command of the output on the screen
there we can see that the database contains three tables songs albums and
artists now each table actually contains an ID column which you can see there's
the first field and have called that _ID you don't have
to call it that but some of the java classes than android users to handle
databases actually require an ID column called and _ID so it's probably
a good habit to get into to actually do that but in fact that's the database is
at the moment the _ID column is just an integer field and we do have
to update it manually but i'll be changing that a little bit later in the
course for now _ID holds a number that uniquely identifies
the rows in the table so we can actually check this out by typing.....
....and you can see we got quite a few their we
ended up with the total of 201 artists and you can see the number on the left there
to the left of the artist name is uniquely identifying each one and the
same is true we do a search for albums...you can see we've
got a total 439 albums there and again the id is unique for each album now
the third column in the album's table is the ID of the artists so that we can see
that the last album that was created was created by a artist 133 now if you read the
screen very quickly when all the artists scroll past you may remember that this
was the black keys but we can actually check that to confirm that by typing
select.....and
you can see that the variety 133 from the artist table the name is black keys
and finally the song so.....
.....and you can see there's quite a few songs here over 5,000 in fact once
again each song has got a unique ID the second number is the position of the
song in its album and the final number is the id of the album so permanent
vacation which you can see the second to last one there is the tenth track in
album 367
another mini challenge
find the title of album 367 so type in the sql command necessary to return
that title of album 367 pause the video now and figure that out and when
you're ready to see me type in the solution start the video pause the video
now
alright so how do we actually find the title for album 367 we type select
....
....and we can see permanent vacation so the album in other words is also
called permanent vacation now we could also use select star as theirs is only three
columns in the album's table that's fine as well so....
....and that would have given the same result and obviously
it's returning the other two fields as well
now one thing I forgot to do was turn headers on and it's not a big deal but
it's helpful to see what the columns are called so let's do that now . headers
...see I can't use my up arrow
normally I'd be able to press the up arrow and get .headers to come back on the
screen again then just type in the rest
it's not letting me for some reason so headers....and now if I did command
again so.....you can see we've got the
field names at the top as well as the actual answer so the ID field can be used to
relate the songs and albums tables so we can easily see which album the song
belongs to now having to perform two queries to do that is a bit tedious but
I want to look a bit more at the structure of the tables and do some more
queering before we talk about how we can join the tables together before moving
on now I'm going to back up the database in case i do something silly with my
updates or deletes we're going to type in....
.....and you can see on my desktop the file music-back up 1
appeared and because it's on my desktop you can see the file that gets created
their so we can see that the file was successfully backed up
alright so let's have a look at the table structures again because there's a
couple of things in there that i didn't mention in the previous video and type
in . schema now the first thing is that the ID column is set to be the primary
key now a key in a table is an index which provides a way to really speed up
searches and joins on a column now when columns are indexed they can be searched
much faster than if they are not basically index columns are sorted so
that they can be searched through much faster now one thing I should mention
about relational databases is that the ordering of the rows is undefined so in
that respect they're very similar to java maps or to set in fact relational
database theory is heavily based on set theory so by defining a key
what you're doing is you're saying that the data should be ordered on that
column or group of columns and searches etc work far more efficiently as a
result of doing that now they can be lots of keys on a table but there can
only be one primary key now usually this is the ID column but if you don't have
an ID column in your table then you can choose another column to be the primary
key if you want now one important thing about the primary key though is that it
must be unique
let's try to add another artist using an insert statement......
....
....and when we do that we should
get an error you can see we've got an error their unique constraint failed
artists . _ID now personally I'm not actually too unhappy that i
could not add beoncy to my record collection but it failed because there's
already a record with a value 201 for its primary key so we get an error we try to
use that id
again now keys don't have to be unique and often you want to index a column
that doesn't have a unique value it doesn't have unique values a surname
column in our context database for example would benefit from being indexed
but many people can have the same surname
so you can have keys that aren't unique but the primary key must be unique
so type in schema again now the other thing to mention is the not null for the
text fields the name column of the artists and album tables is marked as
not null and the title column of songs is also not null and that means that the
columns must contain a value if you try to leave them blank when inserting new
record you'll actually get an error and if you think about it in this case it
really doesn't make much sense to store an artist without a name in the same for
an album so creating those columns as not null ensures that all albums and artists have
got a name and the same goes for song titles now sometimes a null value does
make sense a middle name column in a contacts table may you know well often be
null so it's fine in that situation to allow nulls but when designing a tables
have a think about the data and if it wouldn't make sense to have a null value
then use not null when creating the column now the primary key column in our
tables is automatically not null because integer primary key columns in sql
lite are treated in a special way and we can see that by having another go at
inserting Beyonce into the table so we come back and type insert into artist's
.....and this time we just type....
.....so this time we're
not providing an ID as a result we must explicitly specify the name column so
that's sql lite knows which column we want to have the value Beyonce so now i
do a select...so now beyonce is appeared at the table
right at the bottom
and note how it's how she has been automatically given the ID 202 an
integer primary key column can't contain null values and sql lite automatically
generates a unique number for the column if one isn't provided now this is
slightly different from the behavior of other databases other sql databases
such as Microsoft sql server where you have to specify autoincrement when
creating the column if you want the values to be automatically generated now
there's a description of this behavior and why you would normally use auto
increment in sql lite databases in the documentation so quickly take
you to that page particularly we've got some experience in other databases that
will be good to know this if you're going to be working with databases a lot
it's worth reading that but really all we need to know is that a sql lite
will create the ids for us and we don't have to worry about making sure
that we don't reuse an integer ID in a primary key field
alright so that's all I'm going to say about keys in this course database
administration is a very complex topic in its own right and the aim of this
section is to give you the basics so that's you can use databases to
store your programs data if you are going to be doing a lot of work with
databases and you'll probably need to know about keys and how they affect
performance both positively and negatively and stuff like that but we
don't really need anymore for what we're doing here so i'm going to end the
video here now in the next video will continue on with sql lite and we'll
start looking at the order by Clause i'll see you in the next video