Sunday 19 August 2012

SQL and PL/SQL Interview questions



SQL and PL/SQL Interview questions
1. 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

2. Operators used in SELECT statements.
= 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

3. SELECT statements:
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.

4. 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

5. The INSERT INTO Statements:
INSERT INTO table_name VALUES (value1, value2,....)
INSERT INTO table_name (column1, column2,...) VALUES (value1, value2,....)

6. The Update Statement:
UPDATE table_name SET column_name = new_value WHERE column_name = some_value

7. The Delete Statements:
DELETE FROM table_name WHERE column_name = some_value
Delete All Rows:
DELETE FROM table_name or DELETE * FROM table_name

8. Sort the Rows:
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

9. 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,..)

10. BETWEEN ... AND
SELECT column_name FROM table_name WHERE column_name BETWEEN value1 AND value2 The values can be numbers, text, or dates.

11. What is the use of CASCADE CONSTRAINTS?
When this clause is used with the DROP command, a parent table can be dropped even when a child table exists.

12. Why does the following command give a compilation error?
DROP TABLE &TABLE_NAME; Variable names should start with an alphabet. Here the table name starts with an '&' symbol.
13. Which system tables contain information on privileges granted and privileges obtained?
USER_TAB_PRIVS_MADE, USER_TAB_PRIVS_RECD

14. Which system table contains information on constraints on all the tables created?obtained?
USER_CONSTRAINTS.

15. What is the difference between TRUNCATE and DELETE commands?
< TRUNCATE. with and DELETE used be can clause WHERE back. rolled cannot operation TRUNCATE but back, Hence command. DML a is whereas command DDL>
16. State true or false. !=, <>, ^= all denote the same operation?
True.

17. State true or false. EXISTS, SOME, ANY are operators in SQL?
True.


18. What will be the output of the following query?
SELECT REPLACE(TRANSLATE(LTRIM(RTRIM('!! ATHEN !!','!'), '!'), 'AN', '**'),'*','TROUBLE') FROM DUAL;?
19. What does the following query do?
SELECT SAL + NVL(COMM,0) FROM EMP;?
This displays the total salary of all employees. The null values in the commission column will be replaced by 0 and added to salary.

20. What is the advantage of specifying WITH GRANT OPTION in the GRANT command?
The privilege receiver can further grant the privileges he/she has obtained from the owner to any other user.

21. Which command executes the contents of a specified file?
START or @.

22. What is the value of comm and sal after executing the following query if the initial value of 'sal' is 10000
UPDATE EMP SET SAL = SAL + 1000, COMM = SAL*0.1;?
sal = 11000, comm = 1000.

23. Which command displays the SQL command in the SQL buffer, and then executes it?
RUN.

24. What command is used to get back the privileges offered by the GRANT command?
REVOKE.
25. What will be the output of the following query? SELECT DECODE(TRANSLATE('A','1234567890','1111111111'), '1','YES', 'NO' );? NO.
Explanation : The query checks whether a given string is a numerical digit.

26. Which date function is used to find the difference between two dates?
MONTHS_BETWEEN.

27. What operator performs pattern matching?
LIKE operator.

28. What is the use of the DROP option in the ALTER TABLE command?
It is used to drop constraints specified on the table.

29. What operator tests column for the absence of data?
IS NULL operator.

30. What are the privileges that can be granted on a table by a user to others?
Insert, update, delete, select, references, index, execute, alter, all.
31. Which function is used to find the largest integer less than or equal to a specific value?
FLOOR.

32. Which is the subset of SQL commands used to manipulate Oracle Database structures, including tables?
Data Definition Language (DDL).

33. What is the use of DESC in SQL?
DESC has two purposes. It is used to describe a schema as well as to retrieve rows from table in descending order.
Explanation :
The query SELECT * FROM EMP ORDER BY ENAME DESC will display the output sorted on ENAME in descending order.

34. What command is used to create a table by copying the structure of another table?
CREATE TABLE .. AS SELECT command
Explanation:
To copy only the structure, the WHERE clause of the SELECT command should contain a FALSE statement as in the following.
CREATE TABLE NEWTABLE AS SELECT * FROM EXISTINGTABLE WHERE 1=2;
If the WHERE condition is true, then all the rows or rows satisfying the condition will be copied to the new table.

35. TRUNCATE TABLE EMP;
DELETE FROM EMP;
Will the outputs of the above two commands differ?
Both will result in deleting all the rows in the table EMP..
36. What is the output of the following query SELECT TRUNC(1234.5678,-2) FROM DUAL;?
1200.

37. What are the wildcards used for pattern matching.?
_ for single character substitution and % for multi-character substitution.

38. What is the parameter substitution symbol used with INSERT INTO command?
&
39. What is the sub-query?
Sub-query is a query whose return values are used in filtering conditions of the main query.

40. What is correlated sub-query?
Correlated sub-query is a sub-query, which has reference to the main query.

41. Explain CONNECT BY PRIOR?
Retrieves rows in hierarchical order eg.
select empno, ename from emp where.

42. Difference between SUBSTR and INSTR?
INSTR (String1, String2 (n, (m)),
INSTR returns the position of the m-th 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 n-th position of string1.

43. Explain UNION, MINUS, UNION ALL and 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.

44. What is ROWID?
ROWID is a pseudo column attached to each row of a table. It is 18 characters long, blockno, rownumber are the components of ROWID.

45. What is the fastest way of accessing a row in a table?
Using ROWID.
CONSTRAINTS
46. What is an integrity constraint?
Integrity constraint is a rule that restricts values to a column in a table.

47. What is referential integrity constraint?
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.

48. What is 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.

49. 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.

50. What are the data types allowed in a table?
CHAR, VARCHAR2, NUMBER, DATE, RAW, LONG and LONG RAW.
54. What is difference between CHAR and VARCHAR2? What is the maximum SIZE allowed for each type?
CHAR pads blank spaces to the maximum length.
VARCHAR2 does not pad blank spaces.
For CHAR the maximum length is 255 and 2000 for VARCHAR2.

55. 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 column is allowed. It is not possible to use LONG column in WHERE or ORDER BY clause.

56. What are the pre-requisites to modify datatype of a column and to add a column with NOT NULL constraint?
- To modify the datatype of a column the column must be empty.
- To add a column with NOT NULL constrain, the table must be empty.

57. Where the integrity constraints are stored in data dictionary?
The integrity constraints are stored in USER_CONSTRAINTS.

58. How will you activate/deactivate integrity constraints?
The integrity constraints can be enabled or disabled by ALTER TABLE ENABLE CONSTRAINT / DISABLE CONSTRAINT.
59. If 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.

60. What is a database link?
Database link is a named path through which a remote database can be accessed.

60. How to access the current value and next value from a sequence? Is it possible to access the current value in a session before accessing next value?
Sequence name CURRVAL, sequence name NEXTVAL. It is not possible. Only if you access next value in the session, current value can be accessed.

60.What is CYCLE/NO CYCLE in a Sequence?
CYCLE specifies that the sequence continue 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.

61. What are the advantages of VIEW?
- To protect some of the columns of a table from other users.
- To hide complexity of a query.
- To hide complexity of calculations.

62. 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.

63. 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 and these tables are the base tables of a view, then the changes will be reference on the view.
64. Which of the following statements is true about implicit cursors?
1. Implicit cursors are used for SQL statements that are not named.
2. Developers should use implicit cursors with great care.
3. Implicit cursors are used in cursor for loops to handle data processing.
4. Implicit cursors are no longer a feature in Oracle.

65. Which of the following is not a feature of a cursor FOR loop?
1. Record type declaration.
2. Opening and parsing of SQL statements.
3. Fetches records from cursor.
4. Requires exit condition to be defined.

66. A developer would like to use referential datatype declaration on a variable. The variable name is EMPLOYEE_LASTNAME, and the corresponding table and column is EMPLOYEE, and LNAME, respectively. How would the developer define this variable using referential datatypes?
1. Use employee.lname%type.
2. Use employee.lname%rowtype.
3. Look up datatype for EMPLOYEE column on LASTNAME table and use that.
4. Declare it to be type LONG.

67. Which three of the following are implicit cursor attributes?
1. %found
2. %too_many_rows
3. %notfound
4. %rowcount
5. %rowtype

68. If left out, which of the following would cause an infinite loop to occur in a simple loop?
1. LOOP
2. END LOOP
3. IF-THEN
4. EXIT
69. Which line in the following statement will produce an error?
1. cursor action_cursor is
2. select name, rate, action
3. into action_record
4. from action_table;
5. There are no errors in this statement.

70. The command used to open a CURSOR FOR loop is
1. open
2. fetch
3. parse
4. None, cursor for loops handle cursor opening implicitly.

71. What happens when rows are found using a FETCH statement
1. It causes the cursor to close
2. It causes the cursor to open
3. It loads the current row values into variables
4. It creates the variables to hold the current row values

72. Read the following code:10. CREATE OR REPLACE PROCEDURE find_cpt
11. (v_movie_id {Argument Mode} NUMBER, v_cost_per_ticket {argument mode} NUMBER)
12. IS
13. BEGIN
14. IF v_cost_per_ticket > 8.5 THEN
15. SELECT cost_per_ticket
16. INTO v_cost_per_ticket
17. FROM gross_receipt
18. WHERE movie_id = v_movie_id;
19. END IF;
20. END;
Which mode should be used for V_COST_PER_TICKET?
1. IN
2. OUT
3. RETURN
4. IN OUT
73. Read the following code:22. CREATE OR REPLACE TRIGGER update_show_gross
23. {trigger information}
24. BEGIN
25. {additional code}
26. END;

The trigger code should only execute when the column, COST_PER_TICKET, is greater than $3. Which trigger information will you add?

1. WHEN (new.cost_per_ticket > 3.75)
2. WHEN (:new.cost_per_ticket > 3.75
3. WHERE (new.cost_per_ticket > 3.75)
4. WHERE (:new.cost_per_ticket > 3.75)

74. What is the maximum number of handlers processed before the PL/SQL block is exited when an exception occurs?
1. Only one
2. All that apply
3. All referenced
4. None

77. For which trigger timing can you reference the NEW and OLD qualifiers?
1. Statement and Row 2. Statement only 3. Row only 4. Oracle Forms trigger

78. Read the following code:CREATE OR REPLACE FUNCTION get_budget(v_studio_id IN NUMBER)
RETURN number IS

v_yearly_budget NUMBER;

BEGIN
SELECT yearly_budget
INTO v_yearly_budget
FROM studio
WHERE id = v_studio_id;

RETURN v_yearly_budget;
END;
Which set of statements will successfully invoke this function within SQL*Plus?
1. VARIABLE g_yearly_budget NUMBER
EXECUTE g_yearly_budget := GET_BUDGET(11);
2. VARIABLE g_yearly_budget NUMBER
EXECUTE :g_yearly_budget := GET_BUDGET(11);
3. VARIABLE :g_yearly_budget NUMBER
EXECUTE :g_yearly_budget := GET_BUDGET(11);
4. VARIABLE g_yearly_budget NUMBER

31. CREATE OR REPLACE PROCEDURE update_theater
32. (v_name IN VARCHAR v_theater_id IN NUMBER) IS
33. BEGIN
34. UPDATE theater
35. SET name = v_name
36. WHERE id = v_theater_id;
37. END update_theater;
79. When invoking this procedure, you encounter the error: ORA-000: Unique constraint(SCOTT.THEATER_NAME_UK) violated.
How should you modify the function to handle this error?
1. An user defined exception must be declared and associated with the error code and handled in the EXCEPTION section.
2. Handle the error in EXCEPTION section by referencing the error code directly.
3. Handle the error in the EXCEPTION section by referencing the UNIQUE_ERROR predefined exception.
4. Check for success by checking the value of SQL%FOUND immediately after the UPDATE statement.

80. Read the following code: 40. CREATE OR REPLACE PROCEDURE calculate_budget IS
41. v_budget studio.yearly_budget%TYPE;
42. BEGIN
43. v_budget := get_budget(11);
44. IF v_budget < 30000
45. THEN
46. set_budget(11,30000000);
47. END IF;
48. END;

You are about to add an argument to CALCULATE_BUDGET. What effect will this have?
1. The GET_BUDGET function will be marked invalid and must be recompiled before the next execution.
2. The SET_BUDGET function will be marked invalid and must be recompiled before the next execution.
3. Only the CALCULATE_BUDGET procedure needs to be recompiled.
4. All three procedures are marked invalid and must be recompiled.

81. Which procedure can be used to create a customized error message? 1. RAISE_ERROR
2. SQLERRM
3. RAISE_APPLICATION_ERROR
4. RAISE_SERVER_ERROR

82. The CHECK_THEATER trigger of the THEATER table has been disabled. Which command can you issue to enable this trigger? 1. ALTER TRIGGER check_theater ENABLE;
2. ENABLE TRIGGER check_theater;
3. ALTER TABLE check_theater ENABLE check_theater;
4. ENABLE check_theater;

83. Examine this database trigger 52. CREATE OR REPLACE TRIGGER prevent_gross_modification
53. {additional trigger information}
54. BEGIN
55. IF TO_CHAR(sysdate, DY) = MON
56. THEN
57. RAISE_APPLICATION_ERROR(-20000,Gross receipts cannot be deleted on Monday);
58. END IF;
59. END;

This trigger must fire before each DELETE of the GROSS_RECEIPT table. It should fire only once for the entire DELETE statement. What additional information must you add?
1. BEFORE DELETE ON gross_receipt
2. AFTER DELETE ON gross_receipt
3. BEFORE (gross_receipt DELETE)
4. FOR EACH ROW DELETED FROM gross_receipt 
60.What is CYCLE/NO CYCLE in a Sequence?

CYCLE specifies that the sequence continue 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.



61. What are the advantages of VIEW?

- To protect some of the columns of a table from other users.
- To hide complexity of a query.
- To hide complexity of calculations.



62. 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.



63. 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 and these tables are the base tables of a view, then the changes will be reference on the view.


64. Which of the following statements is true about implicit cursors?

1. Implicit cursors are used for SQL statements that are not named.
2. Developers should use implicit cursors with great care.
3. Implicit cursors are used in cursor for loops to handle data processing.
4. Implicit cursors are no longer a feature in Oracle.



65. Which of the following is not a feature of a cursor FOR loop?

1. Record type declaration.
2. Opening and parsing of SQL statements.
3. Fetches records from cursor.
4. Requires exit condition to be defined.



66. A developer would like to use referential datatype declaration on a variable. The variable name is EMPLOYEE_LASTNAME, and the corresponding table and column is EMPLOYEE, and LNAME, respectively. How would the developer define this variable using referential datatypes?

1. Use employee.lname%type.
2. Use employee.lname%rowtype.
3. Look up datatype for EMPLOYEE column on LASTNAME table and use that.
4. Declare it to be type LONG.



67. Which three of the following are implicit cursor attributes?

1. %found
2. %too_many_rows
3. %notfound
4. %rowcount
5. %rowtype



68. If left out, which of the following would cause an infinite loop to occur in a simple loop?

1. LOOP
2. END LOOP
3. IF-THEN
4. EXIT




69. Which line in the following statement will produce an error?

1. cursor action_cursor is
2. select name, rate, action
3. into action_record
4. from action_table;
5. There are no errors in this statement.



70. The command used to open a CURSOR FOR loop is

1. open
2. fetch
3. parse
4. None, cursor for loops handle cursor opening implicitly.



71. What happens when rows are found using a FETCH statement

1. It causes the cursor to close
2. It causes the cursor to open
3. It loads the current row values into variables
4. It creates the variables to hold the current row values



72. Read the following code:
10. CREATE OR REPLACE PROCEDURE find_cpt
11. (v_movie_id {Argument Mode} NUMBER, v_cost_per_ticket {argument mode} NUMBER)
12. IS
13. BEGIN
14. IF v_cost_per_ticket > 8.5 THEN
15. SELECT cost_per_ticket
16. INTO v_cost_per_ticket
17. FROM gross_receipt
18. WHERE movie_id = v_movie_id;
19. END IF;
20. END;
Which mode should be used for V_COST_PER_TICKET?
1. IN
2. OUT
3. RETURN
4. IN OUT




73. Read the following code:
22. CREATE OR REPLACE TRIGGER update_show_gross
23. {trigger information}
24. BEGIN
25. {additional code}
26. END;

The trigger code should only execute when the column, COST_PER_TICKET, is greater than $3. Which trigger information will you add?

1. WHEN (new.cost_per_ticket > 3.75)
2. WHEN (:new.cost_per_ticket > 3.75
3. WHERE (new.cost_per_ticket > 3.75)
4. WHERE (:new.cost_per_ticket > 3.75)




74. What is the maximum number of handlers processed before the PL/SQL block is exited when an exception occurs?

1. Only one
2. All that apply
3. All referenced
4. None



77. For which trigger timing can you reference the NEW and OLD qualifiers?

1. Statement and Row 2. Statement only 3. Row only 4. Oracle Forms trigger


78. Read the following code:
CREATE OR REPLACE FUNCTION get_budget(v_studio_id IN NUMBER)
RETURN number IS

v_yearly_budget NUMBER;

BEGIN
SELECT yearly_budget
INTO v_yearly_budget
FROM studio
WHERE id = v_studio_id;

RETURN v_yearly_budget;
END;
Which set of statements will successfully invoke this function within SQL*Plus?
1. VARIABLE g_yearly_budget NUMBER
EXECUTE g_yearly_budget := GET_BUDGET(11);
2. VARIABLE g_yearly_budget NUMBER
EXECUTE :g_yearly_budget := GET_BUDGET(11);
3. VARIABLE :g_yearly_budget NUMBER
EXECUTE :g_yearly_budget := GET_BUDGET(11);
4. VARIABLE g_yearly_budget NUMBER

31. CREATE OR REPLACE PROCEDURE update_theater
32. (v_name IN VARCHAR v_theater_id IN NUMBER) IS
33. BEGIN
34. UPDATE theater
35. SET name = v_name
36. WHERE id = v_theater_id;
37. END update_theater;


79. When invoking this procedure, you encounter the error:
ORA-000: Unique constraint(SCOTT.THEATER_NAME_UK) violated.
How should you modify the function to handle this error?
1. An user defined exception must be declared and associated with the error code and handled in the EXCEPTION section.
2. Handle the error in EXCEPTION section by referencing the error code directly.
3. Handle the error in the EXCEPTION section by referencing the UNIQUE_ERROR predefined exception.
4. Check for success by checking the value of SQL%FOUND immediately after the UPDATE statement.



80. Read the following code:
40. CREATE OR REPLACE PROCEDURE calculate_budget IS
41. v_budget studio.yearly_budget%TYPE;
42. BEGIN
43. v_budget := get_budget(11);
44. IF v_budget <>
45. THEN
46. set_budget(11,30000000);
47. END IF;
48. END;

You are about to add an argument to CALCULATE_BUDGET. What effect will this have?
1. The GET_BUDGET function will be marked invalid and must be recompiled before the next execution.
2. The SET_BUDGET function will be marked invalid and must be recompiled before the next execution.
3. Only the CALCULATE_BUDGET procedure needs to be recompiled.
4. All three procedures are marked invalid and must be recompiled.



Posted by at 1 comment:

Pl Sql Questions

http://www.techinterviews.com/interview-questions/unixlinux


 

SQL AND PL/SQL



Read the following code:
Read the following code:
22. CREATE OR REPLACE TRIGGER update_show_gross
23. {trigger information}
24. BEGIN
25. {additional code}
26. END;

The trigger code should only execute when the column, COST_PER_TICKET, is greater than $3. Which trigger information will you add?

1. WHEN (new.cost_per_ticket > 3.75)
2. WHEN (:new.cost_per_ticket > 3.75
3. WHERE (new.cost_per_ticket > 3.75)
4. WHERE (:new.cost_per_ticket > 3.75)





If want more things for interview questions followed by below link:
http://dev.fyicenter.com/Interview-Questions/SQL/_Read_the_following_code__109.html 
Posted by at No comments:

Unix interview and xml queries

Linux application programming questions

  1. Explain the difference between a static library and a dynamic library? - Static library is linked into the executable, while a dynamic library (or shared object) is loaded while the executable has started.
  2. How do you create a static library? - If you have a collection of object (.o) files, you can do it by running ar command. Generally a static library has a .a extension, and you can link it into an executable by providing -l libraryname to gcc.
  3. Where should the developed libraries be installed on the system? - GNU recommends /usr/local/bin for binaries and /usr/local/lib for libraries.
  4. What’s LD_LIBRARY_PATH? - It’s an environment variable that lists all the directories which should be searches for libraries before the standard directories are searched.
  5. How do you create a shared library? - Create the object file with -fPIC for position-independent code, then run gcc with -shared option.
  6. How do you install a shared library? - Run ldconfig in the standard directory that it’s installed in.
  7. What does ldd do? - It shows a list of installed shared libraries.
  8. How do you dynamically load a library in your app? - Use dlopen()
  9. What does nm command do? - It reports the list of symbols in a given library.

Basic shell scripting questions

  1. How do you find out what’s your shell? - echo $SHELL
  2. What’s the command to find out today’s date? - date
  3. What’s the command to find out users on the system? - who
  4. How do you find out the current directory you’re in? - pwd
  5. How do you remove a file? - rm
  6. How do you remove a - rm -rf
  7. How do you find out your own username? - whoami
  8. How do you send a mail message to somebody? - mail somebody@techinterviews.com -s ‘Your subject’ -c ‘cc@techinterviews.com‘
  9. How do you count words, lines and characters in a file? - wc
  10. How do you search for a string inside a given file? - grep string filename
  11. How do you search for a string inside a directory? - grep string *
  12. How do you search for a string in a directory with the subdirectories recursed? - grep -r string *
  13. What are PIDs? - They are process IDs given to processes. A PID can vary from 0 to 65535.
  14. How do you list currently running process? - ps
  15. How do you stop a process? - kill pid
  16. How do you find out about all running processes? - ps -ag
  17. How do you stop all the processes, except the shell window? - kill 0
  18. How do you fire a process in the background? - ./process-name &
  19. How do you refer to the arguments passed to a shell script? - $1, $2 and so on. $0 is your script name.
  20. What’s the conditional statement in shell scripting? - if {condition} then … fi
  21. How do you do number comparison in shell scripts? - -eq, -ne, -lt, -le, -gt, -ge
  22. How do you test for file properties in shell scripts? - -s filename tells you if the file is not empty, -f filename tells you whether the argument is a file, and not a directory, -d filename tests if the argument is a directory, and not a file, -w filename tests for writeability, -r filename tests for readability, -x filename tests for executability
  23. How do you do Boolean logic operators in shell scripting? - ! tests for logical not, -a tests for logical and, and -o tests for logical or.
  24. How do you find out the number of arguments passed to the shell script? - $#
  25. What’s a way to do multilevel if-else’s in shell scripting? - if {condition} then {statement} elif {condition} {statement} fi
  26. How do you write a for loop in shell? - for {variable name} in {list} do {statement} done
  27. How do you write a while loop in shell? - while {condition} do {statement} done
  28. How does a case statement look in shell scripts? - case {variable} in {possible-value-1}) {statement};; {possible-value-2}) {statement};; esac
  29. How do you read keyboard input in shell scripts? - read {variable-name}
  30. How do you define a function in a shell script? - function-name() { #some code here return }
  31. How does getopts command work? - The parameters to your script can be passed as -n 15 -x 20. Inside the script, you can iterate through the getopts array as while getopts n:x option, and the variable $option contains the value of the entered option.

General UNIX interview questions
  1. What are the main differences between Apache 1.x and 2.x?
  2. What does the “route” command do?
  3. What are the read/write/execute bits on a directory mean?
  4. What does iostat do?

  5. what does vmstat do?
  6. What does netstat do?
  7. What is the most graceful way to bring a system into single user mode?
  8. How do you determine disk usage?
  9. What is AWK?
  10. What is SED?
  11. What is the difference between binaries in /bin, and /usr/bin?
  12. What is a dynamically linked file?
  13. What is a statically linked file?

Oracle on Linux/Unix questions

How many memory layers are in the shared pool?
  1. How do you find out from the RMAN catalog if a particular archive log has been backed-up?
  2. How can you tell how much space is left on a given file system and how much space each of the file system’s subdirectories take-up?
  3. Define the SGA and how you would configure SGA for a mid-sized OLTP environment? What is involved in tuning the SGA?
  4. What is the cache hit ratio, what impact does it have on performance of an Oracle database and what is involved in tuning it?
  5. Other than making use of the statspack utility, what would you check when you are monitoring or running a health check on an Oracle 8i or 9i database?
  6. How do you tell what your machine name is and what is its IP address?

  7. How would you go about verifying the network name that the local_listener is currently using?
  8. You have 4 instances running on the same UNIX box. How can you determine which shared memory and semaphores are associated with which instance?
  9. What view(s) do you use to associate a user’s SQLPLUS session with his o/s process?
  10. What is the recommended interval at which to run statspack snapshots, and why?
  11. What spfile/init.ora file parameter exists to force the CBO to make the execution path of a given statement use an index, even if the index scan may appear to be calculated as more costly?
  12. Assuming today is Monday, how would you use the DBMS_JOB package to schedule the execution of a given procedure owned by SCOTT to start Wednesday at 9AM and to run subsequently every other day at 2AM.
  13. How would you edit your CRONTAB to schedule the running of /test/test.sh to run every other day at 2PM?
  14. What do the 9i dbms_standard.sql_txt() and dbms_standard.sql_text() procedures do?
  15. In which dictionary table or view would you look to determine at which time a snapshot or MVIEW last successfully refreshed?
  16. How would you best determine why your MVIEW couldn’t FAST REFRESH?
  17. How does propagation differ between Advanced Replication and Snapshot Replication (read-only)?
  18. Which dictionary view(s) would you first look at to understand or get a high-level idea of a given Advanced Replication environment?
  19. How would you begin to troubleshoot an ORA-3113 error?
  20. Which dictionary tables and/or views would you look at to diagnose a locking issue?
  21. An automatic job running via DBMS_JOB has failed. Knowing only that “it’s failed”, how do you approach troubleshooting this issue?
  22. How would you extract DDL of a table without using a GUI tool?
  23. You’re getting high “busy buffer waits” - how can you find what’s causing it?
  24. What query tells you how much space a tablespace named “test” is taking up, and how much space is remaining?
  25. Database is hung. Old and new user connections alike hang on impact. What do you do? Your SYS SQLPLUS session IS able to connect.
  26. Database crashes. Corruption is found scattered among the file system neither of your doing nor of Oracle’s. What database recovery options are available? Database is in archive log mode.
  27. Illustrate how to determine the amount of physical CPUs a Unix Box possesses (LINUX and/or Solaris).
  28. How do you increase the OS limitation for open files (LINUX and/or Solaris)?
  29. Provide an example of a shell script which logs into SQLPLUS as SYS, determines the current date, changes the date format to include minutes & seconds, issues a drop table command, displays the date again, and finally exits.
  30. Explain how you would restore a database using RMAN to Point in Time?
  31. How does Oracle guarantee data integrity of data changes?
  32. Which environment variables are absolutely critical in order to run the OUI?
  33. What SQL query from v$session can you run to show how many sessions are logged in as a particular user account?
  34. Why does Oracle not permit the use of PCTUSED with indexes?
  35. What would you use to improve performance on an insert statement that places millions of rows into that table?
  36. What would you do with an “in-doubt” distributed transaction?
  37. What are the commands you’d issue to show the explain plan for “select * from dual”?
  38. In what script is “snap$” created? In what script is the “scott/tiger” schema created?
  39. If you’re unsure in which script a sys or system-owned object is created, but you know it’s in a script from a specific directory, what UNIX command from that directory structure can you run to find your answer?
  40. How would you configure your networking files to connect to a database by the name of DSS which resides in domain icallinc.com?
  41. You create a private database link and upon connection, fails with: ORA-2085: connects to . What is the problem? How would you go about resolving this error?
  42. I have my backup RMAN script called “backup_rman.sh”. I am on the target database. My catalog username/password is rman/rman. My catalog db is called rman. How would you run this shell script from the O/S such that it would run as a background process?
  43. Explain the concept of the DUAL table.
  44. What are the ways tablespaces can be managed and how do they differ?
  45. From the database level, how can you tell under which time zone a database is operating?
  46. What’s the benefit of “dbms_stats” over “analyze”?
  47. Typically, where is the conventional directory structure chosen for Oracle binaries to reside?
  48. You have found corruption in a tablespace that contains static tables that are part of a database that is in NOARCHIVE log mode. How would you restore the tablespace without losing new data in the other tablespaces?
  49. How do you recover a datafile that has not been physically been backed up since its creation and has been deleted. Provide syntax example.


SQL/XML Queries

This chapter describes the syntax of Connect for SQL/XML queries. The following conventions are used to document the query syntax:
  • Bold type indicates keywords and tokens that must be entered as part of the query.
  • Italic type indicates variables.
  • Square brackets [ ] surround optional items.
  • A vertical rule | indicates an OR separator to delineate items.
  • Curly brackets { } surround items that can repeat zero or more times.
The following SQL/XML query syntax shows the high-level components of a Connect for SQL/XML query. 
sqlxml_query ::= select 

SQL_value_expression | XML_value_expression 

{, SQL_value_expression | XML_value_expression} 

rest_of_sql_select
The following sections define each of the high-level components of a Connect for SQL/XML query:
High-level Component
See...
SQL_value_expression
"SQL_value_expression"
XML_value_expression
"XML_value_expression"
rest_of_sql_select
"rest_of_sql99_select"

SQL_value_expression

SQL_value_expression is all level 1 alternatives for a value expression, including the optional AS clause, as defined in the document ISO/IEC 9075-2.

XML_value_expression

The syntax for XML_value_expression is: 
XML_value_expression ::= value_expression_primary | 

XML_value_function
where:
value_expression_primary is a primitive value expression, such as a column reference, a variable reference, a parameter reference, and a literal. The complete definition can be found in the document ISO/IEC 9075-2. Also, value_expression_primary can be the SQL/XML operator XMLAGG. This operator is described in the following section.
XML_value_function is one of the following SQL/XML operators: XMLCONCAT, XMLELEMENT, or XMLFOREST. These operators are described next.

XMLAGG

XMLAGG produces a forest of XML elements from a collection of XML elements. XMLAGG accepts a single XML value expression as its argument, and produces a forest of elements by collecting the XML values that are returned from multiple rows and concatenating the values to make one value. XMLAGG concatenates the values returned from one column of multiple rows, unlike XMLCONCAT, which concatenates the values returned from multiple columns in the same row.
If a returned value is NULL, it is ignored in the result. If all the returned values are NULL, the result is NULL.
Subqueries in SQL/XML are allowed to return only one row; therefore, to return more than one row of values in a SQL/XML subquery, you must use XMLAGG.
Syntax 
XMLAGG ::= XMLAGG (XML_value_expression [ORDER BY sort_list] )
where:
See "XML_value_expression" for a definition of XML_value_expression.
sort_list is all alternatives for a sort list as defined in the document ISO/IEC 9075-2.
Example 1
The following example produces a list of all employees in the company by producing an element for each employee, ordering the list by the last name, and concatenating the result. The result set will have one column and one row. 
SELECT 

 XMLAGG (XMLELEMENT (Name "emp", e.LastName) 

   ORDER BY e.LastName) AS "Employee List"  

FROM Employees e
Result Set:
Employee List 
<emp>Allen</emp><emp>Ayers</emp><emp>Cover</emp> 

... 

<emp>Westbrook</emp><emp>Williams</emp>
Example 2
The following example demonstrates using XMLAGG in a subquery. 
SELECT 

  XMLELEMENT(NAME "emp", e.LastName, 

    XMLELEMENT(Name "projects", 

      (SELECT 

        XMLAGG(XMLELEMENT(NAME "project, p.ProjID)) 

      FROM Projects p 

      WHERE EXISTS ( 

        SELECT * FROM Assignments a 

          WHERE a.EmpId=e.EmpId and a.ProjId=p.ProjId)))) 

          AS "Employees" 

FROM Employees e
Result Set:
Employees 
<emp>Ayers 

  <projects> 

    <project>3</project> 

    <project>6</project> 

</emp> 


... 


<emp>Marshall 

  <projects> 

    <project>1</project> 

    <project>5</project> 

</emp> 


...
Example 3
The following example uses a Group By clause to group all tasks assigned to each employee and employee project. 
SELECT 

  XMLELEMENT (NAME "Employee_Tasks_by_Project", 

    XMLATTRIBUTES (a.EmpId, a.ProjId AS "Project"), 

    XMLAGG (XMLELEMENT (NAME "Task", a.Task) ) )

  AS "Employee Task List"  

FROM Assignments a GROUP BY a.EmpId, a.ProjId
Result Set:
Employee Task List 
<Employee_Tasks_by_Project EmpId="1" Project="1" 

  <Task>Analysis</Task> 

  <Task>Documentation</Task> 

  <Task>Planning</Task> 

  <Task>Testing</Task> 

</Employee_Tasks_by_Project> 

<Employee_Tasks_by_Project EmpId="1" Project="5" 

  <Task>Analysis</Task> 

  <Task>Documentation</Task> 

</Employee_Tasks_by_Project> 

<Employee_Tasks_by_Project EmpId="2" Project="3" 

  <Task>Analysis</Task> 

  <Task>Development</Task> 

</Employee_Tasks_by_Project>

XMLCONCAT

XMLCONCAT produces a forest of elements by concatenating a list of XML values. XMLCONCAT accepts a list of XML value expressions as its arguments, and produces a forest of elements by concatenating the XML values that are returned from the same row to make one value. XMLCONCAT performs a similar operation as XMLFOREST, except that XMLCONCAT expressions must evaluate to type XML.
If a returned value is NULL, it is ignored in the result. If all the returned values are NULL, the result is NULL.
Syntax 
XMLCONCAT ::= XMLCONCAT (XML_value_expression {, 

XML_value_expression} )
See "XML_value_expression" for a definition of XML_value_expression.
Example 1
The following example produces an XML element for the first and last names, concatenates the result, and creates a one-column result set: 
SELECT 

  XMLCONCAT ( 

    XMLELEMENT (NAME "first", e.FirstName),  

    XMLELEMENT (NAME "last", e.LastName)) AS "Result" 

FROM Employees e
Result Set:
Result 
<FirstName>Marc</FirstName><LastName>Marshall</LastName> 


... 


<FirstName>Richard</FirstName><LastName>Gamble</LastName>
Example 2
The following example produces an XML element for the first and last names, concatenates the result, and creates a two-column result set: 
SELECT e.EmpId, 

  XMLCONCAT ( 

    XMLELEMENT (NAME "first", e.FirstName),  

    XMLELEMENT (NAME "last", e.LastName)) AS "Result" 

FROM Employees e
Result Set:
EmpId
Result 
1 


<FirstName>Marc</FirstName><LastName>Marshall</LastName> 


  


... 


20 


<FirstName>Richard</FirstName><LastName>Gamble</LastName>

XMLELEMENT

XMLELEMENT is an operator that returns an XML element given an XML element name, an optional list of attributes, and an optional list of values as the content of the new element.
Syntax 
XMLELEMENT ::= XMLELEMENT (NAME xml_element_name 

[XMLATTRIBUTES (value_expression [AS attribute_name] [{, 

value_expression [AS attribute_name]}] )

[{, SQL_value_expression | XML_value_expression}])
where:
xml_element_name is any valid XML element name. This value must be within double quotes.
XMLATTRIBUTES creates a list of XML attributes for the enclosing XML element.
attribute_name is any valid XML attribute name. This value must be within double quotes.
value_expression is all level 1 alternatives for a value expression as defined in the document ISO/IEC 9075-2.
See "SQL_value_expression" for a definition of SQL_value_expression.
See "XML_value_expression" for a definition of XML_value_expression.
Example 1
The following example creates the Employee element with a subelement named name. All values in the LastName column of the Employees table are returned. 
SELECT 

  XMLELEMENT(NAME "Employee", 

    XMLELEMENT(NAME "name", e.LastName)) AS "Employee List" 

FROM Employees e
Result Set:
Employee List 
<Employee> 

  <name>Marshall></name> 

</Employee> 


<Employee> 

  <name>Ayers</name> 

</Employee> 


<Employee> 

  <name>Simpson</name> 

</Employee> 


...
Example 2
The following example demonstrates defining an XML attribute within an XML element. 
SELECT 

      XMLELEMENT(NAME "Employee", 

      XMLATTRIBUTES(e.EmpId, e.LastName as "name")) 

FROM Employees e
Result Set:
Result 
<Employee EmpId="1" name="Marshall"/> 


<Employee EmpId="2" name="Ayers"/> 


... 


<Employee EmpId="20" name="Gamble"/>

XMLFOREST

XMLFOREST produces a forest of XML elements from a given list of arguments. XMLFOREST accepts a list of SQL value expressions as its arguments, and produces an XML element from each value returned. XMLFOREST uses the name of the column as the name of the XML element, unless otherwise specified in an optional AS clause, and uses the value of the SQL value expression as the content of the XML element.
If a value expression evaluates to NULL, then no element is created for that expression. This is unlike XMLELEMENT, where an empty element is created if a value expression evaluates to NULL.
Syntax 
XMLFOREST ::= XMLFOREST 

(value_expression [AS forest_element_name] 

[{, value_expression [AS forest_element_name]}] )
where:
value_expression is all level 1 alternatives for a value expression as defined in the document ISO/IEC 9075-2.
forest_element_name is any valid SQL identifier as defined in the document ISO/IEC 9075-2.
Example
The following example produces four elements (EmpID, FirstName, LastName, and Start) from the value expressions e.EmpID, e.FirstName, e.LastName, and e.StartDate, concatenates the elements produced for each employee, and produces one row for each employee in the result set. 
SELECT 

  XMLFOREST ( 

    e.EmpId, 

    e.FirstName, 

    e.LastName, 

    e.StartDate AS "Start") AS "EmployeeInformation" 

FROM Employees e
Result Set:
EmployeeInformation 
<EmpId>1</EmpId><FirstName>Marc</FirstName><LastName>Mars

hall</LastName><Start>1994-09-01</Start> 


... 


<EmpId>20</EmpId><FirstName>Richard</FirstName><LastName>

Gamble</LastName><Start>1996-10-01</Start>

rest_of_sql99_select

This section of a SQL/XML query allows you to specify the database table from which the data is retrieved and the conditions by which the data is retrieved. The From clause defines the database tables and other optional SQL clauses, such as the Where clause, define the conditions.
Syntax 
rest_of_sql99_select ::= from_clause [where_clause] 

[having_clause] [group_by_clause]
These clauses are defined in the document ISO/IEC 9075-2.

Executing SQL/XML Queries

The SQL/XML JDBC driver processes the SQL/XML query and sends one or multiple SQL statements through to the database to retrieve the result set.



Posted by at No comments:
Subscribe to: Posts (Atom)