CREATE TABLE
” statement. First, you have to name that table and define its columns and datatype for each column.CREATE TABLE table_name
(
column1 datatype [ NULL | NOT NULL ],
column2 datatype [ NULL | NOT NULL ],
…
column_n datatype [ NULL | NOT NULL ]
);
NULL
” or “NOT NULL
”. If in case, the value is left blank, it is treated as “NULL
” as default.Oracle, IBM db2, Microsoft SQL Server, Microsoft Access, MySQL and SQLite, PostgreSQL, MariaDB
.init.ora
file (you can recreate it manually).LOWER, UPPER, INITCAP
CONCAT, SUBSTR, LENGTH, INSTR, LPAD/RPAD, TRIM, REPLACE
ROUND
, TRUNC
, and MOD
MONTHS_BETWEEN
Function, which returns a number. Date Functions are MONTHS_BETWEEN, ADD_MONTHS, NEXT_DAY, LAST_DAY, ROUND, TRUNC.
TO_NUMBER
function is used to convert a Character string to Number format. TO_NUMBER function use fx modifier. Format: TO_NUMBER ( char[, ‘ format_model’] ). fx modifier specifies the exact matching for the character argument and number format model of the TO_NUMBER
function.TO_CHAR
function is used to convert NUMBER
or DATE
data type to CHARACTER
format. TO_CHAR
Function uses fm element to remove padded blanks or suppress leading zeros. TO_CHAR
Function formats : TO_CHAR (date, ‘format_model’)
.The format model must be enclosed in single quotation marks and is case sensitive.Select TO_CHAR (hire date, ‘MM/YY’) from the employee
.TO_DATE
function is used to convert a Character string to date format. TO_DATE
function use fx modifier which specifies the exact matching for the character argument and date format model of TO_DATE
function. TO_DATE
function format: TO_DATE ( char[, ‘ format_model’] )
.Select TO_DATE (‘May 24, 2007’,’ mon dd RR’) from dual;
ANALYZE
command is used to perform various functions on index, table, or cluster. The following list specifies the usage of ANALYZE
command in Oracle :BLOB
data type in the Oracle database has a varying length binary string. It is used to store two gigabytes of memory and for it, the length needs to be specified in bytes. An example to illustrate the usage of the BLOB
data type is given below :create table photos(name varchar(32) not null primary key, picture blob(10M));
select name,length(picture) from photos where name like '%logo%';
RAW datatype
in Oracle is used to store variable-length binary data or byte string values. The maximum size for a raw in a given table in 32767 bytes
.RAW
, varchar
, and varchar2
. Let me point out the major differences between them. PL/SQL does not recognize the data type and hence, it cannot have any conversions when RAW data
is transferred to different systems. This data type can only be queried or can be inserted in a table.X
’. $Oracle_base
is the main or root directory of Oracle whereas $Oracle_Home
is located beneath the base folder in which all Oracle products reside. to_char()
function is used to convert date to character. You can also specify the format in which you want output.SELECT to_char ( to_date ('12-12-2012', 'DD-MM-YYYY') , 'YYYY-MM-DD') FROM dual;
SELECT to_char ( to_date ('12-12-2012', 'DD-MM-YYYY') , 'DD-MM-YYYY') FROM dual;
raise_sal(empno, amt);
PROCEDURE raise_sal(empid INTEGER, amt REAL) IS current_salary REAL;
[sql]Select * from employee join salary using employee ID[/sql]
%FOUND
:%NOT FOUND
:%ISOPEN
:%ROWCOUNT
:Select rownum, <fieldnames> from table;
Select * from (select * from employees order by rownum desc) where rownum<2;
SYSDATE()
function is used in Oracle to find the current date and time of operating system on which the database is running.SELECT TO_CHAR (SYSDATE, 'MM-DD-YYYY HH24:MI:SS') "Current_Date" FROM DUAL;
updateSalary(empNumber,amount);
PROCEDURE updateSalary(empID INTEGER, amount REAL) IS currentSalary REAL;
SELECT AVG(SALARY) FROM EMP GROUP BY SALARY;
SELECT NAME, COUNT (NAME) FROM EMPLOYEE GROUP BY NAME HAVING COUNT (NAME) > 1;
ORDBMS
, the objects can be stored as they are. The language of the DBMS can be integrated with an object-oriented programming language. The language may even be exactly the same as that used in the application, which does not force the programmer to have two representations of his objects. Select * from employee where salary>(select avg(salary) from dept, employee where dept.deptno = employee.deptno);
SQL *Plus
startup command.V$Parameter
V$Database
V$Instance
V$Datafiles
V$controlfiles
V$logfiles
DBWn
”. This method is in charge of storing “dirty
” buffers on disc. When a server process has to update a data block, it first reads it from disc into the buffer cache if it isn’t already there, and then updates the cache copy. So, a “dirty
” block refers to a modified database block in the buffer cache.