Sql questions Question 1


Download 67.6 Kb.
bet1/4
Sana31.01.2024
Hajmi67.6 Kb.
#1818450
  1   2   3   4
Bog'liq
6afac0bf-38b2-49c5-88da-a3701e407493 5 puzzles


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');

Download 67.6 Kb.

Do'stlaringiz bilan baham:
  1   2   3   4




Ma'lumotlar bazasi mualliflik huquqi bilan himoyalangan ©fayllar.org 2024
ma'muriyatiga murojaat qiling