CODD RULES:
Rule 1: The information rule:
All information in the database is to be represented in one and only one way,
ie in the form of rows and columns.
Rule 2: The guaranteed access rule:
All data must be accessible.
If you are able to store, you should able to retrieve.
Rule 3: Systematic treatment of null values:
The DBMS must allow each field to remain null (or empty). Specifically,
it must support a representation of "missing information and inapplicable information" that is systematic.
Treatment of null values should be same irrespective of datatype.
Rule 4: Active online catalog based on the relational model:
That is, users must be able to access the database's structure (catalog) using the same query language that they use to access the database's data.
Rule 5: The comprehensive data sublanguage rule:
Rule 6: The view updating rule:
All views that are theoretically updatable must be updatable by the system.
Rule 7: High-level insert, update, and delete:
The system must support set-at-a-time insert, update, and delete operators.
This rule states that insert, update, and delete operations should be supported for any retrievable set rather than just for a single row in a single table.
Rule 8: Physical data independence:
Changes to the physical level (how the data is stored, whether in arrays or linked lists etc.) must not require a change to an application based on the structure.
Rule 9: Logical data independence:
Changes to the logical level (tables, columns, rows, and so on) must not require a change to an application based on the structure.
Logical data independence is more difficult to achieve than physical data independence.
Rule 10: Integrity independence:
Integrity constraints must be specified separately from application programs and stored in the catalog.
It must be possible to change such constraints as and when appropriate without unnecessarily affecting existing applications.
Rule 11: Distribution independence:
Rule 12: The nonsubversion rule:
If the system provides a low-level (record-at-a-time) interface, then that interface should not subvert the system,
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.
2
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;
3
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} ];
4
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.
5
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;
===========================================
Exists:
-------------
The EXISTS condition is considered "to be met" if the subquery returns at least one row.
The syntax for the EXISTS condition is:
SELECT columns
FROM tables
WHERE EXISTS ( subquery );
Ex:
---
Q.> can we create a primary key on duplicate column?
Ans: Yes
Below are the examples
select * from ZZ_EMP
/
select * from user_cons_columns
where table_name='EMP1'
/
select * from user_indexes
where table_name='ZZ_EMP'
/
select * from user_constraints
where table_name='ZZ_EMP'
/
create index id1_eno on zz_emp(empno)
/
alter table ZZ_EMP add constraint c_pk_zzeno primary key(empno) enable novalidate
/
SELECT *
FROM dept
WHERE EXISTS
(select *
from emp
where dept.deptno = emp.deptno);
--------------------------------------
Not Exists:
SELECT *
FROM dept
WHERE NOT EXISTS
(select *
from emp
where dept.deptno = emp.deptno);
-------------------------------------
Merge:
create table student10 ( sno number(3),
sname varchar2(20),
marks number(3));
insert into student10 values ( 101, 'arun',30);
insert into student10 values ( 102, 'anil',40);
insert into student10 values ( 103, 'kiran',50);
create table student20 ( sno number(3),
sname varchar2(20),
marks number(3));
insert into student20 values ( 101, 'JOHN',30);
insert into student20 values ( 105, 'SMITH',50);
merge into student10 s1
using student20 s2
on ( s1.sno = s2.sno)
when matched
then update set sname=s2.sname, marks = s2.marks
when not matched
then insert (sno,sname,marks ) values (s2.sno,s2.sname,s2.marks);
------------------------------
Decode :
select empno,sal,job , decode (job, 'CLERK' , sal*2,
'MANAGER', sal*3
,sal ) new_sal
from emp;
select ename,sal, deptno, decode(deptno, 10, 'CA',
20, 'SCIENTIST',
'EMPLOYEE') NEW_NAME
from emp;
------------------------------------------
Case :
select empno,sal,job , case job when 'CLERK' then sal*2
when 'MANAGER' then sal*3
else sal
end new_sal
from emp;
Case 2nd example:
------------------------
SELECT ename, sal, deptno,
CASE deptno
WHEN 10
THEN 'ten'
WHEN 20
THEN 'twenty'
WHEN 30
THEN 'thirty'
ELSE NULL
END dept_no
FROM emp;
----------------------------------------
Rank :
In Oracle/PLSQL, the rank function returns the rank of a value in a group of values.
The rank function can be used two ways - as an Aggregate function or as an Analytic function.
---
Rank used as aggregate function:
select rank (800) within group (order by sal) rank from emp;
select rank (850) within group (order by sal) rank from emp;
select rank (5000) within group (order by sal) rank from emp;
Rank used as analytical function:
As an Analytic function, the rank returns the rank of each row of a query
select ename, sal,
rank() OVER (ORDER BY sal) rank
from emp;
--------------------------
NULLIF :
In Oracle/PLSQL, the NULLIF function compares expr1 and expr2. If expr1 and expr2 are equal, the NULLIF function returns NULL. Otherwise, it returns expr1.
The syntax for the NULLIF function is:
NULLIF( expr1, expr2 )
select NULLIF(12,12) from dual; -- returns NULL
select NULLIF(12,13) from dual; -- returns 12
-------------------------------
Rule 1: The information rule:
All information in the database is to be represented in one and only one way,
ie in the form of rows and columns.
Rule 2: The guaranteed access rule:
All data must be accessible.
If you are able to store, you should able to retrieve.
Rule 3: Systematic treatment of null values:
The DBMS must allow each field to remain null (or empty). Specifically,
it must support a representation of "missing information and inapplicable information" that is systematic.
Treatment of null values should be same irrespective of datatype.
Rule 4: Active online catalog based on the relational model:
That is, users must be able to access the database's structure (catalog) using the same query language that they use to access the database's data.
Rule 5: The comprehensive data sublanguage rule:
Rule 6: The view updating rule:
All views that are theoretically updatable must be updatable by the system.
Rule 7: High-level insert, update, and delete:
The system must support set-at-a-time insert, update, and delete operators.
This rule states that insert, update, and delete operations should be supported for any retrievable set rather than just for a single row in a single table.
Rule 8: Physical data independence:
Changes to the physical level (how the data is stored, whether in arrays or linked lists etc.) must not require a change to an application based on the structure.
Rule 9: Logical data independence:
Changes to the logical level (tables, columns, rows, and so on) must not require a change to an application based on the structure.
Logical data independence is more difficult to achieve than physical data independence.
Rule 10: Integrity independence:
Integrity constraints must be specified separately from application programs and stored in the catalog.
It must be possible to change such constraints as and when appropriate without unnecessarily affecting existing applications.
Rule 11: Distribution independence:
Rule 12: The nonsubversion rule:
If the system provides a low-level (record-at-a-time) interface, then that interface should not subvert the system,
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.
2
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;
3
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} ];
4
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.
5
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;
===========================================
Exists:
-------------
The EXISTS condition is considered "to be met" if the subquery returns at least one row.
The syntax for the EXISTS condition is:
SELECT columns
FROM tables
WHERE EXISTS ( subquery );
Ex:
---
Q.> can we create a primary key on duplicate column?
Ans: Yes
Below are the examples
select * from ZZ_EMP
/
select * from user_cons_columns
where table_name='EMP1'
/
select * from user_indexes
where table_name='ZZ_EMP'
/
select * from user_constraints
where table_name='ZZ_EMP'
/
create index id1_eno on zz_emp(empno)
/
alter table ZZ_EMP add constraint c_pk_zzeno primary key(empno) enable novalidate
/
SELECT *
FROM dept
WHERE EXISTS
(select *
from emp
where dept.deptno = emp.deptno);
--------------------------------------
Not Exists:
SELECT *
FROM dept
WHERE NOT EXISTS
(select *
from emp
where dept.deptno = emp.deptno);
-------------------------------------
Merge:
create table student10 ( sno number(3),
sname varchar2(20),
marks number(3));
insert into student10 values ( 101, 'arun',30);
insert into student10 values ( 102, 'anil',40);
insert into student10 values ( 103, 'kiran',50);
create table student20 ( sno number(3),
sname varchar2(20),
marks number(3));
insert into student20 values ( 101, 'JOHN',30);
insert into student20 values ( 105, 'SMITH',50);
merge into student10 s1
using student20 s2
on ( s1.sno = s2.sno)
when matched
then update set sname=s2.sname, marks = s2.marks
when not matched
then insert (sno,sname,marks ) values (s2.sno,s2.sname,s2.marks);
------------------------------
Decode :
select empno,sal,job , decode (job, 'CLERK' , sal*2,
'MANAGER', sal*3
,sal ) new_sal
from emp;
select ename,sal, deptno, decode(deptno, 10, 'CA',
20, 'SCIENTIST',
'EMPLOYEE') NEW_NAME
from emp;
------------------------------------------
Case :
select empno,sal,job , case job when 'CLERK' then sal*2
when 'MANAGER' then sal*3
else sal
end new_sal
from emp;
Case 2nd example:
------------------------
SELECT ename, sal, deptno,
CASE deptno
WHEN 10
THEN 'ten'
WHEN 20
THEN 'twenty'
WHEN 30
THEN 'thirty'
ELSE NULL
END dept_no
FROM emp;
----------------------------------------
Rank :
In Oracle/PLSQL, the rank function returns the rank of a value in a group of values.
The rank function can be used two ways - as an Aggregate function or as an Analytic function.
---
Rank used as aggregate function:
select rank (800) within group (order by sal) rank from emp;
select rank (850) within group (order by sal) rank from emp;
select rank (5000) within group (order by sal) rank from emp;
Rank used as analytical function:
As an Analytic function, the rank returns the rank of each row of a query
select ename, sal,
rank() OVER (ORDER BY sal) rank
from emp;
--------------------------
NULLIF :
In Oracle/PLSQL, the NULLIF function compares expr1 and expr2. If expr1 and expr2 are equal, the NULLIF function returns NULL. Otherwise, it returns expr1.
The syntax for the NULLIF function is:
NULLIF( expr1, expr2 )
select NULLIF(12,12) from dual; -- returns NULL
select NULLIF(12,13) from dual; -- returns 12
-------------------------------
No comments:
Post a Comment