Post reply

SQL Database development Questions

  • 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:

    empid name

    1 Davina

    2 Alf

    3 Claire

    4 Bob

    empid name rownum

    1 Davina 1

    3 Claire 2

    4 Bob 3

    2 Alf 4

    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)?

  • April 12, 2010 at 8:30 am

    #1150081

    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

  • April 12, 2010 at 8:35 am

    #1150086

    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!!!

  • April 12, 2010 at 8:39 am

    #1150090

    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

  • April 12, 2010 at 8:44 am

    #1150099

    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.

  • April 12, 2010 at 8:47 am

    #1150102

    he is doing masters

  • April 12, 2010 at 9:52 am

    #1150136

    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

  • April 12, 2010 at 9:55 am

    #1150144

    MSC in (Information system)

  • April 12, 2010 at 10:12 am

    #1150155

    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)
    Intro to Tally Tables and Functions

  • April 12, 2010 at 10:22 am

    #1150160

    Amen!!

  • April 12, 2010 at 10:25 am

    #1150162

    AMITLSBU (4/12/2010)

    MSC in (Information system)

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

  • April 12, 2010 at 11:07 am

    #1150196

    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

  • April 12, 2010 at 11:09 am

    #1150198

    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

  • April 12, 2010 at 11:30 am

    #1150204

    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.

    Follow me on Twitter: http://www.twitter.com/way0utwest
    Forum Etiquette: How to post data/code on a forum to get the best help
    My Blog: www.voiceofthedba.com

  • April 12, 2010 at 2:08 pm

    #1150329

    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

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

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