Assignment: SQL #2
Putting Data into the Database

To do this assignment, you will be working with the faculty and staff directory database that you used for the in-class exercise (mxdirectdb). You can refer to that document for the database structure and metadata.

Remember, you will need to log in using your account, and the directdb schema will reflect your username. So if you are logging in as m45, the schema name for you will be m45directdb.

You will be further modifying the database using SQL UPDATE, INSERT, and DELETE statements where appropriate according to the scenarios below. You may need more than one statement, especially if you are changing multiple tables or performing multiple operations.

NOTE: Even if you use SQL Workbench to assist you in constructing the queries, you should be prepared to come up with the syntax on your own without help! You should try doing this on your own (entering the queries directly into the SQL pane) until you are comfortable with the syntax.

Guidelines

  • You must submit your answers electronically in a single Word document. You can copy and paste the SQL query from SQL Workbench.
  • You must include your name at the top of the document.
  • Your answers should be emailed, as an attachment, to your instructor with the subject:

MIS2502: SQL Assignment #2

  • The email must be sent by the start of class the day the assignment is due.

If you do not follow these instructions, your assignment will be counted late.

Evaluation

Your submission will be graded based on the correctness of the SQL queries that update the database according to each scenario.

Problems:

1)Add the following new employees to the database (hint – use 6 INSERT statements):

Full name / David Short / Dr. Liz Friedman / Sara Hess
Full title / Director of Student Affairs / Adjunct Professor / Associate Professor
Office address / 111 Alter Hall / 605 Alter Hall / 213 Caribou Hall
Phone / 215-204-9665 / 215-204-6834 / 215-204-4254
Email / / /
Faculty or staff / Staff / Faculty / Faculty
Department(s) / Finance (primary)
Accounting / Risk, Insurance, and Health Care Management / Marketing and Supply Chain Management

2)Add this new building to the database:

Name: Morgan Hall
Address: 1601 N. Broad Street

3)Remove Caribou Hall from the database (you added it in the in-class exercise).

4)Now that you have removed Caribou Hall, there are people without offices. Re-assign the following faculty to new addresses:

Full name / Sam Jones / Sara Hess
New office address / 334 Morgan Hall / 440 Alter Hall

5)So many of the faculty are using Skype at work that we want the database to now keep track of employees’ Skype account names. Add a field to the facultystaff table called “SkypeID.”
(hint: ALTER the TABLE)
Now add entries for the SkypeID of the following employees (you will need multiple statements to do this):

Full name / SkypeID
David Short / dshort
Sam Jones / sjones
Joe Smith / frogman
Liz Friedman / lfriedman
Sara Hess / sarahess