Student: studentNO, firstName, lastName, studentProgramProfessor: professorID, professorProgram, professorNameStudent_professor: studentNO, professorID, mentorComplete the following requirements:Using the UNION operator, do a full outer join to display the student last name and student program as well as the professor name and professor program. No JOIN operator is to be used in this requirement.Create a query/subquery to display student first name, last name and program. The student program must be same as that in the professor table and the professor program name starts with a ‘C’. This query will use a subquery and not a join.Create a query to display the professor ID, professor name and mentor column including records where the professor ID is not on the student_professor table. Note, this will be a left join. Make sure to eliminate duplicates from the output.Display the average age of students in each student program.Display the number of students in the Computer Science program.Display the youngest age of all students.Display the oldest age and average age of all professors.Display the number of mentored students for each ProfessorId.

SQL: SQL stands for Structured Query Language. It is used to acess and manipulate the databases.…

Answered: Student: studentNO, firstName,…

Database System Concepts

7th Edition

ISBN:9780078022159

Author:Abraham Silberschatz Professor, Henry F. Korth, S. Sudarshan

Publisher:Abraham Silberschatz Professor, Henry F. Korth, S. Sudarshan

Chapter1: Introduction

Section: Chapter Questions

Problem 1PE

See similar textbooks

Related questions

Concept explainers

Question

Student: studentNO, firstName, lastName, studentProgram

Professor: professorID, professorProgram, professorName

Student_professor: studentNO, professorID, mentor

Complete the following requirements:
1. Using the UNION operator, do a full outer join to display the student last name and student program as well as the professor name and professor program. No JOIN operator is to be used in this requirement.
2. Create a query/subquery to display student first name, last name and program. The student program must be same as that in the professor table and the professor program name starts with a ‘C’. This query will use a subquery and not a join.
3. Create a query to display the professor ID, professor name and mentor column including records where the professor ID is not on the student_professor table. Note, this will be a left join. Make sure to eliminate duplicates from the output.
4. Display the average age of students in each student program.
5. Display the number of students in the Computer Science program.
6. Display the youngest age of all students.
7. Display the oldest age and average age of all professors.
8. Display the number of mentored students for each ProfessorId.

Expert Solution

Step by step

Solved in 2 steps

SEE SOLUTION Check out a sample Q&A here

Knowledge Booster

Learn more about

Need a deep-dive on the concept behind this application? Look no further. Learn more about this topic, computer-science and related others by exploring similar questions and additional content below.

Similar questions