Oracle One more important Questions and answers.

Difference between varchar2 and char

varchar2 – variable length character
char – fixed length character


How many rules did oracle satisfies

11 rules of Codd



Difference between dbms/rdbms/semirdbms

RDBMS – any package that satisfies 7 & above
Semi RDBMS (MS-ACCESS) – exaclty 7
DBMS – <7


Why is Oracle widely used than sqlserver ?

Oracle is platform independent. But sqlserver works only on windows and it won't support for clustering concept.



Difference between sorting and indexing

Indexing greatly affects the performance of the query. (i.e) each time we issue DML commands it is rearranged internally insert/delete/update... Hence performance will be affected. Hence generally it is advisable to index columns which are frequently used in where clause of select statement

Sorting is not a physical rearrangement where as indexing is physical rearrangement


Oracle9i new features

Timestamp – datatype
spfile – is introduced in 9i
data dictionary is maintained manually


Is it possible to create a table using procedures (PL/SQL)

Yes
CREATE OR REPLACE PACKAGE EX_CREATE_TABLE AUTHID CURRENT_USER AS
PROCEDURE P1 (N1 IN NUMBER);
END EX_CREATE_TABLE;
/


CREATE OR REPLACE PACKAGE BODY EX_CREATE_TABLE IS
PROCEDURE P1 (N1 NUMBER) IS
CUR INTEGER := 0;
CMD VARCHAR2(256);
RC INTEGER;
BEGIN
CUR := DBMS_SQL.OPEN_CURSOR;
CMD := 'CREATE TABLE (T1 NUMBER)';
DBMS_SQL.PARSE (CUR, CMD, DBMS_SQL.NATIVE);
RC := DBMS_SQL.EXECUTE (CUR);
DBMS_SQL.CLOSE_CURSOR (CUR);
EXCEPTION
WHEN OTHERS THEN
IF CUR <> 0 THEN
DBMS_SQL.CLOSE_CURSOR (CUR);
ENDIF;
END P1;
IS IT POSSIBLE TO CREATE PROCEDURES WITH SAME NAME
YES, WE CAN CREATE WITH SAME NAME WITH DIFFERENT PARAMETERS.
EG:-
PROCEDURE T1 (V1 IN VARCHAR2);
PROCEDURE T1;
PROCEDURE T1 (V1 IN NUMBER);


Is it possible to call a function and procedure in a query?

Yes, we can call the function

Eg:-
function name  func()
SQL > select func () from dual;

To find the first 3 max salary

SELECT ENAME, DEPTNO, SAL, ROWNUM FROM (SELECT ROWNUM R, ENAME, DEPTNO, SAL FROM EMP ORDER BY SAL DESC) WHERE ROWNUM <= 3

For break in records

SELECT ENAME, DEPTNO, SAL, ROWNUM FROM (SELECT ROWNUM R, ENAME, DEPTNO, SAL FROM EMP) WHERE MOD(R,9) = 0



To find nth max salary

SELECT SAL FROM EMP A WHERE &N = (SELECT COUNT (DISTINCT (SAL)) FROM EMP B WHERE B.SAL >= A.SAL)


To get the output in words

SELECT TO_CHAR (TO_DATE('1994','YYYY'),'YYYYSP') FROM DUAL;

Output  One thousand nine hundred ninety four


Delete the duplicate row in a table


DELETE FROM EMP A WHERE ROWID > (SELECT MIN (ROWID) FROM EMP B WHERE B.EMPNO = A.EMPNO)

SELECT TRUNC (SYSDATE,'MONTH')-1 FROM DUAL;

It will return previous month last date.


What is ROWID ?

Each and every row in Oracle DB will have ROWID, which is used for unique identification of rows in the table. Which is the fastest way to access the date.


What is correlated query?

Usually in the case of subquery the output of the outquery depends upon the output of the inquery.

But in the correlated query the output of the inner query depends on the outer query. Outer query is being executed for each row.


SELECT * FROM EMP WHERE ENAME IS NULL;
and
SELECT * FROM EMP WHERE ENAME = NULL;

Will the 2 statements return the same no. of rows ?

No. It will not return the same no. of rows, because NULL values cannot be compared.


Which of the following is the valid Trigger ?

1. AFTER 2. INSERT 3. UPDATE 4. DELETE

AFTER

In which part of PL/SQL you will use RAISE statement ?

1. DECLARE 2. BEGIN 3. EXECUTABLE PART 4. EXCEPTIONAL

Executable and Exceptional part


Which of the following can be used for all datatypes ?

1. To_char 2. min 3. max 4. CEIL

min and max


SELECT NVL(NULL,'NOTNULL') FROM DUAL;

NOTNULL


If I declare a variable 'a' as number datatype and not assigning any value to it then variable will be initialized to

1. 0 2. Null 3. garbage value 4. empty

NULL


What is a transaction in Oracle ?

A transaction is a logical unit of work that compromises one or more SQL statements executed by a single user. According to ANSI, a transaction begins with first executable statement and ends when it is explicitly committed or rolled back.










ORACLE KEYWORDS

TRANSACTION

A transaction is a sequence of SQL statements that Oracle treats as a single unit of work.

COMMITTING

A transaction is said to be committed when the transaction makes permanent changes resulting from the SQL statements.

ROLL BACK

A transaction that retracts any of the changes resulting from SQL statements in transaction.

SAVEPOINT

For long transactions that contain many SQL statements, intermediate markers or savepoints are declared. Savepoints can be used to divide a transaction in a smaller points.

ROLLING FORWARD

Process of applying redo log during recovery is called rolling forward.

CURSOR

A cursor is a handle (name or a pointer) for the memory associated with a specific statement. A cursor is basically an area allocated by oracle for executing the SQL statement.

Implicit cursor for single row query and Explicit cursor for multi row query.


SYSTEM GLOBAL AREA (SGA)
PROGRAM GLOBAL AREA (PGA)
DATABASE BUFFER CACHE
REDO LOG BUFFER
REDO LOG FILES
PROCESS


What are procedure, functions and packages ?

Procedures and functions consist of set of PL/SQL statements that are grouped together as a unit to solve a specific problem or perform set of related tasks.
Procedures do not return values whereas functions return only one value.
Packages provide a method of encapsulating and storing related procedures, functions, variables and other package contents.

What is database triggers ?

Database triggers are procedures that are automatically executed as a result of insert in, update to, or delete from table
:NEW and :OLD functionalities in the INSERT, DELETE and UPDATE in a table.

Create or replace trigger ARVI_DUMMY1 AFTER INSERT or DELETE or UPDATE ON ARVI_DUMMY_OUTPUT FOR EACH ROW
DECLARE
Insert into arvi_dummy_output1 values
(:new.t1, :new.t2, :new.t3);
Insert into arvi_dummy_output1 values
(:old.t1, :old.t2, :old.t3);
End;

ARVI_DUMMY_OUTPUT ARVI_DUMMY_OUTPUT1
T1 T2 T3

DELETE T1 T2 T3
:new

:old
2 2 2 - - -
3 3 4 2 2 2
4 3 2 - - -
3 3 2
- - -
4 3 2
ARVI_DUMMY_OUTPUT
T1 T2 T3

INSERT
88 88 88
ARVI_DUMMY_OUTPUT1

T1 T2 T3

:new
:old
88 88 88
- - -
ARVI_DUMMY_OUTPUT
T1 T2 T3

UPDATE
88
77 88
88

ARVI_DUMMY_OUTPUT1

T1 T2 T3

:new

:old
77 88 88
88 88 88
What are the various master and detail relationships
ISOLATED:- The master can be deleted when the child is existing
NON-ISOLATED:- The master cannot be deleted when the child is existing
CASCADING:- The child gets deleted when the master is deleted


SEQUENCES

CREATE SEQUENCE
[Increment by n]
[Start with n]
[{maxvalue n | nomaxvalue}]
[{minvalue n | nominvalue}]
[{cycle | nocycle}]
[{cache n | nocache}];

Eg:- create sequence dept_deptid_seq
increment by 10
startwith 120
maxvalue 9999
nocache
nocycle;

ALTER SEQUENCE
[Increment by n]
[{maxvalue n | nomaxvalue}]
[{minvalue n | nominvalue}]
[{cycle | nocycle}]
[{cache n | nocache}];

Eg:- alter sequence dept_deptid_seq
increment by 20
maxvalue 99999
nocache
nocycle;

DROP SEQUENCE ;
select sequence_name, min_value, max_value, increment_by, last_number from USER_SEQUENCES

INDEX

An Oracle server index is a schema object that can speed up the retrieval of rows by using a pointer. Indexes can be created explicitly or automatically. When the drop a table, the corresponding indexes are also dropped.

Automatically: A unique index is created automatically when you define a PRIMARY KEY or UNIQUE constraint in a table definition.

Manually: Users can create nonunique indexes on columns to speed up access to the rows.

CREATE INDEX ON (column [, column]...)

DROP INDEX ;

Eg:- To improve the speed of query access to the LAST_NAME column in the EMPLOYEES table.
CREATE INDEX emp_last_name_idx ON EMPLOYEES (last_name);

select ic.index_name, ic.column_name, ic.column_position, ix.uniqueness FROM USER_INDEXES ix, USER_IND_COLUMNS ic WHERE ic.index_name = ix.index_name AND ic.table_name = 'EMPLOYEES';
Function-Based Indexes
• A function-based index is and index based on expressions
• The index expression is built from table_columns, constants, SQL funtions, and user-defined funtions.
Eg:- CREATE INDEX upper_dept_name_idx ON departments (UPPER (department_name));

SYNONYMS
CREATE [PUBLIC] SYNONYM FOR object;
Eg:- To create a synonym for the DEPT_SUM_VU view
CREATE SYNONYM d_sum FOR dept_sum_vu;

DROP SYNONYM d_sum;
-------------------------------------------------------------------------------------------------------------
CREATING USERS
CREATE USER IDENTIFIED BY ;
Eg:- CREATE USER scott IDENTIFIED BY tiger;

CHANGING PASSWORD
ALTER USER scott IDENTIFIED BY lion;

SYSTEM PRIVILEGES
1. CREATE SESSION
2. CREATE TABLE
3. CREATE SEQUENCE
4. CREATE VIEW
5. CREATE PROCEDURE

USER SYSTEM PRIVILEGES
GRANT PRIVILEGE [, PRIVILEGE...] TO user [, user/role, PUBLIC...];
Eg:- GRANT create session, create table, create sequence, create view TO scott;

CREATING AND GRANTING PRIVILEGES TO A ROLE
• Create a role => CREATE ROLE manager;
• Grant Privileges to a role => GRANT create table, create view TO manager;
• Grant a role to users => GRANT manager TO DEHAAN, KOCHIHAR;

OBJECT PRIVILEGES
GRANT object_priv [(columns)]
ON object
TO {user/role/PUBLIC}
[WITH GRANT OPTION];
• To grant query privileges on the employees table:-
GRANT select
ON employees
TO sue, rich;
• To grant privileges to update specific columns to users and roles:-
GRANT UPDATE (department_name, location_id)
ON departments
TO scott, manager;
• To give a user authority to pass along privileges
GRANT select, insert ON departments TO scott WITH GRANT OPTION;
• To allow all users on the system to query data from Alice's departments table
GRANT select ON alice.departments TO PUBLIC;

TO REVOKE OBJECT PRIVILEGES
REVOKE {privilege [, privilege...]/ALL}
ON object
FROM {user [, user...]/role/PUBLIC}
[CASCADE CONSTRAINTS];
Eg:- REVOKE select, insert ON departments FROM scott;

DATABASE LINKS
CREATE PUBLIC DATABASE LINK hq.acme.com USING 'sales';

SELECT * FROM emp@hq.acme.com;

THE SET OPERATORS
UNION

All distinct rows selected by either query
SELECT employee_id, job_id from employees
UNION
SELECT employee_id, job_id from job_history;
UNION ALL

All rows selected by either query, including all duplicates.
SELECT employee_id, job_id, department_id FROM employees
UNION ALL
SELECT employee_id, job_id, department_id FROM job_history
ORDER BY employee_id;
INTERSECT

All distinct rows selected by both queries
SELECT employee_id, job_id FROM employees
INTERSECT
SELECT employee_id, job_id FROM job_history;
MINUS

All distinct rows that are selected by the first SELECT statement and not selected in the second SELECT statement
SELECT employee_id, job_id FROM employees
MINUS
SELECT employee_id, job_id FROM job_history;


select colum, group_function from table
[where condition]
[group by group_by_expression]
[having group_condition]
[order by column];

CREATE OR REPLACE TRIGGER BEFORE/AFTER INSERT/UPDATE/DELETE ON FOR EACHROW

CREATE OR REPLACE PROCEDURE (parameters) AUTHID current_user IS
Begin
.......
.......
.......
End;


CREATE OR REPLACE FUNCTION (parameters) RETURN IS
BEGIN
......
......
END;

select a.a1, a.a2, b.b1, b.b2 from a, b where a.a1(+) = b.b1;
CREATE OR REPLACE PACKAGE AUTHID current_user AS
......
......
END ;

CREATE OR REPLACE PACKAGE BODY AS
......
......
......
END ;