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;
- Author:Jason Siu
- URL:https://jason-siu.com/article/03d94298-5a65-4389-93e6-19affa103764
- Copyright:All articles in this blog, except for special statements, adopt BY-NC-SA agreement. Please indicate the source!
Relate Posts
FIT3003 - W8 - CUBE & ROLLUP in SQL (With partial CUBE and partial ROLLUP) (W8)
FIT3003 - W7 - Determinant dimensions
FIT3003 - W8 - SQL Notes about Cumulative and Moving Aggregates
FIT3003 - W8 - SQL Notes about RANK() OVER (PARTITION BY x ORDER BY y)
FIT3003 - W11 - Active Data Warehousing
FIT3003 - W2 - Star Schema