SQL STUDY NOTES

SQL
SQL is an English like language consisting of commands to store, retrieve, maintain & regulate access to your database.
SQL*Plus
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.
NVL
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.
Date Functions
Date Functions are ADD_MONTHS, LAST_DAY, NEXT_DAY, MONTHS_BETWEEN & SYSDATE.
Character Functions
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.
TTITLE & BTITLE
TTITLE & BTITLE are commands to control report headings & footers.
COLUMN
COLUMN command define column headings & format data values.
BREAK
BREAK command clarify reports by suppressing repeated values, skipping lines & allowing for controlled break points.
COMPUTE
command control computations on subsets created by the BREAK command.
SET
SET command changes the system variables affecting the report environment.
SPOOL
SPOOL command creates a print file of the report.
JOIN
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.
Union
Union is the product of two or more tables.
Intersect
Intersect is the product of two tables listing only the matching rows.
Minus
Minus is the product of two tables listing only the non-matching rows.
Correlated Subquery
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.
Multiple columns
Multiple columns can be returned from a Nested Subquery.
Sequences
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.
Synonyms
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.
Indexes
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.
Data types
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.
Order of SQL statement execution
Where clause, Group By clause, Having clause, Order By clause & Select.
Transaction
Transaction is defined as all changes made to the database between successive commits.
Commit
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.
Posting
Posting is an event that writes Inserts, Updates & Deletes in the forms to the database but not committing these transactions to the database.
Rollback
Rollback causes work in the current transaction to be undone.
Savepoint
Savepoint is a point within a particular transaction to which you may rollback without rolling back the entire transaction.
Set Transaction
Set Transaction is to establish properties for the current transaction.
Locking
Locking are mechanisms intended to prevent destructive interaction between users accessing data. Locks are used to achieve.
Consistency
Consistency : Assures users that the data they are changing or viewing is not changed until the are thro’ with it.
Integrity
Assures database data and structures reflects all changes made to them in the correct sequence. Locks ensure data integrity and maximum concurrent access to data. Commit statement releases all locks. Types of locks are given below.
Data Locks protects data i.e. Table or Row lock.
Dictionary Locks protects the structure of database object i.e. ensures table’s structure does not change for the duration of the transaction.
Internal Locks & Latches protects the internal database structures. They are automatic.
Exclusive Lock allows queries on locked table but no other activity is allowed.
Share Lock allows concurrent queries but prohibits updates to the locked tables.
Row Share allows concurrent access to the locked table but prohibits for a exclusive table lock.
Row Exclusive same as Row Share but prohibits locking in shared mode.
Shared Row Exclusive locks the whole table and allows users to look at rows in the table but prohibit others from locking the table in share or updating them.
Share Update are synonymous with Row Share.
Deadlock
Deadlock is a unique situation in a multi user system that causes two or more users to wait indefinitely for a locked resource. First user needs a resource locked by the second user and the second user needs a resource locked by the first user. To avoid dead locks, avoid using exclusive table lock and if using, use it in the same sequence and use Commit frequently to release locks.
Mutating Table
Mutating Table is a table that is currently being modified by an Insert, Update or Delete statement. Constraining Table is a table that a triggering statement might need to read either directly for a SQL statement or indirectly for a declarative Referential Integrity constraints. Pseudo Columns behaves like a column in a table but are not actually stored in the table. E.g. Currval, Nextval, Rowid, Rownum, Level etc.
SQL*Loader
SQL*Loader is a product for moving data in external files into tables in an Oracle database. To load data from external files into an Oracle database, two types of input must be provided to SQL*Loader : the data itself and the control file. The control file describes the data to be loaded. It describes the Names and format of the data files, Specifications for loading data and the Data to be loaded (optional). Invoking the loader sqlload username/password controlfilename.

Oracle on Linux/Unix questions
Database, Unix/Linux interview questions

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

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