Cartesian Product

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:
->Equijoin
->Non-equijoin
->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;

8)select
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;