type
Post
Created date
Sep 4, 2022 11:48 AM
category
Data Science
tags
Data Warehousing
status
Published
Language
English
From
School
summary
slug
password
Author
Jason Ching Yuen Siu
Priority
Featured
Featured
Cover
Origin
Type
URL
Youtube
Youtube
icon

1. OUTER JOIN

SELECT * FROM dtaniar.book5 b, dtaniar.review5 r WHERE b.ISBN=r.ISBN(+);
SELECT * FROM dtaniar.book5 b LEFT JOIN dtaniar.review5 r ON b.ISBN=r.ISBN;
Both of which are fine.

2. NVL()

This is the syntax to set the null value to 0: NVL (attribute_name, 0)

3. ROUND(attribute_name)

When creating the TempBookWithAvgStar, the average of stars for each book and category should be either 0, or 1,2,3,4,5. Thus, we need to use ROUND to make sure the average value is an integer. This is the syntax to set a number into an integer: ROUND(attribute_name)

4. ROWNUM

ROWNUM pseudocolumn returns a number indicating the order in which Oracle selects the row from a table or set of joined rows (ROWNUM (oracle.com) ).
select * from ( select c.CATEGORYID, c.CATEGORYDESCRIPTION, sum(f.num_of_books) as total_num_books from booksalesfact f, categorydim c where f.categoryid = c.categoryid group by c.CATEGORYID, c.CATEGORYDESCRIPTION order by total_num_books desc) where rownum =1;
 
FIT3003 - W6 - Temporal data warehousingFIT3003 - W5 - Level of aggregation