51) What is
SET?
SET command
changes the system variables affecting the report environment.
52) What is
SPOOL?
SPOOL command
creates a print file of the report.
53) What is a
JOIN?
JOIN is the
form of SELECT command that combines info from two or more tables.
Types of Joins
are Simple (Equijoin & Non-Equijoin), Outer & Self join.
Equijoin
returns rows from two or more tables joined together based upon a equality
condition in the WHERE clause.
Non-Equijoin
returns rows from two or more tables based upon a relationship other than the
equality condition in the WHERE clause.
Outer Join
combines two or more tables returning those rows from one table that have no
direct match in the other table.
Self Join joins
a table to itself as though it were two separate tables.
54) What is
Union?
Union is the
product of two or more tables.
55) What is
Intersect?
Intersect is
the product of two tables listing only the matching rows.
56) What is
Minus?
Minus is the
product of two tables listing only the non-matching rows.
57)What is
Correlated Subquery?
Correlated
Subquery is a subquery that is evaluated once for each row processed by the
parent statement. Parent statement can be Select, Update or Delete. Use CRSQ to
answer multipart questions whose answer depends on the value in each row
processed by parent statement.
58) What is
Multiple columns?
Multiple
columns can be returned from a Nested Subquery.
59) What is
Sequences?
Sequences are
used for generating sequence numbers without any overhead of locking. Drawback
is that after generating a sequence number if the transaction is rolled back,
then that sequence number is lost.
Sequence is a
database object used to generate unique sequential integer values.
60) What is
Synonyms?
Synonyms is the
alias name for table, views, sequences & procedures and are created for
reasons of Security and Convenience.Two levels are
Public - created by DBA & accessible to all the users. Private - Accessible
to creator only. Advantages are referencing without specifying the owner and
Flexibility to customize a more meaningful naming convention.
61) What is
Data types?
Max. columns in
a table is 255. Max. Char size is 255, Long is 64K & Number is 38 digits.
Cannot Query on
a long column.
Char, Varchar2
Max. size is 2000 & default is 1 byte.
Number(p,s) p
is precision range 1 to 38, s is scale -84 to 127.
Long Character
data of variable length upto 2GB.
Date Range from
Jan 4712 BC to Dec 4712 AD.
Raw Stores
Binary data (Graphics Image & Digitized Sound). Max. is 255 bytes.
Mslabel Binary
format of an OS label. Used primarily with Trusted Oracle.
62) What is
Transaction?
Transaction is
defined as all changes made to the database between successive commits.
63) What is
Commit?
Commit is an
event that attempts to make data in the database identical to the data in the
form. It involves writing or posting data to the database and committing data
to the database. Forms check the validity of the data in fields and records
during a commit. Validity check are uniqueness, consistency and db
restrictions.
64) What is
Posting?
Posting is an
event that writes Inserts, Updates & Deletes in the forms to the database
but not committing these transactions to the database.
65) What is
Savepoint?
Savepoint is a
point within a particular transaction to which you may rollback without rolling
back the entire transaction.
66) What is Set
Transaction?
Set Transaction
is to establish properties for the current transaction.
67) What is
Locking?
Locking are
mechanisms intended to prevent destructive interaction between users accessing
data. Locks are used to achieve.
68) What is SQL
Deadlock?
Deadlock is a
unique situation in a multi user system that causes two or more users to wait
indefinitely for a locked resource. First user needs a resource locked by the
second user and the second user needs a resource locked by the first user. To
avoid dead locks, avoid using exclusive table lock and if using, use it in the
same sequence and use Commit frequently to release locks
.
69) Give the
structure of the procedure ?
PROCEDURE name
(parameter list.....)
is
local variable
declarations
BEGIN
Executable
statements.
Exception.
exception
handlers
end;
basically
procedure has three
parts
1.variable
declaretion(optional)
2.body(mandetory)
3.Exception(optional)
suppose ex
CREATE OR
REPLACEPROCEDURE emp_pro( p_id IN employees.employee_id%TYPE)
IS
v_name
employees.last_name%TYPE;
v_mail
employees.email%TYPE;
BEGIN
SELECT
last_name,email INTO v_name,v_mail FROM employees
WHERE
employee_id:=p_id;
DBMS_OUTPUT.PUT_LINE('NAME:'||v_name
||'MAILID:'||v_mail);
END;
70) What is
Mutating SQL Table?
Mutating Table
is a table that is currently being modified by an Insert, Update or Delete
statement. Constraining Table is a table that a triggering statement might need
to read either directly for a SQL statement or indirectly for a declarative
Referential Integrity constraints. Pseudo Columns behaves like a column in a
table but are not actually stored in the table. E.g. Currval, Nextval, Rowid,
Rownum, Level etc.
71) What is
SQL*Loader?
SQL*Loader is a
product for moving data in external files into tables in an Oracle database. To
load data from external files into an Oracle database, two types of input must
be provided to SQL*Loader : the data itself and the control file. The control
file describes the data to be loaded. It describes the Names and format of the
data files, Specifications for loading data and the Data to be loaded
(optional). Invoking the loader sqlload username/password controlfilename
<options>.
72) What are
the return values of functions SQLCODE and SQLERRM ?
SQLCODE returns
the latest code of the error that has occurred.
SQLERRM returns
the relevant error message of the SQLCODE.
SQLCODE returns
the latest code of the error that has occured.
SQLERRM returns
relevant massege
of the error
code
73) The most
important DDL statements in SQL are?
CREATE TABLE -
creates a new database table
ALTER TABLE -
alters (changes) a database table
DROP TABLE -
deletes a database table
CREATE INDEX -
creates an index (search key)
DROP INDEX -
deletes an index
74) Operators
used in SELECT statements are?
= Equal
<> or !=
Not equal
> Greater
than
< Less than
>= Greater
than or equal
<= Less than
or equal
BETWEEN Between
an inclusive range
LIKE Search for
a pattern
75) SELECT
statements in SQL?
SELECT
column_name(s) FROM table_name
SELECT DISTINCT
column_name(s) FROM table_name
SELECT column
FROM table WHERE column operator value
SELECT column
FROM table WHERE column LIKE pattern
SELECT
column,SUM(column) FROM table GROUP BY column
SELECT
column,SUM(column) FROM table GROUP BY column HAVING SUM(column) condition
value
Note that
single quotes around text values and numeric values should not be enclosed in
quotes. Double quotes may be acceptable in some databases.
76) What are
the PL/SQL Statements used in cursor processing ?
DECLARE CURSOR
cursor name, OPEN cursor name, FETCH cursor name INTO or Record types, CLOSE
cursor name.
77) The SELECT
INTO Statement is most often used to create backup copies of tables or for
archiving records?
SELECT
column_name(s) INTO newtable [IN externaldatabase] FROM source
SELECT
column_name(s) INTO newtable [IN externaldatabase] FROM source WHERE
column_name operator value.
78) The INSERT
INTO Statements in SQL?
INSERT INTO
table_name VALUES (value1, value2,....)
INSERT INTO
table_name (column1, column2,...) VALUES (value1, value2,....)
79) The Update
Statement in SQL.
UPDATE
table_name SET column_name = new_value WHERE column_name = some_value
80) What is the
Delete Statements in SQL?
Delete
statement in SQL is used to delete partial/all data.
Especially
delete statement is useful in case of partial delete depending upon our
criterias otherwise use TRUNCATE to delete whole data from table.
When delete
command fires then:
1) Triggers
will fire (If created on that table)
2) This will
not auto commit changes made So there is one chance to rollback.
3) If u delete
whole data then HWM (Highest Water Mark) will not change which gets changed in
case of Truncate.
81) How to sort
the rows in SQL?
SELECT column1,
column2, ... FROM table_name ORDER BY columnX, columnY, ..
SELECT column1,
column2, ... FROM table_name ORDER BY columnX DESC
SELECT column1,
column2, ... FROM table_name ORDER BY columnX DESC, columnY ASC
82) The IN
operator may be used if you know the exact value you want to return for at
least one of the columns.
SELECT
column_name FROM table_name WHERE column_name IN (value1,value2,..)
*******Thank you for reading and All the best**************
Your comments will help me a lot...please give your comments which will encourage me to post more.....
