[go: up one dir, main page]
Scribd
Upload
36 views2 pages

Test Answers

1) The document contains 5 solutions to different problems or questions. Each solution is labeled and contains SQL code to solve the problem. 2) The solutions use techniques like CTEs, window functions, joins, aggregations and grouping to retrieve and calculate the requested data. 3) SQL code is provided to find the second highest salary by department, count orders by date and part, and find duplicate employee names.

Uploaded by

Chris Rogelio
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOC, PDF, TXT or read online on Scribd
36 views2 pages

Test Answers

1) The document contains 5 solutions to different problems or questions. Each solution is labeled and contains SQL code to solve the problem. 2) The solutions use techniques like CTEs, window functions, joins, aggregations and grouping to retrieve and calculate the requested data. 3) SQL code is provided to find the second highest salary by department, count orders by date and part, and find duplicate employee names.

Uploaded by

Chris Rogelio
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOC, PDF, TXT or read online on Scribd
You are on page 1/ 2

1) Solution:

/************ SOLUTION 1 | Pawan Kumar Khowal ****************/

;WITH CTE AS
(
SELECT BusinessEntityID ,SalesYear ,CurrentQuota
, ROW_NUMBER() OVER (ORDER BY BusinessEntityID ) AS ID
FROM lag
)
SELECT c.BusinessEntityID ,c.SalesYear , c.CurrentQuota
, ISNULL((SELECT TOP 1 d.CurrentQuota FROM CTE d WHERE c.ID > d.ID ORDER BY ID DESC ),0)
lagCurrentData
FROM CTE c

/************ SOLUTION 2 | Pawan Kumar Khowal ****************/

;WITH CTE AS
(
SELECT BusinessEntityID ,SalesYear ,CurrentQuota ,ROW_NUMBER()OVER (ORDER BY
BusinessEntityID )AS ID
FROM lag
)
SELECT c.Id ,c.BusinessEntityID ,c.SalesYear , c.CurrentQuota,ISNULL(d.CurrentQuota,0)
lagCurrentData
FROM CTE c LEFT OUTER JOIN CTE d ON c.ID =(d.ID+1)

2) Solution:

/************   SOLUTION 1    | Pawan Kumar Khowal     ****************/


 
;WITH CTE AS
( 
        SELECT EmpId , EmpName,BirthDate , MONTH(BirthDate) AS Mont, DAY(BirthDate)
AS days
        FROM EmpBirth
)
SELECT EmpId , EmpName,BirthDate FROM CTE WHERE Mont = 5 AND days >= 7 AND days <= 15
 
 
/************   SOLUTION 2    | Pawan Kumar Khowal     ****************/
 
;WITH CTE 
AS (
    SELECT EmpId , EmpName,BirthDate,
    CASE
      WHEN  MONTH(BirthDate) < 10 THEN  '0' + CAST(MONTH(BirthDate) AS VARCHAR(1))
            ELSE
                CAST(MONTH(BirthDate) AS VARCHAR(2))
      END
    + CASE WHEN DAY(BirthDate) < 10 THEN '0' + CAST(DAY(BirthDate) AS VARCHAR(1))
            ELSE
                  CAST(DAY(BirthDate) AS VARCHAR(2))
            END FormattedDOB
FROM EmpBirth
)
SELECT EmpId , EmpName,BirthDate FROM CTE WHERE FormattedDOB BETWEEN '0507' AND '0515'
 
 
 3)Solution:
SELECT e.Depid,
    ( SELECT p.EmpSalary
      FROM EmployeeMaxSalary p
      WHERE p.depid = e.depid
      ORDER BY p.EmpSalary DESC
      OFFSET (1) ROWS FETCH NEXT 1 ROWS ONLY )
SecondHighestSalary
FROM EmployeeMaxSalary e
GROUP BY e.depid

4) Solution:

SELECT p.partid,d.idate,  COUNT(o.idate) Counts FROM parts p cross join dates d left
join orders1 o ON d.idate = o.idate and p.partid = o.partid
GROUP BY p.partid, d.idate ORDER BY 1
/************   SOLUTION 2    | Pawan Kumar Khowal     ****************/
 
SELECT p.partid, d.idate,COUNT(o.partid) OVER (PARTITION BY o.partid , o.idate ORDER BY
(SELECT NULL)) counts FROM orders1 o
FULL OUTER JOIN Dates d CROSS JOIN parts p ON d.idate = o.idate AND p.partid = o.partid
ORDER BY p.partid

5)Solution:

SELECT EmpName , COUNT(*) DuplicateCount FROM TESTDuplicateCount


GROUP BY EmpName
HAVING COUNT(*) > 1
ORDER BY EmpName DESC

You might also like