Lab Overview

Scenario

The Canal House Books manager has approached you with a few requests for information. The first, provide a list of all loyalty customers’ last names and points using a .gov or .org email address, sorted by last name in ascending order and points in descending order. The second task is to provide a list of all titles with large title lengths.

Objectives

In this lab, you will:

Exercise 1: Select by Email Address and Sort

The manager has requested a list of loyalty members whose email addresses are .gov or .org based. Provide only the last name in ascending order and the loyalty points in descending order.

TODO

Create a custom query to select last names and loyalty points from the loyalty table while sorting them in ascending and descending order respectively.

Steps

  1. Ensure that you are connected to pub1 database from your MySQL Workbench. Select "Continue Anyway" when you receive a warning about incompatible versions.

  2. In Schemas section of the navigation pane in MySQL Workbench, right-click loyalty table and select Select Rows – Limit 1000. You can also click on the table symbol as shown in the diagram below to accomplish the same results:

    Untitled

  3. Type SELECT name FROM pub1.loyalty; in the loyalty table query pane to display the name field only. Click the lightning bolt to execute the query.

    Untitled

  4. Now edit the query using the substring_index function to select just the first name by typing substring_index(name, " ", -1). Click the Lightning bolt to run the query.

    Untitled

    Notes: The substring_index function allows you to select an index; specifically, the -1 index or the last item. In the following figure, the name is added to display first and last only for this step.

  5. Modify line 1 of the query to label the output of the substring_index function as last_name by typing as last_name after the function call.

    Untitled

  6. Replace name at the end of the SELECT line with points, email.