May 9, 2013 at 1:34 pm
Hello All,
I was prescreened for a Senior SQL Program Analyst position and one of the questions asked is shown below. I have been around SQL quite a while but this question doesn't make sense to me. Can anyone help me decipher and understand what the correct answer is and how you got to it? I know this isn't a interview question/answer forum but the subject matter is definitely T-SQL and the correct use of it.
6. Field f of a table is indexed. Which of the following query fragments is written so the index cannot be used? (Assume p1 and p2 are the correct data types for their role in each fragment.)
a. where f > :p1
b. where f between :p1 and dateadd( day, :p2, :p1)
c. where dateadd( day, :p2 , f) > :p1
d. where dateadd( day, :p2, :p1) < f
e. where f not in( select …)
Thanks
A clever person solves a problem. A wise person avoids it. ~ Einstein
select cast (0x5365616E204465596F756E67 as varchar(128))
May 9, 2013 at 1:39 pm
And below are the rest of the questions. BTW, I took myself out of being considered for candidacy for the role so I am not be considered for the position any longer.
1. Name three simple aggregate functions: _________, ________, ________
Answers (any three): Sum, Count, Avg, Min, Max, Stddev (variations: Stddev_XXXX), Variance (variations: Var_XXXX)
2. Table Spending has a foreign key reference defined to Customers. Which of the following are true statements?
a. No row can exist in Spending without a corresponding row in Customers with the same key value.
b. No row can exist in Customers without a corresponding row in Spending with the same key value.
c. No row can be deleted in Spending without first deleting corresponding row(s) in Customers.
d. No row can be deleted in Customers without first deleting corresponding row(s) in Spending.
e. There must be one or more rows in Customers with the same key value as each row in Spending.
f. There may be zero but no more than one row in Spending with the same key value as each row in Customers.
Answer: e
3. We must generate a list of customers who have spent $30,000 or more in the last 30 days. All the information needed is in a table that has a separate entry for every transaction made by each customer. Which, if any, of the following clauses must be part of the query?
a. group by cust_id
b. where sum(amt_spent) >= 30000
c. having sum(amt_spent) >= 30000
d. where transaction_date between now() and now() – 29
Answer: a, c, d
4. Give a brief explanation of the differences between the following statements.
a. drop table table_name;
b. delete table_name;
c. truncate table table_name;
Answer: A: removes the table and all contents from the database. B: removes the contents of the table but the table remains. C: same as B except it cannot be rolled back.
5. A query is generating a compile error. We’ve narrowed the problem to the following fragment. What could be wrong with it?
select coalesce( a.phone_number, b.birth_date) as Answer from …;
Answer: The second and any subsequent arguments to coalesce must be the same data type as the first argument.
6. Field f of a table is indexed. Which of the following query fragments is written so the index cannot be used? (Assume p1 and p2 are the correct data types for their role in each fragment.)
a. where f > :p1
b. where f between :p1 and dateadd( day, :p2, :p1)
c. where dateadd( day, :p2 , f) > :p1
d. where dateadd( day, :p2, :p1) < f
e. where f not in( select …)
Answer: c
7. What is the difference between UNION and UNION ALL?
Answer: UNION performs a set merge where duplicate rows are discarded. UNION ALL concatenates all rows together with no regard to duplicates.
8. What are the two most significant differences between a primary key and a unique key?
Answer: 1) The primary key has the additional constraint of NOT NULL. 2) There can be only one primary key per table but there may be many unique keys.
9. Which use of subquery is correct and why?
a. where f in (select a from tab1)
b. where f = (select a from tab1)
Answer: a
10. What might be done to make the incorrect subquery above executable? Change the subquery, not the WHERE clause.
Answers: (any are fine)
a. where f = (select max(a) from tab1)
b. where f = (select min(a) from tab1)
c. where f = (select avg(a) from tab1)
d. Any change which makes the subquery return no more than one result.
A clever person solves a problem. A wise person avoids it. ~ Einstein
select cast (0x5365616E204465596F756E67 as varchar(128))
May 9, 2013 at 2:01 pm
TeraByteMe (5/9/2013)
Hello All,I was prescreened for a Senior SQL Program Analyst position and one of the questions asked is shown below. I have been around SQL quite a while but this question doesn't make sense to me. Can anyone help me decipher and understand what the correct answer is and how you got to it? I know this isn't a interview question/answer forum but the subject matter is definitely T-SQL and the correct use of it.
6. Field f of a table is indexed. Which of the following query fragments is written so the index cannot be used? (Assume p1 and p2 are the correct data types for their role in each fragment.)
a. where f > :p1
b. where f between :p1 and dateadd( day, :p2, :p1)
c. where dateadd( day, :p2 , f) > :p1
d. where dateadd( day, :p2, :p1) < f
e. where f not in( select …)
Thanks
They are trying to see if you understand SARGability. The correct answer here would be C. The way that is written it will have to compute the dateadd for every single row. All 4 of the other predicates and SARGable.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
May 9, 2013 at 2:12 pm
TeraByteMe (5/9/2013)
And below are the rest of the questions. BTW, I took myself out of being considered for candidacy for the role so I am not be considered for the position any longer.1. Name three simple aggregate functions: _________, ________, ________
Answers (any three): Sum, Count, Avg, Min, Max, Stddev (variations: Stddev_XXXX), Variance (variations: Var_XXXX)
Looks good
2. Table Spending has a foreign key reference defined to Customers. Which of the following are true statements?
a. No row can exist in Spending without a corresponding row in Customers with the same key value.
b. No row can exist in Customers without a corresponding row in Spending with the same key value.
c. No row can be deleted in Spending without first deleting corresponding row(s) in Customers.
d. No row can be deleted in Customers without first deleting corresponding row(s) in Spending.
e. There must be one or more rows in Customers with the same key value as each row in Spending.
f. There may be zero but no more than one row in Spending with the same key value as each row in Customers.
Answer: e
Unless I am reading the question wrong, my answer would be a & d
3. We must generate a list of customers who have spent $30,000 or more in the last 30 days. All the information needed is in a table that has a separate entry for every transaction made by each customer. Which, if any, of the following clauses must be part of the query?
a. group by cust_id
b. where sum(amt_spent) >= 30000
c. having sum(amt_spent) >= 30000
d. where transaction_date between now() and now() – 29
Answer: a, c, d
Looks good to me
4. Give a brief explanation of the differences between the following statements.
a. drop table table_name;
b. delete table_name;
c. truncate table table_name;
Answer: A: removes the table and all contents from the database. B: removes the contents of the table but the table remains. C: same as B except it cannot be rolled back.
your answer for A and B are correct but your answer on C is incorrect. A truncate absolutely can be rolled back. If it couldn't the database would be able to support ATOMicity. This is easy to demonstrate.
create table TruncateTest
(
MyID int identity
)
go
insert TruncateTest default values
go 10
select * from TruncateTest
begin transaction
truncate table TruncateTest
rollback transaction
select * from TruncateTest
drop table TruncateTest
The difference between delete and truncate is that delete is a dml operation that is fully logged and truncate is DDL operation and is minimally logged. What that means is the rows are logged in a delete but the page deallocation is logged for a truncate.
#6 was in your first post and my response was in my first post.
The rest of the answers seem correct to me.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
May 9, 2013 at 2:30 pm
Thank you so much Sean! Us Sean's got to stick together 😉
~Sean
A clever person solves a problem. A wise person avoids it. ~ Einstein
select cast (0x5365616E204465596F756E67 as varchar(128))
May 9, 2013 at 2:33 pm
TeraByteMe (5/9/2013)
Thank you so much Sean! Us Sean's got to stick together 😉~Sean
Sean's of the world unite!!!
You are quite welcome. Hope my answers helped clear up some of your confusion.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
May 9, 2013 at 3:11 pm
Sean Lange (5/9/2013)
TeraByteMe (5/9/2013)
And below are the rest of the questions. BTW, I took myself out of being considered for candidacy for the role so I am not be considered for the position any longer.1. Name three simple aggregate functions: _________, ________, ________
Answers (any three): Sum, Count, Avg, Min, Max, Stddev (variations: Stddev_XXXX), Variance (variations: Var_XXXX)
Looks good
2. Table Spending has a foreign key reference defined to Customers. Which of the following are true statements?
a. No row can exist in Spending without a corresponding row in Customers with the same key value.
b. No row can exist in Customers without a corresponding row in Spending with the same key value.
c. No row can be deleted in Spending without first deleting corresponding row(s) in Customers.
d. No row can be deleted in Customers without first deleting corresponding row(s) in Spending.
e. There must be one or more rows in Customers with the same key value as each row in Spending.
f. There may be zero but no more than one row in Spending with the same key value as each row in Customers.
Answer: e
Unless I am reading the question wrong, my answer would be a & d
3. We must generate a list of customers who have spent $30,000 or more in the last 30 days. All the information needed is in a table that has a separate entry for every transaction made by each customer. Which, if any, of the following clauses must be part of the query?
a. group by cust_id
b. where sum(amt_spent) >= 30000
c. having sum(amt_spent) >= 30000
d. where transaction_date between now() and now() – 29
Answer: a, c, d
Looks good to me
4. Give a brief explanation of the differences between the following statements.
a. drop table table_name;
b. delete table_name;
c. truncate table table_name;
Answer: A: removes the table and all contents from the database. B: removes the contents of the table but the table remains. C: same as B except it cannot be rolled back.
your answer for A and B are correct but your answer on C is incorrect. A truncate absolutely can be rolled back. If it couldn't the database would be able to support ATOMicity. This is easy to demonstrate.
create table TruncateTest
(
MyID int identity
)
go
insert TruncateTest default values
go 10
select * from TruncateTest
begin transaction
truncate table TruncateTest
rollback transaction
select * from TruncateTest
drop table TruncateTest
The difference between delete and truncate is that delete is a dml operation that is fully logged and truncate is DDL operation and is minimally logged. What that means is the rows are logged in a delete but the page deallocation is logged for a truncate.
#6 was in your first post and my response was in my first post.
The rest of the answers seem correct to me.
I may be wrong, would have to test, but I don't think 6e will use an index either because of the NOT IN.
May 9, 2013 at 3:26 pm
Lynn Pettis (5/9/2013)
I may be wrong, would have to test, but I don't think 6e will use an index either because of the NOT IN.
You may be right there.
Just a refresher here is #6 again.
6. Field f of a table is indexed. Which of the following query fragments is written so the index cannot be used? (Assume p1 and p2 are the correct data types for their role in each fragment.)
a. where f > :p1
b. where f between :p1 and dateadd( day, :p2, :p1)
c. where dateadd( day, :p2 , f) > :p1
d. where dateadd( day, :p2, :p1) < f
e. where f not in( select …)
The actual correct answer would that the index will be used in all of these. The difference is that the index will be scanned by some and seeked by others. 😉
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
May 9, 2013 at 3:34 pm
Sean Lange (5/9/2013)
Lynn Pettis (5/9/2013)
I may be wrong, would have to test, but I don't think 6e will use an index either because of the NOT IN.
You may be right there.
Just a refresher here is #6 again.
6. Field f of a table is indexed. Which of the following query fragments is written so the index cannot be used? (Assume p1 and p2 are the correct data types for their role in each fragment.)
a. where f > :p1
b. where f between :p1 and dateadd( day, :p2, :p1)
c. where dateadd( day, :p2 , f) > :p1
d. where dateadd( day, :p2, :p1) < f
e. where f not in( select …)
The actual correct answer would that the index will be used in all of these. The difference is that the index will be scanned by some and seeked by others. 😉
It would also be dependent on if the index on f is clustered or nonclustered, what the query is returning from the table (select * vs select col1, col2, etc), how much data exists and how much is being returned, currency of the statistics, and many other factors not yet mentioned.
May 9, 2013 at 5:05 pm
Sean Lange (5/9/2013)
The difference between delete and truncate is that delete is a dml operation that is fully logged and truncate is DDL operation and is minimally logged. What that means is the rows are logged in a delete but the page deallocation is logged for a truncate.
Technically truncate is a fully logged operation. Minimally logged means that there is a difference in logging behaviour between bulk-logged/simple and full recovery and that in bulk-logged and simple recovery models the operation logs only enough for a roll back, not enough for a roll forward operation, such an operation flips the appropriate bit in the ML map and results in the next log backup copying the affected extents into the log backup.
Minimally logged does not mean that only the page allocation/deallocations are logged. Many operations, including truncate table, drop table, drop index, select into, insert into, alter index .. rebuild, log only page allocations/deallocations in all recovery models.
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
May 10, 2013 at 3:01 am
I wonder, if you could discuss questions with interviewer or it was just written test?
Not long ago I was on the interview, where the interviewer asked me to do technical test without any possibility to discuss questions. I have done the test fine, but after it I've told him that I'm not interested in position. If they want to test knowledge this way, they at least must ask some senior SQL expert to validate questions in their tests...
So, some of your questions are also open to wide interpretation.
2. Table Spending has a foreign key reference defined to Customers. Which of the following are true statements?
a. No row can exist in Spending without a corresponding row in Customers with the same key value.
b. No row can exist in Customers without a corresponding row in Spending with the same key value.
c. No row can be deleted in Spending without first deleting corresponding row(s) in Customers.
d. No row can be deleted in Customers without first deleting corresponding row(s) in Spending.
e. There must be one or more rows in Customers with the same key value as each row in Spending.
f. There may be zero but no more than one row in Spending with the same key value as each row in Customers.
a. looks almost right, but not exactly. As it's possible to have row in Spending without a corresponding row in Customers with the same key value, if the key value for Customer in Spending is NULL.
the same goes for d. & e.
9. Which use of subquery is correct and why?
a. where f in (select a from tab1)
b. where f = (select a from tab1)
Try "incorrect" b. sample:
DECLARE @t TABLE (id int, val char(10))
INSERT @t VALUES (1,'acs')
SELECT 'ok' WHERE 1 = (SELECT id FROM @t)
Works fine. So, the right answer will be: "it depends..."
Saying that your questions were much more matured than ones I had.
May 10, 2013 at 7:20 am
GilaMonster (5/9/2013)
Sean Lange (5/9/2013)
The difference between delete and truncate is that delete is a dml operation that is fully logged and truncate is DDL operation and is minimally logged. What that means is the rows are logged in a delete but the page deallocation is logged for a truncate.Technically truncate is a fully logged operation. Minimally logged means that there is a difference in logging behaviour between bulk-logged/simple and full recovery and that in bulk-logged and simple recovery models the operation logs only enough for a roll back, not enough for a roll forward operation, such an operation flips the appropriate bit in the ML map and results in the next log backup copying the affected extents into the log backup.
Minimally logged does not mean that only the page allocation/deallocations are logged. Many operations, including truncate table, drop table, drop index, select into, insert into, alter index .. rebuild, log only page allocations/deallocations in all recovery models.
Thanks for the clarification and correction.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply