Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Weird interview question Expand / Collapse
Author
Message
Posted Thursday, May 9, 2013 1:34 PM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, September 5, 2014 9:10 PM
Points: 163, Visits: 569
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.
A clever person solves a problem. A wise person avoids it.



A clever person solves a problem. A wise person avoids it. ~ Einstein
select cast (0x5365616E204465596F756E67 as varchar(128))
Post #1451309
Posted Thursday, May 9, 2013 1:39 PM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, September 5, 2014 9:10 PM
Points: 163, Visits: 569
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.
A clever person solves a problem. A wise person avoids it.



A clever person solves a problem. A wise person avoids it. ~ Einstein
select cast (0x5365616E204465596F756E67 as varchar(128))
Post #1451310
Posted Thursday, May 9, 2013 2:01 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 2:56 PM
Points: 13,081, Visits: 12,545
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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1451312
Posted Thursday, May 9, 2013 2:12 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 2:56 PM
Points: 13,081, Visits: 12,545
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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1451315
Posted Thursday, May 9, 2013 2:30 PM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, September 5, 2014 9:10 PM
Points: 163, Visits: 569
Thank you so much Sean! Us Sean's got to stick together

~Sean







A clever person solves a problem. A wise person avoids it.
A clever person solves a problem. A wise person avoids it.



A clever person solves a problem. A wise person avoids it. ~ Einstein
select cast (0x5365616E204465596F756E67 as varchar(128))
Post #1451319
Posted Thursday, May 9, 2013 2:33 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 2:56 PM
Points: 13,081, Visits: 12,545
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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1451321
Posted Thursday, May 9, 2013 3:11 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 6:48 PM
Points: 20,734, Visits: 32,499
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.



Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1451332
Posted Thursday, May 9, 2013 3:26 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 2:56 PM
Points: 13,081, Visits: 12,545
Lynn Pettis (5/9/2013)
[quote][b]
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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1451338
Posted Thursday, May 9, 2013 3:34 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 6:48 PM
Points: 20,734, Visits: 32,499
Sean Lange (5/9/2013)
Lynn Pettis (5/9/2013)
[quote][b]
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.



Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1451346
Posted Thursday, May 9, 2013 5:05 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 4:08 PM
Points: 40,177, Visits: 36,580
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 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1451380
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse