RBN 4 STUDENTS - Built a strong New Generation !

Procedures in Oracle

2009-11-26T03:43:00.000-08:00

ORACLE PROCEDURE

A procedure is a schema object that consists of a set of SQL statements and other PL/SQL constructs, grouped together, stored in the database.it can be executed as a unit to solve a specific problem or perform a set of related tasks. Procedures permit the caller to provide parameters that can be input only, output only, or input and output values.

Procedures and functions in oracle are identical except that functions always return a single value to the caller but procedures can return many values

The syntax for a procedure is:
CREATE [OR REPLACE] PROCEDURE procedure_name
[ (parameter [,parameter]) ]
IS
[declaration_section]
BEGIN
executable_section
[EXCEPTION
exception_section]
END [procedure_name];

Eg :

create or replace procedure acc_bal

(

loancd in varchar2,

accno in number,

bal out number

)

ln_bal number;

tr_bal number;

begin

select nvl(sum(decode(type,'C',-1*amount,amount)),0) into ln_bal from loan_tran_dtl where loan_no=loancd and account_no=accno;

select nvl(sum(decode(type,'C',-1*amount,amount)),0) into tr_bal from tran_detail where ref_id=loancd and

account_no=accno;

bal:=ln_bal+tr_bal;

end;

BOOT

2009-10-01T04:17:00.000-07:00

Boot

The process by which a computer is started up and its operating system kernel is loaded into RAM is called the boot, or bootstrap, process. The basic steps are the:

Execute a hardware self-test.
Look in a predefined place for the boot sector and load this code.
Execute the boot sector program to load other programs.
Execute these programs to load still other programs or to configure the operating environment.
Repeat the previous step as often as dictated by the programs being loaded and by their initialization code.

BOOTCONF.SYS

It is configurations file that specifies how a diskless workstation can boot the operating system in order to access the network.

Boot ROM

It is a ROM (read-only memory) chip used in diskless workstations to enable these machines to boot and connect to a network.

BIOS -BASIC INPUT/OUTPUT SYSTEM

2009-10-01T04:15:00.000-07:00

BIOS (Basic Input/Output System)

The BIOS (Basic Input/Output System) is a collection of services on a ROM. It enables hardware and software, operating systems and applications, and also applications and users to communicate with each other. The BIOS services are loaded automatically into specific addresses. Also these services should always be accessible. BIOS services are updated and expanded to handle newer devices and greater demands. When you replace the ROM chip in your computer with an upgrade chip you will get a new BIOS. Like the standard BIOS, BIOS extensions are also implemented on a ROM chip located on a Mother Board or an expansion board.

Interview Questions

2009-10-01T04:14:00.000-07:00

Common Interview Questions and Answers:

How long would you expect to work for us if hired?

You can answer like: I'd like it to be a long time Or As long as we both feel I'm doing a good job.

Explain how you would be an asset to this organization.

This question gives you a chance to highlight your best points as they relate to the position being discussed. Give a little advance thought to this relationship.

Why should we hire you?

Point out how your assets meet the goals of the organization. Do not mention any other candidates to make a comparison.

What is your greatest strength?

Say positive answers. You can say: your ability to prioritize, your problem solving skills, the ability to work under great pressure, leadership skills, professional skills, positive attitude etc.

What is more important to you: the money or the work?

You have to say Money is always important, but the work is the most important. There is no better answer to say.

Antivirus Program

2009-09-29T01:41:00.001-07:00

Anti-Virus Program

It is used for detecting or removing a computer virus. This program looks for suspicious activity, such as attempts to intercept a BIOS or other low-level call, unnecessary disk access, and attempts to format or delete files. Sometimes the anti-virus program detects a pattern characteristic of a particular virus. Some anti-virus programs are TSR (terminate-and-stay-resident) programs, which monitor computer activity constantly, looking for indications of a virus. In some cases, these types of programs can be extremely annoying and very processor intensive. So users have been known to remove an anti-virus TSR program from memory out of frustration. Other anti-virus programs are intended to be run periodically. When they are run, the

Programs look for the tell-tale signs (known as signatures) of particular viruses and these programs are minimally disruptive. That is their effectiveness is directly proportional to the frequency with which they are used. Antivirus programs must be updated regularly because the coding for computer viruses is constantly changing. Also it is very important to test every new release of anti-virus programs thoroughly. Some antivirus programs uses a large amount of working memory.

ODBC -OPEN DATABASE CONNECTIVITY

2009-09-29T00:52:00.000-07:00

Open Database Connectivity (ODBC)

ODBC provides a common Application Programming Interface (API) to underlying database systems. ODBC communicates with the database through a library driver. Depending on the database being used, a networking driver may be required to connect to a remote database.

ODBC structure.

The unique feature of ODBC is that none of its functions are database-vendor specific. That is, you can use the same code to perform queries against a Microsoft Access table or an Informix database with little or no modification. ODBC has developed into a standard adopted into many products, including Visual Basic, Visual C++, FoxPro. Using ODBC will be more portable but slower than using the Oracle7 or Sybase libraries.

Common Interview Questions

2009-09-29T00:46:00.001-07:00

Common Interview Questions and Answers:

What have you done to improve your knowledge in the last year?

Try to include improvement activities that relate to the job

Are you applying for other jobs?

Be honest. But keep the focus on this job and what you can do for this organization. Anything else is a distraction.

Why do you want to work for this organization?

The answer is based on the research you had done about the organization.. Try to relate it to your long-term career goals.

Do you know anyone who works for us?

Be aware of the policy on relatives working for the organization before the interview. This can affect your answer even though they asked about friends not relatives. Be careful to mention a friend.

What kind of salary do you need?

It is a tough question. If you are confident you can tell what you are expected. Other wise you can ask the range of salary they are giving for this post. Or say that it can depend on the details of the job.

Are you a team player?

You are, of course, a team player. Be sure to have examples ready. This should shows your positive attitude.

Silverlight

2009-09-26T04:49:00.000-07:00

Silverlight

It is a crosss-platform implementation of the .NET framework for building and delivering of the next generation media experiences and Rich Interactive Applications(RIA) for the web.it runs in almost all popular browsers like IE. mozilla firefox, Opera etc. the plugin required is very small and hence installed very quickly. In this different technologies are combined into a single platform which allows you to select programming language and the tools .

Silverlight aims to compete with Adobe Flash and the presentation components of Ajax. It also competes with Sun Microsystems' JavaFX.

2 major versions of Silverlight are :

Silverlight 1.0 and Silverlight 2.0( previously referred to as version 1.1).

Silverlight 1.0 : consists of the core presentation framework, which is responsible for UI, interactivity and user input, basic UI controls, graphics and animation, media playback, DRM support, and DOM integration.

Silverlight 2.0 : includes a version of the .NET Framework, with the full Common Language Runtime as .NET Framework 3.0; so it can execute any . Multiple instances of the CoreCLR included in Silverlight can be hosted in one process. markup file (.xaml file) can be augmented by code-behind code, written in any .NET language, which contains the programming logic.

JAVA

2009-09-26T04:10:00.000-07:00

Java (programming language)

It is a programming language originally developed by James Gosling at Sun Microsystems .It is released in 1995 as a core component of Sun Microsystems' Java platform. Java Programming is based on object oriented concepts. It derives much of its syntax from C and C++. The java programs are compiled to bytecode. This bytecode can run on JAVA VIRTUAL MACHINE regardless of the architecture of the computer.

Java Platform

Java is portable. That is same java program can run in different machines regardless of the computer architecture with JVM. This is achieved by compiling the java program into java bytecode which are analogous to machine code. These bytecodes are interpreted by a virtual machine written specifically for host hardware. End-users commonly use a Java Runtime Environment (JRE) installed on their own machine for standalone Java applications, or in a Web browser for Java applets. A major benefit of using bytecode is porting.

Common Interview Questions and Answers

2009-09-26T03:20:00.000-07:00

Common Interview Questions and Answers:

I am giving you some commonly asking interview questions and answers. Read carefully and face the interview very confidently.
1. Tell me about yourself :
This is very commonly asked question. So it is better to prepare a short note for it. Try to include work related matters. That is what you have done, what you can do etc related to the position you are interviewing for.
2. Why did you leave your last job?
Say positive reasons regardless of the situations. You can tell, to get a better opportunity to do something special etc.
3. What experience do you have in this field?
Speak about your experience that relate to the position you are applying for.
4. Do you consider yourself successful?
For this question the answer should always be ‘YES’ and you have to tell the reason briefly. You can tell your achieved goals and the set goals.
5. What do co-workers say about you?
Be prepared with a quote or two from co-workers. It should always positive.
6. What do you know about this organization?
You must do a research about the organization before attending the interview. Find out where they have been and where they are going, current issues of the organization etc

10 TIPS FOR INTERVIEW

2009-09-16T02:16:00.000-07:00

10TIPS FOR INTERVIEW

1. Do your homework. Know about the company and improve your knowledge about the company. This will help you in interview

2. Know where you’re going. Know where is the interview place. Keep address and phone numbers of the office with you. This will help to save your time and avoids unnecessary stress.

3. Look the part. Wear neat dress with professional look.Your clothing should be neat, pressed, and professional looking. Make sure to have a fresh haircut and clean, manicured nails.

4. Rehearse beforehand. Prepare answers to common questions the interviewer is likely to ask. If possible conduct a mock interview with a trusted friend as practice.

5. Secure your references. Find at least three key people — former supervisors, colleagues, or instructors — who are willing to serve as your professional references. Before interview be sure to secure their permission ,and be certain that they will speak highly of you when an employer contact him.

6. Arrive early. Be sure to arrive the interview place at least 15 minutes before the interview. Then announce yourself to the receptionist to let him or her know that you have arrived and that you have an appointment. Don’t forget to turn your cell phone off so it doesn’t ring during your meeting.

7. Bring necessary documentation. Prepare a checklist of documents that you will need for the interview, and make sure that you have taken them in your briefcase before leaving home. Don’t forget to take extra copies of your resume, a passport, driver’s license, Social Security card, or portfolio of writing samples or other professional work.

8. Sell yourself. The interview is your chance to shine. Develop a 25-second sales pitch that sings your praises. In business this is called an “elevator speech,” a compelling overview of why you? that can be recited in the time it takes to ride the elevator. IThis should include your strengths, your abilities, your knowledge and what sets you uniquely apart from other applicants.

9. Don’t neglect to ask questions. Based on your earlier research about the company, ask how the responsibilities of the open position relate to the company’s goals and plans for the future. Interviewers are impressed by candidates who show that they are knowledgeable about the organization.

10. Follow up. After the interview, don’t forget to send a handwritten note or friendly email. The email includes thanking the interviewer for his or her time and consideration, as well as restating your interest and commitment to the position. If you don’t hear anything after one week, you can call to politely inquire when they will be making a final decision.

Seven Manthras of Success

2009-09-16T00:54:00.000-07:00

SEVEN MANTRAS FOR SUCCESS

1. Communication Skills – Communication is very important in an organization. So the we should be master in both verbal as well as written.

2. Influencing Abilities - we should have leadership power. This includes both persuading and negotiating skills.persuation means convincing and encouraging people to work when a hard situation occurs. It is a very powerful tool in conflicting situations in professional and personal life.

3. Managing Skills – this term means getting things done. That is managing things,people and yourself.managing yourself means managing your time, money etc .

4. Problem Solving – this means to know how to recognize ,define and solve problems. . Effective problem solving skills will help to become the backbone of any team, which in turn will help in career prospects.

5. Creative Thinking – the ability to generate new ideas .that is we have to use both part of the brain effectively. Watch everything around the world very carefully to get new ideas.

6. Social Skills – this means the ability to relate peole, having inslights, helping others and facilitating. Interdependence has a great value in today’s life.

7. Managing Money- develop the ability to manage money efficiently.

Computer Science Seminar Topic

2009-09-15T23:05:00.000-07:00

SPINS -Security Protocol For Sensor Network

Abstract

Wireless sensor networks will be widely deployed in the near future. Many researched are doing on making these networks feasible and useful. Sensor networks are identified as being useful in different domains to include battlefield and perimeter defense. So we have to give high attention to security.

A suite of security protocols optimized for sensor networks: SPINS. SPINS has two secure building blocks: SNEP and _TESLA. SNEP the following important baseline security primitives: data confidentiality, two-party data authentication, and evidence of data freshness. Providing efficient broadcast authentication, which is an important mechanism for sensor networks is a hard problem._TESLA provides authenticated broadcast for severely resource-constrained environments.

Oracle Functions

2009-09-15T03:54:00.000-07:00

ORACLE FUNCTIONS

·         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)  : It returns a char value representing the sound of the words

·         SQRT(n)   :   to find  Square Root of a number

                                  Eg : sqrt(4)=2

·         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

·         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

                               Eg :to_char(to_date(‘1/sep/2009’),’MON’)  =sep

·         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

·         VARIANCE([DISTINCT] n) : Variance of n, ignoring NULLs

·         VSIZE(expr)  :  Value Size - returns the number of bytes used by each row of expr.

Oracle Functions

2009-09-15T03:35:00.000-07:00

ORACLE FUNCTIONS
·         LAST_DAY(date) : Returns the last day of month in Date
                   Eg : last_day('23/sep/2009')  = 9/30/2009
 ·         LEAST(expression, expression...) :  Returns the smallest in a list of expressions
                   Eg : least (2,4,3)=2
 ·         LENGTH(char)  :  Returns the number of characters in char
                   Eg : length(‘abc’)=3
 ·         LENGTHB(char) :  Returns the number of bytes in char                
·         LIKE wildcard/value :    Wildcards are [% = any chars] [ _ = any one char]
                                                   Where 'x' LIKE 'ajith%' [will find 'Ajithraj']
                                                   Where 'x' LIKE 'ajith_' [will find 'Ajitha']
·         LN(n)      :    Natural Log of n, where n>0
·         LOG(b,n)    :  log of n, base b
            ·         LOWER(char)  :  Returns character string in lowercase
                                              Eg : lower(‘Lima’) = lima
·         LPAD(char, n[,PadChar]) :    Left Pad char with n spaces [or PadChars]
                                  Eg : lpad('abc',5,0) = 00abc
·         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 
·         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 in the function is null, returns value_if_null; if expression is not null, returns expression.  
·         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  a raw to a character value containing its hex equivalent
·         REF(table_alias) : Returns a REF value for an object instance .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)
                                              Eg : round(3.5345,2)=3.53
·         ROUND(date,fmt) : date rounded to fmt
·         ROWIDTOCHAR(rowid) :  Convert a rowid value to VARCHAR2
·         ROW_NUMBER   : It assign a unique number to each row of the 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]

Oracle Functions

2009-09-15T01:30:00.001-07:00

Oracle SQL Functions

·         ABS(n)  : It returns absolute value of a number

          Eg:          ABS(-15)=15, ABS(24)=24

·         ACOS(n) : It Returns arc cosine of number(n).’n’ must be in the   range of -1 and 1

                                              Eg:          ACOS(.3)= 1.26610367

·         ADD_MONTHS(date,num_months) : Returns date + num_months

                                  Eg: ADD_MONTHS('01-Aug-03', 3)= '01-Nov-03'

·         ASCII(char) : it Converts char into a decimal ascii code

                                  Eg:          ASCII(3)=51

·         ASCIISTR(char):argument of this function is a string ,or expression that resolves to a string ,in any character set.it returns an ASCII conversions of the string in the database character set.

                                  Eg:          ASCIISTR(ABCDE)= AB\00C4CDE

·         ASIN(n) : returns arc sine of n.

                                  Eg:          ASIN(.3)= 0.30469265

·         ATAN(n) : it returns arc tangent of n.

                                  Eg:          ATAN(.3)= 0.29145679

·         ATAN2(n.m) : it returns arc tangent of n and m.

                                  Eg:          ATAN2(.3,.2)= 0.9827937232

·         AVG([DISTINCT]n): Averge value of 'n' ignoring NULLs

·         BETWEEN value AND value : Where 'x' between 25 AND 100

·         BFILENAME('directory','filename'): To Get the BFILE locator associated with a physical LOB binary file.

·         CASE : It is used to Group the data into sub-sets. The case statement is an alternative for the decode function.

Eg: select id

, case (when status ='A' then 'Accepted'

when status ='R' then 'Rejected'

else 'Other') end

from employee_maser;

·         CEIL(n) :       Round n up to next whole number.

                    Eg: ceil(3.4)=4,        Ceil(3.9)=4

·         CHARTOROWID(char) : it Converts a Char into a rowid value.

                              Eg : SELECT ID from some_table

                                              WHERE ROWID = CHARTOROWID('AAAAtmAAEAAAAFmAAB');

·         CHR(n) : Character with value n

                                             Eg: chr(51)=3

·         CONCAT(s1,s2) :  it Concatenate string1 and string2

                                       Eg : concat('dfq','ee')=dfqee

·         CONVERT (char_to_convert, new_char_set, old_char_set) :

                               Convert a  string from one character set to another.

·         COS(n): Cosine of number

                  Eg : cos(.3)= 0.95533648

·         COSH(n)       Hyperbolic Cosine of number

                  Eg : cosh(.3)= 1.04533851

·         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

                                                             Eg : select decode(id,1,’111’,’2’)

                                                    If id=1 then it return ‘111’ else returns ‘2’

·         DENSE_RANK  :  It is used to 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

·         EMPTY_CLOB  :  Return an empty LOB locator

·         EXISTS  :     This function returns  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.

                                              Eg : Floor(3.6)=4

·         GREATEST(expression, expression...) :  It returns the largest in a list of expressions.

·         GROUPING   :   Grouping for superaggregate rows=NULL

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

·         IN (list of comma separated values) : it is used to check whether the parametercontains a required value.

                                  Eg : emp_code in(10002,35254,43534) –return TRUE if list contains the                   requied emp_code

·         INITCAP(char) :  String with Initial Capitals

                   Eg : Initcap(‘lima’)=Lima

·         INSTR(str, chars[,s[,n]]) :  this function find the 'n'th occurence of 'chars' in 'str'  Starting at position 's'.  n and s default to 1

·         INSTRB (str, chars[,s[,n]]) :  It is same as INSTR, except that 's' and the return value are expressed in bytes,   use for double-byte char sets

·         IS [NOT] NULL  : It is used to check for NULL (empty) values  Where 'x' IS NULL;

Data Modifications in SQL/Oracle

2009-09-14T04:54:00.000-07:00

SQL INSERT INTO Statement

This statement is used to insert new row into a table.

SQL INSERT INTO Syntax

· INSERT INTO table_name
VALUES (value1, value2, value3,...)

· INSERT INTO table_name (column1, column2, column3,...)
VALUES (value1, value2, value3,...)

Insert into employee_dtl values(10002,’Ganesh’,3,’04/05/2009’,2);

Emp_Id	Emp_Name	Department_id	Join_dt	Designation
10001	Manu	1	06/06/2008	1
10254	Meera	1	09/04/2006	3
13654	Thilak	2	09/05/2009	4
10002	Ganesh	3	04/05/2009	2

Insert into employee_dtl(emp_code,emp_name,department_id,designation_id)

Values(13555,’abi’,4,3)

Emp_Id	Emp_Name	Department_id	Join_dt	Designation
10001	Manu	1	06/06/2008	1
10254	Meera	1	09/04/2006	3
13654	Thilak	2	09/05/2009	4
10002	Ganesh	3	04/05/2009	2
13555	abi	4		3

Data Modifications in SQL/Oracle

2009-09-14T04:40:00.000-07:00

SQL AND & OR Operators

· The AND operator uses to satisfy two conditions. That is it returns true only if two conditions are satisfied.

· The OR operator return true only if one of the conditions are satisfied.

Select * from employee_dtl where department_id=1 and designation_id=3

Emp_Id	Emp_Name	Department_id	Join_dt	Designation
10254	Meera	1	09/04/2006	3

Select * from employee_dtl where department_id=1 or designation_id=3

Emp_Id	Emp_Name	Department_id	Join_dt	Designation
10001	Manu	1	06/06/2008	1
10254	Meera	1	09/04/2006	3

Select * from employee_dtl where department_id =1 and (designation_id=1 or designation_id=4)

Emp_Id	Emp_Name	Department_id	Join_dt	Designation
10001	Manu	1	06/06/2008	1

The ORDER BY Keyword

· Used to sort the result set by a specified column

· By default it sort in ascending order

· DESC is the keyword used to sort in descending order

SELECT column_name(s)
FROM table_name
ORDER BY column_name(s) ASC|DESC.

Select * from employee_dtl order by join_dt

Emp_Id	Emp_Name	Department_id	Join_dt	Designation
10254	Meera	1	09/04/2006	3
10001	Manu	1	06/06/2008	1
13654	Thilak	2	09/05/2009	4

Select * from employee_dtl order by emp_code desc

Emp_Id	Emp_Name	Department_id	Join_dt	Designation
13654	Thilak	2	09/05/2009	4
10254	Meera	1	09/04/2006	3
10001	Manu	1	06/06/2008	1

Operators allowed in where condition

2009-09-14T01:14:00.000-07:00

Operators Allowed in the WHERE Clause

Operator	Description
=	Equal
<>	Not equal
>	Greater than
<	Less than
>=	Greater than or equal
<=	Less than or equal
BETWEEN	Between an inclusive range
LIKE	Search for a pattern
IN	If you know the exact value you want to return for at least one of the columns

Data Modifications in SQL/Oracle

2009-09-14T01:13:00.001-07:00

Data Modification Languages(SQL/ORACLE)

SQL DML and DDL

SQL can be divided into two parts: The Data Manipulation Language (DML) and the Data Definition Language (DDL).

The DML commands are:

SELECT - retrieves data from a database
UPDATE - updates data in a database
DELETE - deletes data from a database
INSERT INTO - inserts new data into a database

The DDL statements are:

CREATE DATABASE - creates a new database
ALTER DATABASE - modifies a database
CREATE TABLE - creates a new table
ALTER TABLE - modifies a table
DROP TABLE - deletes a table
CREATE INDEX - creates an index (search key)
DROP INDEX - deletes an index

SELECT Statement

· is used to select data from a database.

· The result is stored in a result table, called the result-set.

Syntax

1. Select column name(s) from table name

2. Select * from table name

3. Select distinct column name(s) from table name

4. Select column name(s) from table name where column name operator value

An SQL SELECT Example

Emp_Id	Emp_Name	Department_id	Join_dt	Designation
10001	Manu	1	06/06/2008	1
10254	Meera	1	09/04/2006	3
13654	Thilak	2	09/05/2009	4

The "Employee_dtl" table:

To select all employees name from this table we can write

· select emp_name from employee_dtl;

The resultant table is as shown below.

Emp_name

Manu

Meera

Thilak

· Select * from employee_dtl - it select all columns in the table

Emp_Id	Emp_Name	Department_id	Join_dt	Designation
10001	Manu	1	06/06/2008	1
10254	Meera	1	09/04/2006	3
13654	Thilak	2	09/05/2009	4

· Select distinct department_id from employee_dtl –it select distinct field values from the table

Department_id

· Select emp_name from employee_dtl where emp_code=10001

Emp_name

Manu

· Select * from employee_dtl where department_id=1

Emp_Id	Emp_Name	Department_id	Join_dt	Designation
10001	Manu	1	06/06/2008	1
10254	Meera	1	09/04/2006	3

· Select emp_code from employee_dtl where emp_name =’Meera’

Emp_code

10254

Data Modifications in SQL/Oracle

2009-09-12T01:00:00.001-07:00

Data Modifications in SQL

We can create a table using ‘create’ command. Now we can see how rows are inserted int the table, deleted from the table and modified within the table.

Insertions

Insert statement look like this ,

important.if no column name is speciafied the values must be given in the same order as the columns created in the table.

Eg: insert into emp(emp_Code,emp_name) values(10001,’JAYA’);

We can also insert data to a table from another table with querry.

Updates

We can use ‘update’ statement for modifying the attribute values of a tuple.

The new value to assign to column i must a matching data type. If we exclude the the where condition then the update statement updates all records of the table.

Eg: update emp set department-id=10 where emp_Code=70754;

Like in the case of insert statement other tables can be used to retrieve data that are used as new values. In this case we can use querry instead of expression.

Deletions

Using the delete command all or selected tuples can be deleted from a table.

If the where clause is omitted, all tuples are deleted from the table. The alternative command

for deleting all tuples from a table is the truncate table[ table] command. However, in this

case, the deletions cannot be undone.

Eg : delete from emp where emp_code=23564

Commit and Rollback

Transaction : A sequence of database modifications, i.e., a sequence of insert, update, and delete statements. Modifications of tuples are temporarily stored in the database system. They become permanent only after the statement commit; has been issued. So as long as the user has not issued the commit statement, it is possible to undo all modifications made since the last commit. To undo modifications, one has to issue the statement rollback.so to complete each modification of the database with a commit..

Any data definition command such as create table results in an internal commit. Also commit is implicitly executed when the user terminates an Oracle session.

Engineering Colleges In Kerala

2009-09-11T21:54:00.000-07:00

Government and Aided colleges under the Director of Technical Education DTE)

1. College of Engineering Thiruvananthapuram(695016)

2. Government Engineering College, Barton Hill, Thiruvananthapuram 695035

3. Government Rajiv Gandhi Institute of Technology, Vellore P.O, Pampady Kottayam 686501.

4. Government Engineering College Thrissur, R V Puram 680009.

5. Government Engineering College Palakkad, Sreekrishnapuram 679513.

6. Government College of Engineering Mangattuparamba, Parassinikadavu P.O, Kannur 670563.

7. Government Engineering College Kozhikode, West Hill P.O Kozhikode 673005.

8. Government Engineering College Painavu, Idukki 685603.

9. Government Engineering College Wayanad, Nalloornad P.O, Mananthavady 670645.

10. TKM College of Engineering, TKM College Post, Kollam 691005.

11. N.S.S College of Engineering, Engineering College P.O, Akathethara, Palakkad 678008.

12. Mar Athanasius College of Engineering , College P.O, Kothamangalam, Ernakulam 686666.

Engineering Colleges under the Kerala Agricultural University (KAU)
1. College of Dairy Science and Technology, Mannuthy P.O Thrissur 680651.

2. Kelappaji College of Agricultural Engineering and Technology, Tavanur P.O, Malappuram 679573.

Self-financing Engineering Colleges under the Institute of Human Resources Development (IHRD)

1. College of Engineering, Manakkala P.O, Adoor 691551.

2. College of Engineering, Chengannur P.O, Alappuzha 689121.

3. Model Engineering College, B.M.C Post, Thrikkaara, Kochi 682021.

4. College of Engineering, Karunagapally, Pada.North P.O, Karunagapally 690518.

5. College of Engineering, Thekkekara P.O, Poonjar 686582.

6. College of Engineering, Attingal, Thiruvananthapuram 695101.

7. College of Engineering, Thrikkannamangal ETC P.O, Kottarakkara 691531.

8. College of Engineering, Pallippuram P.O, Cherthala 688541.

9. College of Engineering, Kallooppara, Kadamankulam P.O, Thiruvalla, Pathanamthitta 689603.

Self-financing Engineering Colleges under the Bahadur Shastri Centre for Science and Technology

1. L.B.S College of Engineering, Kasaragod, Povval, Muliyar P.O, Kasaragod - 671542.

2. L.B.S Institute of Technology for Women, Poojapura P.O, Thiruvananthapuram 695012.

Self-financing Engineering Colleges under the KSRTC

1. Sree Chitra Thirunal College of Engineering, Pappanamcode P.O, Thiruvananthapuram 695018.

Self-financing Engineering Colleges under the CAPE

1. College of Engineering Kidangoor, Kidangoor South P.O, Kottayam 686583.

2. College of Engineering Trikaripur, Cheemeni P.O, Cheruvathur, Kasaragod 671313.

3. College of Engineering Eranjoli P.O, Thalassery, Kannur 670107.

4. College of Engineering Perumon, Perinad P.O, Kollam 691601.

5. Co-operative Institute of Technology, Mandarathur P.O, Kurunthodi, Vadakara, Kozhikode 673105.

Self-financing Engineering Colleges under the Mahatma Gandhi University

1. Mahatma Gandhi University College of Engineering, Muttom P.O, Thodupuzha, Idukki 685587.

Self-financing Engineering Colleges under the University of Kerala

1. University College of Engineering, Golden Jubilee Building, Kariavattom, Thiruvananthapuram 695581.

Self-financing Engineering Colleges under the University of Calicut

1. Calicut University Institute of Engineering and Technology, Kohinoor, Thenhipalam P.O, Malappuram 673636.

Self-financing Engineering Colleges under the Center for Continuing Education

1. College of Engineering, County Hills Munnar P.O, Idukki 685612.

Self-financing Engineering Colleges under the Private Management

1. Al-Ameen College of Engineering, Pattambi, Palakkad 679122.

2. Amal Jyothi Engineering College, Kanjirapally, Kottayam 686518.

3. Adi Sankara Institute of Engg. & Tech., Kalady, Eranakulam 683574.

4. AWH Engineering College, Kuttikatoor, Kozhikkode- 673008.

5. Baselios Mathew II College of Engg., Sasthamcotta, Kollam 690520.

6. Carmel Engineering College, Koonamkara, Ranni, Pathanamthitta

7. Federal Institute of Science & Tech., Mookkannoor, Ernakulam

8. Ilahia College of Engineering & Tech., Mulavoor, Muvattupuzha

9. I E S College of Engineering, Thrissur

10. Jyothi Engineering College, Cheruthuruthy, Thrissur

11. K M C T College of Engineering, Manassery, Kozhikkode

12. K M E A Engineering College, Alwaye

13. Lourdes Matha College of Science & Tech., Kuttichal, Trivandrum

14. Mar Baselios Christian College of Engg. & Tech., Peermede, Idukki

15. Mar Baselios College of Engineering & Tech., Nalanchira, Trivandrum

16. Marian Engineering College, Kazhakuttom, Trivandrum

17. Musaliar College of Engineering & Tech., Kumpazha, Pathanamthitta

18. Mohandas College of Engg. & Tech., Nedumangad, Trivandrum

19. M E A Engineering College, Chemmaniyod, Malappuram

20. M E S College of Engineering, Kuttippuram

21. Met's School of Engineering, Mala, Thrissur

22. M G College of Engineering, Thiruvallom, Thiruvananthapuram.

23. Saintgits College of Engineering, Pathamuttom, Kottayam

24. Mangalam College of Engineering, Ettumanoor, Kottayam

25. Mary Matha College of Engineering, Olathanni, Thiruvananthapuram

26. Matha College of Technology, Manakappadi, North Pravur, Ernakulam

27. Muslim Association College of Engineering, Venjaramoodu

28. Mount Zion College of Engg., Pathanamthitta

29. Nehru College of Engg. & Research Centre, Thiruvillwamala, Thrissur

30. P A Aziz College of Engineering & Technology, Karakulam, Thiruvananthapuram

31. Royal College of Engineering & Technology, Akkikkavu P O, Thrissur

32. Rajagiri Schooll of Engg. & Tech., Kakkanad, Ernakulam

33. Sree Buddha College of Engineering, Pattoor P O, Padanilam, Alappuzha.

34. SCMS School of Engg. & Tech., Ernakulam

35. Shahul Hameed Memorial Engineering College, Kadakkal, Kollam

36. Sahrdaya College of Science & Tech., Kodakara, Thrissur

37. Sarabhai Institute of Science & Technology, Kattakada, Vellanad, Nedumangad.

38. St. Joseph's College of Engg. & Tech., Palai

39. Sree Narayana Guru College of Engineering & Technology, Payyannur, Kannur

40. Sree Narayana Gurukulam College of Engineering, Kadayiruppu P O, Ernakulam

41. Sree Narayana Mangalam Inst. of Mgmt. & Tech., Maliankara

42. Travancore Engineering College, Aayoor, Kollam

43. T. K. M Institute of Technology, Ezhukone, Kollam

44. Toc H Institute of Science & Technology, Mulamthurutthy, Ernakulam

45. Vidya Academy of Science & Technology, Thrissur

46. Veda Vyasa Institute of Technology, Ponnempadam P O, Karadparamba, Malappuram.

47. Viswa Jyothi College of Engineering & Technology, Vazhakkulam, Ernakulam

48. Vimal Jyothi Engineering College, Chemperi, Kannur

49. Younus College of Engineering & Technology, Vadakkevila, Kollam.

Constraints in SQL/ORACLE

2009-09-11T03:55:00.000-07:00

Constraints

Two types of constraints are provided: column constraints and table constraints. Column constraints are associated with a single column but table constraints are associated with more than one column. Any column constraint can also be formulated as a table constraint.

The specification of a (simple) constraint has the following form:

[constraint ] primary key | unique | not null

A constraint can be named. The most important constraints are not null, unique, primary key constraints.The primary key constraint enables a unique identification of each tuple in a table.

With a primary key, the database system ensures that no duplicates appear in a table.

For example, for our EMP table, the specification

create table EMP

(

emp_code number(4) constraint pk emp primary key,

. . .

);

In this case emp_code must be unique in this table.a table must have onlyone primary key, but more than one unique keys. Null values are not allowed for primary keys.

Oracle/SQL

2009-09-11T02:59:00.001-07:00

Data Definition in SQL

Creating Tables

The SQL command for creating table is

create table <table>

(
<column 1> <data type> [not null] [unique] [<column constraint>],
. . . . . . . . .
<column n> <data type> [not null] [unique] [<column constraint>],
[<table constraint(s)>]
);

Each column must have a name and data type. Column name must be unique within the table definition. The column definition must be separated by comma. Column names are not case sensitive. ‘Not null’ constraint is directly specified after the data type of the column. The constraint requires defined attribute values for that column, different from null.
Eg: The create table statement for our EMP table has the form

create table EMP

(

emp_code number(5) not null,

emp_name varchar2(30) not null,

father_name varchar2(10),

Join_date date,

Basic_pay number(7,2),

Department_no number(2)

);

Remark: Except for the columns emp_code and emp_name null values are allowed.

ORACLE/SQL

2009-09-11T02:29:00.000-07:00

ORACLE / SQL

STRUCTURED QUERRY LANGUAGE(SQL)

In Relational database data is represented as tables(relations). The result of a quarrey is also a table. The structure o a table is shown below.

A table has a unique name and consists of many rows and columns. Each row is called Tuple (or record), it contains stored information. A table can have one or more than 1 columns. A column has a column name and data type. The data type of the column describes the attribute of the tuples. The structure of a table is known as relation schema.

In SQL, the ‘relation’ is known as ‘Table’, ‘tuple’ is known as ‘Row’ and the ‘attribute’ is known as ‘Column’.

Data Types in SQL

· Char(n) – fixed length, ncharacter data. Eg : Char(10)

· Varchar(n) – variable ength n character data Eg: varchar(100)

· Number(a,b) – numeric data types for integers and real. A represents total no.of digits and b represents the no of digits in the right side of the decimal point. Eg: number(5,2)

· Date - used for storing date and time.

· Long – character data type.

In Oracle/SQL ‘Boolean’ data type is not used.