sql
select datepart(day, '2015-11-14')
select replace ('aaabbbaa', 'a', 'X')
select rtrim('abc ')
select ltrim(' abc ')
select substring('abcde', 1,2) --result 'ab'
Window functions
SELECT deptno, sal, job, ename,
row_number() over (order by ename),
min(sal) OVER (PARTITION BY job) as min_sal_for_job,
sum(sal) OVER () as [Total Sal],
LEAD(sal, 1) OVER(ORDER BY ename) as [Next Emp Sal],
sal - LEAD(sal, 1) OVER(ORDER BY ename) as [Diff sal with next emp],
sum(sal) OVER(PARTITION BY deptno) [Sum Sal By Dept]
FROM emp
ORDER BY ename
CTE and recursion
--all employees whom 7839 supervises directly or indirectly
with emp_hierarchy AS (
select empno, ename, mgr, 1 as level
from emp
where empno = 7839
UNION ALL
select e.empno, e.ename, e.mgr, level + 1
from emp e JOIN emp_hierarchy m
ON e.mgr = m.empno
)
select * from emp_hierarchy
where empno <> 7902
--all employees whom BLAKE supervises directly or indirectly
with emp_hierarchy AS (
select empno, ename, mgr, 1 as level
from emp
where ename = 'BLAKE'
UNION ALL
select e.empno, e.ename, e.mgr, level + 1
from emp e JOIN emp_hierarchy m
ON e.mgr = m.empno
)
select * from emp_hierarchy
where ename <> 'BLAKE'
MOCKTEST
WITH
cteCandidates (BusinessEntityID)
AS
(
SELECT BusinessEntityID
FROM HumanResources.Employee
INTERSECT
SELECT BusinessEntityID
FROM HumanResources.JobCandidate)
SELECT
c.BusinessEntityID,
e.LoginID,
e.JobTitle
FROM
HumanResources.Employee AS e
INNER JOIN cteCandidates AS c
ON e.BusinessEntityID = c.BusinessEntityID
ORDER BY
c.BusinessEntityID;
MINRATE
Select pp.FirstName, pp.LastName, rate,
min(rate) over (partition by hd.name) as [minimum in its department],
rate-min(rate) over (partition by hd.name) as difference from Person.Person pp
join HumanResources.EmployeePayHistory hep
on pp.BusinessEntityID=hep.BusinessEntityID
join HumanResources.EmployeeDepartmentHistory hed
on hed.BusinessEntityID=pp.BusinessEntityID
join HumanResources.Department hd
on hd.DepartmentID=hed.DepartmentID
order by hd.Name, rate
HAVING
Select count(pp.productID), ps.Name
from Production.Product pp join Production.ProductSubcategory ps
on pp.ProductSubcategoryID=ps.ProductSubcategoryID
group by ps.Name
having count(pp.productID)>20
order by count(pp.productID) desc
UPDATABLE
select * from information_schema.views
AVARAGE RATE
select e.JobTitle, rate
from HumanResources.EmployeePayHistory eph
join HumanResources.Employee e
on eph.BusinessEntityID=e.BusinessEntityID
where rate<(select avg(eph.Rate) from HumanResources.EmployeePayHistory eph)
1. Find out number of clerks in table Emp.
2. Produce a query that will output count of all employees, count of employees with not null commission, number of managers and average salary of all employees.
select count(*) [Total num of emps],
count(comm) [Count of commission],
count(distinct manager) [num of managers],
avg(sal) [Avg salary]
from emp;
3. For each department show number of employees, max, min, and average salary of employees. Show min, max, average but now for total salary (sal + comm). [Hit: convert nulls to 0 before calculations]
4. Show department name, job and number of employees with certain job in each department. Show only those departments that have more than 3 employees.
select d.dname, e.job, count(*)
from emp e join dept d on e.deptno=d.deptno
group by d.dname, e.job
having count(*) > 3
order by d.dname, e.job
5. For each manager show manager name, number of employees that he/she manages directly and their max, min and average salaries. Show only those managers who manage more then 2 employees.
select m.ename [Manager name]
, count(*)
, min(e.sal) [Min sal of Emps]
, max(e.sal) [Max sal of Emps]
, min(m.sal) [Min sal of managers]
, max(m.sal) [Min sal of managers]
from emp e JOIN emp m ON e.manager = m.empno
group by m.ename
having count(*) > 2
not correlated subqueries
6. Find all the employees who are in the same department as the ‘president’. (first find deptno of the department where ‘president’ works then filter out employees working in the same deptno)
7. List all salaries which are equal to hisal in salgrade table. (produce the list of hisal values from salgrade table and then use this list with IN condition to filter by sal column)
select * from emp
where sal IN (
select hisal
from salgrade
)
8. List employees’ names, jobs and names of their managers. For those employees who do not have manger in the name of the manager column output ‘No manager’. (use coalesce function to convert Null to ‘No manager’ string literal )
9. Find employees with the lowest salary (who have salary equal to lowest salary).
10. select *
11. from emp
12. where sal = (select min(sal) from emp)
13. Find employees who earn more than any (/all) employees in department 20.
select *
from emp
where sal > (select min(sal)
from emp
where deptno = 20
)
select *
from emp
where sal >ALL (select sal
from emp
where deptno = 20
)
select *
from emp
where sal > (select max(sal)
from emp
where deptno = 20
)
Correlated subqueries
14. Find all departments without employees. (need to use correlated subquery with EXISTS)
15. Find employees who earn more than the average salary of their department.
16. Find employees who earn more than any other employee in his/her department whose job is not a manager.
select *
from emp e
where job <> 'manager'
AND
sal > (
select min(sal)
from emp e2
where e2.deptno = e.deptno
)
17.
18. Produce a list of all employees and for each employee show min, max, and average salary of all employees; max and average salary of employees in the same department.
select ename, sal, job
, (select avg(sal) from emp) [Total avg sal]
, (select avg(e2.sal) from emp e2 where e2.job=e.job )
from emp e
order by job
select ename, comm, coalesce(comm, 0)
from emp
where coalesce(comm, 0) = 0
--where comm is null OR comm = 0
select *
from emp
where manager is null
select ename, sal, comm
, (coalesce(comm, 0)/sal)*100 [Percentage of comm from sal]
from emp
select count(comm), count(job), count(distinct job) from emp
select deptno, count(*), avg(sal)
from emp
group by deptno
select job, count(*), avg(sal)
from emp
group by job
select count(*), count(comm)
, count(distinct manager) [Num of managers]
, avg(sal)
from emp
select deptno, count(*)
, min(sal) [Min salary]
, max(sal), avg(sal)
,min(sal + coalesce(comm,0))
,max(sal + coalesce(comm,0))
,avg(sal + coalesce(comm,0))
from emp
group by deptno
select d.dname, e.job, count(*)
from emp as e JOIN dept as d ON e.deptno = d.deptno
group by d.dname, e.job
having count(*) > 3
order by d.dname, e.job
select m.ename [Manager name]
, count(*) [Num of manged employees]
, min(e.sal)
, max(e.sal)
, avg(e.sal)
from emp e JOIN emp m ON e.manager = m.empno
where e.ename not like 'A%'
group by m.ename
having count(*) > 2
select *
from emp
where deptno = (select deptno
from emp
where job = 'president')
select *
from emp
where sal IN (select hisal from salgrade)
select ename, sal
from emp
where sal = (select min(sal) from emp)
select ename, sal
from emp
where sal > (
select max(sal)
from emp
where deptno = 20
)
select e.ename, e.sal
, manager
, coalesce(
(select m.ename
from emp m
where e.manager = m.empno
)
, 'No manager'
) [Manager name]
from emp e
select *
from dept
where EXISTS ( select 5
from emp
where emp.deptno = dept.deptno)
SELECT E.ENAME, E.SAL
FROM EMP E
WHERE E.SAL > (
SELECT MIN(EE.SAL)
FROM EMP EE
WHERE EE.DEPTNO = E.DEPTNO
AND EE.JOB <> 'MANAGER'
)
SELECT ENAME, SAL, deptno,
(SELECT MAX(SAL) FROM EMP) AS MaxSal
,(SELECT MIN(SAL) FROM EMP) AS MinSal
,(select avg(e2.sal)
from emp e2
where e2.deptno = emp.deptno)
FROM EMP
order by deptno