What is primary key in SQL?
Primary key let us uniquely identify the row in a table.
How it is different from Unique key?
Primary key never accepts null value whereas in case of unique key one of the values can be null.
Can you write down a query which will display the current financial year?
How will you find the index of a particular character in a SQL variable?
We will use CharIndex method.
Let say we have multiple queries and we have to show result set of all queries as one result. What we have to do?
Use Union.
Is it possible to use order by with UNION?
We cannot write order by in each query but can in last query which will affect entire result set.
What is the difference between Fact Table and Dimensions?
Fact Table contains two things: -
- Foreign keys which pointing to primary key in dimension tables.
- Measures – This identifies some numbers in a business. Example Total number of sales, amount given, purchased amount etc.
Dimension tables contain denormalized
data. Every record in this table will contain a unique identification
key which will refer by Fact Tables.
Data warehouse design – It’s all about creating dimensions and fact tables.
1) Dimensions are extracted from traditional database. Multiple tables in a traditional table
are joined and considered as one dimension table.
Example – Let say we have following database with use
Customer ( CustomerId, CustomerName, CustomerMobile, CItyId, GroupId)
City (CityId,CityName)
Group(GroupId, GroupName)
Dimension table will look like this,
CustomerDimension(CustomerId,CustomerName,CustomerMobile,CityName,GroupName)
2) Fact table will be created containing only numbers and foreign keys pointing to other
dimensions.
Now let say we have following dimensions
CustomerDimension(CustomerId,CustomerName,CustomerMobile,CityName,GroupName)
TimeDimension(Timekey,Year,Month,Day)
ProductDimension(ProductId,ProdudctName,ProductPrice, …..)
Now One Fact table look like
ProductSale(ProductId,CustomerId,TimeKey,QtySold)
No comments:
Post a Comment