Abhishek Mishra Dbms Assignment 2

1.
Perform Constraints on particular field of tables(unique , not null, in,

Default value)
Answer :
Not null:
Unique:
Default value:
2. Check constraint
Answer:
3. How to use on delete cascade at the time of creating table?
Answer :
4. Oracle functions;( nvl() , count(),avg(), sum())
Answer :
Nvl()
Count()
Avg()
Sum()
5 How to apply triggers and types of triggers?
Answer :
A trigger is a stored procedure in database which automatically invokes

whenever a special event in the database occurs.
For example, a trigger can be invoked when a row is inserted into a specified
table or when certain table columns are being updated.
How to apply triggers :-
Triggers supplement the standard capabilities of oracle to provide a highly

customized database management system.
For example, a trigger can restrict DML operation against a table to those
issued during regular business hours.
Types of SQL triggers :-
In SQL Server, There are 3 groups of triggers:
• DML (data manipulation language) triggers – We’ve already

mentioned them, and they react to DML commands. These are –
INSERT, UPDATE, and DELETE
• DDL (data definition language) triggers – As expected, triggers of
this type shall react to DDL commands like – CREATE, ALTER, and
DROP
• Logon triggers – The name says it all. This type reacts to LOGON
events
6: like (% , _) between and operator with example?
Answer:
10. Explain Pragma Exception.
Answer :
. The PRAGMA is a keyword used to signify that the remainder of

the pl/sql statement is a pragma, or directive, to the compiler. Tell the
pl/sql runtime engine to commit or roll back any changes made to the
database inside the current block without affecting the main or outer
transaction.
The pragma exception associates an exception name with an oracle

error number. You can intercept any ORA- error and write a specific
handler for it instead of using the others handler.
11 : Explain the package, grant and revoke
PACKAGE :-
A package is a kind of schema object that groups logically related

pl/sql type, variables, constants, subprograms, cursors, and
exceptions. A package is compiled and stored in the database, where
many application can share its contents.
GRANT : -
Grant command is specifically used to provide privileges to database

object for a user. This command also allows users to grant
permissions to other users too.
It assigns access rights to users.
REVOKE :-
Revoke command withdraw user privileges on database objects if any

granted. It does operations opposite to the grant command. When a
privilege is revoked from a particular user U, then privilege granted to
all other users by user U will be revoked.
12: Create the following tables:
Employee
(Emp_no: varchar2, Emp_name: varchar2, Emp_city varchar2)
Company
(Emp_no:Varchar2, Company_name: varchar2,Salary number)
1.Write the query to display all employees working in ‘XYZ’ company.

2.Write the query to display name of employees whose salary is largest.
3. Perform Alter and delete,truncate,drop,command.
4.Commit and roll back
Answer :
13. Explain The PL/ SQLand SQL *plus.
Answer :
SQL*Plus is essentially an interactive query tool with some scripting

capabilities. You can enter a SQL statement, such as a SELECT
query, and view the results. You can execute data definition
language statements to create tables and other objects. DBAs can
use SQL*Plus to start up, shut down, and otherwise administer a
database. You can even enter and execute PL/SQL code.
SQL*Plus is primarily a command-line application, but, despite its
lack of “flash,” it is a workhorse tool used daily by database
administrators, developers, and yes, even end users. As a DBA, it
is my tool of choice for managing the databases under my care. I
use it to peek under the hood—to explore the physical
implementation of my database, and to create and manage users,
tables, and tablespaces. In my role as a developer, SQL*Plus is the
first tool that I fire up when I need to develop a query. In spite of all
the fancy, GUI-based SQL generators contained in products such
as PowerBuilder, Clear Access, and Crystal Reports, I still find it
quicker and easier to build up and test a complex query in
SQL*Plus before transferring it to whatever development tool I am
using.

Abhishek Mishra Dbms Assignment 2

Uploaded by

Copyright:

Available Formats

Abhishek Mishra Dbms Assignment 2

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

Abhishek Mishra Dbms Assignment 2

Uploaded by

Copyright:

Available Formats

1.

Perform Constraints on particular field of tables(unique , not null, in,

A trigger is a stored procedure in database which automatically invokes

How to apply triggers :-

Triggers supplement the standard capabilities of oracle to provide a highly

Types of SQL triggers :-

In SQL Server, There are 3 groups of triggers:

• DML (data manipulation language) triggers – We’ve already

. The PRAGMA is a keyword used to signify that the remainder of

The pragma exception associates an exception name with an oracle

A package is a kind of schema object that groups logically related

Grant command is specifically used to provide privileges to database

Revoke command withdraw user privileges on database objects if any

1.Write the query to display all employees working in ‘XYZ’ company.

SQL*Plus is essentially an interactive query tool with some scripting

You might also like