1. What is SQL and where does it come from?
Structured
Query Language (SQL) is a language that provides an interface to relational
database systems. SQL was developed by IBM in the 1970s for use in System R,
and is a de facto standard, as well as an ISO and ANSI standard. SQL is often
pronounced SEQUEL.
In common usage SQL also encompasses DML
(Data Manipulation Language), for INSERTs, UPDATEs, DELETEs and DDL (Data
Definition Language), used for creating and modifying tables and other database
structures. The development of SQL is governed by standards. A major revision
to the SQL standard was completed in 1992, called SQL2. SQL3 support object
extensions and are (partially?) implemented in Oracle8 and 9.
2. What are the difference between DDL, DML and DCL
commands?
DDL is Data Definition Language statements. Some
examples:
· CREATE - to create objects in
the database
· ALTER - alters the structure of
the database
· DROP - delete objects from the
database
· TRUNCATE - remove all records
from a table, including all spaces allocated for the records are removed
· COMMENT - add comments to the
data dictionary
· GRANT - gives user's access
privileges to database
· REVOKE - withdraw access
privileges given with the GRANT command
DML is Data Manipulation Language statements. Some
examples:
· SELECT - retrieve data from the
a database
· INSERT - insert data into a
table
· UPDATE - updates existing data
within a table
· DELETE - deletes all records
from a table, the space for the records remain
· CALL - call a PL/SQL or Java
subprogram
· EXPLAIN PLAN - explain access
path to data
·
LOCK
TABLE - control concurrency
DCL is Data Control Language statements. Some examples:
· COMMIT - save work done
· SAVEPOINT - identify a point in
a transaction to which you can later roll back
· ROLLBACK - restore database to
original since the last COMMIT
· SET TRANSACTION - Change
transaction options like what rollback segment to use
Session Control :
ALTERSESSION & SET ROLE.
System Control : ALTER
SYSTEM.
3. How does one escape special characters when building
SQL queries?
The LIKE
keyword allows for string searches. The '_' wild card character is used to
match exactly one character, '%' is used to match zero or more occurrences of
any characters. These characters can be escaped in SQL. Example:
SELECT name FROM emp WHERE id LIKE '%\_%'
ESCAPE '\';
Use two quotes for every one
displayed. Example:
SELECT 'Franks''s Oracle site' FROM DUAL;
SELECT 'A ''quoted'' word.' FROM DUAL;
SELECT 'A ''''double quoted'''' word.' FROM DUAL;
Escape
SQL*Plus special characters
When using SQL*Plus, the DEFINE
setting can be changed to allow &'s (ampersands) to be used in text:
SET DEFINE ~
SELECT 'Lorel & Hardy' FROM dual;
Other methods:
SET ESCAPE '\'
SELECT '\&abc' FROM dual;
SET SCAN OFF
SELECT '&ABC' x FROM dual;
4. How does one eliminate duplicates rows from a table?
Choose one of the
following queries to identify or remove duplicate rows from a table leaving
only unique records in the table:
Method 1:
SQL> DELETE FROM
table_name A WHERE ROWID > (SELECT min(rowid) FROM table_name B WHERE
A.key_values = B.key_values);
Method 2:
SQL>
create table table_name2 as select distinct * from table_name1;
SQL> drop table_name1;
SQL> rename table_name2 to table_name1;
SQL> -- Remember to recreate all indexes,
constraints, triggers, etc on table...
Method 3:
SQL>
delete from my_table t1 where exists
(select 'x' from my_table t2 where t2.key_value1 = t1.key_value1 and
t2.key_value2 = t1.key_value2 and
t2.rowid > t1.rowid);
Note: One
can eliminate N^2 unnecessary operations by creating an index on the joined
fields in the inner loop (no need to loop through the entire table on each pass
by a record). This will speed-up the deletion process.
Note 2: If
you are comparing NOT-NULL columns, use the NVL function. Remember that NULL is
not equal to NULL. This should not be a problem as all key columns should be
NOT NULL by definition.
5. How does one generate primary key values for a
table?
Create your table with a
NOT NULL column (say SEQNO). This column can now be populated with unique
values:
SQL> UPDATE table_name SET seqno = ROWNUM;
or use a sequences generator:
SQL> CREATE SEQUENCE sequence_name START WITH 1 INCREMENT BY 1;
SQL> UPDATE table_name SET seqno = sequence_name.NEXTVAL;
Finally,
create a unique index on this column.
6. How does one get the time difference between two
date columns?
Look at this
example query:
select
floor(((date1-date2)*24*60*60)/3600) ||
' HOURS ' || floor((((date1-date2)*24*60*60) -
floor(((date1-date2)*24*60*60)/3600)*3600)/60) || ' MINUTES ' || round((((date1-date2)*24*60*60) -
floor(((date1-date2)*24*60*60)/3600)*3600 - (floor((((date1-date2)*24*60*60) -
floor(((date1-date2)*24*60*60)/3600)*3600)/60)*60))) || ' SECS '
time_difference from ...
If you don't
want to go through the floor and ceiling math, try this method
select
to_char(to_date('00:00:00','HH24:MI:SS') + (date1 - date2), 'HH24:MI:SS')
time_difference from ...
Note that
this query only uses the time portion of the date and ignores the date itself.
It will thus never return a value bigger than 23:59:59.
SQL> SELECT to_number( to_char(to_date('1','J') + (date1 - date2), 'J') - 1) days, to_char(to_date('00:00:00','HH24:MI:SS') +
(date1 - date2), 'HH24:MI:SS') time FROM dates;
DAYS TIME
---------- --------
1 00:00:00
0 01:00:00
0 00:01:00
NUMTODSINTERVAL: This function
is new to Oracle 9i. It takes two arguments numtodsinterval(x,c) where x is a
number and c is a character string denoting the units of x. Valid units are
'DAY', 'HOUR', 'MINUTE' and 'SECOND'.
This function converts the
number x into an INTERVAL DAY TO SECOND datatype.
SQL> select numtodsinterval(date1-date2,'day') time_difference from dates;
TIME_DIFFERENCE
----------------------------------------------------------------
+000000001 00:00:00.000000000
+000000000 01:00:00.000000000
+000000000 00:01:00.000000000
7. How does one add a day/hour/minute/second to a date
value?
The SYSDATE
pseudo-column shows the current system date and time. Adding 1 to SYSDATE will
advance the date by 1 day. Use fractions to add hours, minutes or seconds to
the date. Look at these examples:
SQL>
select sysdate, sysdate+1/24, sysdate +1/1440, sysdate + 1/86400 from dual;
SYSDATE SYSDATE+1/24 SYSDATE+1/1440 SYSDATE+1/86400
--------------------
-------------------- -------------------- --------------------
03-Jul-2002 08:32:12 03-Jul-2002
09:32:12 03-Jul-2002 08:33:12 03-Jul-2002 08:32:13
The
following format is frequently used with Oracle Replication:
select sysdate NOW,
sysdate+30/(24*60*60) NOW_PLUS_30_SECS from dual;
NOW NOW_PLUS_30_SECS
--------------------
--------------------
03-JUL-2002 16:47:23 03-JUL-2002
16:47:53
8. How does one count different data values in a
column?
Use this simple query to
count the number of data values in a column:
select my_table_column, count(*) from my_table group by my_table_column;
A more sophisticated
example...
select dept, sum( decode(sex,'M',1,0)) MALE, sum( decode(sex,'F',1,0)) FEMALE,
count(decode(sex,'M',1,'F',1)) TOTAL from my_emp_table group by dept;
9. How does one count/sum RANGES of data values in a
column?
A value x
will be between values y and z if GREATEST(x, y) = LEAST(x, z). Look at this
example:
select f2, sum(decode(greatest(f1,59),
least(f1,100), 1, 0)) "Range 60-100",
sum(decode(greatest(f1,30), least(f1, 59), 1,
0)) "Range 30-59",
sum(decode(greatest(f1, 0), least(f1, 29), 1, 0)) "Range
00-29"
from
my_table group by f2;
For equal
size ranges it might be easier to calculate it with DECODE(TRUNC(value/range),
0, rate_0, 1, rate_1, ...). Eg.
select ename
"Name", sal "Salary",
decode( trunc(f2/1000, 0), 0, 0.0,
1, 0.1, 2, 0.2, 3, 0.31) "Tax rate"
from
my_table;
10. Can one retrieve only the Nth row from a table?
SELECT *
FROM ( SELECT ENAME,ROWNUM RN FROM EMP WHERE ROWNUM < 101 ) WHERE
RN = 100;
Note: Note:
In this first it select only one more than the required row, then it selects
the required one. Its far better than using MINUS operation.
SELECT f1 FROM t1 WHERE rowid = ( SELECT rowid FROM t1 WHERE
rownum <= 10 MINUS
SELECT rowid FROM t1 WHERE rownum < 10);
Alternatively...
SELECT * FROM emp WHERE rownum=1 AND
rowid NOT IN (SELECT rowid FROM emp WHERE rownum < 10);
Please note,
there is no explicit row order in a relational database. However, this query is
quite fun and may even help in the odd situation.
11. Can one retrieve only rows X to Y from a table?
SELECT *
FROM ( SELECT ENAME,ROWNUM RN FROM EMP
WHERE ROWNUM < 101 ) WHERE RN between 91 and 100 ;
Note: the
101 is just one greater than the maximum row of the required rows (means x= 90,
y=100, so the inner values is y+1).
Another
solution is to use the MINUS operation. For example, to display rows 5 to 7,
construct a query like this:
SELECT *
FROM tableX WHERE rowid in ( SELECT rowid FROM tableX WHERE rownum <= 7 MINUS
SELECT rowid FROM tableX WHERE
rownum < 5);
Please note,
there is no explicit row order in a relational database. However, this query is
quite fun and may even help in the odd situation.
12. How does one select EVERY Nth row from a table?
One can
easily select all even, odd, or Nth rows from a table using SQL queries like
this:
Method
1: Using a subquery
SELECT * FROM
emp WHERE (ROWID,0) IN (SELECT
ROWID, MOD(ROWNUM,4) FROM emp);
Method
2: Use dynamic views
SELECT * FROM ( SELECT rownum rn, empno, ename FROM emp )
temp
WHERE
MOD(temp.ROWNUM,4) = 0;
Please note,
there is no explicit row order in a relational database. However, these queries
are quite fun and may even help in the odd situation.
13. How does one select the TOP N rows from a table?
Form
Oracle8i one can have an inner-query with an ORDER BY clause. Look at this
example:
SELECT * FROM
(SELECT * FROM my_table ORDER BY col_name_1 DESC) WHERE ROWNUM < 10;
(available
from Oracle7.2):
Use this
workaround with prior releases:
SELECT * FROM my_table a WHERE 10 >=
(SELECT COUNT(DISTINCT maxcol) FROM my_table b
WHERE b.maxcol >= a.maxcol) ORDER BY maxcol DESC;
If you are
using Oracle 9i you can use the RANK function also.
select * from (select empno,sal,rank() over (order by sal desc ) rnk from emp) where rnk <= 5;
select * from (select empno,sal,rank() over (order by sal desc ) rnk from emp) where rnk <= 5;
14. How does one code a tree-structured query?
Tree-structured
queries are definitely non-relational (enough to kill Codd and make him roll in
his grave). Also, this feature is not often found in other database offerings.
The
SCOTT/TIGER database schema contains a table EMP with a self-referencing
relation (EMPNO and MGR columns). This table is perfect for testing and
demonstrating tree-structured queries as the MGR column contains the employee
number of the "current" employee's boss.
The LEVEL
pseudo-column is an indication of how deep in the tree one is. Oracle can
handle queries with a depth of up to 255 levels. Look at this example:
select LEVEL, EMPNO, ENAME, MGR
from
EMP
connect by prior EMPNO = MGR
start with MGR is NULL;
One can
produce an indented report by using the level number to substring or lpad() a
series of spaces, and concatenate that to the string. Look at this example:
select lpad(' ', LEVEL * 2) || ENAME
........
One uses the
"start with" clause to specify the start of the tree. More than one
record can match the starting condition. One disadvantage of having a
"connect by prior" clause is that you cannot perform a join to other
tables. The "connect by prior" clause is rarely implemented in the
other database offerings. Trying to do this programmatically is difficult as
one has to do the top level query first, then, for each of the records open a
cursor to look for child nodes.
One way of
working around this is to use PL/SQL, open the driving cursor with the
"connect by prior" statement, and the select matching records from
other tables on a row-by-row basis, inserting the results into a temporary
table for later retrieval.
15. How does one code a matrix report in SQL?
Look at this
example query with sample output:
SELECT *
FROM (SELECT job, sum(decode(deptno,10,sal)) DEPT10, sum(decode(deptno,20,sal)) DEPT20,
sum(decode(deptno,30,sal))
DEPT30, sum(decode(deptno,40,sal)) DEPT40 FROM scott.emp GROUP BY job) ORDER BY 1;
JOB DEPT10 DEPT20
DEPT30 DEPT40
--------- ---------- ----------
---------- ----------
ANALYST 6000
CLERK 1300 1900 950
MANAGER 2450 2975 2850
PRESIDENT 5000
SALESMAN 5600
16. How does one implement IF-THEN-ELSE in a select
statement?
Oracle SQL
supports several methods of coding conditional IF-THEN-ELSE logic in SQL
statements. Here are some:
CASE Expressions
From Oracle
8i one can use CASE statements in SQL. Look at this example:
SELECT
ename, CASE WHEN sal = 1000 THEN 'Minimum wage' WHEN sal > 1000 THEN 'Over paid'
ELSE 'Under paid' END AS "Salary Status" FROM
emp;
DECODE() Function
The Oracle
decode function acts like a procedural statement inside an SQL statement to
return different values or columns based on the values of other columns in the
select statement. Examples:
select
decode(sex, 'M', 'Male', 'F', 'Female', 'Unknown') from employees;
select a, b,
decode( abs(a-b), a-b, 'a > b',
0, 'a = b', 'a < b') from tableX;
Note: The
decode function is not ANSI SQL and is rarely implemented in other RDBMS offerings. It is one of the
good things about Oracle, but use it sparingly if portability is required.
GREATEST() and LEAST() Functions
select
decode( GREATEST(A,B), A, 'A is greater OR EQUAL than B', 'B is greater than A')...
select
decode( GREATEST(A,B), A, decode(A,
B, 'A NOT GREATER THAN B', 'A GREATER THAN B'),
'A NOT GREATER THAN B')...
NVL() and NVL2() Functions
NVL and NVL2
can be used to test for NULL values.
NVL(a,b) ==
if 'a' is null then return 'b'.
SELECT
nvl(ename, 'No Name') FROM emp;
NVL2(a,b,c)
== if 'a' is not null then return 'b' else return 'c'.
SELECT nvl2(ename,
'Do have a name', 'No Name') FROM emp;
COALESCE() Function
COALESCE()
returns the first expression that is not null. Example:
SELECT 'Dear
'||COALESCE(preferred_name, first_name, 'Sir or Madam') FROM emp2;
NULLIF() Function
NULLIF()
returns a NULL value if both parameters are equal in value. The following query
would return NULL:
SELECT
NULLIF(ename, ename) FROM emp;
17. How can one dump/ examine the exact content of a
database column?
SELECT
DUMP(col1) FROM tab1 WHERE cond1 = val1;
DUMP(COL1)
-------------------------------------
Typ=96 Len=4: 65,66,67,32
For this
example the type is 96, indicating CHAR, and the last byte in the column is 32,
which is the ASCII code for a space. This tells us that this column is blank-padded.
18. How can I change my Oracle password?
Issue the following SQL
command: ALTER USER <username> IDENTIFIED BY <new_password>;
19. How does one find the next value of a sequence?
Perform an
"ALTER SEQUENCE ... NOCACHE" to unload the unused cached sequence
numbers from the Oracle library cache. This way, no cached numbers will be
lost. If you then select from the USER_SEQUENCES dictionary view, you will see
the correct high water mark value that would be returned for the next NEXTVALL
call. Afterwards, perform an "ALTER SEQUENCE ... CACHE" to restore
caching.
You can use
the above technique to prevent sequence number loss before a SHUTDOWN ABORT, or
any other operation that would cause gaps in sequence values.
20.
what is Table ?
A table is the basic unit of data storage in an ORACLE database. The
tables of a database hold all of the user accessible data. Table data is stored
in rows and columns.
21. What is a View ?
A view is a virtual table. Every view has a Query attached to it. (The
Query is a SELECT statement that identifies the columns and rows of the
table(s) the view uses.)
22. Do View contain Data ?
A view does not contain any data of
its own, but is like a window through which data from other tables can be
viewed and changed
The answer depends on the type of
view. In case of normal view, the ans is NO it only contains query based on a
base table but in case of materialized view, YES it does contain data and for
the updated data in the base table, it needs to be refreshed.
NO: Because view is for view one or more
tables data like query.
23. Can a View based on another View ?
Yes.
24. What are the advantages of Views ?
Provide an additional level of table security, by restricting access to
a predetermined set of rows and columns of a table. Hide data complexity.
Simplify commands for the user. Present the data in a different perspective
from that of the base table. Store
complex queries.
25. What is a Sequence ?
A sequence generates a serial list of unique numbers for numerical columns
of a database's tables.
26. What is a Synonym ?
A synonym is an alias for a table, view, sequence or program unit.
27. What are the type of Synonyms?
There are two types of Synonyms Private and Public.
28. What is a Private Synonyms ?
A Private Synonyms can be accessed only by the owner.
29. What is a Public Synonyms ?
A Public synonyms can be accessed by any user on the database.
30. What are synonyms used for ?
Synonyms are used to : Mask the real name and owner of an object.
Provide public access to an object Provide location transparency for
tables,views or program units of a remote database. Simplify the SQL statements
for database users.
31. What is an Index ?
An Index is an optional structure associated with a table to have direct
access to rows,which can be created to increase the performance of data
retrieval. Index can be created on one or more columns of a table.
32. How are Indexes Update ?
Indexes are automatically maintained and used by ORACLE. Changes to
table data are automatically incorporated into all relevant indexes.
33. What are Clusters ?
Clusters are groups of one or more tables physically stores together to
share common columns and are often used together.
34. What is cluster Key ?
The related columns of the tables in a cluster is called the Cluster
Key. The cluster key is indexed using a cluster index and its value is stored
only once for multiple tables in the cluster.
35. What is Index Cluster ?
A Cluster with an index on the Cluster Key.
36. What is Hash Cluster ?
A row is stored in a hash cluster based on the result of applying a hash
function to the row's cluster key value. All rows with the same hash key value
are stores together on disk.
37. When can Hash Cluster used ?
Hash clusters are better choice when a table is often queried with
equality queries. For such queries the specified cluster key value is hashed.
The resulting hash key value points directly to the area on disk that stores
the specified rows.
38. What is an Integrity Constrains ?
An integrity constraint is a declarative way to define a business rule
for a column of a table. Integrity
constraint is a rule that restricts values to a column in a table.
39. Can an Integrity Constraint be enforced on a table if
some existing table data does not satisfy the constraint ?
No.
40. Describe the different type of Integrity Constraints
supported by ORACLE ?
NOT NULL Constraint - Disallows NULLs in a table's column.
UNIQUE Constraint - Disallows duplicate values in a column or set of
columns.
PRIMARY KEY Constraint - Disallows duplicate values and NULLs in a
column or set of columns.
FOREIGN KEY Constrain - Require each value in a column or set of columns
match a value in a related table's UNIQUE or PRIMARY KEY.
CHECK Constraint - Disallows values that do not satisfy the logical
expression of the constraint.
41. What is difference between UNIQUE constraint and PRIMARY
KEY constraint ?
A column defined as UNIQUE can contain NULLs while a column defined as
PRIMARY KEY can't contain Nulls.
A Table may have only one primary key where as more than one unique key
can be assigned within a table
42. Describe Referential Integrity ?
A rule defined on a column (or set of columns) in one table that allows
the insert or update of a row only if the value for the column or set of
columns (the dependent value) matches a value in a column of a related table
(the referenced value). It also specifies the type of data manipulation allowed
on referenced data and the action to be performed on dependent data as
a result of any action on referenced data.
Maintaining data integrity through a set of rules that restrict the
values of one or more columns of the tables based on the values of primary key
or unique key of the referenced table.
43. What are the Referential actions supported by FOREIGN
KEY integrity constraint ?
UPDATE and DELETE Restrict - A referential integrity rule that disallows
the update or deletion of referenced data.
DELETE Cascade - When a referenced row is deleted all associated dependent
rows are deleted.
44. What is self-referential integrity constraint ?
If a foreign key reference a parent key of the same table is called
self-referential integrity constraint.
45. What are the Limitations of a CHECK Constraint ?
The condition must be a Boolean expression evaluated using the values in
the row being inserted or updated and can't contain subqueries, sequence, the
SYSDATE,UID,USER or USERENV SQL functions, or the pseudo columns LEVEL or
ROWNUM.
46. What is the maximum number of CHECK constraints that can
be defined on a column ?
No Limit.
47. Define Transaction ? OR What is a transaction ?
A Transaction is a logical unit of work that comprises one or more SQL
statements executed by a single user. Transaction is logical unit between two
commits and commit and rollback.
48. When does a Transaction end ?
When it is committed or Rollbacked.
49. What does COMMIT do ?
COMMIT makes permanent the changes resulting from all SQL statements in
the transaction. The changes made by the SQL statements of a transaction become
visible to other user sessions transactions that start only after transaction
is
committed.
50. What does ROLLBACK do ?
ROLLBACK retracts any of the changes resulting from the SQL statements
in the transaction.
51. What is SAVE POINT ?
For long transactions that contain many SQL statements, intermediate
markers or savepoints can be declared which can be used to divide a transaction
into smaller parts. This allows the option of later rolling back all work
performed from the current point in the transaction to a declared savepoint
within the transaction.
52. What is Read-Only Transaction ?
A Read-Only transaction ensures that the results of each query executed
in the transaction are consistant with respect to the same point in time.
53. What are the different types of PL/SQL program units
that can be defined and stored in ORACLE database ?
Procedures and Functions, Packages and Database Triggers.
54. What is a Procedure / stored procedure? What are
advantages of Stored Procedures ?
A Procedure consist of a set of SQL and PL/SQL statements that are
grouped together as a unit to solve a specific problem or perform a set of
related tasks.
Extensibility, Modularity, Reusability, Maintainability and one time
compilation.
55. What is difference between Procedures and Functions ?
A Function returns a value to the caller where as a Procedure does not. A
FUNCTION is alway returns a value using the return statement. A PROCEDURE may
return one or more values through parameters or may not return at all.
56. What is a Package ? What are the advantages of packages
?
A Package is a collection of related procedures, functions, variables
and other package constructs together as a unit in the database.
The advantages of packages are Modularity, Easier Applicaton Design,
Information. Hiding,. reusability and Better
Performance. Increased functionality (for example,global package
variables can be declared and used by any proecdure in the package) and
performance (for example all objects of the package are parsed compiled, and loaded
into memory once)
57. What are two parts of package ?
The two parts of package are PACKAGE SPECIFICATION & PACKAGE BODY.
Package Specification contains declarations that are global to the
packages and local to the schema. Package Body contains actual procedures and
local declaration of the procedures and cursor declarations.
58. What is Database Trigger ?
A Database Trigger is procedure (set of SQL and PL/SQL statements) or stored
PL/SQL program unit associated with a specific database table, that is automatically executed as a result of
an insert in, update to, or delete from a table.
59. What are the uses of Database Trigger ?
Database triggers can be used to automatic data generation, Log events
transparently ,audit data modifications, enforce complex Integrity constraints,
and customize complex security authorizations. Maintain replicate tables.
60. What are the differences between Database Trigger and
Integrity constraints ?
A declarative integrity constraint is a statement about the database that
is always true. A constraint applies to existing data in the table and any
statement that manipulates the table. A trigger does not apply to data loaded
before the definition of the trigger, therefore, it does not guarantee all data
in a table conforms to the rules established by an associated trigger. A
trigger can be used to enforce transitional constraints where as a
declarative integrity constraint cannot be used.
61. What is a Schema ?
The set of objects owned by user account is called the schema.
62. What is an Index ? How it is implemented in Oracle
Database ?
An index is a database structure used by the server to have direct
access of a row in a table.
An index is automatically created when a
unique of primary key constraint clause is specified in create table command.
An Index is an optional structure
associated with a table to have direct access to rows, which can be created to
increase the performance of data retrieval. Index can be created on one or more
columns of a table.
63. What is difference between TRUNCATE & DELETE and
DROP ?
Both the truncate and delete command have the desired outcome of getting
rid of all the rows in a table. The difference between the two is that the
truncate command is a DDL operation and just moves the high water mark and
produces a now rollback. The delete command, on the other hand, is a DML
operation, which will produce a rollback and thus take longer to complete.
TRUNCATE commits after deleting entire
table i.e., can not be rolled back. Database triggers do not fire on TRUNCATE,
DELETE allows the filtered deletion. Deleted records can be rolled back or
committed. Database triggers fire on DELETE.
The DROP command removes a
table from the database. All the tables' rows, indexes and privileges will also
be removed. The operation cannot be rolled back.
DROP and TRUNCATE are DDL
commands, whereas DELETE is a DML command. Therefore DELETE operations can be
rolled back (undone), while DROP and TRUNCATE operations cannot be rolled back.
From Oracle 10g a table can be
"undropped". Example:
SQL> FLASHBACK TABLE emp TO BEFORE DROP;
64. What is a join ? Explain the different types of joins ?
Join is a query which retrieves related columns or rows from multiple
tables.
Self Join -Joining the table with itself.
Equi Join - Joining two tables by equating two common columns.
Non-Equi Join - Joining two tables by equating two common columns.
Outer Join - Joining two tables in such a way that query can also
retrive rows that do not have corresponding join value in the other table.
65. What is the Subquery ?
Subquery is a query whose return values are used in filtering conditions
of the main query.
66. What is correlated sub-query ?
Correlated sub_query is a sub_query which has reference to the main
query.
67. Explain CONNECT BY PRIOR ?
Retrives rows in hierarchical order. e.g. select empno, ename from emp
where.
68. Difference between SUBSTR and INSTR ?
INSTR
(String1,String2(n,(m)), INSTR returns the position of the mth occurrence of
the string 2 in string1. The search begins from nth position of string1. SUBSTR
(String1 n,m) SUBSTR returns a character string of size m in string1, starting
from nth postion of string1. SUBSTR return a specified substring from a string.
69. Explain UNION,MINUS,UNION ALL, INTERSECT ?
INTERSECT returns all distinct rows
selected by both queries. MINUS - returns all distinct rows selected by the
first query but not by the second. UNION - returns all distinct rows selected
by either query. UNION ALL - returns all rows selected by either query,including
all duplicates.
if the table
has Duplicates, UNION will remove them. If the table has no duplicates, UNION
will force a sort and cause performance degradation as compared to UNION ALL.
70. What is ROWID ?
ROWID is a pseudo column attached to each row of a table. It is 18
character long, blockno, rownumber are the components of ROWID.
71. What is the fastest way of accessing a row in a table ?
Using ROWID. CONSTRAINTS
72. What are the usage of SAVEPOINTS ?
SAVEPOINTS are used to subdivide a transaction into smaller parts. It
enables rolling back part of a transaction. Maximum of five save points are
allowed.
73. What is ON DELETE CASCADE ?
When ON DELETE CASCADE is specified ORACLE maintains referential
integrity by automatically removing dependent foreign key values if a
referenced primary or unique key value is removed.
74. Built-in Data
types in ORCALE
? OR
What are the data types allowed in a
table ?
a)
VARCHAR2 - Variable length character string. The maximum size is 4000
bytes. Will not have trailing blanks.
b)
NVARCHAR2 - Variable length character string. Generally used with national
character sets. The maximum size is 4000 bytes.
Will not have trailing blanks.
c)
NUMBER - Variable length numeric data. Maximum length is 21 bytes.
d) LONG
- Variable length character data. Maximum length is 2 gigabyte. Provide for
backward capability.
e) DATA
- Fixed length date and time. Uses 7 bytes.
f)
TIMESTAMP - Contain year, month, day,
second, and fractional seconds.
g)
INTERVELYEAR/DAY - Store period of time in years and months.
i) RAW
- Variable length raw binary data. Maximum length is 2000 bytes. Provide for
backward capability.
j) LONG
RAW - Variable length raw binary data. Maximum length is 3 gigabytes. Provide
for backward capability.
h) ROWID
- Hexadecimal string containing the address of the row.
i)
UROWID - Hexadecimal string containing the logical address of the row.
j) CHAR
- Fixed length character string. The maximum size is 2000 bytes. Will have trailing blanks.
k)
NCHAR - Variable length character string. Generally used with national
character sets. The maximum size is 2000 bytes.
Will have trailing blanks.
l) CLOB
- Single byte character data upto 4 gigabyte.
m)
NCLOB - Single byte or fixed length multibyte National Character set up 4
gigabyte.
n)
BLOB- Unstructured binary data up to 4 gigabyte.
o)
BFILE - Binary data stored in an external file.
75. What is difference between CHAR and VARCHAR2 and which is better approach? What is the
maximum SIZE allowed for each type ?
A CHAR datatype and VARCHAR2 datatype are stored identically (eg: the
word 'WORD' stored in a CHAR(4) and a varchar2(4) consume exactly the same
amount of space on disk, both have leading byte counts). For CHAR it is 255 and
2000 for VARCHAR2.
The difference between a CHAR and a VARCHAR is that a CHAR(n) will
ALWAYS be N bytes long, it will be blank padded upon insert to ensure
this. A varchar2(n) on the other hand
will be 1 to N bytes long, it will NOT be blank padded. Using a CHAR on a
varying width field can be a pain due to the search semantics of CHAR.
Consider the following examples:
SQL> create table t ( x char(10) );
Table created.
SQL> insert into t values ( 'Hello' );
1 row created.
SQL> select * from t where x = 'Hello';
X
----------
Hello
SQL> variable y varchar2(25)
SQL> exec :y := 'Hello'
PL/SQL procedure successfully completed.
SQL> select * from t where x = :y;
no rows selected
SQL> select * from t where x = rpad(:y,10);
X
----------
Hello
Notice how when doing the search with a varchar2 variable (almost every
tool in the world uses this type), we have to rpad() it to get a hit. If the
field is in fact ALWAYS 10 bytes long, using a CHAR will not hurt -- HOWEVER,
it will not help either.
76. How many LONG columns are allowed in a table ? Is it
possible to use LONG columns in WHERE clause or ORDER BY ?
Only one LONG columns is allowed. It is not possible to use LONG column
in WHERE or ORDER BY clause.
77. Where the integrity constrints are stored in Data
Dictionary ? OR
Which system table contains information
on constraints on all the tables created? Obtained?
The integrity constraints are stored in
USER_CONSTRAINTS.
78. How will you a activate/deactivate integrity constraints
?
The integrity constraints can be enabled or disabled by ALTER TABLE
ENABLE constraint/DISABLE constraint.
79. If an unique key constraint on DATE column is created,
will it validate the rows that are inserted with SYSDATE ?
It won't, Because SYSDATE format contains time attached with it.
80. What is CYCLE/NO CYCLE in a Sequence ?
CYCLE specifies that the sequence continues to generate values after
reaching either maximum or minimum value. After pan ascending sequence reaches
its maximum value, it generates its minimum value. After a descending sequence
reaches its minimum, it generates its maximum. NO CYCLE specifies that the
sequence cannot generate more values after reaching its maximum or minimum
value.
81. Can a view be updated/inserted/deleted? If Yes under
what conditions ?
A View can be updated/deleted/inserted if it has only one base table if
the view is based on columns from one or more tables then insert, update and
delete is not possible.
82. If a View on a single base table is manipulated will the
changes be reflected on the base table ?
If changes are made to the tables which are base tables of a view will
the changes be reference on the view.
83. What is PL/SQL ?
PL/SQL is a procedural language that has both interactive SQL and
procedural programming language constructs such as iteration, conditional
branching.
84. What is the basic structure of PL/SQL ?
PL/SQL uses block structure as its basic structure. Anonymous blocks or
nested blocks can be used in PL/SQL.
85. what is PL/SQL
Block. What are the components of a PL/SQL block ?
A set of related declarations and procedural statements is called block.
Declarative part, Executable part and Execption part.
86. What are the data types a available in PL/SQL ?
Some scalar data types such as NUMBER, VARCHAR2, DATE, CHAR, LONG,
BOOLEAN.
Some composite data types such as RECORD & TABLE.
87. What are % TYPE and % ROWTYPE ? What are the advantages
of using these over datatypes?
% TYPE provides the data type of a variable or a database column to that
variable. %ROWTYPE provides the record type that represents a entire row of a
table or view or columns selected in the cursor.
The advantages are : I. Need not know about variable's data type ii. If
the database definition of a column in a table changes, the data type of a
variable changes accordingly.
E.g. TYPE r_emp
is RECORD (eno emp.empno% type,ename emp ename %type); e_rec emp% ROWTYPE;
cursor c1 is
select empno,deptno from emp; e_rec c1 %ROWTYPE.
88. What is PL/SQL table ?
Objects of type TABLE are called "PL/SQL tables", which are
modeled as (but not the same as) database tables, PL/SQL tables use a primary
PL/SQL tables can have one column and a primary key.PL/SQL tables are scalar
arrays that can be referenced by a binary integer. They can be used to hold
values for use in later queries or calculations. In Oracle 8 they will be able
to be of the %ROWTYPE designation, or RECORD.
89. What is a cursor ? Why Cursor is required ?
Cursor is a named private SQL area from
where information can be accessed. Cursors are required to process rows
individually for queries returning multiple rows.
Oracle uses work area to execute SQL
statements and store processing information PL/SQL construct called a cursor
lets you name a work area and access its stored information A cursor is a
mechanism used to fetch more than one row in a Pl/SQl block.
90. Explain the two type of Cursors ?
There are two types of cursors, Implict Cursor and Explicit Cursor.
PL/SQL uses Implicit Cursors for queries.
User defined cursors are called Explicit Cursors. They can be declared
and used.
No comments:
Post a Comment