ITEC 4770 – Client/Server Systems
Lab 2 – SQL Review
Due date: February 9th, before class
Take snapshots of the SQL queries used to solve the following exercise and of the results of
those SQL queries. Insert the snapshots into a document, print it out and hand it over in
class on the due date.
Setup
1. Download and extract the zip file SQLDB_Lab2.zip containing the database:
SQL_Lab2_Data.MDF
SQL_Lab2_Log.LDF
2. Open Microsoft SQL Server Management Studio.
3. Open the directories until you get to Databases
4. Right-click Databases and select Attach...
5. Click the ellipsis in order to find and select the MDF file to attach
(SQL_Lab2_Data.MDF).
6. It will fill in the information for the MDF and LDF files.
7. Click OK. Wait for the success message and click OK again.
Check the tables – you should have a new database, SQL_Lab2 that contains four user
tables.
1. Semester
primary key sem_id
2. Course
primary key course_id
foreign key sem_id referencing Semester
3. Student
primary key stud_id
4. Stud_course
Composite primary key of both stud_id and course_id
foreign key stud_id referencing Student
foreign key course_id referencing Course
Assignment Exercises – write SQL statements to accomplish the following:
1. Return the names of all students who took at least one course in any semester.
Use the keyword DISTINCT in your SELECT statement.
The DISTINCT keyword eliminates duplicate rows from the results of a
SELECT statement. If DISTINCT is not specified, all rows are returned,
including duplicates.
2. Return student names, course names, and semester names for all students that
have taken courses.
