"> (Answered) <?xml version="1.0" encoding="utf-8"? - Tutorials Prime

Question Details

(Answered) <?xml version="1.0" encoding="utf-8"?


<?xml version="1.0" encoding="utf-8"?


The questions follow the Chapter 3 PowerPoint in terms of the SQL commands used. The PowerPoint can be used as a guide. For some of the formatting or other requirements you may need to use Google for assistance. Using TSQL (this is the nickname for Microsoft's SQL formats) and the words for what you are trying to do often works. Adding the word EXAMPLE will find solutions that include examples of the SQL. Results from StackOverflow and Microsoft often give the best information. 1.      Using the Teams table, select lgid, teamID, name and Parklgid       teamid   name                                parkNA         HR1       Hartford Dark Blues       Hartford Ball Club GroundsNL          IN1        Indianapolis Blues           South Street ParkAA         LS2        Louisville Eclipse              Eclipse Park I2.      Modify the query in #1 so that teams are only listed once3.      Using the Teams table, select the teamID, name, yearID and calculate the % of games won (W/W+L)teamid     name                              yearid  Percent WinHR1       Hartford Dark Blues       1874      0.301886792452IN1        Indianapolis Blues           1878      0.400000000000LS2        Louisville Eclipse              1882      0.5250000000004.      Modify the query in #3 to show the % of games won formatted as a 2 decimal point percentteamid   name                                yearid  Percent WinHR1       Hartford Dark Blues       1874      30.19 %IN1        Indianapolis Blues           1878      40.00 %LS2        Louisville Eclipse              1882      52.50 %5.      Modify the query in #4 to only show the information for the year 2000.teamid name                                   yearid  Percent WinFLO       Florida Marlins                 2000      49.07 %HOU     Houston Astros                2000      44.44 %KCA       Kansas City Royals          2000      47.53 %6.      Modify the query in #5 to only show the information for teams that won over 90 games in the year 2000. Include the number of wins in your results.teamid name                                   yearid  Wins    Percent WinNYN      New York Mets                2000      94          58.02 %OAK      Oakland Athletics            2000      91          56.52 %SEA       Seattle Mariners             2000      91          56.17 %7.      Create the Cartesian product for the Teams and TeamsFranchises tables (Note the number of rows returned)Your query should return 336,360 rows 8.      Using a WHERE statement and the query from #7, show the teamID and franchid and franchName for active teams. Each team should be shown only once.teamID franchid              franchNameANA      ANA                      Los Angeles Angels of AnaheimARI        ARI                        Arizona DiamondbacksATL        ATL                        Atlanta Braves9.      Modify the query in #7 to use a joinYour query should return 2,803 rows10.  Rewrite the query in #4, renaming the % won result to 'Winning %'teamid name                                   yearid  Winning %HR1       Hartford Dark Blues       1874      30.19 %IN1        Indianapolis Blues           1878      40.00 %11.  Modify the query in #8 renaming the Teams table to A and the TeamsFranchises to B12.  Using the MASTER table. List the first, last and given name for all players that use their initials as their first name (first name contains at least 1 period(.)nameFirst          nameLast           nameGivenT. J.                       Beam                   Theodore LesterT. J.                       Bohn                    Thomas Joseph13.  Using the query from #12, Concatenate the nameGiven, nameFirst and nameLast into a single column called Full Name putting the nameFirst in parenthesis. For example James (Jim) MarkulicNOTE: SQL server uses different symbols for concatenation than the one shown in the textFull NameTheodore Lester (T. J.) BeamThomas Joseph (T. J.) Bohn14.  Modify the query in #13 to have the results sorted by Last NameFull NameAdam Joseph (A. J.) AchterRoy Emilio (R. J.) Alvarez15.  Modify the query in #14 by adding the Salaries table and include only show players who played in 2000 and had a salary between $200,000 and $300,000. The salary in your results must be properly formatted showing dollars and centsFull Name                                          SalaryAllan James (A. J.) Burnett          $204,000.00David Thomas (D. T.) Cromer     $200,000.0016.  Using the appropriate Set Operator and the MASTER and APPEARANCES tables, list the player, full name (as shown in #13) and the teamid of players who were in the appearances table for 2000 but not for 2001playerid              Full Name                                         teamIDabbotje01          Jeffrey William (Jeff) Abbott      CHAabbotku01         Kurt Thomas (Kurt) Abbott         NYNaceveju01          Juan Carlos (Juan) Acevedo        MIL17.  Modify the query in #16 to use the appropriate Set Operator to show players who were in the appearances table for 2000 and 2001playerid              Full Name                                         teamIDabbotpa01         Paul David (Paul) Abbott              SEAabreubo01         Bob Kelly (Bobby) Abreu             PHIadamste01         Terry Wayne (Terry) Adams       LAN18.  List playerIDs from the MASTER table that are living (nothing in the deathyear column)playerid              deathyearbarneau01         NULLbarnebr01          NULL19.  a query that returns the count players from the MASTER table that are living (nothing in the deathyear column)# of Living Players951020.  a query using the salaries table that returns the min, max and average salary for players in 2010. Make sure columns are properly named and formattedMin_Salary        Max_Salary                      Avg_Salary$400,000.00       $33,000,000.00                 $3,278,746.8321.  Modify the query in #20 so that the min, max and average salaries for 2010 are grouped and sorted by league and teamlgid       teamid Min_Salary        Max_Salary                       Avg_SalaryAL          BAL       $400,000.00       $12,000,000.00                 $3,138,942.31AL          BOS       $415,500.00       $18,700,000.00                 $5,601,632.1722.  Modify #21 to only include teams with an average salary > $4 million for all yearslgid       teamid Min_Salary        Max_Salary       Avg_SalaryAL          ARI        $4,875,000.00    $4,875,000.00    $4,875,000.00AL          LAA       $316,000.00       $26,187,500.00 $4,208,635.81NL          CLE        $4,500,000.00    $4,500,000.00    $4,500,000.0023.  Using nested queries modify the query in #21 to only include the salaries for 2010lgid       teamid Minimum Salary             Maximum Salary             Average SalaryAL          BOS       $415,500.00                       $18,700,000.00                 $5,601,632.17AL          CHA      $400,000.00                       $15,000,000.00                 $4,058,846.1524.  Show the playerID, full names and birthdates (properly formatted) and salary for any Yankee(NYA) playing in 1990 whose salary is greater than the salary of any Boston Red Sox (BOS) using the salaries and mater tables sort highest to lowest salaryplayerID              Full Name                                          Birth Day            NYA Salarymattido01          Donald Arthur (Don) Mattingly 4/20/1961           $2,500,000.00winfida01           David Mark (Dave) Winfield       10/3/1951           $1,958,652.00righeda01           David Allan (Dave) Righetti         11/28/1958        $1,550,000.0025.  Rewrite the query from #23 to use a WITH clause instead of a nested querylgid       teamid Minimum Salary             Maximum Salary             Average SalaryAL          BOS       $415,500.00                       $18,700,000.00                 $5,601,632.17AL          CHA      $400,000.00                       $15,000,000.00                 $4,058,846.1526.  Using a Scalar query, show the playerID, Full Name and the # of teams the player Played for using the MASTER and APPEARANCES tables. Make sure you only count each team once!playerID              Full Name                                                         # of Teams Played Forbarnebr01          Brian Keith (Brian) Barnes                           3barnebr02          Brandon Michael (Brandon) Barnes        227.  Add a column to the MASTER table named NJITID_Avg_Salary (NJITID is your NJIT ID such as JM234_Avg_Salary) a query that will calculate the player's average salary and update the new column with that information. Your SQL should check and see if the column exists before adding it.28.  a query that lists the playerID and the Average Salary column you created in the question #27playerid              Average Salaryaardsda01          $1,322,821.00aaronha01          NULLaaronto01          NULL29.  Using a CASE statement, a query that adds 10% to the salaries from 1998 if the salary is below $400,000 and adds 3% if the salary is above $400,00030.  Players are inducted into the Hall of Fame on July 7th each year. Calculate the exact age (using days, months and years) of the players when they were inducted into the Hall of Fame. You results should have the playerID, Full Name, Birth Year, Inducted Year, Full Birth Date (in the proper format), Full Induction Date (in the proper format) and the age in years   playerid                Player Name        birth      yearID   cate-      calcdate                inductdate           age                                                              year                       goryaaronha01              Hank Aaron            1934       1982       Player     2 -5 -1934              07-25-1982            48alexape01              Pete Alexander      1887       1938       Player     2 -26-1887             07-25-1938            51

 


Solution details:

Pay using PayPal (No PayPal account Required) or your credit card . All your purchases are securely protected by .
SiteLock

About this Question

STATUS

Answered

QUALITY

Approved

DATE ANSWERED

Jan 02, 2020

EXPERT

Tutor

ANSWER RATING

YES, THIS IS LEGAL

We have top-notch tutors who can do your essay/homework for you at a reasonable cost and then you can simply use that essay as a template to build your own arguments.

You can also use these solutions:

  • As a reference for in-depth understanding of the subject.
  • As a source of ideas / reasoning for your own research (if properly referenced)
  • For editing and paraphrasing (check your institution's definition of plagiarism and recommended paraphrase).
This we believe is a better way of understanding a problem and makes use of the efficiency of time of the student.

NEW ASSIGNMENT HELP?

Order New Solution. Quick Turnaround

Click on the button below in order to Order for a New, Original and High-Quality Essay Solutions. New orders are original solutions and precise to your writing instruction requirements. Place a New Order using the button below.

WE GUARANTEE, THAT YOUR PAPER WILL BE WRITTEN FROM SCRATCH AND WITHIN A DEADLINE.

Order Now
WP2Social Auto Publish Powered By : XYZScripts.com