Yitro Business Consultants Interview Questions and Answers

SQL aggregation and join queries for data analysis.

• Use GROUP BY clause for aggregation

• Use aggregate functions like SUM, AVG, COUNT, etc.

• Use JOIN clause to combine data from multiple tables

Types of joins in SQL include inner join, left join, right join, and full outer join.

• Inner join: Returns rows when there is a match in both tables.

• Left join: Returns all rows from the left table and the matched rows from the right table.

• Right join: Returns all rows from the right table and the matched rows from the left table.

• Full outer join: Returns all rows when there is a match in either table.

You can import data in Power BI using API by connecting to the API endpoint and specifying the required parameters.

• Use the 'Get Data' option in Power BI to connect to the API endpoint

• Specify the required authentication method (e.g. API key, OAuth)

• Select the data you want to import and transform it as needed

• Refresh the data periodically to keep it up to date

Extract numbers from strings in SQL

• Use the SQL function REGEXP_REPLACE to remove non-numeric characters

• Use the SQL function REGEXP_SUBSTR to extract numbers from strings

• Consider using regular expressions to handle different number formats

Window functions in SQL are used to perform calculations across a set of table rows related to the current row.

• Window functions operate on a set of rows related to the current row

• They can be used to calculate running totals, ranks, and averages

• Common window functions include ROW_NUMBER(), RANK(), DENSE_RANK(), and NTILE()

• They are typically used with the OVER() clause to define the window frame

Joins are used to combine rows from two or more tables based on a related column between them.

• Inner Join: Returns rows when there is a match in both tables.

• Left Join: Returns all rows from the left table and the matched rows from the right table.

• Right Join: Returns all rows from the right table and the matched rows from the left table.

• Full Outer Join: Returns rows when there is a match in either table.

• Cross Join: Retur

Order of execution in SQL refers to the sequence in which different clauses are processed in a SQL query.

• 1. FROM clause is processed first to retrieve data from the specified tables.

• 2. WHERE clause is processed next to filter the rows based on specified conditions.

• 3. GROUP BY clause is processed to group the rows based on specified columns.

• 4. HAVING clause is processed to filter the grouped rows based on specified cond...read more

Use indexes, limit columns in SELECT, avoid using SELECT *, optimize joins

• Use indexes on columns frequently used in WHERE clauses

• Limit columns in SELECT to only those needed

• Avoid using SELECT * to retrieve all columns

• Optimize joins by using INNER JOIN instead of OUTER JOIN when possible