Answer / vikneswarank

Java runtime exception handler
can not catch all type of exceptions. As such we need
to explicitly deal with them to prevent abrupt program
termination.Some of the examples for this could be
ArrayIndexOutOfBounds, ArithmeticExceptions etc. The
key factor to remember is that checked exceptions are
the ones where we need to use the try and catch blocks
to get the exception problem solved. The same is not
mandatory for the unchecked exceptions and are caught
well and good by the Java run time Exception Handler

Answer / vikneswarank

Constraint are used to restrict the value into the columns.
Type of constraints are as follows,

1. Not Null 2. Unique Key

3. Check 4. Primary Key

5. Foreign Key or Reference Key


NOT NULL
Not null constraint ensures that the column cannot accept
null values, which implies that every row in that column
should contain some value.
By default, column accepts null, if you don't specify any
constraint.

HERE WE COVERED THE FOLLOWS,

1. Constraint specification during the table creation
a. with the keyword CONSTRAINT followed by the
constraint name.
b. without specifying the keyword and constraint
name (directly specifying the constraint)

2. Constraint specification using Alter statement

Syntax:

1.a. CREATE TABLE table_name(column_name1 CONSTRAINT
constraint_name NOT NULL, column_name2.......)

1.b.CREATE TABLE table_name(column_name1 NOT NULL,
column_name2.......)

2. ALTER TABLE Employee MODIFY emp_name CONSTRAINT
ename_notnull NOT NULL

Note: In the above syntax column_name1 doesn't accept null,
but column_2 accepts null values.

Example:

1.a. CREATE TABLE Employee(emp_id number(10) CONSTRAINT
emp_notnull NOT NULL, emp_name varchar2(20))

1.b. CREATE TABLE Employee(emp_id number(10) NOT NULL,
emp_name varchar2(20));

2. ALTER TABLE Employee modify emp_name CONSTRAINT
ename_notnull NOT NULL
Note: If the column contains null values already then
the above statement cannot be executed. The above statement
is valid only if the column
doesnot contain any null values.

UNIQUE

Unique key ensures that the column cannot contain duplicate
values, which implies that no more than one rows can have
same value.
Unique column can contains null value.

HERE WE COVERED THE FOLLOWS,

1. Constraint specification during the table creation
a. with the keyword CONSTRAINT followed by the
constraint name.
b. with the keyword CONSTRAINT followed by the
constraint name at the end of query for each single column
c. with the keyword CONSTRAINT followed by the
constraint name at the end of query for more than one column
d. without specifying the keyword and constraint
name (directly specifying the constraint)

2. Constraint specification using Alter statement

Syntax:

1.a. CREATE TABLE table_name(column_name1 CONSTRAINT
constraint_name UNIQUE, column_name2.......)

1.b. CREATE TABLE table_name(column_name1 ,
column_name2....... , CONSTRAINT constraint_name
UNIQUE(column_name1))

1.c. CREATE TABLE table_name(column_name1 ,
column_name2....... , CONSTRAINT constraint_name
UNIQUE(column_name1, column_name1))

1.d.CREATE TABLE table_name(column_name1 UNIQUE,
column_name2.......)

2. ALTER TABLE table_name ADD CONSTRAINT constraint_name
UNIQUE(column_name1)

Example:

1.a.CREATE TABLE Employee(emp_id number(10) CONSTRAINT
empid_unique UNIQUE, emp_name varchar2(20));

1.b.CREATE TABLE Employee(emp_id number(10) , emp_name
varchar2(20), CONSTRAINT empid_unique UNIQUE(emp_id))

1.c.CREATE TABLE Employee(emp_id number(10) , emp_name
varchar2(20), CONSTRAINT empid_unique UNIQUE(emp_id, emp_name))

1.d.CREATE TABLE Employee(emp_id number(10) UNIQUE,
emp_name varchar2(20))

2. ALTER TABLE Employee ADD CONSTRAINT constraint_name
UNIQUE(emp_id )

CHECK

Check constraint ensures that all values in a column satisfy
the particular condition,
which implies that column doesn't accept value if condition
fails.

HERE WE COVERED THE FOLLOWS,

1. Constraint specification during the table creation
a. with the keyword CONSTRAINT followed by the
constraint name.
b. with the keyword CONSTRAINT followed by the
constraint name at the end of query for each single column
c. without specifying the keyword and constraint
name (directly specifying the constraint)

2. Constraint specification using Alter statement

Syntax:

1.a. CREATE TABLE table_name(column_name1 CONSTRAINT
constraint_name CHECK (column_name1 > 0), column_name2.......)

1.b. CREATE TABLE table_name(column_name1 ,
column_name2....... , CONSTRAINT constraint_name
CHECK(column_name1 > 0))

1.c. CREATE TABLE table_name(column_name1 ,
column_name2....... , CHECK(column_name1 > 0))

2. ALTER TABLE table_name ADD CONSTRAINT constraint_name
CHECK (column_name1 > 0)

Example:

1.a.CREATE TABLE Employee(emp_id number(10) CONSTRAINT
empid_check CHECK(emp_id > 0), emp_name varchar2(20));

1.b.CREATE TABLE Employee(emp_id number(10) , emp_name
varchar2(20), CONSTRAINT empid_check CHECK(emp_id > 0))

1.c.CREATE TABLE Employee(emp_id number(10) CHECK(emp_id >
0), emp_name varchar2(20))

2. ALTER TABLE Employee ADD CONSTRAINT constraint_name
CHECK (emp_id >0)

PRIMARY KEY

Primary key is combination of unique key and not null. It is
used to identify the unique row of the table.
Primary key can be created for single field or more than one
fields of the table.
If primary key is created for more than one fields, then its
called as COMPOSITE KEY.
Table can contain only one primary key.

HERE WE COVERED THE FOLLOWS,

1. Constraint specification during the table creation
a. with the keyword CONSTRAINT followed by the
constraint name.
b. with the keyword CONSTRAINT followed by the
constraint name at the end of query for each single column
c. with the keyword CONSTRAINT followed by the
constraint name at the end of query for more than one column
d. without specifying the keyword and constraint
name (directly specifying the constraint)

2. Constraint specification using Alter statement

Syntax:

1.a. CREATE TABLE table_name(column_name1 CONSTRAINT
constraint_name PRIMARY KEY, column_name2.......)

1.b. CREATE TABLE table_name(column_name1,
column_name2....... , CONSTRAINT constraint_name PRIMARY
KEY(column_name1))

1.c. CREATE TABLE table_name(column_name1,
column_name2....... , CONSTRAINT constraint_name PRIMARY
KEY(column_name1, column_name2))

1.d.CREATE TABLE table_name(column_name1 PRIMARY KEY,
column_name2.......)

2. ALTER TABLE table_name ADD CONSTRAINT constraint_name
PRIMARY KEY (column_name1)

Example:

1.a.CREATE TABLE Employee(emp_id number(10) CONSTRAINT
empid_primary PRIMARY KEY, emp_name varchar2(20));

1.b.CREATE TABLE Employee(emp_id number(10) , emp_name
varchar2(20), CONSTRAINT empid_primary PRIMARY KEY(emp_id))

1.c.CREATE TABLE Employee(emp_id number(10) , emp_name
varchar2(20), CONSTRAINT empid_primary PRIMARY KEY(emp_id,
emp_name))

1.d.CREATE TABLE Employee(emp_id number(10) PRIMARY KEY,
emp_name varchar2(20))

2. ALTER TABLE Employee ADD CONSTRAINT ename_primary PRIMARY
KEY (emp_id)

FOREIGN KEY

Foreign key is a field which points to the primary key of
another table. Also known as references key.
A table can contains more than one foreign key but it should
point to primary key of the other tables.

HERE WE COVERED THE FOLLOWS,

1. Constraint specification during the table creation
a. with the keyword CONSTRAINT followed by the
constraint name.
b. with the keyword CONSTRAINT followed by the
constraint name at the end of query for each single column
d. without specifying the keyword and constraint
name (directly specifying the constraint)

2. Constraint specification using Alter statement

Syntax:

1.a. CREATE TABLE table_name(column_name1 CONSTRAINT
constraint_name REFERENCES
parent_tablename(parenttable_column_name1), column_name2.......)

1.b. CREATE TABLE table_name(column_name1 ,
column_name2....... , CONSTRAINT constraint_name FOREIGN KEY
(column_name1) REFERENCES
parent_tablename(parenttable_column_name1))

1.c. CREATE TABLE table_name(column_name1 REFERENCES
parent_tablename(parenttable_column_name1), column_name2.......)

2. ALTER TABLE table_name ADD CONSTRAINT constraint_name
FOREIGN KEY (column_name1) REFERENCES
parent_tablename(parenttable_column_name1)

Example:

1.a.CREATE TABLE Employee(emp_id number(10) PRIMARY KEY,
emp_name varchar2(20), dept_id number(10), CONSTRAINT
deptid_foreign REFERENCES department(dept_id ));

1.b.CREATE TABLE Employee(emp_id number(10) PRIMARY KEY,
emp_name varchar2(20), dept_id number(10) , CONSTRAINT
deptid_foreign FOREIGN KEY (dept_id) REFERENCES
department(dept_id ))

1.c.CREATE TABLE Employee(emp_id number(10) PRIMARY KEY,
emp_name varchar2(20), dept_id number(10) REFERENCES
department(dept_id ))

2. ALTER TABLE Employee ADD CONSTRAINT deptid_foreign
FOREIGN KEY (dept_id) REFERENCES department(dept_id ))

Note: Before execute the abouve statement, department table
should be created as follows,
CREATE TABLE DEPARTMENT (dept_id number(10) PRIMARY KEY,
dept_name varchar2(20))





Set Operators will give the single results by combining the
results of the more than one queries. Number of columns and
data type of columns selected for both queries should be same.
Generally all the query will be executed separately and its
results will be merged.

1. UNION
2. UNION ALL

3. INTERSECT
4. MINUS


The following sql query should be executed to execute the
below given example.

CREATE TABLE DEPARTMENT (dept_id number(10) PRIMARY KEY,
dept_name varchar2(20))
CREATE TABLE Employee(emp_id number(10) PRIMARY KEY,
emp_name varchar2(20), salary number(10,2), design
varchar2(20), dept_id number(10)
REFERENCES department(dept_id), JOINED_DATE DATE)
INSERT into DEPARTMENT values(1,'Development')
INSERT INTO Employee VALUES(110, 'Testemployee1', 10000,
'SE', 1, '01-JAN-1901')
INSERT INTO Employee VALUES(111, 'Testemployee2', 20000,
'SSE', 1, '01-DEC-2001')


UNION
Union will combines the results of more than one queries and
then it will give the single results.
but it will ignore the duplicate rows.

Note: Union is just like OR operator, it will show the
results if row is selected from any one of the queries.

HERE WE COVERED THE FOLLOWS,

1. Union with same table
2. Union with different table.
3. Union with hardcoded column value.

Syntax:

1. SELECT columnname1 from tablename1 UNION SELECT
columnname2 FROM tablename1

2. SELECT columnname1 from tablename1 UNION SELECT
columnname2 FROM tablename2

3. SELECT columnname1 from tablename1 UNION SELECT
'hardcodedvalue' FROM tablename2

Example:

1. SELECT emp_id FROM Employee UNION SELECT dept_id FROM
Employee

2. SELECT emp_id FROM Employee UNION SELECT dept_id FROM
Department
SELECT emp_id, emp_name FROM Employee UNION SELECT
dept_id, dept_name FROM Department

3. SELECT emp_id, emp_name, design FROM Employee UNION
SELECT dept_id, dept_name, 'testname' FROM Department

UNION ALL
Unionall will combines the results of more than one queries
and then it will give as the single results.
but it will combines all the rows including the duplicate rows.

HERE WE COVERED THE FOLLOWS,

1. Union all with same table
2. Union all with different table.
3. Union all with hardcoded column value.

Syntax:

1. SELECT columnname1 from tablename1 UNION ALL SELECT
columnname2 FROM tablename1

2. SELECT columnname1 from tablename1 UNION ALL SELECT
columnname2 FROM tablename2

3. SELECT columnname1 from tablename1 UNION ALL SELECT
'hardcodedvalue' FROM tablename2

Example:

1. SELECT emp_id FROM Employee UNION ALL SELECT dept_id FROM
Employee UNION ALL SELECT dept_id FROM Employee


2. SELECT emp_id FROM Employee UNION ALL SELECT dept_id FROM
Department
SELECT emp_id, emp_name FROM Employee UNION ALL SELECT
dept_id, dept_name FROM Department

3. SELECT emp_name FROM Employee UNION ALL SELECT 'testname'
FROM Department
SELECT emp_id, emp_name, design FROM Employee UNION ALL
SELECT dept_id, dept_name, 'testname' FROM Department

INTERSECT
Intersect will combines the results of more than one queries
and then it will give as the single results.
but it will combines the matched rows only.

Note: Intersect is just like AND operator, it will show the
results of the matched rows in all the queries.

HERE WE COVERED THE FOLLOWS,

1. Intersect with same table
2. Intersect with different table.
3. Intersect with hardcoded column value.

Syntax:

1. SELECT columnname1 from tablename1 INTERSECT SELECT
columnname2 FROM tablename1

2. SELECT columnname1 from tablename1 INTERSECT SELECT
columnname2 FROM tablename2

3. SELECT columnname1 from tablename1 INTERSECT SELECT
'hardcodedvalue' FROM tablename2

Example:

1. SELECT dept_id FROM Employee INTERSECT SELECT dept_id
FROM Employee

2. SELECT dept_id FROM Employee INTERSECT SELECT dept_id
FROM Department

3. SELECT emp_name FROM Employee INTERSECT SELECT
'Testemployee1' FROM Department


MINUS
Intersect will compares the results of more than one queries
and then it will give as the single results by subtracting
right query result with left query result.
(ie., the second results with first results).


HERE WE COVERED THE FOLLOWS,

1. Minus with same table
2. Minus with different table.
3. Minus with hardcoded column value.

Syntax:

1. SELECT columnname1 from tablename1 MINUS SELECT
columnname2 FROM tablename1

2. SELECT columnname1 from tablename1 MINUS SELECT
columnname2 FROM tablename2

3. SELECT columnname1 from tablename1 MINUS SELECT
'hardcodedvalue' FROM tablename2

Example:

1. SELECT dept_id FROM Employee MINUS SELECT dept_id FROM
Employee

2. SELECT dept_id FROM Department MINUS SELECT dept_id FROM
Employee

3. SELECT emp_name FROM Employee MINUS SELECT
'Testemployee1' FROM Department





Join will give the single results by combining the results
of more than one queries. They are two types as follows

1. INNER JOIN
2. OUTER JOIN

In terms of condition type and tables, there are some joins
as follows

1. EQUI JOIN
2. NON EQUI JOIN

3. SELF JOIN


The following sql query should be executed to execute the
below given example.

CREATE TABLE Department(dept_id number(10) PRIMARY KEY,
dept_name varchar2(20))
INSERT into Department VALUES(1,'Development')
INSERT into Department VALUES(2,'Testing')
INSERT into Department VALUES(3,'Admin')

CREATE TABLE Branch (dept_id number(10), location varchar2(20))
INSERT into Branch VALUES (1, 'India')
INSERT into Branch VALUES (1, 'USA')
INSERT into Branch VALUES (1, 'Australia')
INSERT into Branch VALUES (2, 'India')
INSERT into Branch VALUES (2, 'UK')
INSERT into Branch VALUES (4, 'Japan')


INNER JOIN
Join combines the results of more than one queries and will
give single results but it combines the results based on the
given join condition for the tables.
It combines the rows by comparing each row of left table
with all the rows of right table, so that proper join
condition should be mentioned. we can give other conditions
also in the query to restrict the rows from the join results.

HERE WE COVERED THE FOLLOWS,

1. Inner Join using JOIN keyword
2. Inner Join without keyword

Syntax:

1. SELECT columnname1 from tablename1 [JOIN or INNER JOIN]
tablename2 ON tablename1.columnname1= tablename2.columnname2

2. SELECT columnname1 from tablename1, tablename2 WHERE
tablename1.columnname1= tablename2.columnname2


Example:

1.a. SELECT department.dept_id, dept_name, location FROM
Branch JOIN Department ON Department.dept_id = Branch.dept_id

Note: Since it compare each row of left table with all the
rows of right table, Results will be like how many rows has
equivalent dept_id with branch's dept_id in departments.

1.b. SELECT department.dept_id, dept_name, location FROM
Branch JOIN Department ON Department.dept_id > Branch.dept_id

Note: Results will be like how many rows has less dept_id
than branch's dept_id in departments.

1.c. SELECT department.dept_id, dept_name, location FROM
Branch JOIN Department ON Department.dept_id < Branch.dept_id

Note: Results will be like how many rows has greater dept_id
than branch's dept_id in departments.

1.d. SELECT department.dept_id, dept_name, location FROM
Branch JOIN Department ON Department.dept_id <> Branch.dept_id

Note: Results will be like how many rows has is not
equivalent dept_id with branch's dept_id in departments.

2.a. SELECT department.dept_id, dept_name, location FROM
Branch , Department where Department.dept_id = Branch.dept_id

2.b. SELECT department.dept_id, dept_name, location FROM
Branch , Department where Department.dept_id > Branch.dept_id

2.c. SELECT department.dept_id, dept_name, location FROM
Branch , Department where Department.dept_id < Branch.dept_id

2.d. SELECT department.dept_id, dept_name, location FROM
Branch , Department where Department.dept_id <> Branch.dept_id

OUTER JOIN
Join combines the results of more than one queries and will
give single results based on the given join condition for
the tables. And it will add the extra rows from one tables
for which no rows from other tables which statify the join
condition.
It combines the rows by comparing each row of left table
with all the rows of right table, so that proper join
condition should be mentioned. we can give other conditions
also in the query to restrict the rows from the join results.

Three type of outer join,
1. LEFT OUTER JOIN - it will give single results based on
the given join condition for the tables and it will add the
extra rows from right table for which no rows from left
table which statify the join condition.
2. RIGHT OUTER JOIN - it will give single results based on
the given join condition for the tables and it will add the
extra rows from left table for which no rows from right
table which statify the join condition.
3. FULL OUTER JOIN - it will give single results based on
the given join condition for the tables and it will add the
extra rows from both right & left table for which no rows
from both table which statify the join condition.


HERE WE COVERED THE FOLLOWS,

1. Outer Join using JOIN keyword
2. Outer Join without keyword


LEFT OUTER JOIN:

Syntax:

1. SELECT columnname1 from tablename1 [LEFT JOIN or LEFT
OUTER JOIN] tablename2 ON tablename1.columnname1=
tablename2.columnname2

2. SELECT columnname1 from tablename1, tablename2 WHERE
tablename1.columnname1(+)= tablename2.columnname2


Example:

1.a. SELECT department.dept_id, dept_name, location FROM
Branch LEFT JOIN Department ON Department.dept_id =
Branch.dept_id

Note: Since it compare each row of left table with all the
rows of right table, Results will be like how many rows has
equivalent dept_id with branch's dept_id in departments and
extra rows from right table.

1.b. SELECT department.dept_id, dept_name, location FROM
Branch LEFT OUTER JOIN Department ON Department.dept_id >
Branch.dept_id

Note: Results will be like how many rows has less dept_id
than branch's dept_id in departments and extra rows from
right table.

1.c. SELECT department.dept_id, dept_name, location FROM
Branch LEFT JOIN Department ON Department.dept_id <
Branch.dept_id

Note: Results will be like how many rows has greater dept_id
than branch's dept_id in departments and extra rows from
right table.

1.d. SELECT department.dept_id, dept_name, location FROM
Branch LEFT JOIN Department ON Department.dept_id <>
Branch.dept_id

Note: Results will be like how many rows has is not
equivalent dept_id with branch's dept_id in departments and
extra rows from right table.

2.a. SELECT department.dept_id, dept_name, location FROM
Branch , Department where Department.dept_id(+) =
Branch.dept_id

2.b. SELECT department.dept_id, dept_name, location FROM
Branch , Department where Department.dept_id(+) >
Branch.dept_id

2.c. SELECT department.dept_id, dept_name, location FROM
Branch , Department where Department.dept_id(+) <
Branch.dept_id

2.d. SELECT department.dept_id, dept_name, location FROM
Branch , Department where Department.dept_id(+) <>
Branch.dept_id


RIGHT OUTER JOIN:

Syntax:

1. SELECT columnname1 from tablename1 [RIGHT JOIN or RIGHT
OUTER JOIN] tablename2 ON tablename1.columnname1=
tablename2.columnname2

2. SELECT columnname1 from tablename1, tablename2 WHERE
tablename1.columnname1= tablename2.columnname2(+)


Example:

1.a. SELECT department.dept_id, dept_name, location FROM
Branch RIGHT JOIN Department ON Department.dept_id =
Branch.dept_id

Note: Since it compare each row of left table with all the
rows of right table, Results will be like how many rows has
equivalent dept_id with branch's dept_id in departments and
extra rows from left table.

1.b. SELECT department.dept_id, dept_name, location FROM
Branch RIGHT OUTER JOIN Department ON Department.dept_id >
Branch.dept_id

Note: Results will be like how many rows has less dept_id
than branch's dept_id in departments and extra rows from
left table.

1.c. SELECT department.dept_id, dept_name, location FROM
Branch RIGHT JOIN Department ON Department.dept_id <
Branch.dept_id

Note: Results will be like how many rows has greater dept_id
than branch's dept_id in departments and extra rows from
left table.

1.d. SELECT department.dept_id, dept_name, location FROM
Branch RIGHT JOIN Department ON Department.dept_id <>
Branch.dept_id

Note: Results will be like how many rows has is not
equivalent dept_id with branch's dept_id in departments and
extra rows from left table.

2.a. SELECT department.dept_id, dept_name, location FROM
Branch , Department where Department.dept_id =
Branch.dept_id (+)

2.b. SELECT department.dept_id, dept_name, location FROM
Branch , Department where Department.dept_id >
Branch.dept_id (+)

2.c. SELECT department.dept_id, dept_name, location FROM
Branch , Department where Department.dept_id <
Branch.dept_id (+)

2.d. SELECT department.dept_id, dept_name, location FROM
Branch , Department where Department.dept_id <>
Branch.dept_id (+)

FULL OUTER JOIN:

Syntax:

1. SELECT columnname1 from tablename1 [FULL JOIN or FULL
OUTER JOIN] tablename2 ON tablename1.columnname1=
tablename2.columnname2


Example:

1.a. SELECT department.dept_id, dept_name, location FROM
Branch FULL JOIN Department ON Department.dept_id =
Branch.dept_id

Note: Since it compare each row of left table with all the
rows of right table, Results will be like how many rows has
equivalent dept_id with branch's dept_id in departments and
extra rows from both left & right table.

1.b. SELECT department.dept_id, dept_name, location FROM
Branch FULL OUTER JOIN Department ON Department.dept_id >
Branch.dept_id

Note: Results will be like how many rows has less dept_id
than branch's dept_id in departments and extra rows from
both left & right table.

1.c. SELECT department.dept_id, dept_name, location FROM
Branch FULL JOIN Department ON Department.dept_id <
Branch.dept_id

Note: Results will be like how many rows has greater dept_id
than branch's dept_id in departments and extra rows from
both left & right table.

1.d. SELECT department.dept_id, dept_name, location FROM
Branch FULL JOIN Department ON Department.dept_id <>
Branch.dept_id

Note: Results will be like how many rows has is not
equivalent dept_id with branch's dept_id in departments and
extra rows from both left & right table.


EQUI JOIN
Join combines the results of more than one queries and will
give single results but it combines the results based on the
given join condition for the tables. If given condition has
equal operator (=), then its called equi join.

Syntax:

1. SELECT columnname1 from tablename1 [JOIN or LEFT JOIN or
RIGHT JOIN or FULL JOIN] tablename2 ON
tablename1.columnname1= tablename2.columnname2

2. SELECT columnname1 from tablename1, tablename2 WHERE
tablename1.columnname1= tablename2.columnname2

3. SELECT columnname1 from tablename1, tablename2 WHERE
tablename1.columnname1(+)= tablename2.columnname2


Example:

1. SELECT department.dept_id, dept_name, location FROM
Branch JOIN Department ON Department.dept_id = Branch.dept_id

2. SELECT department.dept_id, dept_name, location FROM
Branch , Department where Department.dept_id = Branch.dept_id

3. SELECT department.dept_id, dept_name, location FROM
Branch , Department where Department.dept_id(+) =
Branch.dept_id


NONEQUI JOIN
Join combines the results of more than one queries and will
give single results but it combines the results based on the
given join condition for the tables. If given condition has
non equal operator (<, >, <>), then its called nonequi join.

Syntax:

1. SELECT columnname1 from tablename1 [JOIN or LEFT JOIN or
RIGHT JOIN or FULL JOIN] tablename2 ON
tablename1.columnname1 > tablename2.columnname2

2. SELECT columnname1 from tablename1, tablename2 WHERE
tablename1.columnname1 < tablename2.columnname2

3. SELECT columnname1 from tablename1, tablename2 WHERE
tablename1.columnname1(+) <> tablename2.columnname2


Example:

1. SELECT department.dept_id, dept_name, location FROM
Branch JOIN Department ON Department.dept_id > Branch.dept_id

2. SELECT department.dept_id, dept_name, location FROM
Branch , Department where Department.dept_id < Branch.dept_id

3. SELECT department.dept_id, dept_name, location FROM
Branch , Department where Department.dept_id(+) <>
Branch.dept_id


SELF JOIN
Join combines the results of more than one queries and will
give single results but it combines the results based on the
given join condition for the tables.
If join used to join rows for the same table itself then its
called self join.

Syntax:

1. SELECT columnname1 from tablename1, tablename2 WHERE
tablename1.columnname1= tablename1.columnname2


Example:

1. SELECT emp1.emp_id, emp1.emp_name, emp2.dept_id FROM
Employee emp1, Employee emp2 where emp1.dept_id = emp2.dept_id



View is a virtual table which does not have the data. It is
just a logical representation of the tables or views or
combination of the tables and views.
Any DML operations like insert, update and delete will
affect its base tables. Any DML operations in table will
reflect in the view automatically.
Insert, update and delete command on view will changes the
base table's data, but these commands are not valid if the
view has aggregate function, distinct, group by, having or
order by.

Uses of view:
a. Complex queries will be simplified
b. Security access for the columns or rows of the tables. we
can give access to the user specific for the particular
columns or rows of the tables using view.


The following sql query should be executed to execute the
below given example.

CREATE TABLE DEPARTMENT (dept_id number(10) PRIMARY KEY,
dept_name varchar2(20))
CREATE TABLE Employee(emp_id number(10) PRIMARY KEY,
emp_name varchar2(20), salary number(10,2), design
varchar2(20), dept_id number(10)
REFERENCES department(dept_id))
INSERT into DEPARTMENT values(1,'Development')
INSERT into DEPARTMENT values(2,'Testing')
INSERT INTO Employee VALUES(110, 'Testemployee1', 10000,
'SE', 1)
INSERT INTO Employee VALUES(111, 'Testemployee2', 20000,
'SSE', 1)

HERE WE COVERED THE FOLLOWS,

1. Create view for all the columns of the table
2. Create view for the particular columns of the table
3. Create view for the particular rows of the table
4. Create view for more than one tables
5. Create view for the other view
6. Replace the existing view
7. Alter the view
8. Drop the view

Syntax:

1. CREATE VIEW viewname AS SELECT * FROM tablename

2. CREATE VIEW viewname AS SELECT columnname1, columnanem2
FROM tablename

3. CREATE VIEW viewname AS SELECT * FORM tablename WHERE
columnname1 = value

4. CREATE VIEW viewname AS SELECT tablename1.columnname1,
tablename1.columnname2, tablenname2.columnname1 FROM
tablename1, tablename2 WHERE tablename1.columnname1 =
tablename2.columnname1

5. CREATE VIEW vuewname1 AS SELECT * FROM viewname2

6. CREATE OR REPLACE VIEW viewname1 AS SELECT * FROM tablename

7. ALTER VIEW viewname COMPILE

8. DROP VIEW viewname

Example:

1. CREATE VIEW viewdept1 AS SELECT * FROM Department

2. CREATE VIEW viewdept2 AS SELECT dept_name FROM Department

3. CREATE VIEW viewemp3 AS SELECT * FROM Employee WHERE
dept_id = 1

4. CREATE VIEW view4 AS select emp_id, emp_name,
department.dept_id, dept_name from employee, department
WHERE employee.dept_id = department.dept_id

5. CREATE VIEW newview AS SELECT * FROM viewdept1

6. CREATE OR REPLACE VIEW newview AS SELECT * FROM Department

Note: Replace is used to alter the existing view definition
and to keep already granted privileges.

7. ALTER VIEW newview COMPILE

Note: It will recompile the existing view. It is used to
ensure that any error in view or not after the base table
is altered.

8. DROP VIEW newview

Answer / sagar hire

In checked exception it is necessary to that handle it.and
in case of unchecked exception it is not necessary to handle
that exception.
a method in which possibility of the checked exception
must throws that exception otherwise it will genberate a
complie time error.

What is RRL?

1 Answers  

---

What state does a thread enter when it terminates its processing?

0 Answers  

---

What is RMI and what are the services in RMI?

0 Answers  

---

What are transaction attributes?

0 Answers  

---

diff between jsp include directive and jsp action include?

2 Answers   SolutionNET,

---

---

Write a program on RMI and JDBC using StoredProcedure?

1 Answers  

---

For which statements does it make sense to use a label?

0 Answers  

---

how to make a index.jsp for running the site in internet and find an error for connection with weblogic server and java that give an error invalid object name.and how to maintain session.

0 Answers  

---

What is table mutation and how do you avoid it?

0 Answers  

---

What is the immediate superclass of the applet class?

0 Answers  

---

how to create Calculator GUI using Gridbaglayout as like as Window Calculator??????????? (Open calculator Go-->Run-->type calc-->Enter)

1 Answers   TCS,

---

If i learn Java, what kind of applications can i create that will help Banking, Retail, Hotel, Logistics industry.

2 Answers  

---