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!
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.
