Questions and Answers :
What is a CO-RELATED SUBQUERY ?
A CO-RELATED SUBQUERY is one that has a correlation name as table or view designator in
the FROM clause of the outer query and the same correlation name as a qualifier of a search
condition in the WHERE clause of the subquery.
eg SELECT field1 from table1 X WHERE field2>(select avg(field2) from table1 Y
where field1=X.field1);
(The subquery in a correlated subquery is revaluated
for every row of the table or view named in the outer query.)
What are various joins used while writing SUBQUERIES ?
Self join-Its a join foreign key of a table references the same table.
Outer Join--Its a join condition used where One can query all the rows of one of the
tables in the join condition even though they don't satisfy the join condition.
Equi-join--Its a join condition that retrieves rows from one or more tables in which one
or more columns in one table are equal to one or more columns in the second table.
What are various constraints used in SQL ?
NULL, NOT NULL, CHECK, DEFAULT
What are different Oracle database objects ?
TABLES, VIEWS, INDEXES, SYNONYMS, SEQUENCES, TABLESPACES etc
What is difference between Rename and Alias ?
Rename is a permanent name given to a table or column whereas Alias is a temporary
name given to a table or column which do not exist once the SQL statement is executed.
What is a view ?
A view is stored procedure based on one or more tables, it's a virtual table.
What are various privileges that a user can grant to another user ?
SELECT, CONNECT, RESOURCES
What is difference between UNIQUE and PRIMARY KEY constraints ?
A table can have only one PRIMARY KEY whereas there can be any number of UNIQUE
keys. The columns that compose PK are automatically define NOT NULL, whereas a column that
compose a UNIQUE is not automatically defined to be mandatory must also specify the column is
NOT NULL.
Can a primary key contain more than one columns ?
Yes
How you will avoid duplicating records in a query ?
By using DISTINCT
What is difference between SQL and SQL*PLUS ?
SQL*PLUS is a command line tool where as SQL and PL/SQL language interface and
reporting tool. Its a command line tool that allows user to type SQL commands to be executed
directly against an Oracle database. SQL is a language used to query the relational
database(DML,DCL,DDL). SQL*PLUS commands are used to format query result, Set options,
Edit SQL commands and PL/SQL.
Which datatype is used for storing graphics and images ?
LONG RAW
data type is used for storing BLOB's (binary large objects).
How will you delete duplicating rows from a base table ?
DELETE FROM
table_name A WHERE rowid>(SELECT min(rowid) from table_name B where
B.table_no=A.table_no);
CREATE TABLE
new_table AS SELECT DISTINCT * FROM old_table;
DROP
old_table
RENAME
new_table TO old_table
DELETE FROM table_name A
WHERE rowid NOT IN (SELECT MAX(ROWID) FROM table_name
GROUP BY column_name)
What is difference between SUBSTR and INSTR ?
SUBSTR returns a specified portion of a string eg SUBSTR('BCDEF',4) output BCDE
INSTR provides character position in which a pattern is found in a string.
eg INSTR('ABC-DC-F','-',2) output 7 (2
nd
occurence of '-')
There is a string '120000 12 0 .125' ,how you will find the position of the decimal place ?
INSTR('120000 12 0 .125',1,'.')
output 13
There is a '%' sign in one field of a column. What will be the query to find it ?
'\' Should be used before '%'.
When you use WHERE clause and when you use HAVING clause ?
HAVING
clause is used when you want to specify a condition for a group function and it is
written after GROUP BY clause
The WHERE clause is used when you want to specify a condition for columns, single row
functions except group functions and it is written before GROUP BY clause if it is used.
Tuesday, August 7, 2007
Monday, June 11, 2007
Oracle 7
What is Pseudo Colomn ?
USER, UID, SYSDATE, ROWNUM, ROWID, NULL, AND LEVEL
How to Display output in PL/SQL ?
we first
set serveroutput on
at SQL Prompt. and then in sql programming we specify as
dbms_output.put_line(`hdjhd' || `sakdha');
No Displays are allowed in PL/SQL programming with select statement
Write about Commands and Keywords ?
Alter : ALTER TABLE EMP ADD .......... , MODIFY .......... ;
for a in 1..10 loop ........... end loop;
loop exit when a<1 .................. end loop;
while ..... loop.................end loop;
Data Types : number, integer, varchar2, char, date, Boolean;
goto procedure name;..............<>..............
exit, rollback, commit, savepoint.
References: This keyword is used to refer two similar fields of different tables.
What are different built in functions ?
CONCATENATE, INITCAP, LENGTH, LOWER, UPPER, LPAD, RPAD, LTRIM
(removes the character specified left side of word), RTRIM, SUBSTR (displays values from
specified location onwards), TRANSLATE (for single character), REPLACE (for more than one
character ),CHR ( prints character of given ASCII value), ASCII (value of given character ).
ABS, CEIL, FLOOR, SQRT, POWER, SIGN, TRUNC, ROUND, EXP, MOD, LOG, LN,
GREATEST, LEAST, SYSDATE, ADD_MONTHS, MONTHS_BETWEEN.
what are emp.sal%type and emp%rowtype ?
First one is to assign a variable of type sal only.
Second is to assign a complete row of type emp table.
What are constraints ?
Constraint Clause : This will constrain a single column or a group of columns in a table.
and are used for maintaining integrity of the database.
Different Constraints are : candidate keys, primary keys, foreign keys, check conditions.
Can specify as a part of column definition, or at the end of the table (if more than one column).
Check Constraint : for validations to fields.
Naming Constraint (table constraints): Can name the constraint else system will name it
by default.
Different types of joins ?
Equi join : where emp.deptno = dept.deptno;
Non Equi join : where e.sal >= g.losal;
Outer join :***********e.deptno(+)=d.deptno;
Different types of Keys ?
Foreign Key : This is a referential integrity constraint and specifies the values of primary
key in other table. and is mentioned as :
DEPTNO number(2) REFERENCES DEPT(DEPTNO);
The clause ON DELETE CASCADE added to REFERENCES clause tells Oracle to delete the
dependent rows when we delete the row in the parent table.
Unique Key : If declared, then does not accept duplicate and also NULL values. And can
have many Unique key fields.
Primary Key : Similar to Unique key but, also maintains an index on primary key and also
used to connect two tables.
Write about cursors ?
Implicit (SQL is implicit by default ) and Explicit cursors.
EXIT WHEN C1%NOTFOUND, IF C1%FOUND THEN, IF C1%ISOPEN THEN,
FOR LOOP in CURSOR will :
1) open the cursor.
2) fetches the records from cursor one by one.
3) keeps the track of number of records in the cursor.
Super Dynamism in Cursors is obtained by passing the values as parameters( ie pass by value)
ie., for x in c1(a,b)
Multiple Cursors : These are nothing but nested cursors...........
In Implicit Cursors there is no need of declaration of cursors, by default system will create
a cursor whenever a query is written...and the conditions are like if SQL%NOTFOUND
if SQL%ROWCOUNT > 2 will keep a count of the number of records updated.
SQL is a keyword and cannot be used to name as a cursor name explicitly.
Difference between group functions and single row functions ?
Group Function
Single Row Function
A group function operates
A single row function
on many rows returns one and
result for one row.
returns single result.
Not allowed in Pl/sql procedural Allowed in Pl/Sql Procedural statements
eg SUM(),AVG,MIN,MAX etc
eg UPPER,LOWER,CHR...
Difference between DECODE and TRANSLATE ?
DECODE is value by value
TRANSLATE is character by
character replacement.
replacement.
Ex SELECT DECODE('ABC','A',1,'B',2,'ABC',3) eg SELECT
from dual; o/p 3
TRANSLATE('ABCGH',
'ABCDEFGHIJ', 1234567899)
FROM DUAL; o/p 12378
(DECODE command is used to bring IF,THEN,ELSE logic to SQL.It tests for the IF values(s) and
then aplies THEN value(s) when true, the ELSE value(s) if not.)
Difference between TRUNCATE and DELETE ?
TRUNCATE deletes much faster than DELETE
Truncate
Delete
It is a DDL statement
It is a DML statement
It is a one way trip,cannot
One can Rollback
ROLLBACK
Doesn't have selective features (where clause)
Has
Doesn't fire database triggers
Does
It requires disabling of referential
Does not require
constraints.
USER, UID, SYSDATE, ROWNUM, ROWID, NULL, AND LEVEL
How to Display output in PL/SQL ?
we first
set serveroutput on
at SQL Prompt. and then in sql programming we specify as
dbms_output.put_line(`hdjhd' || `sakdha');
No Displays are allowed in PL/SQL programming with select statement
Write about Commands and Keywords ?
Alter : ALTER TABLE EMP ADD .......... , MODIFY .......... ;
for a in 1..10 loop ........... end loop;
loop exit when a<1 .................. end loop;
while ..... loop.................end loop;
Data Types : number, integer, varchar2, char, date, Boolean;
goto procedure name;..............<
exit, rollback, commit, savepoint.
References: This keyword is used to refer two similar fields of different tables.
What are different built in functions ?
CONCATENATE, INITCAP, LENGTH, LOWER, UPPER, LPAD, RPAD, LTRIM
(removes the character specified left side of word), RTRIM, SUBSTR (displays values from
specified location onwards), TRANSLATE (for single character), REPLACE (for more than one
character ),CHR ( prints character of given ASCII value), ASCII (value of given character ).
ABS, CEIL, FLOOR, SQRT, POWER, SIGN, TRUNC, ROUND, EXP, MOD, LOG, LN,
GREATEST, LEAST, SYSDATE, ADD_MONTHS, MONTHS_BETWEEN.
what are emp.sal%type and emp%rowtype ?
First one is to assign a variable of type sal only.
Second is to assign a complete row of type emp table.
What are constraints ?
Constraint Clause : This will constrain a single column or a group of columns in a table.
and are used for maintaining integrity of the database.
Different Constraints are : candidate keys, primary keys, foreign keys, check conditions.
Can specify as a part of column definition, or at the end of the table (if more than one column).
Check Constraint : for validations to fields.
Naming Constraint (table constraints): Can name the constraint else system will name it
by default.
Different types of joins ?
Equi join : where emp.deptno = dept.deptno;
Non Equi join : where e.sal >= g.losal;
Outer join :***********e.deptno(+)=d.deptno;
Different types of Keys ?
Foreign Key : This is a referential integrity constraint and specifies the values of primary
key in other table. and is mentioned as :
DEPTNO number(2) REFERENCES DEPT(DEPTNO);
The clause ON DELETE CASCADE added to REFERENCES clause tells Oracle to delete the
dependent rows when we delete the row in the parent table.
Unique Key : If declared, then does not accept duplicate and also NULL values. And can
have many Unique key fields.
Primary Key : Similar to Unique key but, also maintains an index on primary key and also
used to connect two tables.
Write about cursors ?
Implicit (SQL is implicit by default ) and Explicit cursors.
EXIT WHEN C1%NOTFOUND, IF C1%FOUND THEN, IF C1%ISOPEN THEN,
FOR LOOP in CURSOR will :
1) open the cursor.
2) fetches the records from cursor one by one.
3) keeps the track of number of records in the cursor.
Super Dynamism in Cursors is obtained by passing the values as parameters( ie pass by value)
ie., for x in c1(a,b)
Multiple Cursors : These are nothing but nested cursors...........
In Implicit Cursors there is no need of declaration of cursors, by default system will create
a cursor whenever a query is written...and the conditions are like if SQL%NOTFOUND
if SQL%ROWCOUNT > 2 will keep a count of the number of records updated.
SQL is a keyword and cannot be used to name as a cursor name explicitly.
Difference between group functions and single row functions ?
Group Function
Single Row Function
A group function operates
A single row function
on many rows returns one and
result for one row.
returns single result.
Not allowed in Pl/sql procedural Allowed in Pl/Sql Procedural statements
eg SUM(),AVG,MIN,MAX etc
eg UPPER,LOWER,CHR...
Difference between DECODE and TRANSLATE ?
DECODE is value by value
TRANSLATE is character by
character replacement.
replacement.
Ex SELECT DECODE('ABC','A',1,'B',2,'ABC',3) eg SELECT
from dual; o/p 3
TRANSLATE('ABCGH',
'ABCDEFGHIJ', 1234567899)
FROM DUAL; o/p 12378
(DECODE command is used to bring IF,THEN,ELSE logic to SQL.It tests for the IF values(s) and
then aplies THEN value(s) when true, the ELSE value(s) if not.)
Difference between TRUNCATE and DELETE ?
TRUNCATE deletes much faster than DELETE
Truncate
Delete
It is a DDL statement
It is a DML statement
It is a one way trip,cannot
One can Rollback
ROLLBACK
Doesn't have selective features (where clause)
Has
Doesn't fire database triggers
Does
It requires disabling of referential
Does not require
constraints.
Subscribe to:
Posts (Atom)
