SQL Lab

Activity 1: How much do you remember?

Let's start off with a little quiz! (Don't worry, it won't be graded by me.) Take the 20 question quiz below. Skip the questions that cover material outside of what you did in ITEC-200. Questions to skip are 3, 4, 5, 13, and 16 to 19.

http://www.w3schools.com/quiztest/quiztest.asp?qtest=SQL

Activity 2: Importing American U Cruise Lines Data into MS Access

It's time for your client to move from Excel to Access. To do so, you'll import all the data from Excel into Access. While doing so, you'll establish Primary Keys for each table. Start MS Access and create a new database called "American U Cruise". Click on "External Data" and "Excel" to import the Excel data into Access. You will have to import each individual worksheet into a table in Access. (There is no way to import all worksheets at once.) Be sure to pay attention to the Primary Key you set for each of the tables. This will be important as you establish relationships. Tables requiring two columns for the Primary Key cannot have the Primary Key assigned during the import process. You'll need to manually establish the Primary Keys after importing the data. The Primary Keys are listed below.

Table Primary Key(s)
VOYAGE PASSENGERNUM, CRUISENUM
CRUISE CRUISENUM
PASSENGER PASSENGERNUM
PORT PORTNAME
SHIP SHIPNUM
VISIT CRUISENUM, PORTNAME
DW What should it be?

Activity 3: Creating an Entity-Relationship Diagram

Now you can build the ER diagram. Keep the following in mind as you create the diagram:

  • Both ship number and ship name are unique in the SHIP Table.
  • A ship goes on many cruises over time. A cruise is associated with a single ship.
  • A port is identified by the combination of port name.
  • As indicated by the VISIT Table, a cruise includes visits to several ports and a port is typically included in several cruises.
  • Both Passenger Number and Social Security Number are unique in the PASSENGER Table. A particular person has a single Passenger Number that is used for all of the cruises that she takes.
  • The VOYAGE indicates that a person can take many cruises and a cruise, of course, has many passengers.

When you are done, export your ER Diagram to RTF (Rich Text) format so you can save it in Word. This is the first part of your lab for this week.

Activity 4: Writing SQL

It's time to write SQL! If you don't remember how, now is the time to use the reference material below as a refresher. Write SQL statements to answer the following questions:

1) Find the passenger name and phone number for the passenger with SSN “356787494”
2) Find the total number of docks available at all ports
3) Find the heaviest ship by weight
4) Find the cruise start and end date (not the port arrival and departure dates) for all cruises into ports in Venezuela
5) Find the fare paid by the passenger with the last name “Brown” where the cruise director was “Lee”

Copy your SQL and the results from your queries into your Word document. This is part two of your lab for this week.

Activity 5: Use the Data Warehouse (DW)

We spent our class last week building a data warehouse. Let's do

Reference

Review SELECT: http://sqlzoo.net/1.htm

Review WHERE: http://sqlzoo.net/1b.htm

Review Joins: http://sqlzoo.net/3.htm

Unless otherwise stated, the content of this page is licensed under Creative Commons Attribution-ShareAlike 3.0 License