Queries retrieve and change data
create, insert, update, delete command.
1. Create command: To create table we use create command.
Eg: To create a table employee(enamevarchar(10),eidint,esalint);
2. Update command :To update the values for particular column(value).
Syntax:
Update table table_nameset column1=value1,column2=value2,..
where condition
3. Delete command: To delete the values for a particular column
(value).
Syntax:
Delete from tablename where [condition];
Queries using operators in SQL.
An operator manipulates individual
data items and returns a result. The data items are called operands or
arguments. Operators are represented by special characters or by keywords. For
example, the multiplication operator is represented by an asterisk (*) and the
operator that tests for nulls is represented by the keywords IS NULL. There are
two general classes of operators: unary and binary. Oracle Database Lite SQL
also supports set operators.
Arithmetic
operators in sql:
1. + (unary operator): Makes operand
positive.
2.-(unary operator): Negates
operand.
3. / (Division operator): Division
(numbers and dates)
4. * (Multiplication operator): Multiplication
Comparison
operators:
a) Equal to Operator (=):
Checks if the values of two operands are equal or
not, if yes then; condition becomes true.
2.Not Equal to
Operator(!=): Checks if the
values of two operands are equal or not, if values are not equal then;condition
becomes true.
3.Greater than
Operator(>): Checks if the
value of left operand is greater than the value of right operand, if yes then
condition becomes true.
4.Less than Operator
(<):Checks if the value of
left operand is less than the value of right operand, if yes then condition
becomes true.
4.Greater than or Equal
to Operator (>=): Checks if the
value of left operand is greater than or equal to the value of right operand,
if yes then condition becomes true.
a)ALL Operator: The ALL operator is used to compare a value to all values
in another value set.
b)AND Operator: The ALL operator is used to compare a value to all values
in another value set.
c) ANY Operator: The ANY operator is used to compare a value to any
applicable value in the list as per the condition.
Queries to facilitate
acquaintance of Built-In Functions, String Functions, Numeric Functions, Date
Functions and Conversion Functions.
Oracle Built-In-Functions:
1.
ASCII:
Returns the number code that represents the specific character.
Query
synatx: ASCII(single_character)
2.
CONCAT:
Concatenates
two or more strings together.
Query
syntax:CONCAT(string1,string2)
3.
LENGTH:
Returns
the length of the specified string
Query
synatx: LENGTH(string)
4.
REPLACE:
Replaces
a sequence of characters in a string with another set of character.
Query
syntax: REPLACE(expression,pattern,replacement)
String functions in SQL:
1.
LOWER:
All
the letters in 'string_value' is converted to lowercase.
Query syntax: LOWER(string_value)
2.
UPPER:
All
the letters in ‘string_value’ is converted to upper case.
Query
syntax: UPPER(string_value)
3.
INITCAP:
All
the letters in 'string_value' is converted to mixed case.
Query
syntax:INITCAP(string_value)
4.
LPAD:
Returns
'string_value' left-padded with 'pad_value' . length of the whole string will
be of 'n' characters.
Query
syntax: LPAD(string_value, n, pad_value)
5.
RPAD:
Returns
‘string_value’ right-padded with ‘pad_value’, length of the whole string will
be of ‘n’ characters.
Query
syntax: RPAD(string_value,n,pad_value)
Numeric functions in SQL:
1. ABS: Returns
absolute value of the number ‘x’
Query syntax: ABS(x)
2. CEIL: Returns
integer value that is greater than or equal to the number ‘x’
Query syntax: CEIL(x)
3. FLOOR: Returns
integer value that is less than or equal to the number ‘x’
Query syntax: FLOOR(x)
4. ROUND: Returns
rounded off value of the number ‘x’ upto ‘y’.
Query syntax: ROUND(x,y)
Date functions in SQL:
1. ADD_MONTHS: Return a date value after adding ‘n’ months to the
date ‘x’.
Query syntax: ADD_MONTHS(date,n)
2. MONTHS_BETWEEN:
Returns the number of months between dates x1
and x2
Query
syntax: MONTHS_BETWEEN(x1,x2)
3. LAST_DAY:
It is used to determine the number of days
remaining in a month from the date 'x' specified.
Query
syntax: LAST_DAY(x)
Conversion functions
in SQL:
1. TO_CHAR:
Converts Numeric and Date values to a character
string value. It cannot be used for calculations since it is a string value.
Query syntax: TO_CHAR (x [,y])
2. TO_DATE:
Converts a valid Numeric and Character values
to a Date value. Date is formatted to the format specified by 'date_format'.
Query syntax: TO_DATE (x [, date_format])
3. NVL:
If 'x' is NULL, replace it with 'y'. 'x' and
'y' must be of the same datatype.
Query syntax: NVL(x,y)
Queries retrieve and change data create, insert, update, delete command. 1. Create command: To create table we use create command. E...
