DML DDL TCL DCL
(data manipulation): (data definition lang.): (transaction control lang.): (data control lang.):
Auto commit-off Auto commit on
Insert Create Commit Grant
Update Alter Rollback revoke
Delete Drop Save point
merge truncate
RETRIVAL: select
SQL is not case sensitive but it is case sensitive within the table
Describe : is used to see structure of a table
• SELECT - extracts data from a database
• UPDATE - updates data in a database
• DELETE - deletes data from a database
• INSERT INTO - inserts new data into a database
Distinct : Used to remove duplicate values in column.
Ex: select distinct deptno from emp .it displays distinct or unique department numbers
Where clause: used for filtering records.
Ex: select * from emp
Where
>,<,=,not NULL,and, or…etc
AND: both left side and right side should be true to return a value.
OR: returns a value if either of conditions is ture.
LIKE: This allows you to perform pattern matching.
% allows you to match any string of any length (including zero length)
_ allows you to match on a single character
IN: The IN function helps reduce the need to use multiple OR conditions.
SELECT columns
FROM tables
WHERE column1 in (value1, value2, .... value_n);
Orderby:used to arrange records in ascending or descending order, by default sql arranges in ascending order.
Group by: The GROUP BY statement is used in conjunction with the aggregate functions to group the result-set by one or more columns.
WHERE clause constraints are evaluated on the data before it is grouped.
HAVING clause constraints are evaluated on the results of the data after it has been grouped together. You can specify multiple columns in a GROUP BY clause
SELECT STAMETENT and group statement should have same number of columns.
Sql functions: performs calculations on data, convert data to different forms. All sql funitons returns a value sometime they accept input parameters. Sql funtions are of 2 types
http://www.w3schools.com/sql/sql_functions.asp
SQL Aggregate Functions
SQL aggregate functions return a single value, calculated from values in a column.
Useful aggregate functions:
• AVG() - Returns the average value
• COUNT() - Returns the number of rows
• FIRST() - Returns the first value
• LAST() - Returns the last value
• MAX() - Returns the largest value
• MIN() - Returns the smallest value
• SUM() - Returns the sum
SQL Scalar functions
SQL scalar functions return a single value, based on the input value.
Useful scalar functions:
• upper() - Converts a field to upper case
• lower() - Converts a field to lower case
length() - Returns the length of a text field
• ROUND() - Rounds a numeric field to the number of decimals specified or whole no.
Dual: is a dummy functions used with one row and one column.
Convertion functions :
1. Implicit convertion: automatically converts one type to another
A=number b=varchar
b=a , implicit convertion automatically converts number datatype to varchar.
And varchar to date
2. Explicit conversion:
To_char from(date or number)-> char
To_number from(varchar)->number
To_date
To_lob
Count: it counts number of rows in column and will not count null values.
DECODE: works like if,else if ,else condition
Ex: select job, decode(job, ’analyst’,10,’manager’,20,40) from emp
It assigns 10 to analyst
20 to manager
40 t o others.
CASE: this is similar to decode but its advanced version over it. Decode is a deprecated method and not preferred to used in latest versions.
Case when job=’analyst’ then 10
When job=’manager’ then 20
Else 40
End case
From employe.
select distinct job,
case JOB when 'ANALYST' then 10
when 'MANAGER' then 20
else 40
END CASE FROM EMP
TRANSLATE AND REPLACE:
Translate:does by character by character
or example:
translate('1tech23', '123','456'); would return '4tech56'
translate('222tech', '2ec', '3it'); would return '333tith'
Replace: replaces entire string
Joins: used to get data form multiple tables
Different types of joins are:
1.Cartesian join
Equi joint+ non equi joint
2.inner join
a.equi join: Return rows when there is at least one match in both tables
3.outer join: matched rows plus ummatched rows from either of table. (+) used for outer join
a.left outer joint: Return all rows from the left table, even if there are no matches in the right table
b. right outer joint: Return all rows from the right table, even if there are no matches in the left table
c.full outer oint: Return rows when there is a match in one of the tables
4.self joint
SETS: union, union all, intersection ,minus
Set operator should satisy
1. Number of columns in each select statement should be same
2. Corresponding data type should be same
UNION ALL
Combines the results of two SELECT statements into one result set.
UNION
Combines the results of two SELECT statements into one result set, and then eliminates any duplicate rows from that result set.
MINUS
Takes the result set of one SELECT statement, and removes those rows that are also returned by a second SELECT statement.
INTERSECT
Returns only those rows that are returned by each of two SELECT statements.
.
• We cannot use orderby in header select statement
• Always header select statement has priority
Sub query: a sub query is a select statement that is embedded in one of the following classes of a select statement. Sub query may be single row or multi row..
Where,having,from….
WHERE clause
Most often, the subquery will be found in the WHERE clause. These subqueries are also called nested subqueries.
FROM clause
A subquery can also be found in the FROM clause. These are called inline views.
For example:
select suppliers.name, subquery1.total_amt
from suppliers,
(select supplier_id, Sum(orders.amount) as total_amt
from orders
group by supplier_id) subquery1,
where subquery1.supplier_id = suppliers.supplier_id;
In this example, we've created a subquery in the FROM clause as follows:
(select supplier_id, Sum(orders.amount) as total_amt
from orders
group by supplier_id) subquery1
This subquery has been aliased with the name subquery1. This will be the name used to reference this subquery or any of its fields.
SELECT clause
A subquery can also be found in the SELECT clause
The trick to placing a subquery in the select clause is that the subquery must return a single value. This is why an aggregate function such as SUM, COUNT, MIN, or MAX is commonly used in the subquery.
Pseudo-column
A pseudo-column is an Oracle assigned value (pseudo-field) used in the same context as an Oracle Database column, but not stored on disk
1. SYSDATE
2. SYSTIMESTAMP
3. ROWID
4. ROWNUM
5. USER
6. UID
7. LEVEL
8. CURRVAL
9. NEXTVAL
1. SYSDATE
It shows the Current date from the local or remore database . We can use the CURRENT_DATE also with for the same purpose.
Code : SELECT SYSDATE FROM DUAL
2. SYSTIMESTAMP
Systimestamp function returns the current system date and time (including fractional seconds and time zone) on your database.
Code :SELECT SYSTIMESTAMP FROM DUAL
3. ROWID
Rowid is a pseudo column that uniquely identifies a row within a table, but not within a database. It is possible for two rows of two different tables stored in the same cluster to have the same rowid
Code :SELECT ROWID FROM EMP
4. ROWNUM
Rownum numbers the records in a result set. The first record that meets the where criteria in a select statement is given rownum=1, and every subsequent record meeting that same criteria increases rownum.
Code :SELECT ROWNUM FROM EMP WHERE ROWNUM <=10
5. USER
User is a pseudo column that returns the name of the user currently connected to the session.
Code :SELECT USER FROM DUAL
6. UID
Uid is a pseudo column that returns the id number of a user currently connected to the session.
Code :SELECT UID FROM DUAL
7. LEVEL
LEVEL pseudo-column is an indication of how deep in the tree one is. It is used in hierarchical queries along with CONNECT by clause.
Code :SELECT level, empno, ename, mgrFROM emp
CONNECT BY PRIOR empno = mgr
START WITH mgr IS NULL
8. NEXTVAL
NEXTVAL is used to invoke a sequence. If nextval is invoked on a sequence, it makes sure that a unique number is generated.
Code :SELECT
9. CURRVAL
CURRVAL can only be used if a session has already called nextval on a trigger. currval will then return the same number that was generated with nextval.
Code :SELECT
DELETE STATEMENT: The DELETE statement allows you to delete a single record or multiple records from a table.
The syntax for the DELETE statement is:
DELETE FROM table
WHERE predicates;
TRUNCATE STATEMENT: The SQL TRUNCATE command is used to delete all the rows from the table and free the space containing the table.
Syntax to TRUNCATE a table:
TRUNCATE TABLE table_name;
TCL :
Commit
If the transaction is commited, the database ensures that the modified data can always be found, even if the database or the hard disk crashes. Of course, for the latter to be true, a decent backup and recovery concept must be in place.
Rollback
If a transaction is rolled back, the database system ensures that no trace of the modified data will be found, that is, the database behaves as though the transaction had never been started.
Oracle uses undo to execute a rollback.
DELETE (DML) delete a single record or multiple records from a table.
TRUNCATE(DDL) all the rows from the table
DROP(DDL) Drops table
INSERT Statement
The INSERT statement allows you to insert a single record or multiple records into a table.
INSERT INTO VALUE
INSERT INTO table (column-1, column-2, ... column-n)
VALUES(value-1, value-2, ... value-n);
INSERT INTO SELECT
INSERT INTO "table1" ("column1", "column2", ...)
SELECT "column3", "column4", ...
FROM "table2"
UPDATE statement
The UPDATE statement allows you to update a single record or multiple records in a table.
The syntax for the UPDATE statement is:
UPDATE tableNAME
SET column = expression
WHERE predicates;
CREATE TABLE Statement
The CREATE TABLE statement allows you to create and define a table.
The basic syntax for a CREATE TABLE statement is:
CREATE TABLE table_name
( column1 datatype null/not null,
column2 datatype null/not null,
...
);
Creating table from another table with data:
Create table
Data dictionary/catalog:http://www.oracle.com/pls/db92/db92.catalog_views?remark=homepage
constraints
• "Check" Constraint: This constraint validates incoming columns at row insert time. For example, rather than having an application verify that all occurrences of region are North, South, East, or West, an Oracle check constraint can be added to the table definition to ensure the validity of the region column.
• Not Null Constraint: This Oracle constraint is used to specify that a column may never contain a NULL value. This is enforced at SQL insert and update time.
• Primary Key Constraint: This Oracle constraint is used to identify the primary key for a table. This operation requires that the primary columns are unique, and this Oracle constraint will create a unique index on the target primary key.
• References Constraint: This is the foreign key constraint as implemented by Oracle. A references constraint is only applied at SQL insert and delete times. At SQL delete time, the references Oracle constraint can be used to ensure that an employee is not deleted, if rows still exist in the DEPENDENT table.
• Unique Constraint: This Oracle constraint is used to ensure that all column values within a table never contain a duplicate entry.
• DEFAULT Constraint
The DEFAULT constraint is used to insert a default value into a column.
Oracle constraint views:
DBA ALL USER
dba_cons_columns all_cons_columns user_cons_columns
dba_constraints all_constraints user_constraints
dba_indexes all_indexes user_indexes
dba_ind_partitions all_ind_partitions user_ind_partitions
dba_ind_subpartitions all_ind_subpartitions user_ind_subpartitions
Note:every constraint is uniquely identifie by its name.oracle create unique constraint name if we donot mention its name.
Syntax:
Create table
(
column1 datatype1
.
.
.
)
Create table
(
column1 datatype1 ;
.
.
Constraint
)
Constraint can be defined at column level or table level.
Check constraint CREATE TABLE Customer
(SID integer CHECK (SID > 0),
Last_Name varchar (30),
First_Name varchar(30));
Foreign key CREATE TABLE ORDERS
(Order_ID integer primary key,
Order_Date date,
Customer_SID integer references CUSTOMER(SID),
Amount double);
Not null key CREATE TABLE Customer
(SID integer NOT NULL,
Last_Name varchar (30) NOT NULL,
First_Name varchar(30));
Unique key cREATE TABLE Customer
(SID integer Unique,
Last_Name varchar (30),
First_Name varchar(30));
Primary key CREATE TABLE Customer
(SID integer PRIMARY KEY,
Last_Name varchar(30),
First_Name varchar(30));
Default key cREATE TABLE Student
(Student_ID integer Unique,
Last_Name varchar (30),
First_Name varchar (30),
Score DEFAULT 80);
Alter table commands:
Its a ddl command.
• Add new column or columns
• Modify a existing column
• Drop a column
• Enable/disable constraints
• Add a constraint
• Drop a constaint
• Rename table
• Rename column
SCHEMA: A schema is a collection of database objects. A schema is owned by a database user and has the same name as that user AND created at the time of user creation. Schema objects are logical structures created by users to contain, or reference, their data. Schema objects include structures like tables, views, and indexes. You can create and manipulate schema objects using sql commands.
“scott”(DB user)----à scott(schema name)
Some of the most common schema objects are defined in the following section.
§ Tables
Tables are the basic unit of data storage in an Oracle database. Database tables hold all user-accessible data. Each table has columns and rows. Columns in a table is the different types of information that the table will contain and all instances of such data is stored in rows.
§ Indexes
Indexes are optional structures associated with tables. Indexes can be created to increase the performance of data retrieval. Just as the index in a book helps you quickly locate specific information, an Oracle index provides an access path to table data.
Indexes are created on one or more columns of a table. After it is created, an index is automatically maintained and used by Oracle. Changes to table data (such as adding new rows, updating rows, or deleting rows) are automatically incorporated into all relevant indexes with complete transparency to the users.
§ Views
Views are customized presentations of data in one or more tables or other views. A view can also be considered a stored query. Views do not actually contain data. Rather, they derive their data from the tables on which they are based, referred to as the base tables of the views.
Like tables, views can be queried, updated, inserted into, and deleted from, with some restrictions. All operations performed on a view actually affect the base tables of the view.
Views provide an additional level of table security by restricting access to a predetermined set of rows and columns of a table. They also hide data complexity and store complex queries.
§ Clusters
Clusters are groups of one or more tables physically stored together because they share common columns and are often used together. Because related rows are physically stored together, disk access time improves.
Privileges
A privilege is a right to execute an SQL statement or to access another user's object. In Oracle, there are two types of privileges: system privileges and object privileges. A privileges can be assigned to a user or a role
System privileges
select name from system_privilege_map
Executing this statement, we find privileges like create session, drop user, alter database, see system privileges.
NAME EXEMPT ACCESS POLICY
---------------------------------------- ON COMMIT REFRESH
CREATE EXTERNAL JOB MERGE ANY VIEW
CHANGE NOTIFICATION ADMINISTER DATABASE TRIGGER
READ ANY FILE GROUP ADMINISTER RESOURCE MANAGER
MANAGE ANY FILE GROUP DROP ANY OUTLINE
MANAGE FILE GROUP ALTER ANY OUTLINE
EXEMPT IDENTITY POLICY CREATE ANY OUTLINE
CREATE ANY SQL PROFILE DROP ANY CONTEXT
ADMINISTER ANY SQL TUNING SET CREATE ANY CONTEXT
ADMINISTER SQL TUNING SET DEQUEUE ANY QUEUE
ALTER ANY SQL PROFILE ENQUEUE ANY QUEUE
DROP ANY SQL PROFILE MANAGE ANY QUEUE
SELECT ANY TRANSACTION DROP ANY DIMENSION
MANAGE SCHEDULER ALTER ANY DIMENSION
EXECUTE ANY CLASS CREATE ANY DIMENSION
EXECUTE ANY PROGRAM CREATE DIMENSION
CREATE ANY JOB UNDER ANY TABLE
CREATE JOB EXECUTE ANY INDEXTYPE
ADVISOR GLOBAL QUERY REWRITE
ANALYZE ANY DICTIONARY QUERY REWRITE
EXECUTE ANY RULE UNDER ANY VIEW
DROP ANY RULE DROP ANY INDEXTYPE
ALTER ANY RULE ALTER ANY INDEXTYPE
CREATE ANY RULE CREATE ANY INDEXTYPE
CREATE RULE CREATE INDEXTYPE
IMPORT FULL DATABASE EXECUTE ANY OPERATOR
EXPORT FULL DATABASE DROP ANY OPERATOR
EXECUTE ANY RULE SET ALTER ANY OPERATOR
DROP ANY RULE SET CREATE ANY OPERATOR
ALTER ANY RULE SET CREATE OPERATOR
CREATE ANY RULE SET EXECUTE ANY LIBRARY
CREATE RULE SET DROP ANY LIBRARY
EXECUTE ANY EVALUATION CONTEXT ALTER ANY LIBRARY
DROP ANY EVALUATION CONTEXT CREATE ANY LIBRARY
ALTER ANY EVALUATION CONTEXT CREATE LIBRARY
CREATE ANY EVALUATION CONTEXT UNDER ANY TYPE
CREATE EVALUATION CONTEXT EXECUTE ANY TYPE
GRANT ANY OBJECT PRIVILEGE DROP ANY TYPE
FLASHBACK ANY TABLE ALTER ANY TYPE
DEBUG ANY PROCEDURE CREATE ANY TYPE
DEBUG CONNECT SESSION CREATE TYPE
SELECT ANY DICTIONARY DROP ANY DIRECTORY
RESUMABLE CREATE ANY DIRECTORY
DROP ANY MATERIALIZED VIEW CREATE SYNONYM
ALTER ANY MATERIALIZED VIEW DROP ANY INDEX
CREATE ANY MATERIALIZED VIEW ALTER ANY INDEX
CREATE MATERIALIZED VIEW CREATE ANY INDEX
GRANT ANY PRIVILEGE DROP ANY CLUSTER
ANALYZE ANY ALTER ANY CLUSTER
ALTER RESOURCE COST CREATE ANY CLUSTER
DROP PROFILE CREATE CLUSTER
ALTER PROFILE DELETE ANY TABLE
CREATE PROFILE UPDATE ANY TABLE
DROP ANY TRIGGER INSERT ANY TABLE
ALTER ANY TRIGGER SELECT ANY TABLE
CREATE ANY TRIGGER COMMENT ANY TABLE
CREATE TRIGGER LOCK ANY TABLE
EXECUTE ANY PROCEDURE DROP ANY TABLE
DROP ANY PROCEDURE BACKUP ANY TABLE
ALTER ANY PROCEDURE ALTER ANY TABLE
CREATE ANY PROCEDURE CREATE ANY TABLE
CREATE PROCEDURE CREATE TABLE
FORCE ANY TRANSACTION DROP ROLLBACK SEGMENT
FORCE TRANSACTION ALTER ROLLBACK SEGMENT
ALTER DATABASE CREATE ROLLBACK SEGMENT
AUDIT ANY DROP USER
ALTER ANY ROLE ALTER USER
GRANT ANY ROLE BECOME USER
DROP ANY ROLE CREATE USER
CREATE ROLE UNLIMITED TABLESPACE
DROP PUBLIC DATABASE LINK DROP TABLESPACE
CREATE PUBLIC DATABASE LINK MANAGE TABLESPACE
CREATE DATABASE LINK ALTER TABLESPACE
SELECT ANY SEQUENCE CREATE TABLESPACE
DROP ANY SEQUENCE RESTRICTED SESSION
ALTER ANY SEQUENCE ALTER SESSION
CREATE ANY SEQUENCE CREATE SESSION
CREATE SEQUENCE AUDIT SYSTEM
DROP ANY VIEW ALTER SYSTEM
CREATE ANY VIEW
CREATE VIEW
DROP PUBLIC SYNONYM
CREATE PUBLIC SYNONYM
SYSOPER
SYSDBA
DROP ANY SYNONYM
CREATE ANY SYNONYM
Arguably, the most important system privileges are:
• create session (A user cannot login without this privilege. If he tries, he gets an ORA-01045).
• create table
• create view
• create procedure
• sysdba
• sysoper
Object privileges
privileges can be assigned to the following types of database objects:
• Tables
select, insert, update, delete, alter, debug, flashback, on commit refresh, query rewrite, references, all
• Views
select, insert, update, delete, under, references, flashback, debug
• Sequence
alter, select
• Packeges, Procedures, Functions (Java classes, sources...)
execute, debug
• Materialized Views
delete, flashback, insert, select, update
• Directories
read, write
• Libraries
execute
• User defined types
execute, debug, under
• Operators
execute
• Indextypes
execute
For a user to be able to access an object in another user's schema, he needs the according object privilege.
Object privileges can be displayed using all_tab_privs_made or user_tab_privs_made.
Grant command: is used to grant permission to users and assigned by dba .
Revoke command is uesed to revoke privilages from databasel
Creating user:with the help of create user statement ,oracle database users can be created.
Create user
Identified by
Grant privilage1,....privilageN to user1............userN to user1,..........userN
Revoke privilage1......privilageN to user1.........userN to user1,............userN
Grant create table to
Grant create any table to
Role:role is a named group of related privileges which can be granted to users
ADV:
1.Ease ot use
2.maintenance is easy
Steps to create and assign role to users:
1.create a role
Create role
2.assign privileges to role
Grant
3.assign role to users
Grant
*grant statement with ,with grant option
aàbàc
grant select on emp to b
with grant option
now b can access and b can grant permission to c user.
*grant statement with ,public- it grants permission to all users of DB
Sequence: is a DB object, used to generate a sequence numbere.
Most of the cases sequence number can be used to generate primary keys of table.
Syntax:
Create sequence
Increment by
Start with
Maxvalue
Minvalue
Cyle/no clycle ----by default no cylcle
Cache
If you omit the MAXVALUE option, your sequence will automatically default to:
MAXVALUE 999999999999999999999999999
In sequence we use nextval and currval pseudo columns
Question: While creating a sequence, what does cache and nocache options mean? For example, you could create a sequence with a cache of 20 as follows:
CREATE SEQUENCE supplier_seq
MINVALUE 1
START WITH 1
INCREMENT BY 1
CACHE 20;
Or you could create the same sequence with the nocache option:
CREATE SEQUENCE supplier_seq
MINVALUE 1
START WITH 1
INCREMENT BY 1
NOCACHE;
Answer: With respect to a sequence, the cache option specifies how many sequence values will be stored in memory for faster access.
By using alter statement we can change the following;
• Maxvalue
• Increment by
• Cylce
• Cache
But we cannot change “start with “
Syntax: alter sequence
Datadictionary:
Dba_sequnce/all_sequence/user_sequence
Synonyms: Is an alias or alternative name for objects such as tables, views, sequences, stored procedures, and other database objects.and will not occupy any of the space.
Advantages:
• Mask the name and owner of an object,additional security of object level
• Location transparency for remote object of a distributed environment
• Simplify sql statements
Creating or replacing a synonym
The syntax for creating a synonym is:
create [or replace] [public] synonym [schema .] synonym_name
for [schema .] object_name [@ dblink];
public:any one can access
drop [public] synonym [schema .] synonym_name [force];
Datadictionary:
Dba_synonyms/all_synonyms/user_synonyms
INDEX:
• Index always assciated to table ,for faster information retrieval
• Physical independent objects,which occupies extrastorage on the database.
• We can create and drop indexes with out effecting base table
• Indexes can be created on column or columns
• If any index contains more than one column then it si called as a composite index.32 and 30for bitmap index
Note:we cannot create a index on long/long raw columns
Types of index:
1.B tree index ----default----32
2.bitmap index ---30
3.funtion based index ---32
4.unique or non unique index --32
When ever you create a primary key on column oracle will create unique index on that column.
General guide line for creating index:
1.create an index if you are retreving less than 15% of total rows from a table.
2.to improve performance on joints of multiple table indexed columns are best suitable for indexes
3.generally small table are not required for index
4.indexed column should be used in where clause for better performance
The syntax for creating a index is:
CREATE [UNIQUE|BITMAP] INDEX index_name
ON table_name (column1, column2, . column_n)
[ COMPUTE STATISTICS ];
Drop index
Bitmap:DSS(decesion support system)/data warehousing application .it stores index entries in form of bit(0 ro 1).so on what column we are going to create bitmap index? –on columns with less cardinality i.e number of distinct rows are less
Ex:gender (F or M),deptno.
Empno/lastname/ssn-----not suitable for bit map index.
Funtional basec index:
Create index
It is not recommend to enable indexing when inseritng large data because it degrades the process so we need to disable index during file load and enable after finishing.
During this process there is a chance for unbalancing of indexes so we need to rebuild indexes.
Alter index
Monitor is used to monitor usefullness of indexes.
Alter index
Then select statement.....
Select * from v$object_usages;
To disable alter index
Composite index: it contains more than one index
Datadictionary:
Dba_index/all_index/user_index
VIEW:logica object or table based on a one or more table or another view associated to select statement.view does not contain any data like table when ever you are selecting a data from view,that means you are selecting data from table associated to view.we can do all dml operations on view with some restirctions.
Ex: abc(select ename from emp);
Select * from abc;
Syntax:
Create or replace view
As