RDBMS

Answer : A DBMS (Database Management System) is a set of programs used to store and manipulate data. Manipulation of data includes adding new data (e.g. details of a new student), deleting unwanted data (e.g. students who completed the course) and changing existing data (e.g. modifying the fee paid by a student).

Answer : Support for large amounts of data; data sharing, concurrency and locking; data security; data integrity; fault tolerance and recovery; and support for languages (a non-procedural language such as SQL and a procedural language such as PL/SQL or T-SQL).

Answer : When the same data is manipulated by several users at the same time problems arise, so the DBMS locks data that is being modified to stop two users from modifying the same data at the same time. Locking is transparent and automatic – the programmer does not have to inform the DBMS or know when it locks, although understanding the mechanism makes one a better programmer.

Answer : While allowing data to be shared, the DBMS ensures data is accessed only by authorised users. By default the data of a user cannot be accessed by other users unless the owner gives them explicit permission.

Answer : Fault tolerance means the DBMS continues to run in spite of errors, where possible, allowing users to rectify mistakes in the meantime. Recovery means that even if data on disk is completely lost (e.g. disk failure), it can be recovered to the point of failure provided a proper backup is available.

Answer : An entity is any object whose data is stored in the database, e.g. a student of a training institute. An attribute is a value associated with the entity, e.g. the student’s roll number, name and course.

Answer : One-to-one: one batch is associated with only one subject. One-to-many: one batch contains many students. Many-to-many: a student may take many subjects and a subject may be taken by many students.

Answer : The hierarchical model stores data as a tree with a parent–child relationship (one root and subordinate records) and supports only one-to-many relationships; it was used in IBM’s IMS. The network model stores data along with pointers that specify relationships, can support many-to-many relationships, but is complex to understand and manipulate; it was used in Honeywell’s IDS.

Answer : Because of its simplicity – data is represented as tables of rows and columns that are easy to understand and manipulate – and because it does not bind data with the relationships between data items; relationships are formed dynamically using the values of columns.

Answer : A primary key is a set of columns used to uniquely identify rows of a table, e.g. ROLLNO in STUDENTS. When no single column can identify rows and two or more columns are combined, it is called a composite primary key, e.g. ROLLNO and DP (date of payment) in the PAYMENTS table.

Answer : A foreign key is a column used to join a child table with its parent table, e.g. ROLLNO of PAYMENTS refers to ROLLNO of STUDENTS. It is on the many side of a one-to-many relationship, and its values must be derived from the parent key.

Answer : An orphan record is a row in a child table that does not refer to any corresponding parent key. Orphan records must not exist because they are the result of a lack of data integrity.

Answer : Domain integrity: the value of a column must be derived from its domain (the collection of potential values, e.g. all valid dates). Entity integrity: every table must have a primary key whose values are not null and unique. Referential integrity: a foreign key must be either null or have a value derived from the corresponding parent key.

Answer : Relational algebra is a set of operators (union, intersect, difference/minus, project, select and join) that take one or more tables and produce one table as the result. Project takes a single table and returns a vertical subset (selected columns); Select takes a single table and returns a horizontal subset (only rows satisfying a condition).

Answer : DDL defines data (CREATE TABLE), DML manipulates data (INSERT, DELETE), DCL controls access (GRANT) and Query retrieves data (SELECT). In 2-tier client/server there are only a client and a database server; in 3-tier an application server sits between them, processing and validating client requests and passing them to the database server, taking some burden off both.

Answer : Oracle is the database management system from Oracle Corporation. Oracle8i is only a database management system, whereas Oracle9i is a platform – a collection of Oracle9i Application Server (Oracle9iAS), Oracle9i Database Server (Oracle9iDB) and Oracle9i Developer Suite (Oracle9iDS).

Answer : It works as the back-end. Front-end programs written using application development tools such as Visual Basic access Oracle and submit SQL commands to it for execution.

Answer : Oracle supports both OLTP (online transaction processing) applications and data-warehouse applications. Data-warehouse applications contain a very large database, called a VLDB.

Answer : Oracle is available on around 100 different platforms, and it is scalable – it can scale up and down easily as requirements change. It also provides a Java Virtual Machine, so from Oracle8i it can run programs written in PL/SQL or Java.

Answer : An Oracle instance is a collection of memory structures and processes used to manage an Oracle database. The instance must be started first; it then opens the database and makes it available to users.

Answer : Each Oracle database is accessed by one or more Oracle instances. When two or more instances access the same database, it is called parallel server architecture.

Answer : Personal Oracle is a flavour of Oracle used mainly as a learning tool on desktop PCs, not for production systems. In Personal Oracle the instance, the database and the client application all run on the same machine, whereas in Oracle Database Server only the instance and database reside on the server and client applications run on clients.

Answer : Starting up the database means starting the Oracle instance and associating it with an Oracle database so that the instance can access the database. On Windows NT/2000 the server usually starts automatically as a service; in Personal Oracle the instance must be started explicitly.

Answer : SQL*Plus is a tool that comes with Oracle. It provides a command-line interface through which SQL and SQL*Plus commands are entered; it displays the SQL> prompt after a successful logon.

Answer : The DBA creates users and manages overall security, makes sure the database is functioning smoothly, takes backups, recovers the database in the event of failure and fine-tunes the database for best performance. Starting and shutting down the database is also the DBA’s job.

Answer : A host string is actually a net service name stored in the TNSNAMES.ORA file on the client. It is needed only to connect to an Oracle server on a remote machine and provides the host name or IP address of the server, the name of the Oracle instance and the port number of the listener.

Answer : Because in Personal Oracle the client and Oracle always run on the same machine; a host string is required only when the Oracle server runs on a different machine from the client.

Answer : An SQL statement may be entered in multiple lines, a word cannot be broken across lines, and the statement must be terminated by a semicolon (;). SQL commands are not case sensitive.

Answer : SQL*Plus commands are available only in SQL*Plus and are not part of standard SQL; they are mainly used to edit SQL commands and format query results. The entire command must be on a single line, it need not be terminated with a semicolon, and it can be abbreviated (e.g. CLEAR SCREEN as CL SCR).

Answer : ORA-01017 (invalid username/password; login denied) occurs when the username does not exist or the password is typed wrongly; SQL*Plus allows three attempts. ORA-01034 (ORACLE not available) occurs when the Oracle instance is not up and running; the instance must be started (or the administrator notified).

Answer : What data the table is supposed to store; the name of the table (which should depict its content); the columns of the table; the name, datatype and maximum length of each column; and the rules to be implemented to maintain the data integrity of the table.

Answer : The name must begin with a letter (A–Z or a–z); letters, digits and the special characters underscore (_), $ and # are allowed; the maximum length is 30 characters; it must not be an SQL reserved word; and no other object in the account may have the same name.

Answer : VARCHAR2(len) stores variable-length strings of up to len characters (maximum 4000). CHAR(len) stores fixed-length data: the string is padded on the right with spaces up to len characters; the default width is 1 and the maximum is 2000.

Answer : p is the maximum number of significant digits allowed and s is the number of digits to the right of the decimal point.

Answer : LONG stores variable-length character values up to 2 gigabytes, but only one LONG column is allowed per table, and LONG columns cannot be used in functions, in the WHERE clause of SELECT, in indexing or in subqueries.

Answer : DESCRIBE (abbreviated DESC) is an SQL*Plus command that displays the structure of an object – the name, datatype and whether the column can store null values for each column. The object may be a table, view, synonym, function or procedure.

Answer : List the columns after the table name, e.g. INSERT INTO courses (ccode, name) VALUES ('odba', 'Oracle Database Administration'). The remaining columns take a NULL value, or the default value if the column has a default.

Answer : COMMIT makes the changes permanent. Without COMMIT, the inserted rows might be lost if there is a power failure.

Answer : NULL means a value that is not available or not known. It is neither 0 nor blank nor any other known value. Oracle stores NULL when NULL is given as a value or when a column is omitted from an INSERT and has no default.

Answer : DATE values are entered in the format DD-MON-YY or DD-MON-YYYY, where MON is the first three letters of the month. If only two digits are given for the year, the current century is used (e.g. 99 is taken as 2099 when the current century is 2000), so four digits should be given when required.

Answer : A column alias is a name given to a column or expression in the select list, displayed as the column heading instead of the expression (e.g. fee * 0.15 DISCOUNT). The alias can be used in the ORDER BY clause, e.g. ORDER BY discount.

Answer : ORDER BY sorts the retrieved rows on the given column(s) in ascending (default) or descending (DESC) order; the rows in the table are not changed. It must be the last clause of SELECT. A column position refers to the position in the select list, not in the table. NULL values are placed at the end in ascending order and at the beginning in descending order.

Answer : BETWEEN checks whether a value is in a range including both limits, e.g. duration BETWEEN 20 AND 25. IN checks whether a value matches any value in a list, e.g. duration IN (20, 30). LIKE matches a pattern where % stands for zero or more characters and _ for exactly one character, e.g. name LIKE '%.NET%'.

Answer : Oracle treats two null values as two different values, so a condition such as fee = NULL is always false. To test for null, the IS NULL (or IS NOT NULL) operator must be used, e.g. WHERE fee IS NULL.

Answer : The SQL buffer is a memory area where SQL*Plus stores the last SQL command entered (SQL*Plus commands such as DESCRIBE are not stored). Commands include APPEND (add text to the current line), DEL (delete current line), INPUT (add lines), LIST (display buffer), RUN or / (execute), SAVE, GET, START and EDIT.

Answer : An integrity constraint is a rule defined on a table to maintain the integrity of data. Once created, the Oracle server makes sure it is not violated whenever a row is inserted, deleted or updated; if the constraint is not satisfied the operation fails.

Answer : Standard rules are universal rules related to primary and foreign keys – every table must have a primary key that is unique and not null, and a foreign key must derive its values from the parent key. Business rules relate to a single application, e.g. rejecting an employee row whose salary is less than 2000, or requiring a bank account balance of at least 500.

Answer : A constraint is named with the CONSTRAINT option; otherwise the system assigns a name of the form SYS_Cn (n is a number). Giving your own name (e.g. TABLENAME_COLUMN_TYPE such as courses_name_nn) is recommended so that referring to the constraint later is easier.

Answer : A column constraint is written immediately after a column and defines a rule only for that column. A table constraint is written at table level, must name the column(s) it applies to and may refer to more than one column. A constraint that deals with more than one column (e.g. a composite primary key) must be given at table level.

Answer : It creates a unique index to enforce uniqueness and defines a NOT NULL constraint to prevent null values. A table can have only one primary key, which may consist of more than one column.

Answer : A composite primary key must be defined as a table constraint, e.g. CONSTRAINT course_faculty_pk PRIMARY KEY (ccode, fcode) in the COURSE_FACULTY table.

Answer : UNIQUE enforces uniqueness of the values in the given column(s), and Oracle automatically creates a unique index for it. If two or more columns together must be unique, the UNIQUE constraint must be given at table level.

Answer : Once a foreign key is defined, Oracle does not allow a parent row to be deleted if dependent child rows exist – e.g. a course cannot be deleted from COURSES while rows for it exist in COURSE_FACULTY.

Answer : ON DELETE CASCADE, given with the REFERENCES clause of a foreign key, makes Oracle delete all child rows automatically when the parent row is deleted.

Answer : When the referenced column of the parent table has the same name as the foreign key column, e.g. REFERENCES courses is enough instead of REFERENCES courses(ccode).

Answer : The condition may not contain a reference to the pseudo column SYSDATE or a subquery. As a column constraint it can refer only to its own column; as a table constraint it can refer to several columns of the same table; in neither case can it refer to columns of other tables.

Answer : Character comparison is case sensitive, so CHECK (grade IN ('A','B','C')) would reject lowercase letters. Using CHECK (UPPER(grade) IN ('A','B','C')) converts the grade to uppercase before comparison and ignores case differences.

Answer : BATCHES stores completed, running and scheduled batches with columns BCODE, CCODE, FCODE, STDATE, ENDDATE and TIMING. BCODE is the primary key; CCODE references COURSES and FCODE references FACULTY; STDATE is not null; ENDDATE is null for batches not yet completed; TIMING must be 1 (morning), 2 (afternoon) or 3 (evening); and STDATE must be less than or equal to ENDDATE.

Answer : COURSES is the parent and COURSE_FACULTY is the child – a one-to-many relationship. CCODE of COURSE_FACULTY is a foreign key referencing CCODE of COURSES, since one course can be handled by many faculty members.

Answer : SELECT * FROM tab lists the tables of your schema and SELECT * FROM user_constraints lists all constraints of your tables. To see constraints of one table, filter on TABLE_NAME in uppercase, e.g. WHERE table_name = 'BATCHES'. CONSTRAINT_TYPE shows C (check), U (unique), R (reference), P (primary key) or V (check on view); for example, WHERE constraint_type = 'C' lists CHECK constraints.

Answer : Adding a new column, adding a new table-level constraint, increasing and decreasing the width of a column, changing the datatype of a column, dropping a column, dropping a constraint, disabling a constraint and enabling a disabled constraint.

Answer : Oracle initialises the new column with NULL in all existing rows, which violates NOT NULL, giving ORA-01758 (table must be empty to add mandatory column). It is possible only when the table is empty or when a DEFAULT value is specified, e.g. ADD (cat varchar2(5) DEFAULT 'prog' NOT NULL).

Answer : Only NOT NULL can be added to an existing column at column level, so a CHECK constraint is added as a table constraint, e.g. ALTER TABLE courses ADD (CONSTRAINT courses_cat_chk CHECK (length(cat) >= 2)).

Answer : The length of a column can always be increased. The length can be decreased and the datatype changed only when the column is empty (no row has a value for it). A NOT NULL constraint can be added; other constraints must be added at table level.

Answer : Use ALTER TABLE table DROP CONSTRAINT constraint_name. The name of the constraint is required and can be obtained from the USER_CONSTRAINTS data dictionary view.

Answer : A UNIQUE or PRIMARY KEY constraint that is referenced by a foreign key cannot be dropped without also dropping the foreign key. The CASCADE option (e.g. ALTER TABLE courses DROP PRIMARY KEY CASCADE) drops it along with the dependent REFERENCES constraints.

Answer : A column can be dropped immediately with DROP COLUMN, removing its data at once. Or it can be marked with SET UNUSED COLUMN – it is treated as dropped and cannot be accessed, but its data remains until it is physically removed later with DROP UNUSED COLUMNS, which is useful when system load is low.

Answer : When a constraint is disabled Oracle does not enforce its rule; this is useful when inserting a large number of rows quickly. It is done with ALTER TABLE ... DISABLE CONSTRAINT name (CASCADE for dependent constraints) and re-enabled with ENABLE CONSTRAINT. The STATUS column of USER_CONSTRAINTS shows the status.

Answer : DROP TABLE removes both the data and the structure of a table and cannot be undone; views and synonyms based on it become invalid. CASCADE CONSTRAINTS drops the constraints of other tables that refer to the primary/unique keys of the dropped table; without it Oracle shows an error if such references exist.

Answer : All rows of the table are affected – UPDATE changes every row and DELETE removes every row. This is more often the result of an error than intentional.

Answer : A transaction is a collection of logically related statements that perform a single task; either all of them are done or none (all or nothing). It begins when the session begins or when the previous transaction ends, and ends when COMMIT or ROLLBACK is issued.

Answer : COMMIT makes all changes of the transaction permanent, unlocks the rows locked during the transaction, ends the transaction and erases its savepoints. Oracle commits implicitly before and after a DDL command and at normal disconnect from the database (e.g. EXIT from SQL*Plus).

Answer : It is the area where Oracle stores the data that existed before a change so that it can be restored when the user rolls back. Every transaction is associated with a rollback segment; Oracle also uses it to provide read consistency.

Answer : SAVEPOINT marks a location in a transaction so that changes can be rolled back up to that mark instead of to the beginning. For example: UPDATE; SAVEPOINT s1; INSERT; INSERT; SAVEPOINT s2; DELETE; ROLLBACK TO s1 – this undoes both INSERTs and the DELETE but keeps the first UPDATE.

Answer : Oracle automatically and transparently locks only the row being updated (row-level locking) until the transaction ends with COMMIT or ROLLBACK; other transactions can read the row but see only committed data, and a transaction trying to update a locked row waits indefinitely. Read consistency ensures that a query returns data consistent to the point the query started, using the rollback segment, even if others change and commit rows meanwhile.

Answer : A function is a name that performs a specific task. It may or may not take values (arguments), given in parentheses after the name, but it always returns a value as the result. If it takes no values the name can be used alone without parentheses.

Answer : Arithmetic functions, date and time functions, string functions, conversion functions, miscellaneous functions and group functions.

Answer : Group (aggregate) functions perform their operation on a group – a collection of rows created by GROUP BY – and return one result per group. All the other functions are single-row functions because they return a result for each row.

Answer : ROUND takes the leftmost digit being lost into account and adds one to the rightmost retained digit if it is 5 or more; TRUNC simply drops the digits. For example, ROUND(1047.785,2) gives 1047.79 while TRUNC(1047.785,2) gives 1047.78.

Answer : A negative precision rounds to the left of the decimal point. In ROUND(1285.356,-1) the one digit left of the decimal point (5) is set to zero; since it is 5 or more, one is added to the digit on its left (8 becomes 9), so the result is 1290.

Answer : CEIL returns the smallest integer greater than or equal to the value: CEIL(1.3)=2, CEIL(2)=2, CEIL(-2.3)=-2. FLOOR returns the largest integer less than or equal to the value: FLOOR(1.3)=1, FLOOR(2)=2, FLOOR(-2.3)=-3.

Answer : DUAL is a table available to every account that contains one row and one column (DUMMY VARCHAR2(1)). It is used with SELECT when the result of an expression not related to any table, such as SYSDATE or MOD(10,4), is to be displayed only once.

Answer : Adding a number to a date to get the date after that many days, subtracting a number from a date to get the date before that many days, and subtracting two dates to get the number of days between them.

Answer : Because the Oracle DATE datatype stores both date and time. SYSDATE contains the current time while DJ has time 00:00:00 (when no time is given), so the fraction in the difference represents the difference in time.

Answer : Oracle compares both date and time portions; even if DP has today’s date, its time portion does not match SYSDATE’s time. Truncating both sets the times to 00:00:00 so only dates are compared: WHERE TRUNC(dp) = TRUNC(SYSDATE).

Answer : The last day of the target month is taken. For example, ADD_MONTHS('31-aug-2001',1) returns 30-SEP-01 because 31-SEP does not exist. A negative count subtracts months.

Answer : It returns the number of months between two dates as a number; the fraction represents the days beyond whole months, calculated on a 31-day month and including time. For example, between 12-JAN-01 and 20-FEB-01 it returns about 1.258. TRUNC(MONTHS_BETWEEN(...)) ignores the fraction.

Answer : LAST_DAY(date) returns the last day of the month of the given date. LAST_DAY(stdate) + 5 gives the 5th of the month after the batch started, used as the due date of the first instalment.

Answer : NEXT_DAY(date, 'day') returns the date of the given weekday that is greater than the given date, so it never returns the same date. To get the same date when it already falls on that weekday, subtract one first: NEXT_DAY(date - 1, 'Sat').

Answer : ROUND(date) increments the date by one day if the time is at or after 12 noon, and TRUNC(date) never increments it; both set the time portion to 00:00:00. NEW_TIME(date, 'this', 'other') returns the time in another time zone (e.g. GMT to AST) for the given time.

Answer : With the string concatenation operator || (two pipe symbols), e.g. SELECT name || ' ' || qual FROM faculty joins the faculty name, a space and the qualification into one string.

Answer : String comparison is case sensitive. Converting the column to a uniform case before comparing ignores case differences, e.g. WHERE LOWER(name) LIKE '%programming%' finds both "C programming" and "XML Programming".

Answer : INITCAP converts the first character of every word to uppercase and the remaining characters to lowercase, e.g. INITCAP('this IS to Test INITCAP') returns "This Is To Test Initcap".

Answer : INSTR(s1, s2 [, pos [, occurrence]]) returns the position where s2 starts in s1, or 0 if not found. By default it returns the first occurrence searching from the first character: INSTR('How do you do','do') is 5. INSTR('How do you do','do',8) starts at position 8 and returns 12, and INSTR('How do you do','do',1,2) returns the second occurrence, 12. When occurrence is given, the start position must also be given.

Answer : First name: SUBSTR(name, 1, INSTR(name, ' ') - 1) – one is subtracted because INSTR gives the position of the space. Last name: SUBSTR(name, INSTR(name, ' ') + 1) – everything after the first space.

Answer : Each character from the left is checked against the characters in the set; if it is in the set it is trimmed, otherwise trimming stops. In the example trimming stops at "d", the first character not in "abc", so the result is "dxyabc". RTRIM works the same way on the right; without a set both trim spaces.

Answer : They pad shorter strings to the given length with spaces or the given fill string, but truncate strings longer than the length. For example, RPAD(name,12,'.') gives "VB.NET......" and "Oracle datab".

Answer : TRANSLATE works on individual characters: TRANSLATE('ABC ABAC XYZ DABC','ABC','PQR') changes every A to P, B to Q and C to R, giving "PQR PQPR XYZ DPQR". REPLACE replaces a whole substring: REPLACE('ABC ABAC XYZ DABC','ABC','PQR') gives "PQR ABAC XYZ DPQR".

Answer : Any NUMBER or DATE is converted to CHAR; CHAR is converted to NUMBER if it contains only digits, a decimal point or a minus sign on the left; CHAR is converted to DATE if it is in DD-MON-YY or DD-MON-YYYY format; a DATE is not converted to NUMBER and a NUMBER is not converted to DATE.

Answer : It gives ORA-01843: not a valid month because Oracle expects the month as the first three letters of its name for automatic conversion. The value must be converted explicitly with TO_DATE and a matching format, e.g. TO_DATE('12-1-2001','dd-mm-yyyy').

Answer : TO_CHAR(SYSDATE,'dd Month yyyy hh24:mi:ss') displays both date and time. dd is day of month, Month the complete month name, yyyy the four-digit year, hh24 the 24-hour based hour, mi minutes and ss seconds; other characters such as ":" are returned as they are.

Answer : Oracle pads month names to 9 characters, leaving trailing spaces after shorter names. fm removes these spaces, e.g. TO_CHAR(dj,'dd-fmMonth-yyyy') displays "06-April-2001" instead of "06-April -2001".

Answer : To convert a formatted number to a number, e.g. TO_NUMBER('$333','$999') * 20 = 6660 (while $333 * 20 gives ORA-00911 invalid character), and to sort CHAR data that contains numbers in numeric order, e.g. ORDER BY TO_NUMBER(vno).

Answer : DECODE(expression, cond1, value1, cond2, value2, ..., elsevalue) works like a multiple IF or CASE statement: it returns the value of the first matching condition, otherwise elsevalue. Example: DECODE(grade,'A','Very Good','B','Good','C','Average','Unknown').

Answer : Any expression involving a null value results in null, so AMOUNT - DISCOUNT is null when DISCOUNT is null. NVL(value1, value2) returns value2 when value1 is null, e.g. NVL(enddate, SYSDATE) - stdate gives the number of days even for running batches; AMOUNT - NVL(DISCOUNT,0) gives the amount.

Answer : GROUP BY divides the rows of a table into groups based on the column(s) given – all rows with the same value form one group – so that group functions can perform operations on each group, e.g. counting the number of students in each batch by grouping STUDENTS on BCODE.

Answer : The table from which the data is to be taken, the column or columns on which the data is to be grouped, and the group function to be used to get the required aggregate.

Answer : Only the columns given in the GROUP BY clause and group functions.

Answer : SUM (sum), AVG (average), MIN (minimum), MAX (maximum), STDDEV (standard deviation), VARIANCE (variance) and COUNT (number of not-null values in a column, or number of rows with *).

Answer : COUNT(*) counts the number of rows in each group. COUNT(column) counts the number of not-null values in that column. COUNT(DISTINCT column) counts only distinct values – e.g. COUNT(DISTINCT rollno) FROM payments counts each student once even if he paid several times.

Answer : Yes. Then all the selected rows are taken as a single group and the function operates on the entire set, e.g. SELECT MAX(fee) FROM courses returns the highest course fee. SELECT COUNT(*) FROM students is therefore a valid query.

Answer : The WHERE clause is executed first and selects the rows; the selected rows are then grouped by GROUP BY. GROUP BY must follow WHERE in the statement.

Answer : Rows are first grouped by the first column, and within each such group they are grouped again by the second column, and so on – e.g. GROUP BY fcode, ccode gives one group for each faculty and course combination.

Answer : HAVING is used to select (filter) groups based on a condition that uses the result of group functions, e.g. GROUP BY ccode HAVING COUNT(*) > 1 displays the courses that have more than one batch.

Answer : WHERE checks conditions on the values of columns and expressions of individual rows and cannot use group functions (ORA-00934: group function is not allowed here). HAVING evaluates conditions based on group functions such as SUM and COUNT and cannot be used for conditions not related to groups (ORA-00979: not a GROUP BY expression), though it can use GROUP BY columns.

Answer : It selects rows using WHERE, groups them using GROUP BY, calculates the results for each group, eliminates groups using HAVING and finally orders the result using ORDER BY.

Answer : ORDER BY must be the last clause, and it should contain either a GROUP BY column or a group function, e.g. GROUP BY fcode ORDER BY COUNT(*).

Answer : ROLLUP, introduced in Oracle8i, creates sub-totals from the most detailed level to the grand total, moving from right to left in its column list. GROUP BY ROLLUP(ccode, fcode) gives counts for each course and faculty, a total for each course and a grand total.

Answer : CUBE generates the same sub-totals as ROLLUP plus all other possible sub-totals. With CUBE(ccode, fcode) it additionally gives the total number of batches taken by each faculty for all courses.

Answer : GROUPING(column) returns 1 if the column’s value in that row was generated by ROLLUP or CUBE (a sub-total row). It is combined with DECODE to display meaningful labels, e.g. DECODE(GROUPING(ccode),1,'ALL courses',ccode).

Answer : In a relational database the required data is generally scattered over several tables, because storing everything in one table leads to redundancy. Joining is combining two or more tables so that values from the columns of all of them can be retrieved to get the required data.

Answer : There must be a common column between the two tables, which is used in the join condition in the WHERE clause, e.g. CCODE to join COURSES and BATCHES.

Answer : A join in which the tables are joined using the equal-to operator in the join condition, e.g. WHERE batches.ccode = courses.ccode.

Answer : By prefixing the column with the table name or the table alias followed by a dot, e.g. batches.ccode or b.ccode. Unqualified ambiguous columns are not allowed.

Answer : A table alias is a short name used to refer to a table within a query, e.g. FROM batches b, courses c. It is not stored anywhere – it is available only in the query in which it is created.

Answer : The result is the product (Cartesian product) of the tables – each row of the first table is joined with each row of the second. Joining 6 rows of COURSES with 7 rows of BATCHES gives 42 rows. It is generally the result of an error.

Answer : The join condition is the condition used to join the tables on their common column. A normal condition filters rows (e.g. enddate IS NOT NULL). Both are given in the WHERE clause; Oracle filters rows with the normal condition and joins the filtered rows using the join condition.

Answer : The minimum number of join conditions = number of tables being joined − 1. For example, joining BATCHES, COURSES and FACULTY needs two conditions (b.ccode = c.ccode and b.fcode = f.fcode). If more than one column is used to join tables, more conditions may be needed.

Answer : A self-join is joining a table with itself – two copies of the same table (two aliases) are joined as if they were two different tables. Example: SELECT b2.* FROM batches b1, batches b2 WHERE b1.bcode = 'b3' AND b2.stdate > b1.stdate lists batches that started after batch b3.

Answer : A normal join returns only rows that have common key values in both tables, so a parent row without child rows – e.g. course CS that has no batch – is not included. An outer join retrieves rows from the parent table even when there are no corresponding child rows.

Answer : The (+) operator is placed in the join condition on the side of the table that is to be extended with null rows, e.g. WHERE c.ccode = b.ccode (+). Oracle adds null rows to BATCHES for courses that have no batch, so their BCODE, STDATE and ENDDATE are shown as null.

Answer : A parent row may exist without child rows, but a child row cannot exist without a parent row when a foreign key constraint is defined on the child table; Oracle enforces this whenever rows are inserted or updated in the child table.

Answer : UNION returns the unique rows of both queries; UNION ALL returns all rows of both queries including duplicates; INTERSECT returns the rows common to both queries; MINUS returns the rows that exist in the first query but not in the second.

Answer : Both queries must select the same number of columns; corresponding columns must be of the same datatype (length and names may differ); the column names of the first query become the column headings; and null values are treated as identical.

Answer : Normally two nulls are not equal, but in set operators Oracle treats two null values as identical. If both tables have a row with C1 = 10 and C2 = null, UNION displays that row only once.

Answer : A view is a window through which a portion of one or more tables is accessed. A view does not contain any data of its own; it refers to the data of the table on which it is based, which is called the base table.

Answer : It is called a virtual table because it can be used wherever a table can be used – it executes its query and presents the data in the form of a table. It is called a stored query because Oracle stores only the query given at creation time in the data dictionary and executes it whenever the view is referred.

Answer : User FIGO owns COURSES and wants user RAUL to access it but not the FEE column. Granting the table would give RAUL complete access. Instead FIGO creates a view on COURSES with all columns except FEE and grants permission on the view only; RAUL sees accurate, up-to-date data without FEE, and no separate copy of data is stored.

Answer : Providing an extra layer over a table that allows only predetermined rows or columns to be accessed; storing complex queries in the database; presenting the data of a table in different forms (renaming, combining or splitting columns); and isolating applications from changes in the definition of tables.

Answer : OR REPLACE allows a view to be created even if one already exists, so a view can be altered without dropping, re-creating and re-granting object privileges. FORCE allows a view to be created even if the base table does not exist; the base table must exist before the view is used.

Answer : The alias of an expression becomes the name of the column in the view, and it is used to refer to the value of that expression, e.g. COUNT(*) NOBATCHES and MAX(stdate) LASTDATE in the COURSECOUNT view.

Answer : The application uses a view (e.g. BATCHDETAILS joining BATCHES and COURSES) instead of the tables. If the structure changes – e.g. FEE moves from COURSES to BATCHES – only the view is re-created with CREATE OR REPLACE VIEW to read the new structure; the application’s SELECT on the view stays the same.

Answer : The view is not dropped; its definition remains but it is marked INVALID and referring to it gives ORA-04063 (view has errors). When the table is re-created with the required columns, Oracle recompiles the view on its next use and its status becomes VALID again (extra columns or different datatypes do not matter). Status is shown in USER_OBJECTS.

Answer : Deleting rows from the base table, updating data of the base table and inserting rows into the base table – provided the view satisfies the conditions for being updatable.

Answer : A set operator, the DISTINCT operator, an aggregate function, a GROUP BY, ORDER BY, CONNECT BY or START WITH clause, a collection expression in the SELECT list, a subquery in the SELECT list, and joins (with some exceptions). Columns derived from expressions or pseudo columns cannot be updated.

Answer : A table is key-preserved if every key of the table can also be a key of the result of the join. In a view joining PAYMENTS and STUDENTS on ROLLNO, PAYMENTS is key-preserved, so its columns can be changed through the view.

Answer : The DML must manipulate only one underlying table; for UPDATE all updated columns must come from a key-preserved table; for DELETE the join must have one and only one key-preserved table; for INSERT all inserted columns must come from a key-preserved table and its NOT NULL columns must be in the view unless they have default values.

Answer : NAME belongs to STUDENTS, which is not key-preserved in the join, so Oracle raises ORA-01779: cannot modify a column which maps to a non key-preserved table. USER_UPDATABLE_COLUMNS shows NAME as not updatable, insertable or deletable.

Answer : It prevents updates and insertions through the view that the view cannot later retrieve. For example, HIGH_PAYMENTS shows payments with AMOUNT > 3000; with WITH CHECK OPTION, inserting a row with amount 2000 through the view fails with ORA-01402: view WITH CHECK OPTION where-clause violation.

Answer : DROP VIEW view_name removes the view. It has no effect on the base tables, but users who accessed the base tables only through the view can no longer access that data.

Answer : A subquery is a query within another query; the outer query is the main query and the inner query is the subquery. A (normal) subquery is always executed first and its result is passed to the main query, which is then executed using that result.

Answer : Because subqueries generally generate a list of values, and the main query must compare a single value against that list. For example, WHERE fcode IN (SELECT fcode FROM course_faculty WHERE ccode = 'ora') displays faculty who can handle the Oracle course.

Answer : A main query can receive values from more than one subquery. Example: SELECT * FROM batches WHERE fcode IN (SELECT fcode FROM faculty WHERE qual LIKE '%MS%') OR ccode IN (SELECT ccode FROM courses WHERE fee > 5000) – both subqueries are executed and then the main query.

Answer : A nested subquery is a subquery that depends on another subquery, which may depend on another, and so on – e.g. students of batches taken by faculty with qualification MS. Subqueries can be nested up to 16 levels, but more than 3 levels is not recommended for performance.

Answer : The multiple columns returned are compared with multiple values in parentheses, e.g. WHERE (ccode, enddate - stdate) IN (SELECT ccode, MAX(enddate - stdate) FROM batches GROUP BY ccode), which returns the batches that took the maximum duration for each course.

Answer : The WHERE clause of UPDATE and DELETE can contain a subquery, e.g. increasing the fee of courses that have more than 5 batches, or DELETE FROM batches WHERE bcode NOT IN (SELECT bcode FROM students). A subquery can also supply the new value in SET, e.g. SET fcode = (SELECT fcode FROM batches WHERE bcode = 'b1').

Answer : It creates a new table from an existing table using a subquery; the structure of the new table is the same as the structure of the query and the rows returned are inserted. Only NOT NULL constraints are carried to the new table – e.g. NEW_BATCHES keeps NOT NULL on STDATE but not the primary key.

Answer : The order and type of columns in the query and the target table should be the same; otherwise the columns must be listed according to the requirement.

Answer : A subquery can be used in the VALUES clause of INSERT (e.g. (SELECT MAX(rollno) + 1 FROM students) to get the next roll number), and ORDER BY is permitted in a subquery, which allows top-N analysis using ROWNUM.

Answer : SELECT ccode, name, fee FROM (SELECT * FROM courses ORDER BY fee DESC) WHERE ROWNUM < 3. The subquery sorts the courses in descending order of fee and the main query takes only the first two rows using the ROWNUM pseudo column.

Answer : A correlated subquery receives some input (a value) from the main query, uses it – generally in its condition – and sends the result back to the main query. It is identified by the use of a column of the main query inside the subquery, e.g. WHERE ccode = b1.ccode.

Answer : A correlated subquery is executed once for each row of the main query, because the value it uses may change from row to row. A normal subquery is executed only once before the main query and only sends a value to the main query, whereas a correlated subquery receives values from and sends values to the main query.

Answer : SELECT name, fee FROM courses c1 WHERE 2 = (SELECT COUNT(*) FROM courses WHERE fee > c1.fee). For each course the subquery counts courses with a higher fee; if exactly two fees are higher, that fee is the third highest.

Answer : EXISTS is true if the subquery returns any row; NOT EXISTS is true if the subquery returns no row. They do not compare column values, so what is selected in the inner query does not matter. They are used with correlated subqueries, e.g. courses for which no batch has started: WHERE NOT EXISTS (SELECT * FROM batches WHERE ccode = courses.ccode).

Answer : Both compare one value with a set of values using a relational operator. ANY is true if the condition is satisfied by any value in the list; ALL is true only if it is satisfied by all values. For x = 15, x > ANY (10,20) is TRUE because 15 is greater than 10.

Answer : Like a book index, an Oracle index is used to locate data quickly, contains key values in ascending order, stores a pointer to the data (ROWID, like a page number), occupies extra space and is used only when it helps. Oracle stores the index separately from the table and uses it only when it improves the performance of a query.

Answer : For each entry an index stores the key (value of the indexed column) and the corresponding ROWID of the row. Internally the index is stored as a self-balancing binary tree, a data structure that makes searching for a value faster.

Answer : To speed up searching for a particular value, thereby improving the performance of queries, and to enforce uniqueness of the indexed column(s) through a UNIQUE index.

Answer : PRIMARY KEY and UNIQUE constraints – Oracle automatically creates a unique index on those columns to enforce uniqueness.

Answer : CREATE INDEX batches_ccode_fcode_idx ON batches (ccode, fcode). When two or more columns are used, they should be given in the order of frequency of usage – the most frequently used column first.

Answer : When the SELECT has no WHERE clause (all rows are retrieved), when the WHERE clause does not refer to an indexed column, and when the indexed column is modified in the WHERE clause, e.g. by a function such as SUBSTR(name,4,3) or UPPER(name).

Answer : Introduced in Oracle8i, it is an index created on the result of an expression, e.g. CREATE INDEX students_name_idx ON students (UPPER(name)) or ON batches (enddate - stdate). Oracle can use it when the query uses the same expression. The user needs the QUERY REWRITE system privilege.

Answer : Before Oracle8i the table was locked throughout index creation, making it unavailable for data manipulation. With ONLINE (Oracle8i), Oracle does not lock the table while building the index, increasing availability – important for Internet databases.

Answer : Indexes can decrease the performance of data manipulation: with 5 indexes on a table every insertion of a row updates all 5 indexes, so one insertion results in six insertions, and every change to an indexed column needs a change to the index. They also occupy extra space. Many indexes suit tables used mainly for querying.

Answer : Clustering stores tables that are often used together in joins in one area of the disk, improving join performance. It also reduces space because the common column (cluster key) is stored only once for each unique value.

Answer : 1. Create the cluster with CREATE CLUSTER, giving the name and the cluster key column and datatype. 2. Create an index on the cluster with CREATE INDEX ... ON CLUSTER before any rows are inserted. 3. Place tables into the cluster using the CLUSTER option of CREATE TABLE, naming the column that matches the cluster key.

Answer : No. A table must be placed into a cluster at the time of its creation using the CLUSTER option of CREATE TABLE; an existing table therefore has to be created again with that option.

Answer : A sequence is a database object used by multiple users to generate unique numbers, typically for primary keys. NEXTVAL yields the next value and automatically increments the sequence; CURRVAL returns the value taken by the most recent NEXTVAL and cannot be used unless NEXTVAL has been called first.

Answer : No. It guarantees uniqueness, but not consecutiveness. If an INSERT that took a number with NEXTVAL is rolled back, that number is not used, and the next NEXTVAL returns the following number – leaving a gap (e.g. 103 is skipped).

Answer : Pseudo columns are used like table columns but are not columns of any table and cannot be modified (CURRVAL, NEXTVAL, NULL, ROWID, ROWNUM, SYSDATE, USER, UID). ROWID is the address of a row, assigned when the row is created and unchanged for its lifetime; Oracle stores it in indexes to access rows. ROWNUM is the order in which Oracle selects rows; it is assigned after a row is selected and before ORDER BY, so only < and <= are meaningful (WHERE ROWNUM = 5 returns no rows).

Answer : Oracle creates SYSTEM and SYS, which have privileges to perform administrative operations such as creating, altering and dropping users, roles, profiles and tablespaces. To create a new user (CREATE USER name IDENTIFIED BY password) you must log on as SYSTEM or SYS.

Answer : The new user lacks the CREATE SESSION system privilege, so logging on gives ORA-01045 (user lacks CREATE SESSION privilege; logon denied). Granting the CONNECT and RESOURCE roles (GRANT connect, resource TO srikanth) lets the user connect and create and use objects.

Answer : A privilege is a right to access an object or to execute a particular type of SQL command. A system privilege is a right to perform an operation in the system, e.g. CREATE SESSION or CREATE TABLE; it is generally granted through roles by DBAs. An object privilege is a right to perform an operation on a particular object such as a table, view, sequence, procedure, function or package.

Answer : ALTER (change the definition), DELETE (delete rows), EXECUTE (execute the object), INDEX (create an index on it), INSERT (insert rows), REFERENCES (refer to it in a foreign key constraint), SELECT (select rows) and UPDATE (update data).

Answer : TABLE: SELECT, INSERT, DELETE, UPDATE, ALTER, INDEX, REFERENCES. VIEW: SELECT, INSERT, UPDATE, DELETE. SEQUENCE: SELECT, ALTER. PROCEDURE, FUNCTION, PACKAGE and OBJECT TYPE: EXECUTE.

Answer : GRANT object_priv ON object TO user/role/PUBLIC grants a privilege. ALL grants all available object privileges on the object, PUBLIC grants the privilege to all users, and for UPDATE, INSERT and REFERENCES the privilege can be restricted to columns, e.g. GRANT UPDATE(duration) ON courses TO praneeth.

Answer : Oracle assumes the table belongs to the current user (PRANEETH); since it is not found in his account it gives ORA-00942: table or view does not exist. The table must be prefixed with the owner’s name: SELECT * FROM srikanth.courses, or a synonym must be used.

Answer : A synonym is an alias for a table or view that avoids giving the owner name. A private synonym is created in the account of the user who uses it (e.g. CREATE SYNONYM courses FOR srikanth.courses in PRANEETH’s account). A public synonym, created with CREATE PUBLIC SYNONYM (which needs the CREATE PUBLIC SYNONYM system privilege), is available to all users.

Answer : It allows the grantee to grant the privilege he received to other users. If SRIKANTH grants SELECT on COURSES to PRANEETH WITH GRANT OPTION, PRANEETH can grant it to ANURAG, who still accesses the table as srikanth.courses.

Answer : REVOKE privilege ON object FROM user/role/PUBLIC takes back privileges granted earlier; ALL revokes all of them. CASCADE CONSTRAINTS drops any referential (foreign key) constraint that was created using a REFERENCES privilege that is being revoked. GRANT and REVOKE are DCL commands.

Answer : When a privilege is revoked from a user who has granted it to others, all those grantees also lose the privilege. If A grants to B and B grants to C, both B and C lose the privilege when A revokes it from B.

Answer : A role is a collection of system and/or object privileges. It is dynamic – privileges added later are available to all its users; creating one needs the CREATE ROLE system privilege; Oracle has predefined roles such as CONNECT, RESOURCE and DBA; a user can be granted multiple roles; and only enabled roles provide privileges, though users can enable or disable their roles.

Answer : Create the role (CREATE ROLE manager), grant the required privileges to it (e.g. GRANT SELECT ON courses TO manager), and grant the role to users (GRANT manager TO praneeth). If the role is not enabled by default, the user enables it with SET ROLE manager. DROP ROLE removes it.

Answer : A password can be assigned with CREATE ROLE manager IDENTIFIED BY efficient (or later with ALTER ROLE). The user must give it when enabling the role: SET ROLE manager IDENTIFIED BY efficient; otherwise ORA-01979: missing or invalid password for role is raised.

Answer : ALTER USER changes a user’s password (ALTER USER praneeth IDENTIFIED BY tunu) and default roles (DEFAULT ROLE ALL EXCEPT manager); a user can change only his own password. Data dictionary views present information about the database in a simple form, are based on tables owned by SYS, and include USER_TABLES (TABS), USER_OBJECTS (OBJ), USER_CATALOG (CAT), USER_VIEWS and ALL_TAB_COLUMNS.

Answer : A report is information presented in a neat and understandable format containing both details and summary information. No single SELECT clause does both – GROUP BY gives summaries but not details and a simple SELECT gives details but not summaries – so SQL*Plus provides commands to generate reports.

Answer : BREAK specifies the column(s) on which the selected data is grouped (broken) and what to do when a break occurs. Actions: SKIP n (print n empty lines), SKIP PAGE (page break), DUPLICATE (print the break column value in every row) and NODUPLICATE (print blanks for duplicate values). BREAK alone displays the current settings.

Answer : BREAK detects a change in the value of a column, so rows must be ordered on the break columns (e.g. ORDER BY course, batch). When multiple columns are given, SQL*Plus issues breaks from the rightmost column to the leftmost – in BREAK ON report ON course SKIP PAGE ON batch SKIP 2 it breaks on batch, then course, then at the end of the report.

Answer : COMPUTE computes and prints summary information. Its functions are AVG, COUNT, MAXIMUM, MINIMUM, NUMBER (count of rows), STD (standard deviation), SUM and VARIANCE. For example, COMPUTE SUM OF amount ON batch course report prints the sum of AMOUNT at the end of each batch, each course and the report.

Answer : Every COMPUTE requires a corresponding BREAK. COMPUTE displays summary information only when there is a break on the given level; e.g. COMPUTE SUM OF amount ON bcode REPORT needs BREAK ON bcode ... ON REPORT.

Answer : LABEL specifies the text displayed as the label of the computed value, e.g. COMPUTE SUM LABEL 'Grand Total' OF amount ON REPORT. The label can be up to 500 characters but is truncated to the size of the first column in the SELECT.

Answer : The text of the column heading (HEADING), alignment of the heading (JUSTIFY LEFT/CENTER/RIGHT), the format of the column (FORMAT), wrapping of text (WRAPPED, WORD_WRAPPED, TRUNCATED) and the text substituted for null values (NULL).

Answer : Character columns use the format An, where n is the display width (longer values are wrapped, word-wrapped or truncated). DATE columns must be formatted with the TO_CHAR function in the query. Numeric columns use format elements such as 9, 0, $, B, MI, PR, comma, period, V and EEEE, e.g. FORMAT 99,999.00.

Answer : The format 99,999.00 displays 12345 as 12,345.00. In HEADING, the separator | splits the heading over multiple lines, e.g. COLUMN dj HEADING 'Date of|Joining' prints "Date of" on the first line and "Joining" on the second.

Answer : TTITLE prints text at the top of each printed page and BTITLE at the bottom. Options include BOLD, COL n, CENTER, LEFT, RIGHT and SKIP n. For example, TTITLE CENTER 'Sales Report' prints the title centred at the top of each page; TTITLE OFF turns the top title off.

Answer : SQL.PNO returns the current page number, SQL.USER the current username and SQL.RELEASE the current Oracle release number – e.g. TTITLE LEFT 'Payments Report' RIGHT SQL.PNO.

Answer : SPOOL stores the output displayed on the screen into an operating system file, which can also be printed. SPOOL filename starts spooling into the file, SPOOL OFF stops spooling, and SPOOL OUT stops spooling and sends the file to the default printer.

Answer : CLEAR resets the specified option: BREAKS (break settings), COMPUTES (compute settings), SCREEN (content of the SQL*Plus window), COLUMNS (column settings) and BUFFER (text in the current buffer).

Answer : AUTOCOMMIT ON commits changes made by each SQL command automatically; FEEDBACK displays the number of rows selected when it is at least the specified number; LINESIZE sets the number of characters per line and PAGESIZE the number of lines per page (together the dimension of the report page); SERVEROUTPUT ON enables display of output from the DBMS_OUTPUT package. They are changed with SET.

Answer : SHOW displays the current values of system variables and other options: e.g. SHOW USER (name of the current user), SHOW PAGESIZE, SHOW ERRORS (compilation errors of procedures and functions), SHOW RELEASE, SHOW TTITLE and SHOW ALL.

Answer : PL/SQL is Oracle’s procedural language extension to SQL. It allows SQL statements to be mixed with procedural statements such as IF statements and loops. It is a superset of SQL because it uses SQL for data retrieval and manipulation and its own statements for data processing.

Answer : An anonymous block is a PL/SQL block that appears within an application wherever SQL statements can appear; it has no name. A stored procedure is a PL/SQL block stored in the database with a name, which application programs execute using that name. Oracle also allows functions (which return a value) and packages (collections of procedures and functions).

Answer : The PL/SQL engine compiles and executes PL/SQL blocks. It executes the procedural statements using its procedural statement executor and sends the SQL commands to the SQL statement executor in the Oracle RDBMS. It is available in the Oracle Server and in tools such as Oracle Forms and Oracle Reports.

Answer : Block structure (with nesting), variables and constants, control structures (IF, FOR loop, WHILE loop), exception handling, modularity (procedures and functions with parameters), cursors and built-in functions.

Answer : A PL/SQL block is sent to the Oracle server as one unit. Without PL/SQL each SQL command is passed to the server separately, which heavily increases network traffic; sending a collection of statements as a block improves performance.

Answer : Static SQL is written at the time of writing the program. Dynamic SQL is the process of constructing and executing SQL statements on the fly at run time. From Oracle8i PL/SQL supports native dynamic SQL, which is easier than the earlier DBMS_SQL package.

Answer : The declarative part (DECLARE – variables, cursors, exceptions), the executable part (BEGIN ... – SQL and PL/SQL commands) and the exception-handling part (EXCEPTION – handlers for errors raised in the executable part), ending with END. Only the executable part is mandatory.

Answer : %TYPE declares a variable with the same datatype as a table column, e.g. v_name students.name%type takes whatever is the datatype of the NAME column of STUDENTS.

Answer : Oracle assumes the column is being referenced whenever the name is used in SQL commands – column names take precedence over variable names in SQL statements.

Answer : Scalar (a single value: NUMBER, CHAR, VARCHAR2, DATE, BOOLEAN), Composite (a collection of components: RECORD, TABLE, VARRAY), Reference (a pointer: REF CURSOR, REF object_type) and LOB (holds a LOB locator: BFILE, BLOB, CLOB, NCLOB). BOOLEAN can hold TRUE, FALSE or NULL.

Answer : In PL/SQL, SELECT only retrieves data; the INTO clause specifies the variable(s) into which the retrieved values are copied, e.g. SELECT SUM(amount) INTO v_sum FROM payments WHERE rollno = 102. The number and datatypes of the INTO variables must match the selected columns.

Answer : A constant is declared with the CONSTANT keyword and an initial value, e.g. bonus CONSTANT number(3) := 500; any attempt to change it gives an error. Single-line comments start with two hyphens (--) and multi-line comments are enclosed in /* and */.

Answer : Scope is the region of the program in which a variable can be used – from its declaration to the end of its block, including nested blocks. A variable is visible where it can be referred to without a qualifier. If an inner block declares a variable with the same name, the outer variable is still in scope but not visible (hidden) in the inner block.

Answer : By labelling the outer block with <<label>> placed before it and prefixing the variable with the label, e.g. mainblock.n := 50 assigns to the variable N of the block labelled MAINBLOCK even though the inner block declares its own N.

Answer : Using the DBMS_OUTPUT package: PUT writes pieces that make up a line and PUT_LINE writes a value followed by an end-of-line character (for NUMBER, VARCHAR2 or DATE values). The output is seen only when the program unit has completed and the SQL*Plus variable SERVEROUTPUT is set ON (SET SERVEROUTPUT ON in each new session).

Answer : IF condition-1 THEN statements_set_1; [ELSIF condition-2 THEN statements_set_2;] ... [ELSE statements_set_3;] END IF; – the statements of the first true condition are executed; if none is true, the ELSE statements are executed.

Answer : > (greater than), >= , < , <= , = (equal), the not-equal operators <>, !=, ~= and ^=, LIKE (pattern match), BETWEEN..AND (range), IN (list) and IS NULL.

Answer : With AND both conditions must be true for the entire condition to be true; with OR the entire condition is true if any one of the conditions is true, e.g. IF rate > 500 AND qty < 10 THEN.

Answer : Conditions are checked one after another; the IF statement terminates once a condition is true and its statements are executed. Every IF must have a matching END IF, but an ELSIF does not need its own END IF, and ELSIF can be used only after an IF.

Answer : It is the simplest looping structure that repeatedly executes a set of statements. It has no termination point, so unless it is terminated using EXIT it becomes an infinite loop.

Answer : EXIT used alone terminates the current loop as soon as it is executed (typically inside an IF). EXIT WHEN condition terminates the loop only when the given condition is satisfied, e.g. EXIT WHEN i > 10.

Answer : DECLARE i NUMBER(2) := 1; BEGIN LOOP DBMS_OUTPUT.PUT_LINE(i); i := i + 1; EXIT WHEN i > 10; END LOOP; END; – I is displayed, incremented, and the loop ends when I becomes greater than 10.

Answer : A loop placed within another loop. The inner loop is executed completely for each iteration of the outer loop – e.g. displaying multiplication tables up to 10 for numbers 1 to 5.

Answer : By assigning a label to the outer loop (<<outerloop>> before LOOP) and using the label with EXIT, e.g. EXIT outerloop WHEN condition, which terminates the specified enclosing loop instead of the current loop.

Answer : WHILE condition LOOP statements; END LOOP; executes the statements repeatedly as long as the condition is true. The condition is checked at the beginning of each iteration, and the loop terminates once the condition becomes false.

Answer : 1. The counter is set to the lower range. 2. If the counter is less than or equal to the upper range the statements are executed, otherwise the loop terminates. 3. The counter is incremented by one and only one. 4. Step 2 is repeated. The ranges may be expressions.

Answer : With REVERSE the counter is set to the upper range, statements are executed while the counter is greater than or equal to the lower range, and the counter is decremented by one each time – e.g. FOR i IN REVERSE 1..10 displays 10 down to 1.

Answer : No. The FOR loop counter is always incremented (or decremented with REVERSE) by one and only one; it is not possible to change the step value.

Answer : It gets the minimum and maximum roll numbers with MIN and MAX, runs a FOR loop from the minimum to the maximum, counts the students having the current roll number with SELECT COUNT(*), and displays the roll number when the count is 0.

Answer : GOTO transfers control to a named label (<<label>>) that must be unique and precede an executable statement or a block. It cannot branch into an IF statement, into a LOOP or into a sub-block, cannot branch out of a subprogram (procedure or function), and cannot branch from an exception handler into the current block.

Answer : In PL/SQL, errors and warnings are called exceptions. Whenever a predefined error occurs, PL/SQL raises an exception automatically – e.g. ZERO_DIVIDE when a number is divided by zero and NO_DATA_FOUND when SELECT cannot find a record. Users can also create their own exceptions.

Answer : When it is handled, control is transferred to the exception-handling part; after the handler executes, control goes to the next statement in the enclosing block, or returns to the host if there is no enclosing block. If it is not handled, the block is terminated and an error message related to the exception is displayed.

Answer : EXCEPTION WHEN exception-1 [OR exception-2] THEN statements; [WHEN exception-3 THEN statements;] ... [WHEN OTHERS THEN statements;]. Several exceptions in one WHEN share the same action. WHEN OTHERS executes its statements for any exception not mentioned in the other handlers.

Answer : They are functions that return the error number and the error message of the most recently occurred error. They are used mainly in WHEN OTHERS because most Oracle errors have no predefined exception. They cannot be used directly in an SQL statement – their values must first be assigned to variables.

Answer : NO_DATA_FOUND (ORA-01403, SQLCODE +100) is raised when a SELECT INTO returns no rows or an uninitialised row of a PL/SQL table is referenced. TOO_MANY_ROWS (ORA-01422, SQLCODE -1422) is raised when a SELECT INTO returns more than one row.

Answer : DUP_VAL_ON_INDEX (ORA-00001) when a duplicate value is stored in a column constrained by a unique index; VALUE_ERROR (ORA-06502) when an arithmetic, conversion, truncation or size-constraint error occurs; INVALID_NUMBER (ORA-01722) when, in an SQL statement, converting a character string to a number fails.

Answer : When a group function is used in the SELECT – e.g. SUM or AVG return NULL and COUNT returns 0 when no row is retrieved – and when UPDATE or DELETE commands affect no rows.

Answer : Since AVG returns NULL instead of raising NO_DATA_FOUND, the result variable is checked: IF v_avgdur IS NULL THEN display "No batch of C++ has been completed" ELSE display the average.

Answer : Declare it in the declarative part (out_of_stock EXCEPTION;), raise it explicitly with the RAISE command when the required condition occurs (IF qty < 10 THEN RAISE out_of_stock; END IF;), and handle it in the exception part like a predefined exception (WHEN out_of_stock THEN ...).

Answer : A predefined exception has a name defined by PL/SQL for a common error and is raised automatically when the corresponding error occurs. A user-defined exception is declared in the block in which it is used and must be raised explicitly with RAISE.

Answer : In an exception handler, RAISE without an exception name raises the current exception again so that it is propagated to the outer block, e.g. a sub-block handles OUT_OF_STOCK partially and then executes RAISE so the outer block also handles it. RAISE without a name is valid only in an exception handler.

Answer : Declare the exception and use PRAGMA EXCEPTION_INIT(exception_name, error_number) in the declarative part, e.g. PRAGMA EXCEPTION_INIT(null_value_error, -1407). Whenever Oracle error -1407 (setting a NOT NULL column to null) occurs, PL/SQL raises NULL_VALUE_ERROR automatically.

Answer : If an exception is not handled in the current block it is propagated to the enclosing blocks one after another, from inside to outside, until a handler is found. If no enclosing block handles it, it is sent to the host environment (e.g. SQL*Plus, Oracle Forms, Oracle Server), and the action depends on the host.

Answer : It is immediately propagated to the enclosing block and is not handled by the handlers of the block in which it occurred. For example, assigning 'abcdef' to a VARCHAR2(5) variable in an inner block’s declaration is caught by the outer block: ORA-06502: numeric or value error: character string buffer too small.

Answer : It is successful if it exits without raising any exception, or raises an exception that is handled in its own exception-handling part. It is unsuccessful if it exits with an unhandled exception or executes RAISE_APPLICATION_ERROR to generate a user-defined error.

Answer : Oracle uses a work area to execute SQL commands and store processing information. A cursor is a name through which PL/SQL accesses this area.

Answer : PL/SQL declares an implicit cursor, named SQL, for every DML command and every query that returns a single row; it needs no declaration. An explicit cursor is declared by the programmer and is required whenever a SELECT in PL/SQL has to retrieve more than one row.

Answer : A SELECT ... INTO in PL/SQL succeeds only when it retrieves exactly one row – no row raises NO_DATA_FOUND and more than one raises TOO_MANY_ROWS – because multiple values for a column cannot be copied into a single variable. An explicit cursor stores the collection of rows and allows the program to fetch and process one row at a time.

Answer : Declare the cursor in the declare section with its SELECT; open it with OPEN in the executable part; fetch one row at a time with FETCH (inside a loop); and close it with CLOSE after all records are fetched and processed.

Answer : OPEN cursor_name [(input arguments)] executes the SELECT command associated with the cursor and places the rows retrieved by the query into the cursor.

Answer : FETCH cursor_name INTO variable-1, variable-2, ... takes the data of the current row of the cursor and copies the column values into the variables. There must be one variable for each column of the cursor and the datatypes must match. It is used inside a loop to read rows repeatedly.

Answer : After each FETCH use EXIT WHEN cursor_name%NOTFOUND – NOTFOUND returns TRUE when the previous FETCH did not read a row. CLOSE cursor_name closes the cursor and releases the resources associated with it.

Answer : FOR rowtype_variable IN cursor_name LOOP statements; END LOOP; is a FOR loop meant for cursors. It automatically opens the cursor, fetches rows one at a time, terminates the loop when all rows are fetched and closes the cursor.

Answer : It is declared automatically as cursor_name%ROWTYPE, has the columns of the cursor as fields, and is available only inside the loop. A column of the current row is accessed as rowtype_variable.columnname, e.g. rec.fee.

Answer : Oracle implicitly opens the cursor named SQL for each SQL statement not associated with an explicit cursor. OPEN, FETCH and CLOSE cannot be used with it, but its attributes give information about the most recently executed SQL statement, e.g. IF SQL%NOTFOUND after an UPDATE.

Answer : %NOTFOUND – TRUE if the previous fetch failed; %FOUND – TRUE if the previous fetch succeeded; %ROWCOUNT – number of rows fetched from the cursor so far; %ISOPEN – TRUE if the cursor is currently open. Syntax: cursor_name%attribute.

Answer : SQL%NOTFOUND is TRUE if the previous DML operation did not affect any row; SQL%FOUND is TRUE if it affected any row; SQL%ROWCOUNT gives the number of rows affected by the most recent DML operation. They are how we know whether the most recent DML affected any row.

Answer : They are parameters declared with the cursor, e.g. CURSOR batch_cursor (pccode VARCHAR2) IS SELECT * FROM batches WHERE ccode = pccode, whose values are passed when the cursor is opened: OPEN batch_cursor('ora') or FOR rec IN batch_cursor('ora'). The same cursor can represent different sets of records at different times. The size of the parameter datatype must not be given.

Answer : Oracle locks rows automatically while manipulating them. FOR UPDATE [OF columns] in the cursor’s SELECT overrides the default locking by locking all records retrieved by the cursor at the time of opening it, so that they are not locked by others before we update or delete them.

Answer : WHERE CURRENT OF cursor_name refers to the current row of the cursor (the row last fetched), so UPDATE or DELETE acts on that row. FOR UPDATE must be given in the cursor declaration to use CURRENT OF.

Answer : A stored procedure is a PL/SQL block stored in the database as an object with a name and invoked using that name; it is also called a database procedure and may take parameters. A function is similar to a procedure except that it returns a value, which the calling program uses.

Answer : Reduced network traffic (only the name is sent to the server), better performance (stored in compiled form in the database and shared in server memory), easy maintenance (business logic changed once in the procedure is used by all clients) and security (users can execute the procedure without having privileges on the underlying tables).

Answer : The procedure and the data it accesses are both in the database, and because its compiled form is stored, no compilation step is required to execute it. It also uses shared memory – once brought into server memory by one user, another user may find it already in memory.

Answer : Oracle creates a procedure even when it has errors and displays "Warning: Procedure created with compilation errors". SHOW ERRORS displays the errors of the most recent CREATE PROCEDURE. OR REPLACE lets the corrected version replace the existing (invalid) procedure; without it Oracle does not allow an existing procedure to be replaced.

Answer : With the EXECUTE command, e.g. EXECUTE newpayment(10, 2000). The user must be the owner of the procedure or must have the EXECUTE privilege on it.

Answer : RETURN datatype in the header specifies the type of value returned. The RETURN statement in the executable part returns the value, which must be of the same type, e.g. RETURN v_fee - v_amtpaid in GETDUEAMT. User-defined functions can be used in SQL like standard functions, e.g. SELECT rollno, getdueamt(rollno) FROM students.

Answer : The USER_SOURCE data dictionary view holds the source, e.g. SELECT text FROM user_source WHERE name = 'NEWPAYMENT'. To create a procedure you need the CREATE PROCEDURE system privilege and the object privileges on the objects it refers to; the owner cannot obtain these required privileges through roles.

Answer : Procedures are stored in compiled form; if a procedure becomes invalid (e.g. it referred to a table without privilege, and the privilege is granted later) it must be recompiled before execution. Oracle recompiles it implicitly when referred, but to avoid the run-time delay it can be recompiled explicitly with ALTER PROCEDURE name COMPILE or ALTER FUNCTION name COMPILE. USER_OBJECTS shows the status (VALID/INVALID).

Answer : Formal parameters are declared in the definition of the procedure and receive the values sent while calling it, e.g. pccode and pamt in increase_fee(pccode varchar2, pamt number). Actual parameters are the values given within parentheses while calling, e.g. v_ccode and 2000; they are copied to the corresponding formal parameters.

Answer : IN (the default) passes a value to the subprogram; it acts like a constant and cannot be assigned; the actual parameter can be a variable, constant or expression. OUT lets the subprogram pass a value back to the caller; it is uninitialised inside the subprogram and the actual parameter must be a variable. IN OUT both receives a value and returns a value; its actual parameter must also be a variable.

Answer : By default OUT and IN OUT parameters are passed by value – copied into the formal parameter and copied back on normal exit. For large data structures such as records and object instances this copying slows execution and uses more memory, so NOCOPY lets PL/SQL pass OUT and IN OUT parameters by reference, e.g. pstudent IN OUT NOCOPY student_type.

Answer : With definer (owner) rights – the default – a procedure runs with the privileges of its owner and all unqualified object references resolve to the owner’s schema. With invoker rights, introduced in Oracle8i and specified with AUTHID CURRENT_USER, it runs with the privileges of the calling user and unqualified objects resolve to the invoker’s schema, allowing centralised code with data stored in each user’s schema.

Answer : It creates your own application error number and message: RAISE_APPLICATION_ERROR(errornumber, errormessage). It terminates the PL/SQL program and sends the error code and message to the host environment, e.g. RAISE_APPLICATION_ERROR(-20100, 'Invalid course code'). The error number is a negative number in the -20000 range (the notes give -20001 to -20999).

Answer : A package is a collection of related procedures, functions, variables and datatypes. The specification (CREATE PACKAGE) declares the public items, which are visible and callable outside the package as package_name.object_name. The body (CREATE PACKAGE BODY) defines the public procedures, functions and cursors and also private procedures, functions and variables that are used only inside the package.

Answer : The initialisation part is a BEGIN ... section at the end of the package body containing code executed when a user refers to the package for the first time. Packages group related procedures and functions under one name and avoid name conflicts between procedures.

Answer : A database trigger is a PL/SQL block that is executed on an event in the database – a particular data manipulation of a table such as inserting, deleting or updating a row. Triggers are executed automatically by Oracle and their execution is transparent to users.

Answer : To implement complex business rules that cannot be implemented using integrity constraints, to audit the process (keep track of changes made to a table), and to automatically perform an action when another related action takes place, e.g. updating a table whenever a row is inserted into another.

Answer : Both are PL/SQL blocks stored in the database, but stored procedures are called explicitly, whereas triggers are called implicitly by Oracle when the concerned event occurs.

Answer : A statement-level trigger fires only once for a DML statement irrespective of the number of rows affected and cannot access NEW and OLD; it is the default and suits rules not related to data. A row-level trigger, created with FOR EACH ROW, fires once for each affected row (100 rows → 100 times) and is used to check the validity of data.

Answer : A BEFORE trigger fires before the INSERT, UPDATE or DELETE is executed and is commonly used to check the validity of data, e.g. to prevent a deletion. An AFTER trigger fires after the triggering action is completed, e.g. after the row is inserted.

Answer : Four timing/level combinations – before statement, before row, after statement and after row – each of which can be associated with INSERT, DELETE and UPDATE, giving a total of 12 triggers.

Answer : The name of the trigger, the table it is associated with, when it is fired (BEFORE or AFTER), the command that invokes it (INSERT, UPDATE [OF columns] or DELETE), whether it is a row-level trigger (FOR EACH ROW), an optional condition to filter rows (WHEN), and the PL/SQL block to execute.

Answer : WHEN fires the trigger only when the given condition is satisfied and can be used only with row triggers (e.g. only when the new AMOUNT is more than 1000). OF specifies the columns whose update fires the trigger. REFERENCING gives new names instead of the default correlation names OLD and NEW, mainly to avoid name conflicts with tables.

Answer : It is a BEFORE INSERT, row-level trigger on STUDENTS (name = table + BI for before insert + ROW). It checks whether :NEW.DJ (date of joining of the row being inserted) is greater than SYSDATE and, if so, raises an error with RAISE_APPLICATION_ERROR(-20002, ...).

Answer : DML commands and SELECT INTO are the valid statements; the trigger can also use existing stored procedures, functions and packages. USER_TRIGGERS lists triggers; its TRIGGER_BODY column is of LONG type, so the SQL*Plus variable LONG (default 80) should be set larger to see the full body.

Answer : NEW refers to the new values and OLD to the old (existing) values of the row in a row-level trigger. For INSERT only NEW is available, for DELETE only OLD is available, and for UPDATE both are available – OLD before the change and NEW after the change.

Answer : A BEFORE UPDATE row-level trigger compares the values: IF :NEW.amount <> :OLD.amount THEN RAISE_APPLICATION_ERROR(-20113, 'Amount cannot be changed...'); END IF; – if they differ, the value is being changed and the trigger fails.

Answer : It is defined on relational views and object views to modify views that cannot be directly modified by DML. Instead of executing the DML command on the view, Oracle invokes the INSTEAD OF trigger – e.g. inserting into the join view NEWSTUDENT (which normally gives ORA-01779) inserts one row into STUDENTS and one into PAYMENTS.

Answer : By using the conditional predicates INSERTING, DELETING and UPDATING in the trigger body, e.g. IF inserting THEN log 1 ELSIF deleting THEN log 2 ELSE log 3 – as used by COURSES_BIUD_ROW to write to COURSES_LOG.

Answer : ALTER TRIGGER name DISABLE / ENABLE disables or enables one trigger; ALTER TABLE table DISABLE ALL TRIGGERS disables all triggers of a table; DROP TRIGGER name removes it. Disabling improves performance when a large amount of data is modified, because the trigger code is not executed.

Answer : LOBs (Large Objects) are datatypes that can hold large amounts of raw binary data, such as graphics images, and large amounts of character data. Oracle extended SQL DDL and DML to support them, and they can also be manipulated using the DBMS_LOB package and OCI (Oracle Call Interface).

Answer : Internal LOBs (CLOB, BLOB, NCLOB) are stored in the database tablespace and support transaction processing like any other scalar datatype. External LOBs (BFILE) are stored in operating-system files with only a pointer stored in the database; they do not support transaction processing and integrity checking.

Answer : CLOB holds single-byte character data; NCLOB holds multi-byte or single-byte fixed-length character data of the national character set; BLOB holds raw binary data such as bitmap images; BFILE refers to data stored in an operating-system file – only a reference to the file is stored in the database.

Answer : LOB data is not stored in the row with the other columns. The row stores a locator, similar to a pointer, that points to the location where the data is actually stored – within the database for internal LOBs or in an operating-system file for external LOBs.

Answer : LONG can contain up to 2 GB whereas a LOB can contain up to 4 GB (as per the notes); a table can contain only one LONG column but more than one LOB column; a subquery cannot select a LONG column but can select a LOB column.

Answer : They are SQL functions used in INSERT and UPDATE to initialise a LOB column to an empty locator. A LOB column must be initialised to an empty locator before data is written to it with OCI or the DBMS_LOB package, e.g. INSERT INTO lob_table (id, clob_col) VALUES (100, EMPTY_CLOB()).

Answer : BFILENAME(directory_alias, filename) returns a BFILE and is used in INSERT and UPDATE to initialise a BFILE column. Directory_alias is the alias of the directory (created with CREATE DIRECTORY) where the file is stored and filename is the name of the file, e.g. BFILENAME('IMAGES', 'BMP1.BMP').

Answer : A directory alias, created with CREATE DIRECTORY (e.g. CREATE DIRECTORY "IMAGES" AS 'c:\bitmaps'), refers to a physical directory in the operating system using a name in Oracle. If files are moved, the alias can be re-pointed to the new physical directory without changing the alias used in BFILE columns. Users need READ privilege on the directory (GRANT READ ON DIRECTORY ...).

Answer : The directory alias is case-sensitive when used in BFILENAME. At creation Oracle converts the alias to uppercase unless the name is given in double quotes, so if proper case is not given in BFILENAME Oracle does not recognise the alias.

Answer : For internal LOBs (CLOB, NCLOB, BLOB) both the locator and the LOB value are deleted. For BFILE (external LOB) only the locator is deleted and the actual file remains, so BFILE data must be deleted manually.

Answer : The SQL functions such as EMPTY_CLOB and BFILENAME deal with the complete LOB column and do not allow modifying a part of it, and normal functions such as SUBSTR cannot be used with LOBs. DBMS_LOB provides procedures and functions such as READ, WRITE, APPEND, ERASE, TRIM, SUBSTR, COPY, GETLENGTH, INSTR and COMPARE.

Answer : Both extract a portion of a LOB value. READ places the extracted piece in the given output variable, whereas SUBSTR returns the extracted value.

Answer : It compares two LOB values and returns 0 if they are the same, 1 if the first is bigger than the second, and -1 if the second is bigger. Offsets specify where in each LOB the comparison begins and nobytes the number of bytes to compare.

Answer : Use DBMS_LOB.INSTR(lob, pattern, offset, occurrence); it returns the position of the pattern or 0 if not found. Before modifying a LOB (e.g. with DBMS_LOB.COPY), the destination LOB must be selected with SELECT ... FOR UPDATE.

Answer : FILEOPEN (opens the file, e.g. in DBMS_LOB.FILE_READONLY mode), FILECLOSE (closes a file), FILECLOSEALL (closes all open files), FILEEXISTS (checks whether the file referenced by the locator exists – non-zero if it does), FILEGETNAME (gets the directory and file name) and FILEISOPEN (checks whether the file is open).

Answer : An object type is a composite, user-defined datatype that contains the data and code for a particular entity and is used to model real-world entities such as a bank account or a customer’s address. It consists of data attributes and methods. An instance of an object type is called an object.

Answer : No – an object type is a template and does not occupy any space. It is created with CREATE TYPE, e.g. CREATE OR REPLACE TYPE project_type AS OBJECT (name VARCHAR2(20), stdate DATE), and can then be used like scalar datatypes such as NUMBER or CHAR.

Answer : To define columns of relational tables, variables in PL/SQL blocks, parameters of PL/SQL subprograms, data attributes of another object type (nested objects) and object tables.

Answer : USER_TYPES contains details of user-defined datatypes and USER_TYPE_ATTRS gives detailed information about each type’s attributes. The DESCRIBE command displays the structure (attributes and methods) of an object type.

Answer : EXECUTE. By default only the owner can refer to an object type; the owner grants EXECUTE (e.g. GRANT EXECUTE ON project_type TO praneeth), after which the other user refers to it with the schema prefix, e.g. srikanth.project_type.

Answer : Every object type has a constructor created by Oracle – a procedure with the same name as the object type that initialises its attributes. It must be passed as many parameters as there are attributes, e.g. project_type('Billing system', '12-dec-2000'); NULL must be given explicitly for an attribute that should be null.

Answer : When a column of a relational table is of an object type – e.g. PROJECT of type PROJECT_TYPE in EMP – the column is called a column object; each row contains an object of that type.

Answer : A method is declared in CREATE TYPE using the MEMBER option (MEMBER FUNCTION or MEMBER PROCEDURE) and defined in the CREATE TYPE BODY command. CREATE TYPE BODY is required only when the object type contains methods.

Answer : Each method is implicitly passed a reference to the object that invokes it; this reference is called SELF. That is why attributes can be accessed inside methods without a qualifier – stdate and self.stdate mean the same.

Answer : Using the dot operator with a table alias prefix, e.g. SELECT e.project.name, e.project.getage() FROM emp e. Without the alias (SELECT project.name FROM emp) SQL does not recognise the attribute and gives ORA-00904: invalid column name.

Answer : A table with a column of an object type, or an object type with an attribute of another type, is dependent on that type. A type with dependents cannot be dropped (ORA-02303: cannot drop or replace a type with type or table dependents) unless the FORCE option of DROP TYPE is used, which makes the dependent objects invalid.

Answer : An object table is a table whose rows are objects (row objects), e.g. CREATE TABLE projects OF project_type. The attributes of the type map to the columns of the table, so rows can be inserted with or without the constructor, queried, updated and deleted like a relational table, and constraints such as a primary key can be defined.

Answer : Each row object in an object table is assigned an object identifier by Oracle. The OID is either system generated or the primary key of the table serves as the OID (specified with OBJECT ID PRIMARY KEY in CREATE TABLE). A row object can be referenced using its OID.

Answer : An attribute declared as REF type (e.g. project REF project_type) stores a reference to a row object. The REF operator gets the reference of a row object, e.g. SELECT 1, 'Praneeth', REF(p) FROM projects p WHERE ...; DEREF de-references a reference to get the object it points to, e.g. SELECT empno, ename, DEREF(project) FROM emp_obt.

Answer : Both allow objects to be compared with operators like > and < and used in ORDER BY. A MAP member function returns the relative position of an instance by mapping it to a value of a predefined scalar type (e.g. returns STDATE) and is called implicitly. An ORDER member function takes another instance as an explicit argument and returns a negative, zero or positive number indicating that SELF is less than, equal to or greater than it; there can be only one, with return type NUMBER. A type can have either MAP or ORDER, not both; with neither, only equality or inequality comparisons are possible.

Answer : A collection is a group of values where all values are of the same type, like a single-dimension array. Oracle provides three types of collections – index-by tables, nested tables and VARRAYs. A table column, an object attribute and the elements of a collection can be of a collection type.

Answer : VARRAY stands for variable-size array. It has a maximum size and can contain from 0 up to that maximum number of elements, and it can be manipulated as a whole or (in PL/SQL) as individual elements. It is stored in-line, i.e. with the rest of the data of the row.

Answer : As a column in a relational table, a PL/SQL variable, a parameter of a PL/SQL procedure or function, a PL/SQL function return type, and a data attribute of an object type.

Answer : With CREATE TYPE array_name AS VARRAY(limit) OF data_type, where limit is the maximum number of elements and data_type can be any standard type or object type, e.g. CREATE TYPE projectlist AS VARRAY(5) OF project_type.

Answer : Using the constructor of the VARRAY type and, inside it, the constructor of the object type for each element, e.g. INSERT INTO emp VALUES (1, 'Ellison', projectlist(project_type('Telephone Billing', 'System Analyst'), project_type('Housing Loans', 'Oracle DBA'))).

Answer : No. In SQL a VARRAY is always manipulated as a single value – SELECT projects(1) FROM emp gives ORA-00904, and individual elements cannot be referenced in INSERT, UPDATE or DELETE; the whole VARRAY must be updated. Individual elements can be handled in PL/SQL using subscripts and collection methods.

Answer : Select the VARRAY into a variable, call EXTEND to make room for a new element, assign the new element at the last position (pl(pl.LAST) := project_type(name, role)), and update the table column with the modified variable.

Answer : A nested table is a table stored in the database as the data of a column of another table. It is like an index-by table, but a nested table can be stored in the database whereas an index-by table cannot, and it allows SELECT, DELETE, UPDATE and INSERT operations on its rows.

Answer : NESTED TABLE column STORE AS storage_table is required when the table has a nested-table column. It specifies the name of the table in which Oracle stores the data of the nested table (e.g. PROJECTS_NT); this table is created and maintained by Oracle.

Answer : The data of a VARRAY is stored as part of the row (in-line), whereas the data of a nested table is stored out-of-row in the separate table named in the NESTED TABLE clause.

Answer : No. Calling the table constructor without any parameter creates an empty table, and an empty table is not equal to NULL.

Answer : As an atomic value – the whole inner table is read or assigned, e.g. copying the projects of employee 1 to employee 3, or DELETE FROM emp WHERE projects IS NULL – or as a table whose individual rows are queried and manipulated using the TABLE operator.

Answer : TABLE takes a single operand of nested-table or VARRAY type and supplies its data as rows to the outer statement. Examples: SELECT * FROM TABLE(SELECT projects FROM emp WHERE empno = 1); INSERT INTO TABLE(SELECT projects FROM emp WHERE empno = 1) VALUES ('BillsOnline.com', 'Web Developer'); UPDATE and DELETE work the same way. Inserting, updating and deleting individual rows is not permitted for VARRAYs.

Answer : COUNT returns the number of elements currently in the collection; LIMIT returns the maximum number of elements for a VARRAY and NULL for a nested table; FIRST and LAST return the smallest and largest index (NULL if empty). For a VARRAY FIRST is always 1 and LAST equals COUNT, but for a nested table LAST may be more than COUNT if items have been deleted.

Answer : EXISTS(n) is TRUE if the nth element exists; PRIOR(n)/NEXT(n) return the preceding/following index (NULL if none) and help traverse a nested table with deleted items; EXTEND appends one null element and EXTEND(n) n elements; TRIM(n) removes elements from the end; DELETE removes all elements, DELETE(n) the nth and DELETE(m,n) elements m to n. These methods can be used only in PL/SQL, not in SQL.

Answer : Normally the programmer knows the SQL statements when writing the program. When the command varies from execution to execution and must be constructed at run time, the SQL statement constructed "on the fly" is called a dynamic SQL statement.

Answer : Until Oracle8 the only way to execute dynamic SQL was the DBMS_SQL package. Oracle8i introduced native dynamic SQL, where dynamic SQL commands are placed directly into PL/SQL blocks; native dynamic SQL is faster than using DBMS_SQL.

Answer : Only when static SQL does not support the required operation, because dynamic SQL affects performance – e.g. to execute a DDL or DCL command that cannot be executed statically in PL/SQL (such as DROP TABLE), or to access a table or view whose name is not known until the program runs.

Answer : A sales table whose name is SALES followed by the four-digit year, e.g. SALES2000. The command is built as cmd := 'DROP TABLE sales' || TO_CHAR(SYSDATE, 'yyyy') and executed with EXECUTE IMMEDIATE cmd.

Answer : EXECUTE IMMEDIATE dynamic_string [INTO {variable[, variable]... | record}] [USING [IN | OUT | IN OUT] bind_argument [, ...]]. dynamic_string contains the command to execute; INTO gives the variables (or a record of a user-defined type or %ROWTYPE) that receive values retrieved by a SELECT; bind_argument values replace the placeholders in the command.

Answer : DDL commands are not allowed in static SQL in PL/SQL. The procedure therefore builds the command as a string (cmd := 'drop table ' || tablename) and executes it with EXECUTE IMMEDIATE cmd, e.g. EXECUTE droptable('oldsales').

Answer : After EXECUTE IMMEDIATE runs the DELETE, the implicit cursor attribute SQL%ROWCOUNT gives the number of rows affected, which the function returns – e.g. DELETESALESROWS(2000) deletes rows from SALES2000 and returns the count.

Answer : A placeholder is a name preceded by a colon (e.g. :prodid) used in the dynamic command to stand for a value missing at writing time. Its value is supplied through the USING clause of EXECUTE IMMEDIATE, e.g. EXECUTE IMMEDIATE cmd USING prodid.

Answer : Placeholders are matched with bind arguments by position, not by name, so a value must be passed for every occurrence. If 2 placeholders are used 3 times in total, 3 bind arguments must be passed (e.g. :x, :x, :y, :x needs USING a, a, b, a – four values for four occurrences).

Answer : No. Placeholders cannot be used for names of schema objects, so 'drop table :table_name' with USING tablename is invalid; the name must be concatenated into the command string.

Answer : With EXECUTE IMMEDIATE and an INTO clause to catch the values, e.g. EXECUTE IMMEDIATE cmd INTO lprice, hprice USING prodid for "select min(price), max(price) from sales<year> where prodid = :1".

Answer : The number of columns to be retrieved must be known when writing the program, and only one record can be retrieved, because only one set of variables can be passed using INTO. If no row is found NO_DATA_FOUND can be handled (e.g. GETSALARY returns NULL).

Answer : Using a cursor variable of a weakly typed REF CURSOR type (TYPE salescursortype IS REF CURSOR). OPEN cursor_variable FOR dynamic_string [USING bind_arguments] executes the query; FETCH cursor_variable INTO variables in a loop (EXIT WHEN cursor_variable%NOTFOUND) processes each row; CLOSE cursor_variable closes it.

Answer : It is a cursor variable whose REF CURSOR type does not have any return type, so it can be opened for any query string at run time with OPEN-FOR.

Answer : An anonymous block is placed in a string and executed with EXECUTE IMMEDIATE, e.g. 'begin hikesal_' || company || '; end;' calls HIKESAL_A or HIKESAL_B depending on the company. This is useful when the procedure to invoke is known only at run time; the static IF version must be modified whenever a company is added.