Calculating fields on the fly

Using the SELECT command we can add fields to our query that have basic algebraic calculations applied to an existing field.

In the example below the query will return the current price of a book as well as the book price ten percent higher.

SELECT price, price*1.1
FROM books;

We can use an alias to clean up the appearance of the query results. Here is an example of the same code with an alias:

SELECT price as "Current Price", price*1.1 AS "10% Increase"
FROM books;

Lesson Activities

  1. Create a table called books using the following code:
    Click to see code
    CREATE TABLE books (
    book_id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(70),
    isbn VARCHAR (17),
    price DECIMAL (5,2),
    author VARCHAR(60)
    );
  2. Insert the following records into the book table.
    Click to see code
    INSERT INTO books (title, isbn, price, author)
    VALUES ("The Daleks", "0-426-10110-3", 19.99, "David Whitaker");
    INSERT INTO books (title, isbn, price, author)
    VALUES ("The Web Planet", "0-426-10129-4", 8.50, "Bill Strutton");
    INSERT INTO books (title, isbn, price, author)
    VALUES ("The Crusade", "0-426-10137-5", 29.99, "David Whitaker");
    INSERT INTO books (title, isbn, price, author)
    VALUES ("The Cybermen", "0-426-10575-3", 8.50, "Gerry Davis");
    INSERT INTO books (title, isbn, price, author)
    VALUES ("The Crusade", "0-426-10137-5", 29.99, "Gerry Davis");
    INSERT INTO books (title, isbn, price, author)
    VALUES ("The Ark in Space", "0-426-11631-3", 19.99, "Ian Marter");
    INSERT INTO books (title, isbn, price, author)
    VALUES ("The Android Invasion", "0-426-20037-3", 29.99, "Terrance Dicks");
    INSERT INTO books (title, isbn, price, author)
    VALUES ("Full Circle", "0-426-1020150-7", 29.99, "Andrew Smith");
    INSERT INTO books (title, isbn, price, author)
    VALUES ("Arc of Infinity", "0-426-19342-3", 29.99, "Terrance Dicks");
  3. Create a calculated field to increase the price of the book by 15 percent.
    Click to see code
    SELECT title, price, price * 1.15 AS "Increase of 15%"
    FROM books;
  4. Create a calculated field that subtract 5 from the price of the book.
    Click to see code
    SELECT title, price, price - 5 AS "5 Less the Price"
    FROM books;

Questions