Deals Of The Week - hours only!Up to 80% off on all courses and bundles.-Close

Wrap course as a gift

Prepare your gift and double-check it looks just right.

Form validation errors

Recipient's Name:
Recipient's Email:
Your Message (Optional):
Proceed to Checkout

Working with Views

Your first view
How to use views
8. Creating views with columns calculated on the fly
Summary

Instruction

Good job! Now, let's look at another use case. Currently, all prices in our tables are expressed in US dollars. However, patients from Germany would like to pay in euros. For this reason, we may want to create another view based on the view given below:

CREATE VIEW german_doctors_offices AS
SELECT d.id, type, rating, price_per_visit, address
FROM doctor d
JOIN office o
  ON d.office_id = o.id
WHERE d.city = 'Berlin';

The new view will query the original view and convert USD prices to EUR prices:

CREATE VIEW german_doctors_eur AS
SELECT id, type, rating, price_per_visit * 0.9 AS price_per_visit_eur
FROM german_doctors_offices;

As you can see, we can create views based on existing views!

Exercise

A primary school would like to get some basic information about the biology textbooks we have. You are given the following two tables and a view:

CREATE TABLE textbook (
  id integer PRIMARY KEY,
  author_id integer,
  title varchar(64),
  subject varchar(32),
  retail_price decimal(5, 2),
  printing_cost decimal(5, 2),
  year integer,
  FOREIGN KEY (author_id)
  REFERENCES author(id)
);
CREATE TABLE author (
  id integer PRIMARY KEY,
  full_name varchar(32)
);
CREATE VIEW website_textbook_author AS
SELECT t.id, full_name, title, subject, retail_price
FROM textbook t
JOIN author a
  ON t.author_id = a.id;

Your task is to create a view named biology_textbook_short. It will be based on the website_textbook_author view and will contain the following columns for textbooks with the subject 'Biology': id, full_name, and title.