Interview questions - dont know answers

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Interview questions - dont know answers

Author

Topic

ask
Starting Member

1 Post

Posted - 2005-03-20 : 01:08:09

Hi,
Yesterday I went interview and asked this questions which I did not know.

1) If you have 100,000 records and you want to delete 95,000 at a time and keep only 5 thousand. But in Local memory is not having enough space for 95,000 records. What do you do on this scenario? How do you delete without any problem at a time?

2) In Ebay I need a data for customers in Chicago who can did bid on it? I dont have access to their database. How I get the information?

3) With DTS Pakaging I can store Tables in Excel and Back to SQL. But once I copied Tables to Excel I want to modify small info. How you do that without Editing the Excel File?

4) What is cursor and where you use them?

Could you please answer me if you know any one of these questions.

Thank you

Kristen
Test

22859 Posts

Posted - 2005-03-20 : 02:41:35

1) memory doesn't matter, but it could be slow. You could copy the 5,000 good records to a new/temp table, drop the old table, rename the temporary table to the original name. But this has all sorts of issues if there is referential integrity on the original table etc.

You could also BCP out the 5,000 good records, truncate the table, BCP the 5,000 good records back in - same problems if there is referential integrity involved.

2) I would write a spider that trawled the eBay site and sccraped the data form the HTML.

Actually, [a] I would work with eBay to obtain, and pay for the data, else
[b] I wouldn't work for the company!

3) The DTS drive for import from Excel is completely PANTS. I'd find a different way to sovle the ACTUAL business need.

4) You do pretty much everything you can to NOT use cursors. If YOU don't know what a cursor is then you need to learn, 'coz although [for performance reasons] you are better off NOT using them, if you are a SQL Bod then you OUGHT to know all about them!

Kristen

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2005-03-21 : 18:24:13

1) You could SET ROWCOUNT <SomeNumber> and then run your DELETE statement until the @@ROWCOUNT returned zero. Then SET ROWCOUNT 0 to restore it to normal usage.

Kristen,

I hate to appear unhip (I don't mind BEING unhip but I hate for it to APPEAR that way) or overly colonial but could you define "PANTS" and "SQL Bod" for me?

Tks,

HTH

=================================================================
In order to improve the mind, we ought less to learn than to contemplate.
-Rene Descartes, philosopher and mathematician (1596-1650)

Kristen
Test

22859 Posts

Posted - 2005-03-22 : 02:02:45

"I hate to appear unhip (I don't mind BEING unhip but I hate for it to APPEAR that way) or overly colonial but could you define "PANTS" and "SQL Bod" for me?"

I say old chap, terribly sorry and all that. How awfully thoughtless of me not to have spotted you were suffering over there

Pants = Rubbish / poor quality

"Bod" is a person, but the nuance would indicate someone useful, but probably not Guru level. So "SQL Bod" is a DBA-in-the-making

Something like that anyway.

Kristen

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2005-03-23 : 15:21:51

Thanks for sussing that out for me...

Cheers

HTH

=================================================================
In order to improve the mind, we ought less to learn than to contemplate.
-Rene Descartes, philosopher and mathematician (1596-1650)

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2005-03-23 : 15:37:51

I hope you don't end up getting that job. Those are pretty bad interview questions.

- Jeff

Xerxes
Aged Yak Warrior

666 Posts

Posted - 2005-03-23 : 15:59:05

quote:
Originally posted by Kristen

"I hate to appear unhip (I don't mind BEING unhip but I hate for it to APPEAR that way) or overly colonial but could you define "PANTS" and "SQL Bod" for me?"

I say old chap, terribly sorry and all that. How awfully thoughtless of me not to have spotted you were suffering over there

Pants = Rubbish / poor quality

"Bod" is a person, but the nuance would indicate someone useful, but probably not Guru level. So "SQL Bod" is a DBA-in-the-making

Something like that anyway.

Kristen

You British truly have a way with words

Semper fi,

XERXES, USMC(Ret.)
------------------------------------------------------
The Marine Corps taught me everything but SQL!

Kristen
Test

22859 Posts

Posted - 2005-03-24 : 00:56:20

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2005-03-24 : 03:28:05

quote:
Originally posted by jsmith8858

I hope you don't end up getting that job. Those are pretty bad interview questions.
- Jeff

With that standard, how many jobs are left then these days ???

btw, my favorite interview Q is:
"How much do you expect to get paid?"
I usually flunk out on that one.

rockmoose

Kristen
Test

22859 Posts

Posted - 2005-03-24 : 08:35:50

A: If you have to ask you can't afford me

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2005-03-24 : 08:59:26

I am sure that will land You the job Kristen

Ok, it is not always polite to answer a Q with another Q, but sometimes clarifications are needed.
A: What is having this job done properly worth to Your organisation?

rockmoose

coolerbob
Aged Yak Warrior

841 Posts

Posted - 2005-03-24 : 11:53:43

kristen,
if performance is not an issue, whats wrong with cursors?
To me it's a whole lot quicker to code and quite elegant.

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2005-03-24 : 12:15:47

>> To me it's a whole lot quicker to code and quite elegant
Not when you get used to set based thinking

rockmoose

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2005-03-24 : 12:38:54

quote:
Originally posted by coolerbob

kristen,
if performance is not an issue, whats wrong with cursors?
To me it's a whole lot quicker to code and quite elegant.

Writing MORE code is quicker and more elegant for you?

Do me a favor -- write up the more elegant cursor version of:

UPDATE SomeTable
SET Col1= 'X', Col2 = 'Y'
WHERE ID > 123

and let's take a look.

- Jeff

Xerxes
Aged Yak Warrior

666 Posts

Posted - 2005-03-24 : 14:18:59

quote:
Originally posted by coolerbob

kristen,
if performance is not an issue, whats wrong with cursors?
To me it's a whole lot quicker to code and quite elegant.

Did someone mention CURSORS?

Semper fi,

XERXES, USMC(Ret.)
------------------------------------------------------
The Marine Corps taught me everything but SQL!

JimL
SQL Slinging Yak Ranger

1537 Posts

Posted - 2005-03-24 : 14:33:55

~~CURSORS~~

Down with CURSORS.

I expect to get paid more than your going to!

Jim
Users <> Logic

Kristen
Test

22859 Posts

Posted - 2005-03-26 : 00:25:21

"if performance is not an issue, whats wrong with cursors?"

Performance is never not an issue for me. Either the DB will be "bigger one day" or the server that it is running on has more databases on it, or will have, and programming inefficiently on one DB is going to steal CPU cycles from the others.

Not to mention concurrency and a range of other issues.

"To me it's a whole lot quicker to code and quite elegant."

Doesn't work for me. OK, so I have learnt how to think "set based", and when people first start linear programming is the way they think.

But the biggest killer for me is having to describe the column list three times - that's a recipe for disaster


DECLARE authors_cursor CURSOR FOR 
SELECT au_id, au_fname, au_lname
FROM authors
WHERE state = 'UT'
ORDER BY au_id

OPEN authors_cursor

FETCH NEXT FROM authors_cursor 
INTO @au_id, @au_fname, @au_lname

WHILE @@FETCH_STATUS = 0
BEGIN
   ... processing ...

   -- Get the next author.
   FETCH NEXT FROM authors_cursor 
   INTO @au_id, @au_fname, @au_lname
END

CLOSE authors_cursor
DEALLOCATE authors_cursor
GO

Kristen

coolerbob
Aged Yak Warrior

841 Posts

Posted - 2005-03-29 : 04:30:20

I can't see how any of the objections you guys made effects me in my situation. I agree that in most situations it is not the best thing to use. But I am writing a one-off data conversition at the moment. The code will run once and then never again.
I am often running into all sorts of situations where cursors seem best. Like when I need to go through a whole table and depending on one of 7 possible values I need to: set a column value in the same table, set a column value in a different table, or insert a records in a different table.
I cant see why cursors are a bad idea in this situation and I cant see a better/faster option either.
Any takers?

robvolk
Most Valuable Yak

15732 Posts

Posted - 2005-03-29 : 06:18:23

I like to use this analogy a lot: you have a cup of coffee (or tea), and you want to put sugar in it. You can use tweezers, or a spoon. Cursors are tweezers.

Seriously. Suppose you will only drink one cup of coffee in your life, would you still use tweezers to sweeten it?

Any time you try to justify using cursors for data operations, you are, in effect, justifying tweezers over a spoon. If this sounds absurd, that's because it is, but it's still a valid point. SQL is meant to work on sets of data, not row-by-row; you actually work AGAINST the language to do so.

More than just being an ineffective way to do something, cursors are a bad habit to get into, and like drugs or smoking, best avoided by not starting in the first place. The fact that you are "running into all sorts of situations where cursors seem best" means that you're not considering those situations carefully enough or thinking them through.

quote:
Like when I need to go through a whole table and depending on one of 7 possible values I need to: set a column value in the same table, set a column value in a different table, or insert a records in a different table.

This is common disconnect experienced by people who are new to set-based operations. The point is, you don't HAVE TO go through the ENTIRE table. You write a query or operation to affect ONLY the rows that operation should affect, and the rest of the data is ignored...as if it never existed. To further illustrate your example, here it is broken into 3 set-based operations:

UPDATE myTable SET col1='some value' WHERE col2 IN (1, 2, 3)

UPDATE B SET col3='some other value'
FROM myTable A INNER JOIN myOtherTable B ON A.ID=B.ID
WHERE A.col2 IN (4, 5, 6)

INSERT INTO yetAnotherTable(col1, col2)
SELECT col1, col2 FROM myTable
WHERE col2=7

This is the second hallmark of SQL: it is a declarative language, you tell the computer WHAT you want to do, not how to do it. No need for loops, cursors, IF or WHILE tests. The WHERE clause in each of these statements limits the action to only those rows that meet that condition.

coolerbob
Aged Yak Warrior

841 Posts

Posted - 2005-03-29 : 06:38:44

Now that is a good answer. I will have to start changing my thinking. To me, set based statements require more knowladge, understanding & confidence with SQL. So I've got some learning to do!
Seeing those JOINs made me think of another reason why I thought it was a good idea to use cursors: the performance hit that is caused by joins. I assume that is not a valid concern...

robvolk
Most Valuable Yak

15732 Posts

Posted - 2005-03-29 : 07:11:25

No. In fact, JOINs can use indexes to their best advantage and reduce overhead substantially. Oftentimes cursors cannot or do not use indexes at all. Proper indexing could even allow you to query data without accessing the table at all. This can be accomplished with a covering index (an index that contains all of the data the query needs) or via index intersection...the optimizer uses several indexes to populate the data needed for the query. Depending on the type of cursor used, it will always hit the table, and do it only one row at a time.

An analogy would be going to the library to find out the author of a book. The card catalog would contain that information, so you don't have to browse the shelves. The card may even have the number of pages in the book, if you wanted that information. This is an example of a covering index. Having the title and author of the book from one catalog/index lets you look up the same book in another, the Subject list for example. This is an example of index intersection. At no time did you have to hit the shelves to find the book in order to get this information. A cursor, on the other hand, would repeatedly go out to the shelves for each book you wanted, and only pull one book at a time.

Whenever you start thinking about using cursors, consider these analogies (and others) that replicate a physical action to a cursor action. If the physical action does not make sense, then it's probably the wrong way to implement it in SQL.

The library is an extremely useful example of database theory and practice (libraries are the original databases) There are a lot more parallels between them than you would imagine. I remember thinking about them for a good couple of hours, and STILL didn't get them all. If you consider why libraries are set up the way they are, and how they evolved into that structure, you'll get some extraordinary insight into how databases should work, and often do work.

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources