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.
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 RecordsJohn Doe 3Theodore Roosevelt 3Abraham Lincoln 23.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- Lumbagohttp://xkcd.com/327/ |
|
|
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. |
|
|
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. |
|
|
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. |
|
|
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).aspxTrick: order by Lastname DESC, so you get the last 10 authors on top...http://msdn.microsoft.com/en-us/library/ms188385(SQL.90).aspxNow it should be possible for you.greetingswebfred No, you're never too old to Yak'n'Roll if you're too young to die. |
|
|
|
|
|
|
|