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
4. Using a view
How to use views
Summary

Instruction

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.

Exercise

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 column avg.

Stuck? Here's a hint!

Use an AVG() function.