Refer Answers Page for Answer and Explanation .
Question 01 :
Write an sql query which will fetch the below details based on Specified date (<>1900)
1) Day of the Year (Sunday,Monday etc…).
2) Based on that Business day or not .
Question 02 :
We have a dependent schedule in Teradata which one TPT load is made to run after Mload Success .
The TPT job got failed in the middle . Kindly find the below parameters
Mload entities :
Target table Name : CI_CCME_UFP_MLoad_First_quater
Log Table Name : CI_CCME_UFP_MLoad_First_quater
UV Table Name : CI_CCME_UFP_MLoad_First_quater_UV
ET table Name : CI_CCME_UFP_MLoad_First_quater_ET
TPT Entities :
Target table Name : CI_CCME_UFP_Load_First_quater_Load_01
Log Table Name : CI_CCME_UFP_Load_First_quater_Load_01_log
UV Table Name : CI_CCME_UFP_Load_First_quater_Load_01_UV
ET table Name : CI_CCME_UFP_Load_First_quater_Load_01_ET
While we try to drop the UV and ET table it says “ Table not found “.
But while we try to run the job it says “ Table Already exists “.
Kindly let me know your approach for the resolution and make TPT execute successfully.
Question 03 :
When you have a timesheet, you often need to merge contiguous or overlapping time periods.
CREATE TABLE Timesheets (task_id CHAR(10) NOT NULL , start_date DATE NOT NULL, end_date DATE NOT NULL);
INSERT INTO Timesheets VALUES (1, '1997-01-01', '1997-01-03'), (2, '1997-01-02', '1997-01-04'), (3, '1997-01-04', '1997-01-05'), (4, '1997-01-06', '1997-01-09'), (5, '1997-01-09', '1997-01-09'), (6, '1997-01-09', '1997-01-09'), (7, '1997-01-12', '1997-01-15'), (8, '1997-01-13', '1997-01-14'), (9, '1997-01-14', '1997-01-14'), (10, '1997-01-17', '1997-01-17');
Sample Input :
2, '1997-01-02', '1997-01-04'
3, '1997-01-04', '1997-01-05'
Expected Output :
2 '1997-01-02' '1997-01-05'
Question 04 :
Kindly find the below sample input and output. Frame a Query to give the result as sample output.
Kindly Note that , You need to give the working query which produces exact output.
Sample Input :
TID Date TotalLines Notworking CustomerCalls
-------- ---------- ---------- ----------------- -------------
111 2010-04-05 345 5 100
111 2010-04-06 345 5 80
111 2010-04-07 120 4 50
111 2010-04-08 345 5 60
111 2010-04-09 345 5 40
111 2010-04-10 345 5 70
111 2010-04-11 117 20 300
111 2010-04-12 345 5 55
111 2010-04-14 345 5 75
111 2010-04-15 260 10 150
111 2010-04-16 345 5 30
111 2010-04-17 345 5 95
111 2010-04-19 345 5 60
Sample Output :
TID Start Date End Date TotalLines Notworking TotalCalls
--------- ---------- ---------- ---------- ----------------- ------------------
111 2010-04-05 2010-04-06 345 5 180
111 2010-04-07 2010-04-07 120 4 50
111 2010-04-08 2010-04-10 345 5 170
111 2010-04-11 2010-04-11 117 20 300
111 2010-04-12 2010-04-14 345 5 130
111 2010-04-15 2010-04-15 260 10 150
111 2010-04-16 2050-12-01 345 5 185
DDL and DML used :
CREATE TABLE SAMPLETP(
ProductID INTEGER NOT NULL,
Date TIMESTAMP(0) NOT NULL,
TotalLines INTEGER ,
LinesOutOfService INTEGER ,
CustomerCalls INTEGER
)
INSERT INTO SAMPLETP (TID ,Date,TotalLines, Notworking, TotalCalls)
SELECT 111,'2010-04-05',345,5,100 UNION ALL
SELECT 111,'2010-04-06',345,5,80 UNION ALL
SELECT 111,'2010-04-07',120,4,50 UNION ALL
SELECT 111,'2010-04-08',345,5,60 UNION ALL
SELECT 111,'2010-04-09',345,5,40 UNION ALL
SELECT 111,'2010-04-10',345,5,70 UNION ALL
SELECT 111,'2010-04-11',117,20,300 UNION ALL
SELECT 111,'2010-04-12',345,5,55 UNION ALL
SELECT 111,'2010-04-14',345,5,75 UNION ALL
SELECT 111,'2010-04-15',260,10,150 UNION ALL
SELECT 111,'2010-04-16',345,5,30 UNION ALL
SELECT 111,'2010-04-17',345,5,95 UNION ALL
SELECT 111,'2010-04-19',345,5,60
Question 5:
Sample Input :
col1 col2
1 AB:123|BC:NA|CD:IND
2 BC:123|AD:NA|HA:IND | MUL : SAM Etc..
Sample Output :
col1 col2 col3
1 AB 123
1 BC NA
1 CD IND
2 BC 123
2 AD NA
2 HA IND
2 MUL SAM
Question 6:
col1 col2 col3 col4
1 AB 123 2
1 BC NA 3
1 CD IND 1
2 BC 123 6
2 AD NA 7
2 HA IND 8
2 MUL SAM 9
Etc..
Sample Output :
col1 col2 Col3
1 AB:123|BC:NA|CD:IND 6
2 BC:123|AD:NA|HA:IND | MUL : SAM 30 Etc..
Question 7:
Sentence_id Text StringPosition
-- -------------------------------------------------------- ------------
1 'Hello this is teradata tech forum ...' 10
2 'We are working for CCR Coke Project ' 20
3 'We work as a team for desired results to make client satisfy 17
Desired Output :
Sentence_id Sub_ID Text
-- ------ --------------------------------------------------------
1 1 Hello this
1 2 is teradat
1 3 a tech for
1 4 um...
2 1 We are working for CCR
2 2 Coke Project
3 1 We work as a team
3 2 for desired resul
3 3 ts to make client
3 4 satisfy
DDL and DML used :
CREATE TABLE Sample (
Sentence_id Integer ,
Text VARCHAR(500),
StringPosition Integer
)
INSERT INTO Sample(Sentence_id , Text, StringPosition)
SELECT 1,'Hello this is teradata tech forum ...',10 UNION ALL
SELECT 2,'We are working for CCR Coke Project ',20 UNION ALL
SELECT 3,'We work as a team for desired results to make client satisfy ',17
The query should dynamically handle even if the values of column rows are added in input increases.
Question 8:
Sentence_id Text StringPosition
-- -------------------------------------------------------- ------------
1 'Hello this is teradata tech forum ...' 10
2 'We are working for CCR Coke Project ' 20
3 'We work as a team for desired results to make client satisfy 17
Desired Output :
Sentence_id Sub_ID Text
-- ------ --------------------------------------------------------
1 1 Hello this
1 2 is teradat
1 3 a tech for
1 4 um...
2 1 We are working for CCR
2 2 Coke Project
3 1 We work as a team
3 2 for desired resul
3 3 ts to make client
3 4 satisfy
DDL and DML used :
CREATE TABLE Sample (
Sentence_id Integer ,
Text VARCHAR(500),
StringPosition Integer
)
INSERT INTO Sample(Sentence_id , Text, StringPosition)
SELECT 1,'Hello this is teradata tech forum ...',10 UNION ALL
SELECT 2,'We are working for CCR Coke Project ',20 UNION ALL
SELECT 3,'We work as a team for desired results to make client satisfy ',17
Question :9
Sample Data :
AuditId BookingId Transaction TransactionType TransactionValue
---- --------- ------------------- --------------- ----------------
1 1 2010-01-01 S 100
2 1 2010-01-01 P 100
3 2 2010-01-01 S 100
4 2 2010-01-01 P 50
5 3 2010-01-02 P 10
6 3 2010-01-01 S 100
7 3 2010-01-01 P 50
8 3 2010-01-01 P 10
9 4 2010-01-01 S 100
10 4 2010-01-01 P 25
11 4 2010-01-01 P 75
Here in TransactionType S stands for Sale and P stands for Payment , the result is likely to be the total amount of sale and total amount of payment on same day must be pulled out.
Total amount of S+P on same day must be equal .
Expected output :
bookingid transactiondate
--------- ---------------
1 20100101
4 20100101
Question :10
Please find this week Question below
Sample Input :
Sentence_id Text StringPosition
-- -------------------------------------------------------- ------------
1 Hello this is teradata tech forum ... 10
1 We are working for CCR Coke Project 20
2 We work as a team for desired results to make client satisfy 17
2 concatenating part of the line with part of another line 11
OUTPUT:
1 Hello thisWe are working for C
2 We work as a teamconcatenati
CREATE VOLATILE TABLE CONCAT_PART
(
SENTENCE_ID INTEGER,
TEXT VARCHAR(250),
STRINGPOSITION INTEGER
)
ON COMMIT PRESERVE ROWS;
INSERT CONCAT_PART(1, 'Hello this is teradata tech forum ... ', 10 );
INSERT CONCAT_PART(1, 'We are working for CCR Coke Project ', 20 );
INSERT CONCAT_PART(2, 'We work as a team for desired results to make client satisfy ', 17 );
INSERT CONCAT_PART(2, 'concatenating part of the line with part of another line ', 11 );
Question :11
Sample Input :
col1 col2
1 AB:123|BC:NA|CD:IND
2 BC:123|AD:NA|HA:IND | MUL : SAM Etc..
Sample Output :
col1 col2 col3
1 AB 123
1 BC NA
1 CD IND
2 BC 123
2 AD NA
2 HA IND
2 MUL SAM
Etc..
The query should dynamically handle even if the Values of column 2 increases.
Question 12:
Sample Input :
Id color1 color2 color3
1 red blue green
2 red pink green
3 green pink red
4 red green blue
5 pink blue red
6 green blue red
7 pink orange red
Sample Output :
Id color1 color2 color3
1 red blue green
2 red pink green
3 green pink red
4 red green blue
6 green blue red
Question 13:
Table 1 :
ID StartDate EndDate
----------- ----------------------- -----------------------
1 2009-03-02 08:00:00.000 2009-03-02 15:00:00.000
2 2009-03-01 16:00:00.000 2009-03-03 14:00:00.000
3 2009-02-26 07:00:00.000 2009-02-26 22:00:00.000
4 2009-01-27 09:15:00.000 2009-01-27 17:15:00.000
5 2009-01-17 13:45:00.000 2009-01-19 07:45:00.000
6 2009-01-27 21:15:00.000 2009-01-28 09:15:00.000
Output :
ID StartDate EndDate No of Active Hours Status
----------- ----------------------- ----------------------- ------------- -----------------------
1 2009-03-02 08:00:00.000 2009-03-02 15:00:00.000
2 2009-03-01 16:00:00.000 2009-03-03 14:00:00.000
3 2009-02-26 07:00:00.000 2009-02-26 22:00:00.000
4 2009-01-27 09:15:00.000 2009-01-27 17:15:00.000
5 2009-01-17 13:45:00.000 2009-01-19 07:45:00.000
6 2009-01-27 21:15:00.000 2009-01-28 09:15:00.000
Where No of Active hours is the time between 12 PM to 6 PM ,Status is eligible if the Employee stays for more than 3 hours else not eligible .
Question 14:
Hotel details table
Hotel_id Hotel_Name latitude Longitude
1, ABC, -116.0345,35.3673
2, xYZ ,-115.5597,34.7393
3, A2b, -117.2278,33.1397
4, MTR, -116.8905,31.5309
METRO
MetroName Latitude Longitude
ABB , -117.7627,34.2326
zzz ,-116.7558,31.8009
Question;
To find the nearest metro for all the hotels , Hotel table contains millions of record but Metro has only 1K record. Please provide a efficient query approach
Question 01 :
Write an sql query which will fetch the below details based on Specified date (<>1900)
1) Day of the Year (Sunday,Monday etc…).
2) Based on that Business day or not .
Question 02 :
We have a dependent schedule in Teradata which one TPT load is made to run after Mload Success .
The TPT job got failed in the middle . Kindly find the below parameters
Mload entities :
Target table Name : CI_CCME_UFP_MLoad_First_quater
Log Table Name : CI_CCME_UFP_MLoad_First_quater
UV Table Name : CI_CCME_UFP_MLoad_First_quater_UV
ET table Name : CI_CCME_UFP_MLoad_First_quater_ET
TPT Entities :
Target table Name : CI_CCME_UFP_Load_First_quater_Load_01
Log Table Name : CI_CCME_UFP_Load_First_quater_Load_01_log
UV Table Name : CI_CCME_UFP_Load_First_quater_Load_01_UV
ET table Name : CI_CCME_UFP_Load_First_quater_Load_01_ET
While we try to drop the UV and ET table it says “ Table not found “.
But while we try to run the job it says “ Table Already exists “.
Kindly let me know your approach for the resolution and make TPT execute successfully.
Question 03 :
When you have a timesheet, you often need to merge contiguous or overlapping time periods.
CREATE TABLE Timesheets (task_id CHAR(10) NOT NULL , start_date DATE NOT NULL, end_date DATE NOT NULL);
INSERT INTO Timesheets VALUES (1, '1997-01-01', '1997-01-03'), (2, '1997-01-02', '1997-01-04'), (3, '1997-01-04', '1997-01-05'), (4, '1997-01-06', '1997-01-09'), (5, '1997-01-09', '1997-01-09'), (6, '1997-01-09', '1997-01-09'), (7, '1997-01-12', '1997-01-15'), (8, '1997-01-13', '1997-01-14'), (9, '1997-01-14', '1997-01-14'), (10, '1997-01-17', '1997-01-17');
Sample Input :
2, '1997-01-02', '1997-01-04'
3, '1997-01-04', '1997-01-05'
Expected Output :
2 '1997-01-02' '1997-01-05'
Question 04 :
Kindly find the below sample input and output. Frame a Query to give the result as sample output.
Kindly Note that , You need to give the working query which produces exact output.
Sample Input :
TID Date TotalLines Notworking CustomerCalls
-------- ---------- ---------- ----------------- -------------
111 2010-04-05 345 5 100
111 2010-04-06 345 5 80
111 2010-04-07 120 4 50
111 2010-04-08 345 5 60
111 2010-04-09 345 5 40
111 2010-04-10 345 5 70
111 2010-04-11 117 20 300
111 2010-04-12 345 5 55
111 2010-04-14 345 5 75
111 2010-04-15 260 10 150
111 2010-04-16 345 5 30
111 2010-04-17 345 5 95
111 2010-04-19 345 5 60
Sample Output :
TID Start Date End Date TotalLines Notworking TotalCalls
--------- ---------- ---------- ---------- ----------------- ------------------
111 2010-04-05 2010-04-06 345 5 180
111 2010-04-07 2010-04-07 120 4 50
111 2010-04-08 2010-04-10 345 5 170
111 2010-04-11 2010-04-11 117 20 300
111 2010-04-12 2010-04-14 345 5 130
111 2010-04-15 2010-04-15 260 10 150
111 2010-04-16 2050-12-01 345 5 185
DDL and DML used :
CREATE TABLE SAMPLETP(
ProductID INTEGER NOT NULL,
Date TIMESTAMP(0) NOT NULL,
TotalLines INTEGER ,
LinesOutOfService INTEGER ,
CustomerCalls INTEGER
)
INSERT INTO SAMPLETP (TID ,Date,TotalLines, Notworking, TotalCalls)
SELECT 111,'2010-04-05',345,5,100 UNION ALL
SELECT 111,'2010-04-06',345,5,80 UNION ALL
SELECT 111,'2010-04-07',120,4,50 UNION ALL
SELECT 111,'2010-04-08',345,5,60 UNION ALL
SELECT 111,'2010-04-09',345,5,40 UNION ALL
SELECT 111,'2010-04-10',345,5,70 UNION ALL
SELECT 111,'2010-04-11',117,20,300 UNION ALL
SELECT 111,'2010-04-12',345,5,55 UNION ALL
SELECT 111,'2010-04-14',345,5,75 UNION ALL
SELECT 111,'2010-04-15',260,10,150 UNION ALL
SELECT 111,'2010-04-16',345,5,30 UNION ALL
SELECT 111,'2010-04-17',345,5,95 UNION ALL
SELECT 111,'2010-04-19',345,5,60
Question 5:
Sample Input :
col1 col2
1 AB:123|BC:NA|CD:IND
2 BC:123|AD:NA|HA:IND | MUL : SAM Etc..
Sample Output :
col1 col2 col3
1 AB 123
1 BC NA
1 CD IND
2 BC 123
2 AD NA
2 HA IND
2 MUL SAM
Question 6:
col1 col2 col3 col4
1 AB 123 2
1 BC NA 3
1 CD IND 1
2 BC 123 6
2 AD NA 7
2 HA IND 8
2 MUL SAM 9
Etc..
Sample Output :
col1 col2 Col3
1 AB:123|BC:NA|CD:IND 6
2 BC:123|AD:NA|HA:IND | MUL : SAM 30 Etc..
Question 7:
Sentence_id Text StringPosition
-- -------------------------------------------------------- ------------
1 'Hello this is teradata tech forum ...' 10
2 'We are working for CCR Coke Project ' 20
3 'We work as a team for desired results to make client satisfy 17
Desired Output :
Sentence_id Sub_ID Text
-- ------ --------------------------------------------------------
1 1 Hello this
1 2 is teradat
1 3 a tech for
1 4 um...
2 1 We are working for CCR
2 2 Coke Project
3 1 We work as a team
3 2 for desired resul
3 3 ts to make client
3 4 satisfy
DDL and DML used :
CREATE TABLE Sample (
Sentence_id Integer ,
Text VARCHAR(500),
StringPosition Integer
)
INSERT INTO Sample(Sentence_id , Text, StringPosition)
SELECT 1,'Hello this is teradata tech forum ...',10 UNION ALL
SELECT 2,'We are working for CCR Coke Project ',20 UNION ALL
SELECT 3,'We work as a team for desired results to make client satisfy ',17
The query should dynamically handle even if the values of column rows are added in input increases.
Question 8:
Sentence_id Text StringPosition
-- -------------------------------------------------------- ------------
1 'Hello this is teradata tech forum ...' 10
2 'We are working for CCR Coke Project ' 20
3 'We work as a team for desired results to make client satisfy 17
Desired Output :
Sentence_id Sub_ID Text
-- ------ --------------------------------------------------------
1 1 Hello this
1 2 is teradat
1 3 a tech for
1 4 um...
2 1 We are working for CCR
2 2 Coke Project
3 1 We work as a team
3 2 for desired resul
3 3 ts to make client
3 4 satisfy
DDL and DML used :
CREATE TABLE Sample (
Sentence_id Integer ,
Text VARCHAR(500),
StringPosition Integer
)
INSERT INTO Sample(Sentence_id , Text, StringPosition)
SELECT 1,'Hello this is teradata tech forum ...',10 UNION ALL
SELECT 2,'We are working for CCR Coke Project ',20 UNION ALL
SELECT 3,'We work as a team for desired results to make client satisfy ',17
Question :9
Sample Data :
AuditId BookingId Transaction TransactionType TransactionValue
---- --------- ------------------- --------------- ----------------
1 1 2010-01-01 S 100
2 1 2010-01-01 P 100
3 2 2010-01-01 S 100
4 2 2010-01-01 P 50
5 3 2010-01-02 P 10
6 3 2010-01-01 S 100
7 3 2010-01-01 P 50
8 3 2010-01-01 P 10
9 4 2010-01-01 S 100
10 4 2010-01-01 P 25
11 4 2010-01-01 P 75
Here in TransactionType S stands for Sale and P stands for Payment , the result is likely to be the total amount of sale and total amount of payment on same day must be pulled out.
Total amount of S+P on same day must be equal .
Expected output :
bookingid transactiondate
--------- ---------------
1 20100101
4 20100101
Question :10
Please find this week Question below
Sample Input :
Sentence_id Text StringPosition
-- -------------------------------------------------------- ------------
1 Hello this is teradata tech forum ... 10
1 We are working for CCR Coke Project 20
2 We work as a team for desired results to make client satisfy 17
2 concatenating part of the line with part of another line 11
OUTPUT:
1 Hello thisWe are working for C
2 We work as a teamconcatenati
CREATE VOLATILE TABLE CONCAT_PART
(
SENTENCE_ID INTEGER,
TEXT VARCHAR(250),
STRINGPOSITION INTEGER
)
ON COMMIT PRESERVE ROWS;
INSERT CONCAT_PART(1, 'Hello this is teradata tech forum ... ', 10 );
INSERT CONCAT_PART(1, 'We are working for CCR Coke Project ', 20 );
INSERT CONCAT_PART(2, 'We work as a team for desired results to make client satisfy ', 17 );
INSERT CONCAT_PART(2, 'concatenating part of the line with part of another line ', 11 );
Question :11
Sample Input :
col1 col2
1 AB:123|BC:NA|CD:IND
2 BC:123|AD:NA|HA:IND | MUL : SAM Etc..
Sample Output :
col1 col2 col3
1 AB 123
1 BC NA
1 CD IND
2 BC 123
2 AD NA
2 HA IND
2 MUL SAM
Etc..
The query should dynamically handle even if the Values of column 2 increases.
Question 12:
Sample Input :
Id color1 color2 color3
1 red blue green
2 red pink green
3 green pink red
4 red green blue
5 pink blue red
6 green blue red
7 pink orange red
Sample Output :
Id color1 color2 color3
1 red blue green
2 red pink green
3 green pink red
4 red green blue
6 green blue red
Question 13:
Table 1 :
ID StartDate EndDate
----------- ----------------------- -----------------------
1 2009-03-02 08:00:00.000 2009-03-02 15:00:00.000
2 2009-03-01 16:00:00.000 2009-03-03 14:00:00.000
3 2009-02-26 07:00:00.000 2009-02-26 22:00:00.000
4 2009-01-27 09:15:00.000 2009-01-27 17:15:00.000
5 2009-01-17 13:45:00.000 2009-01-19 07:45:00.000
6 2009-01-27 21:15:00.000 2009-01-28 09:15:00.000
Output :
ID StartDate EndDate No of Active Hours Status
----------- ----------------------- ----------------------- ------------- -----------------------
1 2009-03-02 08:00:00.000 2009-03-02 15:00:00.000
2 2009-03-01 16:00:00.000 2009-03-03 14:00:00.000
3 2009-02-26 07:00:00.000 2009-02-26 22:00:00.000
4 2009-01-27 09:15:00.000 2009-01-27 17:15:00.000
5 2009-01-17 13:45:00.000 2009-01-19 07:45:00.000
6 2009-01-27 21:15:00.000 2009-01-28 09:15:00.000
Where No of Active hours is the time between 12 PM to 6 PM ,Status is eligible if the Employee stays for more than 3 hours else not eligible .
Question 14:
Hotel details table
Hotel_id Hotel_Name latitude Longitude
1, ABC, -116.0345,35.3673
2, xYZ ,-115.5597,34.7393
3, A2b, -117.2278,33.1397
4, MTR, -116.8905,31.5309
METRO
MetroName Latitude Longitude
ABB , -117.7627,34.2326
zzz ,-116.7558,31.8009
Question;
To find the nearest metro for all the hotels , Hotel table contains millions of record but Metro has only 1K record. Please provide a efficient query approach
2 Comments