SQL Server Performance

Intern Assignment

Discussion in 'Getting Started' started by mxotwas@gmail.com, May 5, 2010.

  1. mxotwas@gmail.com New Member

    Hi
    I am doing my internship and have been given the following assignment can you please assist as I have no experience in Stored Procedures.
    1. Using the table structure below write a production ready stored procedure to do the following:
    a. Find the player with the last name “Tom” with the highest balance.
    b. If there isn’t a player with the surname “Tom”, find the player with the lowest balance.
    c. If you have found a single player, then update his balance with the balance of the player with UserID=33.
    d. IF UserID=33 does not exist, the update to the balance should be 1000.
    e. Output all of the Users Firstname appended to the LastName who have a balance greater than 2000 and the Institution they belong to has more than 30 members.
    User (UserID, UserLogin, FirstName, LastName, Balance)
    User AccountMap(AccountID, UserID)
    Account( AccountID , AccountNumber, Institution )
    2. Describe in words what changes you would make to the schema above based on the following information:
    a. The most common operation performed is the extraction of a users balance based on his UserLogin.
    b. Users are now allowed to share accounts.
    c. We now need to differentiate between account types.
    3. If the table Account is located on another database on another instance of sql server
    a. Explain in words the things you would do to ensure you can query the table from the first database.
    b. If your query was not working(error getting the result back), how would you isolate the problem.
    4. There exists a system that allows a user to select and play from a list of games. However
    a. You want to grant access to a game to only certain players of your choosing. All other players must not have access to this game.
    b. You want to deny access to a game to certain players of your choosing. All other players must have access to the game.
    Players are added automatically to the system when they register online. Assuming you have the player table and game table that contain the lists of players and games, create a table schema to configure the above rules. Remember there could be thousands of players and hundreds of games and players are added all of the time but you only configure your system rules occasionally.
    5. Design a data model that can hold financial transactional data. Transactions are based on a request and response. A request is send to a processor and they respond with the result telling us the status of the request. There may be multiple responses for a request (e.g Pending, Accepted, Rejected). A request may be processed by different parties (we decide this before performing the request) so we need to identify who processed a particular request. The structure of responses and the data in the responses may differ depending on who processed the request, however, there is some data that is common to all requests and responses. In you model, cater for the common request/response data and response/request data that is custom to each processor.
    NOTE : The type of data is not important, what we are interested in is how you model the data. You can make up and names for the common and differing data.
  2. MichaelB Member

    Hi. Cheating is bad and not something any of us condone.
  3. satya Moderator

    Appreciate the OP has been clear stating its an intern assignment.
  4. MichaelB Member

    Satya,
    sorry but thats NOT a intern assignment. it is a quiz. no one would assign that to an intern like that

Share This Page