Module 5 Assignment
docx
keyboard_arrow_up
School
Middle Georgia State University *
*We aren’t endorsed by this school
Course
3245
Subject
Information Systems
Date
Apr 3, 2024
Type
docx
Pages
5
Uploaded by ChefElementAntelope18
Module 5 Assignment: Instructions: Answers should be contained in either a single Word document or PowerPoint presentation. If using PowerPoint, only one answer per slide. Do not zip file before submission. Number your answers appropriately (1b, 3¢, etc.). If you are skipping an answer, number as usual and note “Question Skipped” or “Not Answered.” Keep answers in the order listed in this handout. For written responses, each answer should typically be around 100-150 words (a nice paragraph), well-written (proper spelling, punctuation, grammar, etc.), and cover the topic fully. Be sure to cite your sources (including the textbook)! If an answer requires a screenshot, please make sure that your screenshot shows all relevant information and is large enough to be easily legible. Points will be deducted if your submission is not properly formatted as detailed above. NOTE: MySQL is required for this assignment. 1. Download the HAPPY_CRUISE.sql file from the Module 5 folder. | would recommend opening the script file, using a text editor, to familiarize yourself with its contents. Just make sure not to make any changes! Review this link to learn the different methods to execute a script file from the CLI. Select the one that applies in this case. Now, execute the command to run the HAPPY_CRUISE.sql script to replace the database you made in the Module 4 Assignment with this new version. For a deliverable, scroll up to take a screenshot of the command you typed and the first couple of lines of its successful execution. (20 points) mysql> source C://scripts/HAPPY_CRUISE.sql Query OK, 6 rows affected (0.66 sec) Query OK, Im affected (.00 sec) Database changed Query OK, © rows affected, 2 warnings (8.61 sec) Query OK, 18 rows affected (8.61 sec) Records: 18 Duplicates: © Warnings: [ nu,’- rows affected, 2 warnings (6.62 sec) Query OK, 18 rows affected (0.88 sec) Records: 18 Duplicates: © Warnings: . Query OK, 17 rows affected (68.60 sec) Records: 17 Duplicates: @ Warnings: @ . Query OK, © rows affected, 2 warnings (8.81 sec) _,. ) Query OK, 10 rows affected (.80 sec)
2. Execute the following queries, using the CLIin MySQL. For your deliverables, take a separate screenshot for each query. Screenshots should include both the command typed as well as the results. (80 points) HINT: Review the SQL Query Writing Best Practices handout in the Module 5 folder before completing your queries. Use the tips provided (line breaks, aliases, etc.) to format your queries to improve readability. Failure to do so will result in points lost. HINT 2: For multi-table queries, you may use any one of the three common methods (as shown in the chapter, the JOIN/ON method, or the JOIN/USING method) but | would suggest staying consistent across all your queries to reduce confusion. a. List the cruise number, start date, and end date for all cruises that ship 28 took in July 2011. Do not include cruises that extended into August. SELECT cruisenun, startdate, enddate FROM cruise N WHERE shipnum = 28 AND startdate >='2011-87-81" AND startdate <'2011-88-81' AND enddate 2011-07-01" AND enddate "12011-08-01" f = | cruisenun | startdate | enddate o et | 42983 | 2011-67-15 | 2011-87-22 | | 42996 | 2011-07-22 | 2011-07-29 | B o 2 rows in set (.00 sec) b. List the port name and number of docks for each port in Venezuela. mysql> SELECT portname, numdocks -> FROM port ) l: WHERE country = 'Venezuela' | caracas | | Maracaibo | | Puerto Cabello | ERCTER U CR LD )
. How many ports in Venezuela have more than 10 docks? Do not list out individual ports, just provide a summary value. mysql> SELECT COUNT(*) -> FROM port WHERE country = 'Venezuela' AND numdocks > 18; 1 row in set (8.66 sec) d. List the companies who have built a ship for Happy Cruise Lines. Do not allow duplicate records to be shown. mysql> SELECT DISTINCT builder | Ace Shipbuilding Corp. | Ajax + 4 rows in set (6.0 sec) e. How many cruises has each ship been on? Instead of ship number, display each ship’s name in your answer. mysql> SELECT shipname, COUNT(shipnum) AS TOTAL_CRUISES FROM ship JOIN cruise USING (shipnum) GROUP BY shipname; %—I TOTAL_CRUISES | - Ocean IV Queen Shirley Prince Al Sea Peace Princess of Florida | | | King of the sea | | | The Spirit of Nashville | rows in set (6.88 sec)
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
f. Which ship is the lightest in Happy Cruise Lines’ fleet? mysql> SELECT shipname, weight WHERE weight = Bt g (OS] 1 row in set (8.60 sec) g Whatis the average weight of ships that have visited Miami? mysql> SELECT AVG (weight) AS Average_Weight -> FROM ship JOIN cruise USING (shipnum) JOIN visit USING (cruisenum) WHERE portname = 'Miami'; + | *']zsss.ssfl | + 1 row in set (6.88 sec)
h. List the name, state, and country for all passengers who sailed on the Prince Al in July 2003. mysql> SELECT passengername, state, country -> FROM passenger I JOIN voyage USING (passengernum) JOIN cruise USING (cruisenum) J0IN ship USING (shipnum) WHERE shipname = "prince al" AND startdate >= '2003-87-81' AND enddate < '2003-88-61' > AND startdate < '2003-88-61' AND enddate >= '2003-07-1' 2 rows in set (.88 sec)