Question 1 :
SQL is an English like language consisting of commands to store, retrieve, maintain & regulate access to your database.
Question 2 :
SQL*Plus is an application that recognizes & executes SQL commands & specialized SQL*Plus commands that can customize reports, provide help & edit facility & maintain system variables.
Question 3 :
NVL : Null value function converts a null value to a non-null value for the purpose of evaluating an expression. Numeric Functions accept numeric I/P & return numeric values. They are MOD, SQRT, ROUND, TRUNC & POWER.
Question 4 :
Date Functions are ADD_MONTHS, LAST_DAY, NEXT_DAY, MONTHS_BETWEEN & SYSDATE.
Question 5 :
Character Functions are INITCAP, UPPER, LOWER, SUBSTR & LENGTH. Additional functions are GREATEST & LEAST. Group Functions returns results based upon groups of rows rather than one result per row, use group functions. They are AVG, COUNT, MAX, MIN & SUM.
Question 6 :
TTITLE & BTITLE
TTITLE & BTITLE are commands to control report headings & footers.
Question 7 :
COLUMN command define column headings & format data values.
Question 8 :
BREAK command clarify reports by suppressing repeated values, skipping lines & allowing for controlled break points.
Question 9 :
command control computations on subsets created by the BREAK command.
Question 10 :
SET command changes the system variables affecting the report environment.
Question 11 :
SPOOL command creates a print file of the report.
Question 12 :
JOIN is the form of SELECT command that combines info from two or more tables.
Types of Joins are Simple (Equijoin & Non-Equijoin), Outer & Self join.
Equijoin returns rows from two or more tables joined together based upon a equality condition in the WHERE clause.
Non-Equijoin returns rows from two or more tables based upon a relationship other than the equality condition in the WHERE clause.
Outer Join combines two or more tables returning those rows from one table that have no direct match in the other table.
Self Join joins a table to itself as though it were two separate tables.
Question 13 :
Union is the product of two or more tables.
Question 14 :
Intersect is the product of two tables listing only the matching rows.
Question 15 :
Minus is the product of two tables listing only the non-matching rows.
Question 16 :
Correlated Subquery is a subquery that is evaluated once for each row processed by the parent statement. Parent statement can be Select, Update or Delete. Use CRSQ to answer multipart questions whose answer depends on the value in each row processed by parent statement.
Question 17 :
Multiple columns can be returned from a Nested Subquery.
Question 18 :
Sequences are used for generating sequence numbers without any overhead of locking. Drawback is that after generating a sequence number if the transaction is rolled back, then that sequence number is lost.
Question 19 :
Synonyms is the alias name for table, views, sequences & procedures and are created for reasons of Security and Convenience.
Two levels are Public - created by DBA & accessible to all the users. Private - Accessible to creator only. Advantages are referencing without specifying the owner and Flexibility to customize a more meaningful naming convention.
Question 20 :
Indexes are optional structures associated with tables used to speed query execution and/or guarantee uniqueness. Create an index if there are frequent retrieval of fewer than 10-15% of the rows in a large table and columns are referenced frequently in the WHERE clause. Implied tradeoff is query speed vs. update speed. Oracle automatically update indexes. Concatenated index max. is 16 columns.
Question 21 :
Max. columns in a table is 255. Max. Char size is 255, Long is 64K & Number is 38 digits.
Cannot Query on a long column.
Char, Varchar2 Max. size is 2000 & default is 1 byte.
Number(p,s) p is precision range 1 to 38, s is scale -84 to 127.
Long Character data of variable length upto 2GB.
Date Range from Jan 4712 BC to Dec 4712 AD.
Raw Stores Binary data (Graphics Image & Digitized Sound). Max. is 255 bytes.
Mslabel Binary format of an OS label. Used primarily with Trusted Oracle.
Question 22 :
Order of SQL statement execution
Where clause, Group By clause, Having clause, Order By clause & Select.
Question 23 :
Transaction is defined as all changes made to the database between successive commits.
Question 24 :
Commit is an event that attempts to make data in the database identical to the data in the form. It involves writing or posting data to the database and committing data to the database. Forms check the validity of the data in fields and records during a commit. Validity check are uniqueness, consistency and db restrictions.
Question 25 :
Posting is an event that writes Inserts, Updates & Deletes in the forms to the database but not committing these transactions to the database.