B Explain Set Operators With Example

b Explain set Operators with example
Set operators combine the results of two component queries into a single result. Queries
containing set operators are called as compound queries. Set operators in SQL are
represented with following special keywords as: Union, Union all, intersection & minus.
Consider data from two tables emp and employee as
Emp Employee
Ename Ename
a c
b e
) Union: The Union of two or more sets contains all elements, which are present in
either or both. Union works as or.
E.g. select ename from emp union select ename from employee;
The output considering above data is :
Output
Ename
2) Union all: The Union of 2 or more sets contains all elements, which are present in
both, including duplicates.
E.g. select ename from emp union all select ename from employee;
The output considering above data is:
Output
Ename
3) Intersection: The intersection of two sets includes elements which are
present in both. E.g. select ename from emp intersect select ename from
employee;
Output
Ename
4) Minus: The minus of two sets includes elements from set1 minus elements of set2.
E.g. select ename from emp minus select ename from employee;
ename
d Describe exception handling in brief.
Ans Exception Handling: Exception is nothing but an error. Exception can be raise when
DBMS encounters errors or it can be raised explicitly.
When the system throws a warning or has an error it can lead to an exception. Such
exception needs to be handled and can be defined internally or user defined.
Exception handling is nothing but a code block in memory that will attempt to resolve
current error condition.
Syntax:
DECLARE ;
Declaration section
…executable statement;
EXCEPTION
WHEN ex_name1 THEN ;
Error handling statements/user defined action to be carried out;
END;
Types of Exception:
1) Predefined Exception/system defined exception/named exception: Are always
automatically raised whenever related error occurs. The most common errors that can
occur during the execution of PL/SQL. Not declared explicitly i.e. cursor already open,
invalid cursor, no data found, zero divide and too many rows etc. Programs are handled
by system defined Exceptions.
2) User defined exception: It must be declare by the user in the declaration part of the
block where the exception is used. It is raised explicitly in sequence of statements using:
Raise_application_error(Exception_Number, Error_Message);
a Describe commit and rollback with syntax and example.
Ans Commit:
The COMMIT command saves all transactions to the database since the last COMMIT
or ROLLBACK command
The syntax: SQL> COMMIT;
Or
COMMIT WORK;
Example :
SQL>Commit;
Rollback:
The ROLLBACK command is used to undo transactions that have not already been
saved to the database.
The ROLLBACK command can only be used to undo transactions since the last
COMMIT or ROLLBACK command was issued.
The syntax for ROLLBACK is:
ROLLBACK TO SAVEPOINT_NAME;
OR
ROLLBACK;
OR
ROLLBACK WORK;
Example:
SQL>ROLLBACK;
d Explain database security with its requirements in detail.
Ans Database security
Database security refers to the collective measures used to protect and secure a database
or database management software from illegal use and malicious threats and attacks.
Requirements of Database Security:
1. For prevention of data theft such as bank account numbers, credit card
information, passwords, work related documents or sheets, etc.
2. 2. To make data remain safe and confidential.

3. To provide confidentiality which ensures that only those individuals should ever
be able to view data they are not entitled to.
4. To provide integrity which ensures that only authorized individuals should ever
be able change or modify
5. To provide availability which ensure that the data or system itself is available for
use when authorized user wants it.
6. To provide authentication which deals with the desire to ensure that an
authorized individual.
7. To provide non-repudiation which deals with the ability to verify that message
has been sent and received by an authorized user.
e Explain Transaction ACID properties.
Ans ACID properties of transaction
1. Atomicity: When one transaction takes place, many operations occur under one
transaction. Atomicity means either all operations will take place property and reflect in
the database or none of them will be reflected.
2. Consistency: Consistency keeps the database consistent. Execution of a transaction
needs to take place in isolation. It helps in reducing complications of executing multiple
transactions at a time and preserves the consistency of the database.
3. Isolation: It is necessary to maintain isolation for the transactions. This means one
transaction should not be aware of another transaction getting executed. Also their
intermediate result should be kept hidden.
4. Durability: When a transaction gets completed successfully, it is important that the
changes made by the transaction should be preserved in database in spite of system
failures.
b Consider the following database
Employee(emp_id,emp_name,emp_city,emp_addr,emp_dept,join_date)
i) Display the emp_id of employee who live in city ‘Pune’ or ‘Nagpur’.
Ans i) Display the emp_id of employee who live in city ‘Pune’ or ‘Nagpur’
select emp_id
from Employee
where emp_city=’Pune’ or emp_city=’Nagpur’
ii) Change the employee name ‘Ayush’ to ‘Ayan’
update Employee
set emp_name=’Ayan’
where emp_name=’Ayush’
iii) Display the total number of employee whose dept is 50
Select count(*)
from Employee
where emp_dept=50;
c Consider the following schema Depositor (ACC_no, Name, PAN, Balance). Create a
view on Depositor having attributes(ACC_No,PAN) where balance is greater than
100000
Ans create view v1
as
select ACC_No,PAN
from Depositor
where balance > 100000;
a Create a sequence
i) Sequence name is Seq_1, Start with 1, increment by 1, minimum value 1,
maximum value 20.
ii) Use a seq_1 to insert the values into table Student( ID Number(10), Name char
(20));
iii) Change the Seq_1 max value 20 to 50.
iv) Drop the sequence.
Ans i) create sequence Seq_1 start with 1 increment by 1 minvalue 1 maxvalue
20;
ii) insert into student values(Seq_1.nextval,’ABC’);
iii) Alter sequence Seq_1 maxvalue 50;
iv) Drop sequence Seq_1;
b Write a PL/SQL program which accepts the customer_ID from the user. If the
enters an invalid ID then the exception invalid_id is raised using exception
handling.
Ans DECLARE
c_id numeric(10);
invalid_id_Exception Exception;
BEGIN
c_id:=&c_id;
if(c_id<0) then
raise invalid_id_Exception;
end if;
EXCEPTION
WHEN invalid_id_Exception THEN
dbms_output.put_line('Invalid customer id');
END;

B Explain Set Operators With Example

Uploaded by

Copyright:

Available Formats

B Explain Set Operators With Example

Uploaded by

Document Information

Original Title

Copyright

Available Formats

Share this document

Share or Embed Document

Sharing Options

Did you find this document useful?

Is this content inappropriate?

Copyright:

Available Formats

B Explain Set Operators With Example

Uploaded by

Copyright:

Available Formats

b Explain set Operators with example

Consider data from two tables emp and employee as

either or both. Union works as or.

The output considering above data is :

both, including duplicates.

The output considering above data is:

3) Intersection: The intersection of two sets includes elements which are

The output considering above data is:

The output considering above data is:

d Describe exception handling in brief.

DBMS encounters errors or it can be raised explicitly.

current error condition.

WHEN ex_name1 THEN ;

Error handling statements/user defined action to be carried out;

1) Predefined Exception/system defined exception/named exception: Are always

by system defined Exceptions.

a Describe commit and rollback with syntax and example.

The syntax: SQL> COMMIT;

saved to the database.

COMMIT or ROLLBACK command was issued.

The syntax for ROLLBACK is:

d Explain database security with its requirements in detail.

Ans Database security

Requirements of Database Security:

information, passwords, work related documents or sheets, etc.

2. 2. To make data remain safe and confidential.

be able to view data they are not entitled to.

be able change or modify

use when authorized user wants it.

6. To provide authentication which deals with the desire to ensure that an

has been sent and received by an authorized user.

e Explain Transaction ACID properties.

Ans ACID properties of transaction

the database or none of them will be reflected.

2. Consistency: Consistency keeps the database consistent. Execution of a transaction

needs to take place in isolation. It helps in reducing complications of executing multiple

transactions at a time and preserves the consistency of the database.

intermediate result should be kept hidden.

4. Durability: When a transaction gets completed successfully, it is important that the

changes made by the transaction should be preserved in database in spite of system

b Consider the following database

i) Display the emp_id of employee who live in city ‘Pune’ or ‘Nagpur’.

where emp_city=’Pune’ or emp_city=’Nagpur’

ii) Change the employee name ‘Ayush’ to ‘Ayan’

iii) Display the total number of employee whose dept is 50

view on Depositor having attributes(ACC_No,PAN) where balance is greater than

Ans create view v1

where balance > 100000;

maximum value 20.

iii) Change the Seq_1 max value 20 to 50.

iv) Drop the sequence.

Ans i) create sequence Seq_1 start with 1 increment by 1 minvalue 1 maxvalue

ii) insert into student values(Seq_1.nextval,’ABC’);

iii) Alter sequence Seq_1 maxvalue 50;

iv) Drop sequence Seq_1;

enters an invalid ID then the exception invalid_id is raised using exception

WHEN invalid_id_Exception THEN

dbms_output.put_line('Invalid customer id');

You might also like