A Practicle Guide to Using SQL in Oracle Chapter 3 - 1 e and f: Table 1: Stu sna

Question

A Practicle Guide to Using SQL in Oracle Chapter 3 - 1 e and f:

Table 1: Stu

sname majorCode

---------- --------------

Jones CS

Smith AC

Evans MA

Table 2 :Major

majorCode majorDesc

------------- ---------------

AC Accounting

CS Computer S

e - display all the student names (sname) and major descriptions (majorDesc), nut also show all the majors, even if there are no students with that major. Display your query both ways: First using ANSI SQL standard syntax, and then using Oracle's driving table (+) concept.

f- Add two more students, Arpana and Ayona, to the Stu table with null values for majorCode. Display all the student names (sname) and major descriptions (majorDesc) whether or not the students have a major. Display your query both ways: First using ANSI SQL standard syntax, and then using Oracle's driving table (+) concept.

Explanation / Answer

Hi,

Below is the answer-

Ans e -
ANSI Syntax-
select sname, majorDesc from Stu s
right outer join Major m
on s.majorCode=m.majorCode

Oracle (+) concept Sytax-
select sname, majorDesc from Stu , Major
where Stu.majorCode(+)=Major.majorCode

Ans f-
Inserting values to the Stu table.
insert into Stu values('Arpana',NULL);
insert into Stu values('Ayona',NULL);

ANSI Syntax-
select sname, majorDesc from Stu s
left outer join Major m
on s.majorCode=m.majorCode

Oracle (+) concept Sytax-
select sname, majorDesc from Stu , Major
where Stu.majorCode=Major.majorCode(+)

Regards,

Vinay Singh

Related Questions

Navigate

• Browse (All)
• Browse A
• Subjects