Perfect! We've created a view with cardiologists in the previous explanation:
CREATE VIEW cardiologist AS SELECT * FROM doctor WHERE type = 'cardiologist';
Once we create a view, we can query it just like any other table. For instance:
SELECT full_name, price_per_visit FROM cardiologist WHERE city = 'Boston' ORDER BY price_per_visit;
Result:
| full_name | price_per_visit |
|---|---|
| Jon Snow | 80.00 |
| Kate Morgan | 83.00 |
| ... | ... |
The query above will show the names and prices for all cardiologists in Boston. Note that we didn't query the original table (doctor). Instead, we used the view (cardiologist) we created previously.
After the previous exercise we have the following table and the view:
CREATE TABLE textbook ( id integer PRIMARY KEY, title varchar(64), subject varchar(32), retail_price decimal(5, 2), printing_cost decimal(5, 2), year integer );
CREATE VIEW biology_textbook AS SELECT * FROM textbook WHERE subject = 'Biology';
The publishing house would like to know the
average retail price for biology textbooks
. Use the view and write the appropriate query. Name the columnavg.
Use an AVG() function.
