Introduction to NoSQL
Jake Roach
Data Engineer
Common table expressions (CTEs):
WITH
keywordJOIN
'edWITH <cte-name> AS (
SELECT
....
FROM <table-name>
[JOIN | WHERE | ...]
)
SELECT
...
FROM <cte-name>;
WITH premium_books AS (
SELECT
title,
author,
avg_reviews
FROM books
WHERE price > 25.00
)
SELECT
author,
MIN(avg_reviews) AS min_avg_reviews,
MAX(avg_reviews) AS max_avg_reviews
FROM premium_books
GROUP BY author;
premium_books
temporary objectpremium_books
downstream$$
Can creating multiple temporary objects:
WITH
<first-name> AS (...),
<second-name> AS (...),
...
...
;
Views:
CREATE VIEW <view-name> AS
SELECT
...
FROM <table-name>
[WHERE | JOIN | ...];
CREATE VIEW premium_books AS
SELECT
title,
author,
avg_reviews
FROM books
WHERE price >= 25.00;
SELECT * FROM premium_books;
premium_books
is calledCREATE MATERIALIZED VIEW premium_books AS
SELECT
title,
author,
avg_reviews
FROM books
WHERE price >= 25.00;
SELECT * FROM premium_books;
Introduction to NoSQL