SQL CONCEPTS

Structured Query Language(SQL) is the standard language designed to access relational databases. SQL is pronounced either as the word Sequel or as the letters SQL. SQL uses simple syntax that is easy to learn and use there are five types of SQL statements given below. Query Statements: Allow you to retrieve the rows stored in the database tables. You write a query using the SQL SELECT statement. Data Manipulation Language (DML) Statements: Allows you to modify the contents of the tables. There are three DML statements.

• INSERT - Allows you to Insert row(s) into the Table.
• UPDATE - Allows you to change a Row.
• DELETE - Allows you delete the rows from the Table

Data Definition Language (DDL): Allows you to define the Data structures, such as tables that make up a database. There are five basic types of DDL statements.

• CREATE – Allows you to Create Database structure.
•        Ex: Create Table – Used to create a Table in the Database.
•                 Create User – Used to create the database user.
• 
  ALTER – Allows you to modify a database structure.
• 
  DROP – Allows you to remove a database structure.
• 
  RENAME – Allows you to change the name of the table.
• 
  TRUNCATE – Allows you to delete the entire contents of the table.

Transaction Control (TC) Statements: Allows you to permanently record the changes made to rows stored in a table or undo the changes. There are three TC Statements.
           COMMIT – Allows you to permanently record the changes made to the rows.
           ROLLBACK – Allows you to undo the changes made to the rows.
           SAVEPOINT – Allows you to set a SAVEPOINT to which you can rollback changes you made.

Data Control Language (DCL): Allows you to change the permissions on the database structures. There are two DCL statements.
         GRANT – Allows you to give another user access to your database structures, such as tables.
         REVOKE – Allows you to prevent another user from accessing to your database structures, such as tables.

Tables: Table is a database object which holds the data and contains one or more columns associated with its datatypes Creating a Table: we use the create table statement to create a table. The simple syntax for the creation of table. Syntax: CREATE Table table_name (column_name type [CONSTRAINT constraint_def DEFAULT default_exp], column_name type [CONSTRAINT constraint_def DEFAULT default_exp], column_name type [CONSTRAINT constraint_def DEFAULT default_exp]… ) [ON COMMIT {DELETE | PRESERVE} ROWS] TABLESPACE tab_space; Ex: SQL> Create table hariha_0016(ename varchar2(10), Eno number unique); Table Created. Altering a Table: we can alter the table using the alter statement. The alter table statement perform such tasks given below.
          Add, modify, or drop a column.
          Add or drop a constraint.
          Enable or Disable a constraint. Ex: SQL> Alter table hariha_0016 Add address varchar2(10);  Modifying a Column: Ex: SQL> Alter table hariha_0016 Modify address varchar2(20); Dropping a Column: Ex: SQL> Alter table hariha_0016 Drop address;

Renaming a Table: If we want to change the name of the table then we use this RENAME statement. Ex: SQL> Rename table hariha_0016 To Hariha_0015;

Truncating a Table: If we want to delete all the rows existing in the table then we use the TRUNCATE Statement. Ex: SQL> Truncate table hariha_0015;

Dropping a Table: If we want to drop the total structure along with the records existing in the table we use this DROP statement. Ex: SQL> Drop table hariha_0015;

Views: A view is basically a predefined query on one or more tables. Retrieving information from the view is done in the same manner as retrieving from the table.

Creating a View: by using the create view statement we can create a view. Syntax: Create [Or Replace] View view_name [(allias_name[,allias_name….])] AS subquery [WITH {CHECK OPTION | READ ONLY} CONSTRAINT Constraint_name]; Ex: SQL> Create view medha_0016_view AS Select ename, eno, address from Hariha_0016; View Created. Performing an Insert using a View: we can also perform DML operations using the views. Given below is the example for that. Ex: SQL> Insert into medha_0016_view( Ename, eno, address) Values (HARIHA, 0016, HYD); 1 Row Created.

Modifying a View: we can modify the view using the REPLACE. If there any view existing with that name then it was modified with the current one. Ex: SQL> Create or Replace view medha_0016_view AS Select a.ename, a.eno, a.address, b.city from Hariha_0016 a, hariha_0011 b Where a.eno = b.eno;

Dropping a View: when want to drop the view we use this statement. Only the view will be dropped from the database the table was not effected. Ex: SQL> Drop view hariha_0016_view;

Sequence: A sequence is a database item that generates a sequence of integers. We create the sequence using the CREATE SEQUENCE statement. Syntax: Create Sequence Sequence_name [Start with Start_num] [Increment by increment_num] [ {MAXVALUE max_num | NOMAXVALUE} ] [ {MINVALUE min_num | NOMINVALUE} ] [ {CYCLE | NOCYCLE} ] [ {ORDER | NOORDER} ];

Ex: SQL> Create Sequence medha_seq_0016 Start with 100 Increment by 1; Sequence Created.

Using the Sequence: Ex: SQL> Select medha_seq_0016.currval ”Currval”, medha_seq_0016.nextval “Nextval” From Dual; Output:           Currval      Nextval ---------      ---------- 101                101 Modifying the Sequence: If we want to modify the sequence by using the ALTER SEQUENCE we can do that. Ex: SQL> Alter Sequence medha_seq_0016 Start with 1000 Increment by 2; Sequence Altered.

Dropping a Sequence: If we want to drop the sequence then we use this DROP STATEMENT. Ex: SQL> Drop Sequence medha_seq_0016; Sequence Dropped.

Explain Plan: Explain plan gives the execution plan of the statement. PLAN_TABLE is necessary for explain plan.
        If there is no PLAN_TABLE in your system then go with UTLXPLAN.SQL from the SQL Prompt. Syntax: SQL> Explain plan Set statement_id = ‘hariha_0016’ [into PLAN_TABLE ] for select * from scott.emp where empno = 7369; Plan Explained.
     In TOAD(Tools for Oracle Application Design) write the SQL statement and press CTRL+E then it automatically shows the explain plan. It is the simple way to get the explain plan instead of writing the explain plan in SQL.

SQL Trace: SQL Trace gives a wide range of information & statistics that used to tune a group of SQL operations. We do the Sequel Trace at three levels. 1. SQL 2. Reports 3. Forms
Trace the Sequel Statements. How much time it was taking, how many rows it was fetching, all the information was given from SQL Trace. Steps for generating Trace file:
           Enable the Trace.
           Run the DML statements.
           Disable the Trace.
           Get the Trace file.
           Convert the Trace File to Readable Format.
The Trace file was generated with he extension .TRC. Oracle has give specified directory for trace files. To get the path use the query below. EX: SQL> Select value from V$PARAMETER Where name = ‘USER_DUMP_DEST’;
 To get the name of the Trace file also we have to use the Query. SQL> Select c.value || ‘\ORA’ || TO_CHAR(a.spid, ‘FM00000’) || ‘.TRC’ From V$PROCESS a, V$SESSION b, V$PARAMETER c Where a .addr = b.paddr and b.ausid = userenv(‘sessionid’) and c.name = ‘USER_DUMP_DEST’; TKPROF: Copy the trace file, which was generated earlier, and paste it in your custom directory. Syntax: CMD> TKPROF d:\siri_0016.trc siri_0016.txt
The Hariha_0016.txt was created in the same drive where the Trace file was located. Ex: --Query to Print the Cumulative Salary of the Employee table order by DEPTNO SQL> Select deptno, ename, sal, sum(sal) over(partition by deptno order by deptno, ename) “CUM_SAL” from scott.emp; Check out our Popular Trainings  Structure of OAF Advantages and Dis-Advantages of OAF Oracle data guard Training in Bangalore Creating First OAF Page in Project