August 7, 2008 at 1:53 pm
TBL: ADC
Column NameData type
ID1Int, NOT Null
ID2Int, NOT Null
Beg_dtDate
End_dtDate
Using table above: Write a query to make sure that the beg_dt and end_dt of a row does not have logical conflict with the beg_dt and end_dt .
Answer:
SELECT * FROM ADC WHERE Beg_dt != End_dt
ETL: The transactions are inserted into a T_staging table. Transformation is needed base on the begin date (beg_dt) and end date (end_dt) columns.
The column vstatus in the T_staging table must be updated with the result of
the validation, so that the appropriate corrective action, if needed, may
be taken. Valid entries in the vstatus column and their corresponding
meanings are as follows:
1 - the begin and end dates are the same.
A - the begin date is invalid because it is null
B - the end date is invalid because it is null
C - both dates are invalid because they are null
E - the end date is earlier than the begin date
V - both dates are valid
Answer: I do not know
Rewrite the following statement to achieve the same result.
select *,
datediff(day,a.BEG_DT,b.BEG_DT) bdate,
datediff(day,a.END_DT,b.END_DT) edate
from ADC a
join ADC b
on a.keyone = b.keyone
and a.keytwo = b.keytwo
and a.BEG_DT between b.BEG_DT and b.END_DT
or a.keyone = b.keyone
and a.keytwo = b.keytwo
and a.END_DT between b.BEG_DT and b.END_DT
where datediff(day,a.BEG_DT,b.BEG_DT) <> 0
or datediff(day,a.END_DT,b.END_DT) <> 0
Answer:
select *,
datediff(day,a.BEG_DT,b.BEG_DT) bdate,
datediff(day,a.END_DT,b.END_DT) edate
from ADC a
Inner join ADC b
on (a.keyone = b.keyone
and a.keytwo = b.keytwo
and a.BEG_DT between b.BEG_DT and b.END_DT)
or (a.keyone = b.keyone
and a.keytwo = b.keytwo
and a.END_DT between b.BEG_DT and b.END_DT)
where (datediff(day,a.BEG_DT,b.BEG_DT) <> 0
or datediff(day,a.END_DT,b.END_DT) <> 0 )
Correct the following statements
Select Count(*), company, Sum(dlrs), Item from T_items
Answer:
Select Count(*), company, Sum(dlrs), Item from T_items
Group By Company
T_VOL_USAGE
VOL_nameNODE-NameSIZE
V1B100MB
V12F100KB
V3F50 B
V2C500B
V2D39KB
V3E200 KB
Write a query that will eliminate the many to many relationship and resulting in V1, B ignoring the Size column
Answer: I do not know!!!! 🙁
August 8, 2008 at 1:59 am
Is this for a test, homework, an interview or similar?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 24, 2008 at 3:58 pm
Yeah, I, also, have problems with understanding this test written in Chinglish :(.
flammen_dude (8/7/2008)
TBL: ADCColumn NameData type
ID1Int, NOT Null
ID2Int, NOT Null
Beg_dtDate
End_dtDate
Using table above: Write a query to make sure that the beg_dt and end_dt of a row does not have logical conflict with the beg_dt and end_dt .
Answer:
SELECT * FROM ADC WHERE Beg_dt != End_dt
What is considered a "logical conflict" ? beg_dt should be less than end_dt (guessing from column names) or what ? What do they mean by "make sure": to perform a check for "logical conflict" and output something like "Conflict detected !" for the rows, which have "logical conflict" and "No conflict !" for the rows, which don't ? Or, they want me to prevent inserting data with "logical conflict" into the table ? Finally, they want to check "that the beg_dt and end_dt of a row does not have logical conflict with the beg_dt and end_dt". How could something have a "logical conflict" with itself ?
My take on this question was:
SELECT
CASE
WHEN (beg_dt <= end_dt)
THEN ‘data rulezzz !!!’
ELSE ‘data suxxx !!!’
END
FROM ADC
flammen_dude (8/7/2008)
ETL: The transactions are inserted into a T_staging table. Transformation is needed base on the begin date (beg_dt) and end date (end_dt) columns.The column vstatus in the T_staging table must be updated with the result of
the validation, so that the appropriate corrective action, if needed, may
be taken. Valid entries in the vstatus column and their corresponding
meanings are as follows:
1 - the begin and end dates are the same.
A - the begin date is invalid because it is null
B - the end date is invalid because it is null
C - both dates are invalid because they are null
E - the end date is earlier than the begin date
V - both dates are valid
Answer: I do not know
My version was:
UPDATE T_staging SET
vstatus = CASE
WHEN beg_dt = end_dt
THEN ‘1’
WHEN beg_dt IS NULL AND end_dt IS NOT NULL
THEN ‘A’
WHEN beg_dt IS NOT NULL AND end_dt IS NULL
THEN ‘B’
WHEN beg_dt IS NULL AND end_dt IS NULL
THEN ‘C’
WHEN end_dt < beg_dt
THEN ‘E’
WHEN ISDATE(beg_dt) = 1 AND ISDATE(end_dt) = 1
THEN ‘V’
END
FROM T_staging
flammen_dude (8/7/2008)
Correct the following statementsSelect Count(*), company, Sum(dlrs), Item from T_items
Answer:
Select Count(*), company, Sum(dlrs), Item from T_items
Group By Company
Actually, you have to drop Item column from the select list, because it's neither in GROUP BY clause, nor is inside some aggregate function. It simply will not compile.
flammen_dude (8/7/2008)
T_VOL_USAGEVOL_nameNODE-NameSIZE
V1B100MB
V12F100KB
V3F50 B
V2C500B
V2D39KB
V3E200 KB
Write a query that will eliminate the many to many relationship and resulting in V1, B ignoring the Size column
Answer: I do not know!!!! 🙁
I failed to understand this question as well :(. What is a many-to-many relationship in the scope of one table ? How a relationship could be "eliminated" ? How to translate "resulting in V1, B ignoring the Size column" into plain English ?
I've been sent this test two times from different recruiters and I asked them these questions, but never heard back from them :(.
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply