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/