Assignment 3 Instructions

Create a database in SQLServer for the relational model provided in the HW3- Relational Schema file. Populate your database with some data to test your database. Write the requested queries in SQL. The 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 shipname, YearBuilt, PassengerCapacity , PortName for ships that visited the ports.

4. Display ShipName, CruiseSerialNo, SailingDate, PortName, PortCountry and VisitOrder for all the ships that have been assigned to a cruise.

5. List the cruises which have more than 10 bookings?

6. Determine the total docking fee for each cruise.

7. Determine the total price paid by each passenger and display PFirstName, PLastName and total price paid.

8. Which cruises have Istanbul as the departure port (Hint: the visit order should be equal to 1)

9. Which passengers have visited Venice in October 2015? (Hint: you may use the Month and Year functions on SailingDate and see if SailingDate was in October 2015.)

10. What is the duration of each cruise (Hint: Use the DATEDIFF function to determine the duration)

11. What is the number of bookings on each cruise? Display cruiseId and number of bookings.

12. What is the average rating of all the cruises?

13. What is the average rating for cruise with CruiseSerialNo = 1

14. Which cruises have an average rating greater than the overall average rating? Display cruiseid and average rating.

15. Display the ports visited by the cruise, which has the longest duration. (Display PortName, PortCountry, and CruiseID)

16. List the ships which have not been assigned a cruise yet.

17. 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.)

18. Which port has the highest docking fee?

Submit your SQL Server database creation query file and your select query file through LMS (one submission for each group) by the due date.

I added word file manually.

me 16 HW instruction Design Layout Reference Mag Review Helo Can Body - - A A A E B 214 Dal No Head Heading 2 wsgroh asignment Instructions Create a databasein Soforthe relational model provided in the HW Relational Schemat Fopulate your database with some data to test your database write the requested queries in Soc. The relational model that provided is part of the relational model from your HW 2. however it has been timplified The ER diagram for this relational modelis also provided to you in the Hwa_Data Modelle First create an Sol Server database by night clicking on the databasesfolder in the Object explorer and selecting the New Database option Name the database by the initials of your best names and then A3. For instance a database created by Johnson and white would be named as w_41 . write the SQL statements for creating the tables in your database, spediting catatypes and field lengths establishing primary Vays and forenkeys, and implementing at the necessary constraints Save your creke statements in a file. Urethe sameining convention you used to name your database to name your in your file also indude the folowing SQL statements 1 An sol statement to add constant to one of thetables 2. An solitatement to add column to one of the tables. An Statement to insert data to one of the tables 4 An SOL istement to modify the data in one of the tables A SOL statement to delete a row of data from one of the tables Populiste your database with sample data Write the following sol queries and save them in your file also. Put the question numbers above your queries, Q1, 02, etc. Ensure that your queries are corect and produce the results you expected i i passengers who live in Itanbulsorted by last name and then by first name 2. What is the number of passengers whose city was not specified... NULL)? 3. Display the Year Pasar Borttiamafor ships that visited the . ports 4 play he e Porto, Portucatordar for all the thips that have been used to cruise 5 ust the cruises which we more than 10 bookings? 6. Determine the total deckingtee for each cruise 7 Determine the total price pudbyesch passenger and display firstName Plastic and total price paid Which cruises havelstanbul the deatureport in the visit order should be egon toll Which passengers have visited Venice in dober 2015 (rents you may use the Month and functions on and Stoctober 2015) 10. What is the duration of each crust Hit Use the DATEDIFF function to determine the duration 13 Aramak iin buraya yan EH - G Share Com Find - Replace Select- Dictate Editor OK ab Astec ABC ABCD doba MaaboDE ABDICO ABCD De Subite En Emphis Intense Strong Quote Intense Editing Editor Sentity Voice 11. What is the number of bookings on each cruise? Display crueldand number of bookings 12. What is the average rating of all the cruises? 11. What is the average rating for cruise with Cruise SerialNo=1 14 Which cruises have an average rating greater than the overall average rating? Display quised and average rating 15. Display the ports visited by the truse, which has the longest duration (Display PortName, Port Country, and Cruise 16. List the ships which have not been assigned a cruise yet: 17. 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) 18. Which port has the highest docking fee? Submit your SQL Server database creation queryfile and your select query file through LMS (one submission for each group) by the due date. Socus 1922 9.01.20 W3_Data 20Model pdf E A Sesli ovu v World Traveler Cruise Line SHIP Ship Number Ship Name Weight Year Built Passenger Capacity CRUISE Cruise Serial Number Sailing Date Retum Date VISITS Visit Order BOOKING Price Satisfaction itate PORT Port Port Name Country Docking Fee V PASSENGER Passenger Number Name (First Name, Middle Name, Last Namel Address Street Address City, State, Postal Code Country) o G + E A Sesli oku SHIP Shipid ShipName Weight YearBuilt Passenger Capacity CRUISE CruiseSerialNo Sailing Date ReturnDate Shipl Booking CruiseSerialNo Passenger Price Satisfactionate Passenger Passenger PFirstName PlastName PStreetAddress PCityPState PPostalCode PCountry VISIT CruiseSerialNo Portid VisitOrder PORT Portid PortName PortCountry Docking Fee