1) What is SQL and where does it come from?
Structured
Query Language (SQL) is a language that provides an interface to Relational
database systems. SQL was developed by IBM in the 1970s for use in System, and
are a de facto standard, as well as an ISO and ANSI standard. SQL is often
pronounced SEQUEL.
In
common usage SQL also encompasses DML (Data Manipulation Language), for
Inserts, Updates, Deletes and DDL (Data Definition Language), used for creating
and modifying tables And other database structures.
The
development of SQL is governed by standards. A major revision to the SQL
standard was completed in 1992, called SQL2. SQL3 support object extensions and
will be (partially?) Implemented in Oracle8.
2) DDL home
DDL is
Data Definition Language statements. Some examples:
CREATE
- to create objects in the database
ALTER -
alters the structure of the database
DROP -
delete objects from the database
TRUNCATE
- remove all records from a table, including all spaces allocated for the
records
Are
removed
COMMENT
- add comments to the data dictionary
GRANT -
gives user's access privileges to database
REVOKE
- withdraw access privileges given with the GRANT command
Difference between Delete and truncate
1) Delete-DML Truncate
-It will mark the records to
delete -It is DDL
-We can rollback -WE CAN'T ROLLBACK
-We can delete the records by
condition -WE CAN'T APPLY CONDITIONS
-IF ANY LOCKS ARE THREE ON THE
TABLE
THEY WILL RELEASED
-It will releases the space and we
can latter we use that space From oracle 8i onwards we can a drop a column
Alter
table <Table_name> drop column <column_name>
Alter
table <table_name> set unused (last_name)
Alter
table <table_name> drop unused columns
All
this type of values are stored in user_unused_col_tab table
3) DCL
DCL is
Data Control Language statements. Some examples:
COMMIT
- save work done
SAVEPOINT
- identify a point in a transaction to which you can later roll back
ROLLBACK
- restore database to original since the last COMMIT
SET
TRANSACTION - Change transaction options like what rollback segment to use
Savepoint
Rollback: Oracle
Issues implicit commit before and after any DDL statement. Even if your DDL
statement does not execute then also it issues commit statement
If we issue commit all
savepoints will be erased and all locks are released.
4) DML
SELECT
- retrieve data from the a database
INSERT -
insert data into a table
UPDATE
- updates existing data within a table
DELETE
- deletes all records from a table, the space for the records remain
CALL -
call a PL/SQL or Java subprogram
EXPLAIN
PLAN - explain access path to data
LOCK
TABLE - control concurrency
MUTATING TABLE:
Mutating Table is a table that is currently being modified by a DML statement.
For a Trigger, this is the
table on which the table is defined.
DML Statement:
Select, Update, Delete, Insert
Commit->it we
issue the commit all savepoints are erased and all locks are released; If we
disconnect the database then implicit commit statement will be issued.
Diff between POST and COMMIT:
POST
puts the data from client to the server’s Rollback segment whereas COMMIT put
the data from rollback segment to the Table.
A
transaction is an operation against the database, which comprises a series of
changes to one or more tables
A
transaction begins when the first executable DML and DDL command is encountered
and ends when one of the following occurs
*
commit/Rollback/DDL command /log off/machine failure
6Locks
Shared/exclusive
-When 2 transaction wants to read/write from db at the same time.
1)
Table level2) row level
Row Exclusive locks are obtained when
updating, inserting or deleting rows
Duration of locks
) All
locks acquired during a transaction are released when the transaction is committed
2) All
locks are acquired during a transaction are released when the transaction is
rolled back
3) All
locks are acquired after a savepoint are released when the transaction is
rolled back to the savepoint
Dead
lock-It is will come in multi-user environment
When
the two users issues update statements then dead lock will come
Dead-
1trans updates EMP and dep
2 trans
update dep and EMP
Dead
locks are released when commit/rollback statements are issued or logging off
7) How does one code a tree-structured
query?
Select
LEVEL, EMPNO, ENAME, MGR
From EMP
Connect by prior EMPNO = MGR
start with MGR is NULL;
8.JOINS
Def: -A join is used when a SQL query requires
data from more than one table or the same table on the database.
TYPES OF JOINS:
1) EQUI-JOIN: a join that is formed as a result
of an exact match b/w two columns is called as equi-join or simple join or
inner join.
2)
NON-EQUI-JOIN: - NON-EQUI-JOIN is a join condition containing something other
than an equality
operator.
A join
is that is formed based on the comparison operators (except "=") is
called non-equi join.
EX: -
SQL> SELECT e.last_name, e.salary, j.grade_level
FROM employee e, job_grades j
WHERE e.salary
BETWEEN j.lowest_sal AND
j.highest_sal;
3)
SELF-JOIN: A join relates a table to itself is called a self-join.
EX: -
SQL> SELECT e.empno, e.ename, m.mgr from emp e, emp m where e.empno=m.mgr;
4)
OUTER-JOIN: (+) always at the child side.
--You can use OUTER-JOIN to also see
rows that do not meet the join condition.
--The outer-join operator is the plus sigh (+).
--The missing rows can be returned
if outer-join operator is used in join condition.
EX:
-SQL> SELECT E.EMPNO, D.DEPTNO FROM EMP E, DEPT D WHERE E.DEPTNO
(+)=D.DEPTNO;
CONVERSION FUNCTIONS:
TO_CHAR,
TO_DATE, TO_NUMBER.
DATE FUNCTIONS:
ADD_MONTHS,
LAST_DAY, MONTHS_BETWEEN,
NEW_TIME,
NEXT_DAY, ROUND,
SYSDATE,
TRUNC.
DECODE:
Nested IF THEN ELSE
SELECT DECODE (ename,
‘Smith’, ’a’, ‘Something’);
GROUP FUNCTIONS:
MAX () MIN () COUNT () AVG () SUM
() VARIANCE ()
STDDEV
()
GROUP
FUNCTION WITH having CLAUSE CONDITION:
NOTE:
ALL GROUP FUNCTIONS IGNORE MULL VALUES IN THE COLUMN.
EX:
SQL> SELECT job_id, SUM (salary) PAYROLL
FROM employee
WHERE job_id NOT LIKE '%REP%'
GROUP BY job_id
HAVING SUM (salary)>1000
ORDER BY SUM (salary);
A
Subquery is a SELECT statement that is embedded in a clause of another SELECT
statement.
The
inner query or the subquery returns a value that is used by the outer query or
the main query.
You can
write upto 255 subqueries.
TYPES OF SUBQUERIES:
1.Single-Row
subquery: Query that returns only one row from the inner SELECT statement.
Simple Query--Which fires every time
for entire stmt
2.Multiple-Row
subquery: Query that returns more than one row from the inner SELECT statement.
CORRELATED SUBQUERY:
SQL*Plus
performs a subquery repeatedly once for every row of the main query.
Correlated
Subquery -Which fires only once/ per row for entire stmt.
Operators:
Logical
Operators: NOT, AND, OR
Comparison
Operators: NOT IN, LIKE
Set Operators:
UNION, UNION ALL, INTERSECTION, MINUS
- The SET operator combine the result
of two or more component queries into one result.
- Queries containing SET operator are called COMPOUND QUERIES.
- All SET operators have equal precedence.
1.UNION: All
distinct rows selected by either query
EX: select
job from emp where deptno=10
union
Select job from emp where deptno=20
2.UNION ALL: All
rows selected by either query, including all duplicates.
EX: select job from emp where deptno=10
union
all
Select job from emp where deptno=20
3.INTERSECT: All
distinct rows selected by both queries.
EX: select job from emp where deptno=10
intersect
Select job from emp where deptno=20
4.MINUS: All
distinct rows that are selected by the first SELECT statement and not selected
in the second SELECT statement.
EX: select job from emp where deptno=10
minus
Select job from emp where deptno=20
They
behave like table columns, but are not actually stored in the table.
CURRVAL,
NEXTVAL, ROWID, ROWNUM, SYSDATE, LEVEL, USER, UID PARSING:
It
checks the Syntax of SQL Statements.
13.INTEGRITY CONSTANTS:
Null,
Not Null, Default, Check, Primary Key, Foreign Key
PRIMARY KEY: NOT NULL, UNIQUE, IF U WANT CREATE MORE THEN ONE COLUM WE SHOULD
DEFINE AT TABLE LEVEL, IT'S ALSO CALLED COMPOSITE PRIMARY KEY, WE CAN'T CREATE
MORE THAN ONE PRIMARY KEY.
UNIQUE:IT WILL NOT
ALLOW DUPLICATE VALUES AND IT ACCEPT NULL VALUES CAN BE DEFINED AT THE COLUMN
LEVEL AND TABLE LEAVEL
CHECK: IT WILL
CHECK WITH COLUMN DEFINATION BEFORE ACCEPT.CHECK CONSTRAINT DEFINES A CONDITION
THAT EACH ROW MUST SATISFY REFERENCES TO THE CURRVAL, NEXTVAL, LEVEL, ROWNUM
PSEUDOCOLUMNS CALLS TO SYSDATE UID USER, AND USERENV. CHECK CONS. CAN BE
DEFINED AT THE COLUMN LEVEL AND TABLE LEVEL
FORIEGN KEY:
- foreign KEYS
POVIDE REFERENTIAL INTEGRITY RULES WITHER WITHIN A TABLE OR B/W TABLES
- CAN
BE DEFINED AT TABLE LEVEL OR COLUMN LEVEL.A COMPOSITE FORIEGN KEY MUST BE
CREATED BY USING THE TABLE-LEVEL DEFINATION
- A
FORIEGN KEY IS USED IN A RELATIONSHIP WITH EITHER PRIMARY OR UNIQUE KEY.
References -
identifies the table and column in the parent table.
ON DELETE CASCADE -
Deletes the dependent rows in the child table when a row in the parent
table is deleted.
ON DELETE SET NULL -
CONVERTS DEPENDENT FORIEGN KEY VALUES TO NULL.
CASCADE CONSTRAINT -
The CASCADE CONSTRAINTS clause drops all referential integrity constraints that
refer to the primary and unique keys defined on the dropped columns.
The
CASCADE CONSTRAINTS clause also drops all multicolumn constraints
defined in the dropped
columns.
CREATE
INDEXES IF U FREQUENTLY WANT TO RETRIEVE LESS THAN 15% OF THE ROWS IN A
LARGE TABLE
- INDEX CLOUMNS USED FOR JOINS TO
IMPROVE THE PERFORMANCE ON JOINS.
- DONT USE INDEXES ON TABLES HAVING LESS
NO ROWS.
- IF THE COLUMN HAS NON-UNIQUE
VALUES U CAN USE INDEXES ON THESE COLUMNS.
- DONT USE INDEXES IF THE DATATYPE
IS LOB, CLOB & BLOB.
- IF THE TABLE IS read only WE CAN
CREATE MORE INDEXES ON THE TABLE.
- WE CAN'T CREATE INDEXES ON VIEWS.
*- INDEXES ARE LOGICALLLY &
PHISICALLY INDEPENDENT OF DATA
*- IF THE INDEX IS DEVLOPED ALL
APLLICATIONS CONTINUE TO FUNCTION
Index is an ordered list of
contents of a column or group of columns in a table. Index created on a single
table Simple Index and which is created on multiple tables is called Composite
Index.
CREATE
INDEX Index_Name ON Table_Name (Column_Name);
DROP
INDEX (Index_Name);
Cluster is a method of storing tables that are
intimately related and are often joined
Together into the same area on disk
- SYNONYM IS AN ALIAS FOR A TABLE,
VIEW, SEQUENCE & PROGRAM UNIT.
- A SYNONYM IS A NOT A SCHEMA
OBJECT, IT WILL REFER SCHEMA OBJECT.
- MASK THE REAL NAME AND OWNER OF A
SCHEMA OBJECT.
- PROVIDE PUBLIC ACCESS TO SCHEMA
OBJECT.
- PROVIDE LOCATION TRANSPARENCY FOR
TABLES, VIEWS, OR PROGRAM UNITS IF A REMOTE DATABASE.
- SIMPLIFY THE sql STATEMENTS FOR
DATABASE USERS
PUBLIC
SYNONYM & PRIVATE SYNONYM
17.VIEWS:
- VIEW IS A LOGICAL TABLE BASED ON A
TABLE OR ANOTHER VIEW.
- VIEW CONTAINS NO DATA OF ITS OWN BUT IS
LIKE A WINDOW THROUGH WHICH DATA FROM TABLES CAN BE
VIEWED OR CHANGED.
- VIEW IS STORED AS SELECT STATEMENT IN
THE DATA DICTIONARY.
WHY USE VIEWS:
- TO RESTRICT DATA ACCESS
- TO MAKE QUERIES EASY.
- TO PROVIDE DATA INDEPEMDENCE
- TO PRESENT DIFFERENT VIEWS OF THE
SAME DATA.
SIMPLE VIEW: DERIVES DATA FROM ONLY ONE TABLE, CONTAINS NO
FUNCTIONS OR GROUPS OF DATA, WE CAN
PERFORM dml OPERATIONS THROUGH THE VIEW.
COMPLEX VIEW:
DERIEVE DATA FROM MANY TABLES, CONTAINS FUNCTIONS OR GROUPS OF DATA, DOES NOT
ALWAYS ALLOW dml OPERATIONS THROUGH THE VIEW.
NOTE: - U CAN'T
CREATE INDEXES ON VIEWS.
- U CAM CREATE A VIEW WITHOUT A TABLE
USING force OPTION.
- CAM CREATE with check option & with
read only.
- We can modify, drop view.
SYNTAX:
CREATE OR REPLACE force/noforce VIEW viewname alias subquery with check option
Constraint
with read only.
If we
use NOT NULL constraint in the base table without default values then we can't
insert the row in the base table using the view
If we use decode function in creating the view
we can't update the row in the base table
If we
use these words
1)
Distinct, avg, count, max, min, stddev, sum, union, union all, intersect,
minums, rownum, start with prior to the we can't update the base table through
view
If we
rename the table then the view become invalid
But one
can add the columns and the columns must not be not null columns in the base
table
18.Table:
One can
rename the table. If we rename the table then all views, synonyms, proceudres,
triggers, functions become invalid
If we
drop the table all the indexes are dropped
All
rows corresponding cluster table are deleted from the blocks of the cluster
We can
decrease the length of the column if the data is not there in the table.
I one
can change the datatype at any point of time
If we
drop the column from the base table if the view is based on that column then
the view become invalid.
TO add
a not null column to a table which has already some records -
Alter
table a
Add (b
number default 1 not null)
Portioned table
If we
want to store the data of one table depending upon the range in different
blocks. By using this we can reduce the network traffic. Table partitioning
divides table data between two or more table spaces and physical data file on
separate disk.
Select * from emp partion (p1)
19.TUNING SQL
Statements:
Using
TK_PROF, EXPLAIN PLAN.
If you
have a system that is performing badly, a good way to identify problem SQL
statements is to trace a typical user session and then use TkProf to format the
output using the sort functions on the tkprof command line.
Explain plan is a representation
of the access path that is taken when a query is executed within Oracle.
1. SET TIME ON;
2. ALTER SESSION SET SQL_TRACE = ‘TRUE’;
3. Then run the required program unit
4. Sql_trace file will be created in
User_dump_dest (dir)
5. Since this trace_file is in the binary
format, run tkprof
6. $ Tkprof <trace_file.trc>
<output_file.txt> [options]
7. $ Tkprof <trace_file.trc>
<output_file.txt> [EXPLAIN=user/password]
TKPROF
allows you to analyze a trace file to determine where time is being spent and
what query plans are being used on SQL statements. Tkprof is an executable that
'parses' Oracle trace files to produce more readable output. Remember that all
the information in TkProf is available from the base trace file.
8. Elapsed time/num of rows * 1000 -->
'X' Mille Second.
To
discover the execution plan for a select statement. The explain plan statement
is most often used from sql* plus. Before that you must create the <plan
table> to hold the results
Find
INDEXES is being used:
By
using EXPLAIN PLAN.
Output
is put into PLAN_TABLE.
To
diagnosing performance problems on running systems.
To know
No of
times the sql statement executed.
Total
CPU and elapsed time used by the statement
Total
no of physical reads trigger by the statement
Total
no of records processed by the statement
Total
no of the logical reads trigger by the statement
Hints:
They
can be placed into your Sql statements to force the optimizers to utilize a
particular execution path for absolute best performance.
/*+
ALL_ROWS */
/*+
FIRST_ROWS */
/*+
CHOOSE */
/*+
HASH_SJ */Ã When using subquery after EXISTSÃ It improves the response time.
- Materialized views provide indirect
access to table data by storing the results of a query in a separate schema
object.
- Another name for materialized view is
SNAPSHOT.
- MATERIALIZED VIEW CAN BE STORED IN A SAME
DATABASE OR DIFF DATABASE.
- DATA CAN BE REPLICATED USING MATERIALIZED
VIEW.E
- It is used in a
DISTRIBUTED ENVIRONMENT.
- It guarantees that no
matter what type of system or network failure might occur a distributed
transaction either commits on all involve nodes or rollback on all involve nodes to
maintain data consistency.
- It means that each database
participating in a distributed database is administered separately and independently from
other database.
- The first step of recovery is to
ROLL-FORWARD i.e. reapply to the data files all of the changes that are
recorded in the REDO-LOG FILE.
Rolling
Forward -To reapply to Data file to all changes that are recorded in Redo log
file
Due to
which data file contains committed & uncommitted data.
Forward
Declaration-To declare variable and procedures before using it.
2- Tier
Arch. Disadv-When Business Rule changes.
11.CAN U SELECT PROCEDURE FROM SQL STATEMENT?
Ans: No
we can't select procedure from select statement, but we can select SQL
FUNCTIONS AND
USER DEFINED FUNCTIONS.
26.Eliminate Duplicate records:
How
does one eliminate duplicates rows from a table?
ROWID:
ROWID
is the fastest way to locate records.
ROWID
Uniquely identifies records.
Choose
one of the following queries to identify or remove duplicate rows from a table
Leaving unique records in the table:
Method
1:
SQL> DELETE FROM table_name A WHERE ROWID
> (
2
SELECT min (rowid) FROM table_name B
3
WHERE A.key_values = B.key_values);
Method
2:
SQL> create table table_name2 as select
distinct * from table_name1;
SQL> drop table_name1;
SQL> rename table_name2 to table_name1;
Method
3: (thanks to Kenneth R Vanluvanee)
SQL> Delete from my_table where rowid not
in (
SQL> select max (rowid) from my_table
SQL> group by my_column_name);
Method
4: (thanks to Dennis Gurnick)
SQL> delete from my_table t1
SQL> where exists (select 'x' from
my_table t2
SQL> where t2.key_value1 = t1.key_value1
SQL> and t2.key_value2 = t1.key_value2
SQL> and t2.rowid > t1.rowid);
Large Objects Datatypes. Lets us
store blocks of unstructured data (text, graphic images, video files, audio files) of upto
four Gb of size.
Bfile: BFILE datatype stores large
Binary Objects in OS files outside the database.
Blob: BLOB datatype stores large
Binary Objects in the database
Clob: CLOB datatype stores large
blocks of single-byte Character data in the database.
It is a version control utility.
PVCS change manager for Oracle can identify the location and impact of changes in Oracle
Applications.
30. TOAD
TOAD is a powerful tool that makes
PL/SQL development faster, easier and simpler.
It is a System Parameter
32. Normalization
It's a technique thru. Which we can
design the DB.
During normalization dependencies
can be identified which can cause pbs during deletion &
Updation .It is used in simplifying
the structure of table.
1NF-Unnorma;ised data transfer to
normalized form.
2NF-Functional dependencies can be
find out & decompose the table without loss of data.
3NF-Transist dependencies, every
non-key attribute is functionally dependant on just PK.
4NF(BCNF)-The relation, which has
multiple candidate keys, then we have to go for BCNF.
Denormalization-
At the same time when information is
required from more than one table at faster rate then it is
wiser to add some sort of dependencies.
No comments:
Post a Comment