Question

SQL Queries 1) Use the following relations to answer the problems below. Person(SSN, Age, Name) Professor(SSN, Rank, Specialty) GraduateStudent(SSN, DegreeProgram, AdvisorSSN, DepartmentNumber) Project Number, StartDate, EndDate, Budget, SponsorOrganizationName) DepartmentNumber, Name, Abbreviation, MainOffice, ChairmanSSN) WorksFor(ProfessorSSN, Department Number, TimePercentage) WorksOn(GraduateStudentSSN, ProjectNumber, SupervisingProfessorSSN) Retrieve the Name and Specialty of all Professors who do not supervise work projects. This solution must meet the following conditions: D a. Uses at most one inner joirn b. Uses at most one outer join cNo subqueries al d. All other operators not mentioned in a through c are allowed ) Find the Name and Department Name of all Graduate Students who work on projects that do not employ any other graduate students from their department. For example, if a Biology Grad Student works on a project, they would be retrieved if and only if no other Grad Students from the Biology department work on that project.

Answer 1

a)Select SSN,Speciality from Professor where SSN not in(Select Professor.ssn from Professor inner join Workson on Professor.SSN-WorksOn.SupervisitingProfessorSSn) b)Select Professor.SSN, Professor.Speciality from Professor FULL OUTER JOIN Workson on Professor.SSN-WorksOn.SupervisitingProfessorSSm where SupervisitingProfessorSSn is null; c)Select Professor.SSN,Professor.Speciality from Professor FULL OUTER JOIN Workson on Professor.SSN-WorksOn.SupervisitingProfessorSSn where SupervisitingProfessorSSn is null; d)Select SSN,Speciality from Professor where SSN not in(Select Professor.ssn from Professor inner join Workson on Professor.SSN-WorksOn.SupervisitingProfessorSSn)

j) Select WorksFor.DepartmentNumber from WorksFor inner join workson on worksFor.SSN=worksOn.SupervisitingProfessorssn FULL OUTER JOIN Department on Department.Number-WorksFor.DepartmentNumber where SupervisitingProfessorSSn is null;

if you still have any Problem regarding this question please comment and if you like my code please appreciate me by thumbs up thank you.........