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
 General SQL Server Forums
 New to SQL Server Programming
 Trying to get a job!

Author  Topic 

rosselkins
Starting Member

3 Posts

Posted - 2010-01-21 : 01:27:54
OK Guys I need a miracle to get a great gig. Its not a job as a DBA so I was surprised and bummed to be given an open book test stuffed with SQL questions. I don't know how to thank any expert who can help me, but if the satisfaction of helping me get a job in this terrible economy after being out of work for awhile works then Thank you very very much. Here goes. Also, just to be straight, I explained to the interviewer that I couldn't write SQL queries and he said this is a test to see how I could google information. Other questions asked were more normal for my skilset as a network admin.

1. Create a SQL statement the returns all rows and columns in which the UploadTimeStamp column is greater than March 1, 2006.

2. A SQL Server database contains a table called Authors with the following columns:
AuthorId, FirstName, LastName, Address, City, State, Zip. AuthorId is an identity column and is the primary key for the table.Write a single SQL statement that would delete the last 10 authors in the table based on an alphabetical ordering of their last names.

First NameLast NameNumber of Records
John Doe 3
Theodore Roosevelt 3
Abraham Lincoln 2

3.Due to a programming bug some author records were inserted into the database multiple times. These duplicate records contain identical information for every column except for the AuthorId column. AuthorID is automatically calculated as the last AuthorId + 1 whenever a record is inserted into the database.

Write a SQL query that would return the FirstName, LastName and the number of records for each author that exists more than once in the table sorted from the most duplicates to the least duplicates. The output should look like this:

This means that there are three total records in the table for John Doe, three for Theodore Roosevelt and two for Abraham Lincoln. Authors with only one record per author should not be displayed.

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2010-01-21 : 04:44:59
Nobody's here to do your homework or get you a job. Ask precise questions and show some efforts and you'll get all the help you need but don't ask us to do it all for you. A good place to start if you don't know anything about SQL is http://www.w3schools.com/sql

- Lumbago
http://xkcd.com/327/
Go to Top of Page

rosselkins
Starting Member

3 Posts

Posted - 2010-01-21 : 16:02:59
No thanks for your answer that you didn't need to point out. I have very little time to research this and I asked if someone wanted to help, not if you objected to my asking. As it is, no one has offered to help which may say something in itself.
Go to Top of Page

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-01-21 : 16:53:50
As it is, no one has offered to help which may say something in itself.
Yup!
It says: If we could see what you have done so far then we could help where you stuck.
But it looks like you're looking for a solution without trying anything by yourself.
The interviewer is surely expecting that you code it with a little help from google.

Never mind this is no flaming!
I wish you good luck


No, you're never too old to Yak'n'Roll if you're too young to die.
Go to Top of Page

rosselkins
Starting Member

3 Posts

Posted - 2010-01-21 : 19:20:30
Thanks for the constructive answer Webfred. I sent in all my other nonsql answers and the interviewer was happy enough with them to let me take the time to deal with the sql stuff. I figured out the first question but I am stuck on the second. My technique is to read all sorts of articles based on my needs and then start putting down query commands that look like they get me there. I can see how to order my records alphabetically and how to delete records but I am stuck on putting it all together!
Time for a break.
Go to Top of Page

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-01-22 : 02:21:04
Well the second...
I assume it is at least SQL Server 2005.
So I will give some information and you can solve the problem.
Goal: the last 10 authors in the table based on an alphabetical ordering of their last names.

Nice to know: You can do delete top (10) from...
http://msdn.microsoft.com/en-us/library/ms189835(SQL.90).aspx

Trick: order by Lastname DESC, so you get the last 10 authors on top...
http://msdn.microsoft.com/en-us/library/ms188385(SQL.90).aspx

Now it should be possible for you.

greetings
webfred


No, you're never too old to Yak'n'Roll if you're too young to die.
Go to Top of Page
   

- Advertisement -