Question

You have been given a univerity business plan that produced the
following information:
    PERSON: lname, fname, rname, idnum, private, linkblue
        lname, fname, rname are variable length strings
        idnum is type INT and is a key attribute
        private is BOOLEAN
        linkblue is string of 8 characters 

CREATE TABLE PERSON (idnum INT NOT NULL PRIMARY KEY, lname varchar(40), fname varchar(40), rname varchar(40), private BOOLEAN, linkblue char(8) NOT NULL);

    ADVISOR: student, advisor
        student is a foreign key to PERSON.idnum
        advisor is a foreign key to PERSON.idnum

CREATE TABLE ADVISOR (student INT NOT NULL, advisor INT NOT NULL, student_name VARCHAR(40), advisor_name VARCHAR(40), PRIMARY KEY (student, advisor), FOREIGN KEY (student) REFERENCES PERSON (idnum), FOREIGN KEY (advisor) REFERENCES PERSON (idnum));

#10 The student advisor needs a single list of the following:
    "status" where status is the string either "student" or 
    "advisor", fname, lname, email address.  The list should 
    contain all students at most once, and all advisors at most 
    once.  The advisors should be first and the students second.  

    Like:

    +---------+-------+-----------+-----------------+
    | status  | fname | lname     | email           |
    +---------+-------+-----------+-----------------+
    | advisor | Jane  | Mather    | [email protected] |
    | student | John  | Dillinger | [email protected]   |
    | student | Jane  | Mather    | [email protected] |
    +---------+-------+-----------+-----------------+

    Sort by status and lname.

Answer 1

Answer)
As per the given table, SQL query for listing the student advisor needs a single list of the following "status" where status is the string either "student" or
"advisor", fname, lname, email address:

select "advisor" as status,

Person.fname,

Person.lname,

Person.linkblue as email
from Person inner join ADVISOR
on Person.idnum = ADVISOR.advisor
UNION
select "student" as status,

Person.fname,

Person.lname,

Person.linkblue as email
from Person inner join ADVISOR
on Person.idnum = ADVISOR.student
order by status, Person.lname;

**Please Hit Like if you appreciate my answer. For further doubts on the question or answer please drop a comment, I'll be happy to help. Thanks for posting.**