SQL Installation:

Step 1: First browse to this (https://www.filehorse.com/download-oracle-

database-express/screenshots/ )

Step 2: Then we have to click on the download button on the right side.

Step 3: Then on the next page you will find the Start Download button. There
you have to click.

Step 4: Then in the downloaded zip file there will be a setup.exe file. We
have to click & run it.
Step 5: Then the installation will start.

Step 6: Then you have to accept terms & click on Next

Step 7: Then you have to enter a password & confirm it. You have to
remember the password for future references.

Step 8: Then click Install.

Step 9: Installation will start & it may take some time.

Step 10: After installation, you will find oraclexe folder in the C drive.
 Step 11: You have to enter that folder. Then bin folder there you will find
sqlplus exe file. So, your installation is successfully completed.

SQL Concepts
 ORACLE TOOLS
The Oracle product is primarily divided into

 Oracle Server tools: Oracle Server Product is either called Oracle Workgroup Server or Oracle
Enterprise Server. Oracle Workgroup Server or Oracle Enterprise Server is used for data storage.
  Oracle Client tools: The client roll most commonly used for Commercial Application Development is
called Oracle Developer 2000. Oracle Developer 2000, Oracle’s tool box which consists of Oracle Forms,
Oracle Reports and Oracle Graphics. This suite of tools is used to capture, validate and display data
according to user and system needs.
 SQL*Plus is a separate Oracle client-side tool. Oracle Workgroup or Enterprise Server is bundled with
this SQL*Plus. It is a product that works on Microsoft Windows 95 and Windows NT both of which are
standard Client based GUI operating systems.
 Oracle Workgroup Server and Oracle Developer 2000 are separate products and they must be
purchased separately.

 SQL (commonly expanded to Structured Query Language):

 SQL is standard language for making queries in relational database packages such as SQL server,
Ingress, Sybase, Oracle etc.
 The ORACLE system uses non-procedural Structured Query Language to communicate with its database
kernel.
 SQL does not support any programming language constructs like if..else or while etc, but can be
embedded in other programming languages like C, COBOL, PL/ or ADA
 There are two types of SQL: 1) Interactive SQL 2) Embedded SQL
 Interactive SQL is used to operate directly on a database to produce output for our purpose.
 Embedded SQL consists of SQL commands put inside programs that are mostly written in some other
high level language. This method can make the program more powerful and efficient.

 SQL*PLUS
 SQL*PLUS is powerful ORACLE support that can take your instructions for oracle. This flexible tool
allows both developers and end-user to issue SQL commands directly against the database.
 Database developers use SQL*PLUS to create, fill and monitor an application’s database. End-users of
the database use it to perform ad hoc queries against the database.

 ORACLE DATATYPES
When you create table in SQL*PLUS, you must specify the type of data that may appear in each column.
Basic types of oracle are listed below:

Data Type Command Description

CHAR(size)  Fixed length character data.

 Size written in the bracket determines the length of data
that can be stored.
 Default size is 1 and maximum size is 255 characters.
 CHAR is much faster than VARCHAR, sometimes up to
Character
50%.
VARCHAR(size)  Variable length character string having maximum length
size in bytes.
 VARCHAR hold 1 to 255 characters.
 Size must be specified.
VARCHAR2(size)  Variable length character string having maximum length
size in bytes.
 Maximum size is 4000 characters.
 Size must be specified.
NUMBER(P,S)  Used to store variable length numeric data.
 P determines the total number of digits possible.
 S determines the total number of digits possible to the
right of decimal point.

Number NUMBER(size)  Fixed point number with precision size and scale 0.

Date DATE  This data type is used to store date and time information.
 Default format is DD-MON-YY.
 To enter the dates other than standard format, use the
appropriate functions.
Long LONG  Variable length character strings containing up to 2
gigabytes.
 Table cannot have more than one Long type of data field.
 It cannot be indexed.
 It cannot be used with SQL functions, sub queries,
expressions.
 It cannot appear in WHERE, GROUP BY, ORGER BY
clauses.
 Raw / RAW(size)  Raw and Long Raw data types are used to store binary
data such as digitized picture or image.
Long Raw LONG RAW(size)
 Raw data type can have a Maximum length of 255 bytes.
 Long Raw data type can contain up to 2 GB.
 Value stored in columns having LONG RAW data type
cannot be indexed.

 DATABASE LANGUAGE

 DDL: DDL (Data Definition Language) statements are used to define the database structure or schema.
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
 RENAME - rename an object
 DML: DML (Data Manipulation Language) statements are used for managing data within schema objects.
Some examples:
 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

 DCL: DCL is Data Control Language statements. Some examples:

 GRANT - gives user's access privileges to database
 REVOKE - withdraw access privileges given with the GRANT command
 Transaction Control: Manages the changes made by DML statements. These commands allow statements
to be grouped together into logical transactions.
 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
 Oracle (SQL) Commands (Statements):
(1) The CREATE TABLE command: The CREATE TABLE command defines each column of the table
uniquely. Each column has a minimum of three attributes, a name, data type and size (i.e. column
width).

 Rules for creating Tables:

1. A name can have maximum upto 30 characters.
2. Alphabets from A-Z, a-z and numbers from 0-9 are allowed.
3. A name should begin with an alphabet.
4. The use of the special character like _ is allowed and also recommended (Special characters like
$, # are allowed only inOracle).
5. SQL reserved words not allowed. For example: create, select and so on.

Syntax: CREATE TABLE <tablename>

(<ColumnName1><DataType>(<size>),

<ColumnName2><DataType>(<size>), …… );

Example: Create table client_master

(c_no varchar2(5), name varchar2(10), address varchar2(20),

pincode number(6), bal_due number(10,2));

(2) Inserting Data into Tables using INSERT INTO command: Once a table is created, most natural
thing to do is load this table with data to be manipulated later.

When inserting a single raw of data into the table, insert operation:

 Creates a new raw (empty) in the database table.

 Loads the values passed (by the SQL insert) into the columns specified.
Syntax: INSERT INTO <tablename> [(<ColumnName1>, <ColumnName2>, … )]
VALUES(<value1>,< value2>, ……);
 Example: INSERT INTO client_master (c_no, name, address, pincode, bal_due)

VALUES (‘C001’, ‘Alap’, ‘Ahmedabad’, 380063, 500 );

Character value (expression) placed within the INSERT INTO statement must be enclosed in single
quotes (‘).

(3) Viewing data in the Tables: Once data has been inserted into a table, the next most logical
operation would be to view what has been inserted. The SELECT SQL verb is used to achieve this.
The SELECT command is used to retrieve rows selected from one or more tables.
 The SELECT statement can be used to Display some or all the columns from a specified table.
 Display some or all of the rows from a specified table.
 Display calculated values from the table.
 Display statistical information from the tables, like averages or sums of column values.
 Combine information from two or more tables.

In order to view global table data the syntax is:

SELECT <ColumnName 1> TO <ColumnName N> from TableName;

Note: Here, ColumnName 1 to ColumnName N represents table column namesandthey separated by

‘,’.

All Rows and All Columns: When data from all rows and columns from the table are to be viewed the
syntax of the SELECT statement will be used. The syntax is:

Syntax: SELECT * FROM <TableName>;

Example: SELECT * FROM client_master;

Oracle allows the use of the Meta character asterisk (*), this is expanded by Oracle to mean all rows
and all columns in the table.

Displaying Some Columns from a Table:

 Syntax: SELECT <ColumnName 1>,<ColumnName 2>, …, <ColumnName N>

FROM <TableName>;

Example: SELECT c_no, name FROM client_master;

Note:

 The SELECT clause followed by the FROM clause are required for any SQL query.
 Not all columns need to be selected.
 Columns are displayed left or right in the order specified.
 SELECT list items must be separated by commas.
 Rows are returned in an arbitrary manner.
 Users may query only tables they have created or tables to which they have granted
access.
 If you want to see which tables you have in your account, a special query can be made.
Query is: SELECT* FROM tab;

Displaying Some Specified Rows from the Table:

If you want conditional retrieval of rows i.e. only those rows which satisfy certain condition.
You can use WHERE clause in the SELECT statement.

Syntax: SELECT <ColumnName 1>,<ColumnName 2>, …, <ColumnName N>

FROM <TableName> WHERE <Condition>;

Here,<Condtion>is always quantified as <ColumnName = Value>.

Example: SELECT c_no, name FROM client_master WHERE bal_due>500;

Note:

 Columns specified n the WHERE clause must be part of the table specified in the form clause.
 Columns used in the WHERE clause do not have to be in SELECT list.
 The WHERE clause must specify character data in the same case (upper or lower) that it is in
the database.
 The comparison operators that can be used in SQL statements are < ,> , <= .., >= , = , <>.
Elimination of duplicates from the select statement:

A table could hold duplicate rows. In such a case, to see only unique rows, you have to use DISTINCT
clause.

The DISTINCT clause allows removing duplicates from the result set. The DISTINCT clause can be only
be used with SELECT statements.

Syntax: SELECT DISTINCT <ColumnName 1>, …, <ColumnName N>

FROM <TableName>;

Example: SELECT DISTINCT job FROM emp;

The SELECT DISTINCT * SQL syntax scans through entire rows, and eliminates rows that have
exactly the same contents in each column.

Syntax: SELECT DISTINCT * FROM <TableName>;

Example: SELECT DISTINCT * FROM client_master;

Sorting data in a Table:

Oracle allows data from a table to be viewed in a sorted order. The rows retrieved from the table will
be sorted in either ascending or descending order depending on the condition specified in the SELECT
sentence.

Syntax: SELECT <ColumnName 1>, …, <ColumnName N> FROM <TableName> ORDER BY

<ColumnName 1>, …, <ColumnName N><[Sort Order]>;

Sort Order can be ascending (use word asc) or descending (use word desc). In case there is no
mention of the sort order, the Oracle engine sorts in ascending order by default.

Example: a. SELECT * FROM client_master ORDER BY Name; (In ascending order)

b. SELECT * FROM emp ORDER BY Job DESC; (In descending order)
(4) Creating a Table from a Table:

Syntax: CREATE TABLE <TableName>(<ColumnName 1>, …, <ColumnName N>)

AS SELECT <ColumnName 1>, …, <ColumnName N> FROM <TableName>

Example: Create a table named ACCT_DTLS having three fields i.e. ACCT_NO, BRANCH_NO
and CURBAL from the table ACCT_MSTR and rename field CURBAL to BALACE.

CREATE TABLE ACCT_DTLS(ACCT_NO,BRANCH_NO,BALANCE)

AS SELECT ACCT_NO,BRANCH_NO,CURBAL FROM ACCT_MSTR;

The Source table is a table identified in the SELECT section of this SQL sentence. The Target
table is one identified in the CREATE section of this SQL sentence. This table sentence populates
the Target table with data from the Source table.

To create a Target table without the records from the source table (i.e. create the structure
only), the select statement must be a have a WHERE clause. The WHERE clause must specify
the condition that cannot be satisfied.

Example: Create a table named ACCT_DTLS having three fields i.e. ACCT_NO, BRANCH_NO
and CURBAL from the table ACCT_MSTR and rename field CURBAL to BALACE. The
table ACCT_DTLS should not be populated with any records.

CREATE TABLE ACCT_DTLS(ACCT_NO,BRANCH_NO,BALANCE)

AS SELECT ACCT_NO,BRANCH_NO,CURBAL FROM ACCT_MSTR

WHERE 1=2;
(5) Inserting Data into a Table from another Table: To insert data one row at a time into a table, it is
quite possible to populate a table with data that already exists in another table.

Syntax: INSERT INTO <TableName> SELECT <ColumnName1>, …, <ColumnName N> FROM

<TableName> [WHERE <Condition>];

Here the WHERE clause is optional. If you are not specify the WHERE clause then all the from source
table to target table is copied.

Example: Insert only the savings bank accounts details in the target table ACCT_DTLS from
the source table ACCT_MSTR.

INSERT INTO ACCT_DTLS SELECT ACCT_NO,BRANCH_NO,CURBAL FROM

ACCT_MSTR WHERE ACCT_NO LIKE ‘SB%’;

(6) Delete Operations:The DELETE command deletes rows from the table that satisfies the condition
provided by its WHERE clause, and returns the number of records deleted.

Removal of All Rows:

Syntax: DELETE FROM <TableName>;

Example: Empty the ACCT_DTLS table.

DELETE FROM ACCT_DTLS;

Removal of Specific Rows:

Syntax: DELETE FROM <TableName> WHERE <Condition>;

 Example: Remove only the savings bank account details from the ACCT_DTLS table.

DELETE FROM ACCT_DTLS WHERE ACCT_NO LIKE ‘SB%’;

(7) Updating the contents of a table: The UPDATE command is used to change or modify data values
in table.

Updating of All Rows:

Syntax: UPDATE <TableName> SET <ColumnName 1> = <Expression 1 or Value 1>,

<ColumnName N> = <Expression N or Value N>;

Example: Update the address details by changing its city name to Ahmedabad.

UPDATE ADDR_DTLS SET City = ‘Ahmedabad’;

Updating Records Conditionally:

Syntax: UPDATE <TableName> SET <ColumnName 1> = <Expression 1 or Value 1>,

<ColumnName N> = <Expression N or Value N> WHERE <Condition>;

Example: Update the branch details by changing the Vile Parle (HO) to Head Office.

UPDATE BRANCH_MSTR SET NAME = ‘Head Office’

WHERE NAME = ‘Vile Parle (HO)’;

(8) Modifying the Structure of Tables: The structure of a table can be modified by using ALTER TABLE
command. ALTER TABLE allows changing the structure of an existing table. With ALTER TABLE it is
possible to add or delete columns, change the data type of existing columns.
Adding New Columns:
 Syntax: ALTER TABLE <TableName>

ADD (<NewColumnName><DataType>(<Size>),

<NewColumnName><DataType>(<Size>), …);

Example: Enter a new field called city in the table BRANCH_MSTR.

ALTER TABLE BRANCH_MSTR ADD(CITY VARCHAR2(25));

Dropping a Column from a Table:

Syntax: ALTER TABLE <TableName> DROP COLUMN <ColumnName>;

Example: Drop the column city from the table BRANCH_MSTR.

ALTER TABLE BRANCH_MSTR DROP COLUMN CITY;

Modifying Existing Columns:

Syntax: ALTER TABLE <TableName>

MODIFY(<ColumnName><NewDataType>(<NewSize>));

Example: Alter table BRANCH_MSTR to allow the NAME field to hold maximum of 30
characters.

ALTER TABLE BRANCH_MSTR MODIFY(NAME VARCHAR2(30));

Restrictions on the ALTER TABLE:

The following tasks cannot be performed when using the ALTER TABLE clause:

1. Change the name of the table.

2. Change the name of the column.
3. Decrease the size of a column if table data exists.
 (9) Renaming Tables: Oracle allows renaming of tables. The rename operation is done atomically,
which means that no other thread can access any of the tables while the rename process is running.

Syntax: RENAME <TableName> TO <NewTableName>;

Example: Change the name of BRANCH_MSTR table to BRANCH table.

RENAME BRANCH_MSTR TO BRANCH;

(10) Truncating Tables: TRUNCATE TABLE empties a table completely. Logically, this is equivalent to a
DELETE statement that deletes all rows, but there are practical differences under some
circumstances.
TRUNCATE TABLE differs from DELETE in the following ways:

1. Truncate operations drop and re-create table, which is much faster than deleting rows one by
one.
2. Truncate operations are not transaction-safe (i.e. an error will occur if an active transaction or
an active table lock exists).
3. The numbers of deleted rows are not returned.

Syntax: TRUNCATE TABLE <TableName>;

Example: Truncate the table BRANCH_MSTR.

TRUNCATE TABLE BRANCH_MSTR;

(11) Destroying Tables: Sometimes tables within a particular database become obsolete and need to
be discarded. In such situation using DROP TABLE statement with the table name can destroy a
specific table. If a table is dropped all records held within it are lost and cannot be recovered.
Syntax: DROP TABLE <TableName>;

Example: Remove the table BRANCH_MSTR along with the data held.

DROP TABLE BRANCH_MSTR;

(12) Constraints:
 NOT NULL: NOT NULL column constraint ensures that a table column can not be left empty, It
Implies that a value must be entered into the column.

Syntax: <ColumnName><DataType>(Size) NOT NULL;

Example: Create Table Book (BookId Number(5)) NOT NULL ;

UNIQUE Constraint defined at the column level: UNIQUE column constraint permits multiple
entries of null into the column.

Unique key will not allow duplicate values.

Syntax: <ColumnName><DataType>(Size) UNIQUE;

Example: Create Table Book (BookId Number(5) UNIQUE;

UNIQUE Constraint defined at the table level:

Syntax: Create table TableName (<ColunName1> <DataType> (<size>) , <ColunName2>

<DataType> (<size>), UNIQUE (<Column1> , <Column2>));

Example: Create Table Book (BookId Number(5), BookName Varchar(10),

UNIQUE(BookId));

PRIMARY KEY Constraint defined at the column level: PRIMARY KEY uniquely identifies a row.It
will not allow duplicate values, also not allow null values.

Syntax: <ColumnName> <DataType> (Size) PRIMARY KEY;

Example: Create Table Book (BookId Number(5) PRIMARY KEY;

PRIMARY KEY Constraint defined at the table level: when we are using composite PRIMARY KEY
.
Syntax: PRIMARY KEY (<ColumnName>,<ColumnName>);

Example: Create Table Book (BookId Number(5),BookNameVarchar(10), PRIMARY

KEY(BookId));

FOREIGN KEY Constraint: A FOREIGN KEYa column or group of columns whose values are derived
from the primary key or unique key from other table

A table in which foreign key is defined is called child table or detailed table.

A table that defines primary key and reference by the foreign key called master table or parent
table.

FOREIGN KEY Constraint defined at the column level:

Syntax: <ColumnName> <DataType> (Size) REFERENCES <TableName>(ColumnName);

Example: Create Table Student (Sid Number(5), BookId Number(5) references Book(BookId)) ;

FOREIGN KEY Constraint defined at the table level:

Syntax: FOREIGN KEY (<ColumnName>) REFERENCE

<TableName>(<ColumnName>)
 Example: Create Table Student (Sid Number(5), BookId Number(5), FOREIGN KEY(BookId)
references Book(BookId)) ;

FOREIGN KEY Constraint with on delete cascade: Because of the cascade delete when a record
in parent table is deleted, records in the child table will also deleted.

Example: Create Table Student (Sid Number(5), BookId Number(5), FOREIGN KEY(BookId)
references Book(BookId) ON DELETE CASCADE) ;

FOREIGN KEY Constraint with on delete set NULL: If a record in the parent table is deleted than
the corresponding records in the child table will set to null, the records in the child table will not
be deleted.

Example: Create Table Student (Sid Number(5), BookId Number(5), FOREIGN KEY(BookId)
references Book(BookId) ON DELETE SET NULL) ;

CHECK Constraint defined at the column level:

Syntax: <ColumnName> <DataType> (Size) CHECK (logical expression)

Example: Create Table Licence (LId Number(5), age Number(5) CHECK (age >= 18)) ;

CHECK Constraint defined at the table level:

Syntax: CHECK (logical expression)

Example: Create Table Licence (LId Number(5), age Number(5), CHECK (age >= 18)) ;

(13) SQL LIKE Operator: LIKE operator is used in a where clause.

% (percentage) sign represent zero, one or multiple characters

_(underscore) sign represents one, single characters

 LIKE Operator Description

WHERE CustomerName LIKE

'a%' Finds any values that start with "a"

WHERE CustomerName LIKE

'%a' Finds any values that end with "a"

WHERE CustomerName LIKE

'%or%' Finds any values that have "or" in any position

WHERE CustomerName LIKE

'_r%' Finds any values that have "r" in the second position

WHERE CustomerName LIKE

'a_%' Finds any values that start with "a" and are at least 2
characters in length

WHERE CustomerName LIKE

'a__%' Finds any values that start with "a" and are at least 3
characters in length

WHERE ContactName LIKE

'a%o' Finds any values that start with "a" and ends with "o"

The following SQL statement selects all students with a Name starting with "a":

Example: SELECT * FROM Students WHERE Name LIKE 'a%';

(14) SQL IN Operator :IN operator allows to specify multiple values using WHERE clause.

The following SQL statement selects all students that are located in "Germany", "France" or "UK":
 Example: SELECT * FROM Students
WHERE Country IN ('Germany', 'France', 'UK');

(15) SQL BETWEEN Operator: Whenever we want to select value within range we can use
BETWEENoperator.

Syntax: SELECT column_name(s) FROM table_name

WHERE column_name BETWEEN value1 AND value2;

(16) JOIN
Here are the different types of the JOINs in SQL:

(INNER) JOIN: Returns records that have matching values in both tables

LEFT (OUTER) JOIN: Returns all records from the left table, and the matched records from the
right table

RIGHT (OUTER) JOIN: Returns all records from the right table, and the matched records from
the left table

FULL (OUTER) JOIN: Returns all records when there is a match in either left or right table
 INNER JOIN SELECT table1.column1,table1.column2,table2.column1,....
FROM table1
INNER JOIN table2
ON table1.matching_column = table2.matching_column;

LEFT JOIN SELECT table1.column1,table1.column2,table2.column1,....

FROM table1
LEFT JOIN table2
ON table1.matching_column = table2.matching_column;

RIGHT JOIN SELECT table1.column1,table1.column2,table2.column1,....

FROM table1
RIGHT JOIN table2
ON table1.matching_column = table2.matching_column;

FULL JOIN SELECT table1.column1,table1.column2,table2.column1,....

FROM table1
FULL JOIN table2
ON table1.matching_column = table2.matching_column;

Example:
Student(StudentID, CouseID)
Courses(CouseID, CourseName)

SELECT Student.StudentID, Courses.CourseName

Example: FROM Student
INNER JOIN Courses ON Student.CourseID=Courses.CourseID;
(17) Agrregate Functions

In database management an aggregate function is a function where the values of multiple

rows are grouped together as input on certain criteria to form a single value of more
significant meaning.

AVG() Syntax
SELECT AVG(column_name)
FROM table_name
WHERE condition;

COUNT() Syntax
SELECT COUNT(column_name)
FROM table_name
WHERE condition;

SUM() Syntax
SELECT SUM(column_name)
FROM table_name
WHERE condition;

MIN() Syntax
SELECT MIN(column_name)
FROM table_name
WHERE condition;
 MAX() Syntax
SELECT MAX(column_name)
FROM table_name
WHERE condition;

 GROUP BY: The GROUP BY statement groups rows that have the same values into summary rows,
like "find the number of students in each city".
The GROUP BY statement is often used with aggregate functions
(COUNT(), MAX(), MIN(), SUM(), AVG()) to group the result-set by one or more columns.

SELECT column_name(s) FROM table_name WHERE condition

Syntax: GROUP BY column_name(s)

SELECT COUNT(StudentID), Country FROM student

Example: GROUP BY Country;

 HAVING :The HAVING statement groups rows that have the same values into summary rows with
consition like "find the number of students in each city where count>5".

SELECT COUNT(StudentID), Country FROM student

Example: GROUP BY Country HAVING COUNT(StudentID) > 5;

 SUBQUERIES: A subquery is a query within a query. This means a subquery is a SELECT statement
that is nested within another SELECT statement and which returns intermediate results. It is also
termed as nested query. The statement containing a subquery is called a parent statement. The
parent statement uses the rows (i.e. result set) returned by the subquery.

Subqueries can be used for the following:

 1) To insert into record in a target table.
2) To create tables and insert records in the table created.
3) To update records in a target table.
4) To create views.

Using EXISTS: The EXISTS operator is used to test for the existence of any record in a
subquery.

SELECT column_name(s) FROM table_name

Syntax: WHERE EXISTS
(SELECT column_name FROM table_name WHERE condition);
SELECT SupplierNameFROM Suppliers
Example: WHERE EXISTS (SELECT ProductName FROM Products
WHERE Products.SupplierID = Suppliers.supplierID AND Price < 20);

Using ANY/ALL: The ANY and ALL operators allow you to perform a comparison between a
single column value and a range of other values.

Example: SELECT ProductName FROM Products

WHERE ProductID= ANY(SELECT ProductIDFROM OrderDetails
WHERE Quantity = 10);
SELECT ProductName FROM Products
Example: WHERE ProductID = ALL(SELECT ProductID FROM OrderDetails
WHERE Quantity = 10);

Example 1: List out employee name whose dname is abc and xyz using subquery.
Answer:select ename from emp where deptnoin(select deptno from dept where dname = 'abc' or
dname = 'xyz');
Explanation: First Subquery is executed and values are replaced in query.
DEPTNO
------
10
20
Then the parent query is executed like.
selectename from emp where deptno in(10,20);
 Example: List out employee name whose dname is not abc and xyz using subquery.
Answer:select ename from emp where deptno not in(select deptno from dept where dname = 'abc'
or dname = 'xyz');
Explanation: First Subquery is executed and values are replaced in query.
DEPTNO
------
10
20
Then the parent query is executed like.
selectename from emp where deptno not in(10,20);

 INDEXES

Duplicate / Unique Index:

Oracle allows the creation of two types of indexes. There are:

 Indexes that allow duplicate values for the indexed columns i.e. Duplicate Index.
 Indexes that deny duplicate values for the indexed columns i.e. Unique Index.

Creation of an Index:
An index can be created on one or more columns. Based on the number of columns included in the index,
an index can be:
 Simple Index
 Composite Index

Creation of Simple Index:

An index is created on a single column of a table is called a Simple Index. The syntax for creating simple index
that allows duplicate values is as described:

Syntax:CREATE INDEX <IndexName>ON<TableName>(<ColumnName>);

Example: CREATE INDEX idx_c_no ON client_master(c_no);

Creation of Composite Index:

An index is created on more than one column of a table is called a Composite Index. The syntax for creating
composite index that allows duplicate values is as described:

Syntax:CREATE INDEX<IndexName>ON<TableName>(<ColumnName 1>,<ColumnName 2>);

Example: CREATE INDEXidx_c_noONclient_master(c_no,act_no);

Creation of Unique Index:

A unique index can also be created on one or more columns. If an index is created on a single column, it is
called Simple Unique Index. The syntax for creating simple unique index is as follows:

Syntax:CREATE UNIQUE INDEX<IndexName>ON<TableName>(<ColumnName>);

Example: CREATE UNIQUE INDEX idx_c_no ON client_master(c_no,act_no);

If an index is created on more than one column, it is called Composite Unique Index. The syntax for creating
composite unique index is as follows:

Syntax:CREATE UNIQUE INDEX<IndexName>ON<TableName>(<ColumnName 1>,<ColumnName 2>);

Example: CREATE UNIQUE INDEXidx_c_noONclient_master(c_no,act_no);
Dropping Index:

Indexes associated with the tables can be removed by using the DROP INDEX command.

Syntax:DROP INDEX<IndexName>;
Example: DROP INDEX idx_c_no;

When a table, which has associated indexes, is dropped, the oracle engine automatically drops all the
associated indexes as well.

 VIEW

In SQL, a view is a virtual table based on the result-set of an SQL statement.

A view contains rows and columns, just like a real table. The fields in a view are fields from one or more real
tables in the database.

CREATE VIEW view_name AS SELECT column1, column2,

Syntax: FROM table_name WHERE condition;

CREATE VIEW [Indian students] AS SELECT StudentName, Marks

Example: FROM Student WHERE Country = 'India';

SELECT * FROM [Indian students];

DROP VIEW view_name; (This will Drop VIEW)

Comparison Operators:
Comparison operators compare one expression with another. The result of such a comparison can be
TRUE, FALSE, or UNKNOWN. Table given below lists comparison operators.
Operator Purpose Example

= Equality test. SELECT * FROM emp

WHERE sal = 1500;

!= Inequality test. Some forms of the SELECT * FROM emp

^= inequality operator may be WHERE sal<> 1500;
<> unavailable on some platforms
¬=
> "Greater than" and "less than" a. SELECT * FROM emp
tests. WHERE sal> 1500;
< b. SELECT * FROM emp
WHERE sal< 1500;
>= "Greater than or equal to" and "less a .SELECT * FROM emp
than or equal to" tests. WHERE sal>= 1500;
<= b. SELECT * FROM emp
WHERE sal<= 1500;

IN "Equal to any member of" test. a. SELECT * FROM emp

WHERE job IN
Equivalent to "= ANY". ('CLERK','ANALYST');

b. SELECT * FROM emp

WHERE sal IN
(SELECT sal FROM emp
WHERE deptno =30);
NOT IN Equivalent to "!=ALL". a. SELECT * FROM emp
WHERE sal NOT IN
Evaluates to FALSE if any member of (SELECT sal FROM emp
the set is NULL. WHERE deptno = 30);
b. SELECT * FROM emp
WHERE job NOT IN
('CLERK',ANALYST');
ANY returns a boolean value as a result SELECT ProductName
SOME FROM Products
returns TRUE if ANY of the subquery WHERE ProductID = ANY
values meet the condition (SELECT ProductID
FROM OrderDetails
WHERE Quantity = 10);
 ALL returns a boolean value as a result SELECT ProductName
FROM Products
returns TRUE if ALL of the subquery
WHERE ProductID = ALL
values meet the condition
(SELECT ProductID
is used FROM OrderDetails
with SELECT, WHERE and HAVING sta WHERE Quantity = 10);
tements

Logical Operators:

A logical operator combines the results of two component conditions to produce a single result based on
them or to invert the result of a single condition. Table given below lists logical operators.

Operator Function Example

NOT Returns TRUE if the following condition a. SELECT * FROM emp

is FALSE. Returns FALSE if it is TRUE. WHERE NOT (job IS NULL);
If it is UNKNOWN, it remains b. SELECT * FROM emp
UNKNOWN. WHERE NOT (sal BETWEEN
1000 AND 2000);
AND Returns TRUE if both component
conditions are TRUE. Returns FALSE if SELECT * FROM emp
either is FALSE. Otherwise returns WHERE job = 'CLERK'
UNKNOWN. AND deptno = 10;
OR Returns TRUE if either component
condition is TRUE. Returns FALSE if SELECT * FROM emp
both are FALSE. Otherwise returns WHERE job = 'CLERK'
UNKNOWN. OR deptno = 10;

 SQL FUNCTIONS:
The Oracle Table DUAL :

The DUAL is a table own by system, The structure of DUAL table is viewed as follows.

DESC DUAL;

CHARACTER FUNCTIONS:

Syntax: Select <Function> FROM DUAL;

Example: Select INITCAP ('hello SIR') FROM DUAL;

Function Description Example

Returns the string s with the first INITCAP('hello SIR') =

character of each word in 'Hello Sir'
INITCAP(s)
uppercase and all others in Select INITCAP ('hello
lowercase. SIR') FROM DUAL;

Returns the number of characters LENGTH('Welcome Sir')

LENGTH(s)
in the string s. = 11

Returns the string s with all LOWER('Welcome Sir') =

LOWER(s)
characters in lowercase. 'welcome sir'

Returns s without any leading

character that appear in s1. If no
s1 character are leading characters LTRIM('welcome',
LTRIM(s, s1)
in s, then s is returned unchanged. 'slow') = 'elcome'

Returns s without any trailing

character that appear in s1. If no RTRIM('Mississippi',
RTRIM(s, s1)
s1 character are trailing characters 'pi') = 'Mississ'
in s, then s is returned unchanged.

REPLACE('www.yahoo.com
REPLACE(s, Returns s with all occurrences of ', 'yahoo', 'google')
s1[, s2]) substring s1 replaced with s2. By = 'www.google.com'
 default s2 is NULL and all
occurrences of s1 are removed.

Returns the portion of the string s SUBSTR('welcome', 4) =

'come'
that is len characters long,
beginning at position pos. If pos is
negative, the position is counted
SUBSTR(s, SUBSTR('welcome',2,2)
backwards from the end of the
pos[, len]) = 'el'
string. The function returns NULL if
len is less or equal to zero. If len is
skipped, it returns the remaining SUBSTR('welcome',-3,2)
of s. = 'om'

Returns the string s with all

occurrences of characters in s1
replaced with the positionally TRANSLATE('alfabet',
TRANSLATE(s,
corresponding characters is s2.If 'abscde', 'BCDE') =
s1, s2)
s2 is shorter than s1, the 'BlfBCt'
unmatched characters in s1 are
removed.

Returns the string s with all UPPER('Welcome Sir') =

UPPER(s)
characters in uppercase. 'WELCOME SIR'

Returns the number of bytes in the VSIZE('SCT on the

VSIZE(s)
internal representation of an s. net') = 14

Returns s, left-padded to length n

LPAD(s,n[,s1] with the sequence of characters LPAD('Page 1',10,’*’) =
) specified in s1. if s1 is not specified ****Page 1
Oracle uses blanks by default.

Returns s, right-padded to length n

RPAD(s,n[,s1] with the sequence of characters RPAD('Page 1',10,’*’) =
) specified in s1. if s1 is not specified Page 1****
Oracle uses blanks by default.
NUMERIC FUNCTIONS:

Syntax: Select <Function> FROM DUAL;

Example: Select ABS(-25.52) “Absolute” from DUAL;

Function Description Example

ABS(-25.52) = 25.52
ABS(d) Returns the absolute value of the double d. Select ABS(-25.52)
“Absolute” from DUAL;

Returns the smallest integer that is greater or CEIL(24.8) = 25

CEIL(d)
equal to d. CEIL(-24.8)= -24

EXP(d) Returns ed EXP(5) = 148.413159

FLOOR(24.8) = 24,
FLOOR(d) Returns the largest integer less or equal to d.
FLOOR(-24.8)= -25

LN(d) Returns the natural logarithm of d. LN(10) = 2.30258509

MOD(i1, Returns i1 modulo i2, or the reminder of i1

MOD(15,7) = 1
i2) divided i2.

POWER(d,
Returns d to the pth power (dp). POWER(2,5)= 32
p)

ROUND(2.6586,2) =
Returns d rounded to i digits of precision to the 2.66
ROUND(d,
right of the decimal point. If i is negative, d is
i) ROUND(289.6586,-2)=
rounded to the left of the decimal point.
300

SQRT(d) Returns the square root of d. SQRT(81) = 9

TRUNC(256.8888,2) =
Returns d truncated to i digits of precision to 256.88
TRUNC(d,
the right of the decimal point. If i is negative, d
i) TRUNC(256.8888,-2) =
is truncated to the left of the decimal point.
200
 DATE FUNCTIONS:

Implicit Date-to-String conversion is based on the value of the NLS_DATE_FORMAT variable. To change
its value use the ALTER SESSION command:
ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MM-YYYY HH24:MI:SS';

Function Description Example

Returns the date dt plus i months. If

i is a decimal number. Oracle will
ADD_MONTHS(dt, ADD_MONTHS(SYSDATE, 2) =
automatically convert it to an
i) 13-10-2008 21:23:00
integer by truncating the decimal
portion (it may also be negative).

Returns the current date in the CURRENT_DATE =

CURRENT_DATE Gregorian calendar for the session's
time zone. It requires no arguments. 13-08-2008 21:23:50

MONTHS_BETWEEN('01-JAN-
Returns the number of months that
2009',SYSDATE) = 4
dt1 is later than dt2. A whole
MONTHS_BETWEEN
number is returned if dt1 and dt2 are
(dt1, dt2)
the same day of the month or if both
MONTHS_BETWEEN('01-JAN-
are th last day of the month.
2007',SYSDATE) = -19

Returns the date in time zone tz2 for

NEW_TIME(dt, NEW_TIME(SYSDATE, 'EST',
the date dt in time zone tz1. See time
tz1, tz2) 'PDT') = 13-08-2008 19:25:44
zone constants

Returns the date that corresponds

to the next day of week specified by
the string s following the date dt. NEXT_DAY(SYSDATE, 'Mon') =
NEXT_DAY(dt, s)
The time portion of the date is the 18-08-2008 21:26:32
same as the time portion of dt. The
string s should be a text string
 containing the full or abbreviated
day of the week in the session's
language.

Returns the date dt rounded to the

ROUND(SYSDATE,
ROUND(dt[, granularity specified in the date-
'HH24') = 13-08-2008
fmt]) format string fmt. See date format
21:00:00
codes.

Returns the current date/time, takes SYSDATE = 13-08-2008

SYSDATE
no arguments. 21:28:13

Returns the date dt truncated to the

TRUNC(dt[, TRUNC(SYSdate, 'DD') = 13-
granularity specified by the format
fmt]) 08-2008 00:00:00
string fmt.

CONVERSION FUNCTIONS

Function Description

Takes up to three arguments, where x is either a date or a

number, fmt is a format string specifying the format that x will
TO_CHAR(x[, fmt[, appear in, and nls specifies language or location formatting
nls]]) string.
If x is a date, fmt is a date format code.
If x is a number, fmt is a numeric format code.

Converts string s to DATE datatype accordingly the format

TO_DATE(s[, fmt[, string fmt. The fmt string uses the same date format code. The
nls]]) default is the value stored in the NLS_DATE_FORMAT session
variable.

TO_NUMBER(s[, fmt[, Returns the numeric value represented by the string s. The
nls]]) format string fmt specifies the format the that s appears in.

(1) TO_CHAR FUNCTION: In Oracle/PLSQL, the TO_CHAR function converts a number or date to a string.

Examples – Numbers:

The following are number examples for the TO_CHAR function.

 1. to_char(1210.73,
would return '1210.7'
'9999.9')

2. to_char(1210.73,
would return '1,210.73'
'9,999.99')

3. to_char(1210.73,
would return '$1,210.73'
'$9,999.00')

4. to_char(21, '000099') would return '000021'

Examples – Dates:

The following are date examples for THE TO_CHAR function.

1. to_char(sysdate, 'yyyy/mm/dd') would return '2008/07/09'

2. to_char(sysdate, 'Month DD, YYYY') would return 'July 09, 2008'

3. to_char(sysdate, 'FMMonth DD,

would return 'July 9, 2008'
YYYY')

4. to_char(sysdate, 'MON DDth, YYYY') would return 'JUL 09TH, 2008'

5. to_char(sysdate, 'FMMON DDth,

would return 'JUL 9TH, 2008'
YYYY')

6.
to_char(sysdate, 'FMMonddth, YYYY') would return 'Jul 9th, 2008'

You will notice that in some examples, the format_mask parameter begins with "FM". This
means that zeros and blanks are suppressed. This can be seen in the examples below.

1. to_char(sysdate, 'FMMonth DD, YYYY') would return 'July 9, 2008'

2. to_char(sysdate, 'FMMON DDth, YYYY') would return 'JUL 9TH, 2008'

3. to_char(sysdate, 'FMMonddth, YYYY') would return 'Jul 9th, 2008'

The zeros have been suppressed so that the day component shows as "9" as opposed to "09".
(2) TO_DATE FUNCTION: In Oracle/PLSQL, the TO_DATE function converts a string to a date.

The following is a list of options for the format_mask parameter. These parameters can be used
in many combinations.

For example:

1. to_date('2008/07/09',
would return a date value 09-07-2008 00:00:00
'yyyy/mm/dd')

2. to_date('070908', 'MMDDYY') would return a date value 09-07-2008 00:00:00

3. to_date('2008Mar15',
would return a date value 15-03-2008 00:00:00
'yyyymmdd')

(3) TO_NUMBER FUNCTION: In Oracle/PLSQL, the TO_NUMBER function converts a string to a number.

For example:

1. to_number('1210.73',
would return the number 1210.73
'9999.99')

2. to_number('546', '999') would return the number 546

3. to_number('23', '99') would return the number 23

Since the format_mask and nls_language parameters are optional, you can simply convert a text
string to a numeric value as follows:
 to_number('1210.73') would return the number 1210.73

 Granting And Revoking Permissions:

 Objects that are created by a user are owned and controlled by that user.
 If a user wishes to access any of the objects belonging to another user, the owner of the object
will have to give the permission for each access. This is called Granting of Privileges.
 Privileges once given can be taken back by the owner of the object. This is called Revoking of
Privileges.
Granting Privileges Using the GRANT statement:The Grant statement provides various types of
access to database objects such as tables, views and sequences and so on.

Syntax: CREATE USER Username IDENTIFIED BY PASSWORD;

/ CREATE USER C##Username IDENTIFIED BY PASSWORD;

GRANT CREATE SESSION TO USERNAME;

Syntax: GRANT<Object Privileges>

ON<Object Name>

TO<User Name>

[WITH GRANT OPTION];

OBJECT PRIVILEGES:

Each object privileges that are granted authorize the grantee to perform some operation on the object.
The user can grant the privileges or grant only specific object privileges.

The lists of object privileges are as follows:

 ALTER Allows the grantee to change the table definition with the ALTER TABLE
command.

DELETE Allows the grantee to remove the record from a table with the DELETE
Command.

INDEX Allows the grantee to create an index on the table with the INDEX Command.

INSERT Allows the grantee to add records to the table with the INSERT Command.

SELECT Allows the grantee to query the table with the SELECT Command.

UPDATE Allows the grantee to modify the records in the table with the UPDATE
Command.

WITH GRANT OPTION:

The WITH GRANT OPTION allows the grantee to in turn grant object privileges to other users.

Examples of the GRANT Statement:

Examples: a. Give the user Bandish all data manipulation permission on the table
product_master.
GRANT ALL ON product_masterTO Bandish;

b. Give the user Sagar only the permission to view and modify records in the
table client_master.
GRANT select, update ON client_masterTO Sagar;

c. Give the user Alap all data manipulation privileges on the table ACCT_MSTR
along with an option to further grant permission on the ACCT_MSTR table to
other users.
GRANT ALL ON ACCT_MSTR TOAlapWITH GRANT OPTION;

d. Give the user Jashvant to view records from the TRANS_MSTR table. The
table originally belongs to the user Ankit, who has granted you the privilege
to pass on the privileges that you have to others using GRANT privilege
option.
GRANT SELECT ON Ankit.TRANS_MSTR TO Jashvant;
Referencing a Table Belonging To Another User: Once a user has privileges to access another user’s
object(s), the user can access the table by prefixing the table with the name of the owner.

Example: View the contents of the FD_MSTR table that belongs to Alap.

SELECT * FROM Alap.FD_MSTR;

Revoking Privileges Given:

Privileges once given can be denied to a user using the REVOKE command.

Syntax: REVOKE<Object Privileges>

ON<Object Name>

FROM<User Name>;

Examples: b. All privileges on the table NOMIEE_MSTR have been granted to Rahul. Take
back the Delete privilege on the table.
REVOKE DELETE ON NOMIEE_MSTR FROM Rahul;

c. Take back all privileges on the table NOMIEE_MSTR from Anil.

REVOKE ALL ON NOMIEE_MSTR FROM Anil;

d. Darshan has the permission to view records from FD_MSTR. Take back this
permission. Note that Hardik the original owner of FD_MSTR table.
REVOKE SELECT ON Hardik.FD_MSTR FROM Darshan;