Question

(Oracle SQL) Create the dept_detail_view view using all the fields in departments, locations, countries, and regions. The view is intended only for quick lookups relating to department site details. Using the dept_detail_view, create a report showing those departments in Europe. Use all the fields in the view without the id fields.

Departments

department_id

department_name

manager_id

location_id

Locations

location_id

street_address

postal_code

city

state_province

country_id

Countries

country_name

country_id

region_id

Regions

region_id

region_name

Answer 1

First create all the tables and insert it with appropriate values

Query for creating the view

In order to get all information of department sites, first we need to join all the tables on their common fields and then with the help of where condition we'll be able to get the details of departments located in Europe.

CREATE VIEW dept_details_view AS

SELECT d.department_name, l.street_address, l.postal_code, l.city, l.state_province, c.country_name, r.region_name

FROM department d

INNER JOIN location l on d.location_id = l.location_id

INNER JOIN country c on l.country_id = c.country_id

INNER JOIN region r on c.region_id = r.region_id

WHERE c.country_name='Europe';

Select the View

SELECT * FROM dept_details_view;

Output: