Lesson video

Hello and welcome to Computing.

My name is Mrs. Holborow.

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 how we can create new databases using SQL statements and how we can interrogate data held in a database using SQL.

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

This lesson is called "Data Management with SQL Statements".

And by the end of today's lesson, you'll be able to use SQL statements to manage data in a database, including inserting, updating, and deleting records.

Shall we make a start? We have one key word for today's lesson: Create.

Create: an SQL CREATE statement allows you to create a new table in a database.

Today's lesson is broken down into two sections.

We'll start by creating a new table using an SQL statement, and then we'll move on to interrogate the data in the database.

Let's make a start by creating a new table using an SQL statement.

A local leisure centre is in the process of building a new swimming pool.

You have been asked to set up a database for them that will help them manage their swimming lessons.

Time to check your understanding.

How would you make links between the following three tables? So we have tblCourses, we have tblLessons, and tblMembers.

Pause the video whilst you look carefully at the tables and decide how you may link them together.

Did you spot the links? Well done.

Let's have a look.

So CourseID in tblCourses is linked to CourseID in tblLessons, and MemberID in tblMembers is linked to MemberID in tblLessons.

What type of relationships have been made between the tables? Pause the video whilst you have a think.

That's right, a one-to-many relationship.

For example, a member can only appear in the Members table once, but they can take part in many lessons.

Which fields below are considered to be primary keys? Pause the video whilst you have a think.

Did you spot them? Well done.

CourseID, Lesson ID, and Member ID are primary keys in the tables.

Which fields below are considered to be foreign keys? Pause the video whilst you have a think.

Did you spot them? Well done.

CourseID and MemberID in tblLessons are foreign keys, which are linked to the primary keys in tblCourses and tblMembers.

The database for managing swimming lessons has been partially created for you.

The tblCourses and tblMembers tables have been created, but tblLessons hasn't been created yet.

SQL statements can be used to create new tables as well as updating or adding content in existing tables.

An SQL CREATE statement allows you to create a new table.

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

So CREATE TABLE followed by the table name, which we're going to specify.

And then in brackets we have the name of the fields that the table's going to contain.

So field1, field2, etc.

We also need to specify the data type for these fields.

So you can see we have (field1 datatype, field2 data type,) and so on.

This example shows the statements needed to set up a table to store the details of members for a youth club.

So on line one we have CREATE TABLE tblMembers, and then we open our bracket.

And then over lines two to six, we are specifying the fields.

So on line two, MembeID is going to have the data type CHAR and it's going to be limited to six characters in maximum length, and then we have a comma.

On line three, we have the field named Firstname, which is going to be VARCHAR, which is limited to 40 characters, and we've said NOT NULL here.

We're gonna have a look at what these mean in a bit more detail in a minute.

And then we have Surname, again, similar data type and length to Firstname.

On line five, we have Phone_Number, which is VARCHAR(15), and then on line six, we are specifying the PRIMARY KEY for the table.

So PRIMARY KEY (Member ID).

And then we've got another closed bracket which will close the CREATE statement, and then a semicolon because we've come to the end of our SQL statement.

SQL statements can be written on a single line, but they're much easier to comprehend when split across several lines as shown in the example above.

When using multiple lines, you must not forget to specify the parentheses, brackets, and commas.

So each field definition must be separated from the next by a comma.

It is conventional for the first line to include the opening parenthesis and the last line to include the matching closing parenthesis and the semicolon.

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

So here are some examples of MySQL data types: INT, which is used to hold an integer; FLOAT, which is used to hold a floating point number or a number with a decimal point; CHAR, and then in the brackets, the x represents the fixed length of the string.

This can contain letters, numbers, and special characters.

VARCHAR(x): this is a string of variable length.

The x can be set by the user for the maximum size.

And then DATE: a date value in the format YYYY-MM-DD.

And then TIME in the format hh:mm:ss.

Time to check your understanding.

Why is the MemberID set as the CHAR data type here or the character data type? Pause the video whilst you have a think.

Did you get it right? It's because the CHAR data type allows you to set a fixed length for the field.

In this example, the MemberID will be a set length to ensure that all MemberID's follow the same format.

Constraints specify rules that the data needs to follow to be accepted in a table.

Typically, these constraints are set up as part of a CREATE TABLE SQL statement.

And here are some common constraints: The NOT NULL constraint is used to guarantee that a column won't accept a null value or be left blank.

The PRIMARY KEY constraint ensures that every row of a column has a unique value that isn't null.

So we can't add duplicates in the field MemberID here.

A primary key can be defined by stating the key words PRIMARY KEY followed by the field name in brackets.

So here, the MemberID is being specified as the primary key in this table.

A foreign key is a value that exists as a primary key in another table.

In this example, the certificate table has three foreign keys.

Here are the statements to set up the certificate table.

So you can see on lines seven and eight, we're specifying the foreign keys: FOREIGN KEY (MemberID) REFERENCES member (MemberID).

And then on line eight: FOREIGN KEY (Activity_Code) REFERENCES activity (Activity_Code).

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

A partially complete SQL script to create tblLessons has been written below for you.

Complete the script by adding the appropriate data types and complete any other missing parts of the scripts.

Pause the video whilst you complete the task.

How did you get on? Did you manage to complete the SQL script? Well done.

Let's have a look at the example together.

So on line two, the data type for LessonID should be INTEGER.

On line three, the data type for CourseID should be INTEGER too, and we're setting that as NOT NULL.

On line four, MemberID is going to be INTEGER too, and we're setting that as NOT NULL.

On line five, we're setting the PRIMARY KEY for the table as LessonID.

And then we are setting two foreign keys.

So on line six: FOREIGN KEY (CourseID) REFERENCES tblCourses (CourseID).

And then on line seven: FOREIGN KEY (MemberID) REFERENCES tblMembers (MemberID).

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

So, we've created a new table using an SQL statement.

We are now gonna move on to interrogate the data held in a database.

Imagine you manage a library's database.

How do you add a new book, remove old ones, or fix errors? Maybe pause the video whilst you have a think.

Sofia's got a great response.

Sofia says you can use SQL statements to do all of those things.

You are right, Sofia.

Common SQL operations include: searching and retrieving records, inserting new records, deleting records, and updating records.

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

The basic syntax for an SQL INSERT statement is: INSERT INTO table, followed by the field names in brackets, separated by commas.

And then on line two: VALUES, with the values, again, stored in brackets and separated by commas.

An SQL UPDATE statement allows you to modify records.

The basic syntax for an SQL UPDATE statement is: UPDATE table, where table is the table name.

Line two: SET field1 = value1, field2 = value2, and so on.

And then on line three: WHERE criteria.

So, what criteria are you looking for to do the update? You can use multiple criteria in your WHERE clause by using AND and OR operators.

The AND operator or the OR operator specify the additional condition without specifying the WHERE keyword again.

So let's have a look at that on line three: WHERE criteria OR criteria AND criteria.

Notice we don't repeat the WHERE keyword.

Note: In SQL statements, the AND operator takes precedence over the SQL Server OR operator, just like a multiplication operation takes precedence over an addition operation.

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

The basic syntax for the SQL DELETE statement is: DELETE FROM table WHERE criteria.

An SQL SELECT statement allows you to retrieve data from a database.

The basic syntax for an SQL SELECT statement is: SELECT field1, field2, so on, where field1 and field2 are replaced by the field names.

And then on line two: FROM table.

So you are specifying the table that you are going to select the fields from.

Rather than specifying the fields you want to be returned in specific search results, it is possible to select all available fields by using SELECT * or SELECT asterisk.

The following query can be used to return all of the fields and data in the table tblProducts So SELECT * FROM tblProducts.

Time to check your understanding.

How many records would the following query find? Is it A, one, B, two, C, three, or D, four? Look carefully at the SQL statement and the table of data provided.

Pause the video whilst you have a think.

Did you select two? Well done.

There are two courses in the table where the start date is equal to the 4th of 10th, 2024.

Which SQL statement is used to select all records from the table tblCourses? Is it A, SELECT FROM tblCourses; or B, SELECT * FROM tblCourses;? Pause the video whilst you have a think.

Did you select B? Well done.

Remember, the SELECT * will return all fields and data from a specified table.

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

For part one, I'd like you to write an SQL script that will enter some test data into tblLessons.

The leisure centre has asked you to use the following as test data.

So we've been given some CourseIDs and some MemberIDs as test data.

Pause the video whilst you complete the task.

How did you get on? Did you manage to insert some test data? So, on line one we have INSERT INTO tblLessons or table Lessons, and then we have the field names we are using.

So, (CourseID, MemberID).

And then we have our VALUES statement, so, VALUES.

And then we're putting in the sample data.

So, in brackets we have each record.

So, (3,15), and then comma to separate each record.

So, the next one is (3,18), and so on.

For part two, the following query has been run: So we have SELECT * FROM tableMembers WHERE "City" = "Newcastle".

I'd like you to explain what each line of the query does.

Pause the video whilst you complete the task.

How did you get on? Did you manage to explain each line of the query? Well done.

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

So, SELECT *, this selects or shows all of the fields.

FROM tblMembers, this line finds the fields in the line above from the Members table, tblMembers.

And then the last line: WHERE "City" = "Newcastle", this adds the criterion.

So, only show the members that live in the city of Newcastle.

So, where the field city matches Newcastle.

For part three, I'd like you to write an SQL query that will display all classes that have more than six sessions.

You only need to display the Level and Session fields.

Pause the video whilst you complete the task.

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

Here's a sample answer.

So, on line one: SELECT Level, Sessions, 'cause they're the two fields that we need.

On line two: FROM tblCourses.

And then on line three, we have our WHERE statement, which is WHERE "Sessions" > 6; And remember the semicolon on the end.

For part four, two of the members have left the area and have notified the leisure centre that they no longer wish to have their details stored on the system.

Write an SQL script that will delete the following two members from the tblMembers.

So, Abla Baroni, which has a Member ID of 46, and Nadim Abe, who has the Member ID 8.

Pause the video whilst you complete the task.

How did you get on? Let's have a look at the statement together.

So, on line one, we have DELETE FROM tblMembers WHERE Firstname = "Abla" AND Surname = "Baroni" OR Firstname = "Nadim" AND Surname = "Abe;" Notice the use of AND and OR in this statement.

We only write the WHERE SQL command once.

For part five, all of the members that have been taking part in the Ducklings class have now passed and are ready to progress into the next course, which is Dippers.

Write a script that moves all members currently taking the Ducklings lesson, which is the Course ID 1, into the Dippers course, which is CourseID 2.

Pause the video whilst you complete the task.

How did you get on? We are using an UPDATE statement here.

So, on line one: UPDATE tblLessons.

On line two: SET CourseID = 2 WHERE CourseID = 1.

Okay, we've come to the end of today's lesson, "Data Management with SQL Statements".

And you've done a great job, so well done.

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

SQL is used to manage data with relational databases.

SQL allows users to interact with and interrogate data through standardised commands.

The SQL CREATE command can be used to create a new table.

SQL statements must follow specific syntax rules to execute correctly.

Missing commas, quotes, or incorrect keywords can cause errors.

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

Bye.