Question

I need help with the next TWO SQL question

Table name: STUDENT STU CODE PROF CODE 100278 128569 512272 531235 531268 553427 Table name: PROFESSOR PROF CODE DEPT CODE 26 36 Figure 1 Concepts Question 1.1: Using the STUDENT and PROFESSOR tables displayed in Figure 1, discuss (illustrate) the difference between 4 types of joins; 2 inner joins (natural join and equijoin) and 2 outer joins (left-outer join and right-outer join). For the purposes of the outer joins, assume the STUDENT table is the "left" table and the PROFESSOR table is the "right" table. For each of the four different join types, be sure to state what columns are the "join columns". Also be sure to show the results of each join condition. (Consult your textbook; Chapter 3- Section on Join, for a discussion joins.)

Concepts Question 1.2: Discuss what type of relationship exists between STUDENT and PROFESSOR. (Recall that relationships can be in the form of 1-1, 1-M, M:N.)

Answer 1

equi join --> this join uses one common column from two table and applies 'ON' condition on that column and applies equality operator

here in student and professor table PROF_CODE is common column

so after applying equi join

it can be written as

select * from STUDENT s JOIN PROFESSOR p ON s.PROF_CODE = p.PROF_CODE

it will give you below structure

STU_CODE	PROF_CODE	DEPT_CODE
125869	2	6
512272	4	4
531235	2	6
553427	1	2

Natural Join is an implicit join clause based on the common columns in the two tables being joined. Common columns are columns that have the same name in both tables.

it can be written as

select * from STUDENT NATURAL JOIN PROFESSOR  

LEFT OUTER JOIN : after applying this join all columns from left table will be taken and column from right table will be null if no value is present

here

select * from STUDENT s LEFT JOIN PROFESSOR p ON s.PROF_CODE = p.PROF_CODE

STU_CODE	PROF_CODE	PROF_CODE	DEPT_CODE
100278
128569	2	2	6
512272	4	4	4
531235	2	2	6
531268
553427	1	1	2

Right outer join after applying this join all columns from right table will be taken and column from left table will be null if no value is present..

select * from STUDENT s RIGHT JOIN PROFESSOR p ON s.PROF_CODE = p.PROF_CODE

TU_CODE	PROF_CODE	PROF_CODE	DEPT_CODE
553427		1	2
128569	2	2	6
		3	6
512272	2	4	4

Question 1.2

Here the type of relation between STUDENT and PROFESSOR is 1:M as profeesor code 2 has more than 1 student code (2)