Hello, my name is Mrs. Holborow, and welcome to Computing.

I'm so pleased you've decided to join me for the lesson today.

In today's lesson, we're going to be looking at the function of SQL and learning how to use SQL statements to insert and update data in a database.

Welcome to today's lesson from the unit Databases and SQL.

This lesson is called SQL fundamentals, and by the end of today's lesson, you'll be able to create SQL queries to insert and update data stored in a database.

Shall we make a start? We will be exploring these keywords throughout today's lesson.

SQL.

SQL, Structured Query Language, a decorative language designed for managing data held in a relational database management system.

INSERT.

INSERT, an SQL INSERT statement allows you to add data to pre-existing tables.

UPDATE.

UPDATE, an SQL UPDATE statement allows you to modify records.

Look out for these keywords throughout today's lesson.

Today's lesson is broken down into three parts.

We'll start by describing the function of SQL.

We'll then move on to insert new data using SQL statements and then we'll finish by updating data using an SQL statement.

Let's make a start by describing the function of SQL.

SQL stands for Structured Query Language.

It's a language used to communicate with a database.

You can use SQL to manipulate databases and retrieve records.

On the right-hand side of the screen, you can see some example SQL statements in the code block.

Aisha says, "Why do we need SQL?" Maybe pause the video here and have a think.

When you shop for items online, you'll typically be searching a database that uses SQL.

Your search terms will be added to SQL statements, and the relevant records will be retrieved from the database.

Can you think of any other examples of where you may have seen SQL statements? Common SQL operations include searching and retrieving records, inserting new records, deleting records, and updating records.

Time to check your understanding.

What does SQL stand for? Is it A, Structured Question Language, B, Sequential Query Logic, C, Structured Query Language, or D, System Query Line? Pause the video whilst you have a think of your answer.

Did you select C? Well done.

SQL stands for Structured Query Language.

SQL is widely used.

It's a standard language that comes in many varieties.

The most popular are MySQL and SQLite.

Fields in a database, as with variables in a programming language, have data types associated with them.

Data types specify the type of value the field can hold.

MySQL and SQLite provide different data types.

Let's have a look at them now.

Here are some MySQL data type examples.

So INT is an integer value.

FLOAT is a floating-point number, so a number with a decimal place.

VARCHAR is a string of variable length.

The x can be set by the user to set a maximum size.

DATE, a date value in the format year, year, year, year, hyphen, month, month, hyphen, day, day.

TIME, a time value in the format hour, hour, minute, minute, second, second.

Here are some examples of SQL data types.

INTEGER, an integer value.

REAL, a floating-point number, and TEXT, a string of variable length.

So note there are differences between the two.

Time to check your understanding.

Which SQL data type is used to store floating-point numbers? Is it A, INT, B, REAL, or C, FLOAT? Pause the video here whilst you have a think.

Did you select B? Well done.

REAL is used for floating-point numbers in SQLite.

FLOAT is used for floating-point numbers in MySQL.

It's important to note that there can be significant differences between some database implementations of SQL.

Okay, we are moving on to our first activity of today's lesson.

For part one, I'd like you to describe what SQL is and what it is used for.

For part two, a doctor's surgery stores data on patients.

Some of the fields from tblPatients, or table patients, are listed below.

Allocate an appropriate data type for each of the fields.

Note, if you use VARCHAR(x), you should suggest a maximum field size.

For example, VARCHAR(50) would allow a maximum of 50 characters to be entered.

So, the table below shows you the field titles.

We've got PatientID, Firstname, Surname, Date_of_birth, and Height in centimetres.

Provide the MySQL data type and the SQLite data type.

Pause the video here whilst you have a go at the tasks.

How did you get on with the tasks? Did you manage to describe what SQL is? Well done.

Let's have a look at a sample answer together.

SQL, which stands for Structured Query Language, is a programming language used to manage and interact with databases.

SQR is used to perform tasks such as retrieving data, for example, finding all users who scored more than 80 points in a game, adding new data like saving a new user's details when they sign up, updating, such as changing a password or username, and deleting data, like removing an account that is no longer needed.

Remember, if you need to pause your video here and make any amendments to your answer, you can do that now.

For the second part of task A, you were asked to look at the doctor's surgery data.

Some of the fields from the table patients were listed below and you were asked to allocate an appropriate data type for each of the fields.

So for PatientID, the MySQL data type would be INT and the SQLite data type would be INTEGER.

For Firstname, we've got MySQL data type as VARCHAR with a text limit of 20 characters.

And for SQLite, the data type of TEXT.

Similar for Surname, but this time we've given slightly more characters for the surname in MySQL.

And then in SQLite it's TEXT again.

Date_of_birth in MySQL, we can use the DATE data type.

But in SQLite, we don't have a DATE data type so we're going to use TEXT there.

And then lastly, for the Height in centimetres, in MySQL, we are going to use the FLOAT data type.

And in SQLite that is the REAL data type.

Remember the difference between the two there.

Okay, so we've described the function of SQL.

We're now going to move on to insert data using SQL statements.

An SQL INSERT statement allows you to add data into existing tables.

The basic syntax for an SQL INSERT statement is shown below.

So we have INSERT INTO table where would we would insert the name of the table, and then in brackets we have the field separated by commas.

On line two, we have the values that we're going to insert.

So again, these are stored inside brackets with a comma in between each one.

The statement allows you to specify one, the table where you want to insert data into, two, the fields to use, and three, which values to enter into those fields.

So let's have a look at this example.

So we have INSERT INTO tblFriends, or table friends, open brackets, firstname, surname, close brackets.

On line two, we have values, open brackets, and then we have Rachel, and then we have comma, and then we have Green, and then we're closing our brackets, and then we have a comma.

On line three, we have the next lot of data.

So again, inside brackets we have the values.

So we have Phoebe, comma, Buffay, closed brackets, another comma, so we're putting a comma in between each item, and then open our brackets again.

And then we have Chandler, comma, Bing, closed brackets.

Notice the semicolon at the end to indicate we finished that statement.

Also notice that because we're adding strings using this SQL statement, we're using the speech marks around the bits of text.

Time to check your understanding.

Which SQL command is used to add new data to the table? Is it A, UPDATE, B, ADD, C INSERT INTO, or D, SELECT? Pause the video whilst you have a think.

Did you select C? Well done.

INSERT INTO is the SQL command which is used to add new data into a table.

When you are inserting records, you must be careful to conform to any constraints that have been specified in the table design.

There may be fields that must not be left blank or that can only accept certain values.

A foreign key constraint may mean that another table will be checked for a matching primary key value.

You must also be aware of data types.

In this example, the activity_code and description are text fields.

So the values must be enclosed in quote marks, a bit like our names from the previous example.

assessment_fee is numeric, so must not be enclosed in quotes.

It is possible to add more than one record at a time by using a comma to separate each new set of values.

So you can see at the end of each line, on line three, four, and five, we have a comma before going down to the next line and inserting the new record.

True or false? It is not possible to add more than one record at a time using an SQL INSERT statement.

Pause the video here whilst you have a think.

Did you select false? Well done.

Why is that false? It is possible to add more than one record at a time, but remember you need to use a comma to separate each new set of values.

Okay, we're moving on to our second task of today's lesson.

You're doing a fantastic job so far, so well done.

The music download site has a database to hold customer download details.

The database is split into three tables, each with a primary key which is highlighted.

The tables have relationships, which are shown in the diagram.

So, on the left-hand side, we have the table called tblTracks.

In the middle we have the tblDownloads, and on the right-hand side we have the table called Members.

TrackID, which is the primary key in tblTracks, has a one-to-many relationship with the TrackID which is stored in tblDownloads.

MemberID, which is the primary key in tblMembers, has a one-to-many relationship with the MemberID in tblDownloads.

As the music downloads business grows, the database is in need of some administration.

Note, if you want to have a go at this practically, this database is provides as an additional resource for this lesson, but you will need to have access to a DBMS to perform the tasks.

Recent download data has just been passed to you.

So we have three records.

Consider which table you need to insert the data into.

Does all of the data provided above need to be entered? Write a script that will perform this action.

Pause the video whilst you complete the task.

How did you get on? Did you manage to write a script to insert the data? Well done.

So, you can see here we have an example script.

On line one, we have INSERT INTO tblDownloads.

Did you select the right table? And then in brackets, we have the fields that we need to insert data into.

So TrackID, MemberID, Date, and Time.

On line two, we have the first set of values that we're going to insert.

So we open our brackets, we have 11, and then comma, 53, comma, and then the date, and then comma, and then the time.

Close our brackets, and then we have another comma so that we can move on to the next record we want to insert.

So, first name, surname, and title are not contained in the tblDownloads, so we didn't need to insert that data into this table.

Remember, if you need to make any corrections to your statements, you can pause the video and do that now.

Okay, so we've described the function of SQL and we've inserted new data using SQL statements.

Let's now move on to update data using SQL statements.

An SQL UPDATE statement allows you to modify records.

The basic syntax for an SQL statement is shown below.

So, on line one we have UPDATE table where we'll specify the table we're updating.

On line two, we say SET field1, where the field is going to be replaced by the field name, is equal to, and then we have value1 and then we have comma, field2 = value2, field3 = value3, and so on.

Then, on line three, we have WHERE criteria.

Let's have a look at how this works in a bit more detail.

To update data in a table, you're required to specify the following.

One, the table which you intend to update, which is line one here.

Two, the new value that you want to set, and three, where a condition is being met.

So here's an example.

UPDATE tblEmployees.

SET password to equals, and then in our speech marks we've got the new password.

And then on line three, WHERE employeeid = 8.

So this is going to update employee eight's password.

Time to check your understanding.

What would be the outcome of running the following SQL statement on the data below? Look carefully at the SQL statement and the data and then have a go answering the question.

Did you spot it? Well done.

The SQL statement will change the mileage for both Ford Pumas.

So they will both end up having the mileage of 35,121.

That probably isn't what we wanted to do with the SQL statement.

How could you amend the SQL statement to only update the first Ford Puma in the table? The one that's highlighted.

Pause the video here whilst you have a think.

The model of the car isn't unique, so it means that maybe multiple records could be updated.

However, the vehicle registration is unique to each car, so that will only update the record you want it to.

So we've changed the SQL statement now, which says UPDATE vehicle, SET mileage, so those two lines are exactly the same, but our WHERE statement is different.

So WHERE vehicle_registration = 'DH19NBD'.

This will only update the first Ford Puma in the table.

Existing records can also be deleted from a table using an SQL DELETE statement.

The basic syntax for the DELETE statement is shown below.

So on line one, DELETE FROM table where we'll replace table with the table name, and then on line two, WHERE criteria.

Let's have a look at this in action.

To delete data from a table, you're required to specify the following.

One, the table which you intend to delete from, and two where a condition is being met.

So let's have a look at the example.

On line one, we have DELETE FROM tblStudents, and then on line two, we have WHERE date_of_birth, or date_of_birth, is less than 31/08/2002.

So this will delete any student whose date of birth is less than the 31st of August, 2002.

Okay, we're moving on to our last set of tasks for today's lesson, and you've done a fantastic job so far, so well done.

The artist Angry Pete has notified us that after a career break, he's returned a different man and would now like to be known as Happy Pete.

He has requested that the data in our database is adjusted accordingly.

Write a script that will perform this action.

Pause the video whilst you complete the task.

How did you get on? Did you manage to write your script? Well done.

Here's an example answer for you.

So on line one, we have UPDATE tblTracks.

On line two, SET Artist to, and then we have the equal sign, and then we have "Happy Pete" in speech marks.

And then on line three, we have WHERE Artist = And then we have "Angry Pete" in speech marks.

Don't forget the semicolon at the end of the statement.

For part two, we've just lost our contract with the band The Feast and can no longer offer their track for our members to download.

Find a way to remove the feast from our database.

Note that this requires some problem solving skills.

For part three, by solving the problem this way, can you think of why this might be problematic for the data in the database? Pause the video whilst you have a go at the tasks.

How did you get on? Did you manage to think about a solution? Well done.

Let's have a look at a sample answer together.

We could delete all of the records with the artist ID from the tblDownloads first, before deleting the artist from the tblArtists.

So, it's important to do one action before the other here, which is why we said you required some problem solving skills.

By solving the problem this way, you were asked to think of why this might be problematic for the data in the database.

If you remove the records from tblDownloads, you are deleting the fact that the users downloaded the track in the first place.

This would cause problems if the user or company wanted to find out which tracks a member has downloaded.

It's important to be really careful and consider the actions if you are deleting data from a database.

Okay, we've come to the end of today's lesson, SQL fundamentals, and you've done a great job, so well done.

Let's summarise what we've learned together during today's lesson.

SQL stands for Structured Query Language.

It is a language used to communicate with a database.

You can use SQL to manipulate databases and retrieve records.

An SQL INSERT statement allows you to add data into pre-existing tables.

An SQL update statement allows you to modify records.

Existing records can be deleted from a table by using an SQL DELETE statement.

I hope you've enjoyed today's lesson, and I hope you join me again soon.

Bye.