SQL PLUS exercies As you do the exercises, it is a good idea to copy/paste your

Question

SQL PLUS exercies
As you do the exercises, it is a good idea to copy/paste your query as well as your query result into a word processor.
3-1.
Stu sname Jones Smith Evans Adams
Create two tables, Stu(sname, majorcode) and Major(majorcode, majordesc) with the data shown. Use VARCHAR2(2) for codes and appropriate data-types for the other attributes.

a.) Display the Cartesian product (no WHERE clause). Use SELECT *.... How many rows did you get? How many rows will you always get when combining two tables with n and m rows in them (Cartesian product)?

b.) Display an equi-join of Stu and Major on majorCode (show this both ways: first using an appropriate WHERE clause; and second, using ANSI SQL standard syntax). Use table aliases. How many rows did you get?

c.) Leave off the column qualifiers (the aliases) on the equi-join in step b. What do you get? This will give an error because of ambiguous column names.

d.) Use the COUNT(*) function instead of SELECT * in the query. Use COUNT to show the number of rows in the result set of the equi-join and the Cartesian product. Do the equi-join first with COUNT, then comment out the WHERE clause for the second answer (put -- in front of the word WHERE).

e.) Add two more major codes to the Major table as follows: <’IT’, ‘Information Technology’> and <’ST’, ‘Statistics’>. Display all the student names (snames) and majorDescs, but I would also like to see all the majors listed, even if there are no students taking that major. Show your query in both ways: first, using ANSI SQL standard syntax, and then using Oracle’s driving table(+) concept

f.) Add two more students to the Stu table as follows: <’Arpan’, NULL> and <’Ayona’, NULL>. 87 Display all the student names (snames) and majorDescs, whether the students have a major or not. Show you query both ways: first, using ANSI SQL standard syntax, and then using the Oracle’s driving table (+) concept.

g.) Display all the student names (snames) and majorDescs. Make sure all the students as well as all the majors are shown in the result set, whether the students have a major or not, and whether the major has students or not.

Here is the table

Explanation / Answer

// Table creation
CREATE TABLE Stu (
sname VARCHAR(30),
majorCode VARCHAR(2)
)
CREATE TABLE Major(
majorCode VARCHAR(2),
majorDesc VARCHAR(30)
)
a)
// cartesian produc
select * from stu CROSS JOIN Major;
It will gives the 12 records
1-1
1-2
1-3
2-1
2-2
2-3
like this it will give up to 4 - 3
b) equi-join
select * from stu s, major m where s.majorCode = m.majorCode;
return only 4 rows from the stu table
c) Leaving column qualifier
select * from stu s, major m where majorCode = majorCode;
it is throwing error : ambiguous column names in the query
d)
select count(*)  from stu CROSS JOIN Major;  
answer is 12
select count(*) from stu s, major m where s.majorCode = m.majorCode;
answer is 4
e)
insert into major values('IT','information Technology');
insert into major values('ST','Statistics');
select s.sname, m.majorDesc from stu s, major m left join major on s.majorCode = m.majorCode;
f)
insert into stu values('Arpan',Null);
insert into major values(Ayona,Null);
select s.sname, m.majorDesc from stu s, major m left join stu on s.majorCode = m.majorCode;
g)
select s.sname, m.majorDesc from stu s, major m right outer join stu on s.majorCode = m.majorCode;

Related Questions

Navigate

• Browse (All)
• Browse S
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.