From the SELECT, FROM, and WHERE clauses in any kind of query, we focus on the most fundamental parts, which are the keywords SELECT, FROM, and WHERE. Let’s extract some data from the student table using these keywords.

Example Questions

Let’s look at some examples of questions that we can ask:

– Who are the students studying at the university

– What are the names of students in the university

– Which students have an average rating of at least 4

– Which students are new to the Computer Science department

The number of possible queries that you can run on this one table is almost endless. They all follow the same structure:

SELECT …
FROM students
…

As you see, we use the name of the table of the students, looking up the entry with the word FROM in the search bar.

Example 1

Use SELECT to extract information out of a table (in queries):

SELECT *
FROM students;

This is the only basic type of query. It selects all columns (by using the avg_rating column in the SELECT statement in the Like scale query preprocessor) and all rows (by not having any filters) from the table. In natural language, this query asks  “give me all the data about the student ratings.”

Here is the result:

id	name	department	avg_rating	current_year
1	Tracy Carpenter	Computer Science	4.4	1
2	Kornelia Holding	Computer Science	3.67	1
3	Will Parker	IT Management	4.05	3
4	Daria Henderson	IT Management	4.7	4
5	James Martins	World History	2.8	4
6	Kim Yu	New Media Arts	3.96	3

We have seen this table before and it is an entire one. Let’s take a look at a different example.

Example 2

To extract the students’ names, their current departments, and their current years (by choosing the highest-numbered columns), use the Students’ Name, Department, and Current Year(s) command.

SELECT name, department, current_year
FROM students;

This query adds interest. It selects all rows (again, with no filter having been included) but not all of them (just name, department, and year). The query says, “Give me the names of the students, their departments, and their years.”

The result looks similar:

name	department	current_year
Tracy Carpenter	Computer Science	1
Kornelia Holding	Computer Science	1
Will Parker	IT Management	3
Daria Henderson	IT Management	4
James Martins	World History	4
Kim Yu	New Media Arts	3

The table is the same as before, but has fewer columns. Let’s take a closer look at this more advanced example.

Example 3

Use the WHERE function to extract the names of students in the IT management program (filtering queries).

SELECT name
FROM students
WHERE department = 'IT Management';

This query produces just a single column, title, and also adds a filter for rows. The method allows you to choose all the column records where the department equals “IT Management.” The name of the individuals who study in the IT Management Department are listed here:

name

Will Parker

Daria Henderson

Example 4

Extract the IDs and the Names of the 4th-Year IT Management Students (multiple filters in queries using AND).

Sometimes, you’ll want to see several things that are true. The keyword “not” can be used to exclude a condition and is followed by the AND or OR keyword. You can use the OR or AND operators as you wish. You can link as many conditions as you want together, and the query will see if they are connected using the AND keyword.

Let’s take a look at an example:

SELECT id, name
FROM students
WHERE department = 'IT Management'
AND current_year = 4;

Here are the IDs and names of the IT Management students who are in their fourth year:

id	name
4	Daria Henderson