Creating basic queries

We have already introduced the SELECT command to view the records entered into a table. Using the * wildcard we able to select all field columns of data to be shown. The most basic query used for showing table records is:

SELECT * FROM table_name;

SELECT and WHERE

We can use the WHERE clause to limit the results of a SELECT query. Lets say we want to view the first_name and last_name fields from the student table and only want to limit the results to students that are 21 years old. We could write the following query:

SELECT first_name, last_name
FROM student
WHERE age = 21;

Comparison operators

We have used the = sign in many of the queries written in the course. We also can use the following operators with the WHERE clause:

Comparison Operator Description
= Equal
<> Not Equal
!= Not Equal
> Greater Than
>= Greater Than or Equal
< Less Than
<= Less Than or Equal

AND and OR

Using the logical operators AND and OR we can add complexity to our query statements. When using the keyword AND both conditions must be true. When using the keyword OR either condition can be true. For example, say we want to show students who are either younger than 21 OR are greater than 65. We would write:

SELECT first_name, last_name
FROM student
WHERE age < 21 OR age > 65;

Now lets examine the above code using AND instead of OR. What results would show?

SELECT first_name, last_name
FROM student
WHERE age < 21 AND age > 65;

In this case no results would show. A student can not be both younger than 21 AND older than 65 at the same time.

Here is another example of the AND operator. In this example the database will only return students who are both under 21 and also live in New York City.

SELECT first_name, last_name
FROM student
WHERE age < 21 AND city = "New York City";

ORDER BY

When creating a query we can sort the results on the screen by using the ORDER BY statement. This does not change the order in which the data is stored in the table but rather just how it is displayed on the screen. The default order is ascending (ASC) but you can also use the keyword DESC to sort descending.  You can also sort by more than one field by adding fields to the query.

Below is an example of sorting records from the student table ascending by first_name:

SELECT *
FROM student
ORDER BY first_name;

Here is the same query but sorting the results in descending order

SELECT *
FROM student
ORDER BY first_name DESC;

If we wanted to first sort by first_name descending and then age ascending we could write this query (note: ASC is not needed):

SELECT *
FROM student
ORDER BY first_name DESC, age ASC;

Lesson Activities

  1. Let’s view the records in the program table.
    Click to see code
    SELECT * FROM program;
  2. Now write a query to only show the program_name and description fields from the program table.
    Click to see code
    SELECT program_name, description
    FROM program;
  3. Now let’s view the records in the faculty table and list them ascending by last name.
    Click to see code
    SELECT *
    FROM faculty
    ORDER BY last_name;
  4. Now let’s view the records in the faculty table, showing only the first_name and last_name fields and list them ascending by last name.
    Click to see code
    SELECT first_name, last_name
    FROM faculty
    ORDER BY last_name;
  5. Now let’s view the records in the student table, showing the first_name, last_name, and birth_date. List them descending by birth_date.
    Click to see code
    SELECT first_name, last_name, birth_date
    FROM student
    ORDER BY birth_date DESC;
  6. Now let’s view the students who are male. Show only the first_name and last_name fields.
    Click to see code
    SELECT first_name, last_name
    FROM student
    WHERE sex = "M";
  7. Now let’s view the students who are female. Show only the first_name and last_name fields and sort the results by last_name.
    Click to see code
    SELECT first_name, last_name
    FROM student
    WHERE sex = "F"
    ORDER BY last_name;

Questions