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
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.
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
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:
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.
Insert 2 rows in the MODEL table.
Change the MaxSpeed of the model with name ‘YP103’ to be 15.
Remove the model with MaxOccupancy 30 from your database. What happens if there are two models with this occupancy?
Turn in BOTH electronic and paper copies (due before start of lab on February 19, 2010):