Sql Interview Question
1) What is SQL and where does it come from? Home
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, and are 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 will be (partially?) Implemented in Oracle8.
2) DDL home
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
Difference between Delete and truncate
1) Delete-DML Truncate
-It will mark the records to delete -It is DDL
-We can rollback -WE CAN'T ROLLBACK
-We can delete the records by condition -WE CAN'T APPLY CONDITIONS
-IF ANY LOCKS ARE THREE ON THE TABLE
THEY WILL RELEASED
-It will releases the space and we can latter we use that space From oracle 8i onwards we can a drop a column
Alter table <Table_name> drop column <column_name>
Alter table <table_name> set unused (last_name)
Alter table <table_name> drop unused columns
All this type of values are stored in user_unused_col_tab table
3) DCL
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
Savepoint
Rollback: Oracle Issues implicit commit before and after any DDL statement. Even if your DDL statement does not execute then also it issues commit statement
If we issue commit all savepoints will be erased and all locks are released.
4) DML
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
MUTATING TABLE: Mutating Table is a table that is currently being modified by a DML statement. For a Trigger, this is the table on which the table is defined.
DML Statement: Select, Update, Delete, Insert
Commit->it we issue the commit all savepoints are erased and all locks are released; If we disconnect the database then implicit commit statement will be issued.
Diff between POST and COMMIT:
POST puts the data from client to the server’s Rollback segment whereas COMMIT put the data from rollback segment to the Table.
5 Transaction->
A transaction is an operation against the database, which comprises a series of changes to one or more tables
A transaction begins when the first executable DML and DDL command is encountered and ends when one of the following occurs
* commit/Rollback/DDL command /log off/machine failure
6Locks
Shared/exclusive -When 2 transaction wants to read/write from db at the same time.
1) Table level2) row level
Row Exclusive locks are obtained when updating, inserting or deleting rows
Duration of locks
) All locks acquired during a transaction are released when the transaction is committed
2) All locks are acquired during a transaction are released when the transaction is rolled back
3) All locks are acquired after a savepoint are released when the transaction is rolled back to the savepoint
Dead lock-It is will come in multi-user environment
When the two users issues update statements then dead lock will come
Dead- 1trans updates EMP and dep
2 trans update dep and EMP
Dead locks are released when commit/rollback statements are issued or logging off
7) How does one code a tree-structured query?
Select LEVEL, EMPNO, ENAME, MGR
From EMP
Connect by prior EMPNO = MGR
start with MGR is NULL;
8.JOINS
Def: -A join is used when a SQL query requires data from more than one table or the same table on the database.
TYPES OF JOINS:
1) EQUI-JOIN: a join that is formed as a result of an exact match b/w two columns is called as equi-join or simple join or inner join.
2) NON-EQUI-JOIN: - NON-EQUI-JOIN is a join condition containing something other than an equality operator.
A join is that is formed based on the comparison operators (except "=") is called non-equi join.
EX: - SQL> SELECT e.last_name, e.salary, j.grade_level
FROM employee e, job_grades j
WHERE e.salary
BETWEEN j.lowest_sal AND j.highest_sal;
3) SELF-JOIN: A join relates a table to itself is called a self-join.
EX: - SQL> SELECT e.empno, e.ename, m.mgr from emp e, emp m where e.empno=m.mgr;
4) OUTER-JOIN: (+) always at the child side.
--You can use OUTER-JOIN to also see rows that do not meet the join condition.
--The outer-join operator is the plus sigh (+).
--The missing rows can be returned if outer-join operator is used in join condition.
EX: -SQL> SELECT E.EMPNO, D.DEPTNO FROM EMP E, DEPT D WHERE E.DEPTNO (+)=D.DEPTNO;
9.FUNCTINS->
CONVERSION FUNCTIONS:
TO_CHAR, TO_DATE, TO_NUMBER.
DATE FUNCTIONS:
ADD_MONTHS, LAST_DAY, MONTHS_BETWEEN,
NEW_TIME, NEXT_DAY, ROUND,
SYSDATE, TRUNC.
DECODE:
Nested IF THEN ELSE
SELECT DECODE (ename, ‘Smith’, ’a’, ‘Something’);
GROUP FUNCTIONS:
MAX () MIN () COUNT () AVG () SUM () VARIANCE ()
STDDEV ()
GROUP FUNCTION WITH having CLAUSE CONDITION:
NOTE: ALL GROUP FUNCTIONS IGNORE MULL VALUES IN THE COLUMN.
EX: SQL> SELECT job_id, SUM (salary) PAYROLL
FROM employee
WHERE job_id NOT LIKE '%REP%'
GROUP BY job_id
HAVING SUM (salary)>1000
ORDER BY SUM (salary);
10.SUB QUERIES
A Subquery is a SELECT statement that is embedded in a clause of another SELECT statement.
The inner query or the subquery returns a value that is used by the outer query or the main query.
You can write upto 255 subqueries.
TYPES OF SUBQUERIES:
1.Single-Row subquery: Query that returns only one row from the inner SELECT statement.
Simple Query--Which fires every time for entire stmt
2.Multiple-Row subquery: Query that returns more than one row from the inner SELECT statement.
CORRELATED SUBQUERY:
SQL*Plus performs a subquery repeatedly once for every row of the main query.
Correlated Subquery -Which fires only once/ per row for entire stmt.
Operators:
Logical Operators: NOT, AND, OR
Comparison Operators: NOT IN, LIKE
Set Operators: UNION, UNION ALL, INTERSECTION, MINUS
11.SET OPERATORS:
- The SET operator combine the result of two or more component queries into one result.
- Queries containing SET operator are called COMPOUND QUERIES.
- All SET operators have equal precedence.
1.UNION: All distinct rows selected by either query
EX: select job from emp where deptno=10
union
Select job from emp where deptno=20
2.UNION ALL: All rows selected by either query, including all duplicates.
EX: select job from emp where deptno=10
union all
Select job from emp where deptno=20
3.INTERSECT: All distinct rows selected by both queries.
EX: select job from emp where deptno=10
intersect
Select job from emp where deptno=20
4.MINUS: All distinct rows that are selected by the first SELECT statement and not selected in the second SELECT statement.
EX: select job from emp where deptno=10
minus
Select job from emp where deptno=20
12.PSEUDO COLUMNS:
They behave like table columns, but are not actually stored in the table.
CURRVAL, NEXTVAL, ROWID, ROWNUM, SYSDATE, LEVEL, USER, UID PARSING:
It checks the Syntax of SQL Statements.
13.INTEGRITY CONSTANTS:
Null, Not Null, Default, Check, Primary Key, Foreign Key
PRIMARY KEY: NOT NULL, UNIQUE, IF U WANT CREATE MORE THEN ONE COLUM WE SHOULD DEFINE AT TABLE LEVEL, IT'S ALSO CALLED COMPOSITE PRIMARY KEY, WE CAN'T CREATE MORE THAN ONE PRIMARY KEY.
UNIQUE:IT WILL NOT ALLOW DUPLICATE VALUES AND IT ACCEPT NULL VALUES CAN BE DEFINED AT THE COLUMN LEVEL AND TABLE LEAVEL
CHECK: IT WILL CHECK WITH COLUMN DEFINATION BEFORE ACCEPT.CHECK CONSTRAINT DEFINES A CONDITION THAT EACH ROW MUST SATISFY REFERENCES TO THE CURRVAL, NEXTVAL, LEVEL, ROWNUM PSEUDOCOLUMNS CALLS TO SYSDATE UID USER, AND USERENV. CHECK CONS. CAN BE DEFINED AT THE COLUMN LEVEL AND TABLE LEVEL
FORIEGN KEY:
- foreign KEYS POVIDE REFERENTIAL INTEGRITY RULES WITHER WITHIN A TABLE OR B/W TABLES
- CAN BE DEFINED AT TABLE LEVEL OR COLUMN LEVEL.A COMPOSITE FORIEGN KEY MUST BE CREATED BY USING THE TABLE-LEVEL DEFINATION
- A FORIEGN KEY IS USED IN A RELATIONSHIP WITH EITHER PRIMARY OR UNIQUE KEY.
References - identifies the table and column in the parent table.
ON DELETE CASCADE - Deletes the dependent rows in the child table when a row in the parent table is deleted.
ON DELETE SET NULL - CONVERTS DEPENDENT FORIEGN KEY VALUES TO NULL.
CASCADE CONSTRAINT - The CASCADE CONSTRAINTS clause drops all referential integrity constraints that refer to the primary and unique keys defined on the dropped columns.
The CASCADE CONSTRAINTS clause also drops all multicolumn constraints defined in the dropped columns.
14.INDEXES:
CREATE INDEXES IF U FREQUENTLY WANT TO RETRIEVE LESS THAN 15% OF THE ROWS IN A LARGE TABLE
- INDEX CLOUMNS USED FOR JOINS TO IMPROVE THE PERFORMANCE ON JOINS.
- DONT USE INDEXES ON TABLES HAVING LESS NO ROWS.
- IF THE COLUMN HAS NON-UNIQUE VALUES U CAN USE INDEXES ON THESE COLUMNS.
- DONT USE INDEXES IF THE DATATYPE IS LOB, CLOB & BLOB.
- IF THE TABLE IS read only WE CAN CREATE MORE INDEXES ON THE TABLE.
- WE CAN'T CREATE INDEXES ON VIEWS.
*- INDEXES ARE LOGICALLLY & PHISICALLY INDEPENDENT OF DATA
*- IF THE INDEX IS DEVLOPED ALL APLLICATIONS CONTINUE TO FUNCTION
Index is an ordered list of contents of a column or group of columns in a table. Index created on a single table Simple Index and which is created on multiple tables is called Composite Index.
CREATE INDEX Index_Name ON Table_Name (Column_Name);
DROP INDEX (Index_Name);
15.CLUSETER:
Cluster is a method of storing tables that are intimately related and are often joined
Together into the same area on disk
16.SYNONYM:
- SYNONYM IS AN ALIAS FOR A TABLE, VIEW, SEQUENCE & PROGRAM UNIT.
- A SYNONYM IS A NOT A SCHEMA OBJECT, IT WILL REFER SCHEMA OBJECT.
- MASK THE REAL NAME AND OWNER OF A SCHEMA OBJECT.
- PROVIDE PUBLIC ACCESS TO SCHEMA OBJECT.
- PROVIDE LOCATION TRANSPARENCY FOR TABLES, VIEWS, OR PROGRAM UNITS IF A REMOTE DATABASE.
- SIMPLIFY THE sql STATEMENTS FOR DATABASE USERS
22
PUBLIC SYNONYM & PRIVATE SYNONYM
17.VIEWS:
- VIEW IS A LOGICAL TABLE BASED ON A TABLE OR ANOTHER VIEW.
- VIEW CONTAINS NO DATA OF ITS OWN BUT IS LIKE A WINDOW THROUGH WHICH DATA FROM TABLES CAN BE VIEWED OR CHANGED.
- VIEW IS STORED AS SELECT STATEMENT IN THE DATA DICTIONARY.
WHY USE VIEWS:
- TO RESTRICT DATA ACCESS
- TO MAKE QUERIES EASY.
- TO PROVIDE DATA INDEPEMDENCE
- TO PRESENT DIFFERENT VIEWS OF THE SAME DATA.
SIMPLE VIEW: DERIVES DATA FROM ONLY ONE TABLE, CONTAINS NO FUNCTIONS OR GROUPS OF DATA, WE CAN PERFORM dml OPERATIONS THROUGH THE VIEW.
COMPLEX VIEW: DERIEVE DATA FROM MANY TABLES, CONTAINS FUNCTIONS OR GROUPS OF DATA, DOES NOT ALWAYS ALLOW dml OPERATIONS THROUGH THE VIEW.
NOTE: - U CAN'T CREATE INDEXES ON VIEWS.
- U CAM CREATE A VIEW WITHOUT A TABLE USING force OPTION.
- CAM CREATE with check option & with read only.
- We can modify, drop view.
SYNTAX: CREATE OR REPLACE force/noforce VIEW viewname alias subquery with check option
Constraint with read only.
If we use NOT NULL constraint in the base table without default values then we can't insert the row in the base table using the view
If we use decode function in creating the view we can't update the row in the base table
If we use these words
1) Distinct, avg, count, max, min, stddev, sum, union, union all, intersect, minums, rownum, start with prior to the we can't update the base table through view
If we rename the table then the view become invalid
But one can add the columns and the columns must not be not null columns in the base table
18.Table:
One can rename the table. If we rename the table then all views, synonyms, proceudres, triggers, functions become invalid
If we drop the table all the indexes are dropped
All rows corresponding cluster table are deleted from the blocks of the cluster
We can decrease the length of the column if the data is not there in the table.
I one can change the datatype at any point of time
If we drop the column from the base table if the view is based on that column then the view become invalid.
TO add a not null column to a table which has already some records -
Alter table a
Add (b number default 1 not null)
Portioned table
If we want to store the data of one table depending upon the range in different blocks. By using this we can reduce the network traffic. Table partitioning divides table data between two or more table spaces and physical data file on separate disk.
Select * from emp partion (p1)
19.TUNING SQL Statements:
Using TK_PROF, EXPLAIN PLAN.
If you have a system that is performing badly, a good way to identify problem SQL statements is to trace a typical user session and then use TkProf to format the output using the sort functions on the tkprof command line.
Explain plan is a representation of the access path that is taken when a query is executed within Oracle.
1. SET TIME ON;
2. ALTER SESSION SET SQL_TRACE = ‘TRUE’;
3. Then run the required program unit
4. Sql_trace file will be created in User_dump_dest (dir)
5. Since this trace_file is in the binary format, run tkprof
6. $ Tkprof <trace_file.trc> <output_file.txt> [options]
7. $ Tkprof <trace_file.trc> <output_file.txt> [EXPLAIN=user/password]
TKPROF allows you to analyze a trace file to determine where time is being spent and what query plans are being used on SQL statements. Tkprof is an executable that 'parses' Oracle trace files to produce more readable output. Remember that all the information in TkProf is available from the base trace file.
8. Elapsed time/num of rows * 1000 --> 'X' Mille Second.
20.Explain plan
To discover the execution plan for a select statement. The explain plan statement is most often used from sql* plus. Before that you must create the <plan table> to hold the results
Find INDEXES is being used:
By using EXPLAIN PLAN.
Output is put into PLAN_TABLE.
21.Sql trace:
To diagnosing performance problems on running systems.
To know
No of times the sql statement executed.
Total CPU and elapsed time used by the statement
Total no of physical reads trigger by the statement
Total no of records processed by the statement
Total no of the logical reads trigger by the statement
Hints:
They can be placed into your Sql statements to force the optimizers to utilize a particular execution path for absolute best performance.
/*+ ALL_ROWS */
/*+ FIRST_ROWS */
/*+ CHOOSE */
/*+ HASH_SJ */àWhen using subquery after EXISTSàIt improves the response time.
22.MATERIALLIZED VIEWS:
- Materialized views provide indirect access to table data by storing the results of a query in a separate schema object.
- Another name for materialized view is SNAPSHOT.
- MATERIALIZED VIEW CAN BE STORED IN A SAME DATABASE OR DIFF DATABASE.
- DATA CAN BE REPLICATED USING MATERIALIZED VIEW.E
23.TWO-PHASE COMMIT:
- It is used in a DISTRIBUTED ENVIRONMENT.
- It guarantees that no matter what type of system or network failure might occur a distributed transaction either commits on all involve nodes or rollback on all involve nodes to maintain data consistency.
24.SITE AUTOMONY:
- It means that each database participating in a distributed database is administered separately and independently from other database.
25.ROLLING FORWARD:
- The first step of recovery is to ROLL-FORWARD i.e. reapply to the data files all of the changes that are recorded in the REDO-LOG FILE.
Rolling Forward -To reapply to Data file to all changes that are recorded in Redo log file
Due to which data file contains committed & uncommitted data.
Forward Declaration-To declare variable and procedures before using it.
2- Tier Arch. Disadv-When Business Rule changes.
11.CAN U SELECT PROCEDURE FROM SQL STATEMENT?
Ans: No we can't select procedure from select statement, but we can select SQL FUNCTIONS AND
USER DEFINED FUNCTIONS.
26.Eliminate Duplicate records:
How does one eliminate duplicates rows from a table?
ROWID:
ROWID is the fastest way to locate records.
ROWID Uniquely identifies records.
Choose one of the following queries to identify or remove duplicate rows from a table
Leaving unique records in the table:
Method 1:
SQL> DELETE FROM table_name A WHERE ROWID > (
2 SELECT min (rowid) FROM table_name B
3 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;
Method 3: (thanks to Kenneth R Vanluvanee)
SQL> Delete from my_table where rowid not in (
SQL> select max (rowid) from my_table
SQL> group by my_column_name);
Method 4: (thanks to Dennis Gurnick)
SQL> delete from my_table t1
SQL> where exists (select 'x' from my_table t2
SQL> where t2.key_value1 = t1.key_value1
SQL> and t2.key_value2 = t1.key_value2
SQL> and t2.rowid > t1.rowid);
28.LOB Types:
Large Objects Datatypes. Lets us store blocks of unstructured data (text, graphic images, video files, audio files) of upto four Gb of size.
Bfile: BFILE datatype stores large Binary Objects in OS files outside the database.
Blob: BLOB datatype stores large Binary Objects in the database
Clob: CLOB datatype stores large blocks of single-byte Character data in the database.
29. PVCS:
It is a version control utility. PVCS change manager for Oracle can identify the location and impact of changes in Oracle Applications.
30. TOAD
TOAD is a powerful tool that makes PL/SQL development faster, easier and simpler.
31. INIT.ORA:
It is a System Parameter
32. Normalization
It's a technique thru. Which we can design the DB.
During normalization dependencies can be identified which can cause pbs during deletion &
Updation .It is used in simplifying the structure of table.
1NF-Unnorma;ised data transfer to normalized form.
2NF-Functional dependencies can be find out & decompose the table without loss of data.
3NF-Transist dependencies, every non-key attribute is functionally dependant on just PK.
4NF(BCNF)-The relation, which has multiple candidate keys, then we have to go for BCNF.
Denormalization-
At the same time when information is required from more than one table at faster rate then it is wiser to add some sort of dependencies.
27.FAQ’S
1. Which command in SQL*Plus is used to save the query output to a file?
ANS: SPOOL
2. How would you extract a SQL statement in the SQL buffer of SQL*Plus?
ANS: Enter a SLASH (/)
3. What is the default display length of the DATE Datatype column?
ANS: Eight
4. Which Clause in a query limits the rows selected?
ANS: WHERE
5. Which SQL*Plus command always overwrites a file?
ANS: SPOOL
6. Which single-row function could you use to return a specific portion of a character?
ANS: SUBSTR
7. Which of the following is not a Group Function? (AVG, COUNT, LEAST, STDDEV, VARIANCE)
ANS: LEAST
8. When using multiple tables to query information, in which Clause do you specify the table names?
ANS: FROM
9. What are the special characters allowed in a table name? (&, #, @, $)
ANS: #, $
10. What is the default length of a CHAR and Number Datatype column, if no length is specified in the table definition?
ANS: 1 and 9
11. At a minimum, how many join conditions should be there in the WHERE Clause to avoid a Cartesian join if there are Three Tables in the FROM Clause?
ANS: 2
There should be at least (n - 1) join conditions when joining (n) tables to avoid a Cartesian join
12. A view can only be used to query and update data, you cannot Insert into or delete from a view. True or false
ANS: FALSE
13. Which option is not available in Oracle when modifying tables? (Add new Columns, Rename an Existing Column, Drop an Existing Column) Rename an Existing Column
ANS: You cannot rename an Existing column using ALTER TABLE
14. EDIT SAVE FILE SQL*PLUS Default:
ANS: AFIEDT.buf
15. Eliminate Duplicate Rows in a Table:
ANS: ROWID (50).
16. Eliminate Duplicate Rows in a BASE TABLE BLOCK:
ANS: Create a SELECT DISTINCT VIEW on the base table
17 Set Transaction -To set a current transaction online offline
18 Optimization-Use of index (HINT)
19 Object Privilege - On a particular object- I/U/D/Exec
20 System Privilege -Entire collection object -C/A/D
21 Profile -To control system resources like memory, disk space, and CPU time.
22 Role -Collection of privileges.
23 Type of segment- Rollback, Temp, Data, Index
24 Snapshot-It's a read only table, to improve efficiency of query, which referred remote db, therefore reduce remote traffic.
25. Describe Oracle database's physical and logical structure?
Physical: Data files, Redo Log files, Control file.
Logical: Tables, Views, Table spaces, etc.
26.Can you increase the size of a table space? How?
Yes, by adding datafiles to it.
27.Can you increase the size of datafiles? How?
No (for Oracle 7.0)
Yes (for Oracle 7.3 by using the Resize clause ----- Confirm!!).
28.What is the use of Control files?
Contains pointers to locations of various data files, redo log files, etc.
29.What is the use of Data Dictionary?
Used by Oracle to store information about various physical and logical Oracle structures e.g.
Tables, Tablespaces, datafiles, etc
30.What are the advantages of clusters?
Access time reduced for joins.
31.What are the disadvantages of clusters?
The time for Insert increases.
32.Can Long/Long RAW be clustered?
No.
33.Can null keys be entered in cluster index, normal index?
Yes.
34.Can Check constraint be used for self referential integrity? How?
Yes. In the CHECK condition for a column of a table, we can reference some other
Column of the same table and thus enforce self referential integrity.
35.What are the min. extents allocated to a rollback extent?
Two
36.What are the states of a rollback segment?
The various states of a rollback segment are:
ONLINE, OFFLINE, PARTLY AVAILABLE, NEEDS RECOVERY and INVALID.
37.What is the difference between unique key and primary key?
Unique key can be null; Primary key cannot be null.
38.An insert statement followed by a create table statement followed by rollback? Will the rows be inserted?
No.
39.Can you define multiple savepoints?
Yes.
40.Can you Rollback to any savepoint?
Yes.
41.What is the maximum no? Of columns a table can have?
254.
42.How many rows will the following SQL return?
Select * from emp Where rownum < 10;
9 rows
43.How many rows will the following SQL return?
Select * from emp Where rownum = 10;
No rows
44.Which symbol precedes the path to the table in the remote database?
@
45.Are views automatically updated when base tables are updated?
Yes
46.Can a trigger written for a view?
No
47.A table has the following data: [5, Null, 10]. What will the average function return?
7.5
48.Is Sysdate a system variable or a system function?
System Function
49.Consider a sequence whose currval is 1 and gets incremented by 1 by using the nextval reference we get the next number 2. Suppose at this point we issue a rollback and again issue a nextval. What will the output be?
3
50.What is the result of the following SQL?
Select 1 from dual
UNION
Select 'A' from dual;
Error
51.Can database trigger written on synonym of a table and if it can be then what would be the effect if original table is accessed.
Yes, database trigger would fire.
52.Can you alter synonym of view or view?
No
53.Can you create index on view
No.
54.What is the difference between a view and a synonym?
Synonym is just a second name of table used for multiple link of database. View can be created with many tables, and with virtual columns and with conditions. But synonym can be on view.
55.What is the difference between alias and synonym?
Alias is temporary and used with one query. Synonym is permanent and not used as alias.
56.What is the effect of synonym and table name used in same Select statement?
Valid
57.What's the length of SQL integer?
32-bit length
58.What is the difference between foreign key and reference key?
Foreign key is the key i.e. attribute which refers to another table primary key.
Reference key is the primary key of table referred by another table.
59.Can dual table be deleted, dropped or altered or updated or inserted?
Yes
60.If content of dual is updated to some value computation takes place or not?
Yes
61.If any other table same as dual were created would it act similar to dual?
Yes
62.For which relational operators in where clause, index is not used?
<>, Like '%...' is NOT functions, field +constant, field || ''
63.Assume that there are multiple databases running on one machine. How can you switch from one to another?
Changing the ORACLE_SID
64.If you insert a row in a table, then create another table and then say Rollback. In this case will the row be inserted?
Yes. Because Create table is a DDL which commits automatically as soon as it is executed. The DDL commits the transaction even if the create statement fails internally (e.g. table already exists error) and not syntactically.
65.What are the various types of queries?
Normal Queries
Sub Queries
Co-related queries
Nested queries
Compound queries
66.Which of the following is not a schema object: Indexes, tables, public synonyms, triggers and packages?
Public synonyms
67.What is the difference between a view and a snapshot?
68 Varrays:
They allow us to associate a single identifier with an entire collection. This association let you manipulate the collection as a whole and reference individual elements easily.
69.Create database link <link name> Test_link
Connect to <user name > apps
Identified by <passwd> fnd
Using <connect string> fintst
70 How do you tell the difference between a regular order and a return order?
In so_headers you identify by order_category which will be RMA for
Returns and R for Regular
71 the difference between partly available and needs recovery?
