Populating database with sample data
AND
5. and 6. Questions.
I want answer for 3 questions that i wrote bold and also I mentioned above. Thank you
SHIP Shiple ShipName Weight YearBuilt PassengerCapacity CRUISE Cruise SerialNo SailingDate ReturnDate ShipID Booking CruiseSerialNo Passengerld Price SatisfactionRate Passenger Passenger D PFirstName PlastName PStreetAddress PCity PState PPostalCode PCountry VISIT Cruise SerialNo PortID VisitOrder PORT Portld PortName PortCountry DockingFee World Traveler Cruise Line SHIP Ship Number Ship Name Weight Year Built Passenger Capacity CRUISE Cruise Serial Number Sailing Date Return Date VISITS Visit Order BOOKING Price Satisfaction Rate PORT Portin Port Name Country Docking Fee PASSENGER Passenger Number Name (First Name, Middle Name, Last Name) Address (Street Address, City, State, Postal Code, Country) Can you please answer questions I marked with bold. Thank you very much. I APPRECIATE YOUR WORK. Populate database with sample data 5.What is the average number... 6. Which port has the highest... relational model that is provided is part of the relational model from your HW 2, however it has been simplified. The E-R diagram for this relational model is also provided to you in the HW3_Data Model file First create an SQL Server database by right clicking on the databases folder in the Object explorer and selecting the New Database option. Name the database by the initials of your last names and then A3. For instance a database created by Johnson and White would be named as jw_A3. Write the SQL statements for creating the tables in your database, specifying data types and field lengths, establishing primary keys and foreign keys, and implementing all the necessary constraints. Save your create statements in a file. Use the same naming convention you used to name your database to name your file. In your file also include the following SQL statements 1. An SQL statement to add a constraint to one of the tables. 2. An SQL statement to add a column to one of the tables. 3. An SQL statement to insert data to one of the tables 4. An SQL statement to modify the data in one of the tables. 5. An SQL statement to delete a row of data from one of the tables. Populate your database with sample data. Write the following SQL queries and save them in your file also. Put the question numbers above your queries, e.g. Q1, Q2, Q3 etc. Ensure that your queries are correct and produce the results you expected. 1. List all passengers who live in Istanbul sorted by last name and then by first name. 2. What is the number of passengers whose city was not specified (i.e., IS NULL)? 3. Display shirgame. YearBuilt. Passenger CapacityPortName for ships that visited the ports. 4. Display ShirName, Cruise SerialNe, SailingDate, PortName, Port Country and VisitOrder for all the ships that have been assigned to a cruise. 5. What is the average number of cruises that a ship sails? (Hint: write a subquery to calculate the count of cruises a ship sails, then using that as the data source take the average of the count of cruises.) 6. Which port has the highest docking fee? | . Page 1 of 1 404 words + 101% SHIP Shield ShipName Weight Year Built Passenger Capacity Can you please answer questions I marked with bold. Thank you very much. I APPRECIATE YOUR WORK. Populate database with sample data 5.What is the average number... 6. Which port has the highest... CRUISE CruiseSerialNo SalingDate ReturnDate ShiplD Booking CruiseSerialNo Passenger Price Satisfactionate Passenger Passengerie PFirstName PlastName PStreetAddress PCity PState PostalCode PCountry VISIT Cruise SerialNo Porti VisitOrder . PORT Port PortNarre | PortCountry | DockingFee World Traveler Cruise Line relational model that is provided is part of the relational model from your HW 2, however it has been simplified. The E-R diagram for this relational model is also provided to you in the HW3_Data Model file First create an SQL Server database by right clicking on the databases folder in the Object explorer and selecting the New Database option. Name the database by the initials of your last names and then A3. For instance a database created by Johnson and White would be named as W_A3 Write the SQL statements for creating the tables in your database, specifying data types and field lengths, establishing primary keys and foreign keys, and implementing all the necessary constraints. Save your create statements in a file. Use the same naming convention you used to name your database to name your file. In your file also include the following SQL statements 1. An SQL statement to add a constraint to one of the tables. 2. An SQL statement to add a column ta ane of the tables 2. An SQL statement to insert data to one of the tables 4. An SQL statement to modify the data in one of the tables. 5. An SQL statement to delete a row of data from one of the tables. Populate your database with sample data. Write the following SQL queries and save them in your file also. Put the question numbers above your queries, e.g. 21, Q2, Q. etc. Ensure that your queries are correct and produce the results you expected 1. List all passengers who live in Istanbul sorted by last name and then by first name. 2. What is the number of passengers whose city was not specified (i.e., IS. NULLI? 3. Display shirame YeacBuilt. Passenger Caracity. PartName for ships that visited the ports 4. Display ShipName, Cruiseseca. No, Salcedate, PortName Portcounto, and Visitorder for all the ships that have been assigned to a cruise. 5. What is the average number of cruises that a ship sails? (Hint: write a subquery to calculate the count of cruises a ship sails, then using that as the data source take the average of the count of cruises.) 6. Which port has the highest docking fee? SHIP CRUISE der Nambar Saling Date Saturn Date VISITS Visit Order Ship Name Weight Year Built Page Capacity BOOKING Price Satisfaction Rate PORT Parti Port Name Country Docking Free PASSENGER Per Number Name First Name, Minde Name, Mt Name Address Street Address City State Postal Code Country Page 1 of 1 404 words 101%
