COSC4336: Database Systems 15. By using the NOT EXISTS operator, execute a SQL s
ID: 672263 • Letter: C
Question
COSC4336: Database Systems
15. By using the NOT EXISTS operator, execute a SQL statement to display
the names of the skills not required by the duty President.
16. Create a table NEW_CLUB with the same structure as CLUB. Execute a SQL
statement to copy the records from CLUB to NEW_CLUB.
17. By using an inner join, display the students’ names along with their mentors’
names.
18. By using the LEFT OUTER JOIN operator, execute a SQL statement to
display the names of the skills from the table SKILL and the names of
mentors who provide training for these skills from the table MENTOR.
19. By using the RIGHT OUTER JOIN operator, execute a SQL statement to
display the names of the mentors who provide training for the skills from
the table MENTOR and the names of the skills from the table SKILL.
20. By using a self join, execute a SQL statement to display the mentors’ names
and the pairs of the last names of the students who are mentored by the
same mentors. Order your result by the mentors’ names.
Figure 6.1 Tables in Student_Club database: (a)CLUB, (b)SKILL, (c)SERVICE, (d)MENTOR, (e)STUDENT, (f)STUDENT_MENTOR, (g)STUDENT_CLUB, and (h)STUDENT_SERVICE
Explanation / Answer
Now we have the different table given.
15)Now we have to use the NOT EXIST operator to execute the statement.
SELECT skillsSet FROM skill WHERE NOT EXISTS ('a','b','c');
It has been assumed that the a, b , c are the name of the skills which are not required to be displayed in the ouptput after triggering the query.
This excludes the name of the skill which are not required by the president.
16) For creating the new table with the same structure of the another table we use the following statemenet.Note that this copies records from old table to the new table..The SQL queries
is :-
create table new_club as (select * from club);
17)For the inner joins there should be the foreign key which point to the next table on acording to which the data can be retrieved by satisfying the key conditions.The SQL query for this
part is as:-
Select Sname Mname
from Student Mentor
where (Student.team=Mentor.team);
This query has been written on assumption that there is entities Sname and Mname and the foreign key is the team name.Because the mentor and the student always belong to the same
team.
18)In case of the Left Outer Join all the rows from the first table mentioned in the query is selected, regardless whether there is a matching row on the second table mentioned on SQL
query.The query is as :-
SELECT skillName,Mname
FROM Skill
LEFT OUTER JOIN Mentor
ON skill.subject=Mentor.subject;
The join condition has been made on the basis of the subject category ,assuming there lies an student entry in both the tables.
19)It is same as the previous part above,the only difference is that it will take the entire data from the right table.The query is as given below:-
SELECT skillName,Mname
FROM Skill
Right OUTER JOIN Mentor
ON skill.skillname=Mentor.skillname;
20)Self join means to join the table against itself.Here we have to display the Mentor Names and the last name of the student.The query is as follows:-
SELECT a.Mname, b.Lname
FROM Skill a, Mentor b
WHERE a.skillset = b.skillset;