SQL study material

1. zxINDEX

• Oracle

• Overview Of Oracle

• Basic Oracle Objects

• Data Base

• DBMS

• DBMS JOB

• Types Of Languages In The Industries

• DBMS MODELS

• RDBMS

• Properties Of RDBMS

• What Is SQL

• Tablespace

• SQL Process

• History Of SQL

• E.F Codd Rules And Their Descriptions

• Versions Of Oracle

• How SQL Works

• Features Of SQL

• Data Types

• Dual Table

• Keywords In A SQL Statement

• Sublanguages

• Clauses Of SQL   

• Functions In SQL

• Operators In SQL

• Aliases

• Joins

• Constraints

• Nested/Sub Queries

• Correlated Sub Queries

• Set Operators

• Synonym

• Views

• Copy A Table From One Table To Another Table

• Sequences

• Indexes

• Pseudo Columns

• Matrix Query

• Clusters

• Normalization

• OLAP FEATURES IN ORACLE

2. 
   

ORACLE:

Oak Ridge Automatic Computer and Logical Engine

Oracle is an Object-Relational Database Management System. It is the leading RDBMS vendor worldwide. Nearly half of RDBMS worldwide market is owned by Oracle.

OVERVIEW OF ORACLE:

The Oracle Relational Database Management System, or RDBMS, is designed to allow simultaneous access to large amounts of stored information. The RDBMS consists of the database (the information) and the instance (the embodiment of the system). The database contains the physical files that reside on the system and the logical pieces such as the database schema. These database files take various forms, as described in the following section. The instance is the method used to access the data and consists of processes and system memory.

BASIC ORACLE OBJECTS:

The database schema is a collection of logical-structure objects, known as schema objects, that define how you see the database's data. These schema objects consist of structures such as tables, clusters, indexes, views, stored procedures, database triggers, and sequences. •Table--A table, which consists of a table name and rows and columns of data, is the basic logical storage unit in the Oracle database. Columns are defined by name and data type.

11. DATABASE:

A database is a collection of Data (Information).Examples of databases, which we use in our daily life, is an Attendance Register, Telephone Directory and Muster Rule.  The term "database" is used to describe both the collection of data and the software tool used to manage the data (usually called Database Management System).

Database Management System (DBMS): 

A database management system is a collection of programs written to manage a database. That is, it acts as an interface between user and database.

DBMS JOB:

Store Manipulate Retrieving Sharing/ Distribution information Security

12. 
    

13. TYPES OF LANGUAGES IN THE INDUSTRIES:

Machine Level Language: Combination of 0 & 1. Low Level Language Not Front End          It is interacted with hardware directly. High Level Language English type language supported systematic development language. E.g.: C, C++, COBOL, Java, SQL, PL/SQL (98% information available) 4th Generation Language Developing the solution easy and fast. E.g.: SQL (100% information available) 5th Generation Language Platform independence. E.g.: Java (100%), .Net

15. 
    

16. DBMS MODELS:

1. HDBMS

2. NDBMS

3. RDBMS

HDBMS :( 1960)

HDBMS (Hierarchical Database Management System) is a type of DBMS that supports a hierarchical data model. 

Example HDBMS Systems: 

• Lotus Notes (the most popular HDBMS in use today) 

• IMS - IBM's Information Management System (first HDBMS) 

• System 2000 

Disadvantages: Data Redundancy (Duplication of Data).

NDBMS :( NDBMS NETWORK DATABASE MANAGEMENT SYSTEM ...Mid Of 1960s)

• Represents using a Physical Link.

• No Data Redundancy like HDBMS.

• Retrieving and Manipulating is very fast comparing to HDBMS.

Example NDBMS Systems: 

• DBASE, FOXPRO.

DISADVANTAGE:

• Supports Limited Data.

• Poor Security.

17. RDBMS:

A Database Management System based on Relational Data Model is known as Relational Database Management System (RDBMS). The father of Relational Data Model was Dr. E.F. CODD. He developed the relational data model by taking the concept from Relational Algebra in June-1970.  Relational Data Model is nothing but 12 Rules which are named after Codd as Codd Rules. According to Codd a package can be called as RDBMS only if it satisfies the Codd Rules.

 Properties of the RDBMS: 

Data can be stored in the form of the table. A table can contain collection of rows and columns. The horizontal things are called rows and vertical things are called as columns. The intersection of rows/columns called table. A cell place where we can store data. The other name of row also called as record/tuple. The other name of column can be called as field and other name is entity. Tables should not contain duplicate columns. While inserting records in the table Programmer no need to follow any order. Database should not contain duplicate objects. When we defined the columns in the table programmer need to follow order.

What is SQL?

SQL (pronounced "ess-que-el") stands for Structured Query Language. SQL is used to communicate with a database. According to ANSI (American National Standards Institute), it is the standard language for relational database management systems. SQL statements are used to perform tasks such as update data on a database, or retrieve data from a database. Some common relational database management systems that use SQL are: Oracle, Sybase, Microsoft SQL Server, Access, Ingres, etc. Although most database systems use SQL, most of them also have their own additional proprietary extensions that are usually only used on their system. However, the standard SQL commands such as "Select", "Insert", "Update", "Delete", "Create", and "Drop" can be used to accomplish almost everything that one needs to do with a database. This tutorial will provide you with the instruction on the basics of each of these commands as well as allow you to put them to practice using the SQL Interpreter.

Tablespace:

A tablespace is a container for segments (tables, indexes, etc). A database consists of one or more tablespaces, each made up of one or more data files. Tables and indexes are created within a particular tablespace.

Oracle has a limit of 64,000 data files per database. 

SQL Process:

When you are executing an SQL command for any RDBMS, the system determines the best way to carry out your request and SQL engine figures out how to interpret the task. There are various components included in the process. These components are Query Dispatcher, Optimization engines, Classic Query Engine and SQL query engine etc. Classic query engine handles all non-SQL queries but SQL query engine won't handle logical files.

Following is a simple diagram showing SQL Architecture:

18.  History of SQL:

SQL was initially developed at IBM by Donald D. Chamberlin and Raymond F. Boyce in the early 1970s.  This version, initially called SEQUEL (Structured English Query Language), was designed to manipulate and retrieve data stored in IBM's original quasi-relational database management system, System R, which a group at IBM San Jose Research Laboratory had developed during the 1970s.  The acronym SEQUEL was later changed to SQL because "SEQUEL" was a trademark of the UK-based Hawker Siddeley aircraft company.  In the late 1970s, Relational Software, Inc. (now Oracle Corporation) saw the potential of the concepts described by Codd, Chamberlin, and Boyce and developed their own SQL-based RDBMS with aspirations of selling it to the U.S. Navy, Central Intelligence Agency, and other U.S. government agencies.  In June 1979, Relational Software, Inc. introduced the first commercially available implementation of SQL, Oracle V2 (Version2) for VAX computers. After testing SQL at customer test sites to determine the usefulness and practicality of the system, IBM began developing commercial products based on their System R prototype including System/38, SQL/DS, and DB2, which were commercially available in 1979, 1981, and 1983, respectively.  1970 -- Dr. E.F. "Ted" of IBM is known as the father of relational databases. He described a relational model for databases. 1974 -- Structured Query Language appeared. 1978 -- IBM worked to develop Codd's ideas and released a product named System/R. 1986 -- IBM developed the first prototype of relational database and standardized by ANSI. The first relational database was released by Relational Software and its later becoming Oracle. E.F. Codd, the famous mathematician has introduced 12 rules for the relational model for databases commonly known as Codd's rules. The rules mainly define what is required for a DBMS for it to be considered relational, i.e., an RDBMS. There is also one more rule i.e. Rule00 which specifies the relational model should use the relational way to manage the database.

SQL SERVER:

Generically, any database management system (DBMS) that can respond to queries from client machines formatted in the SQL language. When capitalized, the term generally refers to either of two database management products from Sybase and Microsoft. Both companies offer client-server DBMS products called SQL Server. 

E.F Codd rules and their description is as follows:

There are 13 (0 to 12) rules which were presented by Dr. E.F.Codd, in June 1970,in ACM (Association of Computer Machinery)  Rule 0. Relational Database management “A relational database management system must use only its relational capabilities to manage the information stored in the database”.  Rule 1. The information rule All information in the database to be represented in one and only one way, namely by values in column positions within rows of tables.  Rule 2. Logical accessibility This rule says about the requirement of primary keys. Every individual value in the database must be logically addressable by specifying the name of table, column and the primary key value of the row . Rule 3. Representation of null values The DBMS is required to support a representation of "missing information and inapplicable information" (for example, 0 'Zero' is different from other Numbers), this type of information must be represented by the DBMS in a systematic way (For example Null Character).  Rule 4. Catalog Facilities The system is required to support an on line, in line, relational data access to authorized users by using their Query language.  Rule 5. Data Languages. The system must support a least one relational language (It may support more than one relational language) that (a) has a linear syntax,  (b) Can be used in two ways and within application programs,  (c) Supports data operations security and integrity constraints, and transaction management operations (commit).  Rule 6. View Updatability All views that are theoretically updatable must be updatable by the system.  Rule 7. Update and delete. The system must support INSERT, UPDATE, and DELETE operators.  Rule 8. Physical data independence Changes to the physical level (how the data is stored, whether in arrays or linked lists etc.) must not require a change to an application based on the structure.  Rule 9. Logical data independence Changes made to tables to modify any data stored in the tables must not require changes to be made to application programs. Logical data independence is more difficult to achieve than physical data independence.  Rule 10. Integrity Constraints Integrity constraints must be specified separately from application programs and stored in the catalog. It must be possible to change such constraints when they are unnecessarily affecting existing applications.  Rule 11. Database Distribution The RDBMS may spread across more than one system and across several networks, however the tables should appear in same manner to every user like local users.  Rule 12. The Non Subversion rule If the system provides a low-level interface, then that interface cannot be used to weaken the system (e.g.) bypassing a relational security or integrity constraint.

Briefly E.F. Codd's Rules:
Every DBMS package has to support min 6 / 12        rules to be declared as RDBMS.

1. Information Representation
2. Guaranteed Access
3. Systematic treatment of Null values
4. View Updation (80 % )
5. Comprehensive Data sub language
6. High level Insert, Update, Delete
7. Data description rule
8. Data distribution rule
9. Physical Data Independence
10. Logical Data Independence
11. Data Integrity rules (Constraints )
12. Non sub version rule

 ORACLE VERSIONS:

 

select * from v$version where banner like 'Oracle%'; Ans: Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 – Production.

Oracle 7: ... - 7.3.4.5  Oracle 8: 8.0.3 - 8.0.6  Oracle 8i: 8.1.5.0 - 8.1.7.4  Oracle 9i (Release 1): 9.0.1.0 - 9.0.1.4  Oracle 9i (Release 2): 9.2.0.1 - 9.2.0.8  Oracle 10g (Release 1): 10.1.0.2 - 10.1.0.5  Oracle 10g (Release 2): 10.2.0.1 - 10.2.0.5  Oracle 11g (Release 1): 11.1.0.6 - 11.1.0.7 11g was introduced in 2007 Oracle 11g (Release 2): 11.2.0.1 - 11.2.0.2

Oracle Database, the relational database management system from Oracle Corporation, is arguably the most powerful and feature rich database on the market.

The "i" and "g" Versions:
Starting in 1999 with Version 8i, Oracle added the "i" to the version name to reflect support for the Internet with its built-in Java Virtual Machine (JVM). Oracle 9i added more support for XML in 2001. In 2003, Oracle 10g was introduced with emphasis on the "g" for grid computing, which enables clusters of low-cost, industry standard servers to be treated as a single unit.

Larry Ellison founded Software Development Laboratories in 1977. 

1979 - Oracle release 2

The first commercial RDBMS was built using PDP-11 assembler language. Although they created a commercial version of RDBMS in 1977, it wasn't available for sale until 1979 with the launch of Oracle version 2. The company decided against starting with version 1 because they were afraid that the term "version 1" might be viewed negatively in the marketplace.  USA Air Force and then CIA were the first customers to use Oracle 2. 

In 1982 there was another change of the company’s name, from RSI to Oracle Systems Corporation so as to match its popular database name.  The current company name comes from a CIA project that Larry Ellison had previously worked on code named “Oracle”.

1983 - Oracle release 3

The Oracle version 3 was developed in 1983. This version was assembled using C programming language and could run in mainframes, minicomputers, and PCs – or any hardware with a C compiler. It supported the execution of SQL statements and transactions. This version also included new options of pre-join data to increase Oracle optimization. 

1984 - Oracle release 4

Despite the advances introduced in version 3, demand was so great that Oracle was compelled to improve the software even further with the release of version 4 in 1984. Oracle version 4 included support for reading consistency, which made it much faster than any previous version.  Oracle version 4 also brought us the introduction of the export/import utilities and the report writer, which allows one the ability to create a report based on a query.

1985 - Oracle release 5

With the introduction of version 5 in 1985, Oracle addressed the increasing use of the internet in business computing.  This version was equipped with the capability to connect clients’ software through a network to a database server.  The Clustering Technology was introduced in this version as well and Oracle became the pioneer using this new concept – which would later be known as Oracle Real Application Cluster in version 9i.  Oracle version 5 added some new security features such as auditing, which would help determine who and when someone accessed the database.

Oracle version 5.1 was launched in 1986 and allowed for supporting distributed queries. Later that same year Oracle released SQL*Plus, a tool that offers ad hoc data access and report writing.  1986 also brought the release of SQL*Forms, an application generator and runtime system with facilities for simple application deployment.

1988 - Oracle release 6

The PL/SQL language came with Oracle version 6 in 1988.  This version provided a host of new features including the support of OLTP high-speed systems, hot backup capability and row level locking – which locks only the row or rows being used during a writing operation, rather than locking an entire table.  Prior to the hot backup feature, database administrators were required to shutdown the database to back it up.  Once the hot backup feature was introduced, DBA’s could do a backup while the database was still online.

Oracle Parallel Server was introduced in Oracle version 6.2 and was used with DEC VAX Cluster. This new feature provided high availability because more than one node (server) could access the data in database. With the increased availability this feature also accelerated the performance of the system that was sharing users’ connections between nodes.

1992 - Oracle release 7

1992 was a memorable year for Oracle.  The company announced Oracle version 7, which was the culmination of four years of hard work and two years of customer testing before release to market.  This version of Oracle provided a vast array of new features  and capabilities in areas such as security, administration, development, and performance.  Oracle 7 also addressed security concerns by providing full control of who, when, and what users were doing in the database. Version 7 also allowed us to monitor every command, the use of privileges and the user’s access to a particular item. With Oracle 7 users could use stored procedures and had triggers to enforce business-rules. Roles were created at this version to make the security maintenance easier for users and privileges. The two-phase commit was added to support distributed transactions.

Oracle7 Release 7.1 introduced some good new capabilities for database administrators, such as parallel recovery and read-only tablespaces. For the application developments, Oracle inserted the dynamic SQL, user-defined SQL functions and multiple same-type triggers. The first 64-bit DBMS was introduced within this version as well as the VLM (Very Large Memory) option.  The feature Oracle Parallel Query could make some complex queries run 5 to 20 times faster.

In 1996 Oracle 7.3 was shipped, offering customers the ability to manage all kinds of data types; including video, color images, sounds and spatial data.  1996 also brought the release of Oracle's first biometric authentication for a commercially available database.  This technology could analyze human characteristics, both physical and behavioral, for purposes of authentication.

1997 - Oracle release 8

The Oracle 8 Database was launched in 1997 and was designed to work with Oracle's network computer (NC). This version supported Java, HTML and OLTP.  

1998 - Oracle release 8i

Just one year later Oracle released Oracle 8i which was the first database to support Web technologies such as Java and HTTP.  In 2000 Oracle 8i Parallel Server was working with Linux which eliminated costly downtime.

2001 - Oracle release 9i

Oracle Real Application Cluster came with Oracle 9i Database in 2001. This feature provides software for clustering and high availability in Oracle database environments.  Supporting native XML was also a new feature of Oracle 9i and this was the first relational database to have these characteristics. Version 9i release 2 enabled Oracle to integrate relational and multidimensional database. Despite the fact that hard disks were becoming  cheaper, data was increasing very quickly in databases and Oracle 9i came with a special technology named table compression that reduced  the size of tables by  3 to 10 times  and increased the performance  when accessing those tables.

2003 - Oracle release 10g

Although Oracle 9i had only been in the market for two years, Oracle launched version 10g in 2003.  The release of 10g brought us the introduction to Grid Computing technology.  Data centers could now share hardware resources, thus lowering the cost of computing infrastructure.  10g was also the first Oracle version to support 64-bit on Linux.  With Oracle Database 10g and Real Application Cluster it was now possible to move from very expensive SMP boxes and mainframes to an infrastructure that relies on low costs such as UNIX or Windows servers, which have high availability, scalability and performance.

Oracle has long strived to make their software products available through the internet; but this effort was only enhanced with the creation of the 10g Express Edition.  With the introduction of the 10g Express Edition in 2005, Oracle gave small business and startup corporations a viable option to integrate Oracle into the workplace at no cost.  

2007 - Oracle release 11g

The latest version of Oracle Database is 11g which was released on July 11th 2007. This version introduced more features than any other in Oracle history.  This version includes:

• Oracle Database Replay, a tool that captures SQL statements and lets you replay them all in another database to test the changes before you actually apply then on a production database;

• Transaction Management using Log Miner and Flashback Data Archive to get DML statements from redo log files;

• Virtual Column Partitioning;

• Case sensitive passwords;

• Online Patching;

• Parallel Backups on same file using RMAN and many others.

Oracle is known for growth and change, which is why it is important to continually study its history and previous lessons learned while embracing new features and functionality. Throughout its history Oracle has acquired Database and Software Applications companies in order to provide more complete solutions to its customers and increase the credibility of its products.  Today Oracle has more than 320,000 customers and is present in 145 countries making it one of the elite companies in its field. 

How SQL Works:

The strengths of SQL provide benefits for all types of users, including application programmers, database administrators, managers, and end users. Technically speaking, SQL is a data sub-language. The purpose of SQL is to provide an interface to a relational database such as Oracle Database, and all SQL statements are instructions to the database. In this SQL differs from general-purpose programming languages like C and BASIC.

Features of SQL are the following:

It processes sets of data as groups rather than as individual units. It provides automatic navigation to the data. It uses statements that are complex and powerful individually, and that therefore stand alone. Flow-control statements were not part of SQL originally, but they are found in the recently accepted optional part of SQL, ISO/IEC 9075-5: 1996. Flow-control statements are commonly known as "persistent stored modules" (PSM), and the PL/SQL extension to Oracle SQL is similar to PSM. SQL lets you work with data at the logical level. You need to be concerned with the implementation details only when you want to manipulate the data. For example, to retrieve a set of rows from a table, you define a condition used to filter the rows. All rows satisfying the condition are retrieved in a single step and can be passed as a unit to the user, to another SQL statement, or to an application. You need not deal with the rows one by one, nor do you have to worry about how they are physically stored or retrieved. All SQL statements use the optimizer, a part of Oracle Database that determines the most efficient means of accessing the specified data. Oracle also provides techniques that you can use to make the optimizer perform its job better. SQL provides statements for a variety of tasks, including: Querying data Inserting, updating, and deleting rows in a table Creating, replacing, altering, and dropping objects Controlling access to the database and its objects Guaranteeing database consistency and integrity SQL unifies all of the preceding tasks in one consistent language.

CLIENT SERVER:

Client: Supports to develop user friendly application screens for data entry. It will not store data. It allows manipulating the data.

E.g.: Developer 6i, Java, .Net

Server: Support to store the information with high security. It allows manipulating, retrieving and sharing the information.

E.g.: All RDBMS.

Communication Channel: It is a software tool supports to distribute the information between server and client.

E.g.: SQL * Net, ODBC, JDBC.

DATA TYPES IN ORACLE:

The following is a list of data types available in Oracle.

19. 
    

20. Character Data types:

Data Type Syntax	Oracle 9i	Oracle 10g	Oracle 11g	Explanation (if applicable)
char(size)	Maximum size of 2000 bytes.	Maximum size of 2000 bytes.	Maximum size of 2000 bytes.	Where size is the number of characters to store. Fixed-length strings. Space padded.
nchar(size)	Maximum size of 2000 bytes.	Maximum size of 2000 bytes.	Maximum size of 2000 bytes.	Where size is the number of characters to store. Fixed-length NLS string Space padded.
nvarchar2(size)	Maximum size of 4000 bytes.	Maximum size of 4000 bytes.	Maximum size of 4000 bytes.	Where size is the number of characters to store. Variable-length NLS string.
varchar2(size)	Maximum size of 4000 bytes.	Maximum size of 4000 bytes.	Maximum size of 4000 bytes.	Where size is the number of characters to store. Variable-length string.
long	Maximum size of 2GB.	Maximum size of 2GB.	Maximum size of 2GB.	Variable-length strings. (backward compatible)
raw	Maximum size of 2000 bytes.	Maximum size of 2000 bytes.	Maximum size of 2000 bytes.	Variable-length binary strings
long raw	Maximum size of 2GB.	Maximum size of 2GB.	Maximum size of 2GB.	Variable-length binary strings. (backward compatible)

Numeric Data types:

Data Type Syntax	Oracle 9i	Oracle 10g	Oracle 11g	Explanation (if applicable)
number(p,s)	Precision can range from 1 to 38. Scale can range from -84 to 127.	Precision can range from 1 to 38. Scale can range from -84 to 127.	Precision can range from 1 to 38. Scale can range from -84 to 127.	Where p is the precision and s is the scale.  For example, number(7,2) is a number that has 5 digits before the decimal and 2 digits after the decimal.
numeric(p,s)	Precision can range from 1 to 38.	Precision can range from 1 to 38.	Precision can range from 1 to 38.	Where p is the precision and s is the scale.  For example, numeric(7,2) is a number that has 5 digits before the decimal and 2 digits after the decimal.
float
dec(p,s)	Precision can range from 1 to 38.	Precision can range from 1 to 38.	Precision can range from 1 to 38.	Where p is the precision and s is the scale.  For example, dec(3,1) is a number that has 2 digits before the decimal and 1 digit after the decimal.
decimal(p,s)	Precision can range from 1 to 38.	Precision can range from 1 to 38.	Precision can range from 1 to 38.	Where p is the precision and s is the scale.  For example, decimal(3,1) is a number that has 2 digits before the decimal and 1 digit after the decimal.
integer
int
smallint
real
double precision

21. Date/Time Data types:

Data Type Syntax	Oracle 9i	Oracle 10g	Oracle 11g	Explanation (if applicable)
date	A date between Jan 1, 4712 BC and Dec 31, 9999 AD.	A date between Jan 1, 4712 BC and Dec 31, 9999 AD.	A date between Jan 1, 4712 BC and Dec 31, 9999 AD.
timestamp (fractional seconds precision)	fractional seconds precision must be a number between 0 and 9. (default is 6)	fractional seconds precision must be a number between 0 and 9. (default is 6)	fractional seconds precision must be a number between 0 and 9. (default is 6)	Includes year, month, day, hour, minute, and seconds.  For example: timestamp(6)
timestamp (fractional seconds precision) with time zone	fractional seconds precision must be a number between 0 and 9. (default is 6)	fractional seconds precision must be a number between 0 and 9. (default is 6)	fractional seconds precision must be a number between 0 and 9. (default is 6)	Includes year, month, day, hour, minute, and seconds; with a time zone displacement value.  For example: timestamp(5) with time zone
timestamp (fractional seconds precision) with local time zone	fractional seconds precision must be a number between 0 and 9. (default is 6)	fractional seconds precision must be a number between 0 and 9. (default is 6)	fractional seconds precision must be a number between 0 and 9. (default is 6)	Includes year, month, day, hour, minute, and seconds; with a time zone expressed as the session time zone.  For example: timestamp(4) with local time zone
interval year (year precision) to month	year precision is the number of digits in the year. (default is 2)	year precision is the number of digits in the year. (default is 2)	year precision is the number of digits in the year. (default is 2)	Time period stored in years and months.  For example: interval year(4) to month
interval day (day precision) to second (fractional seconds precision)	day precision must be a number between 0 and 9. (default is 2)  fractional seconds precision must be a number between 0 and 9. (default is 6)	day precision must be a number between 0 and 9. (default is 2)  fractional seconds precision must be a number between 0 and 9. (default is 6)	day precision must be a number between 0 and 9. (default is 2)  fractional seconds precision must be a number between 0 and 9. (default is 6)	Time period stored in days, hours, minutes, and seconds.  For example: interval day(2) to second(6)

22. Large Object (LOB) Data types:

Data Type Syntax	Oracle 9i	Oracle 10g	Oracle 11g	Explanation (if applicable)
bfile	Maximum file size of 4GB.	Maximum file size of 232-1 bytes.	Maximum file size of 264-1 bytes.	File locators that point to a binary file on the server file system (outside the database).
blob	Store up to 4GB of binary data.	Store up to (4 gigabytes -1) * (the value of the CHUNK parameter of LOB storage).	Store up to (4 gigabytes -1) * (the value of the CHUNK parameter of LOB storage).	Stores unstructured binary large objects.
clob	Store up to 4GB of character data.	Store up to (4 gigabytes -1) * (the value of the CHUNK parameter of LOB storage) of character data.	Store up to (4 gigabytes -1) * (the value of the CHUNK parameter of LOB storage) of character data.	Stores single-byte and multi-byte character data.
nclob	Store up to 4GB of character text data.	Store up to (4 gigabytes -1) * (the value of the CHUNK parameter of LOB storage) of character text data.	Store up to (4 gigabytes -1) * (the value of the CHUNK parameter of LOB storage) of character text data.	Stores unicode data.

 

23. Rowid Data types:

Data Type Syntax	Oracle 9i	Oracle 10g	Oracle 11g	Explanation (if applicable)
rowid	The format of the rowid is: BBBBBBB.RRRR.FFFFF  Where BBBBBBB is the block in the database file; RRRR is the row in the block; FFFFF is the database file.	The format of the rowid is: BBBBBBB.RRRR.FFFFF  Where BBBBBBB is the block in the database file; RRRR is the row in the block; FFFFF is the database file.	The format of the rowid is: BBBBBBB.RRRR.FFFFF  Where BBBBBBB is the block in the database file; RRRR is the row in the block; FFFFF is the database file.	Fixed-length binary data. Every record in the database has a physical address or rowid.
urowid(size)				Universal rowid.  Where size is optional.

What is a DUAL Table in Oracle?

This is a single row and single column dummy table provided by oracle. This is used to perform mathematical calculations without using a table. Select * from DUAL ; Output:  DUMMY ------- X Select 777 * 888 from Dual; Output:  777 * 888-------🡪689976

Keywords in a SQL Statement:

Keyword	Required?	Description	Capability
SELECT	Yes	Specifies which columns should be shown in the result. Projection produces a subset of the columns in the table. An expression is a combination of one or more values, operators, and SQL functions that resolves to a value. The list of expressions that appears after the SELECT keyword and before the FROM clause is called the select list.	Projection
FROM	Yes	Specifies the tables or views from which the data should be retrieved.	Joining
WHERE	No	Specifies a condition to filter rows, producing a subset of the rows in the table. A condition specifies a combination of one or more expressions and logical (Boolean) operators and returns a value of TRUE, FALSE, or UNKNOWN.	Selection
ORDER BY	No	Specifies the order in which the rows should be shown.

Projection and Selection:

DISTINCT Clause:

The DISTINCT clause allows you to remove duplicates from the result set. The DISTINCT clause can only be used with select statements. The syntax for the DISTINCT clause is: SELECT DISTINCT columns FROM tables WHERE predicates; Ex: SELECT DISTINCT DEPTNO,JOB FROM EMP; DEPTNO JOB --------- --------- 10 CLERK 10 MANAGER 10 PRESIDENT 20 ANALYST 20 CLERK 20 MANAGER 30 CLERK 30 MANAGER 30 SALESMAN

SUB LANGUAGES:

Structure query language was mainly subdivided into five Sub languages those are fallowing types:

Data Definition Language	Data Manipulation Language	Data Retrieval Language	Transition Control Language(Three)	Data Control Language(Double)
DROP	INSERT	SELECT	COMMIT	GRANT
Rename	DELETE		ROLLBACK	REVOKE
CREATE	UPDATE		SAVEPOINT
ALTER
TRUNCATE

1. Data Definition Language: 

This is the first sub language in SQL which is used to define the database objects such as table, view, synonym, index and sequence. 

Data Definition Language (DDL) statements are used to define the database structure or schema. This language contains five commands those are i) CREATE ii) ALTER iii) RENAME iv) TRUNCATE v)DROP. 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

i) CREATE: This command is used to creating the database objects such as Table, View e.t.c. Syntax:  create table <table name> (col1 datatype(Size), col2 datatype(Size), coln datatype(size)); Ex: create table emp(eid varhcar(3), ename varchar2(12), sal number(6,2));

When creating tables, you must provide: 

Table name Column name(s) Data type for each column

Rules for naming a table: 

1. Table name should start with alphabets which contains minimum 1 and maximum 30 characters it should not allows any spaces or any special characters such as except _# and 0 to 9. 2. A table can have minimum 1 column maximum thousand columns. 3. A table can have 0 no of records and maximum n no of records up to hard disk capacity. 4. Oracle reserved keywords and words should not be used column names or table names. 5. The rules which we are following for table name the same rules are applicable for column names. 6. The numeric precision for column must be 1 to 38.

ii) ALTER: This command is used to modify the structure of the table. Using this command we can perform four different operations. This command contains four subprograms those are ALTER – Modify ALTER – ADD ALTER – RENAME ALTER – DROP

ALTER – Modify: This command is used to increase or decrease the size of the data type and also we can use the data type from all data type to new data type. Syntax:  ALTER TABLE <Table name> MODIFY <Column name> data type(size); Ex: ALTER TABLE EMP MODIFY ENAME VARCHAR2(20); Syntax for modify more than one column: ALTER TABLE <Table name> MODIFY (col1 data type(size), col2 data type(size)----,coln data type(size)); EX: Alter table emp modify (eid number(5), ename char(10));

ALTER ADD: This command is used to add a new column to the existing table. Syntax:  ALTER TABLE<Table name>add column name datatype(size); EX: ALTER TABLE EMP ADD DEPTNO NUMBER(3); Syntax to add more than one column: ALTER TABLE<table name> add (col1 datatype(size), col2 datatype(size)----,coln datatype(size)); EX: ALTER TABLE EMP ADD( mbnno number(10), addrs varchar2(10)); Note: Whenever we add a new column to a table the new column is always added to the end of the table only.

ALTER – RENAME: This command is used to change the column name from old column name to new column name. Syntax:  ALTER TABLE<table name> rename column<old column name> to <new column name); EX: ALTER TABLE EMP RENAME COLUMN SAL TO SLARY; Syntax change table name:  ALTER TABLE<old table name> RENAME TO <New table name> ; EX: ALTER TABLE EMP RENAME TO EMP1;

ALTER – DROP: This command is used to remove the column from the existing table. Syntax:  ALTER TABLE< table name>DROP COLUMN COLUMN NAME; EX: ALTER TABLE EMP DROP COLUMN DEPTNO; Syntax to drop more than one column: ALTER TABLE EMP DROP(eid, ename, salary, mbno, addrs);// Here we can’t drop all columns. ii)RENAME: This command is used to change the table name from old table name to new table name. Syntax: RENAME <old table name> to <new table name>; EX: RENAME EMP TO EMP1;

iii) TRUNCATE: This command is use delete records permanently from the existing table. Syntax: TRUNCATE TABLE<table name>; Ex: TRUNCATE TABLE EMP; iv) DROP: This command is used to drop entire data from database. Syntax:  DROP TABLE< table name>; EX: DROP TABLE EMP;

Difference between TRUNCATE and DELETE:       1. TRUNCATE is DDL command, DELETE is DML command. We can delete data permanently through TRUNCATE; Through DELTE it is temporarily. We can’t delete single record Through TRUNCATE, but it is possible through DELTE.

2.Data Manipulation Language: 

This is the second sub language in SQL which contain four commands those are INSERT, UPDATE, SELECT, DELETE and which is used for manipulate the data within database. Data Manipulation Language (DML) statements are used for managing data within schema objects. Some examples: 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

INSERT: This command is used to insert the records in the table using this command we can insert the data into the table in two methods i. Explicit method  ii. Implicit method i)Explicit Method: In this method user need to enter all the values into the columns without left any column data. Syn: INSERT INTO <TABLE NAME> VALUES (Val1, Val2,------,Valn); EX: INSERT INTO EMP Values(101,’RAJU’,500); Note: We can use && in front of any column. By placing like this the use is – “It takes particular column values as default for remaining all values. Syntax to insert the records into the table using insertion operation: INSERT INTO<Table name> VALUES(&col1,&col2,--------,&coln); EX: INSERT INTO EMP Values(&Eid,’&Ename’,&Sal’); ii)Implicit method: This method we can enter the values at required columns in the table. EX: INSERT INTO < Table name> (Col1,Col2,------,Coln) values(Val1, Val2,--------,Valn); EX: INSERT INTO EMP(EID,SAL) VALUES(115,9800);

UPDATE: This command is used to modify the data existing table. Using this command we can modify all the records in the table also we can modify specific records in the table using WHERE clause. Syn: UPDATE <Table name> SET COLUMN NAME = “VALUE’; EX: UPDATE EMP SET ENAME = ‘RAJU’; Syntax to modify more than one column data at a time: UPDATE <Table name> SET COL1=VALUE,COL2=VALUE,-------,COLN=VALUE; EX: UPDATE EMP SET EID = 007, ENAME = ‘JAMES’;

DELETE: This command is used to delete the records from the existing table. Using this command we can delete all the records and also we can delete specific records from the table. Syn: DELETE FROM <Table Name> <Column Name>; EX1: delete from rose no; EX2: delete from rose where name='UPASANA'; EX3: delete rose name where name='upasana' or name='vijay';

3. Data Retrieval Language:

Select Data Base Object From Data Base:

Select Data Base Object Columns From Data Base:

Select Data Base Object Rows From Data Base:

SELECT: This command is used to retrieve the data from the existing table. Using this command we can retrieve all records and also we can retrieve some specific records in the table(Using where clause). SELECT: This command is used to retrieve the data from the existing table Syntax: SELECT * [selected-columnlist]    FROM <Database_Objectname>    [WHERE search-condition]    [GROUP BY column-name [, column-name]... ]    [HAVING search-condition]    [[UNION | UNION ALL |INTERSECT | MINUS]     select-statement]...    [ORDER BY {unsigned integer | column-name}    [ASC|DESC]]; -OR-  SELECT * FROM < Table Name>; EX: SELECT * FROM EMP; Here * represents all columns.       Note:”*’’ also called Projection Operator.

Data Control Language:

This is the fourth sub language in Sql. DCL commands are used to enforce database security in a multiple user database environment. Only Database Administrator's or owners of the database object can provide/remove privileges on a database object. Contains two commands. Data Control Language (DCL) statements. Some examples: GRANT - gives user's access privileges to database REVOKE - withdraw access privileges given with the GRANT command

 

1. GRANT:

SQL GRANT is a command used to provide access or privileges on the database objects to the users.  The Syntax for the GRANT command is: GRANT privilege_name ON object_name TO {user_name |PUBLIC |role_name} [WITH GRANT OPTION]; Example: GRANT INSERT, UPDATE ON Empl TO HR_Clerk; GRANT ALL ON Payroll TO HR_Manager;

2. REVOKE:

The REVOKE command removes user access rights or privileges to the database objects. The Syntax for the REVOKE command is:  REVOKE privilege_name ON object_name FROM {user_name |PUBLIC |role_name}; Example: REVOKE UPDATE (Salary, Taxes) ON Payroll FROM HR Assc;

2. 
   
3. 
   

4. Privileges and Roles:

Privileges:  Privileges defines the access rights provided to a user on a database object. There are two types of privileges.  1) System privileges - This allows the user to CREATE, ALTER, or DROP database objects.   2) Object privileges - This allows the user to EXECUTE, SELECT, INSERT, UPDATE, or DELETE data from database objects to which the privileges apply.  Roles:  Roles are a collection of privileges or access rights. When there are many users in a database it becomes difficult to grant or revoke privileges to users. Therefore, if you define roles, you can grant or revoke privileges to users, thereby automatically granting or revoking privileges. You can either create Roles or use the system roles pre-defined by oracle.

6. Creating Roles:

The Syntax to create a Role is: CREATE ROLE role_name [IDENTIFIED BY password]; For Example:  To create a role called "developer" with password as "pwd",the code will be as follows CREATE ROLE testing [IDENTIFIED BY pwd]; It's easier to GRANT or REVOKE privileges to the users through a role rather than assigning a privilege direclty to every user. If a role is identified by a password, then, when you GRANT or REVOKE privileges to the role, you definetely have to identify it with the password. We can GRANT or REVOKE privilege to a role as below. First, create a testing Role CREATE ROLE testing; Second, grant a CREATE TABLE privilege to the ROLE testing. You can add more privileges to the ROLE. GRANT CREATE TABLE TO testing; Third, grant the role to a user. GRANT testing TO user1; To Revoke a CREATE TABLE privilege from testing ROLE, you can write: REVOKE CREATE TABLE FROM testing;

The Syntax to drop a role from the database is as below:

DROP ROLE role_name;  For example: To drop a role called developer, you can write: DROP ROLE testing;  For example: To grant CREATE TABLE privilege to a user by creating a testing role:

TRANSACTION CONTROL LANGUAGE:

This is the Fifth sub language in SQL which contains the commands. i)                    Commit ii)                  Rollback iii)                Save point Transaction:  Any operation that can perform by using DML commands is known as Transaction. Session: It can e be defined as some interval of time or some span of time. It means that the moment the user log on to the database to the user logoff to the database this span of the time is known as session.  Transaction Control (TCL) statements are used to manage the changes made by DML statements. It allows 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 We can terminate the session two ways : 1.      Normal Termination 2.      Abnormal Termination 1.      Normal Termination: Terminating the session by typing exit or quit SQL prompt then this termination can be called as Normal Termination. Normal Termination always saves our Transactions. 2.      Abnormal Termination: Terminating the session by directly close the window button or directly switch off the C.P.U or power off. Abnormal Termination does not save our transaction.

Commit: This command is used for save the transactions explicitly from the moment by user logon to the database to till execute this command. SYN: Commit; Then commit will complete.

Rollback: This command is used to discard the transactions from the moment the user logon to the database till execute this command or executing the commit command too . Syn: Rollback; Rollback complete

Save point: save point is reference name for a particular set of transactions. SYN:  savepoint <savepoint name>; EX: savepoint s3; To retrieve syntax is  EX: savepoint to <save point name>; EX: rollback to savepoint  s1; Note: We can’t rollback the committed transactions but we can commit the rollback Transactions.

CLAUSES OF SQL:

Where Clause: 

This clause is used to check the condition based on the condition we can retrieve, we can modify, and we can remove some specific records in the table. When using a WHERE clause: The WHERE clause directly follows the FROM clause in the SQL statement syntax  The WHERE clause consists of the WHERE keyword and a condition or conditions.  The condition in a WHERE clause specifies a comparison of values that limits the rows that are returned by a query. EX: select * from emp where column_name=<value>;

Group by Clause: 

This clause is used to divide the values depending on it’s a true. Group by clause always used along with group functions. Syntax: Guidelines The SELECT clause can contain only the column or columns that appear in the GROUP BY clause, in addition to any group function or functions.  The GROUP BY clause must contain any column or columns that appear in the SELECT clause that are not listed in the group function.  By default, rows are sorted in ascending order of the columns that are included in the GROUP BY list.  The ORDER BY clause is optional. If used, it overrides the default sorting.  EX: select deptno, count(*),sum(sal), max(sal), min(sal), avg(Sal) from emp group by deptno;

HAVING Clause:

This will work as where clause which can  be used only with group by because of absence of where clause in group by. Syntax: EX: select deptno,count(*),sum(sal),max(sal),min(Sal) from emp group by deptno having count(*)>5; EX: select deptno,count(*), sum(sal), max(sal), min(sal), avg(sal) from emp where deptno=20 group by dept no; Here we use where clause but we used where clause before the group by.

ORDER BY Clause: 

This clause is used to arrange the data either ascending or descending order. By default order by clause will arrange the data in ascending order. If we want to arrange the data in descending order then we use an option called DESC stands for descending order. We can order by clause on character columns then it will arrange the data in alphabetic order. We can apply the order by clause on more than one column in the same table. We can apply the order by clause only with the “select” command. Syntax by applying order by clause: SEELCT * FROM EMP ORDER BY <Column name> <ASCE><DESC>; EX: Select * from xyz order by ename salary desc;

FUNCTIONS: 

         A function is a self contained block which is use to perform some task.           The main advantage of function is code reusability.           Then it automatically reduces the redundancy of the instructions so that reduces the maintenance cost and increase the system performance.           Function always returns only one value.

Functions are dividing into two types:

System/predefined functions  2.      User defined functions

1.    1.  System defined functions: A function which is defined by the system comes along with the software those functions can be called as System defined functions. 2.     2.User defined functions: A function which is defined manually and programmatically those functions are called used defined functions.

In SQL predefined functions are further divided into three types:

i)                    Single row functions ii)                   Multiple row functions iii)                Miscellaneous functions.

Definitions:

i.                    Single row functions: Single row functions are those functions will work on each and every record in the table and returns one value. ii.                  Multiple row functions: Multiple row functions are those functions will work on the entire table and returns only one value. iii.                Miscellaneous functions: Miscellaneous functions are those functions which are used to convert the null values into not null values.

SINGLE ROW FUNCTIONS:

Single row functions can be categorized into five. These will be applied for each row and produces individual output for each row.  Numeric functions String functions Date functions Miscellaneous functions Conversion functions

NUMERIC FUNCTIONS:

1.Abs 2.Sign 3.Sqrt 4.Mod 5.NVL 6.Power 7.Exp 8.Ln 9.Log 10.Ceil 11.Floor 12.Round 13.Trunk 14.Bitand 15.Greatest 16.Least 17.Coalesce

1) ABS:

Absolute value is the measure of the magnitude of value.       Absolute value is always a positive number.      Syntax: abs (value)      Ex:          select abs(5), abs(-5), abs(0), abs(null) from dual;                      ABS(5)    ABS(-5)     ABS(0)  ABS(NULL)                      ---------- ----------    ---------- -------------                           5              -5                0

2) SIGN:

Sign gives the sign of a value.      Syntax: sign (value)      Ex: select sign(5), sign(-5), sign(0), sign(null) from dual;    SIGN(5)   SIGN(-5)    SIGN(0) SIGN(NULL) ----------   ----------     ---------- --------------        1            -1          0

3) SQRT:

This will give the square root of the given value.       Syntax: sqrt (value) --  here value must be positive.      Ex:           SQL> select sqrt(4), sqrt(0), sqrt(null), sqrt(1) from dual;     SQRT(4)    SQRT(0) SQRT(NULL)    SQRT(1) ----------    ---------- ---------------    ----------          2               0                     1

4) MOD:     

This will give the remainder.       Syntax: mod (value, divisor)      Ex: select mod(7,4), mod(1,5), mod(null,null), mod(0,0), mod(-7,4) from dual;    MOD(7,4)   MOD(1,5) MOD(NULL,NULL)   MOD(0,0)  MOD(-7,4) ------------   ----------  ---------------------    ----------- -------------          3               1                         0         -3

5) NVL:

This will substitutes the specified value in the place of null values.       Syntax: nvl (null_col, replacement_value)      Ex: select no, name, nvl(marks,300) from student;           NO NAME  NVL(MARKS,300) ---  -------  ---------------------            1  a             100                     2 b             200            3 c             300 SQL> select nvl(1,2), nvl(2,3), nvl(4,3), nvl(5,4) from dual;   NVL(1,2)   NVL(2,3)   NVL(4,3)   NVL(5,4)   ----------    ----------    ----------    ----------          1               2          4                   5

6) POWER:     

Power is the ability to raise a value to a given exponent.       Syntax: power (value, exponent)      Ex: select power(2,5), power(0,0), power(1,1), power(null,null),  power(2,-5)      from dual;

7) EXP:

This will raise e value to the give power.      Syntax: exp (value)      Ex: select exp(1), exp(2), exp(0), exp(null), exp(-2) from dual;      EXP(1)     EXP(2)     EXP(0)  EXP(NULL)    EXP(-2) --------     ---------     --------  -------------    ---------- 2.71828183   7.3890561            1                          .135335283

    8) LN:     

This is based on natural or base e logarithm.      Syntax: ln (value) -- here value must be greater than zero which is positive only.      Ex: select ln(1), ln(2), ln(null) from dual;       LN(1)      LN(2)      LN(NULL) -------      -------      ------------               0   .693147181           Ln and Exp are reciprocal to each other.             EXP (3) = 20.0855369             LN (20.0855369) = 3

9) LOG:

This is based on 10 based logarithm.     Syntax: log (10, value) -- here value must be greater than zero which is positive only.     Ex: select log(10,100), log(10,2), log(10,1), log(10,null) from dual; LOG(10,100)  LOG(10,2)  LOG(10,1) LOG(10,NULL) ---------------   -----------   ------------  -----------------                              2            .301029996          0 LN (value) = LOG (EXP(1), value) SQL> select  ln(3), log(exp(1),3) from dual;       LN(3)      LOG(EXP(1),3) -------      ----------------- 1.09861229    1.09861229

10) CEIL: 

This will produce a whole number that is greater than or equal to the specified value.      Syntax: ceil (value)      Ex: select ceil(5), ceil(5.1), ceil(-5), ceil( -5.1), ceil(0), ceil(null) from dual;     CEIL(5)  CEIL(5.1)   CEIL(-5) CEIL(-5.1)    CEIL(0) CEIL(NULL) ---------  -----------    ---------- ------------     --------  --------------                   5            6                -5            -5                 0

11) FLOOR:

This will produce a whole number that is less than or equal to the specified value.      Syntax: floor (value)      Ex: select floor(5), floor(5.1), floor(-5), floor( -5.1), floor(0), floor(null) from        dual;    FLOOR(5) FLOOR(5.1)  FLOOR(-5) FLOOR(-5.1)   FLOOR(0) FLOOR(NULL) -----------  -------------   ------------  --------------    -----------  ----------------           5               5                 -5                 -6                   0

12) ROUND:    

These will rounds numbers to a given number of digits of precision.      Syntax: round (value, precision)      Ex: select round(123.2345), round(123.2345,2), round(123.2354,2) from dual;           ROUND(123.2345)  ROUND(123.2345,0) ROUND(123.2345,2) ROUND(123.2354,2)           ---------------------   ------------------------  -----------------------  -----------------------                                 123                          123                            123.23                      123.24 SQL> select round(123.2345,-1), round(123.2345,-2), round(123.2345,-3),                    round(123.2345,-4) from dual;     ROUND(123.2345,-1) ROUND(123.2345,-2) ROUND(123.2345,-3) ROUND(123.2345,-4) ------------------------  -------------------------  ------------------------   ------------------------                            120                                100                            0                  0 SQL> select round(123,0), round(123,1), round(123,2) from dual; ROUND(123,0) ROUND(123,1) ROUND(123,2) -----------------  -----------------  ----------------          123                   123          123 SQL> select round(-123,0), round(-123,1), round(-123,2) from dual; ROUND(-123,0) ROUND(-123,1) ROUND(-123,2) ------------------  -----------------   -------------------                                 -123                 -123                    -123 SQL> select round(null,null), round(0,0), round(1,1), round(-1,-1), round(-2,-2)           from dual; ROUND(NULL,NULL) ROUND(0,0) ROUND(1,1) ROUND(-1,-1) ROUND(-2,-2) -----------------------  --------------  --------------  ----------------  ----------------                                     0                       1                   0                    0

     

  13) TRUNC :      

This will truncates or chops off digits of precision from a number.       Syntax: trunc (value, precision)       Ex: select trunc(123.2345), trunc(123.2345,2), trunc(123.2354,2) from dual; TRUNC(123.2345) TRUNC(123.2345,2) TRUNC(123.2354,2) ---------------------  -----------------------  -----------------------             123                        123.23                     123.23  SQL> select trunc(123.2345,-1), trunc(123.2345,-2), trunc(123.2345,-3),           trunc(123.2345,-4) from dual; TRUNC(123.2345,-1) TRUNC(123.2345,-2) TRUNC(123.2345,-3) TRUNC(123.2345,-4) ------------------------  ------------------------   -----------------------  ------------------------               120                              100                                0                             0 SQL> select trunc(123,0), trunc(123,1), trunc(123,2) from dual; TRUNC(123,0) TRUNC(123,1) TRUNC(123,2) ----------------   ----------------  -----------------          123                    123                 123 SQL> select trunc(-123,0), trunc(-123,1), trunc(-123,2) from dual; TRUNC(-123,0) TRUNC(-123,1) TRUNC(-123,2) -----------------   -----------------  -----------------          -123                    -123                -123 SQL> select trunc(123,-1), trunc(123,-2), trunc(123,-3), trunc(-123,-1), trunc(         -123,2), trunc(-123,-3) from dual; TRUNC(123,-1) TRUNC(123,-2) TRUNC(123,-3) TRUNC(-123,-1) TRUNC(-123,2) TRUNC(-123,-3) ------------- ------------- ------------- -------------- ------------- ---------------------------------           120           100             0                   -120          -123              0 SQL> select trunc(null,null), trunc(0,0), trunc(1,1), trunc(-1,-1), trunc(-2,-2) from         dual; TRUNC(NULL,NULL) TRUNC(0,0) TRUNC(1,1) TRUNC(-1,-1) TRUNC(-2,-2) -----------------------  -------------  -------------  ---------------  ----------------                                                      0                   1                     0                      0

14) BITAND

This will perform bitwise and operation.      Syntax: bitand (value1, value2)      Ex: select bitand(2,3), bitand(0,0), bitand(1,1), bitand(null,null), bitand(-2,-3)        from dual; BITAND(2,3) BITAND(0,0) BITAND(1,1) BITAND(NULL,NULL) BITAND(-2,-3) --------------  ---------------  --------------   ------------------------  -----------------           2           0           1                              -4

15) GREATEST:

This will give the greatest number.      Syntax: greatest (value1, value2, value3 … valuen)      Ex: select greatest(1, 2, 3), greatest(-1, -2, -3) from dual; GREATEST(1,2,3) GREATEST(-1,-2,-3) --------------------  -----------------------               3                 -1 If all the values are zeros then it will display zero. If all the parameters are nulls then it will display nothing. If any of the parameters is null it will display nothing.

16) LEAST:

This will give the least number.     Syntax: least (value1, value2, value3 … valuen)     Ex: select least(1, 2, 3), least(-1, -2, -3) from dual; LEAST(1,2,3)         LEAST(-1,-2,-3) --------------------  ----------------------- -3 If all the values are zeros then it will display zero. If all the parameters are nulls then it will display nothing. If any of the parameters is null it will display nothing.

17) COALESCE:    

This will return first non-null value.     Syntax: coalesce (value1, value2, value3 … valuen)      Ex:             SQL> select coalesce(1,2,3), coalesce(null,2,null,5) from dual; COALESCE(1,2,3) COALESCE(NULL,2,NULL,5) -------------------   -------------------------------               1                       2

STRING FUNCTIONS:

18.Initcap 19.Upper 20.Lower 21.Length 22.Rpad 23.Lpad 24.Ltrim 25.Rtrim 26.Trim 27.Translate 28.Replace 29.Soundex 30.Concat  ( ‘ || ‘ Concatenation operator)  31.Ascii 32.Chr 33.Substr 34.Instr 35.Decode 36.Greatest 37.Least 38.Coalesce

18) INITCAP:

This will capitalize the initial letter of the string.      Syntax: initcap (string)      Ex: select initcap('computer') from dual; INITCAP ----------- Computer

19) UPPER

This will convert the string into uppercase.      Syntax: upper (string)      Ex: select upper('computer') from dual; UPPER -----------                                              COMPUTER

20) LOWER

This will convert the string into lowercase.      Syntax: lower (string)      Ex: select lower('COMPUTER') from dual; LOWER ----------- computer

21) LENGTH:

This will give length of the string.      Syntax: length (string)      Ex: select length('computer') from dual; LENGTH -----------        8

22) RPAD:

This will allows you to pad the right side of a column with any set of characters.      Syntax: rpad (string, length [, padding_char])      Ex: select rpad('computer',15,'*'), rpad('computer',15,'*#') from dual; RPAD('COMPUTER'  RPAD('COMPUTER' ----------------------  ---------------------- computer*******    computer*#*#*#* -- Default padding character was blank space.

23) LPAD:

This will allows you to pad the left side of a column with any set of characters.      Syntax: lpad (string, length [, padding_char])      Ex: select lpad('computer',15,'*'), lpad('computer',15,'*#') from dual; LPAD('COMPUTER'  LPAD('COMPUTER' ---------------------   --------------------- *******computer   *#*#*#*computer --Default padding character was blank space.

24) LTRIM:

This will trim off unwanted characters from the left end of string.     Syntax: ltrim (string  [,unwanted_chars])      Ex: select ltrim('computer','co'), ltrim('computer','com') from dual; LTRIM(  LTRIM --------  --------- mputer   puter SQL> select ltrim('computer','puter'), ltrim('computer','omputer') from dual; LTRIM('C  LTRIM('C ----------   ---------- computer   computer                       -- If you haven’t specify any unwanted characters it will display entire string.

25) RTRIM:     

This will trim off unwanted characters from the right end of string.      Syntax: rtrim (string [, unwanted_chars])      Ex: select rtrim('computer','er'), rtrim('computer','ter') from dual; RTRIM(  RTRIM --------  --------- comput   compu SQL> select rtrim('computer','comput’), rtrim('computer','compute') from dual; RTRIM('C  RTRIM('C ----------   ---------- computer   computer            -- If you haven’t specify any unwanted characters it will display entire string.

26) TRIM:

This will trim off unwanted characters from the both sides of string.      Syntax: trim (unwanted_chars from string)      Ex: select trim( 'i' from 'indiani') from dual; TRIM( ----- ndian        SQL> select trim( leading'i' from 'indiani') from dual; -- this will work as LTRIM TRIM(L ------ ndiani SQL> select trim( trailing'i' from 'indiani') from dual; -- this will work as RTRIM TRIM(T ------ Indian

27) TRANSLATE:

This will replace the set of characters, character by character.      Syntax: translate (string, old_chars, new_chars)      Ex: select translate('india','in','xy') from dual; TRANS -------- xydxa

28) REPLACE:

This will replace the set of characters, string by string.      Syntax: replace (string, old_chars [, new_chars])      Ex:           SQL> select replace('india','in','xy'), replace(‘india’,’in’) from dual; REPLACE   REPLACE -----------  ----------- Xydia         dia

29) SOUNDEX:

This will be used to find words that sound like other words, exclusively used in where   clause.     Syntax: soundex (string)     Ex: select * from emp where soundex(ename) = soundex('SMIT');      EMPNO ENAME      JOB              MGR HIREDATE         SAL     DEPTNO      --------  --------      -----             -----  ------------      --------- ----------        7369    SMITH      CLERK         7902   17-DEC-80        500         20

30) CONCAT:

This will be used to combine two strings only.     Syntax: concat (string1, string2)   Ex: select concat('computer',' operator') from dual; CONCAT('COMPUTER' ------------------------- computer operator     If you want to combine more than two strings you have to use concatenation       operator(||).          SQL> select 'how' || ' are' || ' you' from dual; 'HOW'||'ARE --------------- how are you

31) ASCII:

This will return the decimal representation in the database character set of the first character of the string.     Syntax: ascii (string)     Ex: select ascii('a'), ascii('apple') from dual; ASCII('A')  ASCII('APPLE') ------------  ------------------         97             97

32) CHR:

This will return the character having the binary equivalent to the string in either the database character set or the national character set.     Syntax: chr (number)     Ex: select chr(97) from dual; CHR -----   a

33) SUBSTR:

This will be used to extract substrings.       Syntax: substr (string, start_chr_count [, no_of_chars])      Ex: select substr('computer',2), substr('computer',2,5), substr('computer',3,7) from dual; SUBSTR(  SUBST  SUBSTR ----------  -------   -------- omputer  omput   mputer If no_of_chars parameter is negative then it will display nothing. If both parameters except string are null or zeros then it will display nothing. If no_of_chars parameter is greater than the length of the string then it ignores and calculates based on the orginal string length. If start_chr_count is negative then it will extract the substring from right end. 1 2 3 4 5 6 7 8 C O M P U T E R                                 -8 -7 -6 -5 -4 -3 -2 -1

34) INSTR:

This will allows you for searching through a string for set of characters.       Syntax: instr (string, search_str [, start_chr_count [, occurrence] ])      Ex: select instr('information','o',4,1), instr('information','o',4,2) from dual; INSTR('INFORMATION','O',4,1) INSTR('INFORMATION','O',4,2) ------------------------------------  -------------------------------------                            4                           10 If you are not specifying start_chr_count and occurrence then it will start       search from the beginning and finds first occurrence only. If both parameters start_chr_count and occurrence are null, it will display       nothing.

35) DECODE:

Decode will act as value by value substitution.     For every value of field, it will checks for a match in a series of if/then tests.      Syntax: decode (value, if1, then1, if2, then2, ……. else);     Ex: select sal, decode(sal,500,'Low',5000,'High','Medium') from emp;        SAL     DECODE                                                  -----    ---------        500 Low       2500 Medium       2000 Medium       3500 Medium       3000 Medium       5000 High       4000 Medium SQL> select decode(1,1,3), decode(1,2,3,4,4,6) from dual; DECODE(1,1,3) DECODE(1,2,3,4,4,6) -----------------  ------------------------             3                   6 If the number of parameters are odd and different then decode will display       nothing. If the number of parameters are even and different then decode will display last       value. If all the parameters are null then decode will display nothing. If all the parameters are zeros then decode will display zero.

36) GREATEST:

This will give the greatest string.      Syntax: greatest (strng1, string2, string3 … stringn)      Ex: select greatest('a', 'b', 'c'), greatest('satish','srinu','saketh') from dual; GREAT GREAT -------  -------     c   srinu If all the parameters are nulls then it will display nothing. If any of the parameters is null it will display nothing.

37) LEAST:

This will give the least string.     Syntax: greatest (strng1, string2, string3 … stringn)     Ex: select least('a', 'b', 'c'), least('satish','srinu','saketh') from dual; LEAST LEAST -------  -------     a   saketh If all the parameters are nulls then it will display nothing. If any of the parameters is null it will display nothing.

38) COALESCE:

This will gives the first non-null string.     Syntax: coalesce (strng1, string2, string3 … stringn)    Ex: select coalesce('a','b','c'), coalesce(null,'a',null,'b') from dual; COALESCE COALESCE -----------   -----------        a   a

DATE FUNCTIONS:

39. Sysdate   40.Current_date   41. Current_timestamp   42.Systimestamp  43.Localtimestamp   44.Dbtimezone 45.Sessiontimezone   46.To_char  47.To_date  48.Add_months  49.Months_between  50.Next_day  51.Last_day  52.Extract  53.Greatest  54.Least  55.Round  56.Trunc  57.New_time  58.Coalesce

Oracle default date format is DD-MON-YY. We can change the default format to our desired format by using the following command. SQL> alter session set nls_date_format = ‘DD-MONTH-YYYY’;         But this will expire once the session was closed.

39) SYSDATE:

This will give the current date and time.       Ex: select sysdate from dual; SYSDATE ----------- 24-DEC-06

40) CURRENT_DATE:

This will returns the current date in the session’s timezone.       Ex: select current_date from dual; CURRENT_DATE ------------------      24-DEC-06

41) CURRENT_TIMESTAMP:

This will returns the current timestamp with the active time zone information.       Ex:   select current_timestamp from dual; CURRENT_TIMESTAMP --------------------------------------------------------------------------- 24-DEC-06 03.42.41.383369 AM +05:30

42) SYSTIMESTAMP:

This will returns the system date, including fractional seconds and time zone of the   database.       Ex: select systimestamp from dual; SYSTIMESTAMP --------------------------------------------------------------------------- 24-DEC-06 03.49.31.830099 AM +05:30

43) LOCALTIMESTAMP:

This will returns local timestamp in the active time zone information, with no time zone information shown.       Ex: select localtimestamp from dual; LOCALTIMESTAMP --------------------------------------------------------------------------- 24-DEC-06 03.44.18.502874 AM

44) DBTIMEZONE:

This will returns the current database time zone in UTC format. (Coordinated Universal Time)     Ex: select dbtimezone from dual; DBTIMEZONE ---------------    -07:00

45) SESSIONTIMEZONE:

This will returns the value of the current session’s time zone.     Ex: select sessiontimezone from dual; SESSIONTIMEZONE ------------------------------------ +05:30

   

       46) TO_CHAR:

This will be used to extract various date formats.     The available date formats as follows.     Syntax: to_char (date, format)

    DATE FORMATS: 

D -- No of days in week DD -- No of days in month DDD -- No of days in year MM -- No of month MON -- Three letter abbreviation of month MONTH -- Fully spelled out month RM -- Roman numeral month DY -- Three letter abbreviated day DAY -- Fully spelled out day Y -- Last one digit of the year YY -- Last two digits of the year YYY -- Last three digits of the year YYYY -- Full four digit year SYYYY           -- Signed year I -- One digit year from ISO standard IY -- Two digit year from ISO standard IYY -- Three digit year from ISO standard IYYY -- Four digit year from ISO standard Y, YYY -- Year with comma YEAR -- Fully spelled out year CC -- Century Q -- No of quarters W -- No of weeks in month WW -- No of weeks in year IW -- No of weeks in year from ISO standard HH -- Hours MI -- Minutes SS -- Seconds FF -- Fractional seconds AM or PM -- Displays AM or PM depending upon time of day A.M or P.M -- Displays A.M or P.M depending upon time of day AD or BC -- Displays AD or BC depending upon the date A.D or B.C -- Displays AD or BC depending upon the date FM -- Prefix to month or day, suppresses padding of month or day TH -- Suffix to a number SP -- suffix to a number to be spelled out SPTH -- Suffix combination of TH and SP to be both spelled out THSP -- same as SPTH

Ex: select to_char(sysdate,'dd month yyyy hh:mi:ss am dy') from dual; TO_CHAR(SYSDATE,'DD MONTH YYYYHH:MI ---------------------------------------------------- 24 december  2006 02:03:23 pm sun     SQL> select to_char(sysdate,'dd month year') from dual; TO_CHAR(SYSDATE,'DDMONTHYEAR') ------------------------------------------------------- 24 december  two thousand six      SQL> select to_char(sysdate,'dd fmmonth year') from dual; TO_CHAR(SYSDATE,'DD FMMONTH YEAR') ------------------------------------------------------- 24 december two thousand six      SQL> select to_char(sysdate,'ddth DDTH') from dual; TO_CHAR(S ------------24th 24TH      SQL> select to_char(sysdate,'ddspth DDSPTH') from dual; TO_CHAR(SYSDATE,'DDSPTHDDSPTH ------------------------------------------ twenty-fourth TWENTY-FOURTH      SQL> select to_char(sysdate,'ddsp Ddsp DDSP ') from dual; TO_CHAR(SYSDATE,'DDSPDDSPDDSP') ------------------------------------------------ twenty-four Twenty-Four TWENTY-FOUR

47) TO_DATE:

This will be used to convert the string into data format.     Syntax: to_date (date)     Ex: select to_char(to_date('24/dec/2006','dd/mon/yyyy'), 'dd * month * day')      from dual; TO_CHAR(TO_DATE('24/DEC/20 -------------------------- 24 * december  * Sunday -- If you are not using to_char oracle will display output in default date format.

48) ADD_MONTHS:

This will add the specified months to the given date.     Syntax: add_months (date, no_of_months)     Ex:          SQL> select add_months(to_date('11-jan-1990','dd-mon-yyyy'), 5) from dual; ADD_MONTHS ---------------- 11-JUN-90 SQL> select add_months(to_date('11-jan-1990','dd-mon-yyyy'), -5) from dual; ADD_MONTH --------------- 11-AUG-89            If no_of_months is zero then it will display the same date. If no_of_months is null then it will display nothing.

49) MONTHS_BETWEEN:

This will give difference of months between two dates.     Syntax: months_between (date1, date2)     Ex: select months_between(to_date('11-aug-1990','dd-mon-yyyy'), to_date('11-                  jan-1990','dd-mon-yyyy')) from dual;  MONTHS_BETWEEN(TO_DATE('11-AUG-1990','DD-MON-YYYY'),TO_DATE('11-JAN-1990','DD-MON-YYYY')) -----------------------------------------------------------------------------------------------                                                                                         7            SQL> select months_between(to_date('11-jan-1990','dd-mon-yyyy'), to_date('11- aug-1990','dd-mon-yyyy')) from dual;   MONTHS_BETWEEN(TO_DATE('11-JAN-1990','DD-MON-YYYY'),TO_DATE('11-AUG-1990','DD-MON-YYYY')) -------------------------------------------------------------------------------------------------                                                                                       -7

50) NEXT_DAY:

This will produce next day of the given day from the specified date.     Syntax: next_day (date,  day)     Ex: select next_day(to_date('11-nov-2011','dd-mon-yyyy'),'fri') from dual; NEXT_DAY( ------------- 31-DEC-06 -- If the day parameter is null then it will display nothing.

51) LAST_DAY:

This will produce last day of the given date.     Syntax: last_day (date)     Ex:          SQL> select last_day(to_date('24-dec-2006','dd-mon-yyyy'),'sun') from dual; LAST_DAY( -------------                                            31-DEC-06

52) EXTRACT:

This is used to extract a portion of the date value.     Syntax: extract ((year | month | day | hour | minute | second), date)     Ex:          SQL> select extract(year from sysdate) from dual; EXTRACT(YEARFROMSYSDATE) ------------------------------------                     2006 -- You can extract only one value at a time.

53) GREATEST:

This will give the greatest date.      Syntax: greatest (date1, date2, date3 … daten)      Ex:          SQL> select greatest(to_date('11-jan-90','dd-mon-yy'),to_date('11-mar-90','dd-                  mon-yy'),to_date('11-apr-90','dd-mon-yy')) from dual;     GREATEST(   -------------    11-APR-90

54) LEAST:

This will give the least date.      Syntax: least (date1, date2, date3 … daten)       Ex:          SQL> select least(to_date('11-jan-90','dd-mon-yy'),to_date('11-mar-90','dd-mon- yy'),to_date('11-apr-90','dd-mon-yy')) from dual;   LEAST(   -------------    11-JAN-90

55) ROUND:

Round will rounds the date to which it was equal to or greater than the given date.     Syntax: round (date, (day | month | year))     If the second parameter was year then round will checks the month of the given date in the following ranges. JAN -- JUN JUL -- DEC     If the month falls between JAN and JUN then it returns the first day of the current year.     If the month falls between JUL and DEC then it returns the first day of the next year.     If the second parameter was month then round will checks the day of the given date in the following ranges. 1 -- 15 16 -- 31     If the day falls between 1 and 15 then it returns the first day of the current month.     If the day falls between 16 and 31 then it returns the first day of the next month.     If the second parameter was day then round will checks the week day of the given date in the following ranges. SUN -- WED THU -- SUN     If the week day falls between SUN and WED then it returns the previous sunday.     If the weekday falls between THU and SUN then it returns the next sunday. If the second parameter was null then it returns nothing. If the you are not specifying the second parameter then round will resets the time to the begining of the current day in case of user specified date. If the you are not specifying the second parameter then round will resets the time to the begining of the next day in case of sysdate.        Ex:          SQL> select round(to_date('24-dec-04','dd-mon-yy'),'year'), round(to_date('11-mar-  06','dd-mon-yy'),'year') from dual; ROUND(TO_ ROUND(TO_ ------------   --------------- 01-JAN-05   01-JAN-06             SQL> select round(to_date('11-jan-04','dd-mon-yy'),'month'), round(to_date('18- jan-04','dd-mon-yy'),'month') from dual;      ROUND(TO_ ROUND(TO_ -------------  --------------- 01-JAN-04    01-FEB-04             SQL> select round(to_date('26-dec-06','dd-mon-yy'),'day'), round(to_date('29-dec-  06','dd-mon-yy'),'day') from dual;    ROUND(TO_ ROUND(TO_ --------------  -------------- 24-DEC-06     31-DEC-06             SQL> select to_char(round(to_date('24-dec-06','dd-mon-yy')), 'dd mon yyyy    hh:mi:ss am') from dual; TO_CHAR(ROUND(TO_DATE(' --------------------------------- 24 dec 2006 12:00:00 am

56) TRUNC:

Trunc will chops off the date to which it was equal to or less than the given date.     Syntax: trunc (date, (day | month | year)) If the second parameter was year then it always returns the first day of the current year. If the second parameter was month then it always returns the first day of the current month. If the second parameter was day then it always returns the previous sunday. If the second parameter was null then it returns nothing. If the you are not specifying the second parameter then trunk will resets the time to the begining of the current day.      Ex: select trunc(to_date('24-dec-04','dd-mon-yy'),'year'), trunc(to_date('11-mar- 06','dd-mon-yy'),'year') from dual; TRUNC(TO_ TRUNC(TO_ -------------  -------------- 01-JAN-04    01-JAN-06                       SQL> select trunc(to_date('11-jan-04','dd-mon-yy'),'month'), trunc(to_date('18-jan- 04','dd-mon-yy'),'month') from dual; TRUNC(TO_ TRUNC(TO_ -------------  ------------- 01-JAN-04    01-JAN-04     SQL> select trunc(to_date('26-dec-06','dd-mon-yy'),'day'), trunc(to_date('29-dec-06','dd-mon-yy'),'day') from dual;                         TRUNC(TO_ TRUNC(TO_ -------------  -------------- 24-DEC-06 24-DEC-06                     SQL> select to_char(trunc(to_date('24-dec-06','dd-mon-yy')), 'dd mon yyyy hh:mi:ss  am') from dual; TO_CHAR(TRUNC(TO_DATE(' --------------------------------- 24 dec 2006 12:00:00 am

57) NEW_TIME:

This will give the desired timezone’s date and time.      Syntax: new_time (date, current_timezone, desired_timezone)      Available timezones are as follows TIMEZONES: AST/ADT -- Atlantic standard/day light time BST/BDT -- Bering standard/day light time CST/CDT -- Central standard/day light time EST/EDT -- Eastern standard/day light time GMT    -- Greenwich mean time HST/HDT -- Alaska-Hawaii standard/day light time MST/MDT -- Mountain standard/day light time NST           -- Newfoundland standard time PST/PDT -- Pacific standard/day light time YST/YDT -- Yukon standard/day light time     Ex: select to_char(new_time(sysdate,'gmt','yst'),'dd mon yyyy hh:mi:ss am') from    dual; TO_CHAR(NEW_TIME(SYSDAT ----------------------------------- 24 dec 2006 02:51:20 pm           SQL> select to_char(new_time(sysdate,'gmt','est'),'dd mon yyyy hh:mi:ss am') from dual; TO_CHAR(NEW_TIME(SYSDAT ----------------------- 24 dec 2006 06:51:26 pm

58) COALESCE:

This will give the first non-null date.     Syntax: coalesce (date1, date2, date3 … daten)     Ex:          SQL> select coalesce('12-jan-90','13-jan-99'), coalesce(null,'12-jan-90','23-mar- 98',null) from dual; COALESCE( COALESCE( -------------  ------------ 12-jan-90     12-jan-90

MISCELLANEOUS FUNCTIONS:

Uid User Vsize Rank Dense_rank

59) UID:

This will returns the integer value corresponding to the user currently logged in.      Ex:           SQL> select uid from dual;        UID ----------        319

60) USER:

This will returns the login’s user name.      Ex:            SQL> select user from dual; USER ---------------- SAKETH

61) VSIZE:

This will returns the number of bytes in the expression.      Ex:           SQL> select vsize(123), vsize('computer'), vsize('12-jan-90') from dual; VSIZE(123) VSIZE('COMPUTER') VSIZE('12-JAN-90') -------------  -----------------------  ----------------------          3                 8                  9

62) RANK:

This will give the non-sequential ranking.      Ex:           SQL> select rownum,sal from (select sal from emp order by sal desc);     ROWNUM    SAL     ---------- ----------          1       5000          2       3000          3       3000          4       2975 2850      SQL> select rank(2975) within group(order by sal desc) from emp; RANK(2975)WITHINGROUP(ORDERBYSALDESC) ---------------------------------------------------------                                     4

63) DENSE_RANK:

This will give the sequential ranking.      Ex:      SQL> select dense_rank(2975) within group(order by sal desc) from emp; DENSE_RANK(2975)WITHINGROUP(ORDERBYSALDESC) -----------------------------------------------------------------                                           3

CONVERSION FUNCTIONS:

Bin_to_num Chartorowid Rowidtochar To_number To_char To_date

64) BIN_TO_NUM

This will convert the binary value to its numerical equivalent.      Syntax: bin_to_num( binary_bits)      Ex:           SQL> select bin_to_num(1,1,0) from dual; BIN_TO_NUM(1,1,0) ------------------------                6 If all the bits are zero then it produces zero. If all the bits are null then it produces an error.

65) CHARTOROWID:

This will convert a character string to act like an internal oracle row identifier or rowid.

66) ROWIDTOCHAR:

This will convert an internal oracle row identifier or rowid to character string.

67) TO_NUMBER

This will convert a char or varchar to number.

68) TO_CHAR:

This will convert a number or date to character string.

69) TO_DATE:

This will convert a number, char or varchar to a date.

GROUP FUNCTIONS:

Group functions will be applied on all the rows but produces single output.                                                   70    Sum Avg Max Min Count

70) SUM:

This will give the sum of the values of the specified column.      Syntax: sum (column)      Ex: select sum(sal) from emp;   SUM(SAL)    ----------      38600 b) AVG      This will give the average of the values of the specified column.      Syntax: avg (column)      Ex: select avg(sal) from emp;    AVG(SAL)    ---------------    2757.14286

71) MAX:

This will give the maximum of the values of the specified column.      Syntax: max (column)      Ex: select max(sal) from emp;     MAX(SAL)    ----------      5000

       72) MIN:

This will give the minimum of the values of the specified column.      Syntax: min (column)      Ex: select min(sal) from emp;    MIN(SAL)    ----------      500

    73) COUNT:

This will give the count of the values of the specified column.      Syntax: count (column)      Ex: select count(sal),count(*) from emp; COUNT(SAL)   COUNT(*) --------------    ------------         14               14

CASE STATEMENT: 

It is also working as a switch case statement in C language. The only difference between if and switch case is in case of if condition we can check the condition or relation between more than one variable at a time but where as in switch statement we can check all the conditions on one variable. EX: Select sal,            Case sal                     When 500 then ‘low’                     When 5000 then ‘high’                     Else ‘medium’           End case           From emp;

DECODE:

 

It will checks value by value whatever we specify afterwards display result . EX:  select sal, decode(sal,1234,'LOW',2975,'MEDIUM',5000,'HIGH') from emp;

SYNTAX FOR RETRIEVE SPECIFIC ROWID:

SYN: SELECT * FROM <Table name> WHERE ROWID LIKE ‘% ROWIDVALUE’; We can’t retrieve row num through like command. We can retrieve only row id is possible. EX: select * from emp where rowid like '%C'; (sysdate, current_date these are also psudo columns)

OPERATORS IN SQL: 

Operator means special symbol which performs some specific operations on operators. 1. Arithmetic operator: +, -,*, /, MOD   2. Logical: AND, OR, NOT. 3. Relational/Comparison: >, < ,>= ,<= ,!=,<>,^=,= 4. Character: Concatenation: || 5. Assignment  : = Miscellaneous/Special operators: Between, not between, like, not like, in, not in, is null, is not null, exists, not exists, any, all, some, union, union all, intersect, minus.

Arithmetic Operators:

Operator	Description	Example
+ (unary)	Makes operand positive	SELECT +3 FROM DUAL;
- (unary)	Negates operand	SELECT -4 FROM DUAL;
/	Division (numbers and dates)	SELECT SAL / 10 FROM EMP;
*	Multiplication	SELECT SAL * 5 FROM EMP;
+	Addition (numbers and dates)	SELECT SAL + 200 FROM EMP;
-	Subtraction (numbers and dates)	SELECT SAL - 100 FROM EMP;

7. Logical Operators: 

Logical operators manipulate the results of conditions. 

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

8. RELATIONAL/Comparison Operators:

Comparison operators are used in conditions that compare one expression with another. The result of a comparison can be TRUE, FALSE, or UNKNOWN. 

Operator	Description	Example
=	Equality test.	SELECT ENAME "Employee" FROM EMP WHERE SAL = 1500;
!=, ^=, <>	Inequality test.	SELECT ENAME FROM EMP WHERE SAL ^= 5000;
>	Greater than test.	SELECT ENAME "Employee", JOB "Title" FROM EMP WHERE SAL > 3000;
<	Less than test.	SELECT * FROM PRICE WHERE MINPRICE < 30;
>=	Greater than or equal to test.	SELECT * FROM PRICE WHERE MINPRICE >= 20;
<=	Less than or equal to test.	SELECT ENAME FROM EMP WHERE SAL <= 1500;
IN	"Equivalent to any member of" test. Equivalent to "= ANY".	SELECT * FROM EMP WHERE ENAME IN ('SMITH', 'WARD');
ANY/ SOME	Compares a value to each value in a list or returned by a query. Must be preceded by =, !=, >, <, <=, or >=. Evaluates to FALSE if the query returns no rows.	SELECT * FROM DEPT WHERE LOC = SOME ('NEW YORK','DALLAS');
NOT IN	Equivalent to "!= ANY". Evaluates to FALSE if any member of the set is NULL.	SELECT * FROM DEPT WHERE LOC NOT IN ('NEW YORK', 'DALLAS');
ALL	Compares a value with every value in a list or returned by a query. Must be preceded by =, !=, >, <, <=, or >=. Evaluates to TRUE if the query returns no rows.	SELECT * FROM emp WHERE sal >= ALL (1400, 3000);
[NOT] BETWEEN x and y	[Not] greater than or equal to x and less than or equal to y.	SELECT ENAME, JOB FROM EMP WHERE SAL BETWEEN 3000 AND 5000;
EXISTS	TRUE if a sub-query returns at least one row.	SELECT * FROM EMP WHERE EXISTS (SELECT ENAME FROM EMP WHERE MGR IS NULL);
x [NOT] LIKE y [ESCAPE z]	TRUE if x does [not] match the pattern y. Within y, the character "%" matches any string of zero or more characters except null. The character "_" matches any single character. Any character following ESCAPE is interpretted litteraly, useful when y contains a percent (%) or underscore (_).	SELECT * FROM EMP WHERE ENAME LIKE '%E%';
IS [NOT] NULL	Tests for nulls. This is the only operator that should be used to test for nulls.	SELECT * FROM EMP WHERE COMM IS NOT NULL AND SAL > 1500;

9. 
   

10. Character Operators:

Character operators are used in expressions to manipulate character strings. 

Operator	Description	Example
||	Concatenates character strings	SELECT 'The Name of the employee is: ' || ENAME FROM EMP;

11. 
    

PATTREN MATCHING IN ORACLE:

The pattern-matching conditions compare character data. The LIKE condition allows you to use wildcards in the where clause of an SQL statement. This allows you to perform pattern matching. The LIKE condition can be used in any valid SQL statement - select, insert, update, or delete. The patterns that you can choose from are: % allows you to match any string of any length (including zero length) _ allows you to match on a single character Examples using % wildcard: The first example that we'll take a look at involves using % in the where clause of a select statement. We are going to try to find all of the suppliers whose name begins with 'Hew'. SELECT * FROM suppliers WHERE supplier_name like 'Hew%'; You can also using the wildcard multiple times within the same string. For example, SELECT * FROM suppliers WHERE supplier_name like '%bob%'; In this example, we are looking for all suppliers whose name contains the characters 'bob'. You could also use the LIKE condition to find suppliers whose name does not start with 'T'. For example, SELECT * FROM suppliers WHERE supplier_name not like 'T%'; By placing the not keyword in front of the LIKE condition, you are able to retrieve all suppliers whose name does not start with 'T'. Examples using _ wildcard: Next, let's explain how the _ wildcard works. Remember that the _ is looking for only one character. For example, SELECT * FROM suppliers WHERE supplier_name like 'Sm_th'; This SQL statement would return all suppliers whose name is 5 characters long, where the first two characters is 'Sm' and the last two characters is 'th'. For example, it could return suppliers whose name is 'Smith', 'Smyth', 'Smath', 'Smeth', etc. Here is another example, SELECT * FROM suppliers WHERE account_number like '12317_'; You might find that you are looking for an account number, but you only have 5 of the 6 digits. The example above, would retrieve potentially 10 records back (where the missing value could equal anything from 0 to 9). For example, it could return suppliers whose account numbers are: 123170 123171 123172 123173 123174 123175 123176 123177 123178 123179.

Update – Where Clause:

Q) Write a query that changes the employee’s salaries as 2500 whose empno is 7788. A) update emp set sal = 2500 where empno=7788; Q) write a query change the employee’s salaries as 5000 who are working under 10 th department. A) update emp set sal=5000 where deptno in(10);  (OR)  update emp set sal=5000 where deptno = 10; Q) write the query hike the employee’s salaries 30% who are working in the 10th Department. A) update emp set sal=sal+sal*0.3 where deptno in(20);  (OR)  update emp set sal=sal+sal*(30/100) where deptno in(20); Q) write the query increase the employee’s salaries 1000 whose names start with A. A) update emp set sal=sal+1000 where ename like 'A%'; Q) Write a query increases the employee’s salary 1000/- whose name is SCOTT working under 20th DEPT. A) update emp set sal=sal+1000 where ename='SCOTT' and deptno = 20; Q) Increase the employee salary by 1000 whose name starts with ‘S’ and ends with ‘T’ and third letter is ‘O’. A) update emp set sal=sal+1000 where ename like 'S%_O%T'; Q) Change deptno=30 whose emp sal between 1500 to 3000 A) update emp set deptno=30 where sal between 1500 and 3000;  (OR)  update emp set deptno=30 where sal >=1500 and sal<=3000 Q) Change job as clerk whose job is manager are joined before 1982.  Update emp set job = 'CLERK' where job = 'MANAGER' and hiredate< '01-JAN-1982'; Q) Write a query to increase the employee’s salaries 1000 who are having more than 10 years. A) update emp set sal = sal+1000 where (sysdate-hiredate)/365>10; Q) Write a query change the employee’s salaries as 7000 who are having commission. A) update emp set sal=sal+7000 where comm>=0; Q) Write a query change the employee’s salary 10000 who are not working under anybody. A) update emp set sal = sal+10000 where mgr is null; Q) Write a query change employee name as Srinivas and Salary 9000 whose empno=7566. A) update emp set ename = 'SRINIVAS' and sal = 9000 where empno =7566; Q) Write a query change the employees salary 5000 whose name contain more than 6 Character. A) update emp set sal = 5000 where length(ename)>6; Q) Write a query change the employees salaries as 6000 who is less than king. A) update emp set sal = 6000 where ename < 'KING'; Q) Change employee salary as 5799 whose name contains to As. A) update emp set sal=5799 where ename like '%A%'; Q) Change employee salary 2344 where employee name ending with K. A) update emp set sal = 2344 where ename like '%K'; Q) Increase employee salaries as 1000 where employee name last but one letter end with A. A) update emp set sal = sal+1000 where ename like '_A%'; Q) Increase the the employee salaries as 1000 where employee name’s second letter starts with A. A) update emp set sal = sal+1000 where ename like 'A%_'; Q) Change the employee salary 4500 where who’s name contains two As A) update emp set sal = 4500 where ename like '%A%A%'; Q) Change employee salary 4000 whose hiredate is 23rd MAY 87. A) update emp set sal =4000 where hiredate = ’23-MAY-87’ Q) Change the employee salary as 6000 whose joining month in start with M. A) Update emp set sal=6000 where hiredate like ‘___M%’; Q) Change employee salary 1234 where hiredate is in the month MAY or which starts with M and ends with Y A) update emp set sal=1234 where hiredate like '___M%_Y%'; Q)Change the employee salary 4567 where hiredate is 23rd. A) update emp set sal=4567 where hiredate like '2%3%';   Delete with where clause: Syn: DELETE FROM <Table name> where <condition> EX: DELETE FROM EMP WHERE EMPNO=7788 Q) Write a query delete employee details who are working under 10th DEPT. A) delete from emp where deptno in (10); Q) Delete emp details whose number and job ends with n? A) delete emp where ename like '%N' and job like '%N'; Q) Write a query delete the employee details who are looking under 10th and 20th DEPT. A) delete from emp where deptno =10 or deptno =20; NOTE: Here we didn’t use and because an employee doesn’t work in two departments. So we can write this query two ways. delete emp where deptno in(10,20); Q) Write a query delete the employee who are joined in the month april? A) delete from emp where hiredate like '%APR%';  (OR)  delete from emp where hiredate like '___A%P%'; Q) Write a query delete the employee details who are not working under 20th ? A) delete from emp where deptnonot in (20);  (OR)  delete from emp where deptno <> 20; Q) write a query delete emp details whose annual salary is more than 25000 A) delete from emp where (sal*12)>2000; Q) Write a query delete employee details whose name contains letters only 4? A) delete from emp where length(ename) = 4;  (OR)  delete from emp where ename like '____'; Q) Write a query to delete the emp details whose salary not in range of 2000 and 3000 A) delete from emp where sal not between 20000 and 30000; Q) Write a query delete emp details whose name contains letter A? A) delete from emp where ename like '%I'; Q) Write the emp details who are having commission and commission more than 1000? A) delete from emp where comm is not null and comm>1000; Select with Where clause: Select with Where clause: Seelction: Retrieve the data based on some condition is known as selection. Syn: SELECT * FROM EMP WHERE <Condition> Q) Write a query display the employee details whose salary is less than 3000 and working under dept 10? A) select * from emp where sal<=3000 and deptno in(10); Q) write the query display the employee details whose empno and mgr ends with no 9? A) select * from emp where empno like '%9' and mgr like '%9'; Q) Write a query display details whose name and job contains only 5 letters? A) select * from emp where length(ename)= 5 and length(job)=5;  (OR)  select * from emp where ename like '_______' and job like '_____'; Q) Write a query display employee details whose total salary is more than 2500. A) select* from emp where (sal+comm)>2000; Q) write a query display the employee details whose sal and comm. Ends with zero? A) select * from emp where sal like '%0' and comm like '%0'; Q) write a query display the employee details who are having more than 30 years exp? A) select * from emp where (sysdate-hiredate)/365>30 and deptno in(10) and sal>3000; Q) Write a query to display the employee details who doesn’t have commission and their names contains A and department no is 20. A) select * from emp where comm is null and ename like '%A%' and deptno in(20);

ALIASES: 

Alias is duplicate name or an alternative name for the original column name or table name or expression name. Whenever we need to submit meaningful or understanding reports then we need to use alias names.  We can provide the alias names in three levels i) Column level ii) Table level iii) Expression level.

1. Column level Alias: 

Providing the alias name for the columns is called column level alias. Syn: SELECT COL1 AS “ALIAS NAME”, COL2 AS “ ALIAS NAME” ,---------,COLUMN NAME N AS “ALIAS NAME” FROM <Table name>; EX: SELECT EID AS “EMPLOYEEID”, ENAME AS “EMPLOYEE NAME” FROM EMP; In the above example the keyword as is an optional keyword. Syn: SELECT COL1 “ALIAS NAME”, COL2 “ALIAS NAME”, -------,COLN “ALIAS NAME” FROM <Table name>; EX: SELLECT EID “EMPLOYEEID”, ENAME “ EMPLOYEE NAME” FROM EMP; In the above example we no need to use double quotation. Syn: SELECT COL1 ALIASNAME, COL2 ALIASNAME,--------,COLN ALIASNAME FROM <Table name>; EX: SELECT EID EMPLOYEEID, ENAME EMPLOYEENAME FROM EMP;

ii) Expression Level alias:

Providing the alias names for expressions is known as expression level alias. EX: SELECT EID EMPLOYEEID, ENAME EMPLOYEENAME, SAL SALARY, SAL*12 ANNUALSALARY FROM EMP; EX: SELECT EID EMPLOYEEID, ENAME EMPLOYEENAME, SAL ANNUALSALARY, SAL*12 ANNUALSALARY FROM EMP WHERE ANNUALSALARY>150000; O/P: “ANNUAL SALARY” in valid identifier because Alias names are not identifiers. Identifier means column name. Note: The above example the fallowing error message- “Annual salary invalid identifier”. So we can’t check the conditions of Alias names. EX: SELECT EID EMPLOYEEID, ENAME EMPLOYEENAME, SAL SALARY, SAL*12 ANNUALSALARY FROM EMP WHERE SAL*12>150000; EX: SELECT EMP.*, SAL*12 ANNUALSALARY FROM EMP WHERE SAL*12>150000;

JOINS:

The purpose of a join is to combine the data across tables. A join is actually performed by the where clause which combines the specified rows of tables. If a join involves in more than two tables then oracle joins first two tables based on the joins condition and then compares the result with the next table and so on.

JOIN TYPES:

Equi join Non-equi join Self join Natural join Cross join Outer join Left outer Right outer Full outer Inner join Using clause On clause

Assume that we have the following tables.

SQL> select * from dept;

DEPTNO DNAME      LOC ------ ---------- ---------- 10 mkt        hyd 20 fin        bang 30 hr         bombay SQL> select * from emp; EMPNO   ENAME      JOB       MGR     DEPTNO ---------- ---------- ---------- ---------- ---------- 111         saketh     analyst           444         10 222         sudha     clerk                333         20 333         jagan      manager         111         10 444         madhu    engineer         222         40

EQUI JOIN:

A join which contains an ‘=’ operator in the joins condition. Ex: SQL> select empno,ename,job,dname,loc from emp e,dept d where e.deptno=d.deptno; EMPNO     ENAME      JOB    DNAME      LOC ---------- ---------- ---------- ---------- ---------- 111       saketh    analyst    mkt        hyd 333       jagan      manager  mkt        hyd 222       sudha      clerk        fin        bang

USING CLAUSE:

SQL> select empno,ename,job ,dname,loc from emp e join dept d using(deptno); EMPNO     ENAME      JOB    DNAME      LOC ---------- ---------- ---------- ---------- ---------- 111       saketh    analyst    mkt        hyd 333       jagan      manager  mkt        hyd 222       sudha      clerk        fin        bang

ON CLAUSE:

SQL>  select empno,ename,job,dname,loc from emp e join dept d on(e.deptno=d.deptno); EMPNO     ENAME      JOB    DNAME      LOC ---------- ---------- ---------- ---------- ---------- 111       saketh    analyst    mkt        hyd 333       jagan      manager  mkt        hyd 222       sudha      clerk        fin        bang

NON-EQUI JOIN:

A join which contains an operator other than ‘=’ in the joins condition. Ex: SQL> select empno,ename,job,dname,loc from emp e,dept d where e.deptno >d.deptno; EMPNO     ENAME    JOB      DNAME      LOC ---------- ---------- ---------- ---------- ---------- 222    sudha      clerk          mkt        hyd 444    madhu     engineer   mkt        hyd 444    madhu     engineer   fin          bang 444    madhu     engineer   hr           bombay

SELF JOIN:

Joining the table itself is called self join. Ex: SQL> select e1.empno,e2.ename,e1.job,e2.deptno from emp e1,emp e2 where e1.empno=e2.mgr; EMPNO     ENAME    JOB      DEPTNO ---------- ---------- ---------- ---------- 111 jagan      analyst         10 222 madhu      clerk           40 333 sudha      manager      20 444 saketh     engineer      10

NATURAL JOIN:

Natural join compares all the common columns. Ex:SQL> select empno,ename,job,dname,loc from emp natural join dept; EMPNO   ENAME      JOB      DNAME    LOC ---------- ---------- ---------- ---------- ---------- 111 saketh     analyst     mkt        hyd 333 jagan      manager   mkt        hyd 222 sudha      clerk         fin          bang

CROSS JOIN:

This will gives the cross product. Ex: SQL> select empno,ename,job,dname,loc from emp cross join dept; EMPNO  ENAME    JOB        DNAME      LOC ---------- ---------- ---------- ---------- ---------- 111     saketh   analyst      mkt        hyd 222     sudha    clerk          mkt        hyd 333     jagan     manager   mkt        hyd 444     madhu   engineer   mkt        hyd 111     saketh   analyst      fin          bang 222     sudha    clerk          fin          bang 333     jagan     manager   fin          bang 444     madhu   engineer   fin          bang 111     saketh   analyst      hr           bombay 222     sudha    clerk          hr           bombay 333     jagan     manager   hr           bombay 444     madhu   engineer   hr           bombay

OUTER JOIN:

Outer join gives the non-matching records along with matching records. Outer join extend the result of equi-join. Outer join Retrieves the rows returned by equi-Join as well as the rows that do not match any row from outer table. The symbol(+) represents outer join. (+) symbol is applied to that table in which the records are missing for a value in the other table. The operator as the effect of creating one or more null rows,to which one or more rows from the non-deficient table. (+) symbol cannot be applied to both sides of the condition at a time. Example: Select e.ename,d.dname,d.deptno from dept d,emp e where e.deptno=d.deptno(+);

LEFT OUTER JOIN:

This will display the all matching records and the records which are in left hand side table those that are not in right hand side table. Ex: SQL> select empno,ename,job,dname,loc from emp e left outer join dept d on(e.deptno=d.deptno); -Or- SQL> select empno,ename,job,dname,loc from emp e,dept d where e.deptno=d.deptno(+); EMPNO     ENAME   JOB       DNAME      LOC ---------- ---------- ---------- ---------- ---------- 111 saketh    analyst       mkt        hyd 333 jagan      manager    mkt        hyd 222 sudha     clerk           fin          bang 444 madhu    engineer

RIGHT OUTER JOIN:

          

This will display the all matching records and the records which are in right hand side table those that are not in left hand side table. Ex: SQL> select empno,ename,job,dname,loc from emp e right outer join dept d on(e.deptno=d.deptno); -Or- SQL> select empno,ename,job,dname,loc from emp e,dept d where e.deptno(+) = d.deptno; EMPNO    ENAME     JOB      DNAME      LOC ---------- ---------- ---------- ---------- ---------- 111 saketh     analyst      mkt        hyd 333 jagan       manager   mkt        hyd 222 sudha      clerk          fin          bang hr           bombay

FULL OUTER JOIN:

This will display the all matching records and the non-matching records from both tables. Ex: SQL> select empno,ename,job,dname,loc from emp e full outer join dept d on(e.deptno=d.deptno); EMPNO   ENAME    JOB        DNAME      LOC ---------- ---------- ---------- ---------- ---------- 333     jagan     manager    mkt        hyd 111     saketh   analyst       mkt        hyd 222     sudha    clerk           fin        bang 444     madhu   engineer hr        bombay

INNER JOIN:

This will display all the records that have matched. Ex: SQL> select empno,ename,job,dname,loc from emp inner join dept using(deptno); EMPNO     ENAME   JOB        DNAME    LOC ---------- ---------- ---------- ---------- ---------- 111 saketh     analyst      mkt       hyd 333 jagan       manager   mkt       hyd 222 sudha      clerk          fin         bang

CONSTRAINTS: 

Constraint is the mechanism which is used to restrict the invalid data which is enters by the end user by implementing business rules we can apply the constraints on two situation on the table. 1.      During the creation of the table. 2.      After creation of the table.

Why Should  Use Constraints? 

You can use constraints to do the following:  Enforce rules on the data in a table whenever a row is updated, inserted, or deleted from that table Prevent the deletion of a table if there are dependencies from other tables Constraints have two names 1.      System defined Constraints names 2.      User defined constraints names

1.      System defined Constraint names:  A constraint name which is defined by the system internally those constraints can be called as system defined constraint names. System defined constraint names are always in the format of SYS_CN. Here n represents any no. 2.      User defined Constraints: The constraint name which is defined by the user manually those means those names can be called as user defined constraint names.

We can apply constraints in two levels:

1.      Column level constraints 2.      Table level constraints 1.      Column level Constraints: Applying the constraints after defining the column immediately those constraints can be called as column level constraints. 2.      Table level constraints: Applying the constraints after defining all the columns in the table or at the end of the table those constraints can be called as table level constraints.

Constraints are classified into six types:

1.      Unique  2.      Not null 3.      Check  4.      Primary key 5.      Foreign key 6.      Default

1. Unique constraint: 

1.      This constraint doesn’t allow us to enter duplicate values of a particular column in the table. 2.      We can apply constraint more than one column in the table. 3.      We can apply this constraint more than one column in the same table. 4.      This constraint allows us to enter null values. 1.      Unique key: Unique key is a constraint. It never allows duplicate values. But it allows null values. We can give constraint in two levels  1.      Table level constraints. 2.      Column level constraints. Example for Column level constraints:  create table work1 (empno number(10) unique, ename varchar(10), sal number(10)); Example for Table level constraints: create table work2(empno number(10),ename varchar(10), sal number(10), unique(empno));

2. Not Null: 

Not null is a constraint which never allows null values but allows duplicates. We can apply this constraint in column level only. Column level Ex: create table work3 (empno number(10) not null, ename varchar(10), sal number(10)); Table level Ex: create table work4 (empno number(10) ,ename varchar(10),sal number(10) not null);

3.      Primary key:  

It never allows duplicates and null values. It is a combination of Unique and Not Null. Column level: create  table work6 (empno number(10) primary key,ename varchar(10),sal number(10)); Table level: create table work7 (empno number(10),ename varchar(10),sal number(10), primary key(empno));

4.      Check: 

This is used to check particular constraint. EX: create table work8 (empno number(10),ename varchar(10),sal number(10) check (sal=3000);

5.      Foreign key Constraint: 

Using this constraint we can maintain the relation between the tables with in the database. This constraint can also be called as referential integrity. If there is any dependencies on the parent table that is dept then we are not suppose to delete the parent record from the parent table. If there is no parent values in the parent table. We can’t enter the values within the child table that is employee table. Super keys and Candidate keys: Definition: A super key is a combination of attributes that can be uniquely used to identify a database record. A table might have many super keys. Candidate keys are a special subset of super keys that do not have any extraneous information in them. Examples: Imagine a table with the fields <Name>, <Age>, <SSN> and <Phone Extension>. This table has many possible super keys. Three of these are <SSN>, <Phone Extension, Name> and <SSN, Name>. Of those listed, only <SSN> is a candidate key, as the others contain information not necessary to uniquely identify records. EX:  create table child1(no number(10),sal number(10),constraint bhargav foreign key(sal) references         Child(no)); NOTE: 1. We should give the foreign key relationship to another table which has already consist primary key. 2.  We can give the foreign key relationship to parent table’s primary key initiated column only. ON DELETE CASCADE: This option is used to delete the parent “record” from the parent table even though there are some dependencies. NOTE: It is possible to give only at the time of creating foreign key constraint. EX: create table baju1 (no number(10), foreign key(no) references baju(no) on delete cascade);

6. Default: 

This constraint is used to give default value into a particular column in the table. EX: create table baju2 (no number(10) default 111);

Example for applying the constraint after creating the table:

alter table baju1 modify(no number(10) unique); Note: Here we can modify previous table constraints only.

Syntax to see the list of the Constraints:

select * from user_cosntraints; Desc user_constraints;

Syntax to see the list of constraints on a particular table:

select constraint_name,constraint_type,status from user_constraints where table_name='BAJU1'; CONSTRAINT_NAME                C STATUS ------------------------------                 - -------- SYS_C005281                    R ENABLED SYS_C005282                    U ENABLED

Create User Defined Constraint names:

create table iron(no number(10) constraint rohit primary key,name varchar(10)); select Constraint_name,Constraint_type,status from user_constraints where table_name='IRON'; CONSTRAINT_NAME                C                STATUS ------------------------------                                    - -------- ROHIT                                            P                     ENABLED NOTE: It shows C as constraint for check and not null but first priority for not null.

Syntax to drop/enable/disable the Constraint:

SYN: ALTER TABLE<Table name> DISABLE/ENABLE/DROP  CONSTRAINT CONSTRAINT_NAME; EX: alter table iron disable constraint rohit; EX:  alter table iron enable  constraint rohit; EX: alter table iron drop constraint rohit;

Composite Primary key:

 

Composite primary key means share primary key properties to more than one column at a time in the same table. EX:  create table vinod(no number(10),name varchar(10),sal number(10),constraint pse primary key(no,name,sal)); NOTE: Here we can insert same values into columns, different values but do not give null value. But we can give duplicate values.

To copy a table from one table to another table:

SYN: CREATE TABLE<Target table name> AS SELECT * FROM <Source Table name>; EX: create table ranga as select * from vinod;

SUB QUERIES/NESTED QUERIES: 

A query contains another query is known as nested query or sub query. We can write query into a query upto 256 queries. In between the outer query and inner query, inner query executed first then it will execute the outer query . Outer query is always executed descending on inner query result. Sub queries are classified into two types. i)                    Single row sub queries. ii)                  Multiple row sub queries.

i)      Single row sub query:  a)                    If the inner query returns only one value then those queries can be called as single row sub query are equal to >,<,>=,<=,!=.   b)                  These operators can be called as single row sub queries. c)                Inner query returns only one value in two classes. Case (i): If we are using any group or aggregate functions. Case (ii): If we are using any group or aggregate functions any group or aggregate functions. ii)Multiple row sub queries:           If the inner query returns more than one value then those queries can be called as multiple row sub querites.           The operators which are using in multiple row sub queries are IN, ANY, ALL, SOME. Note:          Here ANY and SOME both are same.          These two operators can be called as multiple row operators.

Q) Write a query display the employee details whose salary is less than 7788. A) select * from emp where sal<(select sal from emp where empno=7788); Q) Write a query display the employee details whose salary is less than 7788 working under 30th dept. A) select * from emp where sal<(select sal from emp where empno=7788) and deptno=30; Q) Write a query display the employee details whose salary is less than maximum salary. A) select * from emp where sal<(select max(sal) from emp); Q)  Write a query display the employee details whose salary is less than maximum salary and who are having the commission.  A)  select * from emp where sal<(select max(sal) from emp) and comm is not null; Q) Write a query display the employee details whose salary is greater than maximum salary. A) select * from emp where sal > (select max(sal) from emp); Q) Write a query display the employee details who are working under BLAKE. A)  select * from emp where mgr=(select empno from emp where ename='BLAKE'); Q) Write a query display the employee details who are working sales dept. A) select * from emp where deptno=(select deptno from dept where dname='SALES'); Q) Write a query display maximum salary details who are working under 10th dept. A) select max(sal) from emp where sal=(select max(sal) from emp where deptno=10) Q) Display the employee details whose salary is less than maximum total salary in descending order ename wise. A) select * from emp where sal<(select max(sal+nvl(comm,0)) from emp) order by ename desc; Q)  write a query display maximum total salary who were join in the year 81. A)  select * from emp where sal=(select max(sal+nvl(comm,0))from emp where hiredate like '%81'); Q) Write a query count the no of employees working in the ‘CHICAGO’ A) select count(*) from emp where deptno=(select deptno from dept where loc='CHICAGO'); Q) Find out total salary of maximum salary of employee working in the Sales Department. A)  select sal+nvl(comm,0) from emp where sal=(select max(sal) from emp where  deptno=(select deptno from dept where dname='SALES')); Q) Display the employee details whose experience is more than minimum experience employee. A)  select * from emp where (sysdate-hiredate)/365 <(select min(sysdate-hiredate)/365 from emp) Q) Display the employee details working under the newyork and their name contains the letter ‘A’ A) select * from emp where deptno=(select deptno from dept  where loc='NEW YORK') and ename like ' %A%'; Q)  Write a query display the third maximum salary of employee. A) select * from emp where sal=(select max(sal) from emp where sal <(select max(sal)  from emp where sal<(select max(sal) from emp))); Q) Write a query display the maximum salary of employee name whose name ends with n. A) select ename from emp where sal=(select max(sal) from emp where ename like '%N'); Q) select emp.*,sal+nvl(comm,0) from emp where deptno=(select deptno from dept where dname='RESEARCH’; Q) Write a query display the employee details who are working under sales and accounting department. A) select * from emp where deptno in(select deptno from dept where dname in('ACCOUNTING','SALES')); Q) Write a query display the maximum salary employee details working under sales and accounting department. A)  select * from emp where sal=(select max(sal) from emp where deptno  in(select deptno from dept where dname in('SALES','ACCOUNTING'))); Q) Write a query count the no of employees working in BOASTON and NEWYORK. A)  select count(*) from emp where deptno in(select deptno from dept where loc in('BOASTON','NEWYORK')); Q) Write a query display the employee details whose salary is less than any employee salary working under 20th dept. A) select * from emp where sal<any(select sal from emp where deptno in 20);                                               (OR) A)    select * from emp where sal<some(select sal from emp where deptno in 20) Note: ANY and SOME are same. Q) Write a query display the employee details who are working under BLAKE and BHARGAV. A)  select * from emp where mgr in(select empno from emp where ename ='BLAKE' or ename = 'VISHAL'); Q) write a query display the maximum total salary of employees who are working under ‘BLAKE’ and ‘VINAY’. A)  select * from emp where (sal+nvl(comm,0))=(select max(sal+nvl(comm,0)) from emp where mgr in(select empno from empwhere ename='BLAKE' or ename ='VISHAL')); Q) Write a query display the employee details whose salary is greater than all employees’ salaries who are working under 20th dept. A) select * from emp where sal>all(select sal from emp where deptno=20) Q) Write a query display the employee details who are comes under grade1 and grade2. A) select emp.*,grade from  emp,salgrade where sal>=losal and sal<=hisal and grade in  (select grade from salgrade where grade in (1,2)); Q) Display the maximum salary employee details working under 10th and 20th dept. A) select * from emp where sal in(select min(sal) from emp where deptno in(20,30)) Q) Write a query display the employee details of the department table available under scott user. A) select * from emp where exists(select * from tab where tname='DEPT'); Q) Write a query display the employee details if VIBHAV table not available under SCOTT user. A) select * from emp where not exists(select * from tab where tname='VIBHAV');

CORRELATED SUB QURIES:

Correlated sub quires are evaluated once per row processed by the Parent statement. Correlated sub quire is signaled by a column name, a table name or table alias in WHERE clause. Correlated sub query is used to answer multi-part questions whose result depends on value of each row of parent query. Ex: Select * from emp e where 5>(Select count (distinct sal) from emp where e.sal < sal) Order by sal desc;

SET OPERATORS:

 

There are 4 set operators in SQL those are UNION, UNION ALL, INTERSECT and MINUS.

UNION:  This operator returns all the values from all the tables excluding duplicate values. EX: select deptno from emp union select deptno from dept; UNION ALL:  This operator returns all the values from all the tables including duplicate values. EX: select deptno from emp union all select deptno from dept; INTERSECT:  EX: select deptno from emp intersect select  deptno from dept; MINUS:  EX: select deptno from emp minus select deptno from dept;

Q) Write a query display the last three records in the table. A)  select * from emp where rownum <=(select count(*) from emp) minus select * from emp where rownum<=(select count(*) from emp)-3; Q) To Display the first three records. A) select * from emp where rownum<=3; Q) To display the fifth record. A) select * from emp where rownum=5; Q) to display the records dynamically. A)  select * from emp where rownum<=&rownum minus select * from emp where rownum<=&rownum;

SYNONYM: 

Synonym is a database object.  It is a shortcut name for original base table and it contains the physical representation of data. i.   If we create physical representation of data synonym will be created on the entire table, and it is not possible to create a synonym based on the partial table and subset of a table. ii.   We can’t create a synonym more than one table. iii.  If we perform any DML operation on synonym it automatically shows effect on base table also. Like that if make any modification on base table it automatically modify the synonym. iv.   If we drop the base table the corresponding synonyms won’t be dropped and those become invalid. v.   Synonyms will become invalid in two cases. a.       When we drop the base table. b.      When we change the table name. On invalid synonym It is not possible to perform any type operations. We can make the invalid synonym as valid synonym we can create a synonym with a base table without a base table. vi.    On invalid synonym if the user trying to perform the operations then we will get the following error message. Synonym translation is no longer valid. vii.   Synonyms are classified into two types. a.                           a. Private synonyms. b.                           b. Public synonyms.

a.       Private Synonyms: Private synonyms are synonyms those are confined to only one particular user and these synonyms are access by one particular user. b.      Public synonyms: Public synonyms are synonyms those are accessed by many users at a time those synonyms can be called as public synonyms. Public synonyms always created by DBA. SYN: create synonym <synonym name> for < table name>;

Syntax for creating a synonym: 

CREATE SYNONYM<synonym name> FOR <table name>; EX: create synonym v for EMP;

Public synonyms: SYN: create public synonym <synonym name> for <table name>; EX: create public synonym ps for po; Syntax to drop particular synonym: Syn: DROP SYNONYM <Synonym name>; EX: drop synonym B; NOTE:  we can’t modify the structure of the synonym.

VIEWS: 

i.        View is a database object which contains logical representation of data. ii.      By using views we can hide some column data from the user. iii.    View does not occupy the physical memory. iv.    We can create a view based on entire table or partial table or subset of table. v.      If we perform any type of operations view the same operations automatically affected to the corresponding base table and vice – versa. vi.    We can create a view based on another view upto maximum 32 views. vii. If we drop the base table the corresponding views will not be dropped and those become invalid. viii. On invalid views we can’t perform any type of operations. ix.    Views will become invalid in three cases. 1.      When we drop the base table. 2.      When we change the table name. 3.      When we modify the structure (Rename) of the base table. x.      We can make an invalid view as a valid view. xi.    We can create a view based on one table and also based on the more than one table. xii.  Views are divided into two types. a.       Simple views b.      Complex Views. a.      Simple Views: Creating the view based only one table is known as simple view. b.      Complex View: Creating the view based on more than one table is called Complex view. NOTE: We can create a view based on the synonym and also we can create a synonym based on the view. Syntax to create view: CREATE VIEW <View Name> as SELECT * FROM <Table Name>; EX: create view bn as select * from lak;

Create a view base on Partial table: 

SYN: Create View <View name> a s select col1, col2 ,  -------- ,col n  from <Table Name>; EX: create view see as select ename, sal from emp1; Read Only View: It is specific type of the view. So we can’t perform any type of operations of DML operations. EX: create view kpp as select *from emp with read only; Syntax to create a view without a base table: SYN: create force view <View Name> as select * from <Table name>; EX:  create force view fv as select * from aaa; Warning: View created with compilation errors. NOTE: If we create a synonym for one table then we can’t create view for it and also same thing applicable for reverse.

Complex Views: 

Creating the views based on more than one table is known as complex views. Syn:  CREATE VIEWE<View Name> AS SELECT * FROM <tab1>,<tab2>-----,<tab n>; EX: create view cv as select * from emp,dept where emp.deptno=dept.deptno; NOTE: Here we will get on error message. According to RDBMS rules no database object should not contain duplicate columns. The above example returns the following error message. NOTE: on complex views we can’t perform any type of DML operations. EX: create view cv as select * from emp natural join dept;

FORCE VIEW:

This view is created for the table which is not there in the database. We can create it as create force view as select statement......; CREATE OR REPLACE force VIEW forceview AS SELECT * FROM table1 Warning: VIEW created WITH compilation errors.

MATERIALIZED VIEWS: 

These are special type of views the main difference between normal view and materialized view is if we drop the base table it won’t invalid. Before creating the materialized view user need to remember two points. i)                    He need to take permission from DBA. ii)                  On which table we need to create the materialized view that table should contain primary key Constraint. iii)                Materialized view stores data. iv)                We can’t perform DML Operations on Materialized view. EX: create materialized view dv as select * from emp; Syntax to copy a table to another table with specific columns only: CREATE TABLE TABLE NAME<Target table name> as select col1,col2,-------,coln from <source table name>; EX: create table ranga1 as select no,name from vinod;

COPYING A TABLE FROM ONE TABLE TO ANOTHER TABLE WITHOUT DATA:

CREATE TABLE<Target table name> as select * from <Source table name> where <false condition>; EX: create table ranga3 as  select * from vinod where 1=2; Name                                      Null?                      Type  ----------------------------------------- -------- ----------------------------  NO                                                                   NUMBER(10)  NAME                                                             VARCHAR2(10)  SAL                                                                 NUMBER(10)

Copying a table from one table to another table without data with specific columns:

SYN: CREATE TABLE<Target table name> as select col1,col2,-----------,coln from <Source table name> where <false condition>; EX: create table ranga4 as select no,name from vinod where 1>2; Insert  the data from one table to another table: SYN:  insert into elora1  select * from elora; NOTE: We can insert values from one table to another table the table contains same structure and same data types. Otherwise we can’t insert.

SEQUENCES:

Sequence is a database object which automatically generates unique numeric values on a particular column in the table. Sequence default values start with 1 and it is incremented by +1 up to hard disk capacity. Sequence always generates numeric values we can apply the same sequence on more than one column in sequences on different columns in the same table. Sequence never generates character values. Sequence generates positive values ascending order and descending order. Sequence generates negative values ascending order and descending order. Sequence mechanism we can’t apply an alpha numeric columns.

SYNTAX FOR SEQUENCE:

PURPOSE: To create a sequence. A sequence is a database object from which multiple users may generate unique integers. You can use sequences to automatically generate primary key values.  SYNTAX:  CREATE SEQUENCE sequence_name      [INCREMENT BY integer]      [START WITH integer]      [MAXVALUE integer | NOMAXVALUE]      [MINVALUE integer | NOMINVALUE]      [CYCLE | NOCYCLE]      [CACHE integer | NOCACHE]      [ORDER | NOORDER];

NOTE: 

Schema: Is the schema to contain the sequence. If you omit schema, Oracle creates the sequence in your own schema.

Sequence: Is the name of the sequence to be created.

INCREMENT BY: Specifies the interval between sequence numbers. This value can be any positive or negative Oracle integer, but it cannot be 0. If this value is negative, then the sequence descends. If the increment is positive, then the sequence ascends. If you omit this clause, the interval defaults to 1.

MINVALUE: Specifies the sequence's minimum value.  NOMINVALUE: Specifies a minimum value of 1 for an ascending sequence or -10 for a descending sequence. The default is NOMINVALUE

. 

MAXVALUE: Specifies the maximum value the sequence can generate.

NOMAXVALUE: Specifies a maximum value of 10 for a descending sequence. The default is NOMAXVALUE.

START WITH: Specifies the first sequence number to be generated. You can use this option to start an ascending sequence at a value greater than its minimum or to start a descending sequence at a value less than its maximum. For ascending sequences, the default value is the sequence's minimum value. For descending sequences, the default value is the sequence's maximum value.

CYCLE: Specifies that the sequence continues to generate values after reaching either its maximum or minimum value. After an ascending sequence reaches its maximum value, it generates its minimum value. After a descending sequence reaches its minimum, it generates its maximum.

NOCYCLE: Specifies that the sequence cannot generate more values after reaching its maximum or minimum value. The default is NOCYCLE.

CACHE: Specifies how many values of the sequence Oracle preallocates and keeps in memory for faster access. The minimum value for this parameter is 2. For sequences that cycle, this value must be less than the number of values in the cycle.

NOCACHE: Specifies that values of the sequence are not pre allocated.  If you omit both the CACHE parameter and the NOCACHE option, Oracle caches 20 sequence numbers by default. However, if you are using Oracle with the Parallel Server option in parallel mode and you specify the ORDER option, sequence values are never cached, regardless of whether you specify the CACHE parameter or the NOCACHE option.

ORDER: Guarantees that sequence numbers are generated in order of request. You may want to use this option if you are using the sequence numbers as timestamps. Guaranteeing order is usually not important for sequences used to generate primary keys.

NOORDER: Does not guarantee sequence numbers are generated in order of request. If you omit both the ORDER and NOORDER options, Oracle chooses NOORDER by default. Note that the ORDER option is only necessary to guarantee ordered generation if you are using Oracle with the Parallel Server option in parallel mode. If you are using exclusive mode, sequence numbers are always generated in order.

First we should create sequence.

SYN: create sequence <sequence name>

EX: create sequence s1;

Syntax to applying the sequence on a particular column in the table

SYN: insert into<table name> values ( sequence.nextval,’&columnname’,’&columnname’);

EX: insert into emp1 values (s1.nextval,’&ename’,&sal);

In the above example nextval represents psudo column and it has two advantages.

1.      It initiates the sequence number and also it gives the next value of the sequence.

Syntax to know about the current value of the sequence:

Select sequencename.currval from dual; EX: select s1.currval from dual; In the above example currval represents psudo column and it gives the current value of the sequence.

To apply the syntax on other table:

insert into emp2 values(s1.nextval,'&ename','&sal'); Creating a sequence with starting ending incrimination value: SYN: create sequence <sequence name>             minvalue<integer>             maxvalue<integer>       Incremented by integer EX: create sequence s2  minvalue 101  maxvalue 110  incremented by 1; Note: If sequence reached MAX value then we will get error message.

Syntax to alter the sequence:

SYN: Alter sequence s1           Maxvalue integer           Increment by integer;  (Here it can’t possible modify min value) EX:  alter sequence s1  maxvalue 120  increment by 2;

Example for applying the sequence more than one column on same table:

insert into emp2 values(s1.nextval,'&ename',s1.nextval); Example for creating sequence with descending order  create sequence s3   minvalue 1   maxvalue 10   increment by -1; NOTE: Here it takes first maximum value next it takes minimum value.

Example for creating the sequences with the negative values:

create sequence s4   minvalue -5  maxvalue -1  increment by1

Example for creating the sequence with the negative values with positive increments:

create sequence s5  minvalue -5  maxvalue -1 increment by -1; Example for applying for the two different sequences on two different columns in the same table. insert into emp1 values(s4.nextval,'&ename',s5.nextval);

How to see the list of all sequences

Data dictionary tables are user_tab_columns, recyclebin, user_sequences. EX: select * from user_sequences;

Creating the sequence with the cycle option:

EX: create sequence s8  minvalue 10  maxvalue 15  cycle nocache; OUTPUT:  select * from rose;    NO NAME              SAL ----- ---------- ----------     1 siva                1     2 bhargav             2     3 reddy               3    11 rohit              11    45 ravi               45    44 sonal              44    43 vinamra            43    10 vishal             10    12 narad              12    13 saradh             13    14 bharath            14    NO NAME              SAL ----- ---------- ----------    15 suneel             15    10 isha               10    11 chawla             11 NOTE:  If we give cycle no cache then we will get sequences on columns with cycle. That means if minvalue 10 and maxvalue is 15 then after entering five records it is not possible to enter but here we need not bother about limit. It will automatically take from min value.

Syntax to drop sequence:

Syn: Drop sequence sequence_name; EX: drop sequence s2;

Q) Why we should not drop Table by Alter? A) Because table is database object but column is not a database object.

INDEXES:

Index is a database object which is used to retrieve the data quickly from esisting table.  Indexes are classified into two types.

WHY INDEXES?

Indexes are most useful on larger tables, on columns that are likely to appear in where clauses as simple equality.

TYPES:

Unique index Non-unique index Btree index Bitmap index Composite index Reverse key index Function-based index Descending index Domain index Object index Cluster index Text index Index organized table Partition index Local index Local prefixed Local non-prefixed Global index Global prefixed Global non-prefixed

UNIQUE INDEX:

Unique indexes guarantee that no two rows of a table have duplicate values in the columns that define the index. Unique index is automatically created when primary key or unique constraint is created. Ex: create unique index stud_ind on student(sno);

NON-UNIQUE INDEX:

Non-Unique indexes do not impose the above restriction on the column values. Ex: create index stud_ind on student(sno);

BTREE INDEX or ASCENDING INDEX:

The default type of index used in an oracle database is the btree index. A btree index is designed to provide both rapid access to individual rows and quick access to groups of rows within a range. The btree index does this by performing a succession of value comparisons. Each comparison eliminates many of the rows. Ex:      SQL> create index stud_ind on student(sno);

BITMAP INDEX:

This can be used for low cardinality columns: that is columns in which the number of distinct values is snall when compared to the number of the rows in the table. Ex:      SQL> create bitmap index stud_ind on student(sex);

COMPOSITE INDEX:

A composite index also called a concatenated index is an index created on multiple columns of a table. Columns in a composite index can appear in any order and need not be adjacent columns of the table. Ex: create bitmap index stud_ind on student(sno, sname);

REVERSE KEY INDEX:

A reverse key index when compared to standard index, reverses each byte of the column being indexed while keeping the column order. When the column is indexed in reverse mode then the column values will be stored in an index in different blocks as the starting value differs. Such an arrangement can help avoid performance degradations in indexes where modifications to the index are concentrated on a small set of blocks. Ex: create index stud_ind on student(sno, reverse); We can rebuild a reverse key index into normal index using the noreverse keyword. Ex: alter index stud_ind rebuild noreverse;

FUNCTION BASED INDEX:

This will use result of the function as key instead of using column as the value for the key. Ex: create index stud_ind on student(upper(sname));

DESCENDING INDEX:

The order used by B-tree indexes has been ascending order. You can categorize data in B-tree index in descending order as well. This feature can be useful in applications where sorting operations are required. Ex: create index stud_ind on student(sno desc);

TEXT INDEX:

Querying text is different from querying data because words have shades of meaning, relationships to other words, and opposites. You may want to search for words that are near each other, or words that are related to thers. These queries would be extremely difficult if all you had available was the standard relational operators. By extending SQL to include text indexes, oracle text permits you to ask very complex questions about the text. To use oracle text, you need to create a text index on the column in which the text is stored. Text index is a collection of tables and indexes that store information about the text stored in the column.

TYPES:

There are several different types of indexes available in oracle 9i. The first, CONTEXT is supported in oracle 8i as well as oracle 9i. As of oracle 9i, you can use the CTXCAT text index fo further enhance your text index management and query capabilities. CONTEXT CTXCAT CTXRULE The CTXCAT index type supports the transactional synchronization of data between the base table and its text index. With CONTEXT indexes, you need to manually tell oracle to update the values in the text index after data changes in base table. CTXCAT index types do not generate score values during the text queries.

HOW TO CREATE TEXT INDEX?

You can create a text index via a special version of the create index comman. For context index, specify the ctxsys.context index type and for ctxcat index, specify the ctxsys.ctxcat index type. Ex: Suppose you have a table called BOOKS with the following columns Title, Author, Info. SQL> create index book_index on books(info) indextype is ctxsys.context; SQL> create index book_index on books(info) indextype is ctxsys.ctxcat;

TEXT QUERIES:

Once a text index is created on the info column of BOOKS table, text-searching capabilities increase dynamically.

CONTAINS & CATSEARCH:

CONTAINS function takes two parameters – the column name and the search string. Syntax: Contains(indexed_column, search_str); If you create a CTXCAT index, use the CATSEARCH function in place of CONTAINS. CATSEARCH takes three parameters – the column name, the search string and the index set. Syntax: Contains(indexed_column, search_str, index_set);

HOW A TEXT QEURY WORKS?

When a function such as CONTAINS or CATSEARCH is used in query, the text portion of the query is processed by oracle text. The remainder of the query is processed just like a regular query within the database. The result of the text query processing and the regular query processing are merged to return a single set of records to the user.

SEARCHING FOR AN EXACT MATCH OF A WORD:

The following queries will search for a word called ‘prperty’ whose score is greater than zero. SQL> select * from books where contains(info, ‘property’) > 0; SQL> select * from books where catsearch(info, ‘property’, null) > 0; Suppose if you want to know the score of the ‘property’ in each book, if score values for individual searches range from 0 to 10 for each occurrence of the string within the text then use the score function. SQL> select title, score(10) from books where contains(info, ‘property’, 10) > 0;

SEARCHING FOR AN EXACT MATCH OF MULTIPLE WORDS:

The following queries will search for two words. SQL> select * from books where contains(info, ‘property AND harvests’) > 0; SQL> select * from books where catsearch(info, ‘property AND harvests’, null) > 0; Instead of using AND you could hae used an ampersand(&). Before using this method, set define off so the & character will not be seen as part of a variable name. SQL> set define off SQL> select * from books where contains(info, ‘property & harvests’) > 0; SQL> select * from books where catsearch(info, ‘property  harvests’, null) > 0;

The following queries will search for more than two words:

SQL> select * from books where contains(info, ‘property AND harvests AND workers’) > 0; SQL> select * from books where catsearch(info, ‘property harvests workers’, null) > 0; The following queries will search for either of the two words. SQL> select * from books where contains(info, ‘property OR harvests’) > 0; Instead of OR you can use a vertical line (|). SQL> select * from books where contains(info, ‘property | harvests’) > 0; SQL> select * from books where catsearch(info, ‘property | harvests’, null) > 0; In the following queries the ACCUM(accumulate) operator adds together the scores of the individual searches and compares the accumulated score to the threshold value. SQL> select * from books where contains(info, ‘property ACCUM harvests’) > 0; SQL> select * from books where catsearch(info, ‘property ACCUM harvests’, null) > 0; Instead of OR you can use a comma(,). SQL> select * from books where contains(info, ‘property , harvests’) > 0; SQL> select * from books where catsearch(info, ‘property , harvests’, null) > 0; In the following queries the MINUS operator subtracts the score of the second term’s search from the score of the first term’s search. SQL> select * from books where contains(info, ‘property MINUS harvests’) > 0; SQL> select * from books where catsearch(info, ‘property NOT harvests’, null) > 0; Instead of MINUS you can use – and instead of NOT you can use ~. SQL> select * from books where contains(info, ‘property - harvests’) > 0; SQL> select * from books where catsearch(info, ‘property ~ harvests’, null) > 0;

SEARCHING FOR AN EXACT MATCH OF A PHRASE:

The following queries will search for the phrase. If the search phrase includes a reserved word within oracle text, the you must use curly braces ({}) to enclose text. SQL> select * from books where contains(info, ‘transactions {and} finances’) > 0; SQL> select * from books where catsearch(info, ‘transactions {and} finances’, null) > 0; You can enclose the entire phrase within curly braces, in which case any reserved words within the phrase will be treated as part of the search criteria. SQL> select * from books where contains(info, ‘{transactions and finances}’) > 0; SQL> select * from books where catsearch(info, ‘{transactions and finances}’, null) > 0;

SEARCHING FOR WORDS THAT ARE NEAR EACH OTHER:

The following queries will search for the words that are in between the search terms. SQL> select * from books where contains(info, ‘workers NEAR harvests’) > 0; Instead of NEAR you can use : SQL> select * from books where contains(info, ‘workers ; harvests’) > 0; In CONTEXT index queries, you can specify the maximum number of words between the search terms. SQL> select * from books where contains(info, ‘NEAR((workers, harvests),10)’ > 0;

USING WILDCARDS DURING SEARCHES:

You can use wildcards to expand the list of valid search terms used during your query. Just as in regular text-string wildcard processing, two wildcards are available. % - percent sign; multiple-character wildcard _ - underscore; single-character wildcard SQL> select * from books where contains(info, ‘worker%’) > 0; SQL> select * from books where contains(info, ‘work___’) > 0;

SEARCHING FOR WORDS THAT SHARE THE SAME STEM:

Rather than using wildcards, you can use stem-expansion capabilities to expand the list of text strings. Given the ‘stem’ of a word, oracle will expand the list of words to search for to include all words having the same stem. Sample expansions are show here. Play - plays playing played playful SQL> select * from books where contains(info, ‘$manage’) > 0;

SEARCHING FOR FUZZY MATCHES:

A fuzzy match expands the specified search term to include words that are spelled similarly but that do not necessarily have the same word stem. Fuzzy matches are most helpful when the text contains misspellings. The misspellings can be either in the searched text or in the search string specified by the user during the query. The following queries will not return anything because its search does not contain the word ‘hardest’. SQL> select * from books where contains(info, ‘hardest’) > 0; It does, however, contains the word ‘harvest’. A fuzzy match will return the books containing the word ‘harvest’ even though ‘harvest’ has a different word stem thant the word used as the search term. To use a fuzzy match, precede the search term with a question mark, with no space between the question mark and the beginning of the search term. SQL> select * from books where contains(info, ‘?hardest’) > 0;

SEARCHING FOR WORDS THAT SOUND LIKE OTHER WORDS:

SOUNDEX, expands search terms based on how the word sounds. The SOUNDEX expansion method uses the same text-matching logic available via the SOUNDEX function in SQL. To use the SOUNDEX option, you must precede the search term with an exclamation mark(!). SQL> select * from books where contains(info, ‘!grate’) > 0;

INDEX SYNCHRONIZATION:

When using CONTEXT indexes, you need to manage the text index contents; the text indexes are not updated when the base table is updated. When the table was updated, its text index is out of sync with the base table. To sync of the index, execute the SYNC_INDEX procedure of the CTX_DDL package. SQL> exec CTX_DDL.SYNC_INDEX(‘book_index’);

INDEX SETS:

Historically, problems with queries of text indexes have occurred when other criteria are used alongside text searches as part of the where clause. To improve the mixed query capability, oracle features index sets. The indexes within the index set may be structured relational columns or on text columns. To create an index set, use the CTX_DDL package to create the index set and add indexes to it. When you create a text index, you can then specify the index set it belongs to. SQL> exec CTX_DDL.CREATE_INDEX_SET(‘books_index_set’); The add non-text indexes. SQL> exec CTX_DDL.ADD_INDEX(‘books_index_set’, ‘title_index’); Now create a CTXCAT text index. Specify ctxsys.ctxcat as the index type, and list the index set in the parameters clause. SQL> create index book_index on books(info) indextype is ctxsys.ctxcat          parameters(‘index set books_index_set’);

INDEX-ORGANIZED TABLE:

An index-organized table keeps its data sorted according to the primary key column values for the table. Index-organized tables store their data as if the entire table was stored in an index. An index-organized table allows you to store the entire table’s data in an index. Ex: create table student (sno number(2),sname varchar(10),smarks number(3)               constraint pk primary key(sno) organization index;

PARTITION INDEX:

Similar to partitioning tables, oracle allows you to partition indexes too. Like table partitions, index partitions could be in different tablespaces.

LOCAL INDEXES:

Local keyword tells oracle to create a separte index for each partition. In the local prefixed index the partition key is specified on the left prefix. When the underlying table is partitioned baes on, say two columns then the index can be prefixed on the first column specified. Local prefixed indexes can be unique or non unique. Local indexes may be easier to manage than global indexes. Ex: create index stud_index on student(sno) local;

GLOBAL INDEXES:

A global index may contain values from multiple partitions. An index is global prefixed if it is partitioned on the left prefix of the index columns. The global clause allows you to create a non-partitioned index. Global indexes may perform uniqueness checks faster than local (partitioned) indexes. You cannot create global indexes for hash partitions or subpartitions. Ex: create index stud_index on student(sno) global; Similar to table partitions, it is possible to move them from one device to another. But unlike table partitions, movement of index partitions requires individual reconstruction of the index or each partition (only in the case of global index). Ex: alter index stud_ind rebuild partition p2 Index partitions cannot be dropped manually.  They are dropped implicitly when the data they refer to is dropped from the partitioned table.

MONITORING USE OF INDEXES:

Once you turned on the monitoring the use of indexes, then we can check whether the table is hitting the index or not. To monitor the use of index use the follwing syntax. Syntax: alter index index_name monitoring usage; then check for the details in V$OBJECT_USAGE view. If you want to stop monitoring use the following. Syntax: alter index index_name nomonitoring usage;

DATA MODEL:

ALL_INDEXES DBA_INDEXES USER_INDEXES ALL_IND-COLUMNS DBA-IND_COLUMNS USER_IND_COLUMNS ALL_PART_INDEXES DBA_PART_INDEXES USER_PART_INDEXES V$OBJECT_USAGE

PSEUDO COLUMNS:

Automatically filled by oracle. SYSDATE, NEXTVAL, CURRVAL, ROWID,  ROWNUM, LEVEL, SQLCODE, SQLERRM

ROWID: 

It is an unique value. It is automatically assigned with every row inserted  into table. It is stored permanently in database. It comprises of Object id, Data file id, Block id & Record id.  It is Re usable.

select rowid, dname, loc from dept;
select rowid, empno, ename, sal from emp;
........AAA    101
........AAB    102
........AAC    103   
........AAD    104
........AAE    105

** Removing Duplicate records :
> Delete from emp where rowid not in
   (select min(rowid) from emp group by empno);

....AAA 101 RAM 21000
....AAB 101 RAM 21000    ....AAA
....AAC 101 RAM 21000

....AAD 102 HARI 22000
....AAE 102 HARI 22000    ....AAD
....AAF 102 HARI 22000

....AAG 103 SHAM 24000
....AAH 103 SHAM 24000   ....AAG
....AAI  103 SHAM 24000

* Retrieving Duplicate rows from Table 
select roll,sname,fee from stu_info s where
exists ( select count(*) from stu_info where roll = s.roll having count(*) > 1);

select distinct roll from stu_info s where
exists ( select count(*) from stu_info where roll = s.roll having count(*) > 1);

* Retrieve the rows which r not duplicated .
select distinct roll from stu_info s where
exists ( select count(*) from stu_info where roll = s.roll having count(*) = 1);

* Retrieve all the rows except Last Record
   > select empno,ename,sal,deptno from emp
      where rowid not in (select max(rowid) from             emp);

* Modify all rows except First row in Table.
>Update emp set sal = sal + 2500 where rowid         not in (select min(rowid) from emp);
---------------------------------------------------------------

ROWNUM:

It is an dynamic value automatically retrieved          along with Select statement output.   It is an unique value   It is only for display purpose.   It is not stored in database.

>select rownum, ename, sal from emp;
>select rownum, dname, loc from dept;

select rownum, empno, ename, sal from emp;
select rowid,rownum,empno,ename,sal from emp;
select rownum, empno, ename, sal from emp
order by sal;
select rownum,empno,ename,sal from emp
order by sal desc;

* Retrieving Top 5 Highly paid Employees
> select rownum, empno, ename, job, sal from
   ( select rownum, empno, ename, job, sal from             emp order by sal desc )
      where rownum <= 5;

* Retrieving Nth maximum salaried employ details
   (2 max, 5 max)
  > select rownum, empno, ename, job, sal from
    ( select rownum, empno, ename, job, sal from           emp order by sal desc )
    group by rownum, empno, ename, job, sal
    having rownum = &N;

* Retrieving Alternate rows
   select rownum,empno,ename,job,sal from emp
   group by rownum,empno,ename,job,sal
   having mod(rownum,2) = 0; --- EVEN Rows
   [ having mod(rownum,2) != 0; ] --- ODD Rows

MATRIX QUERY:

Create a matrix query to display the job, the salary for that job based on department number and the total salary for that job, for departments 20, 50, 80, and 90,giving each column and appropriate heading. Ex: Select job, sum(decode(deptno,10,sal)) dept10, sum(decode(deptno,20,sal)) dept20, sum(decode(deptno,30,sal)) dept30, sum(decode(deptno,40,sal)) dept40 from emp group by job;

CLUSTER:

It holds the common column shared by two tables. It will improve the performance while retrieving or manipulating data from Master-Detail Tables. It stores in “USER_CLUSTERS”. It cannot be applied to existing table.

EX:
CREATE TABLE emp (

   empno NUMBER(5) PRIMARY KEY,

   ename VARCHAR2(15) NOT NULL,

   . . .

   deptno NUMBER(3) REFERENCES dept)

   CLUSTER emp_dept (deptno);  

CREATE TABLE dept (

   deptno NUMBER(3) PRIMARY KEY, . . . )

   CLUSTER emp_dept (deptno);

FUNCTIONAL DEPENDENCY (NORMALIZATION):

The concept of functional dependency (also known as normalization was introduced by professor Codd in 1970 when he defined the first three normal forms (first, second and third normal forms). Normalization is used to avoid or eliminate the three types of anomalies (insertion, deletion and update anomalies) which a database may suffer from. These concepts will be clarified soon, but first let us define the first three normal forms.

First Normal Form: A relation is in first normal form if all its attributes are simple. In other words, none of the attributes of the relation is a relation. Notice that relation means 2-diemenatioanl table.

Example -1. Assume the following relation.

Student-courses (Sid:pk, Sname, Phone, Courses-taken)

Where attribute Sid is the primary key, Sname is student name, Phone is student's phone number and Courses-taken is a table contains course-id, course-description, credit hours and grade for each course taken by the student. More precise definition of table Course-taken is:

Course-taken (Course-id:pk, Course-description, Credit-hours, Grade) According to the definition of first normal form relation Student-courses is not in first normal form because one of its attribute Courses-taken is itself a table and is not a simple attribute. To clarify it more assume the above tables contain the data as shown below:

Student-courses

Sid	Sname	Phone	Courses-taken
100	John	487 2454	St-100-courses-taken
200	Smith	671 8120	St-200-courses-taken
300	Russell	871 2356	St-300-courses-taken

St-100-Course-taken

Course-id	Course-description	Credit-hours	Grade
IS380	Database Concepts	3	A
IS416	Unix Operating System	3	B

St-200-Course-taken

Course-id	Course-description	Credit-hours	Grade
IS380	Database Concepts	3	B
IS416	Unix Operating System	3	B
IS420	Data Net Work	3	C

St-300-Course-taken

Course-id	Course-description	Credit-hours	Grade
IS417	System Analysis	3	A

Definition of the three types of anomalies:

Insertion anomaly means that that some data cannot be inserted in the database. For example we cannot add a new course to the database of example-1, unless we insert a student who has taken that course.

Update anomaly means we have data redundancy in the database and to make any modification we have to change all copies of the redundant data or else the database will contain incorrect data. For example in our database we have the Course description "Database Concepts" for IS380 appears in both St-100-Course-taken and St-200-Course-taken tables. To change its description to "New Database Concepts" we have to change it in all places. Indeed one of the purposes of normalization is to eliminate data redundancy in the database.

Deletion anomaly means deleting some data cause other information to be lost. For example if student Russell is deleted from St-100-Course-taken table we also lose the information that we had a course call IS417 with description System Analysis.

Thus Student-courses table suffers from all the three anomalies.

To convert the above structure to first normal form relations, all non-simple attributes must be removed or converted to simple attribute. To do that a new relation is created by combining each row of Student-courses with all rows of its corresponding course table that was taken by that specific student. Following is Student-courses table in first normal form.

Student-courses ( Sid:pk1, Sname, Phone, Course-id:pk2, 

Course-description, Credit-hours, Grade)

Notice that the primary key of this table is a composite key made up of two parts; Sid and Course-id. Note that pk1 following an attribute indicates that the attribute is the first part of the primary key and pk2 indicates that the attribute is the second part of the primary key.

 Student-courses

Sid	Sname	Phone	Course-id	Course-description	Credit-hours	Grade
100	John	487 2454	IS380	Database Concepts	3	A
100	John	487 2454	IS416	Unix Operating System	3	B
200	Smith	671 8120	IS380	Database Concepts	3	B
200	Smith	671 8120	IS416	Unix Operating System	3	B
200	Smith	671 8120	IS420	Data Net Work	3	C
300	Russell	871 2356	IS417	System Analysis	3	A

 Examination of the above Student-courses relation reveals that Sid does not uniquely identify a row (tuple) in the relation hence cannot be the primary key. For the same reason Course-id cannot be the primary key. However the combination of Sid and Course-id uniquely identifies a row in Student-courses, Therefore (Sid, Course-id) is the primary key of the above relation.

The primary key determines every attribute. For example if you know both Sid and Course-id for any student you will be able to retrieve Sname, Phone, Course-description, Credit-hours and Grade, because these attributes are dependent on the primary key. Figure 1 below is the graphical representation of the functional dependency between the primary key and attributes of the above relation.

 

Note that the attribute to the right of the arrow is functionally dependent on the attribute in the left of the arrow. Thus the combination (Sid, Course-id) is the determinant (that determines other attributes) and attributes Sname, Phone, Course-description, Credit-hours and Grade are dependent attributes. 

Formally speaking a determinant is an attribute or a group of attributes determine the value of other attributes. In addition to the (Sid, Course-id) there are two other determinants in the above Student-courses relation. These are; Sid and Course-id attributes. Note that Sid alone determines both Sname and Phone, and attribute Course-id alone determines both Credit-hours and Course_description attributes. 

 Attribute Grade is fully functionally dependent on the primary key (Sid, Course-id) because both parts of the primary keys are needed to determine Grade. On the other hand both Sname, and Phone attributes are not fully functionally dependent on the primary key, because only a part of the primary key namely Sid is needed to determine both Sname and Phone. Also attributes Credit-hours and Course-Description are not fully functionally dependent on the primary key because only Course-id is needed to determine their values. 

The new relation Student-courses still suffers from all three anomalies for the following reasons:

1.     The relation contains redundant data (Note Database_Concepts as the course description for IS380 appears in more than one place).

2.     The relation contains information about two entities Student and course.

Following is the detail description of the anomalies that relation Student-courses suffers from.

1.     Insertion anomaly: We cannot add a new course such as IS247 with course description programming techniques to the database unless we add a student who to take the course. 

2.     Update anomaly: If we change the course description for IS380 from Database Concepts to New_Database_Concepts we have to make changes in more than one place or else the database will be inconsistent. In other words in some places the course description will be New_Database_Concepts and in any place were we forgot to make the changes the description still will be Database_Concepts. 

3.     Deletion anomaly: If student Russell is deleted from the database we also loose information that we had on course IS417 with description System_Analysis.

The above discussion indicates that having a single table Student-courses for our database causing problems (anomalies). Therefore we break the table to smaller table to get a higher normal form relation. Before doing that let us define the second normal form.

Second normal relation: A first normal form relation is in second normal form if all its non-primary attributes are fully functionally dependent on the primary key.

Note that primary attributes are those attributes, which are parts of the primary key, and non-primary attributes do not participate in the primary key. In Student-courses relation both Sid and Course-id are primary attributes because they are components of the primary key. However attributes Sname, Phone, Course-description, Credit-hours and Grade all are non primary attributes because non of them is a component of the primary key.

To convert Student-courses to second normal relations we have to make all non-primary attributes to be fully functionally dependent on the primary key. To do that we need to project (that is we break it down to two or more relations) Student-courses table into two or more tables. However projections may cause problems. To avoid such problems it is important to keep attributes, which are dependent on each other in the same table, when a relation is projected to smaller relations. Following this principle and examination of Figure-1 indicate that we should divide Student-courses relation into following three relations:

PROJECT Student-courses ON (Sid, Sname, Phone) creates a table call it Student. The relation Student will be Student (Sid:pk, Sname, Phone) and

PROJECT Student-courses ON (Sid, Course-id, Grade) creates a table call it Student-grade. The relation Student-grade will be Student-grade (Sid:pk1:fk:Student, Course-id::pk2:fk:Courses, Grade) and

Projects Student-courses ON (Course-id, Course-Description, Credit-hours) create a table call it Courses. Following are these three relations and their contents:

Student (Sid:pk, Sname, Phone)

Sid	Sname	Phone
100	John	487 2454
200	Smith	671 8120
300	Russell	871 2356

 Courses (Course-id::pk, Course-Description)

Course-id	Course-description	Credit-hours
IS380	Database Concepts	3
IS416	Unix Operating System	3
IS420	Data Net Work	3
IS417	System Analysis	3

  Student-grade (Sid:pk1:fk:Student, Course-id::pk2:fk:Courses, Grade)

Sid	Course-id	Grade
100	IS380	A
100	IS416	B
200	IS380	B
200	IS416	B
200	IS420	C
300	IS417	A

 

All these three relations are in second normal form. Examination of these relations shows that we have eliminated the redundancy in the database. Now relation Student contains information only related to the entity student, relation Courses contains information related to entity Courses only, and the relation Student-grade contains information related to the relationship between these two entity.

Further these three sets are free from all anomalies. Let us clarify this in more detail.

Insertion anomaly: Now a new Course with course-id IS247 and Course-description can be inserted to the table Course. Equally we can add any new students to the database by adding their id, name and phone to Student table. Therefore our database, which made up of these three tables does not suffer from insertion anomaly.

Update anomaly: Since redundancy of the data was eliminated no update anomaly can occur. To change the course-description for IS380 only one change is needed in table Courses. 

Deletion anomaly: the deletion of student Russell from the database is achieved by deleting Russell's records from both Student and Student-grade relations and this does not have any side effect because the course IS417 untouched in the table Courses.

Third Normal Form: A second normal form relation is in third normal form if all non-primary attributes (that is attributes that are not parts of the primary key or of any candidate key) have non-transitivity dependency on the primary key. 

Assume the relation:

 STUDENT (Sid: pk, Activity, fee)

Further Activity ------------> fee that is the Activity determine the fee 

Sid	Activity	Fee
100	Swimming	100
200	Tennis	100
300	Golf	300
400	Swimming	100

 Table STUDENT is in first normal form because all its attributes are simple. Also STUDENT is in second normal form because all its non-primary attributes are fully functionally dependent on the primary key (Sid). Notice that a first normal relation with non-composite (that is simple) primary key automatically will be in second normal form because all its non-primary attributes will be fully functionally dependent on the primary key.

Table STUDENT suffers from all 3 anomalies; a new student can not be added to the database unless he/she takes an activity and no activity can be inserted into the database unless we get a student to take that activity. There is redundancy in the table (see Swimming), therefore to change the fee for Swimming we must make changes in more than one place and that will cause update anomaly problem. If student 300 is deleted from the table we also loose the fact that we had Golf activity with its fee to be 300. To overcome these anomalies STUDENT table should be converted to smaller tables. Consider the following three projection of the STUDENT relation:

PROJECT STUDENT on [Sid, Activity] and we get a relation name it

STUD-AVT (Sid:pk, Activity) with the following data :

 STUD_ACT

Sid	Activity
100	Swimming
200	Tennis
300	Golf
400	Swimming

 

PROJECT STUDENT on [Activity, Fee] and we get a relation name AVT-Fee (Activity:pk, Fee) with the following data : 

AVT-Fee

Activity	Fee
Swimming	100
Tennis	100
Golf	300
Swimming	100

 PROJECT STUDENT on [Sid, Fee] and we get a relation name 

Sid-Fee (Sid:pk, Fee) with the following data :

 Sid-Fee

Sid	Fee
100	100
200	100
300	300
400	100

 The question is which pairs of these projections should we choose? The answer to that is to choose the pair STUD-AVT and AVT-Fee because the join of these two projections produces the original STUDENT table. Such projections are called non-loss projections. Therefore the join of STUD-AVT and AVT-Fee on the common attribute Activity recreate the original STUDENT table. On the other hand as shown below the join of projections Sid-Fee and AVT-Fee on their common attribute Sid generates erroneous data that were not in the original STUDENT table and such projections are called loss projections. Following is the join of projections Sid-Fee and AVT-Fee on their common attribute Sid

Sid	Activity	Fee
100	Swimming	100
100	Tennis	100
200	Tennis	100
200	Swimming	100
300	Golf	300
400	Swimming	100
400	Tennis	100

The three rows marked in red color were not in the original STUDENT table. Thus we have an erroneous data in the database. 

Both projections STUD-AVT and AVT-Fee are in third normal form and they do not suffer from any anomalies.

 Boyce Codd normal (BOC): A relation is in BOC form if every determinant is a candidate key. This is an improved form of third normal form.

 Fourth Normal Form: A Boyce Codd normal form relation is in fourth normal form if there is no multi value dependency in the relation or there are multi value dependency but the attributes, which are multi value dependent on a specific attribute, are dependent between themselves. This is best discussed through mathematical notation. Assume the following relation

  R(a:pk1, b:pk2, c:pk3)

 Recall that a relation is in BOC normal form if all its determinant are candidate keys, in other words each determinant can be used as a primary key. Because relation R has only one determinant (a, b, c),, which is the composite primary, key and since the primary is a candidate key therefore R is in BOC normal form.

 Now R may or may not be in fourth normal form. 

 1. If R contains no multi value dependency then R will be in Fourth normal form.

 2. Assume R has the following two-multi value dependencies:

 a ------->> b and a ---------->> c 

 In this case R will be in the fourth normal form if b and c dependent on each other. However if b and c are independent of each other then R is not in fourth normal form and the relation has to be projected to following two non-loss projections. These non-loss projections will be in fourth normal form.

 Example:

 Case 1:

Assume the following relation:

Employee (Eid:pk1, Language:pk2, Skill:pk3) 

  No multi value dependency, therefore R is in fourth normal form.

 case 2: 

Assume the following relation with multi-value dependency:

 Employee (Eid:pk1, Languages:pk2, Skills:pk3) 

Eid ---->> Languages Eid ------>> Skills

Languages and Skills are dependent.

This says an employee speak several languages and has several skills. However for each skill  a specific language is used when that skill is practiced. 

Eid	Language	Skill
100	English	Teaching
100	Kurdish	Politic
100	French	cooking
200	English	cooking
200	Arabic	Singing

Thus employee 100 when he/she teaches speaks English but when he cooks speaks French. This relation is in fourth normal form and does not suffer from any anomalies.

case 3: 

Assume the following relation with multi-value dependency:

Employee (Eid:pk1, Languages:pk2, Skills:pk3) 

Eid ---->> Languages Eid ------>> Skills

Languages and Skills are independent.

Eid	Language	Skill
100	English	Teaching
100	Kurdish	Politic
100	English	politic
100	Kurdish	Teaching
200	Arabic	Singing

 This relation is not in fourth normal form and suffers from all three types of anomalies.

Insertion anomaly: To insert row (200 English Cooking) we have to insert two extra rows (200 Arabic cooking), and (200 English Singing) otherwise the database will be inconsistent. Note the table will be as follow:

Eid	Language	Skill
100	English	Teaching
100	Kurdish	Politic
100	English	politic
100	Kurdish	Teaching
200	Arabic	Singing
200	English	Cooking
200	Arabic	Cooking
200	English	Singing

 Deletion anomaly: If employee 100 discontinue politic skill we have to delete two rows 

(100 Kurdish Politic),  and (100 English Politic) otherwise the database will be inconsistent.

 Update anomaly: If employee 200 changes his skill from singing to dancing we have to make changes in more than one place

 The relation is projected to the following two non-loss projections which are in forth normal form

 Emplyee_Language(Eid:pk1, Languages:pk2)

Eid	Language
100	English
100	Kurdish
200	Arabic

 Emplyee_Language(Eid:pk1, Skills:pk2)

Eid	Skill
100	Teaching
100	Politic
200	Singing

OLAP FEATURES IN ORACLE:

Some features for query processing in ORACLE include the use of ONLINE ANALYTICAL PROCESSING (OLAP) upon the data base.

OLAP Features are useful for data warehousing and data mart applications. The OLAP operations are performance enhancements. TOP_N Queries. Group By Cube Rollup.

ROLLUP OPTION:

It is a group by operation and is used to produce subtotals at any level of the  aggregation. The generated subtotals “Rolled UP “ to produce grand total .  The totaling  is based on A one  dimentional data hierarchy of grouped information. Syntax:  Group by ROLLUP(Col1,Col2..); Ex: Select Deptno, SUM(SAL) Salary From EMP Group by ROLLUP(Deptno); Select Job, Deptno, SUM(SAL) Salary From EMP Group by ROLLUP(Job, Deptno); Select Job, Deptno, AVG(SAL) Average From EMP Group by ROLLUP(Job, Deptno); Passing Multiple Columns To ROLLUP: When Multiple Columns are Passed to ROLLUP, The ROLLUP, Groups the rows into blocks with the same column values. SQL> select deptno,sum(sal)  salary from emp group by rollup(deptno,job); SQL>select job,deptno,sum(sal)  salary from emp group by rollup by rollup(job,deptno); SQL>select job,deptno,avg(sal) average from emp group by rollup by rollup(job,deptno); NULL values in  the ouitput of roll up operations typically mean that the row contains subtotals or grand total information . Use  NVL( ) function for proper meaning.

CUBE OPTION:

It is an extension similar to rollup. CUBE allows taking a specified set of grouping columns and creating sub totals for all possible combinations of them. The result of cube is a summery that shows subtotals for every combination of the columns or expressions in the group by clause. The implementation of cube is also calls as N-Dimensional cross tabulation. Example: SQL> select deptno,job,sum(sal)  salary from emp group by cube(deptno,job); SQL> select job,deptno, ,sum(sal)  salary from emp group by cube(job,deptno);

Disadvantages of SQL:

Disadvantages of SQL are : a. Cannot drop a field Cannot rename a field Cannot manage memory Procedural Language option not provided Index on view or index on index not provided b. View updation problem

SOME QUESTIONS AND ANSWERS:

1. Write a query to generate sequence numbers from 1 to the specified number N?
Solution:  SELECT LEVEL FROM DUAL CONNECT BY LEVEL<=&N;

2.Write a query to display only Friday dates from Jan, 2000 to till now?

Solution: SELECT  C_DATE,

        TO_CHAR(C_DATE,'DY') 

FROM 

(

  SELECT TO_DATE('01-JAN-2000','DD-MON-YYYY')+LEVEL-1 C_DATE 

  FROM   DUAL 

  CONNECT BY LEVEL <= 

       (SYSDATE - TO_DATE('01-JAN-2000','DD-MON-YYYY')+1) 

) WHERE TO_CHAR(C_DATE,'DY') = 'FRI'; 

3. Write a query to display each letter of the word "SMILE" in a separate row?

S

M

I

L

E

Solution: SELECT SUBSTR('SMILE',LEVEL,1) A FROM   DUAL CONNECT BY LEVEL =LENGTH('SMILE');

4. Convert the string "SMILE" to Ascii values?  The output should look like as 83,77,73,76,69. Where 83 is the ascii value of S and so on.
The ASCII function will give ascii value for only one character. If you pass a string to the ascii function, it will give the ascii value of first letter in the string. Here i am providing two solutions to get the ascii values of string.

Solution1: SELECT SUBSTR(DUMP('SMILE'),15) FROM DUAL;

Solution2: SELECT WM_CONCAT(A) FROM (SELECT ASCII(SUBSTR('SMILE',LEVEL,1)) A FROM   DUAL CONNECT BY LEVEL <=LENGTH('SMILE') );

5. How find to find column value without knowing table name?

you can use view "user_tab_columns". in this view you will get. In this you will get table_name, column_name, datatype and all things.

select * from user_tab_columns;