100%(2)2 out of 2 people found this document helpful
This preview shows page 1 - 2 out of 5 pages.
Chapter 3 SQL Assignment – Part 2The following are the queries that are to be written using the BaseBall database you created for the course. Each question is worth 5 points. Points will be taken off for incorrect formatting (dollar amounts should be in $xxx,xxx.00 format, percentages in XX.XX%, etc…). Each of the questions states what the query should do and provides a limited sample of the results set you should get from your query. Note that due to differences in the databases, you result numbers may not exactly match the examples. 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 EXAMPLEwill find solutions that include examples of the SQL. Results from StackOverflowand Microsofoften give the best information.1.Write a query that lists the playerid, birthcity, birthstate, salary and batting average for all playersborn in New Jersey sorted by last name and year in ascending order. The joins must be made using the WHERE clause. Make sure values are properly formatted.Note: your query should return 318 rows.playeridbirthcitybirthstateyearidSalaryBatting Averagearmstja01EnglewoodNJ1990$107,500.000.1064armstja01EnglewoodNJ1991$215,000.000.0930armstja01EnglewoodNJ1993$854,545.000.1515bankswi01Jersey CityNJ1994$190,000.000.1220bankswi01Jersey CityNJ1995$240,000.000.00002.Write the same query as #1 but you need to use JOIN clauses in the FROM clause to join the tables. Your answers and rows returned should be the same. 3.Write the same query as #2 but use a LEFT JOIN. Notethat 1897 rows will be returned and armstja01 will be the first player with a non-null salary. Hint: Look at the data to see what needs to be done to get the correct number of rowsRow playeridBirthcity birthbirthryearidSalaryBattingStateyearAverage1ackerto01 PatersonNJ19301956NULL0.05262ackerto01 PatersonNJ19301957NULL0.05263ackerto01 PatersonNJ19301958NULL0.0667…11armstja01 EnglewoodNJ19651990$107,500.000.10644.Using a BETWEEN clause, find all players with a Batting Average between .0.300 and 0.3249. The