IT360 Lab5: SQL

 

DUE: February 12, 2008, BEFORE start of lab

 

In this lab you will develop your database implementation skills by creating, modifying and querying databases in both Access and SQL Server, using SQL.

 

PART 1: Use SQL in Access

 

To use SQL in Access, do the following:

  1. Open a database.
  2. Click Queries under Objects, and then click New on the Database window toolbar
  3. In the New Query dialog box, click Design View, and then click OK.
  4. Without adding tables or queries, click Close in the Show Table dialog box.
  5. From the menu, select View, and then SQL View.
  6. Enter the SQL statement for your data definition or data manipulation query. Each query consists of just one statement. 
  7. Click ! to execute the query.

You will use SQL for all of the following exercises. Do not use the Wizard or Table Design View!

 

 

(Create the database)

Open MS Access and create a new black database named yourlastname_lab5.mdb, where yourlastname is your last name.

 

 

Ex 1.1: (CREATE TABLE)

 

Using SQL, create two tables with the following schema (the primary key is underlined). Do not create a FOREIGN KEY constraint yet. Choose appropriate data type (a list of available data types for Access can be found at http://msdn2.microsoft.com/en-us/library/bb177899.aspx) and null status for each column. Save each query as Create_Crew and Create_Midshipman, respectively.

 

CREW(CrewNumber, CrewName, Motto)

MIDSHIPMAN(Alpha, LName, FName, Email, Phone, ClassYear, CrewNumber)

 

 

After you create the tables, check they are indeed created:

§         From the main database switchboard select ‘Tables’ and you should see the tables CREW and MIDSHIPMAN. 

§         Open each table in the datasheet view and you should see the columns of each table.

§         Switch to the table design view and you should see the data type and field size specification for each column. When you examine the tables in design view, you should notice the primary key that was defined for each table.

 

Ex 1.2: (ALTER TABLE)

 

Check the Relationship option on Tables (right-click Tables). You should see a CREW and MIDSHIPMAN table, and no relationship between them.

 

Task: Write the SQL statement to modify the MIDSHIPMAN table to add a FOREIGN KEY constraint for the CrewNumber column to reference the CrewNumber from CREW table. Save the query as Alter_Midshipman. Execute the query.

 

Check the Relationship option on Tables (right-click Tables). You should see now a relationship between CREW and MIDSHIPMAN.

 

Ex 1.3: (INSERT)

 

a) Write the SQL statement to insert one row in the CREW table. Save the query as Insert_Crew. Execute the query. Modify the query now to insert another row. Execute the new query.

 

b) Write the SQL statement to insert one row in the MIDSHIPMAN table. Save the query as Insert_Midshipman. Execute the query. Try now to insert a midshipman row with a CrewNumber that does not exist in CREW table. You should get an error.

 

Insert few more rows in the MIDSHIPMAN table.

 

Check the results in the datasheet view of the table.

 

Ex 1.4: (SELECT)

 

Write a SQL query to select all 1/C and 2/C Midshipmen (ClassYear 2008 or 2009). Save the query as Select_Midshipman. Run the query and check the results.

 


PART 2: Use SQL in SQL Server

 

Follow the tutorial on the website to connect to the SQL Server.

 

To write SQL queries in SQL Server:

  1. Select the database created for you (m08xxxx).
  2. From the toolbar, select New Query.
  3. Write the SQL statements in the window opened. You can write one-line comments by starting the line with --, or multi-line comments by using C-style comments /* multi-line comment here */.
  4. Click the checkmark button in the toolbar to check the syntax of the SQL statement.
  5. Click the “! Execute” in the toolbar to execute all SQL statements in the file. If you want to execute only one statement, highlight it (select it with the mouse) and then click on “! Execute”.

To save SQL queries: From the menu à Fileà Save As...  Make sure you save queries in a file on your X drive, not the local drive!

 

In this lab, you will continue working on the YP Squadron database that you created in lab3.

 

(Prepare the database)

Make sure all the tables you created for the YP Squadron are in your database. If some tables were not created, you can download the createYPSquadron.sql file from the course website, open it in SQL Server, and execute the CREATE TABLE statements for the tables you are missing. You can highlight one statement and click on “! Execute” button to execute only one statement instead of the entire file. If the tables you already have in your database have different table name or column name from those in the createYPSquadron.sql  file, you might need to modify the statements for the tables you are missing, such that the names match your existing tables.

 

You are highly encouraged to insert few rows in each table, so you are able to check if the results of the SELECT statements you write are correct.

 

 

Now, write SQL queries to accomplish the following tasks. Save all queries for this lab in a file called yourlastname_lab5_SQLServer.sql Write a comment “—Exercise xx”, where xx is the exercise number, before each query.

 

Ex 2.1 (INSERT)

Insert 2 rows in the MIDSHIPMAN table. 

 

Ex 2.2 (UPDATE)

Change the Phone of the midshipman with alpha 080012 to be ‘3-3456’.

 

Ex 2.3 (DELETE)

Remove the midshipman “Matt Smith” from your database. What happens if there are two mids with this name?

 

(SELECT)

 

Ex 2.4

List the first, last name and email of all midshipmen, in ascending order by last name, and descending by email.

 

Ex 2.5

List the alpha, first name, last name, phone number and title for all commanders.

 

Ex 2.6

List the names of all midshipmen in a crew/pennant with motto containing the word “sea”. Each name should appear only once in the result.

 

Ex 2.7

Find the total number of boats.

 

Ex 2.8

List the model name for the boat models with cruising range lower than the average cruising range.

 

Ex 2.9

List the name and alpha for midshipmen who were in the past part of the crew 1 or 2.

 

Ex 2.10

List the name and alpha for midshipmen who were in the past part of the crew 1 AND 2 (not in the same time, since a midshipman can be in only one crew at any given time).

 (Be careful. A condition that contains “CrewNumber = 1 AND CrewNumber = 2” is never true)

 

Ex 2.11

List the class year and the number of mids in that class year, for each class year in the MIDSHIPMAN table that has at least 5 mids.

 

 

Ex 2.12 (EXTRA CREDIT) List the alpha and name of all midshipmen that were, at some time, part of each existing crew. For example, if crew 1, crew 2, crew 3 are all the crews in the CREW table, list the midshipmen that were part of all these crews in the past.

 

 

 

Turn in BOTH electronic and paper copies (due before start of lab on February 12, 2008):

 

Electronic:

  1. Upload the Access database created in Part 1 (Access) to Lab 5 assignment on Blackboard. The database should be named yourlastname_lab5.mdb.  Make sure all tables and queries have the names indicated in the assignment.
  2. Upload the yourlastname_lab5_SQLServer.sql file containing all the SQL statements in Part 2 (SQLServer) to Lab 5 assignment on Blackboard. 

 

Hard-copies:

  1. The completed assignment coversheet. Your comments will help us improve the course.
  2. A print-screen of each query created in Part 1 (Access), in SQL view (so we can grade the SQL statements you wrote)
  3. A hard copy of the yourlastname_lab5_SQLServer.sql