>> Okay, good evening, everyone.
I'm covering section tonight on SQL.
And first question I would like to ask is what is SQL?
Does anyone know what SQL is?
SQL stands for structure query language
and it is a standard language used
to query relational databases.
What does it look like?
It looks like what you see in green,
the first green statement is actually select statement
in the sequel.
And, and this example, we are selecting all the rows
from a table called employees where an ID is equal to 5.
The second point in this, in this illustration states
that a queries and alters relational databases.
The reason why I mention alters is the fact
that SQL does not only query the database as it,
as it statement intends,
but it also does inserts data into the database.
It modifies data.
It deletes data as well as alters database,
structures in the database.
For example, it might add some new fields to a table
or remove some fields or add a primary key,
remove a primary key, and a foreign key and so forth.
Similarly, we have, I, I've listed 2 sequel examples
for how it's, would alter or would query the database.
When I say query, you would be most likely using the select
command and whereas alters could be updates, insert, delete,
drop add and so forth.
Now, these relational databases are typically stored in a piece
of software that is named,
that is called the relational database management system.
And what this does typically is that it, the database,
the RDBMS typically manages the relational databases
stored inside.
So, it would do tasks such as backups,
replication and the like.
The prominent, the prominent RDBMSs out there are my sequel,
sequel server, which is created by Microsoft, Oracle by Oracle,
MS Access, Microsoft Access
which is obviously created by Microsoft.
And the last 3 that I mentioned are actually proprietary
and cost money to, it costs money
to actually implement them whereas My sequel is free.
My S, My SQL.
That's how it's supposed to be said.
So, in, in this class, we will be covering the My SQL
relational database management system.
And in addition, we're, we're going to be using a,
an application, a web application created in php
to interface with this relational database
management system.
The web application which we will be using is actually called
php my admin.
And I will cover this briefly and shortly.
So, what are the most used sequel commands?
There are, in my opinion there are 4 prominently used
sequel commands.
One is insert, the other is update, delete and select.
Think of, for example, navigating to Facebook.
Once you load the, the first page, you're more,
you're more than likely
to encounter a select command on the database side.
Select was going to go out and query information
which will be pulled out and rendered by the browser.
Insert enters the data.
So, in this same example, let's say you,
you posted a comment that's going
to be inserted in the database.
Update will update an existing piece of data for example,
and delete will remove something.
Like, let's say you were trying to remove a thread
or an event and so forth.
That would entail the usage of something like delete.
So, insert, update, delete
and select are 4 prominent sequel commands.
And the second 4 that I find important to mention,
important enough to mention, are create, alter, add and drop.
Although there are still,
there are several more sequel commends.
But these, I mention these in particular because they pertain
to the database structure.
And what do I mean by this?
I mean in the case of you wanting to create a database
for example, you would use the create command in the case
that you would like to create a,
a new table you would use a create command.
And for, if you choose to for example alter a table,
add some columns, remove columns or add some constraints
or fields, you would use the add commands.
And lastly, the drop command is similar to a delete but it works
on the database structure side.
So, drop would, would be used for example
to remove a database, to remove a table permanently
from your system or to remove constraints such as indexes,
primary keys, foreign keys and the like.
Okay, so example time.
I will go ahead and pull up php my admin
which should be included in your appliance.
And the way to access it is simply to open a web browser
and navigate to appliance forward slash php my admin.
First thing you will notice is that you will be prompted
for a user name and password.
The appliance is configured in such a way
that the user name is j Harvard and the password is crimson.
So, this is the php my admin web interface,
which is right now interfacing
with the my SQL database management system.
I would like to mention
that right now we are using the j Harvard user but for,
for future usage and post this class, if you choose
to deploy your own databases and so forth, you, I would advise
that you create a separate user for each additional database
such that it will be, security will be compartmentalized
and it's much, it's much better for the long term, I think.
Okay, so I'm going to briefly go over the, this interface,
although David kind of touched on this in class.
The databases tab enumerates or, you know,
lists all the databases that, that are currently living
in your current installation of my SQL.
And this is also where you can actually create a new database.
If I click on here for example,
I can type in let's say CS 75 test would be the name
of my new database.
The collation is the character set
which this database will support.
By character sets I mean, like, something like UTF 8.
So, for example if, if this database were
to store Chinese characters,
the current character set would not be able to support it.
And so you would have to search through
and find the appropriate character set.
So, the collation is, is first default,
it defaults to Latin 1 underscore Swedish underscore CI
with the current installation.
And this cascades down to tall the newly created tables inside
this database as well as all the newly created fields inside the
database, all the new, newly created columns.
Fields and columns are the same thing, essentially.
Though you can still, you can override this setting
on the database level, on the, on the table level as well
as on the field level.
Okay, the next tab across is sequel and here's
where I can actually run sequel commands.
And when I run a sequel command here and I click on go,
it will send that sequel command to the my SQL installation
and then that will give me back the resulting output.
Users is where you manage your users.
Export is where you can use, you can use this feature
to export your database out to perhaps another server.
Import is the reverse of this
in that you can import an existing database
from somewhere.
And for the most part, I'm not going
to mention the remaining items here.
You're, feel free to check them out.
Okay.
Let's go back to this.
So, example time.
In my example, I would like to keep track
of CS 75 staffs CD collection.
And this will entail several steps.
First off, we need to record staff member's first name
and last name.
Then, we need to record each CDs, artists,
every CD's artist and title.
And lastly we need to record what CDs each staff member has
and when it was acquired.
Okay, so the first step.
What would the first step be here.
Would anyone like to participate?
>> Create table?
>> One thing before that.
You're pretty close.
One, so, so the answer was create a table.
And I had mentioned that there's a, just one step before that,
before you'd create a table.
[ Inaudible audience comment ]
Okay, pretty, pretty close,
but first off we need to create a database.
Because ultimately this my SQL can,
can store one or more databases.
So, let's give this name, CS 75 staff CDs.
And we're going to leave the default, the collation
to default because for the most part,
it will be using Latin character set.
And so I click on create.
I can go ahead and actually, I'm going to delete this and do it
through the sequel line command, sequel command line,
just for demonstrative purposes.
So, I'm going to issue a, okay.
Need to go back out here so I can have rights to do this.
Draw up database CS 75 staff CDs.
And there you go.
Now, now the database is gone.
So I'm going to create the same database
but using sequel this time.
And I will use the create command.
So, create and then, can anyone guess what the next word
after this would be?
>> Database.
So, the answer was database and, which his correct.
Create database and then database name.
CS 75 staff CDs, and that's all I really need.
And now I have my, my newly created database.
Okay, now onto the next step.
And someone had mentioned that we need to create a table.
And yes we do, we will create a table.
Create, and what comes after create?
>> Create a table.
>> Table. Exactly.
>> Table name.
Let's, what, what should we call this table?
Something that will be representative of a table
that stores first name, last name, CD artist,
CD title and date acquired.
Let's just, let's call it staff collections.
And open parenthesis, close parenthesis.
In other systems, I believe it's open curly brackets,
close curly brackets.
But for this, it's parenthesis.
So, in here, we need to start actually enumerating the actual,
or creating the fields that will store the different types
of data that we like to keep.
Let's start with the first column name,
which is first name.
And what data type would you recommend us using?
>> Varchar.
>> So, the answer was varchar.
Sure, varchar is a good candidate.
And how many characters should we be able to support?
>> 30?
>> 30? 30 sounds reasonable.
So, first name.
we're going to do the same thing with last name.
And CD artist.
Perhaps we could do the same with this.
And CD title.
Lastly, date acquired.
And what type of, what data type should this be or,
is preferable to be?
>> Date.
>> Date. So, there is a date, there,
there is a native date data type in my SQL.
And so when I'm done with this, all I need to do is click on go.
And you can see here under the CS 75 staff CDs database,
I mention of staff under, underscore collections,
which is the table we just had created.
And I can click on this.
When I click on this, I can see every, each field
and its corresponding values.
I met the values.
The type column specifies what data type this is.
The collation, as mentioned earlier, is the character set
that this, this field will support.
Again, I can over-ride the system default and modify it
to the character, the collation of my choice.
And null I will just skip over attributes.
Null specifies whether this, this column can be empty or not.
If, if I say no, if I change this yes to no,
then when I'm entering new data and I do not specify anything
for this, it will error out
and will not let me insert the newly, the intended data.
Default is the actual default value
that will appear each time I create a new instance of,
of this table.
So, if I create a new instance whereby there's a first name,
last name, CD artist, CD title and date acquired.
If I say for example I'm only going
to be covering certain artists, I can just put
that artist's name in here.
I can modify this to default to the artist's title,
and therefore I would not have to input
that artists' title each time.
But this is a very unrealistic example.
Change actually is, is a gooey component to php my admin
which will let you actually,
which will let you change the method data for this,
the different constraints and so forth for this field.
Drop simply deletes entire column, whatever column your,
you choose, and I'm sure you could do more
than one at the same time.
Browse distinct values will specify, will enumerate, let's,
let's say for example we have 3 Davids,
or 3 Davids in this class.
If I click on browse distinct values it,
it would ultimately show me just one David one time.
It won't show it more than once.
Primary key.
The primary key command.
The primary key command will, will change the column
to become a primary key.
What is a primary key?
It's actually 1 or more columns in your database that determine
or determines or determine all the remaining columns.
So, for, for instance, in this case,
can anyone suggest the primary key for this table?
Is there, is there a field or a column, one or more columns,
put, if, if we put them together, like, if,
is there one column or more than one column
that if joined would determine the remaining columns
in this, logically speaking?
SO, for example, if I put a first name,
if I put first name as primary key.
If I put, like, let's say David, will it determine the last name,
CD artist, CD title, date acquired?
Absolutely not.
Okay, I'll, I'll cover this in more detail a little later
but this is just us visiting this section.
So, I'm going to go ahead
and start inputting some data in, in this table.
Does anyone know what command I should be using for this
to actually input data inside the table, inside this table?
>> Insert.
>> Insert, correct.
So, insert, the, the syntax says insert into
and the name of the table.
And I go, I, I return to the next line
because it is not going to affect anything
but it looks better visually.
Insert into staff collections.
Now, I will need to enumerate the fields that I need to fill.
So, for example, if I only need, if I,
if I'm creating a record here and I only want
to add first name and last name and I do not know what CD they,
that person has or when it was acquired, I could just say
for example, Insert into staff collections first name,
last name and then values.
And I need to put the, the corresponding value in quotes.
So, the first value, the first in parenthesis will correspond
to the first field in those parenthesis
and the first set of parenthesis.
Let's put David Malan.
And this is, and the reason why I put a semi colon is
so I could put some more commands later
down in this page.
For example, I could do another insert here, et cetera.
So, let's see what happens when I do this.
Click on go.
And it says one row inserted.
If I click on staff collections, I can see that for first name,
there's a mention of David, last name, CD artist is null,
CD title is null, date acquired is null.
Let's go back here.
I'm going to actually delete.
Now, I would like to delete that record I just created.
How can I delete this.
Does anyone know what command I can use,
what sequel command I can use?
Delete. So, the answer is delete.
And so delete from, delete from users, I mean,
sorry, staff collections.
This, what this essentially says is delete all the records
from this table if I just say delete from
and the name of the table.
When I have a much bigger table and I would
like to delete some data, I can be a little bit more specific
and put a clause, like,
like where something equals something else and so forth.
But for, for this case, I can just use this
because I only have one record.
So, I will click on go.
And one row deleted and if I go, go to browse,
I really don't have anymore records in my table.
Okay, so I'm going to actually insert some data
into this table to make a, a point.
Insert into, let's enter data for 2 or 3 people.
Insert into staff collections.
First name, last name.
Now, I, I will be entering more information,
more than last time at least.
Date acquired.
Okay. Values.
So David Malan.
And for CD artist, I will put let's say Michael Jackson,
because I am familiar with him.
Thriller. And date acquired, dates should be put in quotes.
And one thing I would like to mention about dates,
the way date is laid out in my SQL is
that your typical date format is month, day, year,
but with my SQL, it's actually year, month, day.
So, when I enter the date here, I will need to put
in for example 2012 5 5.
So, this is our first record.
I'm going to copy this.
I'm going to cookie vet it, pretty much.
Past it again, paste it again.
I'll do one for me.
And let's just change this song here, let me, let's say Beat It.
And one more person.
Okay. So, now I'm ready to submit this.
I'm, my plan is to submit 3 records into the database.
I will click on Go.
And if everything was successful, and it was,
I should see, when I click on Browse,
I should see all the data that I just inputted.
Okay, can anyone see an inefficiency in this design?
>> The CD artist is repeated?
>> CD artist is repeated.
That's taking up space on, on disc storage.
That's, that's one thing.
Another, another disadvantage here is that let's suppose
that I have, I have 200 CDs and there are 10,000, there's,
there are 10,000 rows to this table.
If I have, like, let's say 10 CDs and I change my name.
This would entail my going into the database
and locating each single instance
where my name is mentioned and changing my name.
That's another disadvantage.
Lastly, let's suppose that I would like to delete, let's,
let's suppose that I would not,
I would like to not include myself in this table,
I would like to delete myself.
So, if I go ahead and delete this, what ends up happening is,
well, it's going to prompt me.
But what will end up happening in terms of information is
that I will also be deleting the CD artist and CD title,
leaving no mention of it.
So, those are at least 3 disadvantages we have
with this structure.
And so what we need to do is pursue a process called database
normalization and which, which I encourage you to research.
By normalizing the data, you're actually reducing
or eliminating redundancies and inefficiencies in terms
of the structure of your data.
What I will do now is I will delete everything I just did
and start from scratch to adhere to the database,
relational database normalization principles.
So, I click on sequel and I would
like to delete this table, okay?
How can I delete this table, what, what do I type now?
>> Drop.
>> Drop? Okay.
Drop. And then table.
Table name.
And I will click on go.
there we go, it's gone.
All right.
Okay, so how do we move forward from here?
What, how can I make this efficient?
>> You can make 2 tables.
>> Sorry, what was that?
>> Connect the tables.
Sorry, I can't hear you.
>> I said you could make 2 tables.
>> Make 2 tables?
Sure, sure.
Make 2 tables is a good recommendation.
So, what, what can I put in the first table?
What information, what type of information at least?
[ Inaudible audience comment ]
Okay. Yeah, so, the, the answer was information about staff.
And which is pretty good.
So, create, I will go ahead and issue a command.
Create table.
And let's call this table staff.
Okay, I need to cover first name.
Put varchar 30.
Last name.
Varchar 30 as well.
Am I missing anything here?
Do you got, do you see the need for anything additional in this?
>> Maybe we need a, a stop [inaudible] for the primary key?
>> Okay, a primary key.
Sure. Let's go ahead and put ID.
And this will be an integer.
We, we won't be having too many people here,
so let's just give it a length of 4,
which means it can accommodate up to 9,999.
That number.
Okay. So, I will delineate this with a semi colon
and I will create a new table as well.
So, staff is covered.
And for the purpose of this example,
what other information do I still need?
>> Collections.
>> Collections?
But what, what do collections entail?
In this case, we're collecting CDs, so.
>> Artist name and album name.
>> Exactly.
So, artist name, album name.
That's, so let's do artist varchar 30 and title.
Okay, and we still need a primary key here,
so let's do the same as we did earlier.
And ID int 4, comma.
And we can click on go.
Excuse me, sorry?
[ Inaudible audience comment ]
There is a good point, good observation.
Let's call this CDs and let's go ahead and click on go.
So, now I have 2 tables, one's called CDs
and one is called staff.
Although I, I included this to be a primary key,
it's still not a primary key yet.
So, I can do this by clicking on the primary key image or link
or I can go into sequel.
Let's create a primary key for CDs.
I can say alter table.
The name of the table, which in this case is CDs.
Add primary key.
And in parenthesis the name of the field,
the name of the column which I would like to have
as the primary key, which is ID.
And I want to do the same with the staff table.
Okay. I look at the structure now, and you can notice
that ID's underline which means that,
which signifies ID being at primary key.
And there is just one more thing I need to do this column.
Does anyone know what this thing is?
Or what I need to do left to the primary key?
Think of inserting new data and so forth.
>> Maybe not null or other information?
>> By default, the primary keys are not null
and they're indexed and unique as well.
So-
>> They should be auto incremented.
>> Auto incremented.
You had mentioned that.
That was the last on your list.
All right, so I will click on, on, on this
and actually change it.
Click on change.
Here I'm going to put none for default value
because it will not have any default values.
And I will check AI, A undercore I.
Click on save.
And so now each time a value, a new name, first name
and last name is inserted into this table, we will,
the system will generate a new ID based on the last used ID.
So, if the last ID was 1, the next ID will be 2.
If you have 3 people, so, with IDs 1, 2, and 3,
and you delete ID, the one with ID 3, the next,
the next inserted user will not have an ID of 3.
It will have an ID of 4.
So, it just checks for the last number used.
It does not care what, what's available,
what's actually been used in the table or not.
Now, I'll do the same thing now for the staff table.
I'll make sure it's auto incremented.
Okay. So, now we have a CDs table and a staff table.
How can we record what CDs a certain individual has,
a certain one of our, individual from our staff members?
Okay, so the answer is we need to create a new table
that associates a staff member with a certain CD
that the staff member owns.
And I will create a table and call it collections.
So, create table.
The table name is collections.
And let's see, let's see what columns this should have based
on efficiency and what we really need from this.
>> Staff ID.
>> Staff ID is one.
Actually, staff ID is an integer,
and it should be 4 digits long.
And what else?
>> CD, CD name.
>> Someone mentioned CD ID which is, which is correct.
CD ID, because CD ID will ultimately identify the CD
artist and title in that other table.
And these are what we call foreign keys.
Logically, these are foreign keys
but they are not technically foreign keys in that we need
to issue commands to make them foreign keys and that,
there's a whole discussion to that.
If you'd like to research more on this,
look up referential integrity and what that entails.
And our, and in my SQL we have 2,
we're going to cover basically 2 database engines.
One is my ISM and the other is NLDB.
NLDB INNO DB will support foreign key constraints
or foreign keys and my ISM will not.
Okay, so there is one more thing I need to put in here
which I had mentioned previously in the,
in the inefficient example.
There's, there's one more column.
>> Date acquired.
>> Date acquired?
That is correct.
So, data acquired which is essentially a date value,
date field.
Data type field.
Okay. Now, I will click on go.
Do, are we missing anything here?
So, I went ahead and created the table.
This is the structure of the table.
Where is it.
Collections.
And this table does not have a primary key.
So, what is the primary key for this table?
What field or fields determine the remainder of the table?
The remaining values?
[ Inaudible audience comment ]
Sure. So, in this case,
for example if we have a certain staff ID,
staff ID will not determine CD ID and date acquired.
CD ID will not determine staff ID and date acquired.
And date acquired will not determine staff ID and CD ID.
In this case, we need to,
to form what is called a composite primary key is
that which is formed by more than one,
at least 2, 2 or more fields.
So, the composite key here would be staff ID add CD ID.
So, given a certain staff member and given a certain CD
that one could purchase.
So, let's say I purchase a certain CD.
It's impossible for me to have purchased the CD
on 2 different dates at the same time.
Other, only, it might happen in a parallel universe but for now,
if I purchase a CD, if I, if I purchase a CD on a certain date,
that's, I, I can't have more than one of that date.
So, I will form these 2 as a composite key.
And the way to have 2 primary keys,
and I'm not sure you can do this here in, in my,
php my admin directly, or at least I don't know how to do it.
So, you go to the sequel command line.
Alter table collections.
Add primary key.
And then there'll be staff ID, comma CD ID.
And I click on go.
if I click on structure, you can see
that they're both underlined now,
which means that jointly they form a primary key.
Okay, onto the fun part, which is filling out the data.
I have already created some,
just before class I created some sequel commands
to insert some data.
Let's do this one.
Okay. So, let's run this.
We're going to insert 4, 4 CD, 4 titles for Michael Jackson.
Open up. Click on go.
Now, if I look into my CDs, I can see that it,
the system automatically generated the sequential IDs
for me.
And it inputted the artist and title.
And in case you didn't notice what the commands looked like,
I basically inserted into CDs artist and title.
I did not mention DI here
because the system will generate,
generate that for me automatically.
And then I give it the corresponding values.
So, now I'll do the same thing with, with the staff.
Except I haven't used G edit.
Let me see.
There we go.
Okay, so I'm inserting it to staff a first name,
the values for first name and last name for our staff members.
And click on go.
If I hit on browse, I can see that, well,
that we've mentioned this before.
I can see that all of our staff members are inside the table
now, each with a unique I, ID.
Okay, lastly, I will insert our collections.
Okay, so what this essentially does is it takes, if,
if you notice that I'm only using the staff ID and CD ID
for each entry and followed by the acquisition date,
I'm not using any first names, last names.
So your, for, for future applications that you write,
you will need to somehow know what ID the, you need,
that needs, that needs to be used in your queries
to be entered in, in your inserted queries and so forth.
Okay? So, now I have my collections table filled
with staff ID, CD ID and date acquired.
And the, let's suppose I delete this entry which is,
which could mean for example my ID could be 1.
If I delete this, this only means
that this entry is deleted.
My name remains intact here, and the CD,
the mention of the CD is still here and under CDs.
Okay, now I'd like to move
over to the last command I will cover tonight,
which is the select command.
The select command which is,
which essentially queries the database, asks the database
for the database management system for information from it.
And it is a very flexible command.
You can do so many things with it.
Let's, let's try something very simple.
Select star from staff.
And I will explain what this means.
If I click on go, it basically shows the entire table.
Select means query the database.
Star is all fields, all columns.
From what table would I like to get this information from,
the table called staff.
So, it showed me everything.
All the columns, all the rows for all the columns
from the table of staff.
Now, if I wanted just to see first name,
I could put select first name from staff.
And if I click on go, I will only see the first name here.
And if I put select first name as first name
to make it visually more appealing, from staff,
you can see here that the header,
header column is called first name
as I changed it in the query.
Similarly, I can put clauses in the select command.
So, let's say select first name from, as,
as first name from staff.
And let's put a clause in here where ID equals 4.
So, this should only fetch the row where ID,
the value of ID is equal to 4 from the staff table.
And, and I can see that first name Chris has an ID of 4.
And there are some more sophisticated functions you can
do with select but for the scope of this class,
I don't think we will be covering them,
like nested queries and other things, among other things.
You can do something like select star from staff where first name
like and percentage sign is wildcard for example.
Wildcard, wild card, let's say AL.
I want the AL in the middle of the word.
And this should give me something like this.
This is my name, because it has AL in it somewhere.
Okay, so now I have, I have 3 tables.
I have a CDs table, staff table and a collections table.
Let's try to make some meaningful queries given the
3 tables.
So, let's suppose I want to see,
I would like to see each staff member's CD collection or what,
what they've purchased or what, what they've acquired.
I will run a sequel, a select command.
Select first name.
Last name.
Artist. Title.
And date acquired.
From, okay, so from what tables do I need
to bring these results from?
Okay, staff, what else?
CDs? Any more?
>> Collections.
>> And collections.
Correct. One thing I would like to note here is
that typically you would use the table name dot column name.
Like here, I should, I should be using staff dot first name
but since first name is on, is unique only to that table, if I,
I don't have it in any other tables, since it is unique
to that table, I can use it
without using the actually table operator before it.
And lastly, I need to do one more thing, which is,
which is Chris will mention in more detail next,
the next session, next section, is a join, but I will do a join
in kind of a different way.
I will not using, I will not be using the keyword join.
So, here we need to actually join the tables in that I need
to make sure that the ID from the collection,
the CD ID from the collections table corresponds
from the CD ID from the CDs table.
So, I need to put something like this here
where collections, sorry.
Where collections dot CD ID equals to CDs dot ID.
And collections dot staff ID equals 2 staff dot ID.
And if I go ahead and click on go, now this makes,
this kind of has some value to us.
I can see people's names along with the artist,
title and the, when it was acquired.
Now, you can notice that these are not too friendly,
the headers, the header labels.
So, what I can do here is use again use as, as first name.
This is the actual label
that will appear in the resultant set.
Last name.
Artist, let's just keep it the same.
And date acquired as date acquired.
And you can see that this is reflected here.
It looks much more elegant.
Well I, I could've done the same thing with artist and title.
There's some more things that I could do with sequel with, with,
I mean with a select statement.
Let's say we want to find
out how many CDs each staff member has.
What I could here is let's delete everything.
I can select first name, last name
and sequel has some functions, like count max,
which finds the maximum value count which counts the,
the occurrences of a certain value.
So, here I would like to count the mentions
of collections dot staff ID.
Which logically speaking, if you're mentioned
in the collections table, it means that you have,
each time you're mentioned in the collections table,
it implies that you have at least that many number,
that many number of CDs.
So, if I'm mentioned 6 times in the collection table,
it means I, I have at least 6 CDs,
or I have 6 CDs based on that information.
And with the count function, you need to actually use a group
by clause at the very end.
Group by groups certain values together under the same,
so instead of having me mentioned 3 times, it would,
it would just mention me one time.
So, group by collections dot staff ID again.
And this may be a little bit beyond the scope,
scope of this course but I would like to show you what,
how useful this can, how, how useful sequel can be.
Just copy it.
So, I query this, and you can see here
that this tells us how many CDs each individual has.
Okay, does anyone have any questions on anything
that I mentioned so far from SQL
to database structures to the example?
>> You mentioned database normalization before.
Are there tools to take a blasé, dull database and normalize it?
>> Well, typically in, in the, the,
so the question is are there any tools out there
that take an existing data set or data structure
and I guess convert it or give suggestions to,
how to normalize the database.
I believe there are tools in,
in the actual relational database management systems
themselves that would make recommendations for you.
But ultimately, it's, it's, in the end, at the end of the day,
it's a, it's a computer thinking for you.
So, it's best that you do it yourself if you want
to apply the, the, the right tenets
of relational database design.
But yes, there are, there are systems
that will make recommendations for you
and might do it for you as well.
Any other questions?
[ Inaudible audience comment ]
>> Sorry, what was the last part of your question?
>> I mean, just the change in the [inaudible]
onto the database queried?
>> Oh, so the question is can, can we change the character set
of a database after it has been created.
Is that, that's your question?
>> Right.
>> The answer is yes.
>> Same character set?
>> Character set.
The collation, right?
The collation, like the, the character set.
Yes, the answer's yes.
I can go ahead right now and I'll show you how.
Let's suppose I go into structure.
And date acquired, where is it, collation.
Oh, okay, so this is date acquired.
I can go ahead and change it to ask a general,
I just picked one by, by randomly.
That's it.
And I changed it.
And if I look at the data, of collections,
it still looks the same.
So, unless you're, unless you're already in a character set
that doesn't have an equivalent for it, in,
in the other character set you're not going
to see a difference.
But if this were, for example, if I initially had my,
my database storing Chinese characters and then I decided
to change it to Latin, right?
You, you're probably going to see a bunch of question marks
down at, for, for the Chinese values once you convert it.
But it will not change anything otherwise.
I mean, it's fine, you can do it.
Any more questions?
Good. So, I guess we could, we can conclude this session,
and well, before I go actually, let me mention one thing
on the Power Point, on the presentation.
So, recommended things to research.
I do recommend, I know you're working hard
on your projects right now, but when you do have time,
I do recommend that you research certain things.
My sequel data types, because you will be working
with my sequel, my SQL, I keep saying it wrong.
My SQL in the next project, so I recommend that you visit those,
the different data types that it supports and what each one does.
Primary key and foreign key constraints.
Don't stress too much on the,
on the actual constraints themselves but it would be good
for you to understand what primary key and foreign key,
what each means, and so that you can conceptually apply them.
Even, albeit not in the technical sense,
as I mentioned earlier.
Like using through the, through the database engine.
You might not be able to,
depending on which database engine you choose to go with.
The third thing that I recommend you research is normalization.
And when you're, when you're doing your research
on normalization, again you don't want
to spend too much time on it, but enough time
to understand to, how you can reach the third normal form.
Because in normalization,
there are different norm, normal forms.
Again, this is beyond the scope of this class
but I think third normal form would suf, suffice for now.
Lastly, the sequel commands we mentioned today.
Select, put, put some focus on select, insert,
delete and update because these are the commands
that you'll be mostly using in your code
to manipulate the data.
It is very unlikely that you'll be using create,
alter, add and drop.
They probably won't be even used at all in your projects.
But these, these are the more prominent ones
that will be more used.
And as always, if you have any questions you can visit CS 75
dot net forward slash discuss
and we'll be there to assist you.
One more thing.
I know I didn't go through, I mentioned everything
with the exception of updates.
The update command, before I go.
So, the update command basically updates a target,
updates information for a target
of your choice inside a certain table.
Let's suppose that I would
like to update my name from the staff table.
So, I can say update, and the,
the syntax for this is update table name.
Set, I'll put it on the next, next line,
just so it looks good.
Set, and then the column name.
Let's say, let's, first name.
I would like to set first name to, I would to all,
my name to be in all, all caps.
Where I don't know my, I do not know what my ID is
but let's just say where first name like, it's, it looks like,
I mean, it starts with, or it has this,
the following sequence.
ALA and then whatever happens.
So, the, the percentage sign here could be zero
or many characters.
So, when I run this command,
what should happen, one row is affected.
What I should see happen here is, oh, it's under staff.
You can see now that my name is capitalized.
I could have essentially done where ID equals the 2
but I don't know my ID and I didn't feel like going back.
Now, does anyone have any questions about update
or would you like to see an example of, of an update,
insert, select, delete?
Just to shed light, some more light on this?
All good? Okay.
Okay. Any questions over there?
No, you're good?
Okay. So, I guess we can conclude this session, and next,
in our next section Chris Gerber will be covering some more
concepts in sequel such as indexes,
transactions, commits, so forth.
Thanks, thank you, everyone.
[ Applause ]