Hwk5_shark_2024Sp

pdf

School

Northeastern University *

*We aren’t endorsed by this school

Course

3200

Subject

Computer Science

Date

Feb 20, 2024

Type

pdf

Pages

3

Uploaded by ProfRose19771

Report
Northeastern University CS 3200 Homework 5 SQL SELECT practice in MySQL This assignment gives you another opportunity to hone your skills as an SQL programmer. Please download the self-contained exported database schema and import the schema into a database named sharkdblastnamefirstinitial, where /lastname is your last name and firstinitial 1s the first letter of your first name. Please submit one hwk5sharkdblastnamefirstinitial.sql file to canvas that contains the queries that you wrote for the assignment. The database was created using the data available at the Atlantic White Shark Conservancy website as well as from tracking shark attacks in local newspapers. The database contains nine tables: shark, tag, attack, victim, receiver, sponsor, location, bay and gender. The shark table contains a tuple for each shark identified in the northeast waters. Sharks have a length, a gender, and an accumulated count of the number of detections. A tag is a sensor that allows the organization to track the shark through the water. It 1s attached to the shark in order to track the shark’s movement. The tag table contains: the tag id, and the date 1t was deployed (attached to a shark) . A receiver can sense a tag on a shark and specify that the shark’s location 1s close to the receiver. The receiver table fields are: the receiver id, the receiver name, its description, its location, its sponsor, and the bay it is found in. If the receiver is not associated with a bay, the value of the bay field is NULL. The attack table contains data associated with a shark attack, it references a victim, a location, a shark, the activity the victim was engaging in when the attack took place as well as a description of the attack. The township table contains: the town name and a 2 character state abbreviation. The bay table contains the name of a bay. The gender table contains the legal values for the gender of a shark.
' vid INT ) <> shark INT <> name VARCHAR(45) 4 | 1 victim INT <>age INT "] < fatal CHAR(1) > ! date DATE < activity VARCHAR(64) T I <> description VARCHAR(64) hark v | 3 sha : “ location INT * sid INT (- ‘ | > | —|gender v [ — — —I< > hame VARCHAR(64) | - | * sex VARCHAR(?) | - — — —! 9 sexVARCHAR(7) | #— — ————— — ___ | T > .. | > length DOUBLE 7 JI <> detections INT | j receiver v i_ 1T ‘ > ' rid INT | J=e v j : < location INT , ¥ 1 shark INT ] township v . <» sponsor VARCHAR(45) date DATE " tid INT > <» area VARCHAR(64) ——H< deployed VARCHAR(64) | - — ~H1 @ town VARCHAR(64) | <> hauled VARCHAR(64) state CHAR(2) ] sponsor h | > detections INT B | ' sponsor_name VARCHAR(128) |_| MR <> individual_sharks_detected INT > | <> bayside VARCHAR(64) > \'4 | | + —| bay v | name VARCHAR(64) | > Within your solution file please provide the question number in comments before the solution. Write SQL queries that answer the following questions. Make sure you compose a query that does not use literal values determined by perusing the tuples. Also, remember some queries may generate an empty result. 1. (5 points) For each shark (found in the shark table) , determine the number of detections for the shark as documented within the database. Each tuple in the result should contain the shark’s name, shark’s id and the number of detections. In the result, rename the number of detections to “sightings”™. 2. (5 points) Determine the number of shark detections for each bay (as documented in the database). Each tuple in the result should contain the bay’s name and the number of detections. Sort the result by the number of detections in descending order. 3. (5 points) Return the length of the largest shark. The result should consist of a number and be renamed longest. 4. (5 points) What receiver has been documented as having the most number of shark detections? The result should contain the receiver’s area, town, state, and the number of detections 5. (5 points) What receiver has sighted all sharks (sharks documented in the database)? The result should contain all of the receiver fields from the receiver table. 6. (5 points) Make a separate table from the receiver table — where the records are for the bayside receivers. Name the new table bayside encounters.
7. (5 points) Which sharks have been documented as part of an attack. The result should contain the shark.id, shark.name, sex, length and number of detections. 8. (5 points) For each bay (each name in the bay table) , create an aggregated field that contains a list of the receivers in that bay (represented by the field area). The result set should contain the bay name and the grouped area name for the receivers. Do not duplicate area names within the grouped list of receivers. 9. (5 points) Which is the largest shark (by length)? Return the shark’s name in the result. 10. (5 points) How many shark sightings have occurred for the different towns in the database? The result should contain the town name, state and shark detections. 11. (5 points) Which township has had the most shark sightings? The result should contain the town name, state and shark detections. 12. (5 points) Find all receivers where the number of unique sharks detected is equal to 1 and the number of overall shark detections is greater than seven but less than fifteen. Return the receiver name (area), the sponsor name, the number of unique sharks detected, the number of overall detections, the town and the state. 13. (5 points) How many sharks are female? Return a single count. 14 (5 points) For each town determine the number of overall shark sightings. The result should contain the town name, the town state and the number of sightings. All towns must appear in the result. 15. (5 points) For each sponsor in the sponsor table, determine the number of receivers they sponsor. The result should contain the sponsor name and the count. Make sure all sponsors appear in the result. 16. (5 points) Determine the total number of shark detections associated with their sponsored receivers. If a receiver is not sponsored the detections should still be aggregated and assigned to the NULL sponsor. The result should contain the sponsor name and the number of detections. 17. (5 points) For each attack, report the shark name, if the attack was fatal, the description of the attack, the date of the attack, the victim’s activity when attacked, the town where the attack occurred, the state, the victim’s name and the victim’s age. 18. (5 points) Determine the town that first deployed a receiver. The result should contain the town, the state and the date of deployment for the receiver. 19. (5 points) How many receivers are deployed for each town? The result should contain the town name, the town state, and the number of receivers (renamed to num_receivers). All towns in the township table should appear in the result. If a town has no receivers, then the count num_receivers should be O. 20, (5 points) Find the towns that do not have a receiver and do not have an attack. Return the town name and the state of all towns not found in either the receiver table or the attack table.
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
  • Access to all documents
  • Unlimited textbook solutions
  • 24/7 expert homework help

Related Documents

Homework 5.tex
Homework 10.tex
Homework 9.tex
hw-1.docx
Homework 7.tex
Critical Analysis Engage.docx
Quiz_7.pdf
Qualia_leafworm_psid.docx
Trebles_tresslet_masculinizing.docx
Humanized_fuddles_motivationally.docx
Geophysics_nonsatirical_maid-in-waiting.docx
Daemonurgy_dibble_fromenties.docx
Recommended textbooks for you
Text book image
Programming with Microsoft Visual Basic 2017
Computer Science
ISBN:9781337102124
Author:Diane Zak
Publisher:Cengage Learning
Text book image
Np Ms Office 365/Excel 2016 I Ntermed
Computer Science
ISBN:9781337508841
Author:Carey
Publisher:Cengage
Text book image
COMPREHENSIVE MICROSOFT OFFICE 365 EXCE
Computer Science
ISBN:9780357392676
Author:FREUND, Steven
Publisher:CENGAGE L
Text book image
A Guide to SQL
Computer Science
ISBN:9781111527273
Author:Philip J. Pratt
Publisher:Course Technology Ptr
Text book image
Database Systems: Design, Implementation, & Manag...
Computer Science
ISBN:9781305627482
Author:Carlos Coronel, Steven Morris
Publisher:Cengage Learning
Text book image
Database Systems: Design, Implementation, & Manag...
Computer Science
ISBN:9781285196145
Author:Steven, Steven Morris, Carlos Coronel, Carlos, Coronel, Carlos; Morris, Carlos Coronel and Steven Morris, Carlos Coronel; Steven Morris, Steven Morris; Carlos Coronel
Publisher:Cengage Learning
SEE MORE TEXTBOOKS
Recommended textbooks for you
Text book image
Programming with Microsoft Visual Basic 2017
Computer Science
ISBN:9781337102124
Author:Diane Zak
Publisher:Cengage Learning
Text book image
Np Ms Office 365/Excel 2016 I Ntermed
Computer Science
ISBN:9781337508841
Author:Carey
Publisher:Cengage
Text book image
COMPREHENSIVE MICROSOFT OFFICE 365 EXCE
Computer Science
ISBN:9780357392676
Author:FREUND, Steven
Publisher:CENGAGE L
Text book image
A Guide to SQL
Computer Science
ISBN:9781111527273
Author:Philip J. Pratt
Publisher:Course Technology Ptr
Text book image
Database Systems: Design, Implementation, & Manag...
Computer Science
ISBN:9781305627482
Author:Carlos Coronel, Steven Morris
Publisher:Cengage Learning
Text book image
Database Systems: Design, Implementation, & Manag...
Computer Science
ISBN:9781285196145
Author:Steven, Steven Morris, Carlos Coronel, Carlos, Coronel, Carlos; Morris, Carlos Coronel and Steven Morris, Carlos Coronel; Steven Morris, Steven Morris; Carlos Coronel
Publisher:Cengage Learning
SEE MORE TEXTBOOKS