| Q: | What is SQL? | | A: | SQL stands for 'Structured Query Language'. | | |
|
| Q: | What is SELECT statement? | | A: | The SELECT statement lets you select a set of values from a table in a database. The values selected from the database table would depend on the various conditions that are specified in the SQL query. |
| Q: | How can you compare a part of the name rather than the entire name? | | A: | SELECT * FROM people WHERE empname LIKE '%ab%'
Would return a recordset with records consisting empname the sequence 'ab' in empname . | | | |
| Q: | What is the INSERT statement? | | A: | The INSERT statement lets you insert information into a database. |
| Q: | How do you delete a record from a database? | | A: | Use the DELETE statement to remove records or any particular column values from a database. |
| Q: | How could I get distinct entries from a table? | | A: | The SELECT statement in conjunction with DISTINCT lets you select a set of distinct values from a table in a database. The values selected from the database table would of course depend on the various conditions that are specified in the SQL query. Example
SELECT DISTINCT empname FROM emptable | | | |
| Q: | How to get the results of a Query sorted in any order? | | A: | You can sort the results and return the sorted results to your program by using ORDER BY keyword thus saving you the pain of carrying out the sorting yourself. The ORDER BY keyword is used for sorting.
SELECT empname, age, city FROM emptable ORDER BY empname |
| Q: | How can I find the total number of records in a table? | | A: | You could use the COUNT keyword , example
SELECT COUNT(*) FROM emp WHERE age>40 |
| Q: | What is GROUP BY? | | A: | The GROUP BY keywords have been added to SQL because aggregate functions (like SUM) return the aggregate of all column values every time they are called. Without the GROUP BY functionality, finding the sum for each individual group of column values was not possible. |
| Q: | What is the difference among "dropping a table", "truncating a table" and "deleting all records" from a table. | | A: | Dropping : (Table structure + Data are deleted), Invalidates the dependent objects ,Drops the indexes Truncating: (Data alone deleted), Performs an automatic commit, Faster than delete Delete : (Data alone deleted), Doesn’t perform automatic commit |
| Q: | What are the Large object types suported by Oracle? | | A: | Blob and Clob. |
|
|
| Q: | Difference between a "where" clause and a "having" clause. | | A: | Having clause is used only with group functions whereas Where is not used with. | | |
|
| Q: | What's the difference between a primary key and a unique key? | | A: | Both primary key and unique enforce uniqueness of the column on which they are defined. But by default primary key creates a clustered index on the column, where are unique creates a nonclustered index by default. Another major difference is that, primary key doesn't allow NULLs, but unique key allows one NULL only. | | | |
| Q: | What are cursors? Explain different types of cursors. What are the disadvantages of cursors? How can you avoid cursors? | | A: | Cursors allow row-by-row prcessing of the resultsets. Types of cursors: Static, Dynamic, Forward-only, Keyset-driven. See books online for more information. Disadvantages of cursors: Each time you fetch a row from the cursor, it results in a network roundtrip, where as a normal SELECT query makes only one rowundtrip, however large the resultset is. Cursors are also costly because they require more resources and temporary storage (results in more IO operations). Furthere, there are restrictions on the SELECT statements that can be used with some types of cursors. Most of the times, set based operations can be used instead of cursors. | | | |
| Q: | What are triggers? How to invoke a trigger on demand? | | A: | Triggers are special kind of stored procedures that get executed automatically when an INSERT, UPDATE or DELETE operation takes place on a table.
Triggers can't be invoked on demand. They get triggered only when an associated action (INSERT, UPDATE, DELETE) happens on the table on which they are defined. Triggers are generally used to implement business rules, auditing. Triggers can also be used to extend the referential integrity checks, but wherever possible, use constraints for this purpose, instead of triggers, as constraints are much faster. |
| Q: | What is a join and explain different types of joins. | | A: | Joins are used in queries to explain how different tables are related. Joins also let you select data from a table depending upon data from another table. Types of joins: INNER JOINs, OUTER JOINs, CROSS JOINs. OUTER JOINs are further classified as LEFT OUTER JOINS, RIGHT OUTER JOINS and FULL OUTER JOINS. | | | |
| Q: | What is a self join? | | A: | Self join is just like any other join, except that two instances of the same table will be joined in the query. | | | |
==================================================== 1.To see current user name
Sql> show user;
2. Change SQL prompt name
SQL> set sqlprompt “Manimara > “
Manimara >
Manimara >
3. Switch to DOS prompt
SQL> host
4. How do I eliminate the duplicate rows ?
SQL> delete from table_name where rowid not in (select max(rowid) from table group by duplicate_values_field_name);
or
SQL> delete duplicate_values_field_name dv from table_name ta where rowid <(select min(rowid) from table_name tb where ta.dv=tb.dv);
Example.
Table Emp
Empno Ename
101 Scott
102 Jiyo
103 Millor
104 Jiyo
105 Smith
delete ename from emp a where rowid < ( select min(rowid) from emp b where a.ename = b.ename);
The output like,
Empno Ename
101 Scott
102 Millor
103 Jiyo
104 Smith
5. How do I display row number with records?
To achive this use rownum pseudocolumn with query, like SQL> SQL> select rownum, ename from emp;
Output:
1 Scott
2 Millor
3 Jiyo
4 Smith
6.Display the records between two range
select rownum, empno, ename from emp where rowid in
(select rowid from emp where rownum <=&upto
minus
select rowid from emp where rownum<&Start);
Enter value for upto: 10
Enter value for Start: 7
ROWNUM EMPNO ENAME
--------- --------- ----------
1 7782 CLARK
2 7788 SCOTT
3 7839 KING
4 7844 TURNER
7. I know the nvl function only allows the same data type(ie. number or char or date Nvl(comm, 0)), if commission is null then the text “Not Applicable” want to display, instead of blank space. How do I write the query?
SQL> select nvl(to_char(comm.),'NA') from emp;
Output :
NVL(TO_CHAR(COMM),'NA')
-----------------------
NA
300
500
NA
1400
NA
NA
8. Oracle cursor : Implicit & Explicit cursors
Oracle uses work areas called private SQL areas to create SQL statements.
PL/SQL construct to identify each and every work are used, is called as Cursor.
For SQL queries returning a single row, PL/SQL declares all implicit cursors.
For queries that returning more than one row, the cursor needs to be explicitly declared.
9. Explicit Cursor attributes
There are four cursor attributes used in Oracle
cursor_name%Found, cursor_name%NOTFOUND, cursor_name%ROWCOUNT, cursor_name%ISOPEN
10. Implicit Cursor attributes
Same as explicit cursor but prefixed by the word SQL
SQL%Found, SQL%NOTFOUND, SQL%ROWCOUNT, SQL%ISOPEN
Tips : 1. Here SQL%ISOPEN is false, because oracle automatically closed the implicit cursor after executing SQL statements.
: 2. All are Boolean attributes.
11. Find out nth highest salary from emp table
SELECT DISTINCT (a.sal) FROM EMP A WHERE &N = (SELECT COUNT (DISTINCT (b.sal)) FROM EMP B WHERE a.sal<=b.sal);
Enter value for n: 2
SAL
---------
3700
12. To view installed Oracle version information
SQL> select banner from v$version;
13. Display the number value in Words
SQL> select sal, (to_char(to_date(sal,'j'), 'jsp'))
from emp;
the output like,
SAL (TO_CHAR(TO_DATE(SAL,'J'),'JSP'))
--------- -----------------------------------------------------
800 eight hundred
1600 one thousand six hundred
1250 one thousand two hundred fifty
If you want to add some text like,
Rs. Three Thousand only.
SQL> select sal "Salary ",
(' Rs. '|| (to_char(to_date(sal,'j'), 'Jsp'))|| ' only.'))
"Sal in Words" from emp
/
Salary Sal in Words
------- ------------------------------------------------------
800 Rs. Eight Hundred only.
1600 Rs. One Thousand Six Hundred only.
1250 Rs. One Thousand Two Hundred Fifty only.
14. Display Odd/ Even number of records
Odd number of records:
select * from emp where (rowid,1) in (select rowid, mod(rownum,2) from emp);
1
3
5
Even number of records:
select * from emp where (rowid,0) in (select rowid, mod(rownum,2) from emp)
2
4
6
15. Which date function returns number value?
months_between
16. Any three PL/SQL Exceptions?
Too_many_rows, No_Data_Found, Value_Error, Zero_Error, Others
17. What are PL/SQL Cursor Exceptions?
Cursor_Already_Open, Invalid_Cursor
18. Other way to replace query result null value with a text
SQL> Set NULL ‘N/A’
to reset SQL> Set NULL ‘’
19. What are the more common pseudo-columns?
SYSDATE, USER , UID, CURVAL, NEXTVAL, ROWID, ROWNUM
20. What is the output of SIGN function?
1 for positive value,
0 for Zero,
-1 for Negative value.
21. What is the maximum number of triggers, can apply to a single table?
12 triggers.
22. How would you determine the time zone under which a database was operating?
select DBTIMEZONE from dual;
23. Explain the use of setting GLOBAL_NAMES equal to TRUE.
Setting GLOBAL_NAMES dictates how you might connect to a database. This variable is either TRUE or FALSE and if it is set to TRUE it enforces database links to have the same name as the remote database to which they are linking.
23. What command would you use to encrypt a PL/SQL application?
WRAP
24. Explain the difference between a FUNCTION, PROCEDURE and PACKAGE.
A function and procedure are the same in that they are intended to be a collection of PL/SQL code that carries a single task. While a procedure does not have to return any values to the calling application, a function will return a single value. A package on the other hand is a collection of functions and procedures that are grouped together based on their commonality to a business function or application.
25. Explain the use of table functions.
Table functions are designed to return a set of rows through PL/SQL logic but are intended to be used as a normal table or view in a SQL statement. They are also used to pipeline information in an ETL process.
26. Name three advisory statistics you can collect.
Buffer Cache Advice, Segment Level Statistics, & Timed Statistics
27. Where in the Oracle directory tree structure are audit traces placed?
In unix $ORACLE_HOME/rdbms/audit, in Windows the event viewer
28. Explain materialized views and how they are used.
Materialized views are objects that are reduced sets of information that have been summarized, grouped, or aggregated from base tables. They are typically used in data warehouse or decision support systems.
29. When a user process fails, what background process cleans up after it?
PMON
30. What background process refreshes materialized views?
The Job Queue Processes.
31. How would you determine what sessions are connected and what resources they are waiting for?
Use of V$SESSION and V$SESSION_WAIT
32. Describe what redo logs are.
Redo logs are logical and physical structures that are designed to hold all the changes made to a database and are intended to aid in the recovery of a database.
33. How would you force a log switch?
ALTER SYSTEM SWITCH LOGFILE;
34. Give two methods you could use to determine what DDL changes have been made.
You could use Logminer or Streams
35. What does coalescing a tablespace do?
Coalescing is only valid for dictionary-managed tablespaces and de-fragments space by combining neighboring free extents into large single extents.
36. What is the difference between a TEMPORARY tablespace and a PERMANENT tablespace?
A temporary tablespace is used for temporary objects such as sort structures while permanent tablespaces are used to store those objects meant to be used as the true objects of the database.
37. Name a tablespace automatically created when you create a database.
The SYSTEM tablespace.
38. When creating a user, what permissions must you grant to allow them to connect to the database?
Grant the CONNECT to the user.
39. How do you add a data file to a tablespace?
ALTER TABLESPACE ADD DATAFILE SIZE
40. How do you resize a data file?
ALTER DATABASE DATAFILE RESIZE ;
41. What view would you use to look at the size of a data file?
DBA_DATA_FILES
42. What view would you use to determine free space in a tablespace?
DBA_FREE_SPACE
43. How would you determine who has added a row to a table?
Turn on fine grain auditing for the table.
44. How can you rebuild an index?
ALTER INDEX REBUILD;
45. Explain what partitioning is and what its benefit is.
Partitioning is a method of taking large tables and indexes and splitting them into smaller, more manageable pieces.
46. You have just compiled a PL/SQL package but got errors, how would you view the errors?
SHOW ERRORS
47. How can you gather statistics on a table?
The ANALYZE command.
48. How can you enable a trace for a session?
Use the DBMS_SESSION.SET_SQL_TRACE or
Use ALTER SESSION SET SQL_TRACE = TRUE;
49. What is the difference between the SQL*Loader and IMPORT utilities?
These two Oracle utilities are used for loading data into the database. The difference is that the import utility relies on the data being produced by another Oracle utility EXPORT while the SQL*Loader utility allows data to be loaded that has been produced by other utilities from different data sources just so long as it conforms to ASCII formattsed or delimited files.
50. Name two files used for network connection to a database.
TNSNAMES.ORA and SQLNET.ORA
Technical - UNIX
Every DBA should know something about the operating system that the database will be running on. The questions here are related to UNIX but you should equally be able to answer questions related to common Windows environments.
----------------------
How to find the version of each oracle product installed?
$ORACLE_HOME/orainst/inspdver utility provides a list of all the oracle products installed on the server with their verion numbers
-------------------------------------------------------------------------------
Identifying culprit rows when enabling of a constraint fails
When you need to upload huge amount of data, everybody says, that it is better to disable the constraints to give better performance. But, what if afterwards enabling of constraints fail due to bad data. You can find the culprit records by using EXCEPTIONS clause of ALTER TABLE statement. For ex.
ALTER TABLE test ENABLE CONSTRAINT pk_test exceptions into exceptions.
where exceptions table can be created by running $ORACLE_HOME/rdbms/admin/utlexcpt.sql script.
--------------------------------------------------------------------------------
Simpler way of finding plan and statistics of a query
Executing EXPLAIN PLAN and then selecting from plan_table is one way to get the execution plan of a sql statement. But for this you need syntax of both statements and patience to type all that stuff for each statement. Instead SQL*PLUS offers a nifty command to enable and disable the display of execution plan of each statement executed in that session. In addition to this it can display the statistics for each statement.
SET AUTOT[RACE] {OFF | ON | TRACE[ONLY]} [EXP[LAIN]] [STAT[ISTICS]]
thus SET AUTOTRACE ON will do the whole work for you
--------------------------------------------------------------------------------
How to trap errors from sqlplus in NT bat scripts
There is a variable ERRORLEVEL in NT. When you exits from sqlplus with a constant and check this variable in NT script, you can trap the error . For ex. IF ERRORLEVEL 1 PLUS33 will startup the sqlplus if youspecified EXIT 1 to get out from the sqlplus.
--------------------------------------------------------------------------------
How to find the datafile which contains a particular table?
If you query DBA_EXTENTS based on segment_name equal to your table name you will see that the column FILE_ID will show you the database file where each extent is located. You can then use FILE_ID to query DBA_DATA_FILES to find the name of the datafile. Or you could do it all in one query using a join.
--------------------------------------------------------------------------------
How to measure less than a second of time interval?
It is possible to measure time interval of upto 1/100th of a second inoracle. DBMS_UTILITY.GET_TIME function returns a number which increments every 100th of second. Keep in mind that this number can be negative as well. Thus it can only be used for measuring time intervals. Also in sys.v_$timer table the column hsecs contains a number which also incre,emts every 100th of a second. This number can also be negative
--------------------------------------------------------------------------------
How to use date index while using date ranges
It becomes tricky to use an index on date columns for date ranges beacause as soon as you use trunc(), oops! there oes you index. So here is a way to use your index on date column for date ranges...
where datefield >= to_date(to_char(date1,’DD-MON-YYYY’)||'00:00:00','DD-MON-YYYYHH24:MI:SS')
and datefield <= to_date(to_char(date2,’DD-MON-YYYY’)||'23:59:59','DD-MON-YYYYHH24:MI:SS')
--------------------------------------------------------------------------------
How to roll back sequences?
Use a negative increment and select from the sequence once, then reset the increment back to its correct setting. For example, this should reset the qcs_ranum sequence back 100:
alter sequence qcs_ranum increment by -100;
select qcs_ranum.nextval from dual;
alter sequence qcs_ranum increment by 1;
------------------
To Display last 5 records in a table
select * from (select rownum r, emp.* from emp) where r between (Select count(*)-5 from emp) and
(Select count(*) from emp)
-------------------------
To Display last record in a table
select * from (select rownum r, emp.* from emp) where r in (Select count(*) from emp)
-----------------------
To Display particular nth record in a table
select * from (select rownum r, emp.* from emp) where r in (2) or r=2
-----------------------
To Display even or odd records in a table
select * from (select emp.* , rownum r from emp) where mod (r,2)=0
|
|
