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 how we can retrieve data from one or more tables using SQL.

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

This lesson is called SQL searches.

And by the end of today's lesson you will be able to use SQL to retrieve data from a relational database.

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

Let's take a look at them now.

SELECT.

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

SELECT *.

SELECT *: the SQL SELECT * allows you to return all of the columns from a database table.

Look out for these keywords throughout today's lesson.

Today's lesson is broken down into two sections.

We'll start by looking at how we can retrieve data from a table and then we'll move on to how we can retrieve data from more than one table.

Let's make a start by retrieving data from a table.

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.

So here's some example of some SQL code.

We are selecting item, price, and description from a table called Products where the key word is equal to "toy." Common SQL operations include: searching and retrieving records, inserting new records, deleting records, and updating records.

SQL uses recognisable commands that are similar to the English language.

So select, from and where.

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

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

So on line one we have SELECT and then we have the names of the field we want to select.

So in here we've just put field1, field2.

Notice that they're separated by commas and you can have as many as you need to in this SELECT statement.

And then on line two we have FROM table where table will be replaced with the table name.

Let's have a look at this in action.

The SQL keyword SELECT is followed by a list of field names that you want to display.

So in this example we have item, price, and description.

The SQL keyword FROM is followed by the name of the table that you are selecting the fields from.

So in this case, the table is tblProducts or table products.

So we are selecting the item, price and description columns FROM the tblProducts table, WHERE the criteria has been met.

So in this case, the criteria is that the key word must be equal to the word "toy." Time to check your understanding.

Label the database with the labels provided.

So the labels are: record, fields, table and primary key.

Pause the video here whilst you complete the task.

How did you get on? Did you manage to label the database correctly? Well done.

Let's have a look at the answers.

So the table is the complete table where the arrow is currently pointing to that top row at the moment.

The primary key is the TrackID.

So in this case, 1, 2, 3, and 4 are all primary keys.

The fields are the columns in the table.

So, TrackID, Title, Artist and Genre.

And then record is the last row which is highlighted in the table.

So we have TrackID 4, the title is Us, the artist is The Feast and the genre is RnB.

Did you get all of those right? I'm sure you did.

Well done.

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

The following query can be used to return all of the fields and the data that is in the table, tblProducts.

So on line one, rather than specifying the field names, we have SELECT *.

And then on line two, we have the same line we had before, FROM tblProducts.

Alex says, "The SELECT * statement is much easier.

I may just use this all the time." Hmm, Alex, I wonder if that's a good idea.

Perhaps pause your video here and consider Alex's statement.

Ah, Sofia's got a good point.

"You should only use the SELECT * query when all the fields are needed.

Including unnecessary data can be frustrating for the person viewing and working with the results." Time to check your understanding.

What does the * or asterisk symbol mean in the SELECT statement? Is it A: add a new column, B: count all rows, C: select every column, or D: delete data? Pause the video whilst you have a think about your answer.

Did you select C? Well done.

The * symbol in a SELECT statement will select every column.

Comparison operators can be used in SQL statements.

Here are a list of some common comparison operators.

So we have equal to, greater than and less than, greater than or equal to, less than or equal to, and not equal to.

So notice this is slightly different from what you may have experienced in programming languages like Python.

Logical operators can also be used in SQL statements.

Here are a list of common logical operators.

So the AND logical operator is TRUE if both conditions are TRUE.

Here's an example.

SELECT * from tblTracks WHERE Genre = "Pop" AND Artist = "The Springs"; So both of those conditions must be true in this statement.

BETWEEN, this is TRUE if the range is inclusive or within the comparisons.

So the example is SELECT * FROM tblDownloads WHERE Time BETWEEN "06:00" AND "07:00"; and then OR, this is TRUE if any of the conditions are TRUE.

So SELECT * FROM tblTracks WHERE Genre = "Pop" OR Genre = "Rock".

So if either of these conditions are TRUE, it will return the tracks from the tblTracks.

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

A database has been used to record data about the downloads of tracks from a music website.

tblTracks stores information about all of the tracks available for download.

A sample of data is shown below.

So we have the fields TrackID, Title, Artist and Genre.

And at the moment, you can see four records with the primary key 1, 2, 3, and 4.

For part one, write an SQL statement to retrieve how many tracks in the database have the genre Rock.

For part two, write an SQL statement to retrieve how many tracks in the database are created by the artist, "A Box of Spoons." And then for part three, write an SQL statement to retrieve how many tracks in the database have a TrackID greater than 30 and are of the Soul genre.

If you have access to a database management system, this database is provided as an additional resource for the lesson.

Pause the video here whilst you complete the tasks.

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

Let's have a look at some solutions together.

So for part one you were asked to write an SQL statement to retrieve how many tracks in the database have the genre Rock.

On line one we have SELECT *, on line two FROM tblTracks.

And then on line three we have the criteria.

So WHERE Genre = "Rock";.

Notice we've put the word Rock inside speech marks 'cause we are searching for text here.

And remember the semicolon at the end.

For part two you were asked to write an SQL statement to retrieve how many tracks in the database are created by the artist "A Box of Spoons." So similar to the last one, we have SELECT * on line one and FROM tblTracks on line two.

But this time our criteria is different on line three.

So we have WHERE Artist = "A Box of Spoons";.

For part three you were asked to write an SQL statement to retrieve how many tracks in the database have a TrackID greater than 30 and are of the Soul genre.

So same two lines again from our other examples.

So SELECT * FROM tblTracks.

This time though, on line three, our criteria or our WHERE statement is WHERE TrackID > 30 AND, so notice our logical operator here, so AND Genre = "Soul";.

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

So we've retrieved data from a single table.

We are now going to move on to see if we can retrieve data from more than one table.

Your last activity used searches that only looked at one table at a time.

Many queries will require data from more than one table.

In a multi-table query, there must be a common field between each pair of tables.

For example, a primary key and foreign key pair.

The basic syntax for an SQL statement that joins two tables is as follows.

So SELECT field1, field2 and so on, depending on how many fields you need.

Remember these are separated by commas.

And then on the second line, FROM table1, table2, where table1 and table2 will be replaced with the table names.

When retrieving data from more than one table, you need to add in three extra pieces of code to make links between the tables.

Let's have a look at what those bits of code are now.

Firstly you need to specify the table that the field names belong to in the SELECT block.

So on line one we have SELECT tblMembers.

Surname, so the field Surname from tblMembers, tblDownloads.

DownlID or download ID.

So we are taking download ID from table downloads.

So we add the name of the table with a full stop before the field name.

Secondly, you need to list each table that you wish to retrieve data from in the FROM block.

So on line two, we have FROM tblMembers, tblDownloads.

So there's the two tables that we are selecting the data from.

Lastly, you need to state how the tables are joined together.

So on line three we have WHERE tblMembers.

MemberID = tblDownloads.

MemberID.

Time to check your understanding.

Why is MemberID included twice in the WHERE statement? Maybe pause the video whilst you have a think.

The primary key from the tblMembers table is linked to the foreign key in the tblDownloads table.

You can then add your search conditions using the AND operator.

So on line four we have AND tblMembers.

Surname = "Bibi"; Remember we've enclosed this in speech marks 'cause we're searching for text and we finished our SQL statement with a semicolon.

Time to check your understanding.

What data do you think will be retrieved when this code is executed? Pause the video whilst you have a think.

Did you spot it? That's right, the download IDs for each download where the member's surname is Bibi.

So here's a sample of the data that would've been retrieved.

You can change the order of the retrieved records by sorting the data into ascending or descending order.

We do this by using the ORDER BY statement.

So on line three, you can see we have ORDER BY Surname.

And then on the left hand side we have ASC, which stands for ascending.

And on the right hand side we have DESC, which stands for descending.

So ascending and descending.

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

The music downloads database contains more than one table.

The structure of the database is shown.

So we have three tables, tblTracks, tblDownloads and tblMembers.

Remember if you have access to a database management system, this database is provided as an additional resource for this lesson.

For part one, I'd like you to write a SELECT statement that will return how many tracks Percy Winn has downloaded.

For part two, I'd like you to write a SELECT statement that will return how many Pop tracks were downloaded in the year 2012.

And then for part three, I'd like you to write a SELECT statement that will return the title of the first track downloaded in 2014.

Pause the video here whilst you complete the tasks.

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

Let's have a look at some sample answers together.

So for part one you were asked to write a SELECT statement that would return how many tracks Percy Winn has downloaded.

Remember we are selecting from multiple tables here.

So on line one we have SELECT tblMembers.

Firstname, tblMembers.

Surname, tblTracks.

Title.

And then on our line three, we are selecting the tables we are going to get the data from.

So FROM tblMembers, tblDownloads, tblTracks.

On line four we have our WHERE statement.

So WHERE tblMembers.

MemberID = tblDownloads.

MemberID.

So we are showing how the tables are linked.

And then we have our criteria on lines five, six, and seven.

So AND tblTracks.

TrackID = tblDownloads.

TrackID AND tblMembers.

Firstname = "Percy" AND tblMembers.

Surname = "Winn"; For part two, you were asked to write a SELECT statement that will return how many Pop tracks were downloaded in the year 2012.

So on line one we have our SELECT statement.

So SELECT tblTracks.

Genre FROM tblMembers, tblDownloads, tblTracks.

Remember we then show how the tables are linked.

So WHERE tblMembers.

MemberID = tblDownloads.

MemberID AND tblTracks.

TrackID = tblDownloads.

TrackID.

Then we have our criteria.

So AND tblTracks Genre = "Pop" AND tblDownloads.

Date BETWEEN "2012-01-01" AND "2012-12-31"; Look carefully at the date format.

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

For part three, you were asked to write a SELECT statement that will return the title of the first track downloaded in 2014.

On line one we select the fields.

So we have SELECT tblTracks.

Title, tblDownloads.

Date.

Then on line two we specify the tables.

So as we've done before, FROM tblMembers, tblDownloads, tblTracks.

And then on line three, WHERE tblMembers.

MemberID = tblDownloads.

MemberID AND tblTracks.

TrackID = tblDownloads.

TrackID.

And then we are getting the date range.

So AND tblDownloads.

Date BETWEEN "2014-01-01" AND "2014-12-31" And then because we want to know the first track, we're going to use the ORDER BY statement.

So on line seven we have ORDER BY tblDownloads.

Date ASC; That will put the first track in 2014 at the top of our list.

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

Let's summarise what we've learned in this lesson.

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.

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

The SQL SELECT * statement allows you to return all of the data and fields from a table.

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

Bye.