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:
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:
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?
List the first, last name and email of all midshipmen, in ascending order by last name, and descending by email.
List the alpha, first name, last name, phone number and title for all commanders.
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.
Find the total number of boats.
List the model name for the boat models with cruising range lower than the average cruising range.
List the name and alpha for midshipmen who were in the past part of the crew 1 or 2.
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)
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):