IT230 - Phase 3 SQL Statements Single table queries: Create a" query that returns student last name, first name, and email address. Sort by last name....

IT230 - Phase 3 SQL Statements

Single table queries:

1.    Create a" query that returns student last name, first name, and email address. Sort by last name.

2.    Create a query that return student last name, first name, advisor (ProfID) and email address for any student who doesn't have an advisor or an email address. Sort by last name.

3.    Create a query that returns professor rank, last name, first name and email address. Sort by rank then last name.

4.    Create a query that returns professor rank, last name, first name and email address whose rank is Professor.

5.    Create a query that returns building name and location. Sort by location.

Multiple table queries:

1.    Create a query that returns Advisor Last Name, Advisor First Name, Student Last Name, and Student First Name. Sort by Advisor Last Name then Student Last Name.

2.    Create a query that returns School Name, School Dean Last Name, School Dean First Name, Department Name, Department Chair Last Name, and Department Chair First Name. Sort by School Name and Department Name.

3.    Create a query that returns Department Name, Course Title, Course Description, and Course Credits. Sort by Department Name then Course Title.

4.    Create a query that returns Student Last Name, Student First Name, Course Title, Class Section, Class Time, Professor Last Name, Professor First Name, Room Code, Room Type, Building Name and Building Location. Sort by Student Last Name then Course Title

Grades:

1.    Create update queries that will change the grades for the following students and classes:

StudID  ClassCode           Grade

PP001  CL001                                      A

PP001  CL002                                      B

AA001  CL009                    B+

JE001   CL009                                      C

KN001  CL003                     F

You only need to include the update queries in the Word document.

Using Management Studio, enter grades for all the other rows in the Enroll table. 

2.    After all the grades have been entered, create a query that returns Student Last Name, Student First Name, Course Title, Grade, and Number of Credits. Sort by Student Last Name.