Viewing, updating and deleting data in tables

This is part 3 of a four-part article which explains SQL

• Part 1
• Part 2
• Part 3
• Part 4

Retrieving data using the SELECT clause

• As mentioned before, relations / tables / sets are an unordered collection of rows, or tuples.
• Let's retrieve all the content in table products:

SELECT *
FROM products;

• SELECT *
  • This means brings all the columns; it could also be written as SELECT id, name
• FROM products
  • List of tables to operate on. In this case, we're talking about table products
  • Here's a possible result:

• Why possible? Because there is no specific ordering of rows; it all depends on how the DB system stores the rows, the order in which rows were INSERTed, and others

Ordering

• In order to establish a specific order, we must use the ORDER clause:

SELECT id, name
FROM products
ORDER BY id;

• In reverse order, by name:

SELECT id, name
FROM products
ORDER BY name DESC;

• The ORDER clause may list more than one column, or expression; in this simple case, it doesn't make much sense

Filtering

• Let's see how to limit the number of rows, based on filtering conditions:

SELECT *
FROM products
WHERE id = 2;

SELECT *
FROM products
WHERE name LIKE 'Sport %'
ORDER BY id ASC

• Here, a combination of WHERE and ORDER BY clauses:
  • WHERE name LIKE 'Sport %' means all rows where name starts with 'Sport':
  • The % symbol maps from zero to any number of characters
  • If the ? symbol were used, that would mean exactly one character, which would bring zero rows, as
• ORDER BY id ASC: ASC for ascending, which is the default in the clause

Functions

• SQL offers a number of standard functions, and in fact, there are two types of functions:
  • Common functions, which apply to expressions in rows
  • Aggregate and Window functions, which apply to more than one row at a time

Row functions

SELECT id, id * 2 AS double_id, upper(name) AS upper_name
FROM products
ORDER BY id;

• Several things to notice here:
  • Expressions like id * 2 and functions like upper(name)
  • the AS key word is used to give names to the expressions; if it were not used, the calculated columns would have default names, which vary depending on the DB system used

Aggregate and window functions

SELECT COUNT(*) AS count
FROM products;

SELECT id_product,
       COUNT(*) AS number_of_sales,
       SUM(amount) AS sum_amount
FROM sales
GROUP BY product
ORDER BY product;

• Here aggregate functions COUNT(), SUM() are used
• Notice the resulting rows and content:
  • Aggregate functions
  • COUNT(*): counts the number of rows, _according to the GROUP BY criterion
  • SUM(amount): calculates the sum of column amount according to the GROUP BY criterion
  • AVG(x): calculates the average
  • MAX(x), MIN(x): obtains the maximum or minimum value
  • There are other aggregate functions in standard SQL
• The list of columns in the SELECT when using grouping functions, should either:
  • Be the result of an aggregate expression
  • Be named in the GROUP BY clause
• If this is not the case, the query should fail
  • If it doesn't fail, for some DB engines, that's an issue, since it's not easy to spot a failed SELECT
  • PostgreSQL is very strict, which is a great thing; not so with other DB products

Filtering grouped rules

• Instead of using the WHERE clause, for filtering on aggregated expressions, HAVING has to be used:

SELECT id_product,
       COUNT(*) AS number_of_sales,
       SUM(amount) AS sum_amount
FROM sales
GROUP BY product
HAVING COUNT(*) < 5
ORDER BY product;

Combining results from more than one table

• By using the JOIN clause, more than one table can be invoked in the SELECT statement.
• Here is an example:

SELECT s.id_product,
       p.name,
       COUNT(*) AS number_of_sales,
       SUM(s.amount) AS sum_amount
FROM sales s
  JOIN products p
    ON s.id_product = p.id
GROUP BY s.id_product, p.name
HAVING COUNT(*) < 5
ORDER BY s.id_product;

• A few changes to notice:
  • FROM ... JOIN ... ON ...
  • FROM used to name one table,
  • JOIN names the second table
  • ON establishes the way both tables are related
  • Table name aliases:
  • sales s and products p; this is not necessary, but instead of typing sales.id_product, product.name and sales.amount, aliases help us to shorten the sentences
  • If the column names are unique among the invoked tables, there is no strict need to preface the column with the table or alias names, although it's a good practice. In fact, if in the future one of the column names are repeated, what worked so far will stop to do so, as the DB engine wouldn't know what table the column referrer to
• the GROUP BY clause needs to include p.name; otherwise, an error should be triggered (again, not all DB engines do so)
• Types of JOIN:
  • INNER JOIN: Rows from both tables must be present (the default)
  • LEFT JOIN: Rows from the left-side table must exist, for columns in the right-side table missing, NULL values are used
  • RIGHT JOIN: If rows from the left-side table do not exist, NULL values are used instead
  • CROSS JOIN: Cartesian product from both tables are retrieved, as long as the 'ON' clause is fulfilled

As an example of a CROSS JOIN, which is not much used, look at this simple SELECT:

SELECT p.id AS id_product,
       p.name AS name_product,
       s.id AS id_seller,
       s.name AS name_seller
FROM products p,
     sellers s
ORDER BY id_product, id_seller;

Simply naming the tables without any filtering condition, is equivalent to a CROSS JOIN:

This SELECT is just an example of a CROSS JOIN result.

Updating table contents with UPDATE

• The UPDATE sentence is used for updating rows
• An example follows:

UPDATE sellers
SET name = 'María T.'
WHERE id = 341;

• After this change, look at the table contents:

SELECT *
FROM sellers
ORDER BY id;

• Important points:
  • Limit the UPDATE with a WHERE; otherwise, the update will impact all rows in a table, all of them, in one transaction
  • Not all updates are guaranteed to success; for instance, in the schema with all constraints in place, this UPDATE will fail:

UPDATE sellers
SET id = 235
WHERE id = 234;

• If you try this, you'll see that it fails, as there are relational integrity constraints which prevent tables sellers and products to update their PK if there is at least one row in the sales table that point to those values, as is the case in the example here
  • See the error with PostgreSQL:

ERROR:  update or delete on table "sellers" violates foreign     
        key constraint "sales_seller_fkey" on table "sales"
DETAIL: Key (id)=(234) is still referenced from table "sales".

• See it with MariaDB:

ERROR 1451 (23000): Cannot delete or update a parent row:
      a foreign key constraint fails
      (`course`.`sales`,
       CONSTRAINT `sales_ibfk_2`
       FOREIGN KEY (`id_seller`) REFERENCES `sellers` (`id`)
      )

Deleting table contents with DELETE

• The DELETE DML sentence is used to remove rows from tables
• Here's an example:

DELETE
FROM sales
WHERE quantity BETWEEN (4 AND 5);

• After deleting these rows, the contents of the table are:

SELECT id_product,
       id_seller,
       date,
       quantity,
       amount
FROM sales
ORDER BY id_product, id_seller;

• As is the case with the failed UPDATE and constraints are in place, certain DELETE queries will fail
• For instance:

DELETE
FROM sales
WHERE id = 234;

• If the FOREIGN KEY constraints established in part 2 for table sales had the ON UPDATE CASCADE ON DELETE CASCCADE, neither the UPDATE nor the DELETE would fail:
  • For the UPDATE, it would modify the values of the foreign keys in table sales
  • For the DELETE, it would also remove rows from the sales table