rajcode: April 2009

In order to retrieve the information stored in the database, the SQL query language is used. In
the following we restrict our attention to simple SQL queries and defer the discussion of more
complex queries to Section 1.5
In SQL a query has the following (simplified) form (components in brackets [ ] are optional):
select [distinct]
from
[ where ]
[ order by ]
1.2.1 Selecting Columns
The columns to be selected from a table are specified after the keyword select. This operation
is also called projection. For example, the query
select LOC, DEPTNO from DEPT;
lists only the number and the location for each tuple from the relation DEPT. If all columns
should be selected, the asterisk symbol “” can be used to denote all attributes. The query
select from EMP;
retrieves all tuples with all columns from the table EMP. Instead of an attribute name, the select
clause may also contain arithmetic expressions involving arithmetic operators etc.
select ENAME, DEPTNO, SAL 1.55 from EMP;
3
For the different data types supported in Oracle, several operators and functions are provided:
• for numbers: abs, cos, sin, exp, log, power, mod, sqrt, +,−, , /, . . .
• for strings: chr, concat(string1, string2), lower, upper, replace(string, search string,
replacement string), translate, substr(string, m, n), length, to date, . . .
• for the date data type: add month, month between, next day, to char, . . .
The usage of these operations is described in detail in the SQL*Plus help system (see also
Section 2).
Consider the query
select DEPTNO from EMP;
which retrieves the department number for each tuple. Typically, some numbers will appear
more than only once in the query result, that is, duplicate result tuples are not automatically
eliminated. Inserting the keyword distinct after the keyword select, however, forces the
elimination of duplicates from the query result.
It is also possible to specify a sorting order in which the result tuples of a query are displayed.
For this the order by clause is used and which has one or more attributes listed in the select
clause as parameter. desc specifies a descending order and asc specifies an ascending order
(this is also the default order). For example, the query
select ENAME, DEPTNO, HIREDATE from EMP;
from EMP
order by DEPTNO [asc], HIREDATE desc;
displays the result in an ascending order by the attribute DEPTNO. If two tuples have the same
attribute value for DEPTNO, the sorting criteria is a descending order by the attribute values of
HIREDATE. For the above query, we would get the following output:
ENAME DEPTNO HIREDATE
FORD 10 03-DEC-81
SMITH 20 17-DEC-80
BLAKE 30 01-MAY-81
WARD 30 22-FEB-81
ALLEN 30 20-FEB-81
...........................

Wednesday, April 29, 2009

Information Security FAQS with Solutions for JNTU BTech

Explain about the TCP Session Hijacking.
TCP session hijacking" is a technique that involves intercepting a TCP session initiated between two machines in order to hijack it.
In that the authentication check is performed only when opening the session, a pirate who successfully launches this attack is able to take control of the connection throughout the duration of the session

TCP attacks
Although it is hard to do IP spoofing on TCP, it is still can be realized on the specific OS. The attack aims at impersonating another host mostly during the TCP connection establishment phase.
For example:

1) Node A trusts node B (e.g. login with no password)

2) Node C wants to impersonate B with respect to A in opening a TCP connection

3) C kills B (flooding, redirecting or crashing) firstly

4) C sends A an TCP segment in a spoofed IP packet with B‘s address as the source IP and 11000 as the sequence number.

5) A replies with a TCP SYN/ACK segment to B with 54002 as the sequence number

6) C does not receive the segment from A to B, but in order to finish the handshake it has to send an ACK segment with 54002+1 as the acknowledge number to A. C has to guess or predicate the value of 54002.

TCP Spoofing

3. Explain the RSA algorithm in detail.
The RSA algorithm involves three steps: key generation, encryption and decryption.
Key generation
RSA involves a public key and a private key. The public key can be known to everyone and is used for encrypting messages. Messages encrypted with the public key can only be decrypted using the private key. The keys for the RSA algorithm are generated the following way:
Choose two distinct prime numbers p and q
Compute n = pq
n is used as the modulus for both the public and private keys
Compute the totient: .
Choose an integer e such that , and e and share no factors other than 1 (i.e. e and are coprime)
e is released as the public key exponent
Determine d (using modular arithmetic) which satisfies the congruence relation ;
Stated differently, ed − 1 can be evenly divided by the totient (p − 1)(q − 1)
This is often computed using the Extended Euclidean Algorithm
d is kept as the private key exponent
Notes on the above steps:
Step 1: For security purposes, the integers p and q should be chosen uniformly at random and should be of similar bit-length. Prime integers can be efficiently found using a Primality test.
Step 3: PKCS#1 v2.0 and PKCS#1 v2.1 specifies using , where lcm is the least common multiple instead of .
Step 4: Choosing e with a small hamming weight results in more efficient encryption. Small public exponents (such as e=3) could potentially lead to greater security risks.[2]
The public key consists of the modulus n and the public (or encryption) exponent e. The private key consists of the modulus n and the private (or decryption) exponent d which must be kept secret.
For efficiency the following values may be precomputed and stored as part of the private key:
p and q: the primes from the key generation,
and ,
.
Encryption
Alice transmits her public key (n,e) to Bob and keeps the private key secret. Bob then wishes to send message M to Alice.
He first turns M into an integer 0 < title="" href="http://en.wikipedia.org/wiki/RSA#Padding_schemes#Padding_schemes">padding scheme. He then computes the ciphertext c corresponding to:
This can be done quickly using the method of exponentiation by squaring. Bob then transmits c to Alice.
Decryption
Alice can recover m from c by using her private key exponent d by the following computation:
Given m, she can recover the original message M by reversing the padding scheme.
The above decryption procedure works because:
.
Now, since ,
.
The last congruence directly follows from Euler's theorem when m is relatively prime to n. By using the Chinese remainder theorem it can be shown that the equations hold for all m.
This shows that we get the original message back:
A working example
Here is an example of RSA encryption and decryption. The parameters used here are artificially small, but one can also use OpenSSL to generate and examine a real keypair.
Choose two prime numbers
p = 61 and q = 53
Compute n = pq
Compute the totient
Choose e > 1 coprime to 3120
e = 17
Compute d such that e.g., by computing the modular multiplicative inverse of e modulo :
d = 2753
since 17 · 2753 = 46801 = 1 + 15 · 3120.
The public key is (n = 3233, e = 17). For a padded message m the encryption function is:
The private key is (n = 3233, d = 2753). The decryption function is:
For example, to encrypt m = 123, we calculate
To decrypt c = 855, we calculate
.
Both of these calculations can be computed efficiently using the square-and-multiply algorithm for modular exponentiation. In real life situations the primes selected would be much larger, however in our example it would be relatively trivial to factor n, 3233, obtained from the freely available public key back to the primes p and q. Given e, also from the public key, we could then compute d and so acquire the private key.

Tuesday, April 28, 2009

Oracle FAQS for JNTU CSE students andPL/SQL developers

How often should one COMMIT in a PL/SQL loop? / What is the best commit strategy?
Contrary to popular belief, one should COMMIT less frequently within a PL/SQL loop to prevent ORA-1555 (Snapshot too old) errors. The higher the frequency of commit, the sooner the extents in the undo/ rollback segments will be cleared for new transactions, causing ORA-1555 errors.
To fix this problem one can easily rewrite code like this: FOR records IN my_cursor LOOP ...do some stuff... COMMIT;END LOOP;COMMIT;
... to ... FOR records IN my_cursor LOOP ...do some stuff... i := i+1; IF mod(i, 10000) = 0 THEN -- Commit every 10000 records COMMIT; END IF;END LOOP;COMMIT;
If you still get ORA-1555 errors, contact your DBA to increase the undo/ rollback segments.
NOTE: Although fetching across COMMITs work with Oracle, is not supported by the ANSI standard.
What is the difference between stored procedures and functions?
Functions are normally used for computations where as procedures are normally used for executing business logic.
Functions MUST return a value, procedures doesn't need to.
You can have DML (insert,update, delete) statements in a function. But, you cannot call such a function in a SQL query. For example, if you have a function that is updating a table, you cannot call that function from a SQL query.
- select myFunction(field) from sometable; will throw error.
Function returns 1 value only. Procedure can return multiple values (max 1024).
Stored Procedure: supports deferred name resolution. Example while writing a stored procedure that uses table named tabl1 and tabl2 etc..but actually not exists in database is allowed only in during creation but runtime throws error Function wont support deferred name resolution. Stored procedure returns always integer value by default zero. where as function return type could be scalar or table or table values(SQL Server). Stored procedure is precompiled execution plan where as functions are not.
A procedure may modify an object where a function can only return a value.
PS: In earlier releases of Oracle it was better to put as much code as possible in procedures rather than triggers. At that stage procedures executed faster than triggers as triggers had to be re-compiled every time before executed (unless cached). In more recent releases both triggers and procedures are compiled when created (stored p-code) and one can add as much code as one likes in either procedures or triggers.

Oracle FAQS for PL/SQL developers

Can one print to the screen from PL/SQL?
One can use the DBMS_OUTPUT package to write information to an output buffer. This buffer can be displayed on the screen from SQL*Plus if you issue the SET SERVEROUTPUT ON; command. For example:
set serveroutput on
begin
dbms_output.put_line('Look Ma, I can print from PL/SQL!!!');
end;
/
DBMS_OUTPUT is useful for debugging PL/SQL programs. However, if you print too much, the output buffer will overflow. In that case, set the buffer size to a larger value, eg.: set serveroutput on size 200000
If you forget to set serveroutput on type SET SERVEROUTPUT ON once you remember, and then EXEC NULL;. If you haven't cleared the DBMS_OUTPUT buffer with the disable or enable procedure, SQL*Plus will display the entire contents of the buffer when it executes this dummy PL/SQL block.
Note that DBMS_OUTPUT doesn't print blank or NULL lines. To overcome this problem, SET SERVEROUTPUT ON FORMAT WRAP; Look at this example with this option first disabled and then enabled:
SQL> SET SERVEROUTPUT ON
SQL> begin
2 dbms_output.put_line('The next line is blank');
3 dbms_output.put_line();
4 dbms_output.put_line('The above line should be blank');
5 end;
6 /
The next line is blank
The above line should be blank
SQL> SET SERVEROUTPUT ON FORMAT WRAP
SQL> begin
2 dbms_output.put_line('The next line is blank');
3 dbms_output.put_line();
4 dbms_output.put_line('The above line should be blank');
5 end;
6 /
The next line is blank

The above line should be blank

Can one read/write files from PL/SQL?
The UTL_FILE database package can be used to read and write operating system files.
A DBA user needs to grant you access to read from/ write to a specific directory before using this package. Here is an example:
CONNECT / AS SYSDBA
CREATE OR REPLACE DIRECTORY mydir AS '/tmp';
GRANT read, write ON DIRECTORY mydir TO scott;
Provide user access to the UTL_FILE package (created by catproc.sql):
GRANT EXECUTE ON UTL_FILE TO scott;
Copy and paste these examples to get you started:
Write File
DECLARE
fHandler UTL_FILE.FILE_TYPE;
BEGIN
fHandler := UTL_FILE.FOPEN('MYDIR', 'myfile', 'w');
UTL_FILE.PUTF(fHandler, 'Look ma, Im writing to a file!!!\n');
UTL_FILE.FCLOSE(fHandler);
EXCEPTION
WHEN utl_file.invalid_path THEN
raise_application_error(-20000, 'Invalid path. Create directory or set UTL_FILE_DIR.');
END;
/
Read File
DECLARE
fHandler UTL_FILE.FILE_TYPE;
buf varchar2(4000);
BEGIN
fHandler := UTL_FILE.FOPEN('MYDIR', 'myfile', 'r');
UTL_FILE.GET_LINE(fHandler, buf);
dbms_output.put_line('DATA FROM FILE: 'buf);
UTL_FILE.FCLOSE(fHandler);
EXCEPTION
WHEN utl_file.invalid_path THEN
raise_application_error(-20000, 'Invalid path. Create directory or set UTL_FILE_DIR.');
END;
/
NOTE: UTL_FILE was introduced with Oracle 7.3. Before Oracle 7.3 the only means of writing a file was to use DBMS_OUTPUT with the SQL*Plus SPOOL command.

Can one call DDL statements from PL/SQL?
One can call DDL statements like CREATE, DROP, TRUNCATE, etc. from PL/SQL by using the "EXECUTE IMMEDIATE" statement (native SQL). Examples:
begin
EXECUTE IMMEDIATE 'CREATE TABLE X(A DATE)';
end;
begin execute Immediate 'TRUNCATE TABLE emp'; end;
DECLARE
var VARCHAR2(100);
BEGIN
var := 'CREATE TABLE temp1(col1 NUMBER(2))';
EXECUTE IMMEDIATE var;
END;
NOTE: The DDL statement in quotes should not be terminated with a semicolon.
Users running Oracle versions below Oracle 8i can look at the DBMS_SQL package (see FAQ about Dynamic SQL).
[edit]
Can one use dynamic SQL statements from PL/SQL?
Starting from Oracle8i one can use the "EXECUTE IMMEDIATE" statement to execute dynamic SQL and PL/SQL statements (statements created at run-time). Look at these examples. Note that the statements within quotes are NOT semicolon terminated:
EXECUTE IMMEDIATE 'CREATE TABLE x (a NUMBER)';

-- Using bind variables...'
sql_stmt := 'INSERT INTO dept VALUES (:1, :2, :3)';
EXECUTE IMMEDIATE sql_stmt USING dept_id, dept_name, location;

-- Returning a cursor...
sql_stmt := 'SELECT * FROM emp WHERE empno = :id';
EXECUTE IMMEDIATE sql_stmt INTO emp_rec USING emp_id;
One can also use the older DBMS_SQL package (V2.1 and above) to execute dynamic statements. Look at these examples:
CREATE OR REPLACE PROCEDURE DYNSQL AS
cur integer;
rc integer;
BEGIN
cur := DBMS_SQL.OPEN_CURSOR;
DBMS_SQL.PARSE(cur, 'CREATE TABLE X (Y DATE)', DBMS_SQL.NATIVE);
rc := DBMS_SQL.EXECUTE(cur);
DBMS_SQL.CLOSE_CURSOR(cur);
END;
/
More complex DBMS_SQL example using bind variables:
CREATE OR REPLACE PROCEDURE DEPARTMENTS(NO IN DEPT.DEPTNO%TYPE) AS
v_cursor integer;
v_dname char(20);
v_rows integer;
BEGIN
v_cursor := DBMS_SQL.OPEN_CURSOR;
DBMS_SQL.PARSE(v_cursor, 'select dname from dept where deptno > :x', DBMS_SQL.V7);
DBMS_SQL.BIND_VARIABLE(v_cursor, ':x', no);
DBMS_SQL.DEFINE_COLUMN_CHAR(v_cursor, 1, v_dname, 20);
v_rows := DBMS_SQL.EXECUTE(v_cursor);
loop
if DBMS_SQL.FETCH_ROWS(v_cursor) = 0 then
exit;
end if;
DBMS_SQL.COLUMN_VALUE_CHAR(v_cursor, 1, v_dname);
DBMS_OUTPUT.PUT_LINE('Deptartment name: 'v_dname);
end loop;
DBMS_SQL.CLOSE_CURSOR(v_cursor);
EXCEPTION
when others then
DBMS_SQL.CLOSE_CURSOR(v_cursor);
raise_application_error(-20000, 'Unknown Exception Raised: 'sqlcode' 'sqlerrm);
END;
/
What is the difference between %TYPE and %ROWTYPE?
Both %TYPE and %ROWTYPE are used to define variables in PL/SQL as it is defined within the database. If the datatype or precision of a column changes, the program automically picks up the new definition from the database without having to make any code changes.
The %TYPE and %ROWTYPE constructs provide data independence, reduces maintenance costs, and allows programs to adapt as the database changes to meet new business needs.
%TYPE
%TYPE is used to declare a field with the same type as that of a specified table's column. Example:
DECLARE
v_EmpName emp.ename%TYPE;
BEGIN
SELECT ename INTO v_EmpName FROM emp WHERE ROWNUM = 1;
DBMS_OUTPUT.PUT_LINE('Name = ' v_EmpName);
END;
/
%ROWTYPE
%ROWTYPE is used to declare a record with the same types as found in the specified database table, view or cursor. Examples:
DECLARE
v_emp emp%ROWTYPE;
BEGIN
v_emp.empno := 10;
v_emp.ename := 'XXXXXXX';
END;
/

How can I protect my PL/SQL source code?for Oracle developers

How can I protect my PL/SQL source code?
Oracle provides a binary wrapper utility that can be used to scramble PL/SQL source code. This utility was introduced in Oracle7.2 (PL/SQL V2.2) and is located in the ORACLE_HOME/bin directory.
The utility use human-readable PL/SQL source code as input, and writes out portable binary object code (somewhat larger than the original). The binary code can be distributed without fear of exposing your proprietary algorithms and methods. Oracle will still understand and know how to execute the code. Just be careful, there is no "decode" command available. So, don't lose your source!
The syntax is:
wrap iname=myscript.pls oname=xxxx.plb
Please note: there is no way to unwrap a *.plb binary file. You are supposed to backup and keep your *.pls source files after wrapping them.

How can one see if somebody modified any code?In database

How can one see if somebody modified any code?
The source code for stored procedures, functions and packages are stored in the Oracle Data Dictionary. One can detect code changes by looking at the TIMESTAMP and LAST_DDL_TIME column in the USER_OBJECTS dictionary view. Example:
SELECT OBJECT_NAME,
TO_CHAR(CREATED, 'DD-Mon-RR HH24:MI') CREATE_TIME,
TO_CHAR(LAST_DDL_TIME, 'DD-Mon-RR HH24:MI') MOD_TIME,
STATUS
FROM USER_OBJECTS
WHERE LAST_DDL_TIME > '&CHECK_FROM_DATE';
Note: If you recompile an object, the LAST_DDL_TIME column is updated, but the TIMESTAMP column is not updated. If you modified the code, both the TIMESTAMP and LAST_DDL_TIME columns are updated.

Should one use PL/SQL or Java to code procedures and triggers?

Should one use PL/SQL or Java to code procedures and triggers?
Both PL/SQL and Java can be used to create Oracle stored procedures and triggers. This often leads to questions like "Which of the two is the best?" and "Will Oracle ever desupport PL/SQL in favour of Java?".
Many Oracle applications are based on PL/SQL and it would be difficult of Oracle to ever desupport PL/SQL. In fact, all indications are that PL/SQL still has a bright future ahead of it. Many enhancements are still being made to PL/SQL. For example, Oracle 9i supports native compilation of Pl/SQL code to binaries. Not to mention the numerous PL/SQL enhancements made in Oracle 10g and 11g.
PL/SQL and Java appeal to different people in different job roles. The following table briefly describes the similarities and difference between these two language environments:
PL/SQL:
• Can be used to create Oracle packages, procedures and triggers
• Data centric and tightly integrated into the database
• Proprietary to Oracle and difficult to port to other database systems
• Data manipulation is slightly faster in PL/SQL than in Java
• PL/SQL is a traditional procedural programming language
Java:
• Can be used to create Oracle packages, procedures and triggers
• Open standard, not proprietary to Oracle
• Incurs some data conversion overhead between the Database and Java type

• Java is an Object Orientated language, and modules are structured into classes
• Java can be used to produce complete applications
PS: Starting with Oracle 10g, .NET procedures can also be stored within the database (Windows only). Nevertheless, unlike PL/SQL and JAVA, .NET code is not usable on non-Windows systems.

What is the difference between SQL and PL/SQL? for Databse developers

What is the difference between SQL and PL/SQL?
Both SQL and PL/SQL are languages used to access data within Oracle databases.
SQL is a limited language that allows you to directly interact with the database. You can write queries (SELECT), manipulate objects (DDL) and data (DML) with SQL. However, SQL doesn't include all the things that normal programming languages have, such as loops and IF...THEN...ELSE statements.
PL/SQL is a normal programming language that includes all the features of most other programming languages. But, it has one thing that other programming languages don't have: the ability to easily integrate with SQL.
Some of the differences:
• SQL is executed one statement at a time. PL/SQL is executed as a block of code.
• SQL tells the database what to do (declarative), not how to do it. In contrast, PL/SQL tell the database how to do things (procedural).
• SQL is used to code queries, DML and DDL statements. PL/SQL is used to code program blocks, triggers, functions, procedures and packages.
• You can embed SQL in a PL/SQL program, but you cannot embed PL/SQL within a SQL statement.

Cursors Explanation in DBMS for JNTU Computer science students

________________________________________
Cursors
A cursor is a variable that runs through the tuples of some relation. This relation can be a stored table, or it can be the answer to some query. By fetching into the cursor each tuple of the relation, we can write a program to read and process the value of each such tuple. If the relation is stored, we can also update or delete the tuple at the current cursor position.
The example below illustrates a cursor loop. It uses our example relation T1(e,f) whose tuples are pairs of integers. The program will delete every tuple whose first component is less than the second, and insert the reverse tuple into T1.
1) DECLARE

/* Output variables to hold the result of the query: */

2) a T1.e%TYPE;

3) b T1.f%TYPE;

/* Cursor declaration: */

4) CURSOR T1Cursor IS

5) SELECT e, f

6) FROM T1

7) WHERE e < f

8) FOR UPDATE;

9) BEGIN

10) OPEN T1Cursor;

11) LOOP

/* Retrieve each row of the result of the above query

into PL/SQL variables: */

12) FETCH T1Cursor INTO a, b;

/* If there are no more rows to fetch, exit the loop: */

13) EXIT WHEN T1Cursor%NOTFOUND;

/* Delete the current tuple: */

14) DELETE FROM T1 WHERE CURRENT OF T1Cursor;

/* Insert the reverse tuple: */

15) INSERT INTO T1 VALUES(b, a);

16) END LOOP;

/* Free cursor used by the query. */

17) CLOSE T1Cursor;

18) END;

19) .

20) run;
Here are explanations for the various lines of this program:
• Line (1) introduces the declaration section.
• Lines (2) and (3) declare variables a and b to have types equal to the types of attributes e and f of the relation T1. Although we know these types are INTEGER, we wisely make sure that whatever types they may have are copied to the PL/SQL variables (compare with the previous example, where we were less careful and declared the corresponding variables to be of type NUMBER).
• Lines (4) through (8) define the cursor T1Cursor. It ranges over a relation defined by the SELECT-FROM-WHERE query. That query selects those tuples of T1 whose first component is less than the second component. Line (8) declares the cursor FOR UPDATE since we will modify T1 using this cursor later on Line (14). In general, FOR UPDATE is unnecessary if the cursor will not be used for modification.
• Line (9) begins the executable section of the program.
• Line (10) opens the cursor, an essential step.
• Lines (11) through (16) are a PL/SQL loop. Notice that such a loop is bracketed by LOOP and END LOOP. Within the loop we find:
o On Line (12), a fetch through the cursor into the local variables. In general, the FETCH statement must provide variables for each component of the tuple retrieved. Since the query of Lines (5) through (7) produces pairs, we have correctly provided two variables, and we know they are of the correct type.
o On Line (13), a test for the loop-breaking condition. Its meaning should be clear: %NOTFOUND after the name of a cursor is true exactly when a fetch through that cursor has failed to find any more tuples.
o On Line (14), a SQL DELETE statement that deletes the current tuple using the special WHERE condition CURRENT OF T1Cursor.
o On Line (15), a SQL INSERT statement that inserts the reverse tuple into T1.
• Line (17) closes the cursor.
• Line (18) ends the PL/SQL program.
• Lines (19) and (20) cause the program to execute

Simple Programs in PL/SQL for JNTU Students

Simple Programs in PL/SQL
The simplest form of program has some declarations followed by an executable section consisting of one or more of the SQL statements with which we are familiar. The major nuance is that the form of the SELECT statement is different from its SQL form. After the SELECT clause, we must have an INTO clause listing variables, one for each attribute in the SELECT clause, into which the components of the retrieved tuple must be placed.
Notice we said "tuple" rather than "tuples", since the SELECT statement in PL/SQL only works if the result of the query contains a single tuple. The situation is essentially the same as that of the "single-row select" discussed in Section 7.1.5 of the text, in connection with embedded SQL. If the query returns more than one tuple, you need to use a cursor, as described in the next section. Here is an example:
CREATE TABLE T1(

e INTEGER,

f INTEGER

);

DELETE FROM T1;

INSERT INTO T1 VALUES(1, 3);

INSERT INTO T1 VALUES(2, 4);

/* Above is plain SQL; below is the PL/SQL program. */

DECLARE

a NUMBER;

b NUMBER;

BEGIN

SELECT e,f INTO a,b FROM T1 WHERE e>1;

INSERT INTO T1 VALUES(b,a);

END;

.

run;
Fortuitously, there is only one tuple of T1 that has first component greater than 1, namely (2,4). The INSERT statement thus inserts (4,2) into T1.

Control Flow in PL/SQL
PL/SQL allows you to branch and create loops in a fairly familiar way.
An IF statement looks like:
IF THEN ELSE END IF;
The ELSE part is optional. If you want a multiway branch, use:
IF THEN ...

ELSIF THEN ...

... ...

ELSIF THEN ...

ELSE ...

END IF;
The following is an example, slightly modified from the previous one, where now we only do the insertion if the second component is 1. If not, we first add 10 to each component and then insert:
DECLARE

a NUMBER;

b NUMBER;

BEGIN

SELECT e,f INTO a,b FROM T1 WHERE e>1;

IF b=1 THEN

INSERT INTO T1 VALUES(b,a);

ELSE

INSERT INTO T1 VALUES(b+10,a+10);

END IF;

END;

.

run;
Loops are created with the following:
LOOP

/* A list of statements. */

END LOOP;
At least one of the statements in should be an EXIT statement of the form
EXIT WHEN ;
The loop breaks if is true. For example, here is a way to insert each of the pairs (1, 1) through (100, 100) into T1 of the above two examples:
DECLARE

i NUMBER := 1;

BEGIN

LOOP

INSERT INTO T1 VALUES(i,i);

i := i+1;

EXIT WHEN i>100;

END LOOP;

END;

.

run;
Some other useful loop-forming statements are:
• EXIT by itself is an unconditional loop break. Use it inside a conditional if you like.
• A WHILE loop can be formed with
• WHILE LOOP
•
•
•
END LOOP;
• A simple FOR loop can be formed with:
• FOR IN .. LOOP • • • END LOOP; Here, can be any variable; it is local to the for-loop and need not be ________________________________________

Basic Structure of PL/SQL for JNTU students

Basic Structure of PL/SQL
PL/SQL stands for Procedural Language/SQL. PL/SQL extends SQL by adding constructs found in procedural languages, resulting in a structural language that is more powerful than SQL. The basic unit in PL/SQL is a block. All PL/SQL programs are made up of blocks, which can be nested within each other. Typically, each block performs a logical action in he program. A block has the following structure:
DECLARE

/* Declarative section: variables, types, and local subprograms. */

BEGIN

/* Executable section: procedural and SQL statements go here. */

/* This is the only section of the block that is required. */

EXCEPTION

/* Exception handling section: error handling statements go here. */

END;
Only the executable section is required. The other sections are optional. The only SQL statements allowed in a PL/SQL program are SELECT, INSERT, UPDATE, DELETE and several other data manipulation statements plus some transaction control. However, the SELECT statement has a special form in which a single tuple is placed in variables; more on this later. Data definition statements like CREATE, DROP, or ALTER are not allowed. The executable section also contains constructs such as assignments, branches, loops, procedure calls, and triggers, which are all described below (except triggers). PL/SQL is not case sensitive. C style comments (/* ... */) may be used.
To execute a PL/SQL program, we must follow the program text itself by
• A line with a single dot ("."), and then
• A line with run;
As with Oracle SQL programs, we can invoke a PL/SQL program either by typing it in sqlplus or by putting the code in a file and invoking the file in the various ways we learned in Getting Started With Oracle.
________________________________________
Variables and Types
Information is transmitted between a PL/SQL program and the database through variables. Every variable has a specific type associated with it. That type can be
• One of the types used by SQL for database columns
• A generic type used in PL/SQL such as NUMBER
• Declared to be the same as the type of some database column
The most commonly used generic type is NUMBER. Variables of type NUMBER can hold either an integer or a real number. The most commonly used character string type is VARCHAR(n), where n is the maximum length of the string in bytes. This length is required, and there is no default. For example, we might declare:
DECLARE

price NUMBER;

myBeer VARCHAR(20);
Note that PL/SQL allows BOOLEAN variables, even though Oracle does not support BOOLEAN as a type for database columns.
Types in PL/SQL can be tricky. In many cases, a PL/SQL variable will be used to manipulate data stored in a existing relation. In this case, it is essential that the variable have the same type as the relation column. If there is any type mismatch, variable assignments and comparisons may not work the way you expect. To be safe, instead of hard coding the type of a variable, you should use the %TYPE operator. For example:
DECLARE

myBeer Beers.name%TYPE;
gives PL/SQL variable myBeer whatever type was declared for the name column in relation Beers.
A variable may also have a type that is a record with several fields. The simplest way to declare such a variable is to use %ROWTYPE on a relation name. The result is a record type in which the fields have the same names and types as the attributes of the relation. For instance:
DECLARE

beerTuple Beers%ROWTYPE;
makes variable beerTuple be a record with fields name and manufacture, assuming that the relation has the schema Beers(name, manufacture).
The initial value of any variable, regardless of its type, is NULL. We can assign values to variables, using the ":=" operator. The assignment can occur either immediately after the type of the variable is declared, or anywhere in the executable portion of the program. An example:
DECLARE

a NUMBER := 3;

BEGIN

a := a + 1;

END;

.

run;
This program has no effect when run, because there are no changes to the database.
________________________________________

PL/SQL Functionality for Database developer

PL/SQL supports the following: variables, conditions, arrays, and exceptions. Implementations from version 8 of Oracle Database onwards have included features associated with object-orientation.
The underlying SQL functions as a declarative language. Standard SQL—unlike some functional programming languages—does not require implementations to convert tail calls to jumps. The open standard SQL does not readily provide "first row" and "rest of table" accessors, and it cannot easily perform some constructs such as loops. PL/SQL, however, as a Turing-complete procedural language that fills in these gaps, allows Oracle database developers to interface with the underlying relational database in an imperative manner. SQL statements can make explicit in-line calls to PL/SQL functions, or can cause PL/SQL triggers to fire upon pre-defined Data Manipulation Language (DML) events.
PL/SQL stored procedures (functions, procedures, packages, and triggers) performing DML get compiled into an Oracle database: to this extent their SQL code can undergo syntax-checking. Programmers working in an Oracle database environment can construct PL/SQL blocks of functionality to serve as procedures, functions; or they can write in-line segments of PL/SQL within SQL*Plus scripts.
While programmers can readily incorporate SQL DML statements into PL/SQL (as cursor definitions, for example, or using the SELECT ... INTO syntax), Data Definition Language (DDL) statements such as CREATE TABLE/DROP INDEX etc require the use of "Dynamic SQL". Earlier versions of Oracle Database required the use of a complex built-in DBMS_SQL package for Dynamic SQL where the system needed to explicitly parse and execute an SQL statement. Later versions have included an EXECUTE IMMEDIATE syntax called "Native Dynamic SQL" which considerably simplifies matters. Any use of DDL in an Oracle database will result in an implicit commit. Programmers can also use Dynamic SQL to execute DML where they do not know the exact content of the statement in advance.
PL/SQL offers several pre-defined packages for specific purposes. Such PL/SQL packages include:
• DBMS_OUTPUT - for output operations to non-database destinations
• DBMS_JOB - for running specific procedures/functions at a particular time (i.e. scheduling)
• DBMS_XPLAN - for formatting "Explain Plan" output
• DBMS_SESSION - provides access to SQL ALTER SESSION and SET ROLE statements, and other session information.
• DBMS_METADATA - for extracting meta data from the data dictionary (such as DDL statements)
• UTL_FILE - for reading and writing files on disk
• UTL_HTTP - for making requests to web servers from the database
• UTL_SMTP - for sending mail from the database (via an SMTP server)
Oracle Corporation customarily adds more packages and/or extends package functionality with each successive release of Oracle Database.

Saturday, April 25, 2009

DBMS FAQS with Solutions for JNTU BTech

Data base management systems
1. Define following terms.
A.Normalization:
To eliminate redundancy of data i.e. having same information stored at multiple places, which eventually be difficult to maintain and will also increase the size of our database.
With normalization we will have tables with fewer columns which will make data retrieval and insert, update and delete operations more efficient.
(Or)
Let Rbe a relation scheme with a setFof functional dependencies.Decide whether a relation scheme Ris in “good”form.In the case that a relation scheme Ris not in “good”form, decompose it into a set of relation scheme {R1, R2, ..., Rn} such that each relation scheme is in good form the decomposition is a lossless-join decomposition
Preferably, the decomposition should be dependency preserving.
B.Transitive dependency:
Sol: A transitive dependency is an indirect functional dependency, one in which X→Z only by virtue of X→Y and Y→Z.
C. Partial Dependency:
Partial Functional Dependency - A non-key column is dependent on some, but not all the columns in a composite primary key.
In our above example Supplier Address was partially dependent on our composite key columns (Gadgets+Supplier).
D.Multivalued dependency
Sol: A multivalued dependency is a constraint according to which the presence of certain rows in a table implies the presence of certain other rows.
E.Non-prime attribute
Sol: A non-prime attribute is an attribute that does not occur in any candidate key. Employee Address would be a non-prime attribute in the "Employees' Skills" table.

2. Explain various types of Normal forms?
Sol: There are six normal forms.They are
First Normal Form
Second Normal Form
Third Normal Form
Boyce-Codd Normal Form
Fourth Normal Form
Fifth Normal Form
1 NF – No multivalued attributes or repeating groups.
Column values should be atomic, scalar or should be holding single value
No repetition of information or values in multiple columns.
2 NF – 1 NF plus no partial dependency
For second normal form our database should already be in first normal form and every non-key column must depend on entire primary key.
3 NF – 2 NF plus no transitive dependencies
It should already be in Second Normal Form.
There should be no transitive dependency, i.e. we shouldn’t have any non-key column depending on any other non-key column.
BCNF-Boyce Codd normal form-Every determinant should be a candidate key.
4NF- A table is in 4NF if and only if, for every one of its non-trivial multivalued dependencies X →→ Y, X is a super key—that is, X is either a candidate key or a superset thereof.

3. Explain the 4NF? Why it is useful? Explain with example?
Sol: 4NF is concerned with a more general type of dependency known as a multivalued dependency. A table is in 4NF if and only if, for every one of its non-trivial multivalued dependencies X →→ Y, X is a super key—that is, X is either a candidate key or a superset thereof.
Multivalued dependencies
If the column headings in a relational database table are divided into three disjoint groupings X, Y, and Z, then, in the context of a particular row, we can refer to the data beneath each group of headings as x, y, and z respectively. A multivalued dependency X →→ Y signifies that if we choose any x actually occurring in the table (call this choice xc), and compile a list of all the xcyz combinations that occur in the table, we will find that xc is associated with the same y entries regardless of z.
A trivial multivalued dependency X →→ Y is one in which Y consists of all columns not belonging to X. That is, a subset of attributes in a table has a trivial multivalued dependency on the remaining subset of attributes.
A functional dependency is a special case of multivalued dependency. In a functional dependency X → Y, every x determines exactly one y, never more than one.
Example
Consider the following example:
Pizza Delivery Permutations
Restaurant Pizza Variety Delivery Area
A1 Pizza Thick Crust Springfield
A1 Pizza Thick Crust Shelbyville
A1 Pizza Thick Crust Capital City
A1 Pizza Stuffed Crust Springfield
A1 Pizza Stuffed Crust Shelbyville
A1 Pizza Stuffed Crust Capital City
Elite Pizza Thin Crust Capital City
Elite Pizza Stuffed Crust Capital City
Vincenzo's Pizza Thick Crust Springfield
Vincenzo's Pizza Thick Crust Shelbyville
Vincenzo's Pizza Thin Crust Springfield
Vincenzo's Pizza Thin Crust Shelbyville
Each row indicates that a given restaurant can deliver a given variety of pizza to a given area.
The table has no non-key attributes because its only key is {Restaurant, Pizza Variety, Delivery Area}. Therefore it meets all normal forms up to BCNF. It does not, however, meet 4NF. The problem is that the table features two non-trivial multivalued dependencies on the {Restaurant} attribute (which is not a superkey). The dependencies are:
{Restaurant} →→ {Pizza Variety}
{Restaurant} →→ {Delivery Area}
These non-trivial multivalued dependencies on a non-superkey reflect the fact that the varieties of pizza a restaurant offers are independent from the areas to which the restaurant delivers. This state of affairs leads to redundancy in the table: for example, we are told three times that A1 Pizza offers Stuffed Crust, and if A1 Pizza start producing Cheese Crust pizzas then we will need to add multiple rows, one for each of A1 Pizza's delivery areas. There is, moreover, nothing to prevent us from doing this incorrectly: we might add Cheese Crust rows for all but one of A1 Pizza's delivery areas, thereby failing to respect the multivalued dependency {Restaurant} →→ {Pizza Variety}.
To eliminate the possibility of these anomalies, we must place the facts about varieties offered into a different table from the facts about delivery areas, yielding two tables that are both in 4NF:

Varieties By Restaurant
Restaurant Pizza Variety
A1 Pizza Thick Crust
A1 Pizza Stuffed Crust
Elite Pizza Thin Crust
Elite Pizza Stuffed Crust
Vincenzo's Pizza Thick Crust
Vincenzo's Pizza Thin Crust
Delivery Areas By Restaurant
Restaurant Delivery Area
A1 Pizza Springfield
A1 Pizza Shelbyville
A1 Pizza Capital City
Elite Pizza Capital City
Vincenzo's Pizza Springfield
Vincenzo's Pizza Shelbyville

In contrast, if the pizza varieties offered by a restaurant sometimes did legitimately vary from one delivery area to another, the original three-column table would satisfy 4NF.
Ronald Fagin demonstrated that it is always possible to achieve 4NF

4. Explain the 3NF? Give one example?
Sol: Definition states that a table is in 3NF if and only if both of the following conditions hold: The relation R (table) is in second normal form (2NF)
Every non-prime attribute of R is non-transitively dependent (i.e. directly dependent) on every key of R.
A non-prime attribute of R is an attribute that does not belong to any candidate key of R. A transitive dependency is a functional dependency in which X → Z (X determines Z) indirectly, by virtue of X → Y and Y → Z (where it is not the case that Y → X).
This definition states that a table is in 3NF if and only if, for each of its functional dependencies X → A, at least one of the following conditions holds:
X contains A (that is, X → A is trivial functional dependency), or
X is a superkey, or
An example of a 2NF table that fails to meet the requirements of 3NF is:
Tournament Winners
Tournament Year Winner Date of Birth
Indiana Invitational 1998 Al Fredrickson 21 July 1975
Cleveland Open 1999 Bob Albertson 28 September 1968
Des Moines Masters 1999 Al Fredrickson 21 July 1975
Indiana Invitational 1999 Chip Masterson 14 March 1977
Because each row in the table needs to tell us who won a particular Tournament in a particular Year, the composite key {Tournament, Year} is a minimal set of attributes guaranteed to uniquely identify a row. That is, {Tournament, Year} is a candidate key for the table.
The breach of 3NF occurs because the non-prime attribute Winner Date of Birth is transitively dependent on the candidate key {Tournament, Year} via the non-prime attribute Winner. The fact that Winner Date of Birth is functionally dependent on Winner makes the table vulnerable to logical inconsistencies, as there is nothing to stop the same person from being shown with different dates of birth on different records.
In order to express the same facts without violating 3NF, it is necessary to split the table into two:

Tournament Winners
Tournament Year Winner
Indiana Invitational 1998 Al Fredrickson
Cleveland Open 1999 Bob Albertson
Des Moines Masters 1999 Al Fredrickson
Indiana Invitational 1999 Chip Masterson
Player Dates of Birth
Player Date of Birth
Chip Masterson 14 March 1977
Al Fredrickson 21 July 1975
Bob Albertson 28 September 1968

Update anomalies cannot occur in these tables, which are both in 3NF.
5. Explain Atomicity, Consistency, Isolation and Durability?
Sol: Atomicity: Either all operations of the transaction are properly reflected in the database or none are.
Consistency: Execution of a transaction in isolation preserves the consistency of the database.
Isolation: Although multiple transactions may execute concurrently, each transaction must be unaware of other concurrently executing transactions. Intermediate transaction results must be hidden from other concurrently executed transactions. That is, for every pair of transactions Ti and Tj, it appears to Ti that either Tj, finished execution before Ti started, or Tj started execution after Ti finished.
Durability: After a transaction completes successfully, the changes it has made to the database persist, even if there are system failures.
6. Define Transaction? Explain transaction states with neat diagram?
Sol: A transaction is a unit of program execution that accesses and possibly updates various data items. To preserve the integrity of data the database system must ensure:
Transaction State
Active –the initial state; the transaction stays in this state while it is executing
Partially committed –after the final statement has been executed.
Failed --after the discovery that normal execution can no longer proceed.
Aborted –after the transaction has been rolled back and the database restored to its state prior to the start of the transaction. Two options after it has been aborted:
restart the transaction; can be done only if no internal logical error
kill the transaction
Committed –after successful completion

7. Discuss about Conflict Serializability?
Sol: Conflict Serializability:Two schedules are conflict equivalent if:
_ Involve the same actions of the same transactions
_ Every pair of conflicting actions is ordered the same way

If a schedule Scan be transformed into a schedule S´by a series of swaps of non-conflicting instructions, we say that Sand S´are conflict equivalent.
We say that a schedule S is conflict serializableif it is conflict equivalent to a serial schedule
Conflicting Instructions
Instructions li and lj of transactions Ti and Tj respectively, conflict if and only if there exists some item Q accessed by both li and lj, and at least one of these instructions wrote Q.
1. li = read(Q), lj = read(Q). li and lj don’t conflict.
2. li = read(Q), lj = write(Q). They conflict.
3. li = write(Q), lj = read(Q). They conflict
4. li = write(Q), lj = write(Q). They conflict
Intuitively, a conflict between li and lj forces a (logical) temporal order between them. If li and lj are consecutive in a schedule and they do not conflict, their results would remain the same even if they had been interchanged in the schedule.

8.Discuss about View serializability?
Sol:View Serializability :
Let Sand S´be two schedules with the same set of transactions. Sand S´are view equivalentif the following three conditions are met:
1. For each data item Q, if transaction Ti reads the initial value of Q in schedule S, then transaction Ti must, in schedule S´, also read the initial value of Q.
2. For each data item Q if transaction Ti executes read(Q) in schedule S, and that value was produced by transaction Tj (if any), then transaction Ti must in schedule S´ also read the value of Q that was produced by transaction Tj .
3. For each data item Q, the transaction (if any) that performs the final write(Q) operation in schedule S must perform the final write(Q) operation in schedule S´.
As can be seen, view equivalence is also based purely on reads and writes alone.
A schedule S is view serializable it is view equivalent to a serial schedule.
Every conflict serializable schedule is also view serializable.
9. Explain following terms
A. Dense Index:
Dense index—Index record appears for every search-key value in the file.
B.Sparse Index:
Sparse Index: contains index records for only some search-key values.
Applicable when records are sequentially ordered on search-key
To locate a record with search-key value Kwe:
Find index record with largest search-key value < K
Search file sequentially starting at the record to which the index record points
C.Primary Index
Primary index: in a sequentially ordered file, the index whose search key specifies the sequential order of the file.
The search key of a primary index is usually but not necessarily the primary key.
D. Clustered and non clustered:
Clustering index: in a sequentially ordered file, the index whose search key specifies the sequential order of the file. Also called Primary index
Non-clustering index: an index whose search key specifies an order different from the sequential order of the file. Also called Secondary index.

10. How does the strict Two-phase locking protocol ensures serializability?

Sol: According to the two-phase locking protocol, a transaction handles its locks in two distinct, consecutive phases during the transaction's execution:
Phase 1 - Expanding phase: locks are acquired and no locks are released.
Phase 2 - Shrinking phase: locks are released and no locks are acquired.
Strict two-phase locking
The strict two-phase locking (S2PL) class of schedules is the intersection of the 2PL class with the class of schedules possessing the strictness property.
To comply with the S2PL protocol a transaction needs to comply with 2PL, and release its write (exclusive) locks only after it has ended, i.e., being either committed or aborted. On the other hand, read (shared) locks are released regularly during phase 2.
Typically end of phase 1 is safely determined when a transaction has entered its ready state in all its processes (processing has ended, and it is ready to be committed; no additional locking is possible). If several processes (two or more) are involved, then a synchronization point (similar to atomic commitment) among them is needed to determine end of phase 1 for all of them. This is usually too costly, and end of phase 1 is usually postponed to be merged with transaction end (atomic commitment protocol for a multiprocess transaction), which turns S2PL to SS2PL (see below).
S2PL is a special case of 2PL, i.e., the S2PL class is a proper subclass of 2PL.
Strong strict two-phase locking
To comply with the strong strict two-phase locking (SS2PL) protocol a transaction needs to comply with 2PL, and release both its write (exclusive) and read (shared) locks only after it has ended, i.e., being either committed or aborted. A transaction obeying SS2PL can be viewed as having phase 1 that lasts the transaction's entire execution duration, and no phase 2 (or a degenerate phase 2). Thus, only one phase is actually left, and "two-phase" in the name seems to be still utilized due to the historical development of the concept from 2PL. The SS2PL property of a schedule is also called rigorousness, and an SS2PL schedule is also called a rigorous schedule.
SS2PL is a special case of S2PL, i.e., the SS2PL class of schedules is a proper subclass of S2PL (every SS2PL schedule is also an S2PL schedule, but S2PL schedules exist that are not SS2PL).
11.Expalin BCNF? Give one example?
Sol: Boyce-Codd normal form (or BCNF) is a normal form used in database normalization. It is a slightly stronger version of the third normal form (3NF). A table is in Boyce-Codd normal form if and only if, for every one of its non-trivial functional dependencies X → Y, X is a super key—that is, X is either a candidate key or a superset thereof.
Achievability of BCNF
In some cases, a non-BCNF table cannot be decomposed into tables that satisfy BCNF and preserve the dependencies that held in the original table. Beeri and Bernstein showed in 1979 that, for example, a set of functional dependencies {AB → C, C → B} cannot be represented by a BCNF schema.[5] Thus, unlike the first three normal forms, BCNF is not always achievable.
Consider the following non-BCNF table whose functional dependencies follow the {AB → C, C → B} pattern:
Nearest Shops
Person Shop Type Nearest Shop
Davidson Optician Eagle Eye
Davidson Hairdresser Snippets
Wright Bookshop Merlin Books
Fuller Bakery Doughy's
Fuller Hairdresser Sweeney Todd's
Fuller Optician Eagle Eye
For each Person / Shop Type combination, the table tells us which shop of this type is geographically nearest to the person's home.
The candidate keys of the table are:
{Person, Shop Type}
{Person, Nearest Shop}
Because all three attributes are prime attributes (i.e. belong to candidate keys), the table is in 3NF. The table is not in BCNF, however, as the Shop Type attribute is functionally dependent on a non-superkey: Nearest Shop.
The violation of BCNF means that the table is subject to anomalies. For example, Eagle Eye might have its Shop Type changed to "Optometrist" on its "Fuller" record while retaining the Shop Type "Optician" on its "Davidson" record. This would imply contradictory answers to the question: "What is Eagle Eye's Shop Type?" Holding each shop's Shop Type only once would seem preferable, as doing so would prevent such anomalies from occurring:

Shop Near Person
Person Shop
Davidson Eagle Eye
Davidson Snippets
Wright Merlin Books
Fuller Doughy's
Fuller Sweeney Todd's
Fuller Eagle Eye
Shop
Shop Shop Type
Eagle Eye Optician
Snippets Hairdresser
Merlin Books Bookshop
Doughy's Bakery
Sweeney Todd's Hairdresser

In this revised design , the "Shop Near Person" table has a candidate key of {Person, Shop}, and the "Shop" table has a candidate key of {Shop}. Unfortunately, although this design adheres to BCNF, it is unacceptable on different grounds: it allows us to record multiple shops of the same type against the same person. In other words, its candidate keys do not guarantee that the functional dependency {Person, Shop Type} → {Shop} will be respected.
A design that eliminates all of these anomalies (but does not conform to BCNF) is possible.[6] This design consists of the original "Nearest Shops" table supplemented by the "Shop" table described above.

Nearest Shops
Person Shop Type Nearest Shop
Davidson Optician Eagle Eye
Davidson Hairdresser Snippets
Wright Bookshop Merlin Books
Fuller Bakery Doughy's
Fuller Hairdresser Sweeney Todd's
Fuller Optician Eagle Eye
Shop
Shop Shop Type
Eagle Eye Optician
Snippets Hairdresser
Merlin Books Bookshop
Doughy's Bakery
Sweeney Todd's Hairdresser

If a referential integrity constraint is defined to the effect that {Shop Type, Nearest Shop} from the first table must refer to a {Shop Type, Shop} from the second table, then the data anomalies described previously are prevented.
12.Define following terms.
i)Dead lock?
System is deadlocked if there is a set of transactions such that every transaction in the set is waiting for another transaction in the set.
ii) Shared Lock?
Shared(S) mode. Data item can only be read. S-lock is requested using lock-S instruction
iii) Shrinking Phase?
Shrinking Phase:
Transaction may release locks and transaction may not obtain locks.
iv) Growing Phase?
Growing Phase:
Transaction may obtain locks and Transaction may not release locks

SQL Functions FOR Databse developers

Oracle SQL Functions
ABS(n) Absolute value of number
ACOS(n) arc cosine of n
ADD_MONTHS(date,num_months)
Returns date + num_months
ASCII(char) Converts char into a decimal ascii code
ASIN(n) arc sine of n.
ATAN(n) arc tangent of n.
ATAN2(n.m) arc tangent of n and m.
AVG([DISTINCT]n)
Averge value of 'n' ignoring NULLs

BETWEEN value AND value
Where 'x' between 25 AND 100
BFILENAME('directory','filename')
Get the BFILE locator associated with a physical LOB binary file.
CASE Group the data into sub-sets.
CEIL(n) Round n up to next whole number.
CHARTOROWID(char)
Converts a Char into a rowid value.
CHR(n) Character with value n
CONCAT(s1,s2) Concatenate string1 and string2
CONVERT(char_to_convert, new_char_set, old_char_set)
Convert character sets
COS(n) Cosine of number
COSH(n) Hyperbolic Cosine of number
COUNT(*) Count the no of rows returned
COUNT([DISTINCT] expr)
Count the no of rows returned by expr

DECODE IF x THEN return y ELSE return z
DENSE_RANK Calculate the rank of a value in a group
DEREF(e) Return the object reference of argument e.
DUMP(expr,fmt[,start,length])
Convert to dec/hex/oct and display char set

EMPTY_BLOB Return an empty LOB locator (use to empty a column or variable)
EMPTY_CLOB Return an empty LOB locator (use to empty a column or variable)
EXISTS Return TRUE if a subquery returns at least one row
EXP(n) Exponential (e to 'n'th power)

FLOOR(n) Round n down to the next whole number.

GREATEST(expression, expression...)
Returns the largest in a list of expressions.
GROUPING Grouping for superaggregate rows=NULL
(see GROUP BY ROLLUP/CUBE)

HEXTORAW(char) Convert char containing hex digits to a raw value.

IN (list of comma separated values)
Effectively a shorthand for ['x' = y OR 'x' = z...] i.e.
Where 'x' IN ('sales','marketing','recruitment')
INITCAP(char) String with Initial Capitals
INSTR(str, chars[,s[,n]])
Find the 'n'th occurence of 'chars' in 'str'
Starting at position 's'
n and s default to 1
INSTRB (str, chars[,s[,n]])
The same as INSTR, except that 's' and the return value are expressed in bytes,
use for double-byte char sets
IS [NOT] NULL Check for NULL (empty) values
Where 'x' IS NULL;

LAST_DAY(date)Returns the last day of month in Date
LEAST(expression, expression...)
Returns the smallest in a list of expressions
LENGTH(char) Returns the number of characters in char
LENGTHB(char) Returns the number of bytes in char (use for double-byte char sets)
LIKE wildcard/value
Wildcards are [% = any chars] [ _ = any one char]
Where 'x' LIKE 'smith%' [will find 'Smithson']
Where 'x' LIKE 'smith_' [will find 'Smithy']
LN(n) Natural Log of n, where n>0
LOG(b,n) log of n, base b
LOWER(char) Returns character string in lowercase
LPAD(char, n[,PadChar])
Left Pad char with n spaces [or PadChars]
LTRIM(char[,set])
Left Trim char - remove leading spaces [or char set]

MAKE_REF(table,key)
Create a REF to a row of an OBJECT view/table
MAX([DISTINCT] expr)
Maximum value returned by expr
MIN([DISTINCT] expr)
Minimum value returned by expr
MOD(x,y) Remainder of x divided by y
MONTHS_BETWEEN(end_date, start_date)
Number of months between the 2 dates (integer)

NEW_TIME(date, zone1, zone2)
Convert between GMT and US time zones (but not CET)
NEXT_DAY(date,day_of_week)
'12-OCT-01','Monday' will return the next Mon after 12 Oct
NLS_CHARSET_DECL_LEN (bytecount,charset)
Returns the declaration width (no of chars) of an NCHAR column
NLS_CHARSET_ID(varchars)
Returns the char set ID given a charset name
NLS_CHARSET_NAME(charset_id)
Returns the char set name given a charset id
NLS_INITCAP(char[,'NLS_SORT = sort_sequence'])
Returns char in Initial Caps, using an NLS sort_sequence
either the session default or specified directly
NLS_LOWER(char[,'NLS_SORT = sort_sequence'])
Returns char in lower case, using an NLS sort_sequence
either the session default or specified directly
NLSSORT(char[,'NLS_SORT = sort_sequence'])
Return the string of bytes used to sort char, using an NLS sort_sequence
either the session default or specified directly
NLS_UPPER(char[,'NLS_SORT = sort_sequence'])
Returns char in UPPER case, using an NLS sort_sequence
either the session default or specified directly
NVL(expression, value_if_null)
If expression is null, returns value_if_null; if expression is not null, returns expression.
The arguments can have any datatype (Oracle will perform implicit conversion where needed).
PERCENT_RANK Calculate the percent rank of a value in a group.
POWER(m,n) m raised to the nth power

RANK Calculate the rank of a value in a group
RAWTOHEX(raw) Convert raw to a character value containing its hex equivalent
REF(table_alias)
Returns a REF value for an object instance (bound to the variable or row.)
The table alias (correlation variable) is associated with
one row of an object table or an object view in an SQL statement.
REFTOHEX(ref) Convert ref (object type) to a char value containing its hex equivalent.
REPLACE(char, search_str[, replace_str])
ANSI alternative to decode() Replace every occurrence of search_str
with replace_str, replace_str defaults to null.
ROUND(n,d) n rounded to d decimal places (d defaults to 0)
ROUND(date,fmt)
date rounded to fmt
ROWIDTOCHAR(rowid)
Convert a rowid value to VARCHAR2
ROW_NUMBER Assign a unique number to each row of results.
RPAD(char, n[,PadChar])
Right Pad char with n spaces [or PadChars]
RTRIM(char[,set])
Right Trim char - remove trailing spaces [or char set]

SIGN(n) positive = 1, zero = 0, negative = -1
SIN(n) Sine of n in Radians
SINH(n) Hyperbolic Sine of n in Radians
SOUNDEX(char) Returns a char value representing the sound of the words
SQRT(n) Square Root (returns NULL for negative no's)
STDDEV([DISTINCT] n)
Standard deviation of n
SUBSTR(char, s[,l])
A substring of char, starting at character s, length l
SUBSTRB(char, s[,l])
A substring of char, starting at character s, length l
The same as SUBSTR, except that 's', 'l' and the return value are expressed in bytes,
use for double-byte char sets
SUM([DISTINCT] n)
Sum of values of n, ignoring NULLs
SYS_CONTEXT('namespace','attribute_name')
Examine the package associated with the context namespace
Possible attributes are: NLS_TERRITORY, NLS_CURRENCY, NLS_CALENDAR
NLS_DATE_FORMAT, NLS_DATE_LANGUAGE, NLS_SORT, SESSION_USER, CURRENT_USER
CURRENT SCHEMAID,SESSION_USERID, CURRENT_USERID, CURRENT_SCHEMA
note: CURRENT_USER may be different from SESSION_USER within a stored procedure
(e.g an invoker-rights procedure).
SYS_CONTEXT ('USERENV','IP_ADDRESS')
SYS_GUID() Returns a globally unique identifier (16 byte RAW value)
SYSDATE The current system date & time

TAN(n) Tangent of n in Radians
TANH(n) Hyperbolic tangent of n in Radians
TO_CHAR Convert to character String
TO_DATE Convert to date value
TO_LOB(long) Convert LONG values to CLOB or NCLOB values
or convert LONG RAW values to BLOB values
Use only as part of an "INSERT INTO ... SELECT..." subquery.
TO_MULTI_BYTE(char)
Convert single-byte char to multi-byte char.
TO_NUMBER Convert to numeric format
TO_SINGLE_BYTE(char)
Convert multi-byte char to single-byte char.
TRANSLATE('char','search_str','replace_str')
Replace every occurrence of search_str with replace_str
unlike REPLACE() if replace_str is NULL the function returns NULL
TRANSLATE (text USING charset)
Convert text into a specific character set
Use this instead of CONVERT() if either the input or output datatype
is NCHAR or NVARCHAR2.
TRIM(LEADING|TRAILING|BOTH trim_char FROM trim_source)
Returns trim_source as a VARCHAR2 with leading/trailing items removed
trim_char defaults to a space ' ' but may be numeric or char 'A'
TRUNC(i,d) i truncated to d decimal places (d defaults to 0)
TRUNC(date,fmt)
date truncated to nearest fmt

UID User id - unique number
UPPER(char) Returns Chars in uppercase
USER Returns the current Username
USERENV('option')
Can return any of the options: ENTRYID, SESSIONID,
TERMINAL, LANGUAGE, ISDBA, LANG, INSTANCE, CLIENT_INFO

VALUE(correlation_variable)
Return the object instance for a row of an object table
as associated with the correlation_variable (table alias)
VARIANCE([DISTINCT] n)
Variance of n, ignoring NULLs
VSIZE(expr) Value Size - returns the number of bytes used by each row of expr.
This page is not an exhaustive list of all the functions available - to find a complete list of functions for a particular release of Oracle see docs.oracle.com or run this query:

SELECT distinct object_name
FROM all_arguments
WHERE package_name = 'STANDARD';

JAVA lab programs for JNTU II BTech II Sem

Program : 01

/*Write a java program to print all the real solutions of the equation ax2+bx+c=0. Read a, b, c and use the quadratic formula. If discriminant b2-4ac is negative display a message stating that there are no solutions.*/

import java.lang.*;
class roots
{
public static void main(String args[ ])
{
int a = Integer.parseInt(args[0]);
int b = Integer.parseInt(args[1]);
int c = Integer.parseInt(args[2]);
int d = ((b*b)-(4*a*c));
if(d > 0)
{
System.out.println(“Roots are real and distinct”);
}
if(d==0)
{
System.out.println(“Roots are real and equal”);
}
if (d < 0)
{
System.out.println(“There are no solution”);
}
}
}

OUTPUT – Program :01

Program : 02

/*The Fibonacci sequence is defined by the following rule:
The first two values in the sequence are 1 and 1. Every subsequent values is the sum of the two values preceding it. Write a java program that uses both recursive and non recursive functions to print the nth values in the Fibonacci sequence*/

import java.lang.*;
class fibo
{
public static void main(String args[ ])
{
int a, b, n, c, i;
n = Integer.parseInt(args[0]);
a = -1;
b = 1;
i = 1;
System.out.println(“*************Fibonacci Series*************”);
do
{
c = a + b;
System.out.println(c);
a = b;
b = c;
i = i+1;
}
while(i <= n);
}
}

OUTPUT –Program : 02

Program : 03

/*Write a java program that prompts the user for an integer and then prints out all prime numbers up to that integer.*/

import java.io.*;
class prime
{
public static void main(String args[ ])
{
int i, j, n, c;
n = Integer.parseInt(args[0]);
for(i =2; i <= n; i++)
{
c = 0;
for(j =2; j < i; j++)
{
if(i%j == 0)
c+=1;
}
If(c == 0)
System.out.println(i+ “is prime”);
}
}
}

OUTPUT – Program : 03

Program : 04

/*Write a java program to multiply two given matrices.*/

import java.io.*;
class matmul
{
public static void main(String args[ ])throws IOException
{
BufferedReader br = new BufferedReader(new InputStreamReader(System.in));
System.out.println("Enter row size");
int m = Integer.parseInt(br.readLine( ));
System.out.println("Enter column size");
int n = Integer.parseInt(br.readLine());
int a[ ][ ]=new int[m][n];
int b[ ][ ]=new int[m][n];
int c[ ][ ]=new int[m][n];
System.out.println("enter the elements for matirx a:");
for(int i = 0; i < m; i++)
for(int j = 0; j< n; j++)
a[i][j] = Integer.parseInt(br.readLine( ));
System.out.println("enter the elements for matrix b:");
for(int i= 0; i < m; i++)
for(int j = 0; j< n ; j++)
b[i][j] = Integer.parseInt(br.readLine( ));
for(int i = 0;i< m;i++)
{
for(int j =0;j < n;j++)
{
for(int k =0;k < n;k++)
{
c[i][j] = c[i][j] + a[i][k] * b[k][j];
}
}
}
System.out.println("Multiplication of a and b elements is:");
for(int i=0;i< m;i++)
{
for(int j=0; j< n;j++)
{
System.out.println(c[i][j] +"\t");
}
System.out.println( );
}
}
}

OUTPUT – Program : 04

Program : 05

/*Write a java program that reads a line of integers, and then displays each integer, and sum of all the integers*/

import java.io.*;
import java.lang.*;
class array
{
public static void main(String args[ ])throws IOException
{
BufferedReader br = new BufferedReader(new InputStreamReader(System.in));
int size=20, sum=0;
System.out.println(“Enter the size of array:”);
Size = Integer.parseInt(br.readLine( ));
int a[ ] = new int[size];
System.out.println(“enter list of integers”);
for(int i = 0; i < size; i++)
a[i] = Integer.parseInt(br.readLine( ));
for(int i = 0; i < size ; i++)
sum = sum + a[i];
System.out.println(“Entered no’s are:”);
for(int i = 0;i < size; i++)
System.out.println(a[i]);
System.out.println(“sum = “+sum);
}
}

OUTPUT – Program : 05

Program : 06

/*Write a java program that checks whether a given string is a palindrome or not.*/

import java.io.*;
class palindrome
{
public static void main(String args[ ])throws IOException
{
BufferedReader br = new BufferedReader(new InputStreamReader(System.in));
System.out.println(“Enter any string:”);
String S1 = br.readLine( );
String S2 = new String( );
StringBuffer S3 = new StringBuffer(S1);
StringBuffer S4 = S3.reverse( );
System.out.println(S1);
System.out.println(S4);
String S5 = S4.toString( );
if(S1.equals(S5))
System.out.println(“PALINDROME”);
else
System.out.println(“NOT A PALINDROME”);
}
}

OUTPUT – Program : 06

Program – 07

/*Write a java program for sorting a given list of names in ascending order.*/

import java.io.*;
class alphabet
{
public static void main(String args[ ])throws IOException
{
BufferedReader br = new BufferedReader(new InputStreamReader(System.in));
System.out.println(“Enter how many Strings:”);
int n = Integer.parseInt(br.readLine( ));
String x[ ] = new String[n];
System.out.println(“Enter “ +n+ “Strings:”);
for(int i = 0;i< n; i++)
{
x[i] = br.readLine( );
}
String S = new String( );
for(int i = 0; i< n; i++)
{
for(int j = 0;j< n; j++)
{
if(x[i].compareTo(x[j] ) < 0)
{
S = x[i];
x[i] = x[j];
x[j] = S;
}
}
}
System.out.println(“Strings in alphabetical Order”);
for(int i = 0; i < n; i++)
System.out.println(x[i]);
}
}

OUTPUT – Program : 07

Program : 08
/* Write a java program to Implement Stack – ADT.*/

import java.io.*;
class stack
{
int S[ ] = new int[10];
int top;
stack( )
{
top = -1;
}

void push(int item)
{
if(top == 9)
System.out.println(“Stack is Overflow”);
else
S[++top] = item;
}

int pop( )
{
if(top < 0)
{
System.out.println(“Stack is underflow”);
return 0;
}
else
return S[top--];
}
}

class stacktest
{
public static void main(String args[ ])
{
stack MS1 = new stack( );
stack MS2 = new stack( );
for(int i = 0; i< 10;i++)
{
MS1.push(i);
}
for(int i = 10;i < 20; i++)
{
MS2.push(i);
}
System.out.println(“Elements in Stack1”);
for(int i = 0;i < 10; i++)
{
System.out.println(MS1.pop( ));
}
System.out.println(”Elements in stack2”);
for(int i = 10; i< 20; i++)
{
System.out.println(MS2.pop( ) );
}
}
}

OUTPUT – Program : 08

Program : 09
/*Write a java program for implementing Constructors.*/
(i) Default Constructor.
import java.io.*;
class Add
{
int a, b;
Add( )
{
System.out.println(“Default Constructor”);
a = 10;
b = 20;
}
void sum( )
{
System.out.println(“a + b=”+(a+b));
}
}
class addition
{
public static void main(String args[ ])
{
Add test = new Add( );
test.sum( );
}
}

OUTPUT- Program : 09(i)
(ii)Argument Constructor
import java.io.*;
class triangle
{
double base;
double height;
triangle(double b, double h)
{
base = b;
height = h;
}
double area( )
{
return (0.5*base*height);
}
}
class triangle1
{
public static void main(String args[ ])
{
double area1;
triangle t1 = new triangle(10,20);
triangle t2 = new triangle(20,30);
area1 = t1.area( );
System.out.println(“Area of t1=” +area1);
area1 = t2.area( );
System.out.println(“Area of t2=” +area1);
}
}
OUTPUT – Program : 09(ii)
(iii)Copy Constructor

import java.io.*;
class copy
{
int a, b;
public copy(int a1, int b1)
{
a = a1;
b = b1;
}
public copy(copy c)
{
a = c.a;
b = c.b;
}
void view( )
{
System.out.println(“a =”+a+”b = “+b);
}
}
class copytest
{
public static void main(String args[ ])
{
copy test = new copy(10, 15);
System.out.println(“First Constructor”);
test.view( );
copy test1 = new copy(test);
System.out.println(“Second Constructor”);
test1.view( );
}
}

OUTPUT – Program : 09(iii)

Program : 10

/*Write a java program to implement Simple Inheritances.*/

import java.io.*;
class base
{
int x, y;
void viewxy( )
{
System.out.println(“x = “+x+”y = ” +y);
}
void baseAdd( )
{
System.out.println(“x + y = ” +(x+y));
}
}
class derived extends base
{
int z;
void viewz( )
{
System.out.println(“z = “ +z);
}
void derivedsum( )
{
System.out.println(“x + y + z=” +(x+y+z));
}
}
class simpleinheritance
{
public static void main(String args[ ])
{
base b = new base( );
derived d = new derived( );
b.x = 1;
b.y = 2;
System.out.println(“Base class values”);
b.viewxy( );
b.baseAdd( );
System.out.println(“Derived class”);
d.x = 4;
d.y = 5;
d.z = 6;
d.viewxy( );
d.viewz( );
d.derivedsum( );
}
}

OUTPUT – Program – 10

Program : 11

/*Write a java program to Implement Interfaces.*/

import java.io.*;
interface shape
{
double pi = 3.14;
double volume(double a, double b);
}
class sphere implements shape
{
public double volume(double a, double b)
{
return(4.0/3.0*pi*a*a*a);
}
}
class cone implements shape
{
public double volume(double a, double b)
{
return((1.0/3.0)*pi*a*a*b);
}
}
class testinterface
{
public static void main(String args[ ])
{
sphere s = new sphere( );
cone c = new cone( );
shape s1;
s1 = s;
System.out.println(“Volume of the sphere = “+s1.volume(1.0,0));
System.out.println(“Volume of the cone = “+s1.volume(1.0,1.0));
}
}

OUTPUT – Program : 11

Program : 12

/*Write a java program implementing Exception Handling.*/

import java.io.*;
class ownException extends Exception
{
ownException(String msg)
{
super(msg);
}
}
class Test
{
Public static void main(String args[ ])
{
int marks = 101;
try
{
if(marks > 100)
{
throw new ownException(“Mark is greater than 100”);
}
}

catch(ownException e)
{
System.out.println(“Exception caught”);
System.out.println(e.getMessage( ));
}

finally
{
System.out.println(“End of the Program”);
}
}
}

OUTPUT – Program : 12

Program : 13

/*Write a java program on threads.*/

import java io.*;
public class tables
{
public static void main(String args[ ])
{
System.out.println(“Multiplicaion tables using threads”);
new tablethree( ).start( );
new tablefive( ).start( );
new tableseven( ).start( );
}
}
class tablethree extends thread
{
public void run( )
{
System.out.println(“Multiplication Table THREE : “);
for(int i = 1;i <= 10 ; i++)
System.out.println(i+ ”x” + ”3=” +(i*3));
}
}
class tablefive extends thread
{
publlic void run( )
{
System.out.println(” Multiplication Table FIVE”);
for(int i =1 ;i <= 10; i++)
System.out.println(i+ ”x” + ”5=” +(i*5));
}
}
class tableseven extends thread
{
public void run( )
{
System.out.println(”Multiplication Table SEVEN:”);
for(int i = 1; i <= 10; i++)
System.out.println(i+ ”x” + ”7=” +(i*7));
}
}

OUTPUT – Program : 13

Program : 14

/*Write a java program to Implement MOUSE Events. */

import java.awt.*;
import java.awt.event.*;
import java.applet.*;
/*

*/
public class mm1 extends Applet
implements MouseListener, MouseMotionListener
{
String msg = " ";
int mouseX = 0, mouseY=0;
public void init( )
{
addMouseListener(this);
addMouseMotionListener(this);
}
public void mouseClicked(MouseEvent me)
{
mouseX = 0;
mouseY =10;
msg = "Mouse Clicked";
repaint( );
}
public void mouseEntered(MouseEvent me)
{
mouseX = 0;
mouseY = 10;
msg = "Mouse Entered";
repaint( );
}
public void mouseExited(MouseEvent me)
{
mouseX = 0;
mouseY = 10;
msg = "mouse Exited";
repaint( );
}
public void mousePressed(MouseEvent me)
{
mouseX = me.getX( );
mouseY = me.getY( );
msg = "Down";
repaint( );
}
public void mouseReleased(MouseEvent me)
{
mouseX = me.getX( );
mouseY = me.getY( );
msg = "Up";
repaint( );
}
public void mouseDragged(MouseEvent me)
{
mouseX = me.getX( );
mouseY = me.getY( );
msg = " * ";
showStatus("Dragged mouse at" + mouseX + ", " + mouseY);
repaint( );
}
public void mouseMoved(MouseEvent me)
{
showStatus("Moving mouse at " + me.getX( ) + ", " + me.getY( ) );
}
public void paint(Graphics g)
{
g.drawString(msg, mouseX, mouseY);
}
}

OUTPUT – Program : 14

Program : 15

/*Write a java program that Implements KEYBOARD Events.*/

import java.awt.*;
import java.awt.event.*;
import java.applet.*;
/*
*/
public class SimpleKey extends Applet implements KeyListener
{
String msg = " ";
int X = 10, Y = 20;
public void init( )
{
addKeyListener ( this ) ;
requestFocus( );
}
public void keyPressed(KeyEvent ke)
{
showStatus("Key Down");
}
public void keyReleased(KeyEvent ke)
{
showStatus("Key up");
}
public void keyTyped(KeyEvent ke)
{
msg += ke.getKeyChar( );
repaint( );
}
public void paint(Graphics g)
{
g.drawString(msg, X, Y);
}
}

OUTPUT – Program : 15

Applet Viewer :

Program : 16

/*Write a java program on life – Cycle of Applet*/

import java.awt.*;
import java.applet.*;
public class lifecycle extends Applet
{
Public void init( )
{
String out;
public void init( )
{
setBackground(Color.blue);
setForeground(Color.yellow);
System.out.println(“init”);
out = “init -> “ ;
}
public void start( )
{
System.out.println(“start”);
out+= “start ->”;
}
public void stop( )
{
System.out.println(“stop”);
}
public void destroy( )
{
System.out.println(“destroy”);
}
public void paint(Graphics g)
{
out+=”paint->”;
g.drawString(out, 100, 50);
}
}

/*
*/

OUTPUT – Program : 16

Applet Viewer:

Program : 17

/*Write a java program for a Calculator using AWT Components.*/

import java.awt.*;
import java.awt.event.*;
import java.applet.Applet;
public class calc extends Applet implements ActionListener
{
Label l1,l2;
TextField t1,t2,t3;
Button add1,sub,mul,div;
public void init( )
{
l1 = new Label("First No:");
add(l1);
l2 = new Label("Second No:");
add(l2);
t1 = new TextField(10);
add(t1);
t2 = new TextField(10);
add(t2);
add1 = new Button("+");
add(add1);
add1.addActionListener(this);
sub = new Button("-");
add(sub);
sub.addActionListener(this);
mul = new Button("*");
add(mul);
mul.addActionListener(this);
div = new Button("/");
add(div);
div.addActionListener(this);
t3 = new TextField(10);
add(t3);
}

public void actionPerformed(ActionEvent e)
{
if(e.getSource( ) == add1)
{
int sum = Integer.parseInt(t1.getText( )) + Integer.parseInt(t2.getText( ));
t3.setText(String.valueOf(sum));
}
if(e.getSource( ) == sub)
{
int sum = Integer.parseInt(t1.getText( )) - Integer.parseInt(t2.getText( ));
t3.setText(String.valueOf(sum));
}
if(e.getSource( ) == mul)
{
int sum = Integer.parseInt(t1.getText( )) * Integer.parseInt(t2.getText( ));
t3.setText(String.valueOf(sum));
}
if(e.getSource( ) == div)
{
int sum = Integer.parseInt(t1.getText( )) / Integer.parseInt(t2.getText( ));
t3.setText(String.valueOf(sum));
}
}
}
/*
*/

OUTPUT – Program : 17

Program : 18

/*Write a java program on clipping.*/

import java.awt.*;
import java.applet.*;
public class rec extends Applet
{
public void paint(Graphics g)
{
g.setClip(50,50,150,150);
for(int i = 0; i < 300; i+=20)
for(int j = 0; j < 300; j +=20)
g.drawOval(i,j,25,25);
setBackground(Color.blue);
setForeground(Color.white);
}
}
/*
*/

OUTPUT – Program : 18

Program : 19

/*Write a java program for Different Layouts.*/
(i)Border layout
import java.awt.*;
class bl extends Frame
{
bl(String s)
{
super(s);
setSize(300,140);
add(new Button(“North”),BorderLayout.NORTH);
add(new Button(“East”),BorderLayout.EAST);
add(new Button(“South”),BorderLayout.SOUTH);
add(new Button(“West”),BorderLayout.WEST);
add(new Button(“Center”),BorderLayout.CENTER);
setVisible(true);
}
}
class test2
{
public static void main(String args[ ])
{
bl b = new bl(“Border layout”);
}
}
OUTPUT – Program : 19

(ii) Grid Layout
import java.awt.*;
class gl extends Frame
{
gl(String s)
{
super(s);
setSize(300,140);
setLayout(new GridLayout(3,4));
for(int i=1; i<=12;i++)
add(new Button(“Button No” +i));
setVisible(true);
}
}
class test1
{
public static void main(String args[ ]){
gl g=new gl(“Grid Layout”);
}
}

OUTPUT – Program : 19(ii) – Grid Layout

(iii) Flow Layout
import java.awt.*;
class bf extends Frame
{
bf(String s)
{
super(s);
setSize(300,140);
setLayout(new FlowLayout( ));
for(int i=1 ; i<= 9;i++)
add(new Button (“Button No” +i));
setVisible(true);
}
}
class test3
{
public static void main(String args[ ])
{
bf b = new bf(“flow Layout”);
}
}

OUTPUT – Program : 19(iii) – Flow Layout

Program : 20

/*Write a java program for different Components.*/
(i)Label
import java.awt.*;
import java.applet.*;
public class label extends Applet
{
public void init( )
{
setLayout(new GridLayout(3,1));
setBackground(Color.yellow);
Label l1 = new Label("BSIT");
Label l2 = new Label("IT",Label.CENTER);
Label l3 = new Label("Java",Label.LEFT);
add(l1);
add(l2);
add(l3);
}
}
/*
*/

OUTPUT – Program : 20(i)

(ii) Text box
import java.awt.*;
import java.applet.*;
public class text extends Applet
{
public void init( )
{
setLayout(new GridLayout(2,2));
Label l1 = new Label(“Username:”,Label.CENTER);
TextField t1 = new TextField( );
Label l2 = new Label(“password”,Label.CENTER);
TextField t2 = new TextField( );
t2.setEchoChar(‘#’);
add(l1);
add(t1);
add(l2);
add(t2);
}
}
/*
*/

OUTPUT – Program : 20(ii)

(iii) CheckBox

import java.applet.*;
import java.awt.*;
public class check extends Applet
{
public void init( )
{
setLayout(new GridLayout(3,1));
Checkbox c1 = new Checkbox("Tamil",true);
Checkbox c2 = new Checkbox("English");
Checkbox c3 = new Checkbox("Maths");
add(c1);
add(c2);
add(c3);
}
}
/*
*/

OUTPUT – Programs : 20(iii)

(iv) Choice

import java.awt.*;
import java.applet.*;
public class choice extends Applet
{
public void init( )
{
Choice color = new Choice( );
color.add("Red");
color.add("Green");
color.add("Blue");
color.add("Cyan");
add(new Label("Normal Appearance"));
add(color);
}
}
/*
*/

OUTPUT – Program : 20(iv)

(v) CheckBox Group

import java.awt.*;
import java.applet.*;
public class checkbg extends Applet
{
public void init( )
{
setLayout(new GridLayout(3,1));
CheckboxGroup cbg = new CheckboxGroup( );
Checkbox c1 = new Checkbox("B.Tech",cbg,true);
Checkbox c2 = new Checkbox("M.Tech",cbg,false);
Checkbox c3 = new Checkbox("M.C.A",cbg,false);
add(c1);
add(c2);
add(c3);
}
}
/*
*/

OUTPUT – Program : 20(v)

(vi) Frame

import java.awt.*;
class frame
{
public static void main(String args[ ])
{
Frame f = new Frame("This is my Frame");
f.setBackground(Color.red);
f.setSize(300,100);
f.setVisible(true);
System.out.println("Return to DOS prompt and Press Control+C for quit");
}
}

OUTPUT – Program :20(vi)

(vii) Button

import java.awt.*;
class frameb
{
public static void main(String args[ ])
{
Button b;
Frame f =new Frame("This is my Button Frame");
f.setSize(250,150);
b = new Button("Press Me");
f.add(b);
f.setLocation(200,200);
f.setVisible(true);
System.out.println("Return to DOS prompt and Press Control+C for quit");
}
}

OUTPUT – Program : 20(vii)

(viii) Menu Bar

import java.awt.*;
public class mymenu
{
public static void main(String arg[ ])
{
Frame f = new Frame("The is test for Menu");
MenuBar main = new MenuBar( );
f.setMenuBar(main);
Menu filemenu = new Menu("File");
Menu editmenu = new Menu("Edit");
Menu helpmenu = new Menu("Help");
main.add(filemenu);
main.add(editmenu);
main.add(helpmenu);
MenuItem new1 = new MenuItem("New");
MenuItem open = new MenuItem("Open");
MenuItem close = new MenuItem("Close");
MenuItem line = new MenuItem("-");
CheckboxMenuItem print = new CheckboxMenuItem("Print");
MenuItem exit = new MenuItem("Exit");
filemenu.add(new1);
filemenu.add(open);
filemenu.add(close);
filemenu.add(line);
filemenu.add(print);
filemenu.add(exit);
MenuItem cut = new MenuItem("Cut");
MenuItem copy = new MenuItem("Copy");
MenuItem paste = new MenuItem("Paste");
MenuItem undo = new MenuItem("Undo");
editmenu.add(cut);
editmenu.add(copy);
editmenu.add(paste);
editmenu.addSeparator( );
editmenu.add(undo);
undo.setEnabled(false);
Menu more = new Menu("More");
helpmenu.add(more);
more.add("commands");
more.add("about");
f.setSize(200,200);
f.setVisible(true);
}
}

OUTPUT – Program : 20(viii)

rajcode

Thursday, April 30, 2009

AGGREGATE FUNCTIONS IN DATABASE FOR JNTU BTECH STUDENTS

SQL BASICS-II FOR JNTU BTECH STUDENTS

SQL for JNTU BTECH STUDENTS