365 Days of Daily Coding: Day 53

Today, I managed to solve a couple of advanced SQL puzzle. I wish to finish all the SQL puzzles in the advanced sql site as well as the SQLpad. Then perhaps, move on to creating a real projects by creating several tables. I hope there’s free version of some db that I can use.

Advanced SQL Puzzle 1

WITH temp As (
Select 1001 As CustomerID, 'Ord936254' AS OrderId, 'CA' AS Delivery_State, 340 AS Amount FROM Dual Union All
Select 1001 As CustomerID, 'Ord143876' AS OrderId, 'TX' AS Delivery_State, 950 AS Amount FROM Dual Union All
Select 1001 As CustomerID, 'Ord654876' AS OrderId, 'TX' AS Delivery_State, 670 AS Amount FROM Dual Union All
Select 1001 As CustomerID, 'Ord814356' AS OrderId, 'TX' AS Delivery_State, 860 AS Amount FROM Dual Union All
Select 2002 As CustomerID, 'Ord342176' AS OrderId, 'WA' AS Delivery_State, 320 AS Amount FROM Dual Union All
Select 3003 As CustomerID, 'Ord265789' AS OrderId, 'CA' AS Delivery_State, 650 AS Amount FROM Dual Union All
Select 3003 As CustomerID, 'Ord387654' AS OrderId, 'CA' AS Delivery_State, 830 AS Amount FROM Dual Union All
Select 4004 As CustomerID, 'Ord476126' AS OrderId, 'TX' AS Delivery_State, 120 AS Amount FROM Dual 
)

SELECT * FROM temp 
WHERE CustomerID IN (
SELECT CustomerID
FROM temp
WHERE Delivery_State = 'CA') 
AND Delivery_State = 'TX';

Advanced SQL Puzzle 2

WITH temp As (
Select 1001 As CustomerID, 'Cellular' AS Type, '555-897-5421' AS Phone_Number FROM Dual Union All
Select 1001 As CustomerID, 'Work' AS Type,     '555-897-6542' AS Phone_Number FROM Dual Union All
Select 1001 As CustomerID, 'Home' AS Type,     '555-698-9874' AS Phone_Number FROM Dual Union All
Select 2002 As CustomerID, 'Cellular' AS Type, '555-963-6544' AS Phone_Number FROM Dual Union All
Select 2002 As CustomerID, 'Work' AS Type,     '555-812-9856' AS Phone_Number FROM Dual Union All
Select 3003 As CustomerID, 'Cellular' AS Type, '555-987-6541' AS Phone_Number FROM Dual 
)

SELECT * FROM (
    (SELECT CustomerID, Type, Phone_Number
    FROM temp)
    PIVOT (MIN(Phone_Number) FOR Type in ('Cellular' AS Cellular, 'Work' AS Work, 'Home' AS Home))
    ) ORDER BY CustomerID ASC;

Advanced SQL Puzzle 3

WITH temp As (
Select 'Alpha' As Workflow, 1 AS Stepnumber, '7/2/2018' AS CompletionDate FROM Dual Union All
Select 'Alpha' As Workflow, 2 AS Stepnumber, '7/2/2018' AS CompletionDate FROM Dual Union All
Select 'Alpha' As Workflow, 3 AS Stepnumber, '7/1/2018' AS CompletionDate FROM Dual Union All
Select 'Bravo' As Workflow, 1 AS Stepnumber, '6/25/2018' AS CompletionDate FROM Dual Union All
Select 'Bravo' As Workflow, 2 AS Stepnumber, '' AS CompletionDate FROM Dual Union All
Select 'Bravo' As Workflow, 3 AS Stepnumber, '6/27/2018' AS CompletionDate FROM Dual Union All
Select 'Charlie' As Workflow, 1 AS Stepnumber, '' AS CompletionDate FROM Dual Union All
Select 'Charlie' As Workflow, 2 AS Stepnumber, '7/1/2018' AS CompletionDate FROM Dual U
)

SELECT Workflow FROM
(SELECT Workflow, COUNT(Stepnumber) AS countn, COUNT(CompletionDate) AS countd
FROM temp
GROUP BY Workflow)
WHERE countd <> countn;

Advance SQL Puzzle 4

WITH temp As (
Select 1001 As CandidateID, 'Geologist' AS Description FROM Dual Union All
Select 1001 As CandidateID, 'Astrogator' AS Description FROM Dual Union All
Select 1001 As CandidateID, 'Biochemist' AS Description FROM Dual Union All
Select 1001 As CandidateID, 'Technician' AS Description FROM Dual Union All
Select 2002 As CandidateID, 'Geologist' AS Description FROM Dual Union All
Select 2002 As CandidateID, 'Machinist' AS Description FROM Dual Union All
Select 3003 As CandidateID, 'Cryologist' AS Description FROM Dual Union All
Select 4004 As CandidateID, 'Selenologist' AS Description FROM Dual
)

SELECT CandidateID FROM (
SELECT CandidateID, Description
FROM temp
WHERE Description IN ('Geologist', 'Astrogator', 'Technician'))
GROUP BY CandidateID
HAVING COUNT(CandidateID) = 3;

-- Alternative Solution
SELECT CandidateID FROM (
SELECT *
FROM temp a INNER JOIN
 req b ON a.Description = b.Requirement)
 GROUP BY CandidateID
 HAVING COUNT(CandidateID) = 3;