CS 3431 Assignment 1: Basic SQL solution

Description

Submission: zip your tour1.sql and tour2.sql files and submit them to Canvas using the Assignment 1 heading link.

The homework is to be done individually. You may speak to your classmates about the assignment but cannot exchange information on the actual SQL code that needs to be written.

You will be creating a database to keep track of tours that vacationers have reserved. The data is located in the spreadsheet CS3431-A20 Assignment.xlsx. There are 4 tables, one on each spreadsheet tab: Reservation, Customer, Tour and Guide. The fifth tab, Template, is an example of how to automatically generate the insert statements so you DO NOT manually type every line.

Use a text editor to create your SQL commands below. Note: because of the referencing done, there is a specific order that you need to follow in the creation of the files. Likewise, there needs to be a specific order in which the tables are dropped when the code is re-run.

1. Creation of the tables. Create a file named sql for the following SQL commands.
   1. The first four commands will delete the Reservation, Customer, Tour, and Guide tables so you can run your tour.sql file over and over.
   2. (25 points) Write the SQL commands to create the four tables following the instructions below:
      1. For each table, the field name and datatypes are given in the spreadsheet
      2. The first column of each table is the primary key. For all of the tables except for the Tour table, create sequences for the primary key.

• The reservation table contains 3 foreign keys referencing the other three tables.

1. In the Customer table, the Age field must be non-null.

2. Create a file named sql for the following SQL commands.
   1. (25 points) list all of the customers who are taking either the Duck Tour or the Golden Gate tour. Include the customer first name, customer last name, and the name of the tour. Sort the results in alphabetical order by customer last name, and then by customer first name. Use natural joins.
   2. (25 points) list tours that have both customers who are over 21 and guides who drive buses. Include the travel date, customer first name, customer last name, customer age, guide first name and guide last name. Display the customer’s first and last names in a column called CustomerName. Display the guide’s first and last names in a column called GuideName. Use theta joins.
   3. (25 points) list the five tours and the names (first and last) of the guides who will be giving those tours. Include the tour name and the guide’s first and last name. Sort by tour name in reverse alphabetical order, followed by last name in reverse alphabetical order, and make sure there are no duplicate listings of the guides for the same tour. For example, the Duck Tour should not list Noah Smith twice, but Noah Smith can also appear as the tour guide for the Freedom Trail. Use theta joins.

Although you may use Oracle LiveSQL to do this assignment, you may want to get some experience using the WPI Oracle server (or use both for more experience). Use Filezilla to copy your SQL files between your local computer and the linux.wpi.edu server. Use the @ command at the sqlplus prompt to run the file:

sqlplus>   @tour.sql

If you encounter difficulties with step 2, create an SQL command that does part of the problem, and then add another statement or comparison to the SQL command.