This is the 1st video of the video series Oracle Interview questions. The video Demonstrates how to delete duplicate records in Oracle. I will cover all the major common questions asked in SQL and PL/SQL interviews as a part of this series. Please do contribute questions that you have come across in any of your interviews
Views: 3914 Tech Coach
This video explains analytical functions and how they are implemented in real projects. Analytical functions are somewhat similar to aggregate functions,but they offer much more. Why use analytical function ? They allow you to write fast and concise queries which otherwise will involve self join and long processing times They allow you to perform aggregate functions independently on sets of partitions. You can access values from previous rows in current row and you can restrict the window on which you want to apply this analytical function. I have given additional practice exercises along with the dataset so that you can comfortably work with analytical functions. You can find the sample problems along with dataset in the below link. http://www.internshipsfromhome.com/oracle-analytical-functions-ddl-and-dml/
Views: 27372 Tech Coach
This Video explains the limitations of Static lookup and then explains how dynamic lookup transformation provides that solution with a practical Scenario. The Initial PPT explains the generic concept followed by Implementation in powercenter
Views: 15006 Tech Coach
This Video series will explain partitioning and its use cases referencing real project examples from different domain. It will explain what when and why of partitioning in a simple but elaborate manner. This is the 1st video which explains why partitioning is required and what are the advantages we gain from it. The following videos will explain when should we do it and how.
Views: 32529 Tech Coach
The video explains what are slowly changing dimensions, Their relevance in data warehousing and which SCD type should be used in what kind of data scenario. The video Explains below with real project examples. SCD 0 - Passive method (No change in data) SCD 1 – Overwriting the old value (Latest Record Only) SCD 2 – Creating a new additional record (Maintains History) SCD 3 – Creating an additional Column (Rarely Used) SCD 4 – Using history table SCD 6 - The hybrid approach
Views: 30861 Tech Coach
This Video is the 1st tutorial in the video series Indexing in Oracle , The video series explains in detail, What are indexes?It's types, what index should be used in which scenario and other important thing in basic terminology. Note :You may want to watch the video with a higher playback speed(1.25 if it suits you more)
Views: 21830 Tech Coach
Fact, Dimension, Star Schema may sound little tricky specially to people who have never worked on a Datawarehouse, This video explains them in a simple manner with a real world example
Views: 100690 Tech Coach
This is a common question asked in a DWH and BI interview, What are the number of fact and dimension tables you had in your project. While it seems that the answer to the question is numeric (4 fact and 10 dimensions . The interviewer is looking for specific details. This video explains how exactly you should answer such questions, It explains the entire answer with the help of a retail organisation.
Views: 7098 Tech Coach
The video demonstrates the scenario of selecting 10 top salaried employees in an organization it also depicts the limitation of rank transformation in selecting 5th-10th highest record and conveys how easily the same can be achieved using analytical functions in source qualifier query
Views: 9039 Tech Coach
The Video Explains the difference between Local Partitioned Indexes(Prefixed vs Non Prefixed Indexes). and Global Partitioned Index along with the challenges in maintaining Global partitioned Indexes when the underlying tables partitioned is dropped/truncated/Merged/Moved. Local Partitioned Index Shares the same boundaries as the table and are in the same number as table partitions they are widely used in DSS and DWH systems. While Global Partitioned Index are predominantly used in OLTP systems
Views: 8857 Tech Coach
A source table has values Col1 Col2 a l a m a n b p b q x y The target table should have values Col1 Col2 a l,m,n b p,q x y The Video Demonstrates 2 approaches to achieve the same using listagg function in SQ query and by using expression and aggregation in case of flat file. Please post in comments section in case you want help with any scenario i will try to cover it in my next video
Views: 6573 Tech Coach
Please subscribe to my new channel. https://www.youtube.com/c/AnIndianAbroadd The Videos explains how Reverse Btree Index works and in what condition they shall be used. Reverse Btree index are used to solve index block contention. You can't perform range scans in reverse btree Index.
Views: 2936 Tech Coach
A Junk Dimension is a dimension table consisting of attributes that do not belong in the fact table or in any of the existing dimension tables. The nature of these attributes is usually text or various flags, e.g. non-generic comments or just simple yes/no or true/false indicators. The have low cardinality and usually don't come under SCD. If you require any clarifications for this video, Please drop me a comment and I will try to answer asap.
Views: 4140 Tech Coach
The Video Demonstrates a scenario where the Source contains the scores of three students in three subjects in below format. ID Name Subject Score 100 Vivek Science 50 100 Vivek Maths 50 100 Vivek English 70 200 Amit Maths 80 300 Ankit Maths 40 200 Amit Science 70 300 Ankit Science 80 200 Amit English 60 300 Ankit English 60 It explains how we can display the scores of students in cross tabular format using pivot in Source qualifier query or using expression and aggregator in case if source is flat file
Views: 26851 Tech Coach
The video explains Transaction control transformation in Informatica with real project scenario. The Dept heads of all departments wants to see all the Policies sold by the employees working in their respective department on a monthly basis. Using transaction control transformation the process will be automated and the files will be created department wise, So that only the Authorised individuals can see their department files
Views: 2268 Tech Coach
The Video Demonstrates the following scenario using various approaches including normalizer and unpivot and explains why we should choose our approach wisely while implementing . SOURCE COL1 COL2 COL3 A B C P Q R TARGET COL A B C P Q R
Views: 14269 Tech Coach
Star and Snowflake schema are basic and vital concept of dataware housing. This video explains what are star and snowflake schema. Their differences and which should be used when in a very simplified manner with real examples. Please like this video and subscribe to my channel for more such content
Views: 46204 Tech Coach
This Video Explains what are fact tables in Data warehousing ? What is their relevance in Data warehousing ? What are the types of Fact tables present ? What kind of Fact tables you should choose when designing a data warehouse? In case if you have any queries please drop me a comment and I will be happy to help
Views: 17358 Tech Coach
The Video demonstrates how the unique and duplicate records can be segregated from source and loaded to 2 different targets.It shows 2 approaches which covers for both table and flat file as soure
Views: 18216 Tech Coach
This video explains list partitioning in detail with 2 real project examples. I recommend watching these 2 videos on partitioning prior to watching the current one. 1. https://www.youtube.com/watch?v=m3q4lrE671Y&t=25s 2. https://www.youtube.com/watch?v=OQ8LXbxLI5g&t=1312s
Views: 7124 Tech Coach
The Video Explains when should you create indexes. The difference between Simple and composite Index, Relevance of order in composite indexes and Index Scan Methods in detail. 1.Index Unique scan 2.Index Range Scan 3. Index Skip Scan 4. Fast full Index Scan 5. Full Index Scan If you have any questions just drop in a comment
Views: 9827 Tech Coach
Most of the developers can't differentiate between ODS,Data warehouse, Data mart,OLTP systems and Data lakes. This video explains what exactly is an ODS, how is it different from the other systems. What are its properties that make it unique and if you have an ODS or a warehouse in your organisation
Views: 6785 Tech Coach
This video is the 2nd video in the video series Oracle Interview questions.It explains dual table in oracle along with the advantages that it offers. Below tutorial explains how you can generate test data in oracle using oracle table and hierarchical queries. https://www.youtube.com/watch?v=Lf8m9lXNPnc&t=25s
Views: 3429 Tech Coach
This video demonstrates what are oracle views and why and when you should use them. It explains abstraction and security that the views offer with different real project examples. The DML operations you can perform on views and the restrictions. Syntax : CREATE VIEW view_name AS SELECT columns FROM tables [WHERE conditions]; Please do watch the video and leave your inputs/questions in the comments section, If you like the video give a thumbs up and share it with your friends who can benefit from this.
Views: 7877 Tech Coach
In this video I have explained who you can use the Oracle metadata tables to your advantage with real project scenarios and situations. The video is a little descriptive it gives you much more than just the syntax and the tables. All database offer a bunch of tables using which you can make smart inferences about your data,process and health of the database. I have explained the below tables in this video and how to use them smartly 1.All_Source 2.all_objects 3.All_tables 4.all_tab_cons 5.all_constraints I will be sharing more such places where we can use these metadata queries so don't forget to subscribe to my channel. If you can questions/clarifications just drop a comment and I will be more than happy to solve your queries.
Views: 5399 Tech Coach
The video explains Reference partitioning and its advantages in Oracle with real project example. It builds on the limitations of reference partitioning in oracle 11g (Interval partitioning) and scenarios where you should implement reference partitioning
Views: 2474 Tech Coach
Execute Immediate is mainly used to run native dynamic sql, This video takes a lay men approach to easily but precisely explain the concept using real project examples.
Views: 1685 Tech Coach
In this video I have explained what is range partitioning along with it's 2 real project use cases. I have also explained interval Partitioning as an extension of Range partitioning If you have not watched my Initial Videos on partitioning I will recommended watching them before watching this video Apologies for the 10 second video glitch between 6 and 7 minutes :(
Views: 6200 Tech Coach
The video explains an important interview question what is granularity in DWH. The granularity of a table is the finest level of detail it contains, while creating a fact table it is of vital importance to understand which dimensions will form that fact table or in other terms what is the lowest level of detail that can be fetched from the fact table. The video explains the same with real project examples Please do help me in building this new channel https://www.youtube.com/c/AnIndianAbroadd
Views: 1813 Tech Coach
This is the 2nd video from " Partitioning in Oracle " series, It explains how oracle stores and manages data. What is single Block IO and Multi Block IO ? Why full table scan is better than index access in few cases. The video is very elaborate, I have tried my level best to keep it as simple as possible
Views: 13010 Tech Coach
Oracle has made 4 significant enhancements in 12c. 1.Mixing Interval and Reference partitioning 2.DDL operations on multiple partitions 3.Global Index maintenance 4.Cascade Operations from parent to child, Truncate and drop This video explains the enhancements in detail with practical examples
Views: 2256 Tech Coach
The video explains BITMAP and BITMAP JOIN INDEX IN ORACLE and when you should create them on a column. BITMAP INDEXES should be dealt with carefully as they can lead to serious performance issues if the table is updated by multiple processes in parallel. Indexing Basics :https://www.youtube.com/watch?v=0X9bbtwTnuE&t=1095s Star and snowflake Schema :https://www.youtube.com/watch?v=Qq4yhhAk9fc&t=17s
Views: 6470 Tech Coach
The video explains the basic testing strategies and categories for the ETL projects. Which Includes 1.Metadata testing 2.Data testing 3.Performance Testing
Views: 2886 Tech Coach
This tutorial explains how the ETL jobs are scheduled and run.The tool used to explain the scheduling process is Autosys and Informatica powercenter is used to demonstrate the job run. It also explains PMCMD command and its associated syntax in detail and how even if Autosys, Application server and Informatica server are configured on different machines , Still how we can run the informatica workflow directly from autosys box.
Views: 4267 Tech Coach
The Video Demonstrates 2 different appraoches how we can Load Alternate Records to Targets using Informatica The approaches are 1.Using SQl Override 2.In case of a flat file The Key Learning's from the video apart from the scenario are 1.Analytical Function Row_number() 2.Case in Oracle 3.Filter Transformation 4. Router Transformation The Video in its second half takes a next step and explains how we can load alternate records in different targets using the 2 apporaches
Views: 7579 Tech Coach
The video explains how Btree index is stored in blocks and when data is inserted into the underlying table how additional blocks are added to the index which leads to index Spawning and block spliting
Views: 2470 Tech Coach
Please watch and subscribe to our new channel "An Indian Abroad" https://www.youtube.com/channel/UCoLdaMxhfDgdgIAcRGlUvkg This video is the 1st video of the video Series Regular expression in Oracle. Here we will discuss in details "Metacharacters " and how to perform complex searches easily using regular expressions. The series will cover 1.REGEXP_LIKE 2.REGEXP_SUBSTR 3.REGEXP_INSTR 4.REGEXP_REPLACE 5.REGEXP_COUNT with main focus 1,2 and 4. In this video tutorial I have added 12 problems and I will be posting the solutions in the next video tutorials.
Views: 351 Tech Coach
The video demonstrates what are the different types of joins and who they work in SQL. The tutorial is a little elaborate to make sure that you understand different kind of joins and where you should use which kind of join. The 5 Kind of joins explained are. 1. Inner join (Join or simple join) :- Rows that are common to both tables are returned as the output. 2. Left Outer join (Left join):- Returns all rows from the left table along with matching rows from the right table 3.Right Outer join (Right join):- Returns all rows from the right table along with matching rows from the left table 4.Full outer join (Full join) :- Returns all the records when there is a match in either left or right table. 5. Cartesian Join : (Join with no where condition)also known as a Cartesian product, is a join of every row of one table to every row of another table.
Views: 10368 Tech Coach
In the below scenario we have the source table as displayed where the first row contains the details of the manager accompanied by the details of employee reporting to him. We need to put the data in the target so that each employee detail maps with its corresponding manager in other column SOURCE seq EMP_ID ROLE 1 A Manager 2 B Employee 3 C Employee 4 D Employee 5 E Employee 6 F Manager 7 G Employee 8 H Employee 9 I Employee 10 J Employee 11 K Manager 12 L Employee 13 M Employee 14 N Employee Target EMPID_MANAGER EMP_ID_EMPLOYEE A B A C A D A E F G F H F I F J K L K M K N
Views: 4735 Tech Coach
Delete, Truncate and Drop all can be used to remove the contents of a table but they have significant differences and unique properties. While Delete is a DML statement, truncate and DROP are DDL. Delete can be used to remove partial/Selective data. While truncate removes the entire content of the table.
Views: 1470 Tech Coach