Sunday, April 3, 2016

Advanced SQL

Advanced SQL consist of new queries in SQL

There is over 100 videos and interview questions on: https://www.youtube.com/user/kudvenkat

i want to master of joins, paritition, views, trigger, dynamicswql

what database do you practice?
functions, proc, in oracle pl sql
i have both oracle sql and ms server 2008

try 2 question for today
1.
find the 2nd highest salary of employees table

2. list employees alongwith their manager in employees table

this was asked in interview with me in the currentjo

3. find information of those employees whose salary is greater than average salary of their respective departments
after that query that I sent you
it returns 1st row of each join no matter how many matches are there
if in two tables you only want to join first row and no other row
you need to use row_number, partition by and subquery
this question is trending now in interviews



select * from employees x, (select DEPARTMENT_ID,avg(salary) avg_salary from employees group by department_id) y where x.department_id=y.department_id and x.salary>y.avg_salary;
this is query to compare salary of each employee against avg salary of department and display only those with highest salary

can you write query to select max salary for each department?
using cte
WITH cteRowNum AS ( SELECT DeptID, EmpName, Salary, DENSE_RANK() OVER(PARTITION BY DeptID ORDER BY Salary DESC) AS RowNum FROM EmpDetails ) SELECT DeptID, EmpName, Salary FROM cteRowNum WHERE RowNum = 1;

These analytical functions are known as window function in sql server

CTE:
http://sql-beginner.blogspot.co.il/2012/09/difference-between-cte-and-sub-query.html
https://technet.microsoft.com/en-us/library/ms190766(v=sql.105).aspx

https://en.wikipedia.org/wiki/Hierarchical_and_recursive_queries_in_SQL
https://technet.microsoft.com/en-us/library/ms186243%28v=sql.105%29.aspx?f=255&MSPPError=-2147217396
https://www.youtube.com/playlist?list=PL08903FB7ACA1C2FB
http://docs.oracle.com/cd/E11882_01/server.112/e41084/statements_10002.htm#i2077142
dynamic sql

No comments:

Post a Comment