Now that our yoga database is populated with data, let’s work on queries.

The database schema is shown below for easy reference (Note that the highlighted fields in bright yellow are from the adjustments that I made to the original set of data before I populated the database. It won’t affect anything here.):

Ok, so we’re just going to go over a bunch of examples of different queries from our yoga database. Whenever we use the SELECT statement, we are requesting data. This request can be very broad, as well as very specific. The first example below is asking for all the rows of data for every column in the yoga_teacher table.

The next example is asking for just the distinct sex within the yoga_teacher table, which will return M and F. It will not return any duplicates.

For the next statement, we will get the data for just the distinct class names that are in the class_offered table. There are several classes that are repeated in the table, so this is a good way to see a list of each of the different classes offered only.

This statement below will pull up all the data within the studio_id column only from the yoga_teacher table.

Now, we’ll see some examples using the ORDER BY keyword. This example below is asking to get all the data from the f_name, l_name, studio_id columns within the yoga_teacher table, but we want the returned data to be in ascending order by the f_name column (so all the first names will be listed out in alphabetical order, and the rows will be arranged accordingly).

This next example makes use of Alias for the f_name and l_name columns. Using an alias helps make the column names more readable. This SELECT statement also sets a condition to narrow down the data to retrieve for only those with a studio_id of 1. Then it wants that data to be organized in descending order of the f_name column.

We can also ORDER BY multiple columns. It will do so in the order of the columns listed out separated by commas. Each of the columns will indicate either ascending or descending. So below, we are asking for the hourly_rate and l_name columns from the yoga_teacher table. However, we want to first order the rows of data retrieved by the hourly rates, and then secondarily order by last names from that arrangement.

We can also indicate the number of rows of data to be returned for our queries by using the LIMIT clause. In the example below, we are requesting for all the columns of data from the yoga_teacher table, ordered by the teacher_id column, but we only want one row of data returned. The way that we request the order of information will affect which single row of data is returned. If we indicate that we want the data in descending order, we will get a different result.

We can offset the row/rows returned as well. To do so, after we type LIMIT, we would state the number that we want to offset by, followed by a comma, then state the number of rows we are requesting. In the example below, we are offsetting by 3 and requesting 1 row of data. So from all the data that would be returned, we will only retrieve the 4th row of that data since we are skipping the first 3 rows with the offset.

Now, we will see some examples that include functions.

Below is an example of concatenation. We are asking for the first name and last name columns, but we want to combine the columns into one. We do this by typing CONCAT, then in parenthesis we include the columns that we want to combine. In order to add a space between the first and last names, we can separate the column names by commas and add a space in single quotes, which will create that spacing when the data is returned. This example also gives the combined column name an alias of ‘Full Name’.

Now, we have some basic examples of the minimum and maximum functions. First, we are querying the minimum value from the total_sales column in the taught_by table. We do so by typing MIN, then in parenthesis we include the column name to run the function. So, the result here would be the lowest amount for the total sales.

Next is the example for the maximum function. This is pretty much the same scenario as above, except we just use MAX instead of MIN. This will result in the return of the highest value for the total sales.

Now, we will see some examples of the COUNT function. The below query is requesting for a count of all the rows from the teacher_id column in the yoga_teacher table.

This next example is expanding on the above query by adding a condition to have the count pertain just to the teacher_id rows for female teachers.

Now, we are getting even more elaborate with our request in this next example by also bringing in an additional condition to narrow down that count even further for teacher_id rows to female teachers who have a birth date that is greater than or equal to August 4, 1995.

This next example uses a GROUP BY statement. We are requesting the count for the sex column in the yoga_teacher table, but we want the count to be grouped by each sex. So, we want the count to be categorized by how many male and how many female yoga teachers there are, instead of having one overall total count.

We can also use the average function to find the average of amounts in a specified column. This example below is asking to get the average of the amounts within the hourly_rate column in the yoga_teacher table, but only for the female teachers.

Below, is a query for the taught_by table to retrieve the average value from the total_sales column.

The remaining examples go over the sum function, which will total up the indicated column for each query. So here, we have a request to add all the rates in the hourly_rate column, but we want to group the sum by each of the different rates.

This example below will add the grand total all the values from the total_sales column in the taught_by table.

This final example will return the sum of the total_sales column grouped by each teacher id. So, this will reveal the total sales numbers achieved for every individual yoga teacher.

Ok yay! So, that’s a wrap for all the examples of queries that I have for now. Within those examples, we got the chance to incorporate the ORDER BY keyword, LIMIT clause, GROUP BY statement, and some of the common functions.

I look forward to continue learning and sharing with you.

Thanks for hanging with me, friends :).

Here is a video reviewing the the query examples from this post. (It may be helpful to see the actual results from running each query):