Question

1. Write the query that lists the PID, FirstName, LastName, MajorCode, and MajorName of every student.

2. Write the query that lists the PID, FirstName, LastName, MajorCode, and MajorName of every student majoring in a discipline with the word 'undecided' in the MajorName field. DO NOT LOOK UP THE Majors with undecided in their name and then filter on the MajorCodes!!! We must write queries as if we cannot see the data in them. All we know is the metadata and any information included in the problem statement.

3. List the first and last names and the states of every student and advisor pair in which both are from the same state. This means the faculty member must be assigned as the student's advisor and they are both from the same state. It does NOT mean finding every faculty & student who share the same state but are not in an advising pair. Sort on the student's lastname and then firstname within lastname.

4. Write the query that will produce the office number and building where the student with PID of PID01472 can find his or her advisor. Just display the office number and office building.

5. Write the query to display the Student.MajorCode, the MajorName, and a count of the number of students in the major. Do not worry about majors with no students. (Hint: aggregate function & group by)

the file its pulling from has: Student, Major, Faculty, and College as databases to use.

Answer 1