Chapter 3 Questions 2019 Spring Part2 V1.docx - Chapter 3 SQL Assignment \u2013 Part 2 The following are the queries that are to be written using the

Chapter 3 Questions 2019 Spring Part2 V1.docx - Chapter 3...

This preview shows page 1 - 2 out of 5 pages.

Chapter 3 SQL Assignment – Part 2 The 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 EXAMPLE will find solutions that include examples of the SQL. Results from StackOverflow and Microsof often 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 rows Row playerid Birthcity birth birthr yearid Salary Batting State year Average 1 ackerto01 Paterson NJ 1930 1956 NULL 0.0526 2 ackerto01 Paterson NJ 1930 1957 NULL 0.0526 3 ackerto01 Paterson NJ 1930 1958 NULL 0.0667 11 armstja01 Englewood NJ 1965 1990 $107,500.00 0.1064 4. Using a BETWEEN clause, find all players with a Batting Average between .0.300 and 0.3249. The
Image of page 1
Image of page 2

  • Left Quote Icon

    Student Picture

  • Left Quote Icon

    Student Picture

  • Left Quote Icon

    Student Picture