Now that we have inserted data into the students
, courses
, and students_courses
tables, let us look at the different mechanisms of retrieving data, we will be using the SELECT
command to retrieve the data. The SELECT
statement would expect two things as a minimum, the first would be what to retrieve and the second would be where to retrieve it. The simplest SELECT
command would be to retrieve all the student records from the students table:
In this query, we are using *
to retrieve the data for all the columns from the students table, this is not a preferred method of retrieving data. The preferred method for data retrieval is by mentioning the individual columns separated by a comma (,
) after the SELECT
clause:
In this query, we are selecting the student_id
, first_name
, and last_name
columns from the students
table. As we are not filtering the data yet, SELECT
statements would return every student record that is in the students table. We can use the LIMIT
clause to retrieve a certain number of records:
In this query, we are retrieving the data from the students
table and we are retrieving the student_id
, first_name
, and last_name
columns; however, rather than retrieving all the rows, we are only retrieving a single row. To retrieve the next row, we could still use the limit, but we would use LIMIT
clause accompanied by the OFFSET
clause. The OFFSET
clause determines the starting point as to where the records should start from, while the LIMIT
clause determines the number of records that would be retrieved.