SQL

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

Consistency : Assures users that the data they are changing or viewing is not changed until the are thro' with it.

command control computations on subsets created by the BREAK command.

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.

CREATE TABLE .. AS SELECT command

Explanation:

To copy only the structure, the WHERE clause of the SELECT command should contain a FALSE statement as in the following.

CREATE TABLE NEWTABLE AS SELECT * FROM EXISTINGTABLE WHERE 1=2;

If the WHERE condition is true, then all the rows or rows satisfying the condition will be copied to the new table.

Data Definition Language (DDL).

Insert,

update,

delete,

select,

references,

index,

execute,

alter,

all.

It is used to drop constraints specified on the table.

REVOKE.

sal = 11000, comm = 1000.

Which Oracle supplied package can you use to output values and messages from database triggers, stored procedures and functions within SQL*Plus?

1. DBMS_DISPLAY

2. DBMS_OUTPUT

3. DBMS_LIST

4. DBMS_DESCRIBE

Method 1:

Select length (translate (trim (column_name),' +-.0123456789',' ')) from dual ;

Will give you a zero if it is a number or greater than zero if not numeric (actually gives the count of non numeric characters)

Method 2:

select instr(translate('wwww',

'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ',

'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX'),'X')

FROM dual;

It returns 0 if it is a number, 1 if it is not.

SELECT REPLACE(TRANSLATE(LTRIM(RTRIM('!! ATHEN !!','!'), '!'), 'AN', '**'),'*','TROUBLE') FROM DUAL;?

TROUBLETHETROUBLE.

A stored function must return a value based on conditions that are determined at runtime. Therefore, the SELECT statement cannot be hard-coded and must be created dynamically when the function is executed. Which Oracle supplied package will enable this feature?

1. DBMS_DDL

2. DBMS_DML

3. DBMS_SYN

4. DBMS_SQL

True.

USER_CONSTRAINTS.

The privilege receiver can further grant the privileges he/she has obtained from the owner to any other user.

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

Sort the Rows:

SELECT column1, column2, ... FROM table_name ORDER BY columnX, columnY, ..

SELECT column1, column2, ... FROM table_name ORDER BY columnX DESC

SELECT column1, column2, ... FROM table_name ORDER BY columnX DESC, columnY ASC

UPDATE table_name SET column_name = new_value WHERE column_name = some_value