SQL /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
= 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
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.
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
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
DELETE FROM table_name WHERE column_name = some_value
Delete All Rows:
DELETE FROM table_name or DELETE * FROM table_name
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
SELECT column_name FROM table_name WHERE column_name IN (value1,value2,..)
SELECT column_name FROM table_name WHERE column_name BETWEEN value1 AND value2 The values can be numbers, text, or dates.
When this clause is used with the DROP command, a parent table can be dropped even when a child table exists.
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
USER_CONSTRAINTS.
< 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.
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.
The privilege receiver can further grant the privileges he/she has obtained from the owner to any other user.
START or @.
UPDATE EMP SET SAL = SAL + 1000, COMM = SAL*0.1;?
sal = 11000, comm = 1000.
RUN.
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.
MONTHS_BETWEEN.
LIKE operator.
It is used to drop constraints specified on the table.
IS NULL operator.
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.
Data Definition Language (DDL).
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.
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.
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.
_ for single character substitution and % for multi-character substitution.
&
42. What is the sub-query?
Sub-query is a query whose return values are used in filtering conditions of the main query.
Correlated sub-query is a sub-query, which has reference to the main query.
Retrieves rows in hierarchical order eg.
select empno, ename from emp where.
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.
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.
ROWID is a pseudo column attached to each row of a table. It is 18 characters long, blockno, rownumber are the components of ROWID.
Using ROWID.
CONSTRAINTS
49. What is an integrity constraint?
Integrity constraint is a rule that restricts values to a column in a table.
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.
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.
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.
CHAR, VARCHAR2, NUMBER, DATE, RAW, LONG and LONG RAW.
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.
Only one LONG column is allowed. It is not possible to use LONG column in WHERE or ORDER BY clause.
- 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.
The integrity constraints are stored in USER_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.
Database link is a named path through which a remote database can be accessed.
Sequence name CURRVAL, sequence name NEXTVAL. It is not possible. Only if you access next value in the session, current value can be accessed.
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.
- To protect some of the columns of a table from other users.
- To hide complexity of a query.
- To hide complexity of calculations.
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.
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.
1. Record type declaration.
2. Opening and parsing of SQL statements.
3. Fetches records from cursor.
4. Requires exit condition to be defined.
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.
1. %found
2. %too_many_rows
3. %notfound
4. %rowcount
5. %rowtype
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.
1. open
2. fetch
3. parse
4. None, cursor for loops handle cursor opening implicitly.
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
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)
1. Only one
2. All that apply
3. All referenced
4. None
1. Statement and Row 2. Statement only 3. Row only 4. Oracle Forms trigger
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.
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.
1. RAISE_ERROR
2. SQLERRM
3. RAISE_APPLICATION_ERROR
4. RAISE_SERVER_ERROR
1. ALTER TRIGGER check_theater ENABLE;
2. ENABLE TRIGGER check_theater;
3. ALTER TABLE check_theater ENABLE check_theater;
4. ENABLE check_theater;
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
84. Examine this function:
61. CREATE OR REPLACE FUNCTION set_budget
62. (v_studio_id IN NUMBER, v_new_budget IN NUMBER) IS
63. BEGIN
64. UPDATE studio
65. SET yearly_budget = v_new_budget
WHERE id = v_studio_id;
IF SQL%FOUND THEN
RETURN TRUEl;
ELSE
RETURN FALSE;
END IF;
COMMIT;
END;
Which code must be added to successfully compile this function?
1. Add RETURN right before the IS keyword.
2. Add RETURN number right before the IS keyword.
3. Add RETURN boolean right after the IS keyword.
4. Add RETURN boolean right before the IS keyword.
1. Altering the argument list of one of the package constructs
2. Any change made to one of the package constructs
3. Any SQL statement change made to one of the package constructs
4. Removing a local variable from the DECLARE section of one of the package constructs
1. When the transaction is committed
2. During the data manipulation statement
3. When an Oracle supplied package references the trigger
4. During a data manipulation statement and when the transaction is committed
1. DBMS_DISPLAY 2. DBMS_OUTPUT 3. DBMS_LIST 4. DBMS_DESCRIBE
1. Any DML statements issued by the construct are still pending and can be committed or rolled back.
2. Any DML statements issued by the construct are committed
3. Unless a GOTO statement is used to continue processing within the BEGIN section, the construct terminates.
4. The construct rolls back any DML statements issued and returns the unhandled exception to the calling environment.
89. Examine this code
71. BEGIN
72. theater_pck.v_total_seats_sold_overall := theater_pck.get_total_for_year;
73. END;
For this code to be successful, what must be true?
1. Both the V_TOTAL_SEATS_SOLD_OVERALL variable and the GET_TOTAL_FOR_YEAR function must exist only in the body of the THEATER_PCK package.
2. Only the GET_TOTAL_FOR_YEAR variable must exist in the specification of the THEATER_PCK package.
3. Only the V_TOTAL_SEATS_SOLD_OVERALL variable must exist in the specification of the THEATER_PCK package.
4. Both the V_TOTAL_SEATS_SOLD_OVERALL variable and the GET_TOTAL_FOR_YEAR function must exist in the specification of the THEATER_PCK package.
1. DBMS_DDL
2. DBMS_DML
3. DBMS_SYN
4. DBMS_SQL
1. DBMS_DDL
2. DBMS_DML
3. DBMS_SYN
4. DBMS_SQL
Method 1:
Select length (translate (trim (column_name),' +-.0123456789',' ')) from dual ;
Will give you a zero if it is a number or greater than zero if not numeric (actually gives the count of non numeric characters)
Method 2:
select instr(translate('wwww',
'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ',
'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX'),'X')
FROM dual;
It returns 0 if it is a number, 1 if it is not.
92 How to Select last N records from a Table?
select * from (select rownum a, CLASS_CODE,CLASS_DESC from clm)
where a > ( select (max(rownum)-10) from clm)
Here N = 10
The following query has a Problem of performance in the execution of the following query where the table ter.ter_master have 22231 records. So the results are obtained after hours.
Cursor rem_master(brepno VARCHAR2) IS
select a.* from ter.ter_master a
where NOT a.repno in (select repno from ermast) and
(brepno = 'ALL' or a.repno > brepno)
Order by a.repno
What are steps required tuning this query to improve its performance?
-Have an index on TER_MASTER.REPNO and one on ERMAST.REPNO
-Be sure to get familiar with EXPLAIN PLAN. This can help you determine the execution path that Oracle takes. If you are using Cost Based Optimizer mode, then be sure that your statistics on TER_MASTER are up-to-date. -Also, you can change your SQL to:
SELECT a.*
FROM ter.ter_master a
WHERE NOT EXISTS (SELECT b.repno FROM ermast b
WHERE a.repno=b.repno) AND
(a.brepno = 'ALL' or a.repno > a.brepno)
ORDER BY a.repno;
DELETE removes one or more records in a table, checking referential Constraints (to see if there are dependent child records) and firing any DELETE triggers. In the order you are deleting (child first then parent) There will be no problems.
TRUNCATE removes ALL records in a table. It does not execute any triggers. Also, it only checks for the existence (and status) of another foreign key Pointing to the table. If one exists and is enabled, then you will get The following error. This is true even if you do the child tables first.
ORA-02266: unique/primary keys in table referenced by enabled foreign keys
You should disable the foreign key constraints in the child tables before issuing the TRUNCATE command, then re-enable them afterwards.
CLIENT/SERVER
Preemptive refers to the fact that each task is alloted fixed time slots and at the end of that time slot the next task is started.
OLTP stands for On Line Transaction Processing
OLTP requires real time response.
The client deals with the user interface part of the system.
To reduce the network traffic and for application sharing and implementing business rules.
Applications that have outgrown their environment are re-engineered to run in a larger environment. This is upsizing.
With rightsizing, one would move applications to the most appropriate server platforms.
A host based application is re-engineered to run in smaller or LAN based environment.
An event trigger, a segment of code which is associated with each event and is fired when the event occurs.
When a stored procedure is called, only the procedure call is sent to the server and not the statements that the procedure contains.
104. What are the types of processes that a server runs ?
Foreground process and Background process.
An event handler is a routine that is written to respond to a particular event.
An integrity constraint allows the definition of certain restrictions, at the table level, on the data that is entered into a table.
Database triggers can be used to enforce business rules, to maintain derived values and perform value-based auditing.
A transaction is a set of operations that begin when the first DML is issued and end when a commit or rollback is issued. BEGIN COMMIT/ROLLBACK are the boundries of a transaction.
Because it is easier to define an integrity constraint than a database trigger.
Because an integrity constraint is automatically checked while data is inserted into a table. A stored has to be specifically invoked.
A Client,
A Server and
A Network/Communication software.
Flexibility of the system, scalability, cost saving, centralised control and implementation of business rules, increase of developers productivity, portability, improved network and resource utilization.
Heterogeneity of the system results in reduced reliablity. May not be suitable for all applications. Managing and tuning networks becomes difficult.
Star,
Bus,
Ring.
A client process at first establishes connection with the Server.
115. What are the responsibilities of a Server ?
1. Manage resources optimally across multiple clients.
2. Controlling database access and security.
3. Protecting the databse and recovering it from crashes.
4. Enforcing integrity rules globally.
An API, in a Client/Server context, is a specification of a set of functions for communication between the client and the server.
Open Database Connectivity (ODBC),
Integrated Database Application Programming Interface (IDAPI),
XOpen
SQL/CLI
The application can be connected to any back end server that is supported by the API.
The application cannot use any special features of the backend server.
Because an event driven program is always waiting for something to happen before processing.
1. System Events.
2. Control Events
3. User Events
4. Other Events.
A file server just transfers all the data requested by all its client and the client processes the data while a database server runs the query and sends only the query output.
Inheritance is a method by which properties and methods of an existing object are automatically passed to any object derived from it.
1. An ODBC manager/administrator and
2. ODBC driver.
124. What is the function of a ODBC manager ?
The ODBC Manager manages all the data sources that exists in the system.
The ODBC Driver allows the developer to talk to the back end database.
The name of the DBMS, the location of the source and the database dependent information.
ODBC uses the description of the datasource available in the ODBC.INI file to load the required drivers to access that particular back end database.
No comments:
Post a Comment