6  Structured Query Language (SQL)

6.1 Learning objectives

Concepts

  • Structured Query Language (SQL)
  • Database views

Practical skills

  • Writing SQL queries to extract data from a database (Lab #3)
  • Creating database views in a RDBMS (team assignment #3)

6.2 What is SQL?

SQL is a language with a relatively simple and intuitive syntax that uses written statements (or queries) to interact with the RDBMS to perform various operations such as creating databases, tables and other objects, inserting or updating data in tables and, of course, retrieve data.

6.3 Syntax

A SQL statement typically looks like this.

SELECT [TableName].[FieldName] 
FROM [DatabaseName].[TableName]
JOIN [DatabaseName].[TableName] ON [Condition]
WHERE [Condition]
GROUP BY [list of fields that form groups]
ORDER BY [field(s) to use for ordering, either ascending (ASC) or descending (DESC)];

In English, the statement SELECTs some data FROM some table JOINed with some other table ON some condition, and WHERE the final joined tables meet some other condition. The aggregated values (if used) are GROUPed BY the other fields selected. Finally, the results are ORDERed BY one of the field values in ASCending or DESCending order.

Here’s an example of an actual SQL statement, which calculates the average price of gummies with different consistencies, and order the results from highest average price to lowest.

SELECT gummies.consistency, price_denominators.denominator, avg(gummies.price)
FROM gummybox_sales.gummies
JOIN gummybox_sales.price_denominators ON gummybox_sales.price_denominators.id = gummybox_sales.gummies.price_denominator_id
WHERE gummies.colour = 'blue'
GROUP BY gummies.consistency, price_denominators.denominator
ORDER BY avg(gummies.price) DESC;
4 records
consistency denominator avg
hard per unit 0.50
soft per unit 0.25
soft per gram 0.05
chewy per unit 0.03

Not all of these parts are necessary for all statements. What you need to include depends on what you are trying to achieve. However, you will rarely have any other components than these in your statements. You can write your statements on a single line or spread them on multiple lines like I do. However, you must always respect the order of the elements. Your statement will not work if you put the GROUP BY part before the WHERE part, for instance. Also, try to remember to end your statements with a semi-colon.

Finally, keep in mind that the default setting for PostgreSQL is case sensitive, so “banana”, “BANANA”, and “BaNaNa” are not the same. However, this does not apply to the names of tables, functions, and operators. For my examples, I capitalize the functions and operators so that they can be more easily distinguished from the other elements of the statements, but in your SQL statements, select or SELECT would be interpreted the same way and produce the same results.

In the next section, I provide examples of the different functions and operators that you may need to use in your statements. These are far from exhaustive but should cover 95% of the basic queries that users make when working with relational databases. Administrators of databases will typically use many more functions, but that is beyond the scope of this course.

6.4 The gummybox_sales ERD

6.5 Retrieve data

6.5.1 SELECT

You can use the select function to retrieve data from a table.

6.5.1.1 Select an entire table

To retrieve all the data in a table with SELECT * FROM [table].

SELECT *
FROM gummybox_sales.gummies
Displaying records 1 - 10
id name description consistency colour flavour flavour_type_id price price_denominator_id
1 Bada Booms An explosion of taste soft blue strawberry 1 0.05 1
2 Vinson Drops Soup. Now in gummy form chewy brown soup 2 0.20 2
3 Salmoniakki Salmon flavored salted licquorice hard black salmon liquorice 2 0.50 2
4 Froggurt Frog meat flavored yogurt gummy soft green frog 1 0.50 2
5 Garum Gummae A gummy take on a Roman flavor stretchy black anchovie 3 1.00 2
6 Apple Bottom Jeans Gummies shaped like jeans with apple flavor chewy blue apple 1 0.03 2
7 Dog Residue Imitates dog dropping with imitation dog dropping flavor hard brown dog 4 0.01 2
8 Kitty Litter Ever wanted to eat the drop box? hard white ammonia 4 0.01 2
9 Frostbite Imitates how it feels to have frostbite hard blue minty 5 0.50 2
10 Matchsticks Good for eating; not to ignite hard red phosphorus 3 0.80 2

6.5.1.2 Select specific fields from a table

When you only want to retrieve a few fields from a table, you need to specify the names of the fields. In the previous example, I retrieved information about gummies, excluding a few fields, like their id and their price.

Note: when listing fields, the table name is only required when the same field name is ambiguous because it exists in more than one table included in your query.

SELECT 
  gummies.name, 
  gummies.description, 
  gummies.consistency, 
  gummies.colour
FROM gummies;
Displaying records 1 - 10
name description consistency colour
Bada Booms An explosion of taste soft blue
Vinson Drops Soup. Now in gummy form chewy brown
Salmoniakki Salmon flavored salted licquorice hard black
Froggurt Frog meat flavored yogurt gummy soft green
Garum Gummae A gummy take on a Roman flavor stretchy black
Apple Bottom Jeans Gummies shaped like jeans with apple flavor chewy blue
Dog Residue Imitates dog dropping with imitation dog dropping flavor hard brown
Kitty Litter Ever wanted to eat the drop box? hard white
Frostbite Imitates how it feels to have frostbite hard blue
Matchsticks Good for eating; not to ignite hard red

6.5.2 SELECT DISTINCT

The SELECT DISTINCT statement eliminate duplicate rows from the returned table. So if we want to retrieve a list of the gummy consistencies available, we would not want “hard” to be repeated in the list for every hard gummy available. We can do that with a SELECT DISTINCT statement.

SELECT DISTINCT gummies.consistency 
FROM gummies;
4 records
consistency
hard
soft
stretchy
chewy

6.5.3 WHERE

Perhaps you do not want to retrieve all the records of a table, but only a subset that meets a certain condition. Let’s try, for instance, to retrieve all the gummies that are blue from gummy table.

SELECT 
  gummies.name, 
  gummies.description, 
  gummies.colour 
FROM gummies
WHERE gummies.colour = 'blue';
4 records
name description colour
Bada Booms An explosion of taste blue
Apple Bottom Jeans Gummies shaped like jeans with apple flavor blue
Frostbite Imitates how it feels to have frostbite blue
gummyX Strangeness blue

6.5.4 ORDER BY

The order by function lets you choose the order in which you would like the data to be displayed. Here we order the gummies in descending alphabetical order:

SELECT gummies.name, 
       gummies.description, 
       gummies.colour 
FROM gummies
ORDER BY gummies.name DESC;
Displaying records 1 - 10
name description colour
Vinson Drops Soup. Now in gummy form brown
Tempest Get schwasted tan
Salmoniakki Salmon flavored salted licquorice black
Pain Made with Veal; comes with an mp4 of the hand-craftsmanship of orphans making them violet
Matchsticks Good for eating; not to ignite red
Kitty Litter Ever wanted to eat the drop box? white
Just Strawberry It’s strawberry, what more do you want to know?! pink
gummyX Strangeness blue
Gummy to Go Make your friends pee their pants with a gummy laced with diuretics yellow
Garum Gummae A gummy take on a Roman flavor black

And here we order the gummies in ascending alphabetical order:

SELECT gummies.name, 
       gummies.description, 
       gummies.colour 
FROM gummies
ORDER BY gummies.name ASC; -- Note that ASC is the default and doesn't need to be specified.
Displaying records 1 - 10
name description colour
Apple Bottom Jeans Gummies shaped like jeans with apple flavor blue
Bada Booms An explosion of taste blue
Dog Residue Imitates dog dropping with imitation dog dropping flavor brown
Eye for an Eye Blinding flavor white
Froggurt Frog meat flavored yogurt gummy green
Frostbite Imitates how it feels to have frostbite blue
Garum Gummae A gummy take on a Roman flavor black
Gummy to Go Make your friends pee their pants with a gummy laced with diuretics yellow
gummyX Strangeness blue
Just Strawberry It’s strawberry, what more do you want to know?! pink

6.6 Operators

6.6.1 Arithmetic operators

Operator Description
+ Addition
- Subtraction
/ Division
* Multiplication

The following statement returns the results of several mathematical operations.

SELECT 1+1, 
       2/4,
       2*3,
       8-4
1 records
?column? ?column?..2 ?column?..3 ?column?..4
2 0 6 4

Let’s try applying an arithmetic operator to a column and retrieve the price per 100 gummies instead of the unitary price.

SELECT 
  gummies.name, 
  gummies.price*100
FROM gummies
WHERE price_denominator_id = 2;
Displaying records 1 - 10
name ?column?
Vinson Drops 20
Salmoniakki 50
Froggurt 50
Garum Gummae 100
Apple Bottom Jeans 3
Dog Residue 1
Kitty Litter 1
Frostbite 50
Matchsticks 80
Gummy to Go 40

While the next example wouldn’t make much sense in practice, it shows that you can use arithmetic operators with two columns.

SELECT 
  gummies.name, 
  gummies.price+gummies.price
FROM gummies;
Displaying records 1 - 10
name ?column?
Bada Booms 0.10
Vinson Drops 0.40
Salmoniakki 1.00
Froggurt 1.00
Garum Gummae 2.00
Apple Bottom Jeans 0.06
Dog Residue 0.02
Kitty Litter 0.02
Frostbite 1.00
Matchsticks 1.60

6.6.2 Comparison operators

The comparison operators are used to compare values. The result of a comparison is always TRUE (1) or FALSE (0).

Operator Description
= Equal
> Greater than
< Less than
>= Greater than or equal to
<= Less than or equal to
<> or != Not equal to
IN Checks if value is contained in a list of values
BETWEEN … AND … Checks if value is between two values

Here is a simple example that shows how the comparison operators work. The returned value for each column is 1 if the statement is true, and 0 if it’s false.

SELECT 'banana' = 'apple'
1 records
?column?
FALSE 
SELECT 10 < 0
1 records
?column?
FALSE 
SELECT 'b' BETWEEN 'a' AND 'c'
1 records
?column?
TRUE 
SELECT  5 >= 5
1 records
?column?
TRUE 
SELECT 10 < 0
1 records
?column?
FALSE 
SELECT 8 in (1,4,6,8)
1 records
?column?
TRUE

As we have already seen when we introduced the WHERE function, the comparison operators are usually used when checking if some condition is met.

Here is an example in which we retrieve only the Canadian addresses from our address table.

SELECT *
FROM addresses
WHERE addresses.country = 'Canada';
Displaying records 1 - 10
id address1 address2 address3 city province country postal_code
1 55 ice cream crescent NA Candyland NS Canada H2T 1H3
2 66 vanilla avenue NA Candyland NS Canada B3L 1A6
3 77 chocolate avenue NA Candyland NS Canada B3K 2A7
4 88 red velvet avenue NA Candyland NS Canada B3G 4G6
5 99 mint chocolate chip lane NA Candyland NS Canada B4R 5F2
6 11 candycane street NA Candyland NS Canada B7R 4H3
7 22 brownie court NA Candyland NS Canada B3J 1D3
8 33 sour patch drive NA Candyland NS Canada B3E 0I9
9 44 cheesecake boulevard NA Candyland NS Canada B2D 5T6
11 100 swedish berry cul-de-sac NA Candyland NS Canada B3D 9G0

Here is another example in which we retrieve the name and description of gummies that are hard or stretchy.

SELECT 
  gummies.name, 
  gummies.description
FROM gummies
WHERE gummies.consistency IN ('stretchy','hard');
8 records
name description
Salmoniakki Salmon flavored salted licquorice
Garum Gummae A gummy take on a Roman flavor
Dog Residue Imitates dog dropping with imitation dog dropping flavor
Kitty Litter Ever wanted to eat the drop box?
Frostbite Imitates how it feels to have frostbite
Matchsticks Good for eating; not to ignite
Pain Made with Veal; comes with an mp4 of the hand-craftsmanship of orphans making them
Tempest Get schwasted

6.6.3 Logical (boolean) operators

The logical operators are used to combine multiple conditions that must all be true for the data to be returned.

Operators Description
AND Returns TRUE when all conditions are TRUE. Returns FALSE otherwise
OR Returns TRUE when at least one of multiple conditions is TRUE. Returns FALSE otherwise
NOT Returns TRUE when the operand is FALSE, and returns FALSE when the operand is TRUE.

We can test the boolean operators with the following statement.

SELECT TRUE, 
       FALSE, 
       NOT FALSE, 
       NOT TRUE, 
       TRUE AND FALSE, 
       TRUE OR FALSE;
1 records
bool bool..2 ?column? ?column?..4 ?column?..5 ?column?..6
TRUE FALSE TRUE FALSE FALSE TRUE

Let’s now try this in a query and retrieve all gummies from the gummybox_sales database that are blue and AND a semi-hard consistency.

SELECT gummies.id, 
       gummies.name, 
       gummies.description, 
       gummies.consistency, 
       gummies.colour
FROM gummies 
WHERE gummies.colour = 'blue' AND gummies.consistency = 'hard';
1 records
id name description consistency colour
9 Frostbite Imitates how it feels to have frostbite hard blue

6.6.4 Aggregate functions

Function Description
MIN() Returns the minimum value of a column.
MAX() Returns the maximum value of column.
SUM() Returns the sum of all values in a column.
AVG() Returns the average value of a column.
COUNT() Returns the number of rows.

Let’s try the aggregate operators by querying the gummybox_sales database to retrieve the number of gummies in the catalogue and their minimum, maximum, and average prices.

SELECT 
  COUNT(*), 
  MIN(gummies.price), 
  MAX(gummies.price), 
  AVG(gummies.price)
FROM gummies;
1 records
count min max avg
16 0.01 1 0.434375

We can see that there are 15 entries in the gummies table, with prices ranging from 0.01$ to 1.00$ and an average price of 0.45$.

Often times, you will want to retrieve these aggregate values for different subsets of records (e.g. to perform group comparisons). This is where the GROUP BY function comes into play. In fact, you must include a GROUP BY in your statement whenever you use an aggregate operator, unless all of your returned values are aggregates (as in the example above). Let’s write a query that returns the number of records, and the minimum, maximum, and average price for gummies of different consistency.

SELECT 
  gummies.consistency, 
  COUNT(*), 
  MIN(gummies.price), 
  MAX(gummies.price), 
  AVG(gummies.price)  
FROM gummies
GROUP BY gummies.consistency;
4 records
consistency count min max avg
hard 7 0.01 0.8 0.4600000
soft 6 0.05 0.9 0.4166667
stretchy 1 1.00 1.0 1.0000000
chewy 2 0.03 0.2 0.1150000

It’s important to know that PostgreSQL will not return an error message if you forget the GROUP BY part of your statement. But will instead provide an erroneous result: the first value for gummy.consistency and the aggregate value for all of the records in the table. So remember to always examine the results of your queries (no matter what they are) to see if they are consistent with your expectations, and to not simply trust that all is fine and that the query worked and did what you wanted it to do because there is no error or warning message.

6.7 Combining (joining) tables

6.7.1 JOIN (INNER JOIN)

The JOIN returns only records for which the matching condition is TRUE. Here is an example, where I bring together the order and the gummy_order to see what gummies are part of each order, and in what quantity.

SELECT *
FROM orders
JOIN gummies_orders ON gummies_orders.order_id = orders.id;
Displaying records 1 - 10
id customer_id address_id date gummy_id order_id quantity
1 2 3 1995-08-08 1 1 5
2 3 4 1996-05-30 2 2 2
3 4 5 1994-11-05 3 3 7
4 5 6 1998-09-02 4 4 25
5 6 7 1993-10-15 5 5 90
7 8 9 2002-01-15 7 7 17
8 9 10 1987-09-12 8 8 34
9 10 1 1999-06-30 9 9 76
1 2 3 1995-08-08 10 1 12
2 3 4 1996-05-30 11 2 96

6.7.2 LEFT JOIN

The LEFT JOIN function returns all records from the original table and only records from the joined table for which the matching condition is TRUE. This can be useful if I want to retrieve, for example, a list of all the gummies and the orders in which they were included, but keep in my list the gummies that were part of 0 orders.

SELECT 
  gummies.name,
  gummies_orders.order_id
FROM gummies
LEFT JOIN gummies_orders ON gummies_orders.gummy_id = gummies.id;
Displaying records 1 - 10
name order_id
Bada Booms 1
Vinson Drops 2
Salmoniakki 3
Froggurt 4
Garum Gummae 5
Dog Residue 7
Kitty Litter 8
Frostbite 9
Matchsticks 1
Gummy to Go 2

6.7.3 RIGHT JOIN

The RIGHT JOIN function is the same as the LEFT JOIN function, but instead returns all records from the joined table and only records from the original table for which the matching condition is TRUE.

Note: You rarely use right join in practice. It is usually a better idea to use a LEFT JOIN, since most RIGHT JOIN statements can be reformulated as LEFT JOIN statements.

SELECT gummies_orders.order_id,
       gummies.name
FROM gummies_orders
RIGHT JOIN gummies ON gummies.id = gummies_orders.gummy_id;

6.7.4 UNION

While the JOIN functions are used to combine fields from different tables horizontally, the UNION function vertically combines two tables. Note that this requires that the two combined tables have the same structure (same fields). In our database. Let’s use the UNION function to make a list of the names and descriptions of all the products (gummies and boxes) in our inventory.

SELECT gummies.name,
       gummies.description
FROM gummies
UNION
SELECT boxes.name,
       boxes.description
FROM boxes;
Displaying records 1 - 10
name description
Matchsticks Good for eating; not to ignite
Salty Boiz A collection of salty treats to dry your tongue
Salmoniakki Salmon flavored salted licquorice
Vinson Drops Soup. Now in gummy form
Mouth Tingles An explosion of flavours
Earthy Fusion Fruity and tootie and earthy delights
gummyX Strangeness
Apple Bottom Jeans Gummies shaped like jeans with apple flavor
Frostbite Imitates how it feels to have frostbite
Froggurt Frog meat flavored yogurt gummy

As you might have noticed from the previous example, the “peak-a-chew” and “crocodile gumdee” gummies are available in both stores. The UNION function automatically removes duplicates and returns only distinct records. If you would like to keep the duplicate rows, this can be achieved with the UNION ALL function.

6.8 Aliases (AS)

Aliases are used to rename things within your SQL statements. You can change the name of a field or table. You can even change the data type of a field. Unless used in a CREATE or ALTER statement the Aliases do not modify your database. They are simply used for the specific statement that you are executing. We will focus here on one function of aliases: setting column names for the output of your SQL queries.

6.8.1 Setting column names

A typical use of aliases is to choose a column name that is more appropriate for a display that is intended for the user. This is especially useful when you use arithmetic or aggregate operators, for instance. Here’s an example where we get the list of gummies and the price included in the order that has id 1.

SELECT gummies_orders.order_id, 
  gummies.name AS Name,
  gummies_orders.quantity AS Quantity,
  gummies_orders.quantity * gummies.price AS Price
FROM gummies_orders 
JOIN gummies ON gummies.id = gummies_orders.gummy_id
WHERE gummies_orders.order_id = 1;
2 records
order_id name quantity price
1 Bada Booms 5 0.25
1 Matchsticks 12 9.60

6.8.2 Setting table names

Giving an alias to tables in your query can make them easier to write (and read).

SELECT go.order_id, 
  g.name AS Name,
  go.quantity AS Quantity,
  go.quantity * g.price AS Price
FROM gummies_orders AS go 
JOIN gummies as g ON g.id = go.gummy_id
WHERE go.order_id = 1;
2 records
order_id name quantity price
1 Bada Booms 5 0.25
1 Matchsticks 12 9.60

6.9 Database views

What are views? Technically they are not much more than stored query (in some DBMS, they views are actually called “stored queries”) with an alias.

In practice, and for the purpose of this course, we will think of views as tables that your users may wish or need to see (or “view”) to obtain information or perform a certain task.

6.9.1 Single table view

Creating views in PostgreSQL is pretty simple. Here is an example of a single table view that simply provides the list of customers and their email address.

CREATE VIEW customers_emails AS
SELECT DISTINCT first_name, last_name, email FROM customers;

So now we can view the list of emails by retrieving records from our view.

SELECT * FROM customers_emails;
Displaying records 1 - 10
first_name last_name email
Basil Exposition happygolucky@britishintelligence.uk
Number 2 twentytwentyvision@evil.org
Austin Powers groovybaby@ministryofdefence.uk
Mr. Bigglesworth destroyerofworlds@evil.org
Mini Me ilovechocolate@evil.org
Dr. Evil douglaspowers@evil.org
Nigel Powers easypeasylemonsqueezy@britishintelligence.uk
Scott Evil daddywasntthere@evil.org
Gold Member 24k@rollerdisco.com
Foxxy Cleopatra sugar@wholelottawoman.com

6.9.2 Multitable view

You can also create views that combine data from multiple tables using the JOIN or UNION function. Here is an example where we use fields from the orders, customers, and addresses tables to create a view called delivery_addresses.

CREATE VIEW delivery_addresses AS
SELECT DISTINCT 
  orders.id, 
  customers.first_name, 
  customers.last_name, 
  addresses.address1, 
  addresses.address2, 
  addresses.address3, 
  addresses.city, 
  addresses.province, 
  addresses.country, 
  addresses.postal_code
FROM orders
JOIN customers ON customers.id = orders.customer_id
JOIN addresses ON addresses.id = orders.address_id;

Then I can simply request the delivery_addresses for all orders like this:

SELECT * 
FROM delivery_addresses;
Displaying records 1 - 10
id first_name last_name address1 address2 address3 city province country postal_code
5 Number 2 22 brownie court NA Candyland NS Canada B3J 1D3
2 Foxxy Cleopatra 88 red velvet avenue NA Candyland NS Canada B3G 4G6
7 Gold Member 44 cheesecake boulevard NA Candyland NS Canada B2D 5T6
8 Mr. Bigglesworth 55 snowcone crossing NA Candyland NS Canada B4F 5T0
1 Basil Exposition 77 chocolate avenue NA Candyland NS Canada B3K 2A7
6 Scott Evil 33 sour patch drive NA Candyland NS Canada B3E 0I9
10 Austin Powers 66 vanilla avenue NA Candyland NS Canada B3L 1A6
3 Dr. Evil 99 mint chocolate chip lane NA Candyland NS Canada B4R 5F2
4 Mini Me 11 candycane street NA Candyland NS Canada B7R 4H3
9 Nigel Powers 55 ice cream crescent NA Candyland NS Canada H2T 1H3

6.10 Putting it all together

Let us now combine several of the functions that we learned to create a view that provides the average price of hard gummies. We will build the statement one piece at a time.

6.10.1 Step 1 - starting with the gummy table

SELECT *
FROM gummies
Displaying records 1 - 10
id name description consistency colour flavour flavour_type_id price price_denominator_id
1 Bada Booms An explosion of taste soft blue strawberry 1 0.05 1
2 Vinson Drops Soup. Now in gummy form chewy brown soup 2 0.20 2
3 Salmoniakki Salmon flavored salted licquorice hard black salmon liquorice 2 0.50 2
4 Froggurt Frog meat flavored yogurt gummy soft green frog 1 0.50 2
5 Garum Gummae A gummy take on a Roman flavor stretchy black anchovie 3 1.00 2
6 Apple Bottom Jeans Gummies shaped like jeans with apple flavor chewy blue apple 1 0.03 2
7 Dog Residue Imitates dog dropping with imitation dog dropping flavor hard brown dog 4 0.01 2
8 Kitty Litter Ever wanted to eat the drop box? hard white ammonia 4 0.01 2
9 Frostbite Imitates how it feels to have frostbite hard blue minty 5 0.50 2
10 Matchsticks Good for eating; not to ignite hard red phosphorus 3 0.80 2

6.10.2 Step 2 - getting the flavour_type for each gummy

SELECT 
  gummies.id, 
  gummies.name, 
  gummies.consistency, 
  flavour_types.flavour_type, 
  gummies.price
FROM gummies
LEFT JOIN flavour_types ON flavour_types.id = gummies.flavour_type_id
Displaying records 1 - 10
id name consistency flavour_type price
16 gummyX soft sweet 0.25
15 Just Strawberry soft sweet 0.40
14 Eye for an Eye soft sweet 0.90
6 Apple Bottom Jeans chewy sweet 0.03
4 Froggurt soft sweet 0.50
1 Bada Booms soft sweet 0.05
12 Pain hard umami 0.60
3 Salmoniakki hard umami 0.50
2 Vinson Drops chewy umami 0.20
10 Matchsticks hard salty 0.80

6.10.3 Step 3 - Limiting to hard gummies

SELECT 
  gummies.id, 
  gummies.name, 
  gummies.consistency, 
  flavour_types.flavour_type, 
  gummies.price 
FROM gummies
LEFT JOIN flavour_types ON flavour_types.id = gummies.flavour_type_id
WHERE gummies.consistency = 'hard';
7 records
id name consistency flavour_type price
3 Salmoniakki hard umami 0.50
7 Dog Residue hard bitter 0.01
8 Kitty Litter hard bitter 0.01
9 Frostbite hard lidocaine 0.50
10 Matchsticks hard salty 0.80
12 Pain hard umami 0.60
13 Tempest hard sour 0.80

6.10.4 Step 4 - Selecting final columns to display and calculating values

SELECT 
  gummies.consistency AS Consistency, 
  flavour_types.flavour_type AS "Flavour type", 
  COUNT(*) AS "Number of gummies",
  AVG(gummies.price) AS "Average price"
FROM gummies
LEFT JOIN flavour_types 
  ON flavour_types.id = gummies.flavour_type_id
WHERE gummies.consistency = 'hard'
GROUP BY gummies.consistency, flavour_types.flavour_type;
5 records
consistency Flavour type Number of gummies Average price
hard bitter 2 0.01
hard lidocaine 1 0.50
hard salty 1 0.80
hard sour 1 0.80
hard umami 2 0.55

6.10.5 Step 5 - Storing this virtual table as a view

DROP VIEW IF EXISTS gummies_stats;
CREATE VIEW gummies_stats AS
SELECT 
  gummies.consistency AS Consistency, 
  flavour_types.flavour_type AS "Flavour type", 
  COUNT(*) AS "Number of gummies",
  AVG(gummies.price) AS "Average price"
FROM gummies
LEFT JOIN flavour_types ON flavour_types.id = gummies.flavour_type_id
WHERE gummies.consistency = 'hard'
GROUP BY gummies.consistency, flavour_types.flavour_type;

6.10.6 Step 6 - using the view

SELECT * FROM gummies_stats;
5 records
consistency Flavour type Number of gummies Average price
hard bitter 2 0.01
hard lidocaine 1 0.50
hard salty 1 0.80
hard sour 1 0.80
hard umami 2 0.55

6.11 Homework

You are now ready to do individual lab #3 AND the (final) part #3 of your team assignment. Have fun!