Posts

Types of SQL Commands

-
Type of SQL statements are divided into five different categories:   Data definition language (DDL) Data manipulation language (DML),  Data Control Language (DCL),  Transaction Control Statement (TCS),  Session Control Statements (SCS). DDL:(Create,Alter,Drop,Truncate,Rename) Statement Description CREATE Create new database/table. ALTER Modifies the structure of database/table. DROP Deletes a database/table. TRUNCATE Remove all table records including allocated table spaces. RENAME Rename the database/table. DML:(Insert,Update,Delete,Merge,Lock Table,Call Explain Plan) Statement Description SELECT Retrieve data from the table. INSERT Insert data into a table. UPDATE Updates existing data with new data within a table. DELETE Deletes the records rows from the table. MERGE MERGE (also called UPSERT) statements to INSERT new records or UPDATE existing records depending on condition matches or not. LOCK TABLE LOCK TABLE statement to lock one or more table
Post a Comment
Read more

Oracle/PLSQL Sequence

-
Description: Sequence is a database object from which multiple users may generate unique integers. You can use sequences to automatically generate Primary Key Values. Prerequisites : To create a sequence in your own schema,you must have CREATE SEQUENCE system privilege. To create a sequence in another user's schema,you must have CREATE ANY SEQUENCE system privilege. Syntax: CREATE SEQUENCE sequence_name MINVALUE value MAXVALUE value START WITH value INCREMENT BY value CACHE value; Example: The following statement creates the sequence  CREATE SEQUENCE seq_empid MINVALUE 1 MAXVALUE 999999999999999999999999999 START WITH 1 INCREMENT BY 1 CACHE 20; The first sequence number that it would use is 1 and each subsequent number would increment by 1(i.e..2,3,4,..).It will values for performance. If you omit the MAXVALUE when creating the Sequence,it will automatically default to :     MAXVALUE 9999999999999999999999999
Post a Comment
Read more

ORACLE VIEW

-
In Oracle, view is a virtual table that does not physically exist.View does not store any data and It is stored in Oracle data dictionary. A view is created by a query joining one or multiple tables. Syntax : CREATE   VIEW <view_name> as SELECT   columns FROM    tables WHERE    conditions; Example let's take an example to create a view.In this example,we are creating two table emp and dept. EMP table:     CREATE TABLE  EMP      (    EMP_ID NUMBER,    EMP_NAME VARCHAR2(4000),    DEPTNO NUMBER     ) ;  / DEPT table: CREATE TABLE  DEPT       (    DEPTNO NUMBER ,           DNAME VARCHAR2(100)     )  ; /   Execute the following query to create a view name EMP_DET. Create View Query: CREATE VIEW EMP_DET AS   SELECT EMP.EMP_ID ,EMP.EMP_NAME,DEPT.DNAME FROM    EMP,DEPT WHERE   EMP.DEPTNO=DEPT.DEPTNO; Output: View created. You can now check the Oracle VIEW by this query:
Post a Comment
Read more

Oracle Global Temporary Tables

-
Oracle support two types of temporary tables. Global Temporary Tables : Available since Oracle 8i and subject of this article. Private Temporary Tables : Available since Oracle 18c  Creation of Global Temporary Tables The data in a global temporary table is private, such that data inserted by a session can only be accessed by that session. The session-specific rows in a global temporary table can be preserved for the whole session, or just for the current transaction. The ON COMMIT DELETE ROWS clause indicates the data should be deleted at the end of the transaction, or the end of the session. Example : CREATE GLOBAL TEMPORARY TABLE sample_temp_table (   ID           NUMBER,   DESC     VARCHAR2(20) ) ON COMMIT DELETE ROWS;  insert into sample_temp_table(ID,DESC) values(100,'Student 100 ');  SELECT COUNT(*) FROM sample_temp_table; Result :-  1 commit; SELECT COUNT(*) FROM sample_temp_table; Result :-  0
Post a Comment
Read more

Oracle / PLSQL: INSERT STATEMENT

-
Image
Oracle / PLSQL: INSERT STATEMENT:        The oracle INSERT statement is used to insert a single record or multiple records into table. Syntax for INSERT: For single record:- INSERT INTO Table_name ( Column1, Column2,…. ) VALUES (expression1, expression2,……..); For Multiple records:-   INSERT ALL INTO Table_name ( Column1, Column2,…. ) VALUES (expression1, expression2,……..); INTO Table_name ( Column1, Column2,…. ) VALUES (expression1, expression2,……..); Example for Insert Multiple rows: In below example we insert two rows in emp1 table in Oracle, Using a oracle INSERT statement  Insert all into emp1(ename, eid, doj, esal) values('a6',6,'02-02-2015',9000)                     into emp1(ename, eid, doj, esal) values('a5',5,'02-02-2015',9000)  select * from emp1;
Post a Comment
Read more

Oracle PLSQL Create Table

-
Image
Create Table :                             The Create table statement is used to create a new table in a database. Syntax for Create Table:                            CREATE TABLE Table_name ( Column1 data type (), Column2 data type(),…. );   The data type Parameters specifies the type of data the column can hold (e.g varchar, number, integer etc). Example for Create Table:                             In this example we create table called emp that contains four columns: ename,   eid, DOJ, esal:                             CREATE TABLE EMP1 ( ename varchar2 (20), eid number(6), DOJ date(), esal number(6));
Post a Comment
Read more