TYPES OF KEYS IN ORACLE

super key : all attributes that are select to make a primary key are known as super key.

primary key:-attribute that uniquely identified a row in a table is known as primary key and primary key shuld be unique and not null.

candidate key:-remaining all the keys which are not select to a primary key are known as candidate key(alternate key).

composit key :-when mare than one field choose to make a primary key then, that keys are known as composit key.

ORACLE SQL QUERI ES

1. Physical Level --> Organize & Store raw Data 2. Conceptual Level --> isolate Data Storage Details

3. Extenal Level --> Varying View of application to different users.

Normalization

Key --> Uniquely Identified row in table Intelligentr Key --> Keys based on data values Non Intelligent Key --> to identify Row

Relationships One to One One to Many Many to Many

Normalization Forms

First NF --> Elimination of Repeated Groups Second NF --> Eliminate Redundant Data

Third NF --> Eliminate Column Not dependent on Keys Fourth NF --> Isolate independent Multiple relationships Fifth NF --> Isolate Semantically related multiple relationships

De-normalization --> to improve performance using frequently performed calculations.

Data Definition Language (DDL) contains the commands used to create and destroy databases DDL Commands :

CREATE,

create table iot_ ( a number, b varchar2(10),

constraint pk_iot_ primary key (a, b) )

ALTER,

ALTER TABLE table_name RENAME TO new_table_name; ALTER TABLE table_name

ADD column_name column-definition; ALTER TABLE table_name

MODIFY column_name column_type; ALTER TABLE table_name

DROP COLUMN column_name; truncate table table_name; truncate cluster cluster_name; Delete

The DELETE command is used to remove rows from a table. A WHERE clause can be used to only remove some rows. If no WHERE condition is specified, all rows will be removed. After performing a DELETE operation you need to COMMIT or ROLLBACK the transaction to make the change permanent or to undo it. Note that this operation will cause all DELETE triggers on the table to fire.

SQL> SELECT COUNT(*) FROM emp;

SQL> DELETE FROM emp WHERE job = 'CLERK'; TRUNCATE

TRUNCATE removes all rows from a table. The operation cannot be rolled back and no triggers will be fired. As such, TRUCATE is faster and doesn't use as much undo space as a DELETE.

SQL> TRUNCATE TABLE emp; Table truncated.

DROP

The DROP command removes a table from the database. All the tables' rows, indexes and privileges will also be removed. No DML triggers will be fired. The operation cannot be rolled back.

SQL> DROP TABLE emp;

DROP and TRUNCATE are DDL commands, whereas DELETE is a DML command. Therefore DELETE operations can be rolled back (undone), while DROP and TRUNCATE operations cannot be rolled back. DML Commands:

Select

SELECT department_id, job_id INTO deptid, jobid FROM employees WHERE employee_id = 140; Insert

INSERT INTO suppliers (supplier_id, supplier_name) SELECT account_no, name FROM customers WHERE city = 'Newark';

MERGE:

MERGE INTO target_table tgt USING source_table src ON

( src.object_id = tgt.object_id ) WHEN MATCHED

THEN UPDATE SET tgt.object_name = src.object_name , tgt.object_type = src.object_type

WHEN NOT MATCHED THEN INSERT ( tgt.object_id , tgt.object_name , gt.object_type )

VALUES ( src.object_id , src.object_name , src.object_type ); Update multiple rows of data..

UPDATE CLASSCONVENINGS SET ACITVE_FLG = 2 WHERE CLASS_CONVENE_DATE = TO_DATE('2005-12-31 00:00:00', 'YYYY-MM-DD HH24:MI:SS') / COMMIT WORK ; / UPSERT

create or replace procedure ups(xa number) as

begin merge into mergetest m using dual on (a = xa) when not matched then insert (a,b) values (xa,1) when matched then update set b = b+1; end ups; / drop table mergetest;

create table mergetest(a number, b number); call ups(10); call ups(10); call ups(20); select * from mergetest

DCL involves operation of granting permission and some operations related to control the database

DCL Commands: GRANT,REVOKE Join

EQUI-JOIN --> comparison operator is equality

Non EQUI-JOIN--> comparison operator is not equal sign

Natural Join --> produce result that contain 2 identical rows ,1 row being Eliminated Self Join --> A Table joined with Itself

Outer Join --> Extended Inner Join

Table are joined contain matching values both matching & non matching rows returned. LEFT Outer Join --> Use Keyword LEFT [OUTER] or (*)

RIGHT Outer Join --> use keyword Right [OUTER] FULL Outer Join --> use (+)

Grant Permission Grant (All/Privilege)

On { Table Name Column-Name VIEW NAME } To { PUBLIC /USER LIST }

With Grant option

Public --> to all users of the system ALL --> all Privileges

With Grant --> All users can access Privileges REVOKE --> Take away privileges given by Grant Revoke { Privileges List / ALL }

On Table /Coloumn /View } From { Public /User List }

I ntegrity Constraints --> restrict data values that can be inserted into database that could be inserted into database that could be deleted & Modified.

Referential Entity

Create Assertation – check (not Exists (cond)); Domain

Create Domain D-Name as Defn

Alter Domain D-Name‏Add‏………..‏/‏Drop‏………

Candidate Key --> unique identifier ( a column or combination of columns) of table

Foreign Key --> a column or combination of column in one table ,values required to match some candidate key in another table

Lock --> On Reading Data --> on Updating Data

Exclusive Lock --> during data modification on insert / delete / update Update --> no access to other users

Shared Lock --> read only access to other users.

Trigger. --> An action database should take PL / SQL Code

--> Need to be executed only once and will get invoked automatically till dropped Row Level Trigger --> once Fro each row of transaction

Before Trigger , After Trigger Create or Replace Trigger Trigger-Name Before /After

Delete / INSERT / UPDATE On [USER] Table-Name For Each Row

[PL/SQL Block] Backup: COMMIT; ROLLBACK;

SavePoint --> A point on transaction That you can rollback without rollback entire transaction. SavePoint sp1 SQL1; …….. SavePoint sp2 SQL2; Rollback sp2; --> rollback SP2 String DataTypes

CONCATINATION | | STRING | | STRING2

RPAD & LPAD --> Pad right side with any set of characters. LTRIM --> delete unwanted character on left end of string. RTRIM --> delete unwanted character on right end of string Length (string) --> 6

SUBSTR(String, Start[count]); --> generate substring from start.

INSTR(String, Set[,start[ocandea]); --> insert a string at desired Location. SOUNDEX --> Compare sound entry in selected coloumn

Dates

ADD_MONTHS (PDate ,2); PDATE --> Present date in Table Least

Greatest

DUAL --> Inbuilt Table provides with 1 row & 1 column initialized by Oracle. NEXT_DAY LAST_DAY MONTHS_BETWEEN TO_DATE ROUND TRUNC TO-CHAR NEW-DATE

SYNONYM --> Alias Name to Object.

Create Synonym emp for emp1.employee select * from empl.employee --> replace emp in table name; Drop Synonym emp;

SNAPSHOT --> Read Only Table Simple SnapShot

Complex SnapShot Create SnapShot emp;

DECLARE --> Define a CURSOR

OPEN --> Define a executable statement FETCH --> Return Data from Resultant Table CLOSE --> Release all resources.

For Nth Highest salary

SELECT DISTINCT (a.sal) FROM EMP A WHERE &N = (SELECT COUNT (DISTINCT (b.sal)) FROM EMP B WHERE a.sal<=b.sal);

JOINS: