We use cookies to give you the best experience possible. By continuing we’ll assume you’re on board with our cookie policy

Cartesian Product

The whole doc is available only for registered users

A limited time offer! Get a custom sample essay written according to your requirements urgent 3h delivery guaranteed

Order Now

1Q)Briefly describe a Cartesian product is formed when?

Ans: A Cartesian product is formed, when a join condition is invalid or omitted completely. All rows in the first table are joined to the all rows in the second table.To avoid a Cartesian product,always include a valid join condition in a WHERE clause.

02) Briefly describe how you can avoid a Cartesian product?
Ans: To avoid a Cartesian product we should always include valid join condition in a WHERE clause, unless you have a specific need to combine all rows from all tables.Generally when you are joining n tables,you need to join atleast n-1 conditions.

03) Briefly describe what are DBMS proprietary Joins?
Ans: The DBMS Proprietary Joins are as follows:
Oracle Proprietary Joins:
->Outer join
->Self join
->Simple join
->Inner join

04) Briefly describe when join query data from more than one table and the same column name appears in more than one table, how you can resolve it? Ans: Use a join to query data from more than one table.

->We should write the join condition in the WHERE clause. ->We should prefix the column name with the table name when the same column name appears in more than one table.

05) Briefly describe when join n tables together; you need a minimum how many join conditions? Ans: To join ‘n’ tables together, you need minimum of ‘n-1’ join conditions. For Example, if you want to join 10 tables together, you need minimum of 9 join conditions. OR

Employee & manager(2tables) where emp.empno=manager.empno.

06) Briefly describe what are SQL 1999 Compliant Joins?
Ans:SQL:1999 Complaint Joins:
->Cross joins
->Natural joins
->Using clause
->Full or two sided outer joins
->Arbitrary joins conditions for outer joins.

07) Briefly describe what purpose to using outer join is and how to use it? Ans: The purpose of using outer join is to see the rows that don’t meet the join condition. It returns all rows that satisfy the join condition. We can use the outer join by using plus sign enclosed in parentheses ‘(+)’. For Example,

SELECT table1.column, table2.column FROM table1, table2
WHERE table1.column = table2.column (+);

08) Briefly describe SQL 1999 “CROSS JOIN”?
Ans: The CROSS JOIN clause gives the cross product of two tables. This is same as the Cartesian product between the two tables. SELECT last_name, department_name FROM employees, departments;

09) Briefly describe SQL 1999 “NATURAL JOINS”? Ans:
->The NATURAL JOIN clause is based on all columns in the two tables that have the same name ->It selects rows from the two tables that have equal values in all matched columns. ->If the columns have the same name and different data types, then an error occurs. Therefore, it should have the same name and same data type. ->It hides the join process from the user who gets the result.

10) Briefly describe what are the guidelines when using sub-queries?

Ans: Guidelines for using Subquery
->Place subqueries on the right side of the comparison condition. ->The ORDER BY clause in the subquery is not needed unless you are performing top-n analysis. ->If you want to check for existence of a single value within a set of other values with in a set of other values,use the IN keyword as an operator upon the result set from a sub query. 3rd

1)Select * from department,empply;

90 rows selected.

1 Select * from empply,department where empply.deptid=department.departmentid;

3)select e.empid,e.firstname,e.managerid,d.managerid,d.locationid from empply e.department d where e.deptid=d.departmentid;

4)Create table location(locationid number(4) not null primary key,city varchar2(30) not null);Select * from location

Desc location;

5)select e.empid,e.firstname,e.lastname,d.departmentname,l.city from empply e,department d,location l where e.deptid=d.departmentid and d.locationid=l.locationid order by empid;

6)select d.departmentname,e.firstname,e.lastname from department d,empply e where d.departmentid=e.deptid;

7)select ‘empply’ || worker.firstname || ‘work for manager’ || manager.firstname from empply worker,empply manager where worker.managerid=manager.empid;

department.departmentid,department.department.departmentname,location.locationid,location.city from department inner join location ON department.locationid=location.locationid;

9)select e.lastname,d.departmentid,d.departmentname from empply E FULL OUTER JOIN department d ON e.deptid=d.departmentid

10)select e.empid.l.city,d.departmentname from empply e INNER JOIN department d ON e.deptid=d.departmentid INNER JOIN location l ON d.locationid=l.locationid;

Related Topics

We can write a custom essay

According to Your Specific Requirements

Order an essay
Materials Daily
100,000+ Subjects
2000+ Topics
Free Plagiarism
All Materials
are Cataloged Well

Sorry, but copying text is forbidden on this website. If you need this or any other sample, we can send it to you via email.

By clicking "SEND", you agree to our terms of service and privacy policy. We'll occasionally send you account related and promo emails.
Sorry, but only registered users have full access

How about getting this access

Your Answer Is Very Helpful For Us
Thank You A Lot!


Emma Taylor


Hi there!
Would you like to get such a paper?
How about getting a customized one?

Can't find What you were Looking for?

Get access to our huge, continuously updated knowledge base

The next update will be in:
14 : 59 : 59