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 SQLStatements:
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