OK. Let's take a look at our doctor table again:
CREATE TABLE doctor ( id integer PRIMARY KEY, full_name varchar(128), type varchar(32), city varchar(128), rating decimal(2, 1), price_per_visit decimal(5, 2), office_id integer, FOREIGN KEY (office_id) REFERENCES office(id) );
Note the office_id column at the end. It refers to another table, which is named office:
CREATE TABLE office ( id integer primary key, name varchar(32), address text );
Our doctors are mostly located in the US, but we've also got some doctors in Europe (Berlin, to be precise). According to European GDPR law, though, we must protect personal data. For this reason, we can only expose limited information about our German doctors. So we do the following:
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 view above uses a JOIN to retrieve data from two tables: doctor and office. However, it only exposes non-sensitive information. Columns like full_name were omitted.
We now have two tables in our publishing house: textbook and author.
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) );
We want to create a view which will present some data to customers who visit the company website. Obviously, we want to hide information such as printing_cost.
Create a view named website_textbook_author that will show the following information from the textbook and author tables: the textbook ID, the author's full name, and the book's title, subject, and retail price.
You'll need to use a JOIN on the textbook and author tables.
