Good job! Now, we need to create some aggregated metrics for all doctors. Given the same table ...
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) );
... we can now create a view like this:
CREATE VIEW doctors_metrics AS SELECT city, COUNT(id) AS doctor_count, AVG(price_per_visit) AS avg_price_per_visit FROM doctor GROUP BY city HAVING COUNT(id) > 2;
The view above shows the number of doctors in each city alongside the average visit price for that city. Only cities with more than two doctors are shown.
As you can see, views can be quite complex – they may also use clauses like GROUP BY or HAVING.
We need some textbook statistics now. You're given the following definition of the textbook table:
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 );
Your task is to create a view named textbook_stats which will have the following columns:
subjectavg_retail_price – The average retail price for textbooks on the given subject.avg_printing_cost – The average printing cost for textbooks on the given subject.You'll need to use GROUP BY subject.
