SQL, Arrays, Functions and Procedures

Sharing buttons:

hello there what we are looking at today

is SQL arrays functions and procedures

as part of OCR J 276 GCSE computer

science in specific we are going to be

looking at data storage inside SQL using

1 & 2 dimensional arrays and also having

a look at the differences and usages of

functions and procedures so there's the

learning outcomes for what we're looking

at today so we can get the pitch of what

we are doing so let's get started by

looking at some SQL so inside here we

have got an SQL database we've got this

tape thing here which is referred to as

a table and we are just going to call it

for now table you'll see how we're going

to use this name in a second

but we could be calling it profile we

could be calling it users we could be

calling it address information

what SQL is which stands for structured

query language structured query language

and it is a way of retrieving and

finding information from something like

this from a database and there's

different statements that you need to be

aware of and we're gonna look at some of

them now so we want to find some

information so we're going to write

something which is called a select

statement so with the Select statement

that can be used for finding particular

pieces of information you can be as

specific as you want so if we went for

select first name from table what that

would do is it would show me all the

information in the first name column for

everybody on the table so what that

means is we can see everybody's names

everyone's first names but that's it or

what we could write is select first name

Homer email , City from table and then

that would give us the first name the

email address and the city from the

table so what that means is we can take

multiple pieces of information that we

need so we're avoiding data duplication

data duplication is bad and what would

that allows us to do is then be more

specific with what we want to be looking

at imagine though I want to be a little

bit more specific with what I'm looking

at and what the information is imagine I

only want to find the people who get

live in New York so I only want to be

looking at these people here so what I'm

going to write is select first name

comma email from table where state

equals and why so what we can see from

here is we are selecting the first name

selecting the email address from the

table where the state here is equal to n

Wiese that means we're not worrying

about this piece of information here

we're not worrying about these two in

pieces of information here all we're

focusing is on where the states are

equal to and why and what that means

allows us to do is we can control our

information a lot better we've got for

example here as well in the phone field

two people with the phone number so

again we could go for select first name

from table where

phone is not null because what is not

not null means is that it's not an empty

piece of information it's not something

which is that one other thing that we

can do with a scroll which is quite

handy is we can do select star from

table and what this means is we're gonna

get absolutely everything from the table

every single piece of information

although we've got little bit formation

here not in the circle we're getting all

the information that's in the table we

can even be a bit more specific on there

we can select if I could write that be

amazing select star from table where

state is not equal to and why so then

that is only going to return this row

here this row here this row here so we

can use SQL to find specific pieces of

information really quickly really easily

so don't want you to do is show your

knowledge of SQL what it's used for how

can we use to retrieve information and

have a quick look at the SQL statements

you mentioned previously give the video

quick pause and then we'll move on so

we're going to look at now is arrays

arrays are very are arrays bad grammar

there from me and arrays are used for

storing information storing information

in lists so we can see here we've got

this example of an array we've got six

elements here arrays more after the nuts

start off with the first number being

zero because that is a true first row

number so we can see here we have got an

array of six elements they've all got

integers in here

and it's got six pieces of information

in here with a raise you can find them

you can search to them you can add to

them you can append to them they're

really really useful for data storage so

here we've got a nice little list and

that's all that's in the array this is

good because then if we're going to

search for example the number three we

could then search for the number three

if we wanted to

something onto the end we could do that

quite nice nice and happily and then

that become at one day array with seven

elements in nice easy quick to find data

search for data and you can do loads of

different funky stuff with them if we

look at two-dimensional arrays

originally with a one-dimensional array

we were just looking at with a single

list whereas with a two-dimensional


we've got columns and rows so now we can

create little tables of information

using lists so here we've got this array

again we can see like the previous one

is being declared as an integer so it's

only going to accept whole numbers and

we can see that we're going to be

getting three rows four columns and

again as we saw in the previous example

the first value here number one is in

location zero zero if we're going to be

looking at location two one that would

give us seven because we're going to go

to zero one two zero one takes us to

seven there so two-dimensional arrays

can be used really quickly and easily if

you're going to be creating like a board

game or a game of chess you'd probably

want to use a two-dimensional array to

store the positions of different pieces

and different moves so all I want you to

do now talk about arrays what they can

be used how cab data can be accessed and

their differences between one

dimensional and two dimensional arrays

give their video quick pause and then

we'll move on so finally we are going to

be looking at just the differences

between functions and procedures you'll

see functions and procedures in code

that you write in code other people

write and they are very very useful by

using a function it means you can write

a piece of code which can then be called

multiple times and be used multiple

times the same thing with a procedure so

straight away it's saving time for the

developer and then also it means by

using functions their codes more

maintainable because you can see where

things work in a particular function

rather than having to read the same

lines of code over and over again a

procedure performs a task so if there's

got a job to do that's going to be up to

the procedure where as a function

produces information a function can

return information as well so you might


some code which needs to work something

out and then delete give you an output

at the end that would be a function

procedures just perform some form of

tasks for you

you can pass parameters into functions

and procedures so if you've got a

variable called Bob you could pass that

variable Bob into a function or a

procedure and then those functions and

procedures can use those you need to

make sure that we're thinking about

scope when we're looking at functions

and procedures we've got local scope got

global scope if you create a variable

inside a function or procedure it's

going to be local to that function that

procedure so can only be used in there

if we're thinking about a function

that's going to return information you

need to make sure that you're defining

it as a global variable which means that

they can be used anywhere inside a

function outside of a function and can

be and modified if needed inside a

function or outside a function where as

parameters only performs a particular

task doesn't return anything so last

thing to do today share your knowledge

of function procedures what are they

used for

and will the difference between them I

hope this video has been helpful for you

and I see you in another video see you