Creating the tables and view

You see how to create simple tables, insert the data, and join the tables using a materialized view. You run the query, and the optimizer takes advantage of the precomputation performed by the materialized view to speed response time.

Log into the Data Warehouse service as DWUser.
Go to the Virtual Warehouses tab, locate the Virtual Warehouse using which you want to run queries, and click HUE.
The Hue query editor opens in a new browser tab.
Select a database, for example default.

Create a managed table.

For example, create this emps table in the Hue editor:

CREATE TABLE emps (
empid INT,
deptno INT,
name VARCHAR(256),
salary FLOAT,
hire_date TIMESTAMP);

Create another table.
For example, create this depts table:
```
CREATE TABLE depts (
deptno INT,
deptname VARCHAR(256),
locationid INT);
```

Insert data into the tables by copy/pasting the following statements one-by-one and executing them one-by-one.

INSERT INTO TABLE emps VALUES (10001,101,CAST('jane doe' as VARCHAR(256)),250000,'2018-01-10');
INSERT INTO TABLE emps VALUES (10002,100,CAST('somporn klailee' as VARCHAR(256)),210000,'2017-12-25');
INSERT INTO TABLE emps VALUES (10003,200,CAST('jeiranan thongnopneua' as VARCHAR(256)),175000,'2018-05-05');

INSERT INTO TABLE depts VALUES (100,CAST('HR' as VARCHAR(256)),10);
INSERT INTO TABLE depts VALUES (101,CAST('Eng' as VARCHAR(256)),11);
INSERT INTO TABLE depts VALUES (200,CAST('Sup' as VARCHAR(256)),20);

Create a materialized view to join the tables:

CREATE MATERIALIZED VIEW mv
AS SELECT empid, deptname, hire_date
FROM emps JOIN depts
ON (emps.deptno = depts.deptno)
WHERE hire_date >= '2017-01-01';