FIT 100: Assignment 3 Answers

A) Show all students from the Student table.

SELECT *

FROM tblStudent;

B) Show all students in alphabetical order by last name.

SELECT *

FROM tblStudent

ORDER BY LName Asc;

C) Show only the Advisor Names from a particular Department [insert name of one of the Departments in the Advisor table].

SELECT tblAdvisor.LName

FROM tblAdvisor

WHERE tblAdvisor.Department =”INFO”;

D) Show all Advisors and the Students they advise. (Requires a Join)

SELECT tblAdvisor.LName, tblAdvisor.FName, tblStudent.LName, tblStudent.FName

FROM tblAdvisor INNER JOIN tblStudent ON tblStudent.AID = tblAdvisor.AdvisorID;

E) Show the last name and first name of [insert Major Name] majors. (Requires a Join)

SELECT tblStudent.LName, tblStudent.FName

FROM tblMajor INNER JOIN tblStudent ON tblStudent.Major = tblMajor.MajorID

WHERE tblMajor.Major = “CSE”;

 HAD TO BE DONE IN QBE TO GET THE CORRECT SQL (it's a hard one!)

F) Show all advisors working with ART Majors. (Requires a Join)

SELECT tblAdvisor.LName, tblAdvisor.FName

FROM tblMajor INNER JOIN (tblAdvisor INNER JOIN tblStudent ON tblAdvisor.AdvisorID = tblStudent.AdvisorID) ON tblMajor.MajorID = tblStudent.Major

WHERE tblMajor.Major=”ART”;

 HAD TO BE DONE IN QBE TO GET THE CORRECT SQL (it's a hard one!)

G) Show each student and their advisor, the advisor’s department and their major. (Requires a Join)

SELECT tblStudent.LName, tblStudent.FName, tblAdvisor.LName, tblAdvisor.FName, tblAdvisor.Department, tblMajor.Major

FROM tblMajor INNER JOIN (tblAdvisor INNER JOIN tblStudent ON tblAdvisor.AdvisorID = tblStudent.AdvisorID) ON tblMajor.MajorID = tblStudent.Major;