SQL Database development Questions

Question

SQL Database development Questions

andyman123456

SSC-Addicted

Points: 479
More actions
April 12, 2010 at 8:26 am

#100240

Dear Experts,
Could anybody please provide me the solution of my below questions ?
I have done the part I successfully i just cant figure out the solution of part II. If any body can provide me sql scripts in order to resolve this questions. That would be great.Please reply i am really struggling.
Part I
0.Create, and populate with an odd number of rows of test data, a table TBL_EMP with “employee” details defined as follows:
empid – integer, primary key, identity field starting at 1, increments by 1
managerid – int, allows nulls, pointer to another employee record in TBL_EMP
name – string of 50 characters
salary – money
Not all employees have their managers defined (managerid is NULL).
Each of the next three questions below can be answered by a single executable T-SQL statement/query (which may use inner queries, unions etc.).
1.The company is doing well and decides to give all employees a pay rise based on the following scheme:
Salary between £20000 and £40000  £5000 increase
Salary between £40000 and £55000  £7000 increase
Salary between £55000 and £65000  £9000 increase
Write a single UPDATE statement which updates the salaries in the TBL_EMP table as appropriate.
2.Write a query which returns the names of each employee along with their manager’s name, or ‘No Manager’ if none is defined
3.Write a query to return the name of the employee(s) with the median salary.
Part II
4.This question asks you to generate row numbers in SQL Server 2000 (no ROW_NUMBER() function) in a single SELECT query.
How would you write a query to return employees names with IDs, in descending name order, but with ascending row numbers:
empidname
1Davina
2Alf
3Claire
4Bob
empidnamerownum
1Davina1
3Claire2
4Bob3
2Alf4
5.There are a few ways of achieving the results in (4) with multiple queries, but what is wrong with this method?
SELECT empid, name, identity(int,1,1) AS rownumber
INTO #TEMPOUT
FROM TBL_EMP e
ORDER BY name desc
SELECT * FROM #TEMPOUT
How would you fix it with as few code changes as possible?
6.Table TBL_EMP_AUDIT is defined as follows:
create table TBL_EMP_AUDIT
(auditid int primary key identity(1,1),
notes varchar(200),
delta money)
Write a trigger which, when the salary of any employee(s) is updated, inserts a single record into this audit table with the total amount of the change across all employees.
e.g. if five employees were given £1000 pay cuts in a single update statement, the audit table should contain one row with values delta = £-5000, notes = ‘Salaries updated’
7.How would you add a column to the audit table to hold the time of the update, without altering the trigger written in (6)?

Viewing 15 posts - 1 through 15 (of 28 total)

You must be logged in to reply to this topic. Login to reply

GSquared SSC Guru Points: 260824 More actions · Answer 1

Well, the answer to #4 is let the front end application do it. Presentation data belongs in the presentation layer, not in the database. That won't get you any points on the exam, but it's the correct answer in the real world.

For the rest of the questions, what degree or cert do I get if I answer them correctly? (In other words, why would someone take your test for you?)

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

andyman123456 SSC-Addicted Points: 479 More actions · Answer 2

Thanks for your response.

I am just trying to resolve this question for someone.

If you know the answers then please do reply.

Thanks in advance!!!

TheRedneckDBA SSChampion Points: 14001 More actions · Answer 3

AMITLSBU (4/12/2010)
Thanks for your response.
I am just trying to resolve this question for someone.
If you know the answers then please do reply.
Thanks in advance!!!

In that case, what degree or cert is this "someone" working on? 😛

The Redneck DBA

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 4

AMITLSBU (4/12/2010)
Thanks for your response.
I am just trying to resolve this question for someone.
If you know the answers then please do reply.
Thanks in advance!!!

Better question is this: What answers has this individual currently provided. We might be more willing to help by providing guidance than just providing answers.

andyman123456 SSC-Addicted Points: 479 More actions · Answer 5

andyman123456

SSC-Addicted

Points: 479

April 12, 2010 at 8:47 am

#1150102

he is doing masters

TheRedneckDBA SSChampion Points: 14001 More actions · Answer 6

AMITLSBU (4/12/2010)
he is doing masters

Interesting. Just out of curiosity, what is the masters program in? Seems like some pretty basic SQL questions for a masters-level class.

The Redneck DBA

andyman123456 SSC-Addicted Points: 479 More actions · Answer 7

andyman123456

SSC-Addicted

Points: 479

April 12, 2010 at 9:55 am

#1150144

MSC in (Information system)

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 8

This is precisely why I don't trust people with letters after their names. Tell your friend to do his/her own work and to earn the degree instead of faking their way through tests with other people's answers.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Bill-89778 Ten Centuries Points: 1201 More actions · Answer 9

Bill-89778

Ten Centuries

Points: 1201

April 12, 2010 at 10:22 am

#1150160

Amen!!

steveb. SSC-Forever Points: 46733 More actions · Answer 10

AMITLSBU (4/12/2010)
MSC in (Information system)

These look like questions from INFO 101 not a masters level paper.

TheRedneckDBA SSChampion Points: 14001 More actions · Answer 11

Jeff Moden (4/12/2010)
This is precisely why I don't trust people with letters after their names. Tell your friend to do his/her own work and to earn the degree instead of faking their way through tests with other people's answers.

Or at least have the decency to post the plea for help under your own account instead of getting a friend to do it for you! 🙂

The Redneck DBA

TheRedneckDBA SSChampion Points: 14001 More actions · Answer 12

In that case, what degree or cert is this "someone" working on? 😛

Good point. I don't mean to suggest it's not OK to ask for help. Just ask specific questions about what you (or the someone you are helping) doesn't understand instead of posting the whole test/homework assignment and asking people to solve it for you.

The Redneck DBA

Steve Jones - SSC Editor SSC Guru Points: 736270 More actions · Answer 13

Please do not post homework/exam/interview questions with the expectation that someone will do them for you. You are responsible for doing your own work. Or your friend is.

We are happy to help, but we do not intend to do the work for someone unwilling to make the effort themselves.

SQLRNNR SSC Guru Points: 281334 More actions · Answer 14

Jeff Moden (4/12/2010)
This is precisely why I don't trust people with letters after their names. Tell your friend to do his/her own work and to earn the degree instead of faking their way through tests with other people's answers.

You won't get a lot of sympathy here in these forums for questions like these unless you can provide the answers you already discovered on your own. We like to help each other, not do it for you.

I also agree with Jeff.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events