2.Difference between Union and union all,drop and Truncate,star schema and snowflake schema,Dimension table and fact table.

Question

Accepted Answer

Union combines the result sets of two or more SELECT statements, while Union All combines all rows from two or more SELECT statements.

Union removes duplicate rows, while Union All does not.
Union requires the number and order of columns in all SELECT statements to be the same, while Union All does not have this requirement.
Example: SELECT column1 FROM table1 UNION SELECT column1 FROM table2;
Example: SELECT column1 FROM table1 UNION ALL SELECT column1 FROM table2;

Accepted Answer

both union and union all gives all records from both the tables, union all allows duplicates where as union doesnot allows the duplicates.

union all gives better performance compared to union.

both drop and truncate comes under DDL statements.

drop removes both data and metadata whereas truncate removes only data metadat remains same

both star schema and snow flake schemes are logical archetecture of DWH.

star schema consists of centralized face table surrounded by multiple dimention tables. it looks like star hence it is called star schema.

snow flake schema is the extended schema of star schema, the surrounded dimention table again attached with some more tables.

Top Cognizant ETL Tester interview questions & answers