Power bi is data visualization tool. It convert data from different data sources to interactive dashboard and reports.
The following are the building blocks in power bi:
Dax means Data Analysis Expression it is used for creating some basic formulas or expression.
Power query is self- service ETL tool and allow user to pull data from different data sources.
Power BI Architecture:
SQL Questions & Answers:
; with T as ( select EmployeeName,DepartmentId,Salary, DENSE_RANK() over (partition by DepartmentId order by Salary desc) as toprank from Employee ) select * from T where T.toprank <=3
UPDATE Customer SET Gender = CASE Gender WHEN 'male' THEN 'female' WHEN 'female' THEN 'male' ELSE Gender END
Stored procedure is prepared sql code that you can save and code is reused again and again.
Advantages of Stored Procedure:
It is Faster, Pre-compiled, reusable
It reduce network traffic.
It can handle complex operations.
Security is high.
Sub Query: In sub query the inner query executes only one time. The inner query executes first and send the output to the outer query and inner query is not depended on outer query.
Correlated Query: In correlated query Outer query executes first. The inner query executes so many times as no of rows results of outer query. The inner query depended on outer query.
|Primary key||Foreign key||Unique key|
|Primary key uniquely identify record in the table||Foreign key is field in the table that is primary key of another table||Unique key uniquely identify record in the table|
|Primary key can't accept null values||Foreign key accept multiple null values||Unique key accept only one null value|
|We can apply only one primary key in a table||We can apply more than one foreign key in a table||We can apply more than one unique key in a table|
|Primary key creates clustered index by default||Foreign key can not creates index by default||Unique key creates non clustered index by default|
;WITH TempEmp (Name,duplicateRecCount) AS ( SELECT Name,ROW_NUMBER() OVER(PARTITION by Name, Gender ORDER BY Name) AS duplicateRecCount FROM Customer ) --Now Delete Duplicate Rows DELETE FROM TempEmp WHERE duplicateRecCount > 1
SQL joins are used to combine data from two or more tables based on a common field between them.
Types of Join:
In parameter: This types of parameters are used to send values to stored procedure.
Out Parameter: This types of parameter are used to get values from stored procedure.
In Out parameter: This types of parameters are used to send values and get values from stored procedure.