IT360 Lab 5: SQL

 

DUE: February 19, 2010, BEFORE start of lab

 

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

 

 

PART 1: Use SQL in MySQL

 

Follow the tutorial on the website to connect to MySQL.

 

You will use SQL for all of the following exercises.  You will use the tables you created in the previous lab.

 

Save all of the following queries in Part 1 in a file called yourlastname_lab4_MySQL.sql

 

  1. (ALTER TABLE)

 

Write the SQL statement to modify your MIDSHIPMAN table to add a new column called DateOfBirth. Choose the appropriate data type for this column. The values in the column can be null.

 

  1. (INSERT)

 

a) Write the SQL statement to insert one row in the CREW table. Execute the query. Insert one more row into the table.

 

b) Write the SQL statement to insert one row in the MIDSHIPMAN table. 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 by executing the following statement: SELECT * FROM MIDSHIPMAN

 

  1. (SELECT)

 

Write a SQL query to list all columns for all 1/C and 2/C Midshipmen (ClassYear 2010 or 2011) in the MIDSHIPMAN table. 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 (mxxxxxx).
  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 work on the YP Squadron database that you created in lab 3, this time re-creating the tables and asking queries in SQL Server instead of MySQL.

 

(Prepare the database)

Download the createYPSquadron.sql file from the course website, and open it in SQL Server. Read the file, to familiarize yourself with the tables and column names. Execute the file. It should create all the tables for the YP Squadron, similar with the ones you created in the previous lab for MySQL. 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 art in a file called yourlastname_lab4_SQLServer.sql Write a comment “—Exercise xx”, where xx is the exercise number, before each query.

 

  1. (INSERT)

Insert 2 rows in the MODEL table. 

 

  1. (UPDATE)

Change the MaxSpeed of the model with name ‘YP103’ to be 15.

 

  1. (DELETE)

Remove the model with MaxOccupancy 30 from your database. What happens if there are two models with this occupancy?

 

(SELECT)

 

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

 

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

 

  1. 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.

 

  1. Find the total number of boats.

 

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

 

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

 

  1. 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.

 

 

  1. (EXTRA CREDIT) 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)

 

 

  1. (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 19, 2010):

 

Electronic:

  1. Upload the yourlastname_lab4_MySQL.sql file containing all the SQL statements in Part 1 (MySQL) to Lab 4 assignment on Blackboard.
  2. Upload the yourlastname_lab4_SQLServer.sql file containing all the SQL statements in Part 2 (SQLServer) to Lab 4 assignment on Blackboard.

Hard-copies:

  1. The completed assignment coversheet. Your comments will help us improve the course.
  2. A hard copy of the yourlastname_lab4_MySQL.sql and yourlastname_lab4_SQLServer.sql files.