Analytic Functions: Databases for Developers #13

[@clockfixer5049]

Hi there! At 3:53 I think there’s a mistake: Range between 2 preceding and 2 following should include all the toys (1 is missed in the video) If we start counting from toy ‘3’, then the range preceding would not differentiate between the same values (unlike rows), hence we include 3 toys on the right. BUT why do we not include 2 toys on the left? The only explanation is either ‘2 following’ includes the current row - and I’m in the wrong, or there’s an omission. Hope you could help clarify!

[@TheMagicofSQL]

RANGE uses numeric values to calculate the offsets. The current toy has weight 3, so RANGE BETWEEN 2 PRECEDING AND 2 FOLLOWING includes all those with weight values BETWEEN (3-2) AND (3+2) => BETWEEN 1 AND 5 The Kangaroo's weight is 8, which is > 5. So is excluded from the calculation.

[@kaz255]

Great video but if you present the queries on a screen for a little bit longer it would have been helpful. Some queries are hard to pause on using mobile

[@TheMagicofSQL]

Thanks, we'll keep this in mind for future videos

[@dummuvikash4957]

I want to know recursive query working

[@IMdAbdulquadirKhan]

Hi chirs , i don't have an idea on projects related to sql can you please guide me on how to create and find ideas for projects !!!!!!

[@TheMagicofSQL]

Find a data set that interests you - there are loads available as "Open Data", load it into your database, think of questions about it, and run the queries to answer them e.g. births/deaths details for countries - what's the average lifespan? How many live past 100? Which cities have the highest birth/death rates, etc.

[@IMdAbdulquadirKhan]

@@TheMagicofSQL THANK YOU , YOU ARE KIND AND RESPONSIVE

[@NewYork0110J]

If main queries returns too many rows. Is having scaler queries good idea or should they be replaced by join in the main query.

[@TheMagicofSQL]

There's no general answer to this - you need to understand WHY the query currently gives too many rows. Once you've figured this out, the "right" way to do it should become clearer

[@NewYork0110J]

@@TheMagicofSQL Thank you for your response. Sometimes when you working in data-warehouse you may have large number of rows to process and at the same time if your table have so many lookups for descriptions should we be using scaler queries or what is the better method.

[@TheMagicofSQL]

This is something you need to decide on a query-by-query level. "It's a data warehouse" is still way too broad. It comes down to the specifics of the table structures and what question you're answering. That said, it's rare for scalar subqueries to be the best method. If there's a specific query you're struggling with, put together a test case (create table + insert into) and post it on asktom.oracle.com/ and we'll help you out.