Database Reference
In-Depth Information
The
warehouse_tbl
and
history
tables have many columns but you are interested
only in showing a few columns to the users. These columns are:
• The
warehouse_name
,
year_created
, and
city
columns from the
warehouse_tbl
table
• The
amount
and
date
columns from the
history
table
You will create a view that is created on multiple tables as a
SELECT
query and
display data as one table. Here,
view_multiple_tables
irst selects columns to be
shown from both tables and then validates the IDs in the
WHERE
clause.
Create a view on the multiple tables in the following manner:
warehouse_db=# CREATE VIEW view_multiple_tables
AS SELECT warehouse_name, year_created, city, amount, date
FROM record.warehouse_tbl, record.history
WHERE warehouse_tbl.warehouse_id = history.warehouse_id;
Now, perform a
SELECT
query to see the content of the view:
warehouse_db=# SELECT * FROM view_multiple_tables;
Insert a few rows in both tables and access the view again to see whether it refreshes
the data. The irst
INSERT
statement inserts data into the
warehouse_tbl
table and
the next
INSERT
statement inserts data in the
history
table.
Insert rows in both tables as follows:
warehouse_db=# INSERT INTO warehouse_tbl
(warehouse_id, warehouse_name, year_created, street_address,
city, state, zip)
VALUES
(6, 'Jackson & Co', 2010, 'lincoln Road', 'Buffalo', 'NY',
4331);
warehouse_db=# INSERT INTO history
(history_id, date, amount, data, customer_id, warehouse_id)
VALUES
(1, 'Jul-10-14', 1234, 'thedata', 1, 1);
warehouse_db=# INSERT INTO history
(history_id, date, amount, data, customer_id, warehouse_id)
VALUES
(2, 'Jul-10-15', 2345, 'thedatasecond', 2, 2);
