Docsity
Docsity

Prepare for your exams
Prepare for your exams

Study with the several resources on Docsity


Earn points to download
Earn points to download

Earn points by helping other students or get them with a premium plan


Guidelines and tips
Guidelines and tips

SQL Lab 2 - ITEC 4770 Client/Server Systems - Prof. Dan R. Lipsa, Lab Reports of Information Technology

The instructions for lab 2 of the itec 4770 - client/server systems course, focusing on sql queries. Students are required to take snapshots of sql queries and their results, attach a database, and hand in the document by february 9th. The lab covers various sql exercises, such as returning student and course information, identifying courses offered in specific years, and deleting or inserting student data.

Typology: Lab Reports

Pre 2010

Uploaded on 08/04/2009

koofers-user-1lx-1
koofers-user-1lx-1 🇺🇸

4

(1)

10 documents

1 / 2

Toggle sidebar

This page cannot be seen from the preview

Don't miss anything!

bg1
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.
pf2

Partial preview of the text

Download SQL Lab 2 - ITEC 4770 Client/Server Systems - Prof. Dan R. Lipsa and more Lab Reports Information Technology in PDF only on Docsity!

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.
  8. Semester  primary key sem_id
  9. Course  primary key course_id  foreign key sem_id referencing Semester
  10. Student  primary key stud_id
  11. 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:
  12. 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.
  13. Return student names, course names, and semester names for all students that have taken courses.
  1. Return the names of all courses offered in 2003.
  2. How many students have taken the course ITEC 4770 in spring 2004? Hint: use the COUNT(*) function in your query.
  3. Return the names of the students that took ITEC 4770 in spring 2004.
  4. Return the course name, semester name, and year for all courses taken by any student whose name begins with ‘Chris’.
  5. Return the total number of courses that were offered in 2003.
  6. Return the total number of courses that were taken in 2003 by any students whose name begins with ‘Lisa’.
  7. What if we wanted to delete information for the student ‘Sandra Dee’? What delete statements (if any) should be executed prior to deleting her row from the Student table?
  8. Write the insert statements needed to insert a new student named ‘James Brown’ , stud_id is 9797, and Stud_AASU_id is 856-00-0017. He is also taking the course with course_id ‘3435’. Extra Credit. Return the names of the course and their course id for all courses that have not been taken by any student.