Sql questions Question 1

SQL Questions

Question 1:

Your customer phone directory table allows individuals to set up a home, cellular, or work phone number. Write an SQL statement to transform the following table into the expected output.

DDL:
DROP TABLE IF EXISTS #PhoneDirectory;
CREATE TABLE #PhoneDirectory
(
CustomerID INTEGER,
[Type] VARCHAR(100),
PhoneNumber VARCHAR(12) NOT NULL,
PRIMARY KEY (CustomerID, [Type])
);

INSERT INTO #PhoneDirectory (CustomerID, [Type], PhoneNumber) VALUES

(1001,'Cellular','555-897-5421'),
(1001,'Work','555-897-6542'),
(1001,'Home','555-698-9874'),
(2002,'Cellular','555-963-6544'),
(2002,'Work','555-812-9856'),
(3003,'Cellular','555-987-6541');

Question 2:

Write an SQL statement that determines all workflows that have started but have not been completed.

The expected output would be Bravo and Charlie, as they have a workflow that has started but has not been completed.
DDL:
DROP TABLE IF EXISTS #WorkflowSteps;
CREATE TABLE #WorkflowSteps
(
Workflow VARCHAR(100),
StepNumber INTEGER,
CompletionDate DATE NULL,
PRIMARY KEY (Workflow, StepNumber)
);

INSERT INTO #WorkflowSteps (Workflow, StepNumber, CompletionDate)

VALUES
('Alpha',1,'7/2/2018'),('Alpha',2,'7/2/2018'),('Alpha',3,'7/1/2018'),
('Bravo',1,'6/25/2018'),('Bravo',2,NULL),('Bravo',3,'6/27/2018'),
('Charlie',1,NULL),('Charlie',2,'7/1/2018');

Question 3:

Write an SQL statement to determine the average number of days between executions for each workflow.

DDL:
DROP TABLE IF EXISTS #ProcessLog;

CREATE TABLE #ProcessLog

(
Workflow VARCHAR(100),
ExecutionDate DATE,
PRIMARY KEY (Workflow, ExecutionDate)
);

INSERT INTO #ProcessLog (Workflow, ExecutionDate)

VALUES
('Alpha','6/01/2018'),('Alpha','6/14/2018'),('Alpha','6/15/2018'),
('Bravo','6/1/2018'),('Bravo','6/2/2018'),('Bravo','6/19/2018'),
('Charlie','6/1/2018'),('Charlie','6/15/2018'),('Charlie','6/30/2018');