ORACLE 12C SQL & PL/SQL
By Dinesh PV
APRIL 14, 2017 DURGA SOFTWARE SOLUTIONS Mythrivanam,Hyderabad
ORACLE Oracle Database 12c is a signif icant upgr ade f rom prior releases of Oracle. New f eatures give developers, database administrators, and end users greater control over the storage, processing, and retrieval of their data. A relat ional database management system (R DBMS) such as Oracle gives you a way of doing these tasks in an understandable and reasonably uncomplicated way. i) Lets you put data int o it Keeps the data ii) Lets you get the dat a out iii) and work with it Oracle supports this in/keep/out approach and provides c lever tools that allow you considerable sophistication in how the data is captur ed, edited, modif ied, and put in; how you keep it securely; and how you get it out to manipulate and report on it.
Oracle is a RELATIONAL DATA BASE MANAGEMENT SYSTEM (RDBMS) Oracle dat a base is a sof tware where we can store and process (f etch / insert / change / delete) business data. Oracle is an RDBMS s/w f rom oracle corp.
A Timeline of Database Histor y Ancient Times: Hum an beings began to store inf ormation ver y long ago. In the ancient t imes, elaborate database systems were developed by
government off ices, librar ies, hospitals, and business organizations, and some of the basic pr inciples of these systems are still be ing used today. 1960s: Computer ized database started in the 1960s, when the use of computers became a more cost -eff ective option f or private or ganizat ions. There were t wo popular data models in this decade: a network model called CODASYL and a hierarchical model called IMS. One database system that proved to be a commercial success was t he SABRE system that was used by IBM to help American Air lines manage it s reser vat ions data .
Heirarchical Model In a hier archical model , data is organized into a tree- like structure, implying a single parent f or each record. A sort f ield k eeps sibling records in a particular order. Hier archical structures were widely used in the early mainf rame database management system s, such as the Inf ormation Management System (IMS) by IBM, and now describe the structure of XML documents. This structure allows one one -to-many relationship bet ween t wo t ypes of data. This structure is ver y ef f icient to describe many relat ionships in the r eal wor ld; recipes, t able of contents, ordering of paragraphs/ verses, any nest ed and sorted inf ormation. This hierarchy is used as the physical order of records in stor age. Record access is done by navigating downward t hrough the data structure using pointers combined with sequential ac cessing. Because of this, the hier archical structur e is inef f icient f or certain dat abase oper ations when a f ull path (as opposed to upward link and sort f ield) is not also included f or each record. Such limitations have been compensated f or in later I MS ver sions by additional logical hierarchies imposed on the base physical hier archy.
Network Model
The net work model expands upon the hier archical structure, allowing manyto-many relat ionships in a tree - like struct ure that allows multiple parents. It was most popular bef ore being replaced by the relational model, and is def ined by the CODASYL specif icat ion. The net work model organizes data using two f undamental concepts, called records and sets. Records contain f ields ( which may be organized hier archically, as in the programming language COBOL). Sets (not to be conf used with mathematical sets) def ine one-to-many relat ionships bet ween records: one owner, many members. A record may be an owner in any number of sets, and a member in any number of sets. A set consists of circular linked lists wher e one record t ype, the set owner or parent, appears once in each circle, and a s econd record t ype, the subordinate or child, may appear multiple times in each circle. In this way a hier archy may be est ablished bet ween any t wo record t ypes, e.g., type A is the owner of B. At the same time another set may be def ined where B is the owner of A. Thus all the sets compr ise a general directed graph (ownership def ines a direct ion), or network construct . Access to records is either sequential (usuall y in each recor d t ype) or by navigation i n the circular linked lists. The net work model is able to represent redundancy in data more eff icient ly than in the hierarchical model, and there can be more than one path f rom an ancestor node to a descendant. The oper ations of t he net work model are navigational in st yle: a program maintains a current posit ion, and navigates f rom one record to another by f ollowing the relationships in which the record participates. Records can also be located by supplying key values. Although it i s not an essent ial f eature of the model, net work databases generally implement the set relationships by means of pointer s that direct ly address the locat ion of a record on disk. This gives excellent retrieval perf ormance, at the expense of operations such as database loading and reorganization. Popular DBMS pr oducts that utilized it were Cincom Systems ' Total and Cullinet's IDMS. IDMS gained a consider able customer base; in the 1980s, it adopted the relat ional model and SQL in addit ion t o its orig inal tools and languages. Most object databases (invented in the 1990s) use the navigational concept to provide f as t navigation across net works of objects, generally using object identif iers as "smart" point ers to related objects. Objectivit y/DB, f or instance, implement s named one -to-one, one-to-many, many- to-one, and many-to-many named relat ionships that can cross databases. Many object databases also support SQL, combining the strengths of both models.
1970 to 1972: E. F. Codd published an important paper to propose the use of a relat ional database model, and his ideas changed the way people thought about databases. In his model, the database’s schema, or logical organizat ion, is disconnect ed f rom physical inf ormation storage, and this became the standar d principle f or database systems. 1970s: Two major relational dat abase system prot otypes were created bet ween the years 1974 and 1977, and t hey wer e the Ingres, which was developed at UBC, and System R, created at IBM San Jose. Ingres used a quer y language known as QUEL, and it led to t he creat ion of systems such as Ingres Corp., MS SQL Server, Sybase, W ang’s PACE, and Britton-Lee. On the other hand, System R used the SEQ UEL query language, and it contributed to the development of SQL/DS, DB2, Allbase, Oracle, and Non- Stop SQL. It was also in this decade that Relational Database Management System , or RDBMS, became a recognized term. 1976: A new database model called Entit y-Relationship, or ER, was proposed by P. Chen this year. Thi s model made it possible for designers to f ocus on data application, instead of logical table structure.
Relational Model
1980s: Structured Q uer y Language, or SQL, became the standard quer y language. Relational dat abase systems became a commercial success as the rapid increase in computer sales boost ed the database market, and this caused a major decline in the popular it y of net work and hierarchical dat abase models. DB2 became the f lagship database pr oduct f or IBM, and the introduct ion of the IBM PC resulted in the establishment s of many new database companies and the development of products such as PARADOX, RBASE 5000, RI M, Dbase III and IV, OS/2 Database Manager, and W atcom SQL. Early 1990s: Af ter a database industry shakeout, most of the sur viving companies sold com plex database pr oducts at high prices. Ar ound this time, new client tools f or application developm ent were released, and these included the Oracle Developer, PowerBuilder, VB, and ot hers. A number of tools f or personal pr oduct ivity, such as O DBC and Excel/Access, were also developed. Protot ypes f or Object Database Management Syst ems, or ODBMS, were cr eated in the early 1990s. Mid 1990s: The advent of the Internet led to exponential growt h of the database industry. Aver age desktop user s be gan to use client -server database systems to access computer systems that contained legacy data. Late 1990s: Increased investment in online businesses resulted in a r ise in demand f or Internet database connectors, such as Front Page, Active Ser ver Pages, Java Ser velets, Dream W eaver, ColdFusion, Enterpr ise Java Beans, and Oracle Developer 2000. The use of cgi, gcc, MySQL, Apache, and ot her systems brought open source solution to the Internet. W ith the increased use of point-of -sale technology, online transa ction pr ocessing and online analyt ic processing began to come of age. 2000s: Although the Internet industr y experienced a decline in the early 2000s, database applications continue to grow. New interact ive applications were developed f or PDAs, point -of -sale t ransact ions, and consolidation of vendors. Presently, t he three leading database companies in the wester n world are Micr osof t, IBM, and Oracle. Then there are many changes to Relati onal Dat abase like
1) Object Oriented database model 2) Dimensional mode l 3) Multival ve Model
How the data is generated? Through business objects and its activit ies (transactions) , dat a will be generated. W hat is a business? Business is a collection of real world ent ities and its activities. BUSI NESS | --------------------------------------------------------------------------------------------| | | | {emps depts Products/ customers} -----------> [entities/ Objects] ser vices In early days of a business, we have ver y lim ited dat a like
lim ited lim ited lim ited lim ited
number of emp loyees departments pr oducts customers ( No customers on the ver y beginning day )
Day-By- Day the business may impr ove , means need more number of business resources and there is an incr ease in number of transacti ons. In this case we need the automated Business syst em called DBMS, to maintain business data and it s activities automat ically. D AT A B ASE CONCEPTS : D AT A: Collection of inf ormation of any one Business ent it y is known as data. [One line of inf ormation] Ex:
one employee inf ormation One product inf ormation One sales transaction inf ormation
D AT A B ASE: It is sof tware which stores and manages the collection of inf ormation of all objects in the business. Technically, it is collection of programs and each program is responsible f or perf orming a specif ic task. D AT AB ASE M AN AG EMENT SYSTEM: DB which is comprised with management system ser vices is known as DBMS.
Here the ser vices ar e
Entering new data Updat ing old data with new data Deleting unwanted data Authenticating the users Providing secur it y.
RDBMS: Author of RDBMS I S E.F. CODD and he invented 12 Rules f or an RDBMS. Collection of interrelated data of all inter related objects with in the business is kno wn as RDBMS. The relation bet ween the tables is implem ented by using Ref erential integrit y constraints. In any RDBMS Data stored in the f orm of 2 -dimensional tables A table is a collection of rows and colum ns. A row is known as record (collection of columns) A column is known as a f ield
Tables of Information Oracle stores inf ormation in tables. Each of these tables has one or more columns. The inf ormationis stor ed row after row. Each unique set of data gets its own row. Oracle avoids specialized, academ ic terminolog y in order to m ake the product more approachable. In research papers on relational theor y, a column may be called an “attribut e,” a row may be called a “tuple” and a table may be called an “entit y.” For an end user, however, these terms are unnecessar y. Ex: WITHOUT REL ATION W e are unable to f etch some kind of required data. Consider t he below example. By maintaining data in 2 individual tables [no relat ion bet ween them] we are unable to f etch relevant dat a f rom these tables. Try to f ind the answers f or below quest ions. 1) Number of products f rom Sony? 2) Company details of product id “p001”? 3) Total investment made f or Asus products?......etc.
Prod_Dtls PID P001 P003
PN AM E Mobile Desktop
COST 14000 27000
MFG 22-oct-14 14-may-15
WARRENTY 1 year 3 years
P006 P004 P005
Laptop Tablet Smart phone
35990 12000 37000
11-may-12` 21-mar-13 10-oct-15
2 years 1 year 1 year
Comp_Dtls Comp_code Cmp1 Cmp2
Ex:
Comp_name Sony Asus
Country Japan South Korea
WITH REL ATI ON
By using Primary key of one table we need to implement foreign key in other table. This is called as implementing Physical relat ion bet ween the tables. See Below example:
Comp_Dtls Comp_code Cmp1
Comp_name Sony
Japan
Asus
South Korea
Cmp2
Country
Prod_Dtls Pid Pname Comp_Code P001 Mobile P003 Desktop
Cost 14000 27000
P006
Laptop
35990
P004 P005
Tablet Smart phone
12000 37000
Mfg 22-oct-14 14-may15 11-may12` 21-mar-13 10-oct-15
Warrent y 1 year 3 years
Cmp1 Cmp2
2 years
Cmp1
1 year 1 year
Cmp1 Cmp2
Ex: Inf ormation f or below requirement is easy now. Find Number of products f rom Sony? Find Number of products f rom Asus? Average product cost f rom any company? Company details of any product? Advantages:
W e can maintain integrity among table data W e can f etch accurate and complete data.
E.F. CODD Rules Dr Edgar F. Codd, af ter his extensive research on the Relat ional Model of database systems, came up wit h t welve r ules of his own, which accor ding to him, a database m ust obey in order to be regarded as a true relat ional database. These rules can be applied on any dat abase syst em that manages stored data using only its r elat ional capabilit ies. This is a f oundat ion rule, which acts as a base f or all the other rules. Rule 1: Information Rule The data stor ed in a database, may it be user data or met adata, must be a value of some table cell. Ever yt hing in a database must be st ored in a table f ormat. Rule 2: Guaranteed Access Rule Ever y single dat a element (value) is guaranteed to be accessible logicall y with a combinat ion of table -name, pr imary-key (row value), and attribute name (column value). No other means, such as pointers, can be used to access data. Rule 3: S ystematic Treatm ent of NULL Values The NULL values in a database must be given a systematic and unif orm treatment. This is a ver y important rule because a NULL can be interpreted as one the f ollowing − data is missing, data is not known, or data is not applicable. Rule 4: Acti ve Online Catal og The structure description of the ent ire database must be stored in an online catalog, known as data dictionar y, which can be accessed by aut horized users. Users can use the same quer y language to access the catalog which they use to access t he database itself . Rule 5: Comprehensive Dat a Sub -Language Rule A database can only be accessed using a language having linear syntax that supports dat a def init ion, data manipulat ion, and transaction management operat ions. This language can be use d direct ly or by means of some applicat ion. If the database allows access t o data without any help of this language, then it is considered as a violat ion. Rule 6: View Updating Rule All the views of a database, which can theoretically be updated, must also be updatable by the system.
Rule 7: High-Level Insert, Update, and Delete Rule A database must support high -level insertion, updat ion, and deletion. This must not be lim ited to a single row, that is, it must also support union, intersection and minus oper at ions to yield sets of data records. Rule 8: Physical Data Independence The data stored in a database must be independent of the applications that access the dat abase. Any change in the physical structure of a database must not have any impact on how the data is being accessed by external applications. Rule 9: Logical Dat a Independence The logical data in a database must be independent of its user’s view (application). Any change in logical dat a must not aff ect the applications using it. For example, if tw o tables are merged or one is split into t wo dif f erent tables, there should be no impact or change on the user application. This is one of the most diff icult rule to apply. Rule 10: Integrit y Independence A database must be independent of the applicat ion t hat uses it. All its integrit y constraints can be independent ly modif ied without t he need of any change in the application. This rule makes a database independent of the f ront-end application and its interf ace. Rule 11: Distribution Independence The end-user must not be able to see that the data is distributed over var ious locations. Users should always get the impression t hat the data is located at one site only. This rule has been regarded as the f oundat ion of distr ibuted database systems. Rule 12: Non- Subversion Rul e If a system has an interf ace that provides access to low - level records, then the interf ace must not be able to subvert the system and bypass secur it y and integrit y constraints.
PROJECT DEVELOPMENT PROCESS
BUSINES BR-1
BR-2
INDIA
USA
WEB PAGE
WEB PAGE
SQL SERVER
DATA
BO
WARE
LOADING OLAP RDBMS
HOUSE
COGNOS
USER INTERFACES
OLTP RDBMS
DB2
TRANSFORMATION
EXTRACTION
USERS
UK
WEB PAGE
ORACLE
OBIEE
BR-3
SSRS
REPORTING TOOLS
OLTP RDBMS: On Line Transaction Processing It contains transact ional data/ day -t o-day data/ current dat a / dynamic data It is used to store or process the business data. OL AP RDBMS: On Line Anal ytical Processing It contains historical data / old data. It is used to analyze the business.
Brief History of Oracle Database The current version of Oracle Database is the result of over 35 years of innovat ive developm ent. The current version of Oracle Database is the result of over 30 years of innovat ive developm ent. Highlights in the evolution of Oracle Database include the f ollowing :
Founding of Oracle In 1977, Larr y Ellison, Bob Miner, and Ed Oates started the consultancy Sof t war e Development Laboratories, which became Relational Sof tware, Inc. (RSI). In 1983, RSI became Oracle Systems Corpor ation and then later Oracle Corporation.
First commercially available RDBMS In 1979, R SI introduced Oracle V2 (Version 2) as the f irst commercially available SQL-based RDBMS, a landmark event in the histor y of relat ional da tabases.
Portable version of Oracle Database Oracle Version 3, released in 1983, was t he f irst relational dat abase to run on mainf rames, minicomputers, and PCs. The database was wr itten in C, enabling the database to be ported to mult iple platf orms.
Enhancements to concurrency control, data distr ibut ion, and scalabilit y Version 4 introduced multi - version read consistency. Version 5, released in 1985, supported client/ser ver computing and distributed database syst ems. Version 6 br ought enhancements to disk I/O, row locking, scalabilit y, and backup and recover y. Also, Version 6 introduced the f irst version of the PL/SQL language, a proprietary procedural extens ion to SQL.
PL/SQL stored program units Oracle7, released in 1992, introduced PL/ SQL stored procedur es and triggers.
Objects and part itioning Oracle8 was released in 1997 as the obj ect -relat ional database, support ing many new data t ypes. Addit ionally, Orac le8 supported partit ioning of large tables.
Internet comput ing
Oracle8 i Database, released in 1999, provided nat ive support f or internet protocols and ser ver -side suppor t f or Java. Oracle8 i was designed f or internet computing, enabling the database to be deployed in a multit ier environment.
Oracle Real Application Clusters (Oracle RAC) Oracle9 i Database introduced Oracle RAC in 2001, enabling multiple instances to access a single database simultaneously. Additionally, Oracle X ML Database ( Oracle XML DB) introduced the abilit y to store and quer y X ML.
Grid comput ing Oracle Database 10 g introduced grid computing in 2003. This release enabled organizations to virtualize computing resources by building a grid infrastructure based on low-cost commodit y ser vers. A key goal was to make the database self -managing and self -tuning. Oracle Automatic St orage Management (Oracle ASM) helped achieve this goal by vitalizing and simplif ying database storage management.
Manageabilit y, diagn oisabilit y, and availabilit y Oracle Database 11 g, released in 2007, introduced a host of new f eatures that enabled administrators and developers to adapt quickly to changing business requirements. The key to adaptabilit y is simplif ying the inf ormation inf rastructure by consolidat ing inf ormation and using automat ion whereve r possible.
Plugging In to the Cloud Oracle Database 12 c, released in 2013, was designed f or the Cloud, f eaturing a new Mult itenant architectur e, In - Memor y column store, and support f or JSON documents. Oracle Dat abase 12 c helps customers make more eff icien t use of their IT resources, while cont inuing to reduce costs and im prove ser vice levels f or users.
Oracle Database Architecture A database server is the key to inf ormation management. In general, a server reliably manages a large amount of data in a multiuser envir onment so that users can concurrent ly access the same data. A database s er ver also prevents unauthorized access and pr ovides eff icient solutions f or f ailure recover y .
Database and Instance An Oracle database ser ver consists of a database and at least one database instance, commonly ref erred to as simply an instance. Because an instance and a database are so closely connected, the term Oracle database is sometimes used to r ef er to both instance and database.
Database A database is a set of f iles, located on disk, that store data. These f iles can exist independently of a database inst ance.
Database instance An instance is a set of memory structures that manage database f iles. The instance consists of a shared m emory area, called the s ystem global area (SG A) , and a set of background processes. An instance can exist independently of database f iles .
Figure 1-1 shows a database and its instance.
For each user connection to the inst ance, a client process r uns the application. Each client process is associated with its own server process. The server process has its own pr ivat e session memor y, known as the program global area (PG A) .
Oracle Instance and Database
A database can be considered f rom both a physical and logical perspect ive. Physical dat a is data viewable at the oper ating system level. For example, operat ing system utilities such as the Linux ls and ps can list database f iles and processes. Logical data such as a table is meaningf ul only f or the
database. A SQL statement can list the t ables in an Oracle database, but an operat ing system utilit y cannot. The database h as physical structur es and logical structures. Because the physical and logical structures are separ ate, you can manage the physical storage of data without aff ecting access to logical storage str uctures. For example, renam ing a physical database f ile does not rename the tables whose data is stored in this f ile.
ORACLE-12c Installation In this section, you will be installing the Oracle Database and creating an Oracle Home User account. Expand the dat aba s e folder that you extracted in the previous section. Double-click setup.
Click Yes in the User Account Control window to continue with the installation.
The Configure Security Updates window appears. Enter your email address and My Oracle Support password to receive securit y issue notifications via email. If you do not wish to receive notifications via email, deselect "I wish to receive security updates via My Oracle
Support". Click Next to continue. Click " Yes" in the confirmation window to confirm your preference.
The Download Software Updates window appears with the following options: o o o
Select "Use My Oracle Support credentials for download" to download and appl y the latest software updates. Select "Use pre -downloaded software updates" to appl y software updates that you previousl y downloaded. Select "Skip software updates" if do not want to appl y any updates.
Accept the default and click Next.
The Select Installation Option window appears with the following options: o o o
Select "Create and configure a database" to insta ll the database, create database instance and configure the database. Select "Install database software onl y" to onl y install the database software. Select "Upgrade an existing database" to upgrade the database that is already installed.
In this OBE, we create and configure the database. Select the Create and configure a database option and click Next.
The System Class window appears. Select Desktop Class or Server Class depending on the t ype of system you are using. In this OBE, we will perform the installation on a desktop/laptop. Select Desktop class and click Next.
The Oracle Home User Selection window appears. Starting with Oracle Database 12c Release 1 (12.1), Oracle Database on Microsoft Windows supports the use of an Oracle Home User, speci fied at the time of installation. This Oracle Home User is used to run the Windows services for a Oracle Home, and is similar to the Oracle User on Oracle Database on Linux. This user is associated with an Oracle Home and cannot be changed to a different u ser post installation. Note: o o
Different Oracle homes on a system can share the same Oracle Home User or use different Oracle Home Users. The Oracle Home User is different from an Oracle Installation User. The Oracle Installation User is the user who requi res administrative privileges to install Oracle products. The Oracle Home User is used to run the Windows services for the Oracle Home.
The window provides the following options: o
If you select "Use Existing Windows User", the user credentials provided must be a standard Windows user account (not an administrator).
o
o
If this is a single instance database installation, the user can be a local user, a domain user, or a managed services account. If this is an Oracle RAC database installation, the existing user must be a Windows domain user. The Oracle installer will display an error if this user has administrator privileges. If you select "Create New Windows User", the Oracle installer will create a new standard Windows user account. This user will be assigned as the Oracle Home User. Please note that this user will not have login privileges. This option is not available for an Oracle RAC Database installation. If you select "Use Windows Built -in Account", the system uses the Windows Built -in account as the Oracle Home User.
Select the Create New Windows User option. Enter the user name as OracleHomeUser1 and password as Welcome1. Click Next. Note: Remember the Windows User password. It will be required later to administer or manage database services.
The Typical Install Configuration window appears. Click on a text field and then the balloon icon (
)to know more about the field.
Note that by default, the installer creates a container database along with a pluggable database called " pdb orc l ". The pluggable database contains the sample HR schema. Change the Global database name to or cl . Enter the "Administrative password" as Oracle_1. This password will be used later to log into administrator accounts such as SYS and SYS TEM . Click Next.
The prereq uisite checks are performed and a Summary window appears. Review the settings and click Install. Note: Depending on your firewall settings, you may need to grant permissions to allow java to access the network.
The progress window appears.
The Databas e Configuration Assistant creates the database.
After the Database Configuration Assistant creates the database, you can navigate to https://localhost:5500/em as a SYS user to manage the database using Enterprise Manager Database Express. You can click "Password Management..." to unlock accounts. Click OK to continue.
The Finish window appears. Click Close to exit the Oracle Universal Installer.
Verifying the Installation
In this section, you will be performing steps to verify the installation of Oracle Database. If you had changed the default location to install the database, make sure to specify the correct location of the files in the following steps. Alternativel y, you can also perform a quick sea rch using the Windows Start Menu
to locate files.
View Oracle Services Navigate to C: \Wi nd ows \ sys tem 32 using Windows Explorer. Double-click services. The Services window appears, displaying a list of services.
Scroll down to view a list Oracle services. You see that most of the Oracle services are started successfull y by the database.
View Oracle Home on the File System Navigate to the C:\ a pp \O rac leH ome Us er 1 folder. This folder contains database files (in or ada ta folder) and the Oracle Database software (in the pro duct folder).
Navigate to C: \a pp \ Ora cle Ho meUs er1 \pr odu ct \1 2.1. 0 \d bho me_ 1 folder. This folder is the new "Oracle Home" created by the installer and contains software files related to the database.
View the tnsnames.ora File Navigate to C: \a pp \ Ora cle Ho meUs er1 \pr odu ct \1 2.1. 0 \d bho me_ 1 \ NE TWOR K \ ADMI N . Double-click tnsnames.ora to view the network configuration settings.
You see that a connect alias called " OR CL " has been created. This " ORC L " alias points to the container database with the service name " ORC L ".
Create a database connect alias called " P DBOR CL " and specify the network configuration settings to access the pluggable database " PDB ORC L " that we created during installation. Copy the following code and paste it in the tnsnames.ora file. If necessary, modify the host and port to match the values in the O RCL alias. PDBO RCL = (D ESC RIP TIO N = (AD DRE SS = (P ROTO COL = TCP )( HO ST = lo cal hos t) (P ORT = 1521 )) (CO NNE CT_ DA TA = ( SER VER = D EDIC ATE D) ( SER VIC E_ N A ME = pd bor cl) ) )
Save and close the file.
Connecting to Oracle Database Using SQL*Plus
In this section, you will be connecting to the pluggable database using the SQL*Plus utilit y. Open a command prompt and execute the following command. This command uses SQL*Plus to connect to the pluggable database as a system administrator: sqlp lus sy s/O ra cl e_1@ pdb orc l a s sy sdba ;
Note: If you had chosen a different administrative password during installation, replace Orac le_ 1 with the appropriate password in the command.
By default, the H R schema is locked. Execute the following command to unlock the H R schema. alte r u ser hr i de ntif ied by hr a cc ount un loc k;
Execute the following commands to connect to the HR schema in the pluggable database and query the EMP LOY EES table. conn ect hr /hr @p db orcl sele ct cou nt( *) f rom emp loy ees ;
Connecting with DB W e can communicat e with database by using a GUI tools or CUI tools. It depends on user t ype.
Types of users: 2 W e can devide the users of data base as f ollows. i) Non- Technical user: These users interact with any database t hrough GUI applications or web applications (pages ). ii) Technical user: These users interact s with any dat abase through any client tool SQL * PLUS window or any GUI (Graphical User Interf ace) Data Base tools like SQL DEVELOPER, PL/SQL DEVELOPER and TO AD. Technical users are Developers and DBA.
Parts of Oracle 1) SQL 2) PL/SQL
SQL (Structured query language) Oracle was the f irst company to release a product that used t he English based Structured Q uery Language, or SQL. This language allows end users to extract inf ormation themselves, without using a syst ems group f or ever y little report. Oracle’s quer y langua ge has structure, just as English or any other language has structure. It has rules of grammar and syntax, but they ar e basically the normal rules of careful English speech and can be readily understood. It is known as dat a base language. It is used to communicate with any database. W e can use this language constructs to wr ite SQL QUERIES. SQL * PLUS is a def ault client tool and acts as an interf ace bet ween client and database. SQL Def : It is a collection of pre def ined commands and constructs wit h syntactical rules . Request
processed on
Client
SQL Query Output
DB
1. Sql is a client tool to interact wi th ORACLE DB /any DB 2. Sql is to be installed in to the system whenever we have inst alled the db sof twar e. 3. Client [Technical] requests should be submitted in the f orm of "Queries". 4. Queries ar e executed by SQL ST MT EXECUTOR ( Oracle Db Engine ) 5. Queries ar e executed against database and output will be displayed on the Sql * plus window. Features of Sql * P l us:
At a time only one quer y is allowed to execute Sql queries ar e not case sensit ive Each quer y is terminated with ; ( semi colon ) SQL commands ar e ANSI standar d ( American Nat ional standard inst itute )
SQL COMMANDS Types of SQL commands: 1) DDL (data def inition language) commands: Used to create or change or delete any data base objects CREATE ALTER DROP TRUNCATE RENAME 2) DML (data manipulat ion language) COMMANDS These commands ar e u sed to enter new data/ chang ing exist ed data / deleting the dat a f rom table. INSERT UPDATE DELETE 3) DRL (data retrieval language) Command SELECT (logical command) 4) DCL ( DATA CONT ROL LANGUAGE) COMMANDS Used to control the access of data base objects . These commands are used by DBA (database administrator) GRANT REVO KE
5) TCL (TRANSACTION CONTROL LANGUAGE) CO MMANDS Used to save or cancel the actions /transactions made on table data. CO MMIT
ROLLBACK
SAVEPOINT
Login into data base Bef ore communicating with database the user should be authenticated by DBA by creat ing user account. HOW TO OPEN SQL * PLUS WINDOW ? N AVIG ATION: This navigation is usef ul to open sql * plus window START--> PROGRAMS--> ORACLE ora_Home1_11G --> Application development --> select Sql command Then it opens Sql command window, then submit user credent ials.
HOW TO CRE ATE A USER ACCOUNT? DBA ( Data Base Administrator) can CREATE and DELETE and manage user accounts NOTE: User name is not case sensitive, but password is case sensit ive f rom oracle 11g onwards General DBA credentials 1) In login window, submit the f ollowing details username: system password: manager Hoststring: orcl / oracle -----> It is the database ser vice name. 2) Now it opens Sql * Plus window In this window, the DBA has to writ e queries to create user account. Syntax: creat ing a new user account Create User <user_name> Identif ied by <password >; Ex:
create user dinesh identif ied by welcome; User created.
3) Giving permissions t o use the resources of data base GRANT resource, connect to
; Ex:
grant resource,connect to dinesh; Grant succeeded.
Ex:
Sql> exit; [ Disconnecting f rom database ]
Ex:
Connecting to data base as Di nesh. username: dinesh password: welcome Hoststring: orcl
LOG ON PROCEDURE: 1) Double click on sqlplus icon on desktop ( In oracle 8i/9i/10g ) Or Double click SqlPlus Command window 2) It opens a log on window, in this t ype the f ollowing username: password: hoststring:
pv welcome oracle / orcl
In Oracle 11g Com mand window, t ype as below. Enter Username: dinesh Password : welcome 3) It opens Sql * Plus window with pr ompt SQL>_ LOG OUT PROCEDURE: In the sql window, type the command EXIT. It disconnects t he current user f rom the data base and it close the sql window. Ex:
SQL>EXIT;
How to create user Account In Oracle -12c ? 1) Connect as DB A? Open Command window
C:\users\dinesh> sqlplus system/manager [ Hit enter ] Connected. 2) Check your User name SHOW USER; 3) Check Container Database SHOW CON_NAME; Note: If it is CDB$ROO T, THEN CH ANG E I T TO “PDBORCL” [ Pluggable DataBase ]. 4) Changing container to PDBO RCL ALTER SESSION SET CONTAINER=pdborcl; 5) Now check your container name, usuall y it is PDBORCL, Create new user account in this container database. Create User dinesh Identif ied by w elcome Quota 100M On Users; Grant connect,resource to dinesh; 6) Now connect as DI NESH CONN dinesh/ welcome@pdborcl ; D AT A MO DEL For any OLTP RDBMS the data model is E-R ( Ent it y-Relat ionship ) Model. Create an Ent it y-Relationship (ER) model is to visually r epresent the structure of a business database, where data equates to ent it ies (or objects) that are linked by def ined relat ionships expr essing dependencies and requirements. By nat ure it is an abstract visualization, the f irst step in the design process towards creating a logical and f unctional dat abase. Entit y : Ex customers
:
Any real time object is known as ent it y emp, dept, orders, transact ions, sales, pr oducts, ser vice,
Ex
:
For each object in the business we need to create a table. Ever y object is having some Properties. For each propert y we need to maintain a column.
Consider emp object Then table name is EMP
Propert ies of emp are as f ollows. eid mailid mobile
ename ......
sal
job
hiredate
Relation:
How business activit y is working bet ween 2 objects
Ex
emp<-->dept; company<-->product ; prod<-->sales ;
:
Sample E-R Model is as follows
gender
Entit y / Object represented with Rectangle Ellipses represents propert ies of object. Rhombus represents relat ion bet ween obj ects.
DDL command 1) CRE ATE It is used to create any data base object like tables , views, indexes, sequences, synonym s, users, f unctions, procedures, triggers, packages and so on. HOW TO CREATE A TABLE? syn:
CREATE TABLE ( DATATYPE (size), DATATYPE (size), : : : :, : : : :, );
Naming Rules Rules to f ollow bef ore specif ying names (Object names, Column Names and Variable Names). i) Each name should begins alphabet ii) Valid character set is a-z, A-Z,0-9,@,$, # and _ ( underscore) Ex:
Emp123 Emp_o11
iii) Names are not case sensitive iv) Already existed names are not allowed v) Pre def ined keywords (Reser ved W ords) are not allowed as names. vi) Blank space within a name is not allowed. vii) Max length of any name is 30 char act ers.
Ex:
valid_names ___________ prod_dt ls emp@sal emp123 emp_inf o emp_table
invalid_names ______________ prod dtls--Since blank space within the name emp.sal----Since " . " is not valid character 123emp-----Since Name is not beginning with alphabet emp-inf o---Since " -" is not valid char. table------Since " table " is a reser ved word
DATATYPES: The data type represents the t ype of data to be entered into a column and Db engine can assign memory f or the value entered into the column.
I)
String Data types:
These data t ypes support alphabets, digits and any sym bol f rom keyboard. 1) CH AR (size) It is used to store f ixed length character strings. By def ault the size is 1 character, and max size is 2000 chars or byt es. Ex:
empid, pnr number, bank account numbers, Policy Numbers and so on
2) V ARCH AR2 (size) It is used to store variable length char act er strings. No def ault size. we should specif y size and max size is 4000 chars /bytes. Ex:
emp names, addresses, descr iptions, cit y names,
3) LONG It is used to store variable length char data (similar to varchar 2 data type) but max size is 2 GB NOTE:
Only one long type column is allowed per a table.
4)NCH AR(size) It is similar to CHAR(SIZE) data t ype ,but it is used to support any Nat ional char set. 5)NV ARCH AR2(size) It is similar to VARCHAR2(SIZE) data t ype ,but it is used to support any National char set.
II)
Numeric Data types
The NUMBER dat at ype stores f ixed and f loat ing -point numbers. Numbers of virtually any magnitude can be stored and are guaranteed por table among dif f erent systems operating Oracle, up to 38 digits of precision. The f ollowing numbers can be stored in a NUMBER column:
Positive numbers in the range 1 x 10 - 1 3 0 t o 9.99..9 x 10 1 2 5 with up to 38 signif icant digits
Negative number s f rom -1 x 10 - 1 3 0 to 9.99..99 x 10 1 2 5 with up to 38 signif icant digits Zero
1) NUMBER (Precision, [Scale]) It is used to store numbers along with decimal point. 2) NUMBER (Precision ) It is used to store numbers wit hout decim al point. Precision represent s total number of digits in the value. Scale represents the max number of digits af ter decimal point. Total number of digit s in the value should be less than or equal to Precision value. Total number of digit s in the decimal part should be less than or equal to scale value. Note: The max value f or precision is 38 Ex:
prod_pr ice
number(7,2)
12.75 123.1 45621.08 99999.99 125 Ex:
123.4567--> Invalid 0.0972----> Invalid
Ex:
emp_sal
number(6)
12560 100 0 10 999999 III) Date data type D ATE The DATE datat ype stores point -in-time values (dates and t imes) in a table. The DATE dat atype stores the year (including the cent ury), the mont h, the day, the hours, the minut es, and the seconds (af ter midnight). Oracle can store dat es in the Julian era, ranging f rom Januar y 1, 4712 BCE through December 31, 4712 CE (Common Era). Unless BCE ('BC' in the f ormat mask) is specif ically used, CE dat e entries are the default. Oracle uses its own internal f ormat to store dates. Dat e data is stored in f ixed- length f ields of seven byt es each, corresponding to century, year, month, day, hour, minute, and second.
For input and output of dates, the standar d Oracle def ault dat e f ormat is DD MO N-YY, as below: '13-NOV-92'
You can change this def ault date f orm at for an instance with the parameter NLS_DATE_FORM AT . You can also change it during a user session with the ALTER SESSION st atement. To enter dates that are not in standard Oracle date f ormat, use the TO_DATE f unction with a f ormat mask: TO_DATE ('November 13, 1992', 'MO NTH DD, YYYY') Note: If you use the standard date f ormat DD -MO N-YY, YY gives the year in the 20t h centur y (f or example, 31 -DEC-92 is December 31, 1992). If you want to indicate years in any centur y other than the 20th cent ur y, use a dif f erent f ormat mask, as shown above.
Oracle stores t ime in 24 -hour f ormat --HH:MI:SS. By def ault, the time in a date f ield is 00:00:00 A. M. (midnight) if no time port ion is entered. In a time only entr y, the date portion def aults to the f irst day of the current month. To enter the time portion of a date, use the TO_DATE f unction with a f ormat mask indicating the time port ion, as in INSERT INTO birthdays (bname, bday) VALUES ('ANDY',TO_DATE('13 -AUG-66 12:56 A. M.','DD - MON-YY HH: MI A. M.')); DD-MON-YY DD Digits of date MO N First 3 chars of month name YY Last 2 digits of year Ex: Ex:
12-may-13 12/may/13 --It is not considered as a date f ormat --
IV) Binary Data types 1) R AW (size) It is used to store binar y data like images, thumb impressions, logos and so on. Max size is 2000 byt es < 2 KB 2) LONG R AW It is similar to RAW data t ype but max size is 2 GB
NOTE:
Only one long raw type column is allowed per a table.
V) LOB--Large Objects The LOB datat ypes BLOB, CLO B, NCLOB, and BFILE enable you to store large blocks of unstructured data (such as text, graphic images, video clips, and sound wavef orms) up to f our gigabytes in size. They provide eff icient, random, piece - wise access to the data. You can perf orm parallel queries (but not parallel DML or DDL) on LOB columns. LOB datat ypes diff er from LONG and LONG RAW datatypes in several ways. For example:
A table can contain multiple LOB columns but only one LONG column. A table containing one or more LOB columns can be partit ioned, but a table containing a LONG column cannot be partit ioned. The maximum size of a LOB is f our gigabytes, but the maximum size of a LONG is t wo gigabytes. LOBs support random access to data, but LONGs support only sequential access. LOB datat ypes (except NCLOB) can be at tributes of a user -def ined object type but LO NG datatypes cannot. Temporary LOBs that act like local variables can be used to perf orm transf ormations on LOB data. Temporary internal LO Bs (BLOBs, CLOBs, and NCLOBs) are cre ated in the user's temporar y tablespace and are independent of tables. For LONG datat ypes, however, no temporar y structures are available.
SQL statements def ine LOB columns in a table and LOB attr ibutes in a user def ined object type. W hen def ining LOBs in a table, you can explicit ly specif y the tablespace and stor age char acterist ics f or each LOB. LOB datat ypes can be stored inline ( within a table), out -of -line ( within a tablespace, using a LOB locator), or in an external f ile (BFILE datat ypes).
LOB TYPES -- 3 CLOB--CHAR LOB-- used to store character data BLOB-- BINARY LO B-- Used to stor e binary data NCLO B--Fixed length multi char large objects --Used to store both binar y and char data.
vi) BFILE Datat ype The BFILE datat ype stores unstruct ured binar y data in operat ing -system f iles outside the database. A BFILE column or attribute stor es a f ile locator that
points to an exter nal f ile containing the data. BFILEs can stor e up to f our gigabytes of data. BFILEs are read -only; you cannot modif y them. They support only random (not sequential) reads, and they do not participate in transact ions. The under lying operat ing system must maintain the f ile int egrity and durabilit y f or BFILEs. The database administrator must ensure that the f ile exists and that Oracle processes have operat ing -system read perm issions on the f ile. vii) ROWID and UROWID Datat ypes Oracle uses a ROW ID datat ype to store t he addr ess (rowid) of every row in the database.
Physical rowids stor e the addr esses of rows in ordinar y tables (excluding index-organized tables), clust ered tables, table partitions and subpart itions, indexes, and index par titions and subpartit ions. Logical rowids store the addresses of rows in index - organized tables.
A single datat ype called the universal row id , or UROW ID, s upports both logical and physical rowids, as well as rowids of f oreign tables such as non Oracle tables accessed through a gateway. A column of the UROW ID datatype can store all kinds of rowids. The value of the CO MPATIBLE initialization paramet er must be set to 8.1 or higher to use UROW ID columns.
Ex: W rite a query to create emp_inf o table with columns eid, ename, sal, jdate, desg, and gender wit h appropriat e data t ypes. CREATE TABLE EMP_INFO ( EID NUMBER (4), ENAME VARCHAR2 (20), SAL NUMBER (5), JDATE DATE, DESG VARCHAR2 (20), GENDER CHAR );
HOW TO INSERT DATA INTO A TABLE? INSERT: It is used to insert new recor ds in to the table. Assigns the values in to corresponding columns in the column list. If there is no colum n list, the f irst value is inserted into the f irst column def ined by
the CREATE TABLE statement, the second value is inserted into the second column, and so on. There must be one value f or ea ch column in the column list. The datatypes of the values being inserted must be compatible with the datat ypes of corresponding columns in the column list. Note:Character and date literals in the VALUES list must be enclosed by single quotes ('). Numeric literals are not enclosed by quotes. Syntax: INSERT INTO [(col1, col2,. ..., col n)] VALUES ( val1, val2,. ....,val n); Note: i) ii) iii)
If number of columns in the table and number of values inser ting in to the table are equal, then no need to specif y c olumn names while inserting records. Char, Varchar2 and date t ype values should be enclosed in Single Quotes. If Number of inserting values are less than the number of columns then we must specif y column names while insert ing records.
Example Insert ions: a) insert into emp_inf o (eid,ename,sal,jdate,desg,gender) values (1111,'Dinesh',75000,'23 -may-14',' developer',' M'); b) insert into emp_inf o(eid,ename,sal,jdate, desg,gender) values (1112,' Madhu',30000,'23 -may-14',' developer','F'); c) insert into emp_inf o(eid,enam e,sal,jdate, desg,gender) values(1191,'Xavier',10000,'02 -f eb-10','clerk','M'); d) insert into emp_inf o values(1110,'john',11700,'23 -may-14','developer',' M'); e) insert into emp_inf o Values(1121,'dilroop',61000,'21 -oct-13','developer','F'); f ) insert into emp_inf o Values (1120,'abhi',10000,'23 -may-14','admin',' M'); g) Insert into emp_inf o Values( ‘kiran’,’5000’); Error: Not enough values h) Insert into emp_inf o (ename,sal) Values( ‘kiran’,’5000’); The f ollowing examples show various f orms of INSERT statement: INSERT INTO bonus SELECT ename, job, sal, comm FRO M emp
W HERE comm > sal * 0.25; ... INSERT INTO emp (empno, ename, job, sal, comm, deptno) VALUES (4160, 'STURDEVIN', 'SECURITY GUARD', 2045, NULL, 30); ... INSERT INTO dept VALUES (m y_dept no, UPPER(my_dna me), 'CHICAGO');
Inserting records using & [ Substitution] operator: “ & “ is known as " substitut ion operator". Ex: Insert into emp_inf o Values('&eno', '&ename', '&sal', '&jdate', '&desg', '&gender'); enter enter enter enter enter
value value value value value
f or f or f or f or f or
eno: enam e: sal: jdate: desg:
232 hari 23000 22-oct-11 admin
1 row created. In sql * plus window sql> / [ to re-execut e the recent Quer y ] enter enter enter enter enter
value value value value value
f or f or f or f or f or
eno: 231 enam e: samuel sal: 12000 jdate: 10-may-14 desg: admin
1 row created. sql > / INSERT ALL The Oracle INSERT ALL st atement is used to add multiple rows with a singleINSERT statement . The rows can be inserted into one table or multiple tables using only one SQL command. Ex: Say you have a table COLORS wi th this str ucture:
Name ---------------NAME CATEGORY
Type -----------VARCHAR2(30) VARCHAR2(10)
Ex: insert all into colors(name, category) into colors(name, category) into colors(name, category) into colors(name, category) into colors(name, category) select * from dual;
values('yellow', 1) values('red', 1) values('blue', 1) values('yellow', 2) values('blue', 2)
NULL V ALUES: A missed value in a column is known null value. Null value is not equal to zero or space or other null values. NULL values represent missing or unknown data. NULL values are used as placeholders or as t he def ault entr y in columns to indicate that no actual data is present. The NULL is unt yped in SQL, meaning that it is not an integer, a character, or any other specif ic data t ype. INSERTI NG NULL VALUES: 2 Methods 1) Implicit insertion: If we miss a value in a column the n it is dynam ically maint ains a null value at that place. To insert like this, we need to specif y column names while using insert command. Ex:
Insert into emp_inf o (eno,ename,sal,desg ) Values(555,'john',12000,'salesman');
2) Explicit insertion: Just specif y NULL keywor d at the place of missed values. Here no need to maintain column names while inserting data. Ex: Insert into emp_inf o Values (888,'martin', 12000,'salesman',null,null,NULL,Null);
DESCCRIBE / DESC This command is used to display table st ructure. A table structure contains column names, dat a types and sizes.
SYNTAX DESC ; EX:
describe
emp_inf o;
or desc emp_inf o; Ex: SQL> desc emp; Name Null? Type ----------------------------------------- -------- ---------------------------EMPNO NOT NULL NUMBER(4) ENAME VARCHAR2( 10) JOB VARCHAR2(9) MGR NUMBER(4) HIREDATE DATE SAL NUMBER(7,2) CO MM NUMBE R(7,2) DEPTNO NUMBER(2) HOW TO DISPLAY LIST OF TABLE NAMES? EX:
SELECT * FRO M TAB;
DEFAULT Keywor d W e can create a table with DEFAULT Clause. W hile inserting data in to table , we don’t need to enter any v alue in to def ault column. The column is populated with the value supplied along with DEFAULT keyword. Syntax: CREATE TABLE < table_name> ( Col1 datat ype(size) DEFAULT ‘Value’ “ “ , “ “ , );
,
Example: Create Table cust omer ( Cno number(2), Cname var char2( 20), Cit y var char2( 10) DEFAULT ‘Hyderabad’ ); Example: Insert ing values in to Cust omer table
Insert into customer( cno,cname) values (1,’kiran’); Insert into customer( cno,cname) values (2,’Madhu’); Insert into customer values (3,’dinesh’,Null); Insert into customer values (4,’john’, ’Texas); Ex:
Selecting data f rom Customer table.
Select * f rom tab; 1 2 3 4
Kiran Madhu Dinesh john
Hyder abad Hyder abad Texas
Note:By Def ault the colum n value is “Hyderabad”. If we submit a dif f erent value or Null value then the def ault column is populated with given value.
D AT A RETRIEV AL COMM AND ( DRL COMM AND ) SELECT It is used to retrieve a logical copy of required data f rom a table or columns. Syntax:
SELECT col1, col2,...., coln / * FRO M table_name;
Retrieving data from single column Ex:
Get employee names?
SQL> select ename f rom emp; ENAME ---------SMITH ALLEN W ARD JONES MARTIN BLAKE CLARK SCOTT
KING TURNER ADAMS ENAME ---------JAMES FORD MILLER 14 rows selected. SQL> Retrieving data from multiple columns Ex:
Get emplyee id, nam es and salaries? SQL> select empno, ename,sal f rom emp;
EMPNO ENAME SAL ---------------------------7369 SMITH 800 7499 ALLEN 1600 7521 W ARD 1250 7566 JONES 2975 7654 MARTIN 1250 Retrieving All Column data Ex: SQL> set linesize 300; SQL> select * f rom emp; Note: “ * ” represents all columns. E MP N O E N A ME JOB MG R H I R E D A TE SAL COMM D E P TN O ---------- ---------- --------- ---------- --------- ---------- ---------- ---------7 3 6 9 S MI TH CLERK 7902 17 -DEC-80 800 20 7499 ALLEN S A L E S MA N 7698 20 -FEB-81 1600 300 30 7521 W ARD S A L E S MA N 7698 22 -FEB-81 1250 500 30 7566 JONES MA N A G E R 7839 02 -APR-81 2975 20 7 6 5 4 MA R TI N S A L E S MA N 7698 28 -SEP-81 1250 1400 30 7698 BLAKE MA N A G E R 7 8 3 9 0 1 - M A Y- 8 1 2850 30 7782 CLARK MA N A G E R 7839 09 -JUN-81 2450 10 7788 SCOTT A N A L YS T 7566 19 -APR-87 3000 20 7839 KING PRESIDENT 17 -NOV-81 5000 10 7844 TURNER S A L E S MA N 7698 08 -SEP-81 1500 0 30 7 8 7 6 A D A MS CLERK 7 7 8 8 2 3 - MA Y - 8 7 1100 20 E MP N O E N A ME JOB MG R H I R E D A TE SAL COMM D E P TN O ---------- ---------- --------- ---------- --------- ---------- ---------- ---------7 9 0 0 J A ME S CLERK 7698 03 -DEC-81 950 30 7902 FORD A N A L YS T 7566 03 -DEC-81 3000 20 7 9 3 4 MI L L E R CLERK 7782 23 -JAN-82 1300 10
1 4 r o ws s e l e c t e d . SQL>
Retrieving column data in user required order (Instead of Physical order of table colum ns ) Ex: Display employee join_dates, ename, designit ions and salar ies? SQL> select hir edat e, ename, job, sal f rom emp; HIREDATE ENAME JOB --------- ---------- --------- ---------17-DEC-80 SMITH CLERK 20-FEB-81 ALLEN SALESMAN 22-FEB-81 W ARD SALESMAN 02-APR-81 JO NES MANAGER 28-SEP-81 MARTIN SALESMAN 01- MAY-81 BLAKE MANAGER 09-JUN-81 CLARK MANAGER 19-APR-87 SCOTT ANALYST 17-NOV-81 KING PRESIDENT 08-SEP-81 TURNER SALESMAN 23- MAY-87 ADAMS CLERK
SAL
HIREDATE ENAME JOB --------- ---------- --------- ---------03-DEC-81 JAMES CLERK 03-DEC-81 FORD ANALYST 23-JAN-82 MI LLER CLERK
SAL
800 1600 1250 2975 1250 2850 2450 3000 5000 1500 1100
950 3000 1300
14 rows selected. SQL>
DISPLAYING COLUMN DATA WITH USER DEFI NED TITLES: From above examples , By def ault the column names are output titles in the output. W e can also change these output titles as f ollows. Syntax-1
select colname "tit le", colname "t itle".. . f rom table;
“In this format w e can also maint ain spaces in the titles.” Ex:
select ename "Employee Name", desg " Job of Employee" f rom employee_inf o;
employee name job of employee -------------------- -------------------dinesh rao developer smitha panday developer madhu admin madhu admin Allen developer king salesman torjan clerk john salesman martin salesman 9 rows selected Syntax-2 Select col-name t itle, colname t itle, ….. From Table_name; “In this st yle w e cannot maintain spaces in the title” Ex: SQL> select ename empname, sal salar y from emp; EMPNAME SALARY ------------------SMITH 800 ALLEN 1600 W ARD 1250 JONES 2975 MARTIN 1250 BLAKE 2850 CLARK 2450 SCOTT 3000 KING 5000 TURNER 1500 ADAMS 1100 14 rows selected. SQL> Syntax-3 Select col-name AS title, col -name AS t it le,. . . From table_name; “In this st yle w e cannot maintain space s in the title” Ex:
SQL> select empno as eid, ename as empnames f rom emp; EID EMPNAMES ---------- ---------7369 SMITH 7499 ALLEN 7521 W ARD 7566 JONES 7654 MARTIN 7698 BLAKE 7782 CLARK 7788 SCOTT 7839 KING 7844 TURNER 7876 ADAMS EID EMPNAMES ---------- ---------7900 JAMES 7902 FORD 7934 MILLER 14 rows selected. SQL> Assignments: Create the f ollowing tables with your own assumpted relevant column names and dat atypes. i) Creat e a table Item_dtls ( Electronics ) ii) Create a table Sales_dt ls iii) creat e a table manuf acturers iv) Try to insert at least 10 records in the above tables v) Try to insert at least 2 records with null values
Clauses DISTINCT clause ORDER BY clause Ex: Consider the f ollowing table and records create table departm ents ( did number(2),
dname varchar2(20) , cit y varchar 2(20) ); insert into departments values(10,' Production','Dallas'); insert into departments values(10,' Production','Dallas'); insert into departments values(10,' Production','Dallas'); insert into departments values(10,' Production','Dallas'); insert into departments values(10,' Production','Dallas'); insert into departments values(10,' Production',' Dallas'); insert into departments values(20,' Sales','Texas'); insert into departments values(20,' Sales','Texas'); insert into departments values(20,' Sales','Texas'); insert into departments values(20,' Sales','Texas'); insert into departments values(30,'Finance','Chicago'); insert into departments values(30,'Finance','Chicago'); insert into departments values(30,'Finance','Chicago'); insert into departments values(30,'Finance','Chicago'); insert into departments values(30,'Finance','Chicago');
DISTINCT Clause It will display dif f erent / unique values f rom the column and display unique recor ds f rom the table.
it will also
i)Getting Unique values f rom single column. Syntax: select distinct colname from ; ii) Getting unique combinat ion of values f rom multiple columns. Syntax: select distinct colname1,colname2,.... f rom ; iii) Getting Unique records f rom the table. Syntax: select distinct * f rom ; Ex:
display list of diff erent designitions?
SQL> select distinct job f rom emp; JOB --------CLERK SALESMAN PRESIDENT MANAGER ANALYST SQL> Ex:
display distinct department names? select dist inct(dnam e) f rom dept;
output: Product ion Sales Finance Ex:
Get unique recor ds from the above table? or Display recor ds f rom the above table without duplicates? select dist inct did, dname,cit y f rom dept;
10 20 30
product ion Sales Finance
chicago Texas Dallas
ORDER BY Clause This clause is used t o display the column data or table data in ascending / descending or sorting [ a to z] / reverse sorting [z -a] order. Syntax:
select col1,..... / * f rom Table Name order by col1, col2,. ..... [asc / desc ];
Note: i) By def ault it will display a to z data or ascending order dat a. ii) In case of char data, if there exists upper and lower case data then order by gives highest priorit y to the upper case data and next prior it y goes to lower case data. Since upper case data A -Z ascii values are 65 -90 lower case data a - z ascii values are 91 -122
Ex:
Display employee names in alphabet ical ( sorting) order? select ename f rom emp ORDER BY ename;
sample output: ENAME ---------ADAMS ALLEN BLAKE CLARK FORD ... Ex:
Display employee names in rever se order ? select ename f rom emp ORDER BY ename DESC;
sample output: ENAME ---------W ARD TURNER SMITH SCOTT MILLER ... Ex:
display ename,sal, desg on the order of salar y? select ename, sal, job f rom emp order by sal;
sample output: ENAME SAL JOB ---------- ---------------------- --------SMITH 800 CLERK JAMES 950 CLERK ADAMS 1100 CLERK W ARD 1250 SALESMAN MARTIN 1250 SALESMAN NOTE: W e can also wr ite the column position number in the ORDER BY clause. The column position num ber should be f rom the select column list, not f rom table column list. ORDER BY clause on more than one column: Here the f irst prior it y of o rder by clause given to f irst column, if f irst column having duplicates then order by prior it y goes to second column and so on.
Ex:
display sal and ename from emp based on order of salar ies? select sal, ename f rom emp order by sal;
sample output: SAL ENAME ---------------------- ---------800 SMITH 950 JAMES 1100 ADAMS 1250 W ARD-------> 1250 MARTI N ----> 1300 MILLER 1500 TURNER 1600 ALLEN 2450 CLARK 2850 BLAKE 2975 JONES 3000 SCOTT ----> 3000 FORD ----> 5000 KING Ex: display sal and ename from emp based on order of salar ies and employee names? select sal, ename f rom emp order by sal,ename; sample output: SAL ENAME ---------------------- ---------800 SMITH 950 JAMES 1100 ADAMS 1250 MARTI N ------> 1250 W ARD --------> 1300 MILLER 1500 TURNER 1600 ALLEN 2450 CLARK 2850 BLAKE 2975 JONES 3000 FORD ------> 3000 SCOTT -----> 5000 KING Ex: Get employee data based on deptno order and f rom each dept least salr ied emp to highest salar ied employee? SQL> select empno, ename,sal, deptno f rom emp order by deptno, sal;
EMPNO ENAME SAL DEPTNO ---------- ---------- ---------- ---------7934 MILLER 1300 10 7782 CLARK 2450 10 7839 KING 5000 10 7369 SMITH 800 20 7876 ADAMS 1100 20 7566 JONES 2975 20 7788 SCOTT 3000 20 7902 FORD 3000 20 7900 JAMES 950 30 7654 MARTIN 1250 30 7521 W ARD 1250 30 EMPNO ENAME SAL ---------- ---------- ---------- ---------7844 TURNER 1500 7499 ALLEN 1600 7698 BLAKE 2850 14 rows selected. SQL>
DEPTNO 30 30 30
OPERATORS Arithmetic Operators:
+
-
*
/
These operat ors are used to perf orm Arit hmetic calculations on user's own data and table data. DU AL table The DU AL table is a special one -row, one-column table present by def ault inOracle and other database installat ions. In Oracle, the table has a single VARCHAR2(1) colum n called DUMMY that has a value of 'X'. It is suit able f or use in select ing a pseudo column such as SYSDATE or USER. It is a system def ined table which contains only one column t o perf orm calculations on user s own data. Arit hmetic Calculat ions On Users dat a: Ex:
select 200+300 f rom dual; 500
Ex:
select (90000*10)/100 "10% of 90000" f rom dual; 10% of 90000 -----------9000
Ex:
select 2000+(0.10*5000) -300 " Af ter calculat ion" f rom dual; 2200
Arit hmetic Calculat ions On Table data: Ex:
Display emp salar ies and 2% of salar y as TA? select sal " Basic Sal", (0.02*sal) " TA" from emp;
Ex:
Display employee salar ies, 2% as TA, 5% as DA, 10% HRA, 4% as CO MM and f inal salary?
select Sal " Basic", (0.02*Sal) " TA", (0.05*sal) "DA", (0.10*sal) "HRA", (0.04*sal) " Comm" , (Sal + (0.02*Sal) + (0.05*sal) + (0.10*sal) + (0.04*sal) ) " Final Salar y"
f rom emp; output Basic TA DA HRA Comm Final Salar y ---------- ---------- ---------- ---------- ---------- ------------800 16 40 80 32 968 1600 32 80 160 64 1936 1250 25 62.5 125 50 1512.5 2975 59.5 148.75 297.5 119 3599.75 1250 25 62.5 125 50 1512.5 2850 57 142.5 285 114 3448.5 2450 49 122.5 245 98 2964.5 3000 60 150 300 120 3630 5000 100 250 500 200 6050 1500 30 75 150 60 1815 1100 22 55 110 44 1331 Basic TA DA HRA Comm Final Salar y ---------- ---------- ---------- ---------- ---------- ------------950 19 47.5 95 38 1149.5 3000 60 150 300 120 3630 1300 26 65 130 52 1573 14 rows selected. SQL>
RELATIONAL OPERATORS: These are used to compare values by specif ying condit ions on the columns.
<
>
=
<=
>=
WHERE clause: In select quer y we can wr ite condit ions in this clause. Syntax: select cl1, cl2,......,cl -n / * f rom table_name where order by cl1, cl2,...,cln [asc/desc]; Ex:
display salar ies below 2000? select sal " emp sal below 2000" f rom emp_inf o wher e sal < 2000;
emp sal below 2000 ------------------800 1600 1250 1250 1500 1100 950 1300 8 rows select ed. Ex:
display the det ails of account ing dept? select * f rom dept where dname='ACCOUNTING';
DEPTNO DNAME LOC ---------- -------------- ------------10 ACCO UNTING NEW YORK SQL> Ex:
display the det ails of managers? select * f rom emp where job=' MANAGER';
EMPNO ENAME JOB DEPTNO ---------- ------------------------- ---------7566 JONES MANAGER 20 7698 BLAKE MANAGER 30 7782 CLARK MANAGER 10
MGR HIREDATE
SAL
---------- ---------
----------
7839 02 -APR- 81
2975
7839 01 - MAY-81
2850
7839 09 -JUN-81
2450
SQL> Ex:
display employee name and sal of empno 7788? select ename,sal f rom emp where empno=7788;
Ex:
Display employee details who joined bef ore 1st jan 1981? select * f rom emp where hir edate < '01 -jan-81';
COMM --
SQL> select * f rom emp where hiredate < '01 -jan-81'; EMPNO ENAME JOB MGR HIREDATE SAL DEPTNO ---------- ---------- --------- ---------- --------- ---------- ---------- ---------7369 SMITH CLERK 7902 17 -DEC-80 800 20
COMM
SQL>
ASSIGNM ENTS: Consider the below tables with est imated columns and then pr actise below questions. CUST_DTLS CUST_Act_DTLS ACT_TYPES_I NFO PROD_DTLS EMP DEPT
1) Fetch all clerks inf ormation 2) Display all departments inf ormation located at CHICAGO? 3) Display product details manuf actured in the current year only? 4) Get the details of cutomers accounts who ope ned the accounts bef ore this year? 5) Get all SALARY account details? 6) Display custom er names and mobile numbers f rom the city 'Texas'? select cname,mobile from cust_dtls wher e cit y='Texas'; 7) Get the inf ormation of Trading account? 8) Display only Expir ed product details? select * f rom prod_dtls where exp<sysdate; 9)Display dif f erent department numbers from emp table? 10)Display customer details f rom the cit y HYD? 11)Display customer account details wit h balance below 5000? 12)Display mobile details? 13)Display products having min warrent y 2 years? 14)Display products with below 10000 cost?
SPECIAL OPERATO RS : BETWEEN This operat or suppor ts specif ic range of values f rom a column . This is applicable on NUMERIC, CHAR and DATE data t ype columns Syntax:-1
BETWEEN
select cl1, cl2,......,cl -n / * f rom table_name where < ColumnName> BETW EEN <start_value> AND <end_value> order by cl1, cl2,...,cln [asc/desc]; IN This Operator supports specif ic list of values f rom a column. This is applicable to Numeric, date & character data t ype columns. Syntax: select cl1, cl2,......,cl -n / * f rom table_name where < ColumnName> IN(val1, val2, val3,.....) order by cl1, cl2,...,cln [asc/desc];
IS NULL This Operator is used to check the column value is null or not , if it is null display output, Otherwise it won’t display output. Syntax:
IS NULL
select cl1, cl2,......,cl -n / * f rom table_name where < ColumnName> IS NULL order by cl1, cl2,...,cln [asc/desc]; Note: It works on only null values and it is independent of data type of column LIKE The LIKE condit ions specif y a test involving pattern matching. W hereas the equalit y oper ator (=) exactly matches one character value to another, the LIKE conditions match a portion of one character value t o another by searching the f irst value f or the pattern specif ied by the second. LIKE calculates strings using characters as def ined by the input character set. In the f ollowing synt a x ESCAPE symbol is any char except _ & % . A charact er preceeding escape character remove the meaning of that character.
Syntax:
LIKE
select cl1, cl2,......,cl -n / * f rom table_name where < ColumnName> LIKE'string' ESCAPE ‘liter al’; order by cl1, cl2,...,cln [asc/desc]; LIKE: It uses 2 symbols _ (underscore) %
represents anyone char
represents any number of chars
EXAMPLES: Ex:
display salar ies bet ween 2000 and 3000 in ascending order? select sal f rom emp where sal bet ween 2000 and 3000 order by sal;
SQL> select sal f rom emp where sal bet ween 2000 and 3000 order by sal; SAL ---------2450 2850 2975 3000 3000 SQL> Ex:
display employee details who is joined in 1981? select * f rom emp where hir eda te bet ween '01 -jan-81' and '31 -dec-81'; or select * f rom emp where hir edate like'%81';
E M P NO E N AM E JOB MG R H IR ED AT E S AL CO M M DE PT NO - - - - - - - - - - - --------- ------ --- - - - - - - - - - - ---- ----- - -- ------- ---- ------ - - - - - - - - - 74 9 9 A LL E N S A L ES M A N 7 6 98 2 0 - F E B- 81 1 6 00 300 30 75 2 1 W ARD S A L ES M A N 7 6 98 2 2 - F E B- 81 1 2 50 500 30 75 6 6 J O N E S M AN AG E R 78 3 9 02 - A P R- 81 2 9 75 20 76 5 4 M ART I N S A LE S M A N 7 6 98 28 - S E P- 8 1 12 5 0 14 0 0 30 76 9 8 B L A K E M AN A G E R 7 83 9 0 1 - MA Y- 8 1 2 85 0 30 77 8 2 CL A R K M AN AG E R 78 3 9 09 - J UN - 8 1 2 45 0 10 78 3 9 K ING P RE S ID E NT 17 - NO V- 8 1 5 0 00 10 78 4 4 T UR N ER S A LE S M A N 7 6 98 08 - S E P- 8 1 15 0 0 0 30 79 0 0 J A M E S C L ER K 7 69 8 0 3 - DE C- 81 9 50 30
79 0 2 F O R D
A NA L YST
7 5 66 03 - D EC- 8 1
30 0 0
20
10 r o ws s e l ec t e d. SQ L >
Ex:
display emplyees working like clerks and managers? select * f rom emp where job IN('CLERK','MANAGER');
E M PNO EN A M E JOB MG R H IR ED AT E S AL CO M M DE PT NO - - - - - - - - - - - --------- ------ --- - - - - - - - - - - ---- ----- - -- ------- ---- ------ - - - - - - - - - 73 6 9 S MIT H C L ER K 7 90 2 1 7 - DE C- 80 8 00 75 6 6 J O N E S M AN AG E R 78 3 9 02 - A P R- 81 2 9 75 76 9 8 B L A K E M AN A G E R 7 83 9 0 1 - MA Y- 8 1 2 85 0 77 8 2 CL A R K M AN AG E R 78 3 9 09- J UN - 8 1 2 45 0 78 7 6 A D AM S CL E R K 77 8 8 2 3 - MA Y- 8 7 1 10 0 79 0 0 J A M E S C L ER K 7 69 8 0 3 - DE C- 81 9 50 79 3 4 MI L L ER C L ER K 7 78 2 2 3 - J AN - 8 2 1 30 0
20 20 30 10 20 30 10
7 r o ws s el ec te d . SQ L >
Ex: display employee names and salar ies who is getting any one of f ollowing salar y? 1250,3000,5000 select ename,sal f rom emp where sal in( 1250,3000,5000); ENAME ---------W ARD MARTIN SCOTT KING FORD
SAL ---------1250 1250 3000 5000 3000
SQL> Ex:
display employee id, name,sal,comm who is not getting comission? select empno,ename,sal,comm f rom emp where comm is null;
EMPNO ENAME SAL COMM ---------- ---------- ------------------7369 SMITH 800 7566 JONES 2975
7698 7782 7788 7839 7876 7900 7902 7934
BLAKE CLARK SCOTT KING ADAMS JAMES FORD MILLER
2850 2450 3000 5000 1100 950 3000 1300
10 rows selected. SQL> Ex:
dispaly 3 digit salaries? select sal f rom emp where sal like'___';
SQL> select sal f rom emp where sal like'___'; SAL ---------800 950 SQL> Ex: display names of emps begins s? select ename f rom emp where ename like'S%'; ENAME ---------SMITH SCOTT SQL> Ex:
display employees joined in 87? select * f rom emp where hir edate like'%87';
22-may-87 02-f eb-87 11-oct-87 Ex: Execut e the f ollowing queries and ver if y the outputs select * f rom emp; select * f rom emp where sal bet ween 1000 and 2000; select * f rom emp where sal not bet ween 1000 and 2000;
select * f rom emp where hir edate bet ween '01 -jan-81' and '31 -dec-81' order by hiredate; select * f rom emp where hir edate not bet ween '01 -jan-81' and '31-dec-81' order by hiredate; select * f rom emp where job in( ' CLERK','SALESMAN'); select * f rom emp where job not in('CLERK','SALESMAN'); select * f rom emp where deptno in(10,20) ; select * f rom emp where hir edate in('19 - apr-87','23-jan-82') ; create table sample as select * f rom emp; select * f rom sample; update sample set deptno=null where em pno in(7499,7566,7698,7788,7876,79007902, 7934); --Display 3-digit salaries select sal f rom emp where sal like'___'; --Display salar ies begining with digit "2"? select sal f rom emp where sal like'2%'; --Display employee names begins with " J" and ends with "S" ? select ename f rom emp where ename like'J%S'; --Display 4-char leng th employee names? select ename f rom emp where ename like'____'; --Display 4-char leng th employee names ends with " D"? select ename f rom emp where ename like'___D'; --Display employee names,salar ies, hiredates joined inn the year " 81"? select ename,sal,hir edate f rom emp where hiredate like'%81' ;
RELATION NEGATION OPERATORS: The f ollowing operat ors are the negation operators f or the above special operators. !=
(or) <> (or)
NO T BETWEEN NO T LIKE
^=
(NOT EQ U AL TO)
NO T IN IS NO T NULL Ex:
Display all emps det ails except SALESMAN? select * f rom emp where job<>'SALESMA N';
EM P NO E N AM E JOB MG R H I RE D AT E SAL COMM DE PT NO - - - - - - - - - - - --------- ------ --- - - - - - - - - - - ---- ----- - -- ------- ---- ------ - - - - - - - - - 73 6 9 S MIT H C L ER K 7 90 2 1 7 - DE C- 80 8 00 75 6 6 J O N E S M AN AG E R 78 3 9 02 - A P R- 81 2 9 75 76 9 8 B L A K E M AN A G E R 7 83 9 0 1 - MA Y- 8 1 2 85 0 77 8 2 CL A R K M AN AG E R 78 3 9 09 - J UN - 8 1 2 45 0 77 8 8 S CO T T A NA L YST 7 5 66 19 - A PR - 8 7 3 00 0 78 3 9 K ING P RE S ID E NT 17 - NO V- 8 1 5 0 00 78 7 6 A D AM S CL E R K 77 8 8 2 3 - MA Y- 8 7 1 10 0 79 0 0 J A M E S C L ER K 7 69 8 0 3 - DE C- 81 9 50 79 0 2 F O R D A NA L YST 7 5 66 03 - D EC- 8 1 30 0 0 79 3 4 MI L L ER C L ER K 7 78 2 2 3 - J AN - 8 2 1 30 0
20 20 30 10 20 10 20 30 20 10
10 r o ws s e l ec t e d. SQ L >
Ex:
Display employee details not joined in the last year?
select * f rom emp where hir edate NOT BETW EEN '01 -jan-14' and '31dec-14';
ASSIGNMENTS: 1) Display custom er account details whose balance is at least 10000 and at most 100000? 2) Display unknown account details? 3) Display custom er details whose gender is unknown? 4) Display custom ers from the citites 'TEXAS ' and 'CHICAG O'? 5) Display Product details manuf actured in januar y of this year? 6) Display product details whose warrenty is f inished in the last year? 7) Display custom er names having a char 'K'? 8) Display custom er details who is living in 6 char length cit ies?
LOGICAL OPER ATO RS: These Operators are used to specif y Mult iple conditions in the where clause. AND Display output if all conditions are true. If any one condition was f ailed then it will not display output.
OR
Display output if anyone condition is true. If all conditions are f alse then it will not display output.
Syntax: SELECT cl1,cl2,....., / * FRO M W HERE [ AND / OR ] [ AND / OR ] [ AND / OR ]....... ORDER BY cl1, cl2,. ..... [ ASC /DESC]; Ex:
Display manager det ails getting above 2500 sal? select * f rom emp where job=' MANAGER' and sal>2500;
Ex: Display clerks and salesman details if their salar y at least 1000 and atmost 1500? select ename,sal,job from emp where job in(' CLERK','SALESMAN') AND sal bet ween 1000 and 1500; Ex:
Display salar y account details having below 100000 balance? select * f rom cust_act_dtls where act_t ype='sal' and bal <100000;
Assignments: Ex: Display tablet or mobile inf ormation if their cost min 10000 and max 15000? Ex: Display product details if they were manuf actured in current year and min cost 2000 and max cos t 10000?
ACTNO
CUST_ACT_DTLS ------------ACT_TYPE ACT_OPEN_DT ACT_BAL
CUST_ID
Ex: Display "male" customers f rom "texas" and "f emale" customer s f rom "chicago"?
CUST_ID CUST_MOBILE
CUST_DTLS --------CUST_NAME
CUST_CITY CUST_GENDER
select * f rom cust_dtls where (gender='male' and cit y='texas') or ( gender='f emale' and cit y='chicago'); EX: Display employee details joined in 87 year or working under deptno 10? select * f rom emp where hir edate like'%87' or deptno= 10; Ex: Display trading account details having min balance 10000 and savings account details having min balance 100000?
DML COMMANDS
UPD ATE It is used to update old values with new values within the table. By def ault it updates all values in a column. W e can also update column values by ver if ying condit ions. Updat ing single column value: Syntax:
update set colname= value / expression / select. . .Query where ;
Note: W ithout condition update command change all values in t he column.
Updat ing mult iple column values: Syntax: update set colname1= value / expression, colname2= value / expression, colname3= value / expression :
wher e ; Ex:
update the commission of 7 369 as 500? update emp set com m=500 where empno=7369;
Ex:
update all emps commissions as 1000? update emp set com m=1000;
Ex:
update the salesman salar y wit h 20% incr ement , change their designition as Sr. SALES who joined before 2005? update emp set sal=sal+(0. 20*sal), job='Sr.SALES' where job=' SALESMAN'
AND hiredate < '01 -jan'05';
Ex: Update the customer account details with 25% addit ional interest? Updat e cust_act_dt ls Set act_bal= act_bal + (act_bal*0.25); DELETE: This command is used to delete the records f rom the table. By def ault it deletes all the recor ds. W e can also delete the records condit ionally. Syntax: delete f rom where ; Ex:
delete all customer details? delete f rom cust_dtls;
Ex:
delete employees inf ormation who is not getting any comm ission? delete f rom emp where comm is null;
Note: W e can get the back the deleted records / data wit hin the curr ent session by using ROLLBACK.
DDL COMMANDS:CRE ATE ALTER DROP TRUNC ATE REN AME ALTER: This command is used to change the str ucture of the table by i) adding new columns syn: alter table ADD ( datat ype(size), datat ype(size), : : );
ii) deleting existed c olumns syn:
alter table DROP COLUMN ;
iii) changing the datat ype and size (increasing / decreasi ng) of column syn: alter table MO DIFY new_datat ype(new_size); Note: A) If the column is empty then we can do the f ollowing W e can change f rom any data t ype to any data t ype. W e can change any size to any size . B) If column is not empty then, Number t ype and CHAR t ype column size can be decreased but can be incr eased. Data t ypes can be change d f rom CHAR TO VARCHAR2 AND vice versa, NUMBER(p) to NUMBER(p,s) BUT NOT VICE VERSA. iv) Rename a column w ith New Name.
syn:
alter table RENAME COLUMN to ;
Ex:
alter table emp rename column ename to empnames;
3) DROP This command is used to delete any dat a base object. How to delete the table? Ex:
drop table ;
4) TRUNC ATE : It deletes all the data f rom the table, it cannot deletes the partial data f rom a table. W e cannot get back or restore deleted data. Syntax:
truncate table ;
Ex:
truncate table customers;
Ex:
let us consider a table stud_dt ls with columns rno ,sname, fee
Ex:
rno
stud_dt ls --------sname
number(2)
var char2( 5)
f ee number(5)
change the above table to maintain course name f or each student? alter table stud_dt ls add course_nam e varchar2(10);
Ex: change the above table to maintain gender and mobile number f or each student? alter table stud_dt ls add ( gender char, mobile number( 10) ); Ex:
f rom the above table delet e student mobile number column?
alter table stud_dt ls drop column mobile;
Ex:
increase the st udent name column size t o 20? alter table stud_dt ls modif y sname varchar2(20);
Ex:
change the name of column sname to stud_name? alter table stud_dt ls RENAME sname to STUD_NAME;
NOTE: W e can also change the table name by using ALTER command as f ollows. ALTER TABLE emp RENAME emp empinf o;
TCL ( TR ANS AC TION CONTROL L ANGUGE ) COMM ANDS: Generally, DML oper ations on table data are considered as tr ansact ions. Transaction Control L anguage(TCL) commands are used to manage transac tions in da tab ase.These a re used to manage the change s made b y DML statements . It also all ows s ta tements to be grouped toge the r in to logical transac tions. 1) COMMIT It is used to make permanent the user tr ansact ions(DML operations) on the table. Note: Once a transact ion made permanent then we cannot cancel it 2) ROLLB ACK: It is used to cancel t he user transaction. 3)S AVEPOINT This command is used to save a s et of transact ions under a name temporarily. So that if we want to rollback transactions to a save point we can rollback up to a save point. S AVEPOINT ; Examples: create table cust ( cid char(3), cname varchar2 (20) );
insert into cust values('c00','Sanju'); insert into cust values('c01',' Manoj'); select * f rom cust; rollback; select * f rom cust; insert into cust values('c00','Sanju'); insert into cust values('c01',' Manoj'); commit; select * f rom cust; rollback; select * f rom cust; Ex-2: Insert into cust values('c02','hellen','Female',' xx','hyd'); delete f rom cust where cname='Sanju'; savepoint s1; update cust set phone='aa' wher e cname='hellen'; savepoint s2; delete f rom cust; select * f rom cus t; rollback to s2; rollback;
DCL commands: [ Data Control Language commands ] GR ANTI NG AND REVOKING PERMISSI ONS: Oracle provides extensive secur it y f eatures in or der to saf eguard inf ormation stored in its tables f rom unauthorized viewing and damage. Depending on a user's status and responsibilit y, appropr iate rights on Oracle' s resources can be assigned to the user by the DBA. The rights that allow t he use of some or all of oracle's resour ces on the Ser ver ar e called PRI VILEGES. Objects that are crea ted by a user are owned and controlled by that user. If a user wishes to access any of the object s belonging to another user, the owner of the object will have to give per missions f or such access. This is called GR ANTING of PRIVILEGES.
Privileges once gi ven can be taken back by the owner of the object. This is called REVOKING of PRIVILEGES. GR ANT Statement: Syntax: GR ANT < object pr ivileges> ON TO <User_Name> [WITH GR AN T OPTI ON] eg: GR ANT ALL ON Student TO Mohd Imran WITH GR ANT OPTI ON The WITH GR AN T OPTION allows the grantee to in turn grant object privileges to other users. eg: GR ANT SELECT, UPDATE ON Student TO Fareen WITH GR ANT OPTI ON The user Fareen has been given permission to view and modif y records in the table Student. To view the contents of the student table that belongs to Mohd Imran. eg: SELECT * FROM Mohd Imran. Student ; Mohd Imran is the owner of the table, and he has given pr ivileges to other user f or his table. Object s Pri vileges t hat can be gi ven to users. Each object privileges that is granted aut horized the grantee t o perf orm some operat ion on t he object. A user can grant all the privileges or grant only specif ic object privileges. The list of object privileges is as f ollows: Al t er: Allows the grantee to change the table def inition with t he ALTER TABLE command. DELETE: Allows the grantee to remove the records f rom the table wit h the DELETE command. INDEX: Allows the grantee to create an index on the table with the CREATE INDEX command.
INSERT: Allows the grantee to add records to the table with the INSERT command. SELECT: Allows the grantee to query the table with the SELECT command. UPD ATE: Allows the grantee to modif y the records in the tables with the UPDATE command. REVO KING PRI VILEGES GIVEN: Privileges once given can be denied to a user using the REVOKE command. The object owner can revoke privileges granted to another user. A user of an object who is not the owner, but has been granted the GRANT privileges, has the power to REVOKE the pr ivileges f rom grantee. Revoking the Permission using the REVOKE command. Syntax: REVOKE ON FROM <User_Name> Eg: REVOKE UPDATE ON Student FROM Fareen;
Ex: Assign SELECT previllage to the user -2 on the table PRODUCTS created by USER-1 GRANT SELECT ON ORCL. USER_1.PRODUCTS TO USER_2; Ex:
How do i cancel the above permission? REVO KE SELECT ON ORCL. USER_1.PRODUCTS FRO M USER -2;
Ex:
How to assign CREATE VIEW permission to user -2? GRANT CREATE VI EW To user_2;
Ex:
How do i cancel all previllages on all objects to the user -1? REVO KE ALL f rom USER_1;
Data Integrity Constraints CONSTR AI NTS: Constraints ar e set of rules / business rules which will be def ined at DDL level. Constraints enf orce the data base to allow only valid values in to the tables. Constraints ensure t he user to f etch only valid / complete and accurate data f rom the database. Categories of Constraints: 3 1) Key Constraints 2) Domain Constraints 3) Referential Integrit y constraints 1) KEY CONSTR AI NTS: These constraints check the individual values in to a column according to Business. These are divided into 3 types a) UNIQUE It doesn’t allow duplicates but allows null values. Ex: This constraint is suitable f or maintaining phone numbers, mail id, etc... b) NO T NULL It doesn’t allow null values but allows duplicates . Ex: Emp Names, Cust Names, ..... c) PRIM ARY KEY It doesnt allow duplicates and null values. Generally a Primary key is used to identif y any record in a table uniquel y. “Only one pr imary key is allowed per a table ”. Primar y key is of 2 types. Simple primary key If a Primar y key Def ined on a single column then it is known as Simple Primar y key. Composite primary key If a Primar y key constraint def ined on m ore than one column then it is known as Composite Prim ar y Key . (Max numbers of columns in to a composite Primary key are 32 columns ) Composite Pr imar y Key: primar y key (custid,prodid,timeid)
SALES_DTLS ========== custid ------
prodid ------
------
c1 c1 c2 c1 c5
p3 p2 p3 p3 p1
jan5-15 jan5-15 jan5-15 jan7-15 jan5-15
Syntax:
timeid
qty
sales_amount ------------
100 100 100 100 200
100000 200000 100000 150000 300000
----
Creat ing a table with key constraints:
Create table ( col1 data t ype(size) , col2 data t ype(size) , : : : : ); Ex: create a table student with columns rno, sname, course, f ee and mobile along with constraint s pk, nn, nn, nn and unique respect ively? Create table st udent ( rno number(2) pr imar y key, sname var char2( 10) not null, course var char2( 15) not null, f ee number(5) not null, mobile char(10) unique );
insert insert insert insert
into into into into
student student student student
values(1,'a','oracle', 9000,'8989898989'); values(0,'b','java',2000 ,'8787878787'); values(2,' x','oracle', 9000,null); values(11,'s','abc',100,null);
data:RNO ----------
SNAME ----------
COURSE FEE --------------- ----------
MOBILE ----------
1 0 2 11
a
oracle b
x s
9000 java
oracle abc
2000
8989898989 8787878787
9000 100
ERROR GENERATING RECORDS: insert insert insert insert insert insert
into into into into into into
student student student student student student
values(1,'kiran','java', 2300,null); values(null,'kiran','java',2300,null); values(12,null,'java',2300,null); values(1,'kiran',null,2300,null); values(1,'kir an','java', null,null); values(1,'kiran','java', 2300,8989898989);
Note: Even af ter the key constraints on the table, still we have invalid values. W e can elim inate them by using DOM AIN constraints.
Displaying constraints information on a table:In oracle database, all constraints stored in a system def ined table called USER_CONSTRAINTS . Each constraint nam ed and numbered uniquely like, SYS_Cn (System def ined Constraint). To Fetch data f rom this table use the below example. Ex: select constraint_name,constraint_t ype from USER_CONSTRAINTS where table_name=' STUDENT'; CONSTRAINT_NAME CONSTRAINT_TYPE ------------------------------ --------------SYS_C007050 C --either Not null or Check SYS_C007051 C SYS_C007052 C SYS_C007053 P --Primary key SYS_C007054 U --Unique key SYS_C007055 R –Foreign key
CONSTRAINTS w ith user-defined names: SYN: datat ype(size) Constraint <User def ined name>, datat ype(size) Constraint <User def ined name>, Ex:
datat ype(size) Constraint pk_rno_student Primar y key,
create table stud_dt ls ( rno number(2) constraint pk_rno_stud prim ary key, sname var char2( 20) constraint nn_sname_stud not null, course var char2( 7) constraint nn_cour se_stud not null, f ee number(5) constraint nn_f ee_stud not null, mobile number(10) constraint uk_mobile_stud unique ); create table student _dtls ( rno number(2) constraint pk_rno_student pr imar y key, constraint ck_rno_st udent check (rno between 1 and 60), sname var char2( 20) constraint nn_sname_student not null, course var char2( 7) constraint nn_cour se_student not null, constraint ck_course_student check (course In('cse','ece','eee','it')), f ee number(5) constraint nn_f ee_student not null, constraint ck_f ee_student check (f ee between 30000 and 40000), mobile number(10) constraint uk_mobile_student unique );
select constraint_name,constraint_t ype from user_constraints where table_name=' STUDENT_DTLS'; CONSTRAINT_NAME -----------------------------NN_SNAME_STUDENT NN_COURSE_STUDENT NN_FEE_STUDENT CK_RNO_STUDENT CK_COURSE_STUDENT CK_FEE_STUDENT PK_RNO_STUDENT UK_MOBILE_STUDENT
CONSTRAINT_TYPE --------------C C C C C C P U
8 rows selected
DOMAIN constraints: It is used to def ine a valid range / valid list of values on a column by using the keyword CHECK. CHECK uses 2 operators. BETW EEN IN Syntax:
to def ine range to def ine list of values.
create table ( col1 datat ype( size) , col2 datat ype( size) , : : : :, CHECK (col1 BETW EEN begin_value AND end_value), CHECK (col2 IN ( val1, val2,........,)), .... .... ); Ex:
create the above table along with below domain constraints: --rno should be bet ween 1 and 60 --course names are oracle, sql ser ver and unix --Min f ee is 5000 and max f ee 10000
create table stud_dt ls ( rno number(2) const raint pk_rno_stud pr imary key, sname varchar2(10) constraint nn_name_stud not null, course varchar2(15) constraint nn_cour se_stud not null, f ee number(5) const raint nn_f ee_stud not null, mobile char( 10) constraint uk_mobile_st ud unique, constraint ck_rno_st ud check (rno bet ween 1 and 60), constraint ck_course_stud check (course in('oracle','sql ser ver','unix')), constraint ck_f ee_stud check (f ee between 5000 and 10000) ); Recor ds: insert into stud_dt ls values(1,'a','oracle', 7000,1212); insert into stud_dtls values(0,'b','sql ser ver',7000,null); insert into stud_dtls values(61,'a','oracle',7000,null); insert into stud_dtls values(12,'b','sql server',7000,null); insert into stud_dtls values(11,'ajay','unics',10000,2212); insert into stud_dtls values(21,'hari','unix',17000, 1211); insert into stud_dt ls values(21,'hari','unix',11000,1211);
RNO ---------1 12 21
SNAME ---------a b hari
COURSE FEE MO BILE --------------------------------oracle 7000 1212 sql ser ver 7000 unix 11000 1211
CREATING A TABLE W ITH USER -FRIENDLY NAMES TO THE CONSTRAINTS: -
create table s_dtls ( rno number(2) CONSTRAINT PK_RNO_S_DTLS primar y key, sname varchar2(10) CONSTRAINT NN_SNAME_S_DTLS not null, course varchar2(15) CONSTRAINT NN_COURSE_S_DTLS not null, f ee number(5) CONSTRAINT NN_FEE_S_DTLS not null, mobile char( 10) CONSTRAINT UK_MOB_S_DTLS unique, CONSTRAI NT CK_RNO_S_DTLS check (rno bet ween 1 and 60), CONSTRAI NT CK_COURSE_S_DTLS check (course in('oracle','sql ser ver','unix')), CONSTRAI NT CK_FEE_S_DTLS check (f ee between 10000 and 20000) ); How do i display constraints inf ormation of a table? SELECT CONSTRAI NT_NAME, CONSTRAINT_TYPE FRO M USER_CONST RAINTS W HERE TABLE_NAME='s_dtls'; ----> this won't work SELECT CONSTRAI NT_NAME, CONSTRAINT_TYPE FRO M USER_CONSTRAINTS W HERE TABLE_NAME='S_DTLS'; CONSTRAINT_NAME CONSTRAINT_TYPE ------------------------------ --------------NN_SNAME_S_DTLS C NN_COURSE_S_DT LS C NN_FEE_S_DTLS C CK_RNO_S_DTLS C CK_COURSE_S_DT LS C CK_FEE_S_DTLS C PK_RNO_S_DTLS P UK_MOB_S_DTLS U constraint_t ype -----------------
Meaning -------------
C P U R
check or not null Primar y Key Unique key Foreign key
Assignment: i) create customers table with columns custid,custname,cit y,gender,mailid,phone, Address with the constraints Pk,NN,NN, NN, and Unique and Unique Respectively.
NORMALIZATION AND DENORMALIZATION CONCEPTS: Consider the below tables and data. DEPT DNO 10
DN AM E Product ion
LOC Hyder abad
20
Sales
Hyder abad
30
Finance
Chennai
EMP Eid 1
Ename
Salary
A
2000
2
X
1200
3
A
3400
4
Z
5000
5
C
1000
6
S
1300
7
D
2300
8
X
1200
9
B
2200
Note: By using above tables we are unable f etch the complete dat a of an object, like, department nam e of any employee, number of emp loyees in dept and etc. The solut ion f or such kind of requirements we have 2 methods. 1) Maintaining all the inf ormation in one big table [ DENORMALIZED DATA] 2) Maintaining data in dif f erent tables and implement Physical relationships bet ween the tables [NORMALIZED DATA ] 1) DENORM ALIZ ATION Maintaining all inf ormation in one big table is known as De - normalized method.
emp_dept_details Eid
Ename
Salar y
Dno
Dname
Loc 1
A
2000
10
Product ion
Hyder abad
2
X
1200
10
Product ion
Hyder abad
3
A
3400
10
Product ion
Hyder abad
4
Z
5000
10
Product ion
Hyder abad
5
C
1000
20
Sales
Hyder abad
6
S
1300
20
Sales
Hyder abad
7
D
2300
20
Sales
Hyder abad
8
X
1200
30
Finance
Chennai
9
B
2200
30
Finance
Chennai
Note: From
the above table we will get the required inf ormation, but i t has data duplicacy it occupies more disk space data retrieval time is ver y high.
Disk space: 6X9=54 kb
2) NORMALIZATION: The Process of dividing the above big table in to sub tables until the data duplicacy is maximum reduced is called normalizat ion process. i) Ist NF(normal form): Dividing the table int o sub tables based on repeated groups of data.
eid --1 2 3 4 5 6 7 8 9
emp --ename sal ----- ---a 2000 x 1200 a 3400 z 5000 c 1000 s 1300 d 2300 x 1200 b 2200
dept -----------dno --10 10 10 10 20 20 20 30 30
dname --------product ion hyderabad product ion hyderabad product ion hyderabad product ion hyderabad sales hyderabad sales hyderabad sales hyderabad f in chennai f in chennai
loc
Ex: Emp Table: create table emp as select eid,ename,sal from emp_dept_details; DEPT table: create table dept as select dno,dname, loc f ro m emp_dept_details;
ii) IInd NF: Elem inating duplicat es and def ining primary keys
eid --1 2 3 4 5 6 7 8 9 PK
emp --ename sal ----- ---a 2000 x 1200 a 3400 z 5000 c 1000 s 1300 d 2300 x 1200 b 2200
dept -----------dno --10 20 30 PK
dname --------product ion hyderabad sales hyderabad f in chennai
loc
Ex: Eleminating Duplicate records create table dept1 as select dist inct dno,dname,loc f rom dept;
iii) IIIrd NF: Implement ing relat ionships bet ween the t ables by using Pr imary key of dept table , def ine the f oreign key column under emp table.
eid --1 2 3
emp --ename ----- ---a 2000 x 1200 a 3400
dept ---sal dno ------10 10 10
dno --10 20 30
dname --------product ion hyderabad sales hyderabad f in chennai
loc
4 5 6 7 8 9 PK
z c s d x b
5000 1000 1300 2300 1200 2200
10 20 20 20 30 30 FK
PK
It occupies less disk space and max .dat a duplicacy is reduced. disk space: 4X9=36 3X3=9 45KB Advantages: --Searching f or required data is as much as f ast --And data retr ieval is f ast --Max data duplicacy is eleminated. --Occupy less Disk space.
3) REFERENTI AL INTEGRITY CONSTRAINTS : This constraint is u sed to implement PHYSICAL relat ionship bet ween the tables by using prim ary key of one table and we can def ine f oriegn key in other table. Foriegn key column contains only values from primary key. Foriegn key contains duplicates and null values also. --A table which cont ains primar y key is considere d as par ent / Master/Base table. --A table which cont ains f oriegn key is known as child table / Detailed table/ Derived table. REFERENCES we can use this keyword in the "creat ion of child table and to def ine f oriegn key column". Ex:
create comp_dtls as parent table
Ex:
create prod_dt ls as child table create table comp_dtls ( cmpId char(5) constraint pk_cmpid_comp_dtls primar y key, cmpName var char2( 20) not null, cmpCountr y var char2( 20) not null, constraint ck_countr y_cmp check (cmpcountr y IN('india','usa','japan' ,'uk')) );
insert insert insert insert
into into into into
comp_dt ls comp_dt ls comp_dt ls comp_dt ls
values('cmp01','sony','japan'); values('cmp02',' wipr o','india') ; values('cmp03','Philips','india'); values ('cmp04','sem antic','usa');
create table prod_DTLS ( pid char(4) primar y key, pname var char2( 20) not null, cost number(7,2), mfg date, warrent y varchar2(10), cmpId char(5), constraint f k_prod_cmpid FOREIGN KEY(cmpId) REFERENCES comp_dt ls(cmpId ) /* f oriegn key column */ ); insert into prod_dt ls values ('p001','smart phone' ,34000,'12 -may-14',' 1 year','cmp01'); insert into prod_dt ls values ('p002','laptop',54000,'03 -f eb-14','3 year s','cmp01'); insert into prod_dt ls values ('p003','Televis ion',24000,'08 -aug-14','5 years','cmp03'); insert into prod_dt ls values ('p004','Home Theatr e',55000,'11 -aug-13' ,'2 years','cmp03'); insert into prod_dt ls values ('p005',' Mobile',24000,'08 -aug-14','1 year ',null); insert into prod_dt ls values ('p006',' vm ware',64000,'22 -oct-10','1 year','cmp04'); Ex:
create the f ollowing tables and Implement relationships accor dingly?
1) Cust_dt ls
( parent table)
cno cname | Primar y key
cit y
gender
mobile
CREATE TABLE cust_dtls ( Cno char(5) constra int pk_cno PRI MARY KEY, Cname var char2( 20) constraint nn_cnam e NOT NULL, Cit y varchar2(20) , MO BILE number(10) ); 2) Act_t ypes Act_t ype
( parent table) act_name
desc
SB DEMAT CA
Savings Bank Trading account Current account
CREATE TABLE act _types ( Act_t ype char(5) constraint pk_act_t ype Act_name varchar2( 20) );
PRI MARY KEY,
3) cust_act_dtls (Child table) Actno Act_t ype PK
Act_open_date
Foreign key
Act_bal
cno Foreign key
CREATE TABLE cust_act_dt ls ( Actno number (10) constraint pk_act no pr imar y key, Act_t ype char(5), Constraint f k_act_type FOREIGN KEY(act_type) REFERENCES act_t ypes(act_t ype), Act_open_date date, Act_bal number(8,2), Cno char(5), Constraint f k_cno FOREIGN KEY(cno) REFERENCES cust_dt ls(cno) ); ON DELETE C ASC ADE It allows deletion of parent table records even the parent is having childs. As an eff ect the dependant child records ar e automatically deleted. This known as “Maintaining Data Integrity among the tables. ”
ALTERING CONSTRAINTS Alter ing constraints means, i)Adding constraints to the existed colum ns Keyword:
ADD CONSTRAINT
ii) Delet ing constraint s f rom the columns Keyword:
DROP CONSTRAINT
iii) Modif y constraints / changing exist ing constraints with new constraints To do this ,F irst drop old constraint and then add new constr aints.
EN ABLI NG / DIS AB LING CONSTR AI NTS W e can also tempor arily enable or disable constraints on the columns based on business requirement. Syn:- Enable constraint Alter table ENABLE CONSTRAI NT ; Syn:- Disable constr aint Alter table DISABLE CONSTRAINT ; NOTE: Q: Can w e enable any constraint if a column havi ng invali d data according to constraint? Ans: YES ,w e can enable , but the column should be defined w ith DIFFER ABLE ke yw ord.
MERGE statement Oracle’s MERG E statement is used f or situations when you want to do an "upsert" i. e. update exist ing rows in a table or insert new r ows depending on a match condition. This is t ypically t he c ase when you have to synchronize a table periodically wit h dat a f rom another source (table/ view/quer y). Syntax: MERGE into USING <source table/ view/result of subquery> ON <match condition> W HEN MATCHED THEN <delete clause> W HEN NOT MATCHED THEN Example: SQL> select * f rom student; ID NAME SCORE ---------- --------------- ---------1 Jack 540 2 Rose 3 W illiam 650 4 Caledon 620 5 Fabrizio 600
6 Thomas 7 Ruth 8 Spacer
680 555
8 rows select ed. SQL> select * f rom student_n; ID NAME SCORE ---------- --------------- ---------7 Ruth 690 8 Spicer 620 9 W allace 600 10 Lizzy 11 Brock 705 As you can see, the f ollowing act ions ar e requir ed on table STUDENT: 1 row f or id#7 to be corrected f or score: Ruth had scored 690, not 680. 1 row f or id#8 to be corrected f or name: the student is called Spicer, not Spacer. 3 new rows (ids#9, 10,11) to be inserted into STUDENT table. Note: 5 rows shoul d get processed in all. merge into student a using (select id, name, score f rom student_n) b on (a.id = b.id) when matched then update set a.nam e = b.name , a.score = b.score when not matched t hen insert (a.id, a.name, a .score) values (b. id, b.name, b.score); 5 rows merged. SQL> select * f rom student; ID NAME SCORE ---------- --------------- ---------1 Jack 540 2 Rose 3 W illiam 650 4 Caledon 620 5 Fabrizio 600 6 Thomas 7 Ruth 690 11 Brock 705 10 Lizzy 9 W allace 600 8 Spicer 620
11 rows selected. Sure enough, 5 rows have got merged as expected – 2 updates + 3 inserts.
SET OPERATORS You can combine multiple queries using the set operators UNION, UNION ALL, INTERSECT, and MINUS. All set operators have equal precedence. If a SQL statement contains multiple set operators, then Oracle Database evaluates them f rom the lef t to right unless parentheses explicit ly specif y anot her or der. The corresponding expr essions in the select lists of the component queries of a compound quer y must match in number and must be in the same datat ype group (such as numer ic or char acter). Or These operat ors will display combined data f rom multiple tables. 1) UNION It will display combined data f rom multiple tables with out duplicates 2) UNION ALL It will display combined data f rom multiple tables with duplicates 3) INTERSECT It will display common data f rom multiple tables (From mult iple Select stmts) 4) MINUS It will display values f rom f irst select ion by eliminating values which are repeat ing in second select ion For example: s1={a,b,c,d}
s2={x, y, z,c, b}
1) select * f rom s1 union select * f rom s2; output:
{a,b,c,d,x, y, z}
2) select * f rom s1 union all select * f rom s2; output:
{a,b,c,d,x, y, z,c, b}
3)select * f rom s1 intersect
select * f rom s2; output:
{c,b}
4)select * f rom s1 minus select * f rom s2; output:
{a,d}
Sample Tables: CREATE TABLE CUST_BR1 ( CID CHAR(3), CNAME VARCHAR2( 20), MO BILE NUMBER( 10), CITY VARCHAR2(20), GENDER VARCHAR2(10) ); INSERT INTO CUST_BR1 VALUES('C1','VIJAY',1212121212,' HYD',' MALE'); INSERT INTO CUST_BR1 VALUES('C2','JOHN',1313131313,'DELHI',' MALE'); INSERT INTO CUST_BR1 VALUES('C3','SW ATHI',1414141414,'HYD ','FEMALE'); CREATE TABLE CUST_BR2 ( CID CHAR(3), CNAME VARCHAR2( 20), MO BILE NUMBER( 10), CITY VARCHAR2(20), GENDER VARCHAR2(10) ); INSERT INTO CUST_BR2 VALUES('C1','KIRAN',9898989898,'HYD',' MALE'); INSERT INTO CUST_BR2 VALUES('C2','JOHN',1313131313,'DELHI',' MALE'); INSERT INTO CUST_BR2 VALUES('C3','LAKSHMI',8989898989,'DELHI','FEMALE'); CREATE TABLE CUST_BR3 ( CID CHAR(3), CNAME VARCHAR2( 20), MO BILE NUMBER( 10), CITY VARCHAR2(20), GENDER VARCHAR2(10) ); INSERT INTO CUST_BR3 VALUES('C1','KIRAN',9898989898,'HYD',' MALE'); INSERT INTO CUST_BR3 VALUES('C2','JOHN',1313131313,'DELHI',' MALE'); INSERT INTO CUST_BR3 VALUES('C5','VI NAY',7878787878,'DELHI','MALE');
Examples: Display all custom ers inf o f rom all branches select * f rom cust_br1 union all select * f rom cust_br2 union all select * f rom cust_br3; Get the customer details without duplicates select * f rom cust_br1 union select * f rom cust_br2 union select * f rom cust_br3; DISPLAY CO MMO N CUSTO MER NAMES AND MO BILE NUMBERS FRO M ALL BRANCHES SELECT CNAME, MO BILE FRO M CUST_BR1 INTERSECT SELECT CNAME, MO BILE FRO M CUST_BR2 INTERSECT SELECT CNAME, MO BILE FRO M CUST_BR3; DISPLAY CUSTOMERS DETAILS W HO IS THE ONLY CUSTOMER FOR BRANCH 2 SELECT * FRO M CUST_BR2 MINUS ( SELECT * FRO M CUST_BR1 UNION ALL SELECT * FRO M CUST_BR3 );
Note: What are the limitations of set operators? We need to select equal number of col umns from each table We need to select same data type of data in the same sequence under each select query. Ex: select cname,mobile f rom cust_br1 union all select cid f rom cust_br2;
select cname,mobile from cust_br1 * ERROR at line 1: ORA-01789: quer y block has incorrect number of result colum ns SQL> select cname, mobile f rom cust_br1 2 union all 3 select mobile,cname f rom cust_br2; select cname,mobile from cust_br1 * ERROR at line 1: ORA-01790: expression must have same datat ype as corresponding expression
Restrictions on the Set Operators The set operators ar e subj ect to the f ollowing restrict ions:
The set operators ar e not valid on colum ns of type BLOB, CLOB, BFILE, VARRAY, or nested table. The UNION, INTERSECT , and MI NUS operators are not valid on LONG columns. If the select list preceding the set operat or contains an expression, then you must provide a column alias f or the expression in or der to ref er to it in the order_by_clause . You cannot also specif y the for_updat e_clause with the set operators. You cannot specif y the order_by_clause in the subquery of these operators. You cannot use these operators in SELECT statements containing TABLE collection expressions.
JOINS Joins are used to display mult iple data t ypes of data f rom multiple tables. A join is a quer y that combines rows f rom two or mor e tables, views, or materialized views. Oracle Dat abase perf orms a join whenever mult iple tables appear in the FRO M clause of the query. The select list of the query can select any colum ns f rom any of these tables. If any t wo of these tables have a column name in common, the n you must qualif y all ref erences to these columns thr oughout the query with table names to avoid ambiguit y. Types of joins:
4
i)
CROSS JOIN
ii)
EQUI JOIN / INNER JOIN
iii)
SELF JOIN
iv)
OUTER JOINS
CROSS JOIN / C AR TESI AN PRODUCT It will display combination of data from mult iple tables. In this join, each value in the f irst table is mapped with all values in the second table. It will display all possible combinations of data f rom multiple tables. If two tables in a join query have no join conditi on, then Oracle Database returns their Cartesian product . Oracle combines each row of one table with each row of the other. A Cartesian product always generates many rows and is rarely usef ul. Example: consider s1={a,b,c,d}
s2={d1,d2}
s1Xs2={(a,d1),(a,d2) ,(b,d1),(b,d2),.......} syn: select col1, col2,...,coln f rom table1, table2,.... where order by col1, col2,.. ..[ desc ]; Ex:
Display employee names , salar ies and their department names? select ename,sal,dname f rom emp,dept;
ENAME SAL DNAME ---------- ---------- -------------SMITH 800 ACCOUNTING ALLEN 1600 ACCOUNTING W ARD 1250 ACCO UNTING JONES 2975 ACCO UNTING ……… ……… ……… SMITH 800 RESEARCH ALLEN 1600 RESEARCH W ARD 1250 RESEARCH JONES 2975 RESEARCH MARTIN 1250 RESEARCH …… …… …… SMITH 800 SALES ALLEN 1600 SALES W ARD 1250 SALES JONES 2975 SALES MARTIN 1250 SALES ….. ….. ….. SMITH ALLEN
800 OPERATIONS 1600 OPERATIONS
ENAME SAL DNAME ---------- ---------- -------------W ARD 1250 OPERATIONS JONES 2975 OPERATIONS MARTIN 1250 OPERAT IONS BLAKE 2850 OPERATIONS ….. ….. 56 rows selected. SQL> Note: In the above output some combinat ions are valid and remaining are invalid according to physical table dat a. Ex: Get manager details and their dept details? SQL> select * f rom 2 emp,dept 3 where job=' MANAGER';
EMPNO ENAME JOB MGR HIRE DATE SAL COMM DEPTNO DNAME LOC ---------- ---------- --------- ---------- --------- ---------- ---------- ---------- ---------7566 JONES MANA GER 7839 02 -APR-81 2975 ACCOUNTING NEW YORK 7566 JONES M AN AG E R 7 8 3 9 0 2 - AP R - 8 1 2975 R E S E AR C H D AL L AS 7566 JONES MANA GER 7839 02 -APR-81 2975 CHICAGO 7566 JONES MANA GER 7839 02 -APR-81 2975 OPERATIONS BOSTON 7698 BLAKE MANAGER 7839 01 -MAY-81 2850 ACCOUNTING NEW YORK 7698 BLAKE MANAGER 7839 01 -MAY-81 2850 RESEARCH DALLAS 7 6 9 8 B L AK E M AN AG E R 7 8 3 9 0 1 - M AY - 8 1 2850 C H I C AG O 7698 BLAKE MANAGER 7839 01 -MAY-81 2850 OPERATIONS BOSTON 7 7 8 2 C L AR K M AN AG E R 7839 09-JUN-81 2450 AC C O U N T I N G NEW YORK 7782 CLARK MANA GER 7839 09 -JUN-81 2450 RESEARCH DALLAS 7782 CLARK MANA GER 7839 09 -JUN-81 2450 CHICAGO
DEPTNO -------------- -----------20
10
20
20
20
30 SALES
20
40
30
10
30
20
30
3 0 S AL E S
30
40
10
10
10
20
10
30 SALES
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO DEPTNO DNAME LOC ---------- ---------- --------- ---------- --------- ---------- ---------- ---------- ---------- -------------- -----------7782 CLARK MANA GER 7839 09 -JUN-81 2450 10 40 OPERATIONS BOSTON 1 2 r o ws s e l e c t e d .
SQL> “In the above output, bold faced lines are valid , and re maining are invalid.” So, Then in which case cross join will display only valid combination?
Ex:
f or all managers display 13% increment details? select ename,sal,job,per_id,per_incr,desc f rom emp,Percent_incr_dtls where job='manager' and per _incr='13%';
Cross join examples: CREATE TABLE "PER_INCR" ( "INCRI D" NUMBER, "INCRVAL" VARCHAR2(20 BYTE), "DESCRI PTION" VARCHAR2(40 BYTE) DEFAULT null ); insert into per_incr values (101,'5%',' Min INcrement');
insert into per_incr values (102,'10%','Second level INcrement'); insert into per_incr values (103,'15%','3rd level INcrement'); insert into per_incr values (104,'25%',' Max level INcrement');
select * f rom emp; select * f rom per_incr;
INCRI D ---------101 102 103 104
INCRVAL DESCRIPTION -------------------- ---------------------------------------5% Min INcrement 10% Second level INcrement 15% 3rd level INcrement 25% Max level INcr ement
Ex: display manager s details with 15% increment? select e.ename, e.sal,e.job,i.incrid,i. incr val, i.descr ipt ion f rom emp e,per_incr i where e.job=' MANAGER' AND i. incr val=' 15%'; Ex:
Apply 5% f or all cler ks
select e.*, i.* f rom emp e, per_incr i where e.job='CLERK' and i. incr val='5%';
EQUI JOIN / INNER JOIN A cross join is known as equi join if we specif y join condition using '=' operator. It will display only matched data f rom all tables. A condit ion is known as join condition if it is specif ied bet ween primar y key of one table and f oriegn key of other table. syn:
select col1, col2,...,coln / * f rom table1, table2,.... where table1.pk=table2.f k and table2. pk=table3.fk....... order by col1,col2,…..[ asc/ desc] ;
Ex: display employee names , salar ies and corresponding depart ment details?
select ename, sal,dept.deptno,dname, loc f rom emp,dept where emp. deptno=dept.deptno; ENAME SAL DEPTNO DNAME LOC ---------- ---------- ---------- -------------- ------------CLARK 2450 10 ACCOUNTING NEW YORK KING 5000 10 ACCOUNTING NE W YORK MILLER 1300 10 ACCOUNTING NEW YORK JONES 2975 20 RESEARCH DALLAS FORD 3000 20 RESEARCH DALLAS ADAMS 1100 20 RESEARCH DALLAS SMITH 800 20 RESEARCH DALLAS SCOTT 3000 20 RESEARCH DALLAS W ARD 1250 30 SALES CHICAGO TURNER 1500 30 SALES CHICAGO ALLEN 1600 30 SALES CHICAGO ENAME SAL DEPTNO DNAME LOC ---------- ---------- ---------- -------------- ------------JAMES 950 30 SALES CHICAGO BLAKE 2850 30 SALES CHICAGO MARTIN 1250 30 SALES CHICAGO 14 rows selected. SQL> WRITING JOIN QUERIES WITH ALI AS NAMES Alias name is a temporar y name f or the table and it is valid with in the quer y. Ex: Display employee details and dept details ? select e.ename, e.sal,e.job,e.deptno ,d.deptno,d.dname,d.loc from emp e, dept d where e. deptno=d.deptno; Ex: Display employee details and dept det ails f or all managers? select e.*,d.* f rom emp e, dept d where e.job=' MANAGER' and e. DEPTNO = d.DEPTNO; OUTPUT: EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO DNAME LOC ---------- ---------- --------- ---------- --------- ---------- ---------- ---------- ---------7782 CLARK MANA GER 7839 09-JUN-81 2450 ACCOUNTING NEW YORK 7566 JONES MANA GER 7839 02 -APR-81 2975 RESEARCH DALLAS 7698 BLAKE MANAGER 7839 01-MAY-81 2850 CHICAGO
DEPTNO -------------- -----------10
10
20
20
30
30 SALES
SQL>
Ex: Display all clerks det ails and their department names like hig hest salaried clerk to least salaried clerk ? SELECT E.*,D.DEPT NO,D.DNAME FRO M EMP E, DEPT D W HERE E.JOB='CLERK' AND E. DEPTNO=D.DEPTNO ORDER BY SAL DESC; Output: EMPNO ENAME JOB MGR HIRE DATE SAL COMM DEPTNO DEPTNO DNAME ---------- ---------- --------- ---------- --------- ---------- ---------- ---------- ---------- -------------7934 MILLE R CLERK 7782 23 -JAN-82 1300 10 10 ACCOUNTING 7876 ADA MS CLERK 7788 23 -MAY-87 1100 20 20 RESEARCH 7900 JAMES CLERK 7698 03-DEC-81 950 30 30 SALES 7369 SMITH CLERK 7902 17 -DEC-80 800 20 20 RESEARCH SQL>
Examples : Consider the f ollowi ng tables.
Act_types
CUST_DTLS Cno PK Cname City Gender Mobile
Act_type PK Act_name Act_desc
Cust_act_Dtls Actno Act_type Act_Open_date Act_bal Cno FK FK
EQUI JOIN EXAMPLES W ITH MORETHAN 2 TABLES Ex: Get customer details with account balance and account name? Select cd.*,cad.act_bal,at.act_name From cust_dtls cd, cust_act_dtls cad, act_types at W here cd.cno=cad.cno
And Cad.act_type=at.act_type; OUTPUT: CNO ---------cust-1 cust-2 cust-3 cust-3 cust-4 cust-5 cust-6
CNAME CITY G ACT_BAL ACT_NAME ------------ ---------- - ---------- -----------------------------Ajay Texas M 32000 Salary A/c. Kiran Chicago M 23000 Savings Bank A/c. vinod Delhi M 49000 Savings Bank A/c. vinod Delhi M 123000 Trading A/c. Madhu Delhi M 11000 Salary A/c. Rocky Texas M 13000 Savings Bank A/c. Ching Fu Chicago F 23000 Salary A/c.
7 rows selected. SQL> Ex: Get product name,cost, warrenty, company name and sale date? Select p.pname,p.cost,p.warr,c.cname,s.sale_dt From prod_dtls p, comp_dtls c, sales s W here c.cmpid=p.cmpid And p.pid=s.pid; INNER JOIN Ex: display customer name and cit y, custom er actno,actt ype and bal, act_name f or all cust omers? select cd.cname,cd.cit y,cad.actno,cad.act_t ype, cad.act_bal,at.act_name f rom cust_dtls cd INNER JOIN cust_act_dtls cad ON cd.cno=cad. cno INNER JOIN act_types at ON cad.act_t ype=at.act_type; OUTPUT: CNAME -----------Ajay Kiran vinod vinod Madhu
CITY ACTNO ACT_T ACT_BAL ACT_NAME ---------- ----------- ----- ---------- -----------------------------Texas 20035201471 SAL 32000 Salar y A/c. Chicago 20035201473 SB 23000 Savings Bank A/c. Delhi 20035201470 SB 49000 Savings Bank A/c. Delhi 20035201472 DEMA T 123000 Trading A/c. Delhi 20035201474 SAL 11000 Sal ar y A/c.
Rocky Ching Fu
Texas 20035201475 SB Chicago 20035201476 SAL
13000 Savings Bank A/c. 23000 Salar y A/c.
7 rows select ed. SQL> SQL> Ex:
Applying equi join on 5 tables? select t1.*,t2.*... f rom t1,t2,t3, t4,t5 where t1.col1=t2.col4 and t2.col3=t3.col1 and t3.col2=t4.col31 and t4.col31=t5.col30;
Ex:
Applying Inner join on 5 tables? select t1.*,t2.*... f rom t1 Inner Join t2 ON t1.col1=t2.col4 Inner Join t3 ON t2.col3=t3.col1
Inner Join t4
ON t3.col2=t4.col31
Inner Join t5
ON t4.col31=t5.col30;
Ex:
Display manager det ails and corresponding department details? select e.*,d.* f rom emp e, dept d where job=' MANAGER' AND e.deptno=d.dept no; or
select e.*,d.* f rom emp e inner join dept d on e.deptno=d.deptno where e.job=' MANAGER'; Ex:
select e.eid,e.sal,e.job,d.dname f rom emp,dept where e. deptno=d.deptno;
[ invalid quer y ]
NOTE: In the above query t he e and d are temporar y alias names f or the table emp and dept respectively. And these alias nam es are valid f or current query only.
Ex: DISPLAY EMPLOYEE DETAILS AND DEPART MENT DETAILS W HO IS W ORKING UNDER account ing and sales? SELECT E.*,D.* FROM EMP E INNER JOIN DEPT D ON d.dname IN('ACCOUNTING','SALES' ) AND E.DEPTNO = D.DEPTNO; OUTPUT: EMPNO ENAME JOB DEPTNO DNAME LOC ---------- ---------- -------------- -------------- ------------7782 CLARK MANA GER ACCOUNTING NEW YORK 7839 KING PRESIDENT ACCOUNTING NEW YORK 7934 MILLE R CLERK ACCOUNTING NEW YORK 7521 W ARD SALESMAN CHICAGO 7844 TURNER SALESMAN CHICAGO 7499 ALLEN SALESMAN CHICAGO 7900 JAMES CLERK CHICAGO 7698 BLAKE MANAGER CHICAGO 7654 MARTIN SALESMAN CHICAGO
MGR HIREDATE
SAL
---------- --------7839 09 -JUN-81
COMM
DEPTNO
----------
---------- ---------- ----
2450
10
10
17-NOV-81
5000
10
10
7782 23 -JAN-82
1300
10
10
7698 22-FEB-81 7698 08 -SEP-81 7698 20 -FEB-81 7698 03 -DEC-81
1250 1500 1600
500
30
30 SALES
0
30
30 SALES
300
30
30 SALES
950
7839 01 -MAY-81
2850
7698 28 -SEP-81
1250
30
30 SALES 30
1400
30 SALES 30
30 SALES
9 r o ws s e l e c t e d . SQL>
Ex: display product and corresponding company details? Ex: display customer details and corresponding account details? Ex: display company name and its pr oducts details if the product s are f rom the company "sony"? Ex: display expired product details and their corresponding company details?
Ex: Display customer n ames, mobile, product names, cost, warrent y and company names? select c.cname, c.mobile, p.pname,p.cost , p.warrent y, cmp.comp_name f rom cust_dtls c, prod_dtls p, comp_dtls cmp, sales_dt ls s where c.cid=s.cid and s.pid= p.pid and p.comp_id=cmp.com p_id; Ex: Display product details along with its sales inf ormations from the year 2014? select p.*,s.* f rom prod_dt ls p, sales_dt ls s where s.tid IN('Q1 -2014','Q2-2014','Q3-2014','Q4-2014') and s.pid= p.pid;
Ex: Display cust omer det ails and the ir account details who is f rom the cit y 'CHICAGO'? Ex: Display above inf ormation if the customer held DEMAT account and with m in balance 100000? select c.*,a.* f rom cust_dtls c, cust_act _dtls a where (c.cit y='CHICAGO' and (a.act_t ype='DEMAT' and a.act_bal>=100000)) and c.cid=a.cid;
Practical Examples: Ex: W rite a query to display customer names, account names and respective Act balances? By Using Equi Join: select cd.cname, at.act_name,cad.act_bal f rom CUST_DTLS cd,ACT_TYPES at,CUST_ACT_D TLS cad where cd.cno=cad.cust_code AND cad.act_t ype=at.act_t ype; By using Inner Join: select cd.cname, at.act_name,cad.act_bal f rom CUST_DTLS cd Inner Join CUST_ACT_DTLS cad
ON cd.cno=cad.cust _code Inner Join act_types at ON cad.act_t ype=at. act_t ype;
SQL> select cd.cname,cad.actno f rom cust_dtls cd inner join cust _act_dtls cad on cd.cno=cad.cust_code; CNAME -----------Anil Kiran vinod vinod Madhu Rocky Ching Fu
ACTNO ----------20035201471 20035201473 20035201472 20035201470 20035201474 20035201475 20035201476
7 rows select ed. SQL> select cd.cname,cd.cit y,cad.actno,cad.act_t ype,cad.act_bal 2 f rom cust_dtls cd inner join cust_act_dtls cad 3 on cd.cno=cad.cust_code; CNAME -----------Anil Kiran vinod vinod Madhu Rocky Ching Fu
CITY ACTNO ACT_T ACT_BAL ---------- ----------- ----- ---------Texas 20035201471 SAL 32000 Chicago 20035201473 SB 23000 Delhi 20035201472 DEMA T 123000 Delhi 20035201470 SB 49000 Delhi 20035201474 SAL 11000 Texas 20035201475 SB 13000 Chicago 20035201476 SAL 23000
7 rows select ed. SQL> select cd.cname,cd.cit y,cad.actno,cad.act_t ype,cad.act_bal 2 f rom cust_dtls cd inner join cust_act_dtls cad 3 on cd.cno=cad.cust_code 4 order by cad.act _bal; CNAME -----------Madhu Rocky Kiran Ching Fu Anil vinod vinod
CITY ACTNO ACT_T ACT_BAL ---------- ----------- ----- ---------Delhi 20035201474 SAL 11000 Texas 20035201475 SB 13000 Chicago 20035201473 SB 23000 Chicago 20035201476 SAL 23000 Texas 200352014 71 SAL 32000 Delhi 20035201470 SB 49000 Delhi 20035201472 DEMA T 123000
7 rows select ed.
SQL> select cd.cname,cd.cit y,cad.actno,cad.act_t ype,cad.act_bal 2 f rom cust_dtls cd inner join cust_act_dtls cad 3 on cd.cno=cad.cust_code 4 order by cad.act _bal desc; CNAME -----------vinod vinod Anil Ching Fu Kiran Rocky Madhu
CITY ACTNO ACT_T ACT_BAL ---------- ----------- ----- ---------Delhi 20035201472 DEMA T 123000 Delhi 20035201470 SB 49000 Texas 20035201471 SAL 32000 Chicago 20035201476 SAL 23000 Chicago 20035201473 SB 23000 Texas 20035201475 SB 13000 Delhi 20035201474 SAL 11000
7 rows select ed. SQL> select cd.cname,at.act_name,cad.actno,cad.act_bal 2 f rom cust_dtls cd,act_t ypes_inf o at,cust_act_dtls cad 3 where cd.cno=cad.cust_code 4 and 5 at.act_type=cad. act_t ype; CNAME -----------Anil Kiran vinod vinod Madhu Rocky Ching Fu
ACT_NAME ACTNO ACT_BAL -------------------- ----------- ---------Salar y A/c. 20035201471 32000 Savings Bank A/c. 20035201473 23000 Savings Bank A/c. 200352 01470 49000 Trading A/c. 20035201472 123000 Salar y A/c. 20035201474 11000 Savings Bank A/c. 20035201475 13000 Salar y A/c. 20035201476 23000
7 rows select ed. SQL> select cd.cname,at.act_name,cad.actno,cad.act_bal 2 f rom cust_dtls cd Inner Join cust_act _dtls cad 3 on cd.cno=cad.cust_code 4 Inner join act_t ypes_inf o at 5 on cad.act_t ype=at.act_type; CNAME -----------Anil Kiran vinod vinod Madhu Rocky Ching Fu
ACT_NAME ACTNO ACT_BAL -------------------- ----------- ---------Salar y A/c. 20035201471 32000 Savings Bank A/c. 20035201473 23000 Savings Bank A/c. 20035201470 49000 Trading A/c. 20035201472 123000 Salar y A/c. 20035201474 11000 Savings Bank A/c. 20035201475 13000 Salar y A/c. 20035201476 23000
7 rows select ed. Assignment:
1) W rite a query to display employee names,salaries ,job tit les, hiredat e and respect ive dept nam es based on salar y order? 2) W rite a query to display all "salesman and clerk" names,salar ies ,job titles, hiredate and respect ive dept names based o n salar y and job tit le order? 3) W rite a query to display product detils and company details of each product with min cost 5000 and max cost 40000 also manuf actured in current year with warrenty? 4) W rite a query to display the customer names, product nam es , cost , quantit y and sales amount generated by the customers from the cit y "Delhi" and with min sales amount 10000? 5) W rite a query to display customer code, customer name, phone_number and actno, act_bal who is maintaining below 500 0 act_balance? 6) W rite a query to display customer nam e, Phone number, actno,act_bal and act_name who has opted f or loan account?
SELF JOIN A self join is a join of a table to itself . This table appears t wice in the FROM clause and is f ollowed by table aliases that qualif y column names in the join condition. To perf orm a self join, Oracle Database combines and returns rows of the table that sat isf y the join condition.
ename ----kiran hari madhu smith scott allen soum ya john
employee -------cit y -----mumbai hyd hyd delhi mumbai hyd chennai delhi
Ex: select * f rom employee where ename='john'; [ NOT CORRECT ] Ex:
display emplyoee details who is living in a cit y where "john" is living? e1 --ename
e2 --ename
cit y
cit y -----
------
-----
------
kiran hari madhu
mumbai hyd hyd
kiran hari
mumbai hyd madhu
delhi mumbai hyd chennai delhi-----(matched)
smith scott allen soum ya john
hyd smith scott allen soum ya john delhi(matched)
delhi(matched) mumbai hyd chennai
goto f irst alias table and check the employee name "john" If it is there then get his "cit y" then the cit y f rom first alias table is compared with a ll cit y names in 2nd alias table If the cit y values are equal then get the records f rom second alias table. select e2.ename,e2. cit y f rom employee e1, employee e2 where e1.ename='john' and e1.cit y=e2.cit y; (or) select e2.* f rom employee e1, employee e2 where e1.ename='john' and e1.cit y=e2.cit y;
output: smith delhi john delhi Ex:
select e1.* f rom employee e1, employee e2 where e1.ename='john' and e1.cit y=e2.cit y;
output: If you display output f rom First alias table then y ou will get Duplicate data john john Ex:
delhi delhi
display the employee details who is working like 'smith'?
select e2.* f rom emp e1, emp e2 where e1.ename=' SMITH' AND e1.job=e2.job;
Ex:
display customer details who is living in a cit y where c5 is living? select c2.* f rom customers c1, customers c2 where c1.cid='c5' and c1.cit y=c2.cit y;
Ex:--DISPLAY EMPLOYEE DETAILS W HO IS W ORKING LIKE "CLARK" SELECT e2.* f rom emp e1, emp e2 where e1.ename='CLARK' AND E1.JOB=E2.JOB; output: EMPNO ENAME JOB MGR HIREDATE DEPTNO ---------- ---------- --------- ---------- --------- ---------- ---------7566 JONES MANAGER 7839 02 -APR- 81 20 7698 BLAKE MANAGER 7839 01 - MAY-81 30 7782 CLARK MANAGER 7839 09 -JUN-81 10
SAL
COMM
---------2975 2850 2450
OUTER JOINS These are used to display all data f rom one table and only matched data f rom other table. An outer joi n extends the result of a simple join. An outer join returns all rows that sat isf y the join condition and also returns some or all of those rows f rom one table f or which no rows f rom the other sat isf y the join condition. Types of outer joins: 3 1) Left outer join / left join Display all the data f rom lef t table and only matched data f rom right table. 2) Right out er joi n / Right join Display complete dat a f rom right table and only matched data f rom lef t table.
3) Full outer join / Full join: Display --matched data f rom both the tables --unmatched data f rom lef t table --unmatched data f rom right table syn-1: select col1, col2, col..... / * f rom table_1 [lef t join / right join / f ull join] table_2 ON table1.pk=table2.fk; Create a new table EMPCP from the table emp and delete deptno of emps working under deptno 30? Create table empcp As Select * f rom emp; Updat e empcp set deptno=NULL wher e deptno= 30; Ex: Display all employee details and if he is working under a dept then display his department details also? select e.*,d.* f rom emp e left outer join dept d on e.deptno=d.deptno; output: EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO DEPTNO DNAME LOC ---------- ---------- --------- ---------- --------- ---------- ---------- ---------- ---------- -------------- -----------7934 MILLE R CLERK 7782 23 -JAN-82 1300 10 10 ACCOUNTING NEW YORK 7839 KING PRESIDENT 17 -NOV-81 5000 10 10 ACCOUNTING NEW YORK 7782 CLARK MANA GER 7839 09 -JUN-81 2450 10 10 ACCOUNTING NEW YORK 7902 FORD ANALYST 7566 03 -DEC-81 3000 20 20 RESEARCH DALLAS 7876 ADA MS CLERK 7788 23 -MAY-87 1100 20 20 RESEARCH DALLAS 7788 SCOTT ANALYST 7566 19-APR-87 3000 20 20 RESEARCH DALLAS 7566 JONES MANA GER 7839 02 -APR-81 2975 20 20 RESEARCH DALLAS 7369 SMITH CLERK 7902 17 -DEC-80 800 20 20 RESEARCH DALLAS 7900 JAMES CLERK 7698 03 -DEC-81 950 30 30 SALES CHICAGO 7844 TURNER SALESMAN 7698 08 -SEP-81 1500 0 30 30 SALES CHICAGO 7698 BLAKE MANAGER 7839 01 -MAY-81 2850 30 30 SALES CHICAGO EMPNO ENAME DEPTNO DNAME
JOB LOC
MGR HIREDATE
SAL
COMM
DEP TNO
---------- ---------- --------- ---------- --------- ---------- ---------- ---------7654 MARTIN SALESMAN 7698 28 -SEP-81 1250 CHICAGO 7521 W ARD SALESMAN 7698 22 -FEB-81 1250 CHICAGO 7499 ALLEN SALESMAN 7698 20 -FEB-81 1600 CHICAGO
---------- -------------- -----------1400
30
30 SALES
500
30
30 SALES
300
30
30 SALES
1 4 r o ws s e l e c t e d . SQL>
Ex: Display all employee details and if he is working under a dept then display his department details also? SQL> select e.*,d.* 2 f rom empcp e lef t join dept d 3 on e.deptno= d.deptno; EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO DEPTNO DNAME LOC ---------- ---------- --------- ---------- --------- ---------- ---------- ---------- ---------- -------------- -----------7934 MILLE R CLERK 7782 23-JAN-82 1300 10 10 ACCOUNTING NEW YORK 7839 KING PRESIDENT 17 -NOV-81 5000 10 10 ACCOUNTING NEW YORK 7782 CLARK MANA GE R 7839 09-JUN-81 2450 10 10 ACCOUNTING NEW YORK 7902 FORD ANALYST 7566 03 -DEC-81 3000 20 20 RESEARCH DALLAS 7876 ADA MS CLERK 7788 2 3-MAY-87 1100 20 20 RESEARCH DALLAS 7788 SCOTT ANALYST 7566 19 -APR-87 3000 20 20 RESEARCH DALLAS 7566 JONES MANA GER 7839 02 -APR-81 2975 20 20 RESEARCH DALLAS 7369 SMITH CLERK 7902 17 -DEC-80 800 20 20 RESEARCH DALLAS 7900 JAMES CLERK 7698 03 -DEC-81 950 7844 TURNER SALESMAN 7698 08 -SEP-81 1500 0 7698 BLAKE MANAGER 7839 01 -MAY-81 2850 EMPNO ENAME JOB MGR HIREDATE SAL DEPTNO DNAME LOC ---------- ---------- --------- ---------- --------- ---------- ---------- ---------7654 MARTIN SALESMAN 7698 28 -SEP-81 1250 7521 W ARD SALESMAN 7698 22 -FEB-81 1250 7499 ALLEN SALESMAN 7698 20 -FEB-81 1600
COMM
DEPTNO
---------- -------------- -----------1400 500 300
1 4 r o ws s e l e c t e d . SQL>
NO TE Check the above out put , we are getting employees even they are not working under any department, it is not possible using EQUI or Inner joins.
Ex: Get all department details and also if a dept having emps then display its emps details also?
select e.*, d.* f rom emp cp e right outer join dept d on e.deptno=d.dept no; ex: EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO DEPTNO DNAME LOC ---------- ---------- --------- ---------- --------- ---------- ---------- ---------- ---------- -------------- -----------7782 CLARK MANA GER 7839 09-JUN-81 2450 10 10 ACCOUNTING NEW YORK 7839 KING PRESIDENT 17 -NOV-81 5000 10 10 ACCOUNTING NEW YORK 7934 MILLE R CLERK 7782 23-JAN-82 1300 10 10 ACCOUNTING NEW YORK 7566 JONES MANA GER 7839 02 -APR-81 2975 20 20 RESEARCH DALLAS 7902 FORD ANALYST 7566 0 3-DEC-81 3000 20 20 RESEARCH DALLAS 7876 ADA MS CLERK 7788 23 -MAY-87 1100 20 20 RESEARCH DALLAS 7369 SMITH CLERK 7902 17 -DEC-80 800 20 20 RESEARCH DALLAS 7788 SCOTT ANALYST 7566 19 -APR-87 3000 20 20 RESEARCH DALLAS 3 0 S AL E S C H I C AG O 4 0 O P E R AT I O N S BOSTON 1 0 r o ws s e l e c t e d .
SQL> “Check above output for bold faced lines, these are dept have no emps, still w e are getting the details, since the quer y is RIGHT OUTER JOIN. ” Ex: Get emp details and dept details using FULL OUTERJOIN? SQL> select * f rom empcp e f ull out er join dept d 2 on e.deptno= d.deptno; EMPNO ENAME JOB MGR HIREDATE SAL DEPTNO DNAME LOC ---------- ---------- --------- ---------- --------- ---------- ---------- ---------7369 SMITH CLERK 7902 17 -DEC-80 800 DALLAS 7499 ALLEN SALESMAN 7698 20 -FEB-81 1600 7521 W ARD SALESMAN 7698 22 -FEB-81 1250 7566 JONES MANA GER 7839 0 2-APR-81 2975 RESEARCH DALLAS 7654 MARTIN SALESMAN 7698 28 -SEP-81 1250 7698 BLAKE MANAGER 7839 01 -MAY-81 2850 7782 CLARK MANA GER 7839 09-JUN-81 2450 ACCOUNTING NEW YORK 7788 SCOTT ANALYST 7566 19 -APR-87 3000 RESEARCH DALLAS 7839 KING PRESIDENT 17 -NOV-81 5000 ACCOUNTING NEW YORK 7844 TURNER SALESMAN 7698 08 -SEP-81 1500 7876 ADA MS CLERK 7788 23 -MAY-87 1100 RESEARCH DALLAS
COMM
DEPTNO
---------- -------------- -----------20
20 RESEARCH
300 500 20
20
10
10
1400
20 10
20 10
0 20
20
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO DEPTNO DNAME LOC ---------- ---------- --------- ---------- --------- ---------- ---------- ---------- ---------- -------------- ------------
7900 JAMES CLERK 7902 FORD ANALYST RESEARCH DALLAS 7934 MILLE R CLERK ACCOUNTING NEW YORK
7698 03 -DEC-81 7566 03 -DEC-81 7782 23 -JAN-82
950 3000
20
1300
10
20 10
30 SALES CHICAGO 40 OPERATIONS BOSTON 1 6 r o ws s e l e c t e d . SQL>
Ex:
Display cust omer name, actno, act type and act nam e?
Ex: Display Female cust omers f rom CHICAGO, wit h their account s inf ormation who have DEMAT account? Ex: Display all customer details and if a customer having SALARY account then display account details also? Ex: Display all customer s personnel details and all accounts list of inf ormation? If a customer have an account then display his details along with his account details? NON-EQUI JOIN An nonequi (or thet a) join is an inner join statement that uses an unequal operat ion ( i.e: <>, >, <, !=, BETW EEN, etc.) to match rows f rom dif f erent tables. The converse of an nonequi join is a equi join operat ion. Ex: S E LE C T e .e n a m e , e . s a l, s . g ra d e 2 F RO M e m p e , sa l g r a de s 3 W HE R E e . sa l B ET W E E N s . l o s a l A N D s . h i s al ;
N ATUR AL JOIN The join is based on all the columns in the two tables that have the same name and data t ypes.The join creates, by using the NATURAL JOIN keywords.It selects rows f rom the two tables that have equal values in all matched columns.W hen specif ying colum ns that are involved in the natural join, do not qualif y the column name wit h a table name or table alias.
SQL: Syntax SELECT table1.column, table2.column FROM table1 NATURAL JOIN table2
A NATURAL JOIN is a JOIN operat ion that creates an implicit join clause f or you based on the common columns in the two tables bein g joined. Common columns are columns that have the same name in both tables. A NATURAL JOIN can be an INNER join, a LEFT OUTER join, or a RIGHT OUTER join. The def ault is INNER join. Assignments a)Get b)Get c)Get d)Get
product details and respective sales details? customer details and respective account details? Bookdetails along with author details? account t ypes along with account details?
SUB QUERIES A query with in ot her query is known as sub query. Sub queries are pr eferable to display out put f rom one table and having an input value f rom other table. Syntax: select ..... f rom table...where [=/IN/exists/not exists]( select ..... f rom table...... wher e ....[=/IN/exits/not exists]( select.......).....); Execution process: Here the execut ion process is always f rom innermost quer y to the outermost. Outer query<-----------o/p<------------Inner query< ----------o/p<---------Inner query
TYPES OF SUBQ UERIES: 2 1) Single row sub query: A sub quer y which returns single output value. In this case in bet ween the outer and inner queries we can use = operator. 2) Multi row sub query: A sub quer y which returns mult iple output values. In this case in bet ween the outer and inner queries we can use IN operator. ***CORREL ATED SU BQUERY: A sub quer y which depends on a value generated by outer query. Here the execut ion process is as f ollows. outer query-------o/p- ------> Inner query-------->o/p----------> | ^ | | V <------------------------------ ------------------------
First Outer query has to be executed and generates some out put values
Second based on these output values, sub query has to be executed From sub quer y we have some output and it is passed to outer query again Then outer quer y has to be executed again. Correlated subqueries reduce perf ormance. Correlated subqueries are usef ul in rever se business processes.
EXAMPLES Ex:
display department details of employee smith? select * f rom dept where deptno IN(select deptno f rom emp where ename='SMITH');
DEPTNO DNAME LOC ---------- -------------- ------------20 RESEARCH DALLAS By using join Quer y: select d.* f rom emp e, dept d where e. ename='SMI TH' and e.deptno=d. deptno; DEPTNO DNAME LOC ---------- -------------- ------------20 RESEARCH DALLAS Ex: Display employee details who is working under ACCO UNTING ? select * f rom emp where deptno=(select deptno f rom dept where dname='ACCOUNTING '); E MP N O E N A ME JOB MG R H I R E D A TE SAL COMM D E P TN O ---------- ---------- --------- ---------- --------- ---------- ---------- ---------7782 CLARK MA N A G E R 7839 09 -JUN-81 2450 10 7839 KING PRESIDENT 17 -NOV-81 5000 10 7 9 3 4 MI L L E R CLERK 7782 23 -JAN-82 1300 10
Ex:
display the department details of all managers? select * f rom dept where deptno IN(select deptno f rom emp where job=' MANAG ER');
DEPTNO DNAME LOC ---------- -------------- ------------10 ACCO UNTING NEW YORK 20 RESEARCH DALLAS 30 SALES CHICAGO
sample tables: comp_dt ls --------comp_code comp_name comp_countr y (PK) | | V---------------------------------------------------------------------- -------------->
prod_dt ls --------prodid prodname prod_comp_code (PK)
| | | | V prodcost
mfg
warrent y (FK)
SQL> select * from comp_dtls; C O MP _ C O D E C O MP _ N A ME CITY ---------- -------------------- ---------I101 IBM N EW YO R K W 20 2 W IPRO CHICAGO D303 DELL TE X A S S404 S A MS U N G KOREA SQL> SQL> select * from prod_dtls; PROD_CODE ---------ILTPQS I L T P XS ILTPZS W D TP G W CF L L D D T PW D L TP G D MB L Y S L TP C S L TP D S MB L G L X Y
P R O D _ N A ME C O S T MF G W ARRENTY ----------------------------- --------LAPTOP 45000 11 -FEB-12 4 YE A R S LAPTOP 3 4 0 0 0 2 1 - MA R - 1 2 4 YE A R S LAPTOP 40000 12 -FEB-13 7 YE A R S D E S K TO P 2 3 0 0 0 3 0 - O C T- 1 2 1 YE A R L I G H TS 3400 22 -JAN-13 2 YE A R S DESKTOP 2 7 0 0 0 1 3 - N O V - 1 2 5 YE A R S LAPTOP 46000 08 -JAN-13 2 YE A R S MO B I L E 32000 10 -JAN-13 LAPTOP 34000 04 -DEC-12 6 MO N T H S LAPTOP 45000 09 -DEC-12 2 YE A R S MO B I L E 24000 10-JAN-12 1 YE A R
C O MP _ C O D E ---------- ---------I101 I101 I101 W 202 W 202 D303 D303 D303 S404 S404 S404
P R O D _ C O D E P R O D _ N A ME C O S T MF G W A R R E N T Y C O MP _ C O D E ---------- -------------------- ---------- --------- ---------- ---------S MB L G L X G MO B I L E 21500 02 -FEB-13 1 YEAR S404 1 2 r o ws s e l e c t e d .
Ex:
display product details f rom the company IBM? select * f rom prod_dtls where comp_code= (select comp_code f rom comp_dtls where comp_name=' IBM’);
P R O D _ C O D E P R O D _ N A ME C O S T MF G W ARRENTY ---------- -------------------- ---------- --------- ---------- ---------ILTPQS LAPTOP 4 5 0 0 0 1 1 - F E B - 1 2 4 YE A R S I101 I L T P XS LAPTOP 3 4 0 0 0 2 1 - MA R - 1 2 4 YE A R S I101 ILTPZS LAPTOP 4 0 0 0 0 1 2 - F E B - 1 3 7 YE A R S I101
C O MP _ C O D E
S Q L > S E L E C T * F R O M P R O D _ D TL S 2 W H E R E C O MP _ C O D E I N 3 ( 4 S E L E C T C O MP _ C O D E F R O M C O MP _ D TL S 5 W H E R E C O MP _ N A M E I N ( ' I B M' , ' S A MS U N G ' ) 6 ); P R O D _ C O D E P R O D _ N A ME C O S T MF G W A R R E N T Y C O MP _ C O D E ---------- -------------------- ---------- --------- ---------- ---------ILTPQS LAPTOP 4 5 0 0 0 1 1 - F E B - 1 2 4 YE A R S I101 I L T P XS LAPTOP 3 4 0 0 0 2 1 - MA R - 1 2 4 YE A R S I101 ILTPZS LAPTOP 4 0 0 0 0 1 2 - F E B - 1 3 7 YE A R S I101 S L TP C LAPTOP 3 4 0 0 0 0 4 - D E C - 1 2 6 MO N T H S S 4 0 4 S L TP D LAPTOP 4 5 0 0 0 0 9 - D E C - 1 2 2 YE A R S S404 S MB L G L X Y MO B I L E 24000 10 -JAN-12 1 YEAR S404 S MB L G L X G MO B I L E 21500 02 -FEB-13 1 YEAR S404 7 r o ws s e l e c t e d .
Ex:
display product details f rom the companies other than IBM, samsung?
S E L E C T * F R O M P R O D _ D TL S W H E R E C O MP _ C O D E N O T I N ( S E L E C T C O MP _ C O D E F R O M C O MP _ D TL S W HE R E C O MP _ N A ME I N ( ' I B M' , ' S A MS U N G ' ) ); P R O D _ C O D E P R O D _ N A ME C O S T MF G W A R R E N T Y C O MP _ C O D E ---------- -------------------- ---------- --------- ---------- ---------W CF L L LIGHTS 3 4 0 0 2 2 - J A N - 1 3 2 YE A R S W 202 W D TP G DESKTOP 2 3 0 0 0 3 0 - O C T- 1 2 1 Y E A R W 202 D MB L Y MO B I L E 32000 10 -JAN-13 D303 D L TP G L A P TO P 4 6 0 0 0 0 8 - J A N - 1 3 2 YE A R S D303 D D T PW DESKTOP 27000 13 -NOV-12 5 YEARS D303
Ex:
display company names of the prod ids ' W DTPG','DMBLY' ?
SQL> select comp_name f rom comp_dtls 2 where comp_code in 3 ( 4 select comp_code f rom prod_dtls 5 where prod_code in('W DTPG','DMBLY') 6 ); CO MP_NAME -------------------DELL W IPRO
CORREL ATED SUBQUERIES These sub queries use 2 oper ators either EXISTS OR NOT EXISTS EXISTS It returns true if a sub query f etches at least one value. If it returns TRUE then outer quer y will display the result. NOT EXI STS It returns true if a sub query f etches no values at all. If it returns TRUE then outer query will display the result.
Ex: display department details which is having at least one employee with in it? select d.* f rom dept d where exists( select 10 f ro m empcp e where e.dept no=d.dept no); DEPTNO DNAME LOC ---------- -------------- ------------10 ACCO UNTING NEW YORK 20 RESEARCH DALLAS Ex: display department details which not having at least one employee with in it? select d.* f rom dept d where not exists( select 10 f rom emp cp e where e.deptno=d. deptno); DEPTNO DNAME LOC ---------- -------------- ------------30 SALES CHICAGO 40 OPERATIONS BOSTON ex: Display compnies information f rom which we are maintaining at least one product Ex:
Display company det ails f rom which we have no pr oducts at all?
SQL> select c.* f rom comp_dtls c 2 where not exists 3 (select 1 f rom prod_dt ls p where p.comp_code= c. comp_code); no rows selected Assignments: Get customer details have no accounts at all? Get customer details who has at least one account? Get account t ypes inf ormation having at least one customer f or that?
Get account det ails opted by the custom ers f r om the cit y “HYD”? Get account det ails of “vinod”? Get dept details of empnos 7654, 7902?
ALL The ALL comparison condition is used to compare a value to a list or subquery. It must be preceded by =, !=, >, <, <=, >= and followed by a list or subquery. When the ALL condition is followed by a list, the optimizer expands the initial condition to all elements of the list and strings them together with AND operators, as shown below. SELECT empno, sal FROM emp WHERE sal > ALL (2000, 3000, 4000);
EMPNO
SAL
---------- ---------7839
5000
SQL>
-- Transformed to equivalent statement without ALL.
SELECT empno, sal FROM emp WHERE sal > 2000 AND sal > 3000 AND sal > 4000;
EMPNO
SAL
---------- ---------7839
5000
SQL>
When the ALL condition is followed by a subquery, the optimizer performs a two-step transformation as shown below.
SELECT e1.empno, e1.sal FROM emp e1 WHERE e1.sal > ALL (SELECT e2.sal FROM emp e2 WHERE e2.deptno = 20);
EMPNO
SAL
---------- ---------7839
5000
SQL>
-- Transformed to equivalent statement using ANY.
SELECT e1.empno, e1.sal FROM emp e1 WHERE NOT (e1.sal <= ANY (SELECT e2.sal FROM emp e2 WHERE e2.deptno = 20));
EMPNO
SAL
---------- ---------7839
5000
SQL>
-- Transformed to equivalent statement without ANY.
SELECT e1.empno, e1.sal FROM emp e1 WHERE NOT EXISTS (SELECT e2.sal FROM emp e2
WHERE e2.deptno = 20 AND e1.sal <= e2.sal);
EMPNO
SAL
---------- ---------7839
5000
SQL>
Assuming subqueries don't return zero rows, the following statements can be made for both list and subquery versions:/p>
"x = ALL (...)": The value must match all the values in the list to evaluate to TRUE. "x != ALL (...)": The value must not match any values in the list to evaluate to TRUE. "x > ALL (...)": The value must be greater than the biggest value in the list to evaluate to TRUE. "x < ALL (...)": The value must be smaller than the smallest value in the list to evaluate to TRUE. "x >= ALL (...)": The value must be greater than or equal to the biggest value in the list to evaluate to TRUE. "x <= ALL (...)": The value must be smaller than or equal to the smallest value in the list to evaluate to TRUE.
If a subquery returns zero rows, the condition evaluates to TRUE. In the following example, the subquery returns zero rows, which means the whole expression "sal > ALL (zero rows)" evaluates to TRUE, so all rows are displayed. -- The following query returns zero rows. SELECT e2.sal FROM emp e2 WHERE e2.deptno = 100;
no rows selected
SQL>
-- Place it in the subquery of an ALL and the -- condition "sal > ALL (zero rows)" evaluates to TRUE -- so all rows are displayed. SELECT e1.empno, e1.sal FROM emp e1
WHERE e1.sal > ALL (SELECT e2.sal FROM emp e2 WHERE e2.deptno = 100);
EMPNO
SAL
---------- ---------7369
800
7900
950
7876
1100
7521
1250
7654
1250
7934
1300
7844
1500
7499
1600
7782
2450
7698
2850
7566
2975
7788
3000
7902
3000
7839
5000
SQL>
ANY The ANY comparison condition is used to compare a value to a list or subquery. It must be preceded by =, !=, >, <, <=, >= and followed by a list or subquery. When the ANY condition is followed by a list, the optimizer expands the initial condition to all elements of the list and strings them together with OR operators, as shown below. SELECT empno, sal FROM emp WHERE sal > ANY (2000, 3000, 4000);
EMPNO
SAL
---------- ---------7566
2975
7698
2850
7782
2450
7788
3000
7839
5000
7902
3000
SQL>
-- Transformed to equivalent statement without ANY.
SELECT empno, sal FROM emp WHERE sal > 2000 OR sal > 3000 OR sal > 4000;
EMPNO
SAL
---------- ---------7566
2975
7698
2850
7782
2450
7788
3000
7839
5000
7902
3000
SQL>
When the ANY condition is followed by a subquery, the optimizer performs a single transformation as shown below. SELECT e1.empno, e1.sal FROM emp e1 WHERE e1.sal > ANY (SELECT e2.sal
FROM emp e2 WHERE e2.deptno = 10);
EMPNO
SAL
---------- ---------7839
5000
7902
3000
7788
3000
7566
2975
7698
2850
7782
2450
7499
1600
7844
1500
SQL>
-- Transformed to equivalent statement without ANY.
SELECT e1.empno, e1.sal FROM emp e1 WHERE EXISTS (SELECT e2.sal FROM emp e2 WHERE e2.deptno = 10 AND e1.sal > e2.sal);
EMPNO
SAL
---------- ---------7839
5000
7902
3000
7788
3000
7566
2975
7698
2850
7782
2450
7499
1600
7844
1500
SQL>
Assuming subqueries don't return zero rows, the following statements can be made for both list and subquery versions:
"x = ANY (...)": The value must match one or more values in the list to evaluate to TRUE. "x != ANY (...)": The value must not match one or more values in the list to evaluate to TRUE. "x > ANY (...)": The value must be greater than the smallest value in the list to evaluate to TRUE. "x < ANY (...)": The value must be smaller than the biggest value in the list to evaluate to TRUE. "x >= ANY (...)": The value must be greater than or equal to the smallest value in the list to evaluate to TRUE. "x <= ANY (...)": The value must be smaller than or equal to the biggest value in the list to evaluate to TRUE.
If a subquery returns zero rows, the condition evaluates to FALSE. In the following example, the subquery returns zero rows, which means the whole expression "sal > ANY (zero rows)" evaluates to FALSE, so no rows are displayed. -- The following query returns zero rows. SELECT e2.sal FROM emp e2 WHERE e2.deptno = 100; no rows selected SQL> -- Place it in the subquery of an ANY and the -- condition "sal > ANY (zero rows)" evaluates to FALSE -- so no rows are displayed. SELECT e1.empno, e1.sal FROM emp e1 WHERE e1.sal > ANY (SELECT e2.sal FROM emp e2 WHERE e2.deptno = 100);
no rows selected
SQL>
SOME The SOME and ANY comparison conditions do exactly the same thing and are completely interchangeable.
VIEWS Views ar e known as logical tables. They represent the data of one of more tables. A view derives its data f rom the tables on which it is based. These tables are called base tables. Views can be based on actual tables or another view also. W hatever DML operations you perf ormed on a view they actually aff ect the base table of the view. You can treat views same as any other table. You can Quer y, Insert, Update and delete f rom vi ews, just as any other table. Views ar e ver y powerf ul and handy since they can be treated j ust like any other table but do not occupy the space of a table.
QUERY EXECUTION PROCESS <---------------------- DATA BASE SERVER- ----------------------> CLIENT/USER
ORACLE/DB ENGINE
DATABASE
Request DATABASE ( query )------------->ENGINE--------------->-- ^---------------->DB | | | sql stmt executor | | | | Data dictionar y( met a data) pl/sql engine | | | | | |--------------------Actual Dat a | ----------------------| | selecting f rom | | table V | 1<------------------------| 2<------------------------| 3<------------------------| 4<------------------------| : n hits to DB | n<------------------------| | | | output selecting f rom view |
<---------CACHE <---------------------------------------<---------MEMO RY Only one HIT to the DB <--------<--------: <---------
View: It It It It
is a database object contains logical copy of selected table data. can be created based on f requently using data. reduces number of hits to the dat abase. improves t he perf ormance of queries and database.
Types of View s: 5 1) Simple view / updateabl e view : It is created based on single table data. I t allows dm l operations. syn:
create view < view_name> AS select ...... f rom table_name wher e ;
2) Composite view or read onl y view : It is created based on morethan one table data. It doesnt allow dml operat ions. Advantage: It will reduce wr it ing number of join queries again and again. Syntax:
create view < vw_name> AS select ........ f rom table1, table2,..... where <join -cond>;
NO TE: By def ault the Client user not having permission to creat e views. That privillage is assigned by DBA. Ex: wr ite a quer y to create a view which cont ains managers inf ormation f rom emp table? create view vw_mgr_inf o as select * f rom emp where job=' MANAG ER'; Error: Insuff icient pr ivillages ex:
connect to dba system manager
orcl Ex:
grant create view to manju; grant succeeded
Ex:
conn manju / welcom e@orcl connected.
Ex:
create view vw_mgr_inf o as select * f rom emp where job=' MANAG ER'; view created.
HOW TO SELECT THE DATA FRO M VIEW ? SYN: SELECT * f rom ; Ex:
select * f rom vw_mgr_inf o;
sample output: E M P NO E N AM E JOB MG R H IR ED AT E S AL CO M M DE PT NO - - - - - - - - - - - --------- ------ --- - - - - - - - - - - ---- ----- - -- ------- ---- ------ - - - - - - - - - 75 6 6 J O N E S M AN AG E R 78 3 9 02 - A P R- 81 2 9 75 76 9 8 B L A K E M AN A G E R 7 83 9 0 1 - MA Y- 8 1 2 85 0 77 8 2 CL A R K M AN AG E R 78 3 9 09 - J UN - 8 1 2 45 0
Ex:
display manager names and salar ies? select ename,sal f rom vw_mgr_inf o;
ENAME SAL ---------- ---------JONES 2975 BLAKE 2850 CLARK 2450 NOTE SUB TABLES M AI NTAI NS D AT A STATIC ALLY VIEWS M AI NTAI NS D ATA DYN AMIC ALLY. sample hands-on: sub table:
20 30 10
create tabl e emp_mgr as select * f rom emp where job=' MANAGER'; Ex: select * f rom emp_mgr; E M P NO E N AM E JOB MG R H IR ED AT E S AL CO M M DE PT NO - - - - - - - - - - - --------- ------ --- - - - - - - - - - - ---- ----- - -- ------- ---- ------ - - - - - - - - - 75 6 6 J O N E S M AN AG E R 78 3 9 02 - A P R- 81 2 9 75 20 76 9 8 B L A K E M AN A G E R 7 83 9 0 1 - MA Y- 8 1 2 85 0 30 77 8 2 CL A R K M AN AG E R 78 3 9 09 - J UN - 8 1 2 45 0 10 11 2 2 d in es h M AN A G E R 1 2 - M A Y- 1 3 4 5 00 700 40
Ex:
creating a view cont aining managers inf o?
create view vw _mgr_info as select * f rom emp where job=' MANAG ER'; Ex: select * f rom vw_mgr_inf o; E M P NO E N AM E JOB MG R H IR ED AT E S AL CO M M DE PT NO - - - - - - - - - - - --------- ------ --- - - - - - - - - - - ---- ----- - -- ------- ---- ------ - - - - - - - - - 75 6 6 J O N E S M AN AG E R 78 3 9 02 - A P R- 81 2 9 75 20 76 9 8 B L A K E M AN A G E R 7 83 9 0 1 - MA Y- 8 1 2 85 0 30 77 8 2 CL A R K M AN AG E R 78 3 9 09 - J UN - 8 1 2 45 0 10 11 2 2 d in es h M AN A G E R 1 2 - M A Y- 1 3 4 5 00 700 40
Ex: ins ert into emp values(2233,'naresh',' MANAGER',NULL,'21 - MAY14',3400,200,10); 1 rows inserted. Ex: In the below sub table data we cannot see any new manager inf ormation. select * f rom emp_mgr; E M P NO E N AM E JOB MG R H IR ED AT E S AL CO M M DE PT NO - - - - - - - - - - - --------- ------ --- - - - - - - - - - - ---- ----- - -- ------- ---- ------ - - - - - - - - - 75 6 6 J O N E S M AN AG E R 78 3 9 02 - A P R- 81 2 9 75 20 76 9 8 B L A K E M AN A G E R 7 83 9 0 1 - MA Y- 8 1 2 85 0 30 77 8 2 CL A R K M AN AG E R 78 3 9 09 - J UN - 8 1 2 45 0 10 11 2 2 d in es h M AN A G E R 1 2 - M A Y- 1 3 4 5 00 700 40
In the below data w e are seeing manager information since w e are taking dat a from the vi ew . select * f rom vw_mgr_inf o; E M P NO E N AM E DE PT NO
JOB
MG R H IR ED AT E
S AL
CO M M
- - - - - - - - - - - --------- ------ --- - - - - - - - - - - ---- ----- - -- ------- ---- ------ - - - - - - - - - 75 6 6 J O N E S M AN AG E R 78 3 9 02 - A P R- 81 2 9 75 76 9 8 B L A K E M AN A G E R 7 83 9 0 1 - MA Y- 8 1 2 85 0 77 8 2 CL A R K M AN AG E R 78 3 9 09 - J UN - 8 1 2 45 0 11 2 2 d in es h M AN A G E R 1 2 - M A Y- 1 3 4 5 00 700 22 3 3 n ares h M AN A G E R 2 1 - M A Y- 1 4 3 4 00 200
Ex:
20 30 10 40 10
delete f rom vw_m gr_inf o where empno in( 1122,2233); select * f rom emp_mgr; select * f rom vw_mgr_inf o; select * f rom emp where job=' MANAGER';
E M P NO E N AM E JOB MG R H IR ED AT E S AL CO M M DE PT NO - - - - - - - - - - - --------- ------ --- - - - - - - - - - - ---- ----- - -- ------- ---- ------ - - - - - - - - - 75 6 6 J O N E S M AN AG E R 78 3 9 02 - A P R- 81 2 9 75 20 76 9 8 B L A K E M AN A G E R 7 83 9 0 1 - MA Y- 8 1 2 85 0 30 77 8 2 CL A R K M AN AG E R 78 3 9 09 - J UN - 8 1 2 45 0 10 11 2 2 d in es h M AN A G E R 1 2 - M A Y- 1 3 4 5 00 700 40 E M P NO E N AM E JOB MG R H IR ED AT E DE PT NO - - - - - - - - - - - --------- - ----- --- - - - - - - - - - - ---- ----- - -- ------- ---- -----75 6 6 J O N E S M AN AG E R 78 3 9 02 - A P R- 81 76 9 8 B L A K E M AN A G E R 7 83 9 0 1 - MA Y- 8 1 77 8 2 CL A R K M AN AG E R 78 3 9 09 - J UN - 8 1 E M P NO E N AM E JOB MG R H IR ED AT E DE PT NO - - - - - - - - - - - --------- ------ --- - - - - - - - - - - ---- ----- - -- ------- ---- -----75 6 6 J O N E S M AN AG E R 78 3 9 02 - A P R- 81 76 9 8 B L A K E M AN A G E R 7 83 9 0 1 - MA Y- 8 1 77 8 2 CL A R K M AN AG E R 78 3 9 09 - J UN - 8 1
S AL
CO M M
---------2 9 75 2 85 0 2 45 0 S AL ---------2 9 75 2 85 0 2 45 0
20 30 10 CO M M 20 30 10
2) RE AD ONLY VIEWS / COMPOSITE VIEWS EX AM PLES Ex: wr ite a quer y to maintain a view containing managers ename, sal, job, dept name and dept loc? create view vw_emp_dept_inf o as select ename,sal,job,dname, loc f rom emp,dept where job=' MANAG ER' and emp.deptno=dept.deptno; Ex: Display manager details and their dept details?
select * f rom VW _EMP_DEPT_INFo; ENAME SAL JOB DNAME LOC ---------- ---------- --------- -------------- ------------JONES 2975 MANAGER RESEARCH DALLAS BLAKE 2850 MANAGER SALES CHICAGO CLARK 2450 MANAGER ACCOUNTING NEW YORK Ex:
display manager names and their depart ment names? select ename,dname from vw_emp_dept_inf o;
ENAME DNAME ---------- -------------JONES RESEARCH BLAKE SALES CLARK ACCOUNTING
3) Inline view An inline view is a SELECT statement in the FRO M -clause of another SELECT statement. I n-line views are commonly used to simplif y complex queries by removing join operations and condensing several separate queries into a single query.
Ex: sub quer y Generally sub quer y is to be wr itten inside the W HERE clause. Display employee details working under account ing department? select * f rom emp where deptno=( select deptno f rom dept where dname='ACCOUNTING'); Ex: Inline view SELECT * FRO M ( SELECT deptno, count(*) emp_count FROM emp GROUP BY deptno ) emp, dept W HERE dept.deptno = emp.deptno; Explanation: deptno -----10
emp_count --------4
Ex:
20 30
6 7
deptno -----10
dname loc ----- -----------Accounting chicago
20
REsearch
Texas
30
Sales
Dallas
emp_count 4
6 7
Ex: display the employees who earn the highest salar y in each department ? SELECT * FRO M ( SELECT deptno, max(sal) maxsal FROM emp GROUP BY deptno ) b, emp a W HERE a.sal= b.maxsal and a.deptno = b.deptno; output: DEPTNO M A XS A L EMPNO ENA ME JOB MGR HIREDATE SAL DEPTNO ---------- ---------- ---------- ---------- --------- ---------- --------- ---------- ---------- ---------30 2850 7698 BLAKE MANAGER 7839 01-MAY-81 2850 20 3000 7788 SCOTT ANALYST 7566 19 -APR-87 3000 10 5000 7839 KING PRESIDENT 17 -NOV-81 5000 20 3000 7902 FORD ANALYST 7566 03 -DEC-81 3000
Ex: SELECT d.dept_id, d.name, emp_cnt.tot FRO M department d INNER JOIN (SELECT dept_id, COUNT(*) tot FRO M employee GROUP BY dept_id) emp_cnt ON d.dept_id = emp_cnt.dept_id;
DEPT_ID NAME ---------- -------------------- ----------
TOT
COMM 30 20 10 20
10 ACCO UNTING
3
20 RESEARCH 30 SALES
5 6
Ex: Display comp_codes, number of products f rom each company code and also display company det ails? select * f rom (select comp_code,count(*) from prod_dtls group by comp_code) p,comp_dtls C where p.comp_co de=c.comp_code;
ex: select d.deptno,d.dname,d.loc, e.deptno,count(e.empno) f rom emp e ,dept d where e. deptno=d.deptno group by d.deptno,d. dname,d. loc,e.deptno;
iv)
Creating FORCE VIEWS:
A view can be created even if the def ining query of the view cannot be executed, as long as the CREATE VIEW command has no syntax errors. W e call such a view a view with errors. For example, if a view ref ers --to a non-existent t able or --an inval id column of an exist ing table, or --if the owner of the view does not have the required privileges, then the view can st ill be created and ent ered into the dat a dictionar y. You can only create a view with errors by using the FORCE option of the CREATE VIEW command: CREATE FORCE VI EW AS ...; Ex: create FORCE view vw_act_dtls_Sb as select * f rom cust_act_dtls where act_t ype='SB' order by act _bal;
Materialized Views in Oracle A materialized view in Oracle is a database object that contains the results of a query. They are local copies of data located remotely, or are used to create summar y tables based on aggregations of a table's dat a. Mater ialized views, which store data based on remote tables are also, know as snapshots. A materialized view c an quer y tables, views, and ot her materialized views. Collectively t hese ar e called master tables (a replicat ion term) or detail tables (a data warehouse term). For replication purposes, materialized views allow you to maintain copies of remote data on you r local node. These copies are read - only. If you want to update the local copies, you have to use t he Advanced Replication f eature. You can select dat a f rom a materialized view as you would f rom a table or view. For data warehousing purposes, the mat erialized views com monly created are aggregate views, single -table aggregate views, and join views. In this article, we shall see how to create a Mater ialized View in Oracle and discuss Ref resh Option of the view. In replicat ion environments, the materi alized views commonly created are primar y key, rowid, and subquer y mater ialized views. Primar y Key Materialized Views The f ollowing statement creates the prim ary -key materialized view on the table emp located on a remote database. SQL>
CREATE MATERIALI ZED VIEW mv_emp_pk REFRESH FAST START W ITH SYSDATE NEXT SYSDATE + 1/48 W ITH PRI MARY KEY AS SELECT * FRO M emp@remote_db;
Mater ialized view cr eated. Note: W hen you create a materialized view using the FAST option you will need to create a view log on the master tables(s) as shown below: SQL> CREATE MATERIALIZED VIEW LOG ON emp; Mater ialized view log created. Rowid Mater ialized Views The f ollowing statement creates the rowi d materialized view on table emp located on a remote database: SQL>
CREATE MATERIALI ZED VIEW mv_emp_rowid REFRESH W ITH ROW ID
AS SELECT * FRO M emp@remote_db; Mater ialized view log created.
Subquery Materi alized View s The f ollowing statement creates a subquery mater ialized view based on the emp and dept tables loc ated on the remote database: SQL> CREATE MATERIALIZED VIEW mv_empdept AS SELECT * FRO M emp@remote_db e W HERE EXISTS (SELECT * FRO M dept @remote_db d W HERE e.dept_no = d.dept_no)
REFRESH CL AUSE [ref resh [f ast|complete|f orce] [on demand | commit ] [start with date] [next date] [with {pr imar y key|r owid}]] The ref resh option specif ies: a. The ref resh method used by Oracle to ref resh data in materialized view b. W hether the view is primar y key based or row -id based c. The time and inter val at which the view is to be ref reshed Refresh Method - FAST Clause The FAST ref reshes use the mater ialized view logs (as seen above) to send the rows that have changed f rom master tables to the materialized view. You should create a materialized view log f or the master table s if you specif y the REFRESH FAST clause. SQL> CREATE MATERIALIZED VIEW LOG ON emp; Mater ialized view log created.
Mater ialized views are not eligible f or f ast refresh if the def ined subquer y contains an analyt ic f unction. Refresh Method - COMPLETE Clause The complete ref resh re -creates the entire materialized view. If you request a complete ref resh, Oracle perf orms a complete ref resh even if a f ast ref resh is possible.
Re f r e sh M eth od - F OR C E Cl au s e Wh en y ou sp e ci fy a FOR C E cl au s e, O ra cl e wi l l pe rf o rm a fa st r ef r e sh i f on e i s p o ssi bl e o r a c om pl et e r e f r esh oth e r w i se . If yo u d o n ot sp e ci fy a r ef r e sh me th od (FAS T , C OM PL E T E , o r F OR C E) , F OR C E i s th e d e fau l t. PRI MAR Y K EY an d R O WID Clau s e W IT H PR IM ARY K E Y i s u s e d t o c r eat e a pri ma r y k e y mat e ri a li z ed vi e w i . e . th e m at e ri al i z ed vi e w i s ba s ed on th e p r i mar y k ey o f t h e ma st e r tabl e i n ste ad o f R O W ID ( f o r R OW ID cl au s e) . P R IM ARY K E Y i s th e d e fau l t opti on . T o u s e th e PR IM AR Y K EY cl au s e y ou sh o u l d h av e d e fi n ed PR IMA RY K E Y on th e m as t er t abl e o r el s e y ou sh ou l d u s e RO W ID ba s ed m at e r i ali z ed vi ew s . Pri ma r y k e y ma t eri a li z ed vi e w s al l ow m at eri al i z ed vi e w ma s te r ta bl es t o be r e o rgan i z ed wi th ou t a ff e cti n g th e el i gi bili ty of th e m at e r i ali z ed vi ew f or fa st r e f r e sh . Ro wi d m at e ri al i z ed vi ew s sh ou l d h a v e a si n gl e m ast e r t abl e an d can n ot c on tai n an y o f th e f ol l owi n g:
Di sti n ct o r a gg r ega t e fu n cti o n s GR O UP B Y Su bqu e ri e s , J oi n s & S et op e rati on s
Timing the refresh The START W ITH clause tells the database when to perf orm the f irst replicat ion f rom the master table to the local base table. It should evaluate to a f uture point in time. The NEXT clause specif ies the inter val bet ween ref reshes SQL>
CREATE MATERIALI ZED VIEW mv_emp_pk REFRESH FAST START W ITH SYSDATE NEXT SYSDATE + 2 W ITH PRI MARY KEY AS SELECT * FRO M emp@remote_db;
Mater ialized view cr eated. In the above example, the f irst copy of the materialized view is made at SYSDATE and the interval at which the r ef resh has to be perf ormed is ever y two days. Summary Mater ialized Views t hus of f er us f lexibilit y of basing a view o n Primar y key or ROW ID, specif ying ref resh methods and specif ying time of automatic ref reshes.
Replacing/ Alteri ng View s To alter the def inition of a view, you must replace the view using one of the f ollowing methods:
A view can be dropped and then re-creat ed. W hen a view is dropped, all grants of corresponding view pr ivileg es are revoked f rom roles and users. Af ter the view is re -created, necessar y privileges must be regranted. A view can be replaced by redef ining it with a CREATE VIEW statement that contains the OR REPLACE option. This option replaces the current def inition of a view, but preserves the present securit y author izat ions.
For example, assume that you creat e the ACCO UNTS_STAFF view, as given in a previous example. You also grant sever al object privileges to roles and other users. However, now you realize that you must redef ine the ACCOUNTS_STAFF view to correct the department number specif ied in the W HERE clause of the def ining query, because it should have been 30. To preser ve the grants of object privileges t hat you have made, you can replace the current version of the ACCO UNTS_STAFF view with the f ollowing statement: CREATE OR REPLACE VI EW Accounts_staff AS SELECT Empno, Ename, Deptno FROM Emp W HERE Deptno = 30 W ITH CHECK OPTION CONSTRAINT ica_Accounts_cnst;
Replacing a view has the f ollowing eff ects:
Replacing a view replaces the view's def init ion in the data dictionar y. All under lying objects ref erenced by the view are not af f ected. If previously def ined but not includ ed in t he new view def init ion, then the constraint associated with the W ITH CHECK OPTION f or a view's def init ion is dropped. All views and PL/SQ L program units dependent on a replaced view become invalid.
Referencing Invalid View s W hen a user attempts to ref erence an invalid view, Oracle ret urns an error message to the user: ORA-04063: view ' view_name' has errors This error message is returned when a view exists but is unusable due to errors in its query ( whether it had errors when originally cr eat ed or it was
created successf ully but became unusable later because underlying objects were altered or dropped).
Dropping View s Use the SQL command DROP VIEW to drop a view. For exam ple: DROP VI EW Accounts_staff ;
Listing Information about VIEWS. To see how many views are there in your schema. Give the following query. select * from user_views; To see which columns are updatable in join views. Data Dict ionaries which shows which columns are updatable. View Name Descr ipt ion USER_UPDATABLE_COLUMNS Shows all columns in all tables and views in the user's schema that are modif iable DBA_UPDATABLE_COLUMNS Shows all columns in all tables and views in the DBA schema that are modif iable ALL_UPDATABLE_VIEW S Shows all columns in all tables and views that are modif iable
INDEXES Indexes are opt ional structures associated with tables and clusters that allow SQL queries to execute more quickly against a table. Just as the index in t his manual helps you locate inf ormation f aster than if there were no index, an Oracle Database index provides a f aster access path to table data. You can use indexes without rewr iting any queries. Your results ar e the same, but you see them more quickly. Oracle Database provides sever al indexing schemes that provide complementar y perf ormance f unctionalit y. These are:
B-tree indexes: the def ault and the most common B-tree cluster indexes: def ined specif ically f or cluster Hash cluster indexes: def ined specif ically f or a hash cluster Global and local indexes: relate to part itioned tables and indexes Reverse key indexes: most usef ul f or Oracle Real Application Clusters applications Bitmap indexes: compact; work best f or columns with a small set of values Funct ion-based indexes: contain the precomputed value of a f unction/ expr ession Domain indexes: specif ic to an application or cartridge.
Indexes are logically and physically independent of the data in the associated table. Being independent structures, they require st orage space. You can create or dr op an index without aff e cting the base tables, database applications, or other indexes. The database automat ically maintains indexes when you insert, update, and delet e rows of the associated table. If you drop an index, all applicat ions cont inue to wor k. However, access to previ ously indexed data might be slower.
Index is a table like object which maintains ordered data of the column physically. It reduces number of comparisons to make, to f etch the required data. It f astens the search as much as f ast. It occupies physical disk space. i)
Index contains 2 par ts Data part ii) Address part ( ROWID)
ROWID is a pseudo column / virtual column. It contains Physical address of each record. W e can access the values of rowid , but we cannot modif y them.
This (ROWID) address is a combination of the f ollowing. {f ileno
datablock_no
record_no}.
This f irst data block of a table is indicated with 0. And the f irst record in each data block is indicated with 0. Ex:
let us consider the f ile [ table ] number is 555. select * f rom prod_dtls;
555.000.000 555.000.001 555.000.002 : 555.000.099-----> this is the last recor d in f irst data block of table no 555. (100 records) 555.001.000 555.001.001 555.001.002 : : 555.001.999------> this is the last record in second data block of table no 555 ( 1000 records) 555.002.000 555.002.001 : 555.002.009------> this is the last record in the third data block of table no 555 ex: salaries(table) Idx_sal(indexed table) ------------------------------1--9000 2--1000 2--1000 5--1000 3--2000 8--1000 4--9000 3--2000 5--1000 7--2000 6--2500 9--2000 7--2000 6--2500 8--1000 1--9000 9--2000 4--9000 10--9000 10-9000 Bef ore INdex creation: select * f rom emp where sal <=2000; 10 compar isions 6 salaries
B- Tree Indexes
Af ter INdex creation: select * f rom emp where sal <=2000; 7 compar isions 6 records
B-trees, short f or balanced trees, are the most common type of database index. A B-tree index is an or dered list of values divided into ranges. By associat ing a key wit h a row or range of rows, B -trees provide excellent retrieval perf ormance f or a wide range of queries, including exact match and range searches. Figure 3-1 illustrates the structure of a B -tree index. The example shows an index on the department_id column, which is a f oreign key column in the employees table.
Figure 3-1 Int ernal Structure of a B-tree Index
TYPES OF INDEXES: 2 1) Simple index: It is created on a table on single column. Syntax:
create index < idx_name> on table_name(colname);
2) Composite index It is created on mult iple columns of a table. Syntax:
Ex:
create index < idx_name> on table_name(col1 , col2,....);
create an index on t able emp on the column sal? create index idx_sal on emp(sal);
Ex:
create an index on prod_dt ls on the columns cost,comp_code? create index idx_prod_cost_comp_code on prod_dtls(cost,com p_code);
3) Function Based Index In the index def inition, If we specif y any cal culations or functions on table columns then it is known as Function Based Index. syn:
create index < idx_name> on table_name( col+ 100,col2*0.10,Func( col3));
Ex:
create index idx_sal_ename on emp((0.20*sal),Initcap(ename));
Ex:
select sal,(0. 20*sal), initcap( ename) f rom emp;
4) REVERSE KEY INDEX In this index , the search criter ia is f rom right most leaf to lef t. This index is pref erable to search f or highes t values f requently. Syntax:
create index < idx_name> on table_name(col) REVERSE;
Ex: create a reverse index on sales transact ion table on sales am ount column? Create index idx_higher_sales on sales(sales_amt) REVERSE; 5) UNIQUE INDEX If an index is cr eating on Unique column then it is known as unique index. Ex:
create unique index idx_comm on emp(comm);
6) Bitmap Index Use this index if you have ver y less num ber of diff erent values in a column like gender, emp_job_status Ex:
create bitmap index i x_gender on cust_dtls(gender);
Note By def ault the or acle engine create and maintains a def ault index on each primar y key column of a table. Note In Oracle indexes inf ormation is maintained under a system def ined table called USER_INDEXES HOW TO DELETE AN INDEX? drop index idx_name; Multiple lndexes on same set of columns:
You can create multiple indexes on the same set of columns when the indexes ar e dif f erent in some way. For example, you can cr eat e a B -tree index and a bitmap index on the same set of columns. W hen you have multiple indexes on the same set of columns, only one of these indexes can be visible at a t ime, and any other indexes must be invisible. You might create dif ferent indexes on the same set of columns because they provide the f lexibilit y to meet your requirements. You can also create multiple indexes on t he same set of columns to perf orm application migrations without dr opping an existing index and recreat ing it with dif f erent attributes.
Creating an Invisible Index An invisible index is an index that is ignored by the opt imizer unless you explicit ly set the OPTIMIZER_USE_INVI SIBLE_INDEXES init ialization parameter to TRUE at the session or system level. To create an invisible index:
Use the CREATE INDEX statement with t he INVI SIBLE keyword.
The f ollowing statement creates an invisible index named emp_ename f or the ename column of the emp table: CREATE INDEX emp_ename ON emp( ename) TABLESPACE users STORAGE (INITIAL 20K NEXT 20k) INVISIBLE;
USER_I NDEXES USER_I NDEXES describes the indexes owned by the current user. This view does not display the OW NER column.
CLUSTERS
Creat ing Clusters To create a cluster in your schema, you must have the CRE ATE CLUSTER system privilege and a quota f or the tablespace int ended to contain the cluster or the UNLIMI TED TABLESP ACE system privilege. To create a cluster in another user's schema you must have the CRE ATE ANY CLUSTER syst em privilege, and the owner must have a quota f or t he tablespace intended to contain the cluster or the UNLIMI TED TABLESP ACE system privilege. You create a cluster using the CRE ATE CLUSTER statement. The f ollowing statement creates a cluster named emp_dept, which st ores the emp and dept tables, clust ered b y thedeptno column: CREATE CLUSTER emp_dept (deptno NUMBER(3))
If no INDEX keyword is specif ied, as is tr ue in this example, an index cluster is created by def ault. You can also creat e a H ASH cluster, when hash parameters ( H ASHKEYS, H ASH IS, orSI NGLE TABL E H ASH KEYS) are specif ied Creat ing Clustered Tables To create a table in a clust er, you must have either the CRE ATE TABLE or CRE ATE ANY TABLE system privilege. You do not need a tablespace quota or the UNLIMITED TAB LESP ACE system privilege to create a table in a cluster. You create a table in a cluster using the CRE ATE TABLE stat ement with the CLUSTER clause. The emp and dept tables can be cr eat ed in the emp_dept cluster using the f ollowing statements: CREATE TABLE em p ( empno NUMBER( 5) PRI MARY KEY,
ename VARCHAR2(15) NOT NULL, . . . deptno NUMBER(3) REFERENCES dept) CLUSTER emp_dept (deptno);
CREATE TABLE dept ( deptno NUMBER(3) PRI MARY KEY, . . . ) CLUSTER emp_dept (deptno);
Cust_dt ls ( cust id,cname,cit y) Act_t ypes ( act_t ype,act_name,desc) Cust_act_dt ls(actno,act_t ype,act_open_dt,act_bal,cust id)
Example:
create cluster emp_dept (deptno number( 2));
create table deptcp ( deptno number(2) primar y key, dname varchar2(20) , loc varchar2(20) ) cluster emp_dept(deptn o);
create table empcp ( eid number(4) pr imary key, ename varchar2(20), sal number(5), deptno number(2) ref erences deptcp( deptno) ) cluster emp_dept(deptno);
ALL_CLUSTERS ALL_ CLU STE RS descri bes all clus ters acces sible to the curren t u ser.
Related Views
DBA_ CLU STE RS d esc ribes a ll clu sters in th e d ataba se. USER _CL UST ERS describes all c luste rs o wned b y the curren t use r. Th is view does no t d isp la y the OW NER c olu mn.
Column
Datatype
NULL
OWNE R
VARC HAR 2(3 0)
NOT NUL L
CLUS TER _NA ME
VARC HAR 2(3 0)
NOT NUL L
TABL ESP ACE _NA ME
VARC HAR 2(3 0)
NOT NUL L
SEQUENCES Use the CREATE SEQUENCE statement to create a sequence , which is a database object f rom which mult iple user s may generat e unique int egers. You can use sequences to automat ically generate primar y key values. W hen a sequence number is generated, the sequence is incr emented, independent of the transact ion committing or rolling back. If two user s concurrently incr ement the same sequence, then the sequence numbers each user acquires may have gaps, because sequence numbers are being generated by the other user. One user can never acquire the sequence number generated by another user. Af t er a sequence value is generated by one user, that user can cont inue to access that value regardless of whether the sequence is incr emented by another user. Sequence numbers are generated independent ly of tables, so the same sequence can be used f or one or f or multiple tables. It is possible that individual sequence numbers will appear to be skipped, because they were generated and used in a transaction that ult imately r olled back. Additionally, a single user may not realize that other users are drawing fro m the same sequence. Af ter a sequence is created, you can access its values in SQ L statements with the CURRVAL pseudocolumn, which returns the current value of the sequence, or the NEXTVAL pseudocolum n, which increments the sequence and returns the new val ue.
It is a data base obj ect which is used to generate sequent ial integers with the specif ied inter val value. Generally the sequences are used to generate primar y key values.
The syntax to create a sequence in Oracle is: CREATE SEQUENCE sequence_name MINVALUE value MAXVALUE value START WITH value
INCREMENT BY value CACHE value;
Note: by def ault the sequence starts with 1 and increment value is also 1. ** Sequence is an independent object.( It is not depending on any table ) Ex:
create sequence srno1;
Pseudo columns: <seq_name>.CURRVAL Display current value of the sequence <seq_name>.NEXTVAL Display next value of sequence Ex:
select srno1.currval f rom dual;
CURRVAL ---------1
Ex:
select srno1.next val f rom dual;
NEXTVAL ---------2 Ex:
create a sequence st arts with 1001? create sequence custno start with 1001;
Ex: update the cust_dtls table with sequence values?
under that update the cust _srno column
update cust_dt ls set cust_srno=sr no1.nextval; Ex:
adding a primar y key constraint on the column cust_srno?
alter table cust_dt ls add constraint pk_custsrno primar y key(cust_sr no); Ex:
Insert new record in the table cust_dtls along with sequence values?
insert into cust_dt ls values(4,'abc','hyd',6767676765,'F',null,srno1.next val); ************** Hands-on: CREATE TABLE CUSTOMERS ( ID INT, NAME VARCHAR2( 20), SRNO NUMBER) CREATE SEQUENCE ID START W ITH 6600 INCREMENT BY 5 CREATE SEQUENCE SRNO INSERT INTO CUSTOMERS VALUES(I D.NEXTVAL,'AJAY', NULL) INSERT INTO CUSTOMERS VALUES(I D.NEXTVAL,'KI RAN',NULL) INSERT INTO CUSTOMERS VALUES(I D.NEXTVAL,' MADHU',NULL) SELECT * FRO M CUSTOMERS UPDATE CUSTO MERS SET SRNO=SRNO.NEXTVAL results: CREATE TABLE succeeded. CREATE SEQUENCE succeeded. CREATE SEQUENCE succeeded. 1 rows inserted 1 rows inserted 1 rows inserted ID NAME SRNO ---------------------- -------------------- ---------------------6605 AJAY 6610 KIRAN 6615 MADHU 3 rows select ed 3 rows updated ID NAME ---------------------- -------------------6605 AJAY 6610 KIRAN 6615 MADHU 3 rows select ed Ex: CREATE SEQUENCE t1_seq;
SRNO ---------------------1 2 3
CREATE TABLE t1 ( id NUMBER DEFAULT t1_seq.NEXTVAL, descript ion VARCHAR2( 30) ); INSERT INTO t1 (descript ion) VALUES ('DESCRIPTION only') ; INSERT INTO t1 (id, description) VALUES (999, 'ID=999 and DESCRIPTION'); INSERT INTO t1 (id, description) VALUES (NULL, 'ID=NULL and DESCRIPTION'); SELECT * FRO M t1; ID DESCRIPTI ON ---------- -----------------------------1 DESCRIPTION only 999 ID=999 and DESCRI PTION ID=NULL and DESCRI PTION 3 rows select ed.
DEFAULT Values Using Sequences In Oracle 12c, it is now possible to specif y the CURRVAL and NEXTVAL sequence pseudocolumns as the def ault values f or a column. You should also consider using Ident it y columns f or this purpose . In the f ollowing example you can see the eff ect of specif ying a sequence as the def ault value f or a column. The def ault value is only used when the column is not ref erenced by the insert. This behaviour can be modif ied using the ON NULL clause described in the next section.
CREATE SEQUENCE t1_seq; CREATE TABLE t1 ( id NUMBER DEFAULT t1_seq.NEXTVAL, description VARCHAR2(30) ); INSERT INTO t1 (description) VALUES ('DESCRIPTION only'); INSERT INTO t1 (id, description) VALUES (999, 'ID=999 and DESCRIPTION'); INSERT INTO t1 (id, description) VALUES (NULL, 'ID=NULL and DESCRIPTION'); SELECT * FROM t1;
ID ---------1 999
DESCRIPTION -----------------------------DESCRIPTION only ID=999 and DESCRIPTION ID=NULL and DESCRIPTION
3 rows selected. SQL>
IDENTITY COLUMNS IN ORACLE 12c
CREATE TABLE identity_test_tab ( id
NUMBER GENERATED ALWAYS AS IDENTITY,
description VARCHAR2(30) );
SQL> INSERT INTO identity_test_tab (description) VALUES ('Just DESCRIPTION');
1 row created.
SQL> INSERT INTO identity_test_tab (id, description) VALUES (NULL, 'ID=NULL and DESCRIPTION'); INSERT INTO identity_test_tab (id, description) VALUES (NULL, 'ID=NULL and DESCRIPTION') * ERROR at line 1: ORA-32795: cannot insert into a generated always identity column
SQL> INSERT INTO identity_test_tab (id, description) VALUES (999, 'ID=999 and DESCRIPTION'); INSERT INTO identity_test_tab (id, description) VALUES (999, 'ID=999 and DESCRIPTION') *
ERROR at line 1: ORA-32795: cannot insert into a generated always identity column
SQL>
Using BY DEFAULT allows you to use the identity if the column isn't referenced in the insert statement, but if the column is referenced, the specified value will be used in place of the identity. Attempting to specif y the value NULL in this case results in an error, since identity columns are always NOT NULL . DROP TABLE identity_test_tab PURGE;
CREATE TABLE identity_test_tab ( id
NUMBER GENERATED BY DEFAULT AS IDENTITY,
description VARCHAR2(30) );
SQL> INSERT INTO identity_test_tab (description) VALUES ('Just DESCRIPTION');
1 row created.
SQL> INSERT INTO identity_test_tab (id, description) VALUES (999, 'ID=999 and DESCRIPTION');
1 row created.
SQL> INSERT INTO identity_test_tab (id, description) VALUES (NULL, 'ID=NULL and DESCRIPTION'); INSERT INTO identity_test_tab (id, description) VALUES (NULL, 'ID=NULL and DESCRIPTION') * ERROR at line 1:
ORA-01400: cannot insert NULL into ("TEST"."IDENTITY_TEST_TAB"."ID")
SQL>
Using BY DEFAULT ON NULL allows the identity to be used if the identity column is referenced, but a value of NULL is specified. DROP TABLE identity_test_tab PURGE;
CREATE TABLE identity_test_tab ( id IDENTITY,
NUMBER GENERATED BY DEFAULT ON NULL AS
description VARCHAR2(30) );
SQL> INSERT INTO identity_test_tab (description) VALUES ('Just DESCRIPTION');
1 row created.
SQL> INSERT INTO identity_test_tab (id, description) VALUES (999, 'ID=999 and DESCRIPTION');
1 row created.
SQL> INSERT INTO identity_test_tab (id, description) VALUES (NULL, 'ID=NULL and DESCRIPTION');
1 row created.
SQL> SELECT * FROM identity_test_tab;
ID DESCRIPTION
---------- -----------------------------1 Just DESCRIPTION 999 ID=999 and DESCRIPTION 2 ID=NULL and DESCRIPTION
SQL>
HOW TO DELETE THE SEQUENCE? Ex:
drop sequence srno;
Note: System def ined view to get sequences inf ormation is “user _sequences”.
SYNONYMS Use the CREATE SYNONYM statement to create a synonym, which is an alternative name f or a table, view, sequence, oper ator, procedure, stored f unction, package, materialized view, Java class schema object, user def ined object type, or another synonym. A syno nym places a dependency on its target object and becomes invalid if the target object is changed or dropped. Synonyms provide both data independence and location transparency. Synonyms permit applicat ions to f unction without modif icat ion regardless of which user owns the table or view and regardless of which database holds the table or view. However, synonyms ar e not a subst itute f or privileges on database objects. Appropriate pr ivileges must be granted to a user bef ore the user can use the synonym. You can ref er to synonyms in the f ollowi ng DML statements: SELECT , INSERT , UPDATE, DELETE, FLASHBACK TABLE, EXPLAIN PLAN, and LOCK TABLE. You can ref er to synonyms in the f ollowi ng DDL statements: AUDIT, NOAUDIT , GRANT, REVOKE, and CO MMENT .
Synonyms are u sed to create permanent alias names f or the tables. Types of Synonyms : 2 I ) Private synonym It is a def ault synonym. and it is used by the owner only. ii) Public synonym It is created by DBA. And it can be accessed by permitted authent icat ed user.
syn:
create [ public ] synonym <syn_name> FOR ;
Ex:
create synonym cd f or cust_dt ls;
Note: Once a synonym is created then we can use synonym name instead of table name f or any operat ions on the table. Ex:
display cust det ails data? select * f rom cust_dtls; or select * f rom cd;
Ex:
insert into cd values((10,'c','mumbai',null,'M',null,cust no.next val);
Ex:
deleting the dat a f rom table using alias name? delete f rom cd where mobile is null;
Ex:
how to delete a synonym ? drop synonym cd;
USER_SYNONYMS It is a system def ined view, f rom where we can retr ieve the synonyms inf ormation
PARTITIONS Partitioning enables you to decompose very large tables and indexes into smaller and more manageable pieces called partitions. Each partit ion is an independent object with its own name and optionally its own storage character ist ics. For an analogy that illustrates partitioning, suppose an HR m anager has one big box that contains employee f olders. Each f older lis ts the employee hire date. Queries are of ten made f or employees hired in a part icular month. One approach to sat isf ying such requests is to create an index on employee hire date that specif ies t he locations of the folders scattered throughout the box. In contrast, a partit ioning strategy uses m any smaller boxes, with each box containing f olders f or employees hired in a given month. Using smaller boxes has several advantages. W hen asked to retrieve the f olders f or employees hired in June, the HR manager can retrieve the June box. Furthermore, if any small box is tem porarily damaged, the other small boxes remain available. Moving off ices also becomes easier because instead of moving a single heavy box, the manager can move several small boxes. From the perspec tive of an applicat ion, only one schema object exists. DM L statements require no modif ication to access part itioned tables. Partit ioning is usef ul f or many dif f erent types of database applicat ions, particularly those that manage large volumes of data. Benef its include:
Increased availabilit y The unavailabilit y of a partit ion does not entail the unavailabilit y of the object. The query optimizer automatically removes unref erenced partit ions f rom the query plan so queries are not aff ected when the partit ions ar e unavailable.
Easier adm inistration of schema objects A partit ioned object has pieces that can be managed either collect ively or individually. DDL statements can manipulate part itions rather than entire tables or indexes. Thus, you can break up resource - int ensive
tasks such as rebuilding an index or table. For example, you can move one table partit ion at a time. If a probl em occurs, then only the partit ion move must be redone, not the table move. Also, dropping a partit ion avoids executing numerous DELETE statements.
Reduced content ion f or shared resources in OLTP systems In some OLTP systems, partitions can decrease content ion f or a shared resource. For example, DML is distributed over many segments rather than one s egment.
Enhanced query performance in data war ehouses In a data w arehouse , partitioning can speed pr ocessing of ad hoc queries. For example, a sales table cont aining a million rows can be partit ioned by quarter.
Partition Charact eristics Each partit ion of a table or index must have the same logical attributes, such as column names, data t ypes, and const raints. For example, all part itions in a table share the same column and constraint def initions, and all part itions in an index share the same indexed columns. However, each partit ion can have separate physical attributes, such as the tablespace to which it belongs. Partition Ke y The partition key is a set of one or more columns that determ ines the partit ion in which each row in a partit ioned table should go. Each row is unambiguously assigned to a single partit ion. In the sales table, you could specif y the time_id column as the key of a range partition. The database assigns rows to partit ions based on whether the date in this column f alls in a specif ied range. Oracle Database automatically directs insert, update, and delete operations to the appropriate partit ion by using the partit i on key. Partitioning Strategies Oracle Partitioning off ers several partit ioning strategies that control how the database places dat a into partit ions. The basic strat egies are range, list, and hash partitioning. A single-level partit ioning strategy uses onl y one method of data distr ibut ion, f or example, only list partit ioning or only range partit ioning. In composite partitioning , a table is part itioned by one data distr ibut ion method and then each partit ion is f urther divided into
subpart itions using a secon d data distribution method. For example, you could use a list part ition f or channel_id and a range subpartit ion f or time_id.
Range Partitioni ng In range partitioning , the database maps rows to part itions based on ranges of values of the partit ioning key. Range part itioning is the most common type of partitioning and is of ten used with dates. Suppose that you want to populate a partitioned table with the sales rows shown in Example 4- 1. Example 4-1 Sample Row Set for Partitioned Table PROD_ID CUST_ID TIME_ID CHANNEL_ID PRO MO _I D QUANTITY_SOLD AMO UNT_SOLD ---------- ---------- --------- ---------- ---------- ------------- ----------116 11393 05-JUN- 99 2 999 1 12.18 40 100530 30 -NOV-98 9 33 1 44.99 118 133 06 -JUN-01 2 999 1 17.12 133 9450 01 -DEC- 00 2 999 1 31.28 36 4523 27 -JAN-99 3 999 1 53.89 125 9417 04 -FEB-98 3 999 1 16.86 30 170 23 -FEB-01 2 999 1 8.8 24 11899 26 -JUN-99 4 999 1 43.04 35 2606 17 -FEB-00 3 999 1 54.94 45 9491 28 -AUG-98 4 350 1 47.45 You create time_r ange_sales as a part itioned table using the statement in Example 4-2. The time_id column is the partit ion key. Example 4-2 Range- Partitioned Table CREATE TABLE tim e_range_sales ( prod_id NUMBER(6) , cust_id NUMBER , time_id DATE , channel_id CHAR(1) , promo_id NUMBER(6) , quantit y_sold NUMBER(3) , amount_sold NUMBER(10,2) )
PARTITION BY RANGE ( time_id) (PARTITION SALES_1998 VALUES 1999','DD- MO N-YYYY')), PARTITION SALES_1999 VALUES 2000','DD- MO N-YYYY')), PARTITION SALES_2000 VALUES 2001','DD- MO N-YYYY')), PARTITION SALES_2001 VALUES );
LESS THAN (TO_DATE(' 01 -JANLESS THAN (TO_DATE(' 01 -JANLESS THAN (TO_DATE(' 01 -JANLESS THAN ( MAXVALUE)
Af terward, you load t ime_range_sales wit h the rows f rom Example 41. Figure 4-1 shows the row distributions in the f our partitions. The database chooses the part ition f or each row b ased on the time_id value according to the rules specif ied in the PARTITION BY RANGE clause. Figure 4-1 Range Partitions
Descr ipt ion of "Figure 4 -1 Range Partit ions" The range partition key value determines the high value of the range partit ions, which is called the transition point . In Figure 4-1, the SALES_1998 partition contains rows with partit ioning key time_id values less than the transit ion point 01-JAN- 1999. The database creates interval partitions f or data beyond that transition point. Interval partit ions extend range partitioning by instruc t ing the database to create partitions of the specif ied range or inter val automatically when data inserted into the table exceeds all of the range partitions. In Figure 4-1, theSALES_2001 part ition contains rows with partitioning key time_id values greater than or equal to 01-JAN-2001. List Partitioning
In list partitioning, the database uses a list of discrete values as the partit ion key f or each partit ion. You can use list part itioning to control how individual rows map to specif ic partit ions. By using lists, you can group and organize related set s of data when the key used to identif y t hem is not conveniently ordered. Assume that you create list_sales as a list-partitioned table using the statement in Example 4-3. The channel_id column is the partition key. Example 4-3 List-Partitioned Table CREATE TABLE list _sales ( prod_id NUMBER(6) , cust_id NUMBER , time_id DATE , channel_id CHAR(1) , promo_id NUMBER(6) , quantit y_sold NUMBER(3) , amount_sold NUMBER(10,2) ) PARTITION BY LIST ( channel_id) (PARTITION even_channels VALUES (2, 4), PARTITION odd_channels VALUES (3,9) ); Af terward, you load t he table with the rows f rom Example 4-1. Figure 42 shows the row dist ribut ion in the t wo partitions. The database chooses the partit ion f or each row based on the channel_id value according to the rules specif ied i n the PARTITION BY LIST clause. Rows with a channel_id value of 2 or 4 are stored in t he EVEN_CHANNELS partit ions, while rows with a channel_id value of 3 or 9 are stored in the ODD_CHANNELS partit ion. Figure 4-2 List Part itions
Descr ipt ion of "Figure 4 -2 List Partit ions" Hash Partitioning In hash partitioning , the database maps rows to part itions based on a hashi ng algorithm that the database applies to the user -specif ied partit ioning key. The destination of a row is determ ined by the internal hash function applied to t he row by the database. The hashing algorithm is designed to evenly distributes rows across devices so that each partit ion contains about the same number o f rows. Hash partit ioning is usef ul f or dividing large tables to increase manageabilit y. Instead of one large table to manage, you have several smaller pieces. The loss of a single hash partit ion does not affect the remaining partit ions and can be recovere d independently. Hash partit ioning is also usef ul in OLTP systems with high update content ion. For example, a segment is divided into several pieces, each of w hich is updated, instead of a single segment that experiences contention. Assume that you create the partit ioned hash_sales table using the statement in Examp le 4-4. The prod_id column is the partit ion key. Example 4-4 Hash- Partitioned Table CREATE TABLE hash_sales ( prod_id NUMBER(6) , cust_id NUMBER , time_id DATE , channel_id CHAR(1)
, promo_id NUMBER(6) , quantit y_sold NUMBER(3) , amount_sold NUMBER(10,2) ) PARTITION BY HASH ( prod_id) PARTITIONS 2; Af terward, you load t he table with the rows f rom Example 4-1. Figure 43 shows a possible row distribution in the two partit ions. Note that the names of these partitions ar e system -generated. As you insert r ows, the database attempt s to randomly and evenly distribute them across part itions. You cannot specif y the partit ion into which a row is placed. The database applies the hash f unction, whose outcome determines which part ition contains the row. If you chan ge the number of partitions, then the database redistr ibutes the data over all of the partitions. Figure 4-3 Hash Partitions
SQL FUNCTIONS Oracle provides set of built-in f unctions to perf orm user required operat ions. These f unctions are divided into 2 categories. C ATEGORES: 2 1) GROUP OR AGGREG ATE FUNCTIONS These f unctions can acts on group of values display single output value. These f unctions act on column level / f ield level. Ex:
sum(), avg()...
2) SC AL AR /SINGLE ROW FUNCTIONS These f unctions acts on group of values and display a set of output values. These f unctions act on Record level / row level. Ex:
lower(), length(), trim()
The above 2 categories of f unctions are f urther divided into f ollowing 4 types. TYPES OF FUNCTIO NS: 4 i) Numeri c functions Acts on numeric dat a ii) String functions Acts on char data iii) Date functions Acts on date data iv) Conversion functions It acts on one data type and display result in ot her data t ype.
NUMERIC FUNCTIONS [ GROUP FUNCTIONS] These f unctions act on column dat a only. i) SUM(colname) It will display addit ion of values f rom the column Ex:
display addit ion of all salar ies? select sum(sal) f rom emp;
output: SUM(SAL) ---------29025 Ex:
Find the sum of salaries f or managers?
Ex: Find the sum of salaries of emps working under RESEARCH department? Ex:
Find the total invest ment amount f or the products f rom sony?
Ex:
Find the total balance f rom the account t ypes SB and SAL?
Output: SQL> select sum(act _bal) 2 f rom cust_act_dt ls 3 where act_t ype in('SB','SAL'); SUM(ACT_BAL) -----------151000
ii) AVG(col name) It will display averag e value f rom the column Ex:
display average product cost? select avg(cost) AvgCostOf Product f rom prod_dtls;
output: SQL> select avg(cost) AvgCostOf Product 2 f rom prod_dtls; AVGCOSTOFPRODUCT ---------------31241.6667 Ex:
Find the average sales amount in the last year?
select avg(sales_am ount) f ro m sales where t id like'%14'; iii) M AX(colname) Display higher value from the column. Ex:
Display highest salar y among all salesman?
SQL> select max(sal ) f rom emp where job='SALESMAN'; output: MAX(SAL) ---------1600 Ex:
display employee details who is getting higher salar y? select * f rom emp where sal=max( sal); -----W RONG Or select * f rom emp where sal I N(select max(sal) f rom emp);
iv) MIN(colname) Display lower value f rom the column Ex:
display least cost pr oduct details among a ll mobiles?
SQL> select * f rom prod_dt ls where cost I N(select min(cost) f rom prod_dt ls where pr od_name=' MO BILE'); PROD_CODE PRO D_NAME COST MFG W ARRENTY CO MP_CODE ---------- -------------------- ---------- --------- ---------- ---------SMBLGLXG MOBILE 21500 02 -FEB-13 1 YEAR S404 v) COUNT( colname) Display number of values f rom the colum n except null values Ex:
f ind how many emps getting commission? select count(empno) f rom emp where com m is not nu ll; OR select count(comm) from emp;
COUNT(CO MM) ----------4 vi) CO UNT(*) Display number of records f rom a table. Ex:
display number of transact ions on current day?
select count(*) f rom trans_dt ls where trans_dat e=sysdate; Note: " Aggregate functions executes on fiel d /column level dat a " Ex:
Display number of emps under deptno 10? select count(*) f rom emp where deptno=10; 10
Ex:
5
Display number of emps under deptno 20? select count(*) f rom emp where deptno=20; 20
7
Expect ed output: 10 20 30 40 50
5 7 5 2 1
GROUP BY Clause: It is used to group related data by considering distinct values f rom the column. On each group the oracle engine executes the aggregate f u nction and display result individually. Syntax:
Ex:
select colname, colname,..., aggf unction1, aggregate2,..... f rom table where GROUP BY ,,... HAVI NG aggf unc1,... ORDER BY cl1,col2,.....;
f ind out number of emps working under each dept ? select deptno, count (*) " No. of emps" f rom emp GROUP BY deptno;
output: deptno --------30 20
No. of emps ------------6 7
10 Ex:
5
f ind out number of emps working under each dept on order of deptno? select deptno, count (*) " No. of emps" f rom emp GROUP BY deptno order by deptno;
output: 20 30
10 7 6
5
Ex: Find out number of customers f or each account t ype? SQL> select act_t ype,count(cno) " No of customers" 2 f rom cust_act_dt ls 3 group by act_t ype; ACT_T No of customers ----- ---------------SB 3 SAL 3 DEMAT 1 Ex: Find number of emps under each job cat egory f or each dept number? SQL> select deptno,job,count(empno) "No of emps" 2 f rom emp 3 group by deptno, job; DEPTNO JOB No of emps ---------- --------- ---------20 CLERK 2 30 SALESMAN 4 20 MANAGER 1 30 CLERK 1 10 PRESIDENT 1 30 MANAGER 1 10 CLERK 1 10 MANAGER 1 20 ANALYST 2 9 rows select ed. SQL> Ex:
f indout max sal under each deptno?
Ex:
f indout min sal f or each job categor y based on the order of job?
Ex: f indout number of emps under each dept getting morethan 5000 salar y based on the order of deptno? select deptno, count(*) f rom emp where sal> 5000 group by deptno order by deptno; Ex:
f ind out number of customers f rom each cit y based on order of city?
Ex:
f ind out number of customers f or each account t ype?
Ex
f ind out number of products f rom each product categor y?
HAVING clause: It is used to specif y conditions on group by output. Ex: f ind out number of emps working under each dept on order of deptno if a dept contains at least 10 emps? select deptno, count (*) " No. of emps" f rom emp GROUP BY deptno HAVI NG count(*)>=10 order by deptno; output: 30
20 60
70
Examples: select deptno,count(empno) EmpCount f rom emp group by deptno; select deptno,count(empno) EmpCount f rom emp group by deptno Having count(empno)>=5 ; select job,count(empno) EmpCount f rom emp group by job; --Having count(emEx: Display number of emps under deptno 10? select count(*) f rom emp where deptno=10;pno)>=5 ; select job,count(empno) EmpCount f rom emp
group by job Having count(empno)<=3 ; select d.dname, avg(e.sal) "Average Salary" f rom dept d, emp e where d. deptno=e.deptno group by d.dname; OUTPUT: DEPTNO EMPCO UNT ---------- ---------30 6 20 5 10 2 DEPTNO EMPCOUNT ---------- ---------30 6 20 5 10 2 DEPTNO EMPCOUNT ---------- ---------30 6 20 5 JOB EMPCOUNT --------- ---------CLERK 4 SALESMAN 4 PRESIDENT 1 MANAGER 2 ANALYST 2 JOB EMPCOUNT --------- ---------PRESIDENT 1 MANAGER 2 ANALYST 2 DNAME Average Salar y -------------- -------------ACCOUNTING 3150 RESEARCH 2175 SALES 1925
NUMERIC FUNCTIONS (SCAL AR FUNCTIONS) These f unctions are acting on record level. 1) ABS( n) [ ABSOLUTE ] Display absolute value of n. Ex:
select abs( -9) f rom dual; 9
Ex:
select abs(13. 23) f rom dual; 13.23
Ex:
select abs(round((m onths_bet ween(hiredate,sysdat e)/12))) f rom emp; 2)
mod(m,n) Display remainder value af ter m devides n.
Ex:
select mod(25,5) f rom dual; 0
Ex:
select mod(17,3) f rom dual; 2 3) pow er(m,n) Display m power nth value
Ex:
select power(5,3) f rom dual; 125 4)
Ex:
SQRT(n) [ square root ] Display square root value of n select sqrt(64) f rom dual; 8
5) ROUND(m,n) Display value “m ” which is rounded to the “n” number of decimal places. Bef ore displaying “n th” Decimal digit it will check " n+1 th” decimal digit, if it is > or = 5 then “nth ” digit incr emented by 1. Ex:
select round( 63.354,2) from dual; 63.35
Ex:
select round( 63.354,1) f rom dual; 63.4
Ex:
select round( 63.354) from dual; 63
Ex:
select round( 69.554) from dual; 70
6) TRUNC(m,n) Display value m which is truncated to the n number of decimal places. Ex:
select trunc(63.354, 1) from dual; 63.3
Ex:
select trunc(69.554) f rom dual; 69
7) FLOOR(n) Display highest int eger value which is lessthan or equal to given value. Ex:
select f loor(64.2) f rom dual; {0,1,2,......,61,62,63,64}= 64
8) CEIL(n) Display lowest int eger value which is greater than or equal to given value. Ex:
select ceil(64. 2) f rom dual; {65,66,67,,,........}=65 9) LE AST( val/expr, val/expr,....) Display minimum value f rom the given values or expr ession results.
Ex:
select least( 32,(6*5), (20 -10), (36/2)) f rom dual; 10 10)
Ex:
GRE ATEST ( val/expr, val/expr,.....) Display maximum value f rom the given values or expr essions select greatest (32,(6*5), (20 -10), (36/2)) f rom dual; 32
STRING FUNCTIONS (scalar functions) 1) ASCII('ch') display ascii value of the character Ex:
select ascii('a') f rom dual; 97
Ex:
select ascii('A') f rom dual; 65
Ex:
select ascii('@') f rom dual; 64 2) LENG TH('str'/col) Display number of chars f rom the given string or column values
Ex: Get all employee names and also each name length? select ename, length(ename) " length of name" f rom emp; ENAME length of name ---------- ---------------SMITH 5 ALLEN 5 W ARD 4 JONES 5 MARTIN 6 BLAKE 5 CLARK 5 SCOTT 5 KING 4 TURNER 6 ADAMS 5 ENAME length of name ---------- ---------------JAMES 5 FORD 4 MILLER 6 Ex:
select length('oracle' ) f rom dual; 6 3) LOWER('str'/col) Display the given string chars or column values in lower case.
Ex: Get all employee names in lower case? select lower(ename) f rom emp;
ENAME LOW ER(ENAM ---------- ---------SMITH smith ALLEN allen W ARD ward JONES jones MARTIN martin BLAKE blake CLARK clark SCOTT scott KING king TURNER turner ADAMS adams ENAME LOW ER(ENAM ---------- ---------JAMES james FORD f ord MILLER miller 14 rows selected. SQL> Ex:
select lower('HAI') from dual; hai 4) UPPER( 'str'/col) Display given string chars or column values in upper case
Ex:
select upper (pnam e) from products;
5) INITC AP('str'/col) [ initial capital ] Display the given string or column values with begining char as capital. Ex:
select initcap(' welcome to oracle') f rom dual;
W elcome To Oracle Ex: Get employee names with beginnin g char acter as capital? SQL> select initcap( ename) f rom emp; INITCAP(EN ---------Smith Allen W ard Jones Martin Blake
Clark Scott King Turner Adams INITCAP(EN ---------James Ford Miller 14 rows selected. 6) SUBSTR('str'/col,m,n) (substring) Display a substring from the given string. Here the substring started with "m" th char and through "n" number of chars. Ex:
select substr('secur e',3,4) f rom dual; cure
Ex:
select substr(' welcome to oracle f unctions',12,6) f rom dual; oracle
Ex:
select substr(' welcome to oracle f unctions',12) f rom dual; oracle f unctions 7) INSTR('str'/col,'ch', m,n) [ instring] Display the posit ion of char in the given string or col. Here "m" value is eit her +1( def ault ), or -1 +1 Means search the character posit ion f rom the begining of string. -1 Means search the character posit ion f rom the end of string. Here "n" is nth occurance of give charact er.
ex:
select instr(' welcome','e') f rom dual; 2 or
ex:
select instr(' welcome','e',1,1) f rom dual; 2
Ex:
display second occur ance of 'e' f rom the begining of string? select instr(' welcome','e',1,2) f rom dual; 7
Ex:
Find the length of username in a mail id? select email, instr(email,'@') - 1 " Lengt h of Mail"
f rom emp;
Ex:
select instr('dineshp. [email protected]','@') -1 from dual;
output:- 11 select instr(' welcome','e') f rom dual; O/P: 2 select instr(' welcome','e',+1) f rom dual; O/P: 2 select instr(' welcome','e', -1,1) f rom dual; O/P: 7 select instr(' welcome','e', -1,2) f rom dual; O/P: 2 8) TR ANSL ATE('str'/ col, 'sourcechars','t argetchars') It will display given string chars by tr anslating source chars with corresponding target chars. Ex:
select translate(' welcome','em','xy') f rom dual; wxlcoyx
Ex:
select translate(' welcome','em','x') f rom dual; wxlcox 9) REPL ACE('str'/col, 'source string','target string') Display given string by replacing source string with target string.
Ex:
select replace(' welcome','come','sys') f rom dual; welsys
Ex:
select repl ace(' welcome','come','X') from dual; welX
10)
TRIM('str'/col) Display given string by eleminating blank spaces bef ore and af ter the
string. Ex: select trim(' f rom dual;
welcome to
') " trim" || initcap('oracle')
:welcome to Oracle 11) only.
LTRIM('str'/col) [ left trim ] Display given string by removing blank spaces f rom the lef t of string
Ex: select ltrim(' dual;
welcome to
:welcome to 12)
'), initcap(' oracle') f rom
Oracle
RTRIM('str'/col) [right trim ] Display given string by removing blank spaces f rom right of string
only. Ex: select rtrim(' dual; :
welcome to
'), initcap(' oracle') f rom
welc ome to Oracle
TRIM WITH KEYWO RDS 13)
LE ADING 'ch' FRO M 'str'/col Display given string by removing similar occurrnaces of specif ic char f rom lef t of string Ex:
select trim(leading 'x' f rom 'xxxcxaxdxxxx') f rom dual; cxaxdxxxx 14)
TR AI LING 'ch' FRO M 'str'/col Display given string by removing similar occurances of specific char f rom right of string Ex:
select trim(trailing 'x' f rom 'xxxcaxdxxxx' ) f rom dual; xxxcaxd 15)
BO TH 'ch' FROM 'str'/col Display given string by eleminating sim ilar occurances o f specif ic char f rom both sides of string. Ex:
select trim(both ' x' from 'xxxcaxdxxxx') f rom dual; caxd 16)
LP AD('str'/col,n,'ch') [ left padding ] Display given string along with the specif ic char in the lef t of the
string. Ex:
select lpad('page 1', 12, '*') from dual; ******page 1 17)
Ex:
RP AD('str'/ col, n,'ch') [ right padding] Display given string along with specif ic char in the right of string. select rpad('page 1' ,12,'*') f rom dual;
page 1****** 18)
CONC AT(str1, str2)
CONCAT returns char1 concatenated wit h char2. Both char1 and char2 can be any of the datat ypes CHAR, VARCHAR2, NCHAR, NVARCHAR2, CLOB, or NCLOB. The string returned is in the same character set as char1. Its datat ype depends on the datat ypes of the arguments. In concatenations of two dif f erent datatypes, Oracle Database returns the datat ype that results in a lossless conversion. Theref ore, if one of the arguments is a LOB, then the returned value is a LOB. If one of the arguments is a nat ional datat ype, then the returned value is a national datat ype. For example:
CONCAT (CLOB, NCLOB) returns NCLOB CONCAT (NCLOB, NCHAR) returns NCLOB CONCAT (NCLOB, CHAR) returns NCLO B CONCAT (NCHAR, CLOB) returns NCLOB
This f unction is equivalent to the concatenation operator ( ||).
Ex: SELE CT CON CAT (C ON CAT( las t_n ame , '' 's j ob cat ego ry i s ') , j ob_ id) " Jo b" F ROM em plo ye es W HER E e mpl oy ee _id = 1 52;
Job ---- --- --- --- -- -- ---- --- --- --- -- -- ---- --- --- --- -- -- --Hall 's job ca te go ry i s S A_R EP
Ex: Get the below out put ? SMITH working as CLERK with salar y 1200 CLARK working as MANAG ER with salar y 3400 SQL> select ename||' working as '||job||' with salar y '||sal 2 f rom emp; ENAME||'W ORKINGAS' ||JOB||'W ITHSALARY' ||SAL --------------------------------------------------------------------- --------------SMITH working as CLERK with salar y 800
ALLEN working as SALESMAN wit h salar y 1600 W ARD working as SALESMAN wit h salar y 1250 JONES working as MANAG ER with salar y 2975 MARTIN working as SALESMAN with salary 1250 BLAKE working as MANAG ER with salar y 2850 CLARK working as MANAG ER with salar y 2450 SCOTT working as ANALYST with salar y 3000 KING working as PRESIDENT with salar y 5000 TURNER working as SALESMAN with salary 1500 ADAMS working as CLERK wit h salar y 1100 ENAME||'W ORKINGAS' ||JOB||'W ITHSA LARY' ||SAL -----------------------------------------------------------------------------------JAMES working as CLERK with salar y 950 FORD working as ANALYST with salar y 3000 MILLER working as CLERK wit h salar y 1300 14 rows selected. SQL>
DATE FUNCTIONS 1) TO_D ATE('char fmt of date',dat e) It will display any non -Oracle date f ormat value in oracle's dat e f ormat. It accepts any char format of date(dd/mm/yy or dd -mm-yyyy or dd:mon: yyyy or yyyy-mm-dd) and converts it into oracle's def ault date f ormat. Ex:
select to_date('22/03/2015','dd/mm/yyyy' ) f rom dual; 22-mar-15
Ex:
select to_date('2015:03:12',' yyyy:mm:dd' ) f rom dual; 12-mar-15 2) ADD_MONTHS(d,n)
Display a dat e value af ter adding " n " number of months to the specif ied date Ex:
select add_months( sysdate,6) f rom dual;
Ex:
select add_months( mfg,24) " exp " from products; pid ----
pname ------------
cost ----
mfg
exp
update the expdt f or all products like 24 months f rom the date of mfg? update prod_dtls set exp=add_months(mf g,24); 3) MONTHS_BETWEEN(d1,d2) it shows number of months bet ween dat es. Ex:
select months_bet ween(sysdate,'21 -may- 13') f rom dual;
Ex:
select months_bet ween('01 -jan- 13','01-j an-14') f rom dual; -12 4) L AST_D AY( d) Display the date value of last day in the month.
Ex:
select last_day('06 -jul-10') f rom dual; 31-jul-10
5) NEXT_D AY(d,'w eekdayname') it will display the dat e value of given weekdayname af ter the specif ied date. Ex:
select next_day(sysdate,'saturday') f rom dual;
Ex:
select next_day(sysdate,'monday') f rom dual;
6) EXTR ACT(D ATE P ART, D ATE_V ALU E) The Oracle/PLSQL EXTRACT f unction ext racts a value f rom a date or inter val value.
Syntax The synt ax f or the EXTRACT f unction in Oracle/PLSQL is:
EXTRACT ( { YEAR | MONTH | DAY | HOUR | MINUTE | SECOND } | { TIMEZONE_HOUR | TIMEZONE_MINUTE } | { TIMEZONE_REGION | TIMEZONE_ABBR } FROM { date_value | interval_value } )
Note
You can only extract YEAR, MONTH, and DAY f rom a DATE. You can only extract TIMEZO NE_HOUR and TI MEZO NE_MINUTE f rom a timestamp with a t ime zone dat atype.
Applies To The EXTRACT function can be used in the following versions of Oracle/PLSQL:
Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i
SQL> select extract( year f rom sysdate) f rom dual; EXTRACT(YEARFROMSYSDATE) -----------------------2017
7) TRUNC Function (with dates) Description The Oracle/PLSQL TRUNC f unction retur ns a date truncated t o a specif ic unit of measure.
Syntax (w ith dates) The synt ax f or the TRUNC f unction in Oracle/PLSQL is: TRUNC ( date [, f ormat ] )
Parameters or Arguments date The date to truncate. format Optional. The unit of measure to apply f or truncating. If the format parameter is omitted, the TRUNC f unction will truncate the date to the day value, so that any hours, minutes, or seconds will be truncated of f . It can be one of the f ollowing values: Unit
Valid f ormat parameters
Year
SYYYY, YYYY, YEAR, SYEAR, YYY, YY, Y
ISO Year
IYYY, IY, I
Quarter
Q
Month
MO NTH, MON, MM, RM
W eek
WW
IW
IW
W
W
Day
DDD, DD, J
Start day of the week
DAY, DY, D
Hour
HH, HH12, HH24
Minute
MI
Applies To The TRUNC f unction can be used in the f ollowing versions of Oracle/PLSQL:
Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i
Example - w ith dates Let's look at some Oracle TRUNC f unction examples and explore how to use the TRUNC f unction in Oracle/PLSQL. For example: TRUNC(TO_DATE('22 -AUG-03'), 'YEAR') Result: '01-JAN-03'
TRUNC(TO_DATE('22 -AUG-03'), 'Q') Result: '01-JUL-03'
TRUNC(TO_DATE('22 -AUG-03'), ' MONTH') Result: '01-AUG-03'
TRUNC(TO_DATE('22 -AUG-03'), 'DDD') Result: '22-AUG-03'
TRUNC(TO_DATE('22 -AUG-03'), 'DAY') Result: '17-AUG-03'
8) CURRENT_DATE function Description The Oracle/PLSQL CURRENT_DATE f unction returns the curr ent date in the time zone of the current SQL session as set by the ALTER SESSION command. Syntax The synt ax f or the CURRENT_DATE f unction in Oracle/PLSQ L is: CURRENT_DATE Parameters or Arguments There are no par ameters or arguments f or the CURRENT_DAT E f unction.
Applies To The CURRENT_DATE f unction can be used in the f ollowing versions of Oracle/PLSQL: Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i Example The CURRENT_DATE f unction can be used in Oracle/ PLSQL. If the f ollowing ALTER SESSION command was issued: ALTER SESSION SET TIME_ZONE = ' -7:0'; And then the f ollowing SQL statement was executed: select CURRENT_DATE f rom dual; You might get the f ollowing result: 9/10/2005 10:58:24 PM You then modif ied the session t ime zone with the f ollowing ALTER SESSI ON command: ALTER SESSION SET TIME_ZONE = ' -2:0'; And then the f ollowing SQL statement was executed: select CURRENT_DATE f rom dual; You would now get the f ollowing result: 9/11/2005 3:58: 24 AM The session time zone value has changed f rom -7:0 to -2:0, causing the CURRENT_DATE f unction to return the current date as a value 5 hours ahead.
9) CURRENT_TIMESTAMP The Oracle/PLSQL CURRENT_TIMESTAMP f unction returns t he current date and time in the t ime zone of the current SQL session as set by the ALTER SESSION command. It returns a TIMESTAMP W ITH TIME ZO NE value. Syntax The synt ax f or the CURRENT_TIMESTAMP f unction in Oracle/PLSQL is: CURRENT_TI MESTAMP Parameters or Arguments There are no par ameters or arguments f or the CURRENT_TI MESTAMP f unction. Note A sim ilar f unction to the CURRENT_TI MESTAMP f unction is the LOCALTIMESTAMP f unction .
The diff erence bet ween these t wo f unctions is that the CURRENT_TI MESTAMP f unction returns a TIMESTAMP W ITH TIME ZONE value while the LOCALTI MESTAMP f unction returns a TIMESTAMP value. Applies To The CURRENT_TIMESTAMP f unction can be used in the f ollowing ver sions of Oracle/PLSQL: Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i Example The CURRENT_TIMESTAMP f unction can be used in Oracle/ PLSQL. If the f ollowing ALTER SESSION command was issued: ALTER SESSION SET TIME_ZONE = ' -7:0'; And then the f ollowing SQL statement was executed: select CURRENT_TIMESTAMP f rom dual; You might get the f ollowing result: 10-Sep-05 10.58.24. 853421 PM -07:00 You then modif ied the session t ime zone with the f ollowing ALTER SESSION command: ALTER SESSION SET TIME_ZONE = ' -2:0'; And then the f ollowing SQL statement was executed: select CURRENT_TIMESTAMP f rom dual; You would now get the f ollowing result: 10-Sep-05 03.58.24. 853421 AM -02:00 The session time zone value has changed f rom -7:0 to -2:0, causing the CURRENT_TI MESTAMP f unction to retur n the current dat e and time as a value 5 hours ahead.
10)
LOCALTIMESTAMP
The Oracle/PLSQL LOCALTI MESTAMP f unction returns the current date and time in the time zone of the current SQL session as set by the ALTER SESSION command. It returns a TIMESTAMP value. Syntax The synt ax f or the LOCALTI MESTAMP f unction in Oracle/ PLSQL is: LOCALTI MESTAMP Parameters or Arguments There are no par ameters or arguments f or the LOCALTI MESTAMP f unction.
Note
A sim ilar f unction to the LOCALTIMESTAMP f unction is the CURRENT_TI MESTAMP f unction . The diff erence bet ween these t wo f unctions is that the LOCALTI MESTAMP f unction returns a TIMESTAMP value while the CURRENT_TI MESTAMP f unction returns a TIMESTAMP W ITH TIME ZO NE value. Applies To The LOCALTI MESTAMP f unction can be used in the f ollowing versions of Oracle/PLSQL: Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i Example Let's look at some Oracle LO CALTI MEST AMP f unction examples and explore how to use the LOCALTI MESTAMP f unction in Oracle/PLSQL. For example: If the f ollowing ALTER SESSION command was issued: ALTER SESSION SET TIME_ZONE = ' -7:0'; And then the f ollowing SQL statement was executed: select LOCALTIMESTAMP f rom dual; You might get the f ollowing result: 10-Sep-05 10.58.24 PM You then modif ied the session t ime zone with the f ollowing ALTER SESSION command: ALTER SESSION SET TIME_ZONE = '-2:0'; And then the f ollowing SQL statement was executed: select LOCALTIMESTAMP f rom dual; You would now get the f ollowing result: 10-Sep-05 03.58.24 AM The session time zone value has changed f rom -7:0 to -2:0, causing the LOCALTI MESTAMP f unction to return the current date and tim e as a value 5 hours ahead .
11)
SYSTIMESTAMP
The Oracle/PLSQL SYSTI MESTAMP f unction retur ns the current syst em date and time ( including fractional seconds and time zone) on your local database. Syntax The synt ax f or the SYSTI MESTAMP f unction in Oracle/PLSQL is:
SYSTI MESTAMP Parameters or Arguments There are no par ameters or arguments f or the SYSTI MESTAMP f unction. Applies To The SYSTI MESTAMP f unction can be used in the f ollowing versions of Oracle/PLSQL: Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i Example Let's look at some Oracle SYSTIMESTAMP f unction examples and explore how to use the SYST I MESTAMP f unction in Oracle/PLSQL. For example: SELECT SYSTI MESTAMP INTO v_time FRO M dual; The var iable called v_time will now contain the date and time ( including f ractional seconds and time zone) at the moment the command is executed. The SYSTI MESTAMP f unction might return a value like this: 2015-07-22 10:35:07.417849 -06:00 You can also choose to use the TO_CHAR f unction with the SYSTI MESTAMP f unction. For example: SELECT TO_CHAR( SYSTI MESTAMP, 'SSSS. FF') FRO M dual; The f unction above may return a value such as: 4141.550774 You could also use the SYSTI MESTAMP f unction in any SQL statement. For example: SELECT supplier_id, SYSTI MESTAMP FRO M suppliers W HERE supplier_id > 5000;
CONVERSION FUNCTIONS 1) TO_NUM BER(chardata,[numberdata]) It accepts the chardata which contains a sequence of digits and convert it into number type data. Ex:
salaries -------$1200 $11005 $107069.12
target:
(char type dat a)
f ind the sum of salaries 1) substr(sal, 2) 1200 11005 ( chardata ) 107069.12 2)to_number(substr( sal,2)) converted int o number type data 3) sum(to_number(substr(sal,2)))
Ex:
select sum(to_number(substr(sal,2))) f rom emp; 2) TO_CH AR( num, [char]) it accepts number t ype data and convert it into charact er type data.
Ex: price_list --------100.78 1200.12 5463.00 (char f ormat) 100700.00 1223501.01 Ex:
target_f ormat --------------00,00,100.78 00,01,200.12 (number type data) 00,05,463.00 01,00,700.00 12,23,501.01
select to_char(pr ice_list,'00,00, 000.00') from products;
3) TO_CH AR( date,[char]) It accepts Oracle's date type data and convert it into required char f ormat.
Date Formats: DD digits of month Day Dayname day dayname DAY DAYNAME Mon 3-chars of month wit h begining char capit al MO N " mon " month f ull month name( in lower case) MO NTH f ull month name( caps) Month f ull month name with begining char capital yy last 2 digits of year YY " yyyy complete year number Ex: input sysdate Ex:
output 27 September
select to_char(sysdate,'dd
20 14
month
yyyy') f rom dual;
mont h
yyyy') f rom dual;
SAMPLE EXECUTIONS: SQL> select to_char(sysdate,'dd TO_CHAR( SYSDATE,'DDMO --------------------28 f ebruary 2015 SQL> select to_char(sysdate,'dd
mon
yyyy') f rom dual;
TO_CHAR( SYSDATE --------------28 f eb 2015 SQL> select to_char(sysdate,'dd
MO N
yyyy') f rom dual;
TO_CHAR( SYSDATE --------------28 FEB 2015 SQL> select to_char(sysdate,'dd
Mon
yyyy') f rom dual;
TO_CHAR( SYSDATE --------------28 Feb 2015 SQL> select to_char(sysdate,'dd day Mon TO_CHAR( SYSDATE,'DDDAYMO -----------------------28 saturday Feb 2015
yyyy') f rom dual;
SQL> select to_char(sysdate,'dd Day Mon
yyyy') f rom dual;
TO_CHAR( SYSDATE,'DDDAYMO -----------------------28 Satur day Feb 2015 SQL> select to_char(sysdate,'dd DAY Mon
yyyy') f rom dual;
TO_CHAR( SYSDATE,'DDDAYMO -----------------------28 SATURDAY Feb 2015
4) CAST The Oracle/PLSQL CAST f unction c onvert s one datat ype to another. Syntax The synt ax f or the CAST f unction in Oracle/PLSQL is: CAST ( { expr | ( subquery ) | MULTISET ( subquery ) } AS type_name )
Applies To The CAST f unction can be used in the f ollowing versions of Oracle/ PLSQL: Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i Example Let's look at some Oracle CAST f unction examples and explor e how to use the CAST f unction in Oracle/PLSQL. For example: select CAST( '22 -Aug-2003' AS varchar2(30) ) f rom dual; This would convert the date ( ie: 22-Aug-2003) into a var char 2(30) value.
5) TO_TIMESTAMP
The Oracle/PLSQL TO_TIMESTAMP f unction converts a str ing to a timestamp. Syntax The synt ax f or the TO_TIMESTAMP f unction in Oracle/PLSQL is: TO_TIMESTAMP( str ing1 [, f ormat_mask] ['nlsparam'] ) Parameters or Arguments string1 The string that will be converted to a tim estamp. format_mask Optional. This is the f ormat that will be used to convert string1 to a timestamp. It can be one or a combination of the f ollowing values
Parameter
Explanation
YYYY
4-digit year
MM
Month (01-12; JAN = 01).
MO N
Abbreviat ed name of month.
MO NTH
Name of month, padded with blanks to length of 9 characters.
DD
Day of month (1 -31).
HH
Hour of day (1-12).
HH12
Hour of day (1-12).
HH24
Hour of day (0-23).
MI
Minute (0-59).
SS
Second (0-59).
Applies To The TO_TIMESTAMP f unction can be used in the f ollowing versions of Oracle/PLSQL: Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i Example Let's look at some Oracle TO_TIMESTAMP f unction examples and explore how to use the TO_TI MESTAMP f unction in Oracle/PLSQL. For example: TO_TIMESTAMP('2003/12/13 10:13: 18', 'YYYY/ MM/DD HH: MI :SS') would return '13 -DEC-03 10. 13.18.000000000 AM' as a timest amp value. TO_TIMESTAMP('2003/DEC/13 10:13:18' , 'YYYY/ MON/DD HH: MI:SS') would also return '13 -DEC- 03 10.13.18.000000000 AM' as a timestamp value.
ORACLE MISLLANEOUS CONCEPTS SQL C ASE st atement The CASE statement is like a series of IF statements, only using the key word W HEN. A CASE statement is evaluated f rom top to bottom. If a condition is true, then corresponding THEN clause is executed and execut ion jumps to the END CASE (short circuit evaluation) clause. Oracle SQL allows you to add "Boolean logic" and branching using the decode and CASE clauses. The case st atement is a more f lexible ext ension of the Decode statement. In its simplest f orm the Oracle CASE f unction is used to return a value when a match is f ound: Ex: SELECT last _name, commission_pct, (CASE commission_pct W HEN 0.1 THEN 'Low' W HEN 0.15 THEN 'Aver age' W HEN 0.2 THEN 'High' ELSE 'N/A' END ) Commission FRO M employees ORDER BY last_name; Ex: select case when sal<1000 then ' low' when sal > =1000 and sal <2000 then ' Medium' else 'High' end f rom emp; CO ALESCE function The COALESCE f unction takes t wo or more compatible arguments and returns the f irst argument that is not null. The result is null only if all the argument s are null. If all the parameters of the f unction call are dynam ic , an error occurs. Syntax CO ALESCE ( expression, expression [, expression]* ) The f unction must have at least t wo arguments. Example create tabl e w ith three different integer t ypes create tabl e temp(smallintcol smallint, bigintcol bigint, intcol integer); 0 row s inserted/updated/deleted ij> insert into temp values (1, null, null); 1 row inserted/updated/delet ed ij> insert into temp values (null, 2, null);
1 row inserted/updated/delet ed ij> insert into temp values (null, null, 3); 1 row inserted/updated/del et ed ij> select * from temp; SM ALL&|BIGINTCO L |INTCOL --------------------------------------1 |NULL |NULL NULL |2 |NULL NULL |NULL |3 3 row s selected ij> -- the return dat a t ype of coalesce is bigint ij> select coalesce (smallintcol, bigintcol) from temp; 1 -------------------1 2 NULL 3 row s selected ij> -- the return dat a t ype of coalesce is bigint ij> select coalesce (smallintcol, bigintcol, intcol) from temp; 1 -------------------1 2 3 3 row s selected ij> -- the return dat a t ype of coalesce is integer ij> select coalesce (smallintcol, intcol) from temp; 1 ----------1 NULL 3 3 row s selected
DEF AULT Values Using Sequences In Oracle 12c, it is now possible to specif y the CURRVAL and NEXTVAL sequence pseudo columns as the def ault values f or a column.
CREATE SEQUENCE t1_seq; CREATE TABLE t1 ( id NUMBER DEFAULT t1_seq.NEXTVAL, descript ion VARCHAR2( 30) ); INSERT INTO t1 (descript ion) VALUES ('DESCRIPTION only') ; INSERT INTO t1 (id, description) VALUES (999, 'ID=999 and DESCRIPTION'); INSERT INTO t1 (id, description) VALUES (NUL L, 'ID=NULL and DESCRIPTION'); SELECT * FRO M t1; ID DESCRIPTI ON ---------- -----------------------------1 DESCRIPTION only 999 ID=999 and DESCRI PTION ID=NULL and DESCRI PTION 3 rows select ed.
LAG The LAG f unction is used to access data f rom a previous row. The f ollowing query returns the salary f rom the previous row to calculate the dif f erence bet ween the salar y of the current row and that of the previous row. Not ice that the ORDER BY of the LAG f unction is used to or der the data by salar y. SELECT empno, ename, job, sal, LAG(sal, 1, 0) OVER (ORDER BY sal) AS sal_prev, sal - LAG(sal, 1, 0) OVER (ORDER BY sal) AS sal_dif f FRO M emp; EMPNO ENAME JOB SAL SAL_PREV SAL_DIFF ---------- ---------- --------- ---------- ---------- ---------7369 SMITH CLERK 800 0 800 7900 JAMES CLERK 950 800 150
7876 7521 7654 7934 7844 7499 7782 7698 7566 7788 7902 7839
ADAMS CLERK W ARD SALESMAN MARTIN SALESMAN MILLER CLERK TURNER SALESMAN ALLEN SALESMAN CLARK MANAGER BLAKE MANAGER JONES MANAGER SCOTT ANALYST FORD ANALYST KING PRESIDENT
1100 950 1250 1100 1250 1250 1300 1250 1500 1300 1600 1500 2450 1600 2850 2450 2975 2850 3000 2975 3000 3000 5000 3000
150 150 0 50 200 100 850 400 125 25 0 2000
SQL>
LEAD The LEAD f unction is used to return data from the next row. T he f ollowing query returns the salary f rom the next row to calculate the dif f erence bet ween the salar y of the current row and the f ollowing row. SELECT empno, ename, job, sal, LEAD(sal, 1, 0) OVER (ORDER BY sal) AS sal_next, LEAD(sal, 1, 0) OVER (ORDER BY sal) - sal AS sal_diff FRO M emp; EMPNO ENAME JOB SAL SAL_NEXT SAL_DIFF ---------- ---------- --------- ---------- ---------- ---------7369 SMITH CLERK 800 950 150 7900 JAMES CLERK 950 1100 150 7876 ADAMS CLERK 1100 1250 150 7521 W ARD SALESMAN 1250 1250 0 7654 MARTIN SALESMAN 1250 1300 50 7934 MILLER CLERK 1300 1500 200 7844 TURNER SALESMAN 1500 1600 100 7499 ALLEN SALESMAN 1600 2450 850 7782 CLARK MANAGER 2450 2850 400 7698 BLAKE MANAGER 2850 2975 125 7566 JONES MANAGER 2975 3000 25 7788 SCOTT ANALYST 3000 3000 0 7902 FORD ANALYST 3000 5000 2000 7839 KING PRESIDENT 5000 0 -5000
RANK Let's assume we want to assign a sequential order, or rank, to people wit hin a department based on salar y, we m ight use the RANK f unction like. SELECT empno, deptno, sal, RANK() OVER ( PARTITION BY dept no ORDER BY sal) "rank" FRO M emp; EMPNO DEPT NO SAL ---------- ---------- ---------- ---------7934 10 1300 1 7782 10 2450 2 7839 10 5000 3 7369 20 800 1 7876 20 1100 2 7566 20 2975 3 7788 20 3000 4 7902 20 3000 4 7900 30 950 1 7654 30 1250 2 7521 30 1250 2 7844 30 1500 4 7499 30 1600 5 7698 30 2850 6
rank
SQL> W hat we see here is where t wo people have the same salar y t hey are assigne d the same r ank. W hen multiple rows share the same rank the next rank in the sequence is not consecutive. DENSE_RANK The DENSE_RANK function acts like the RANK f unction except that it assigns consecutive ranks. SELECT empno, deptno, sal, DENSE_RANK() OVER (PARTITION BY deptno ORDER BY sal) "rank" FRO M emp; EMPNO DEPT NO SAL ---------- ---------- ---------- ---------7934 10 1300 1 7782 10 2450 2 7839 10 5000 3 7369 20 800 1
rank
7876 7566 7788 7902 7900 7654 7521 7844 7499 7698
20 20 20 20 30 30 30 30 30 30
1100 2975 3000 3000 950 1250 1250 1500 1600 2850
2 3 4 4 1 2 2 3 4 5
SQL> FIRST and LAST The FIRST and LAST f unctions can be used to return the f irst or last value f rom an ordered sequence. Say we want to display t he salar y of each employee, along wit h the lowest and highest with in their department we may use something like. SELECT empno, deptno, sal, MIN(sal) KEEP (DENSE_RANK FIRST ORDER BY sal) OVER (PARTITION BY dept no) "Lowest", MAX(sal) KEEP (DENSE_RANK LAST ORDER BY sal) OVER (PARTITION BY dept no) "Highest" FRO M emp ORDER BY deptno, sal; EMPNO DEPT NO SAL Lowest Highest ---------- ---------- ---------- ---------- ---------7934 10 1300 1300 5000 7782 10 2450 1300 5000 7839 10 5000 1300 5000 7369 20 800 800 3000 7876 20 1100 800 3000 7566 20 2975 800 3000 7788 20 3000 800 3000 7902 20 3000 800 3000 7900 30 950 950 2850 7654 30 1250 950 2850 7521 30 1250 950 2850 7844 30 1500 950 2850 7499 30 1600 950 2850 7698 30 2850 950 2850 SQL>
NVL The NVL f unction allows you to replace null values with a def ault value. If the value in the f irst parameter is null, the f unction returns the value in the second parameter. If the f irst parameter is any value other than null, it is returned unchanged. W e know that COL1 in the test table cont ains n ull in all rows except the f irst. Using the NVL f unction we replace the null values with 'ZERO' . SQL> SELECT id, NVL(col1, 'ZERO') AS output FRO M null_test_tab ORDER BY id; ID OUTPUT ---------- ---------1 ONE 2 ZERO 3 ZERO 4 ZERO 4 rows select ed. SQL> DECODE The DECO DE f unction is not specif ically f or handling null values, but it can be used in a sim ilar way to the NVL f unct ion, as shown by the f ollowing example. SQL> SELECT id, DECODE(col1, NULL, 'ZERO', col1) AS out put FRO M null_test_tab ORDER BY id; ID OUTPUT ---------- ---------1 ONE 2 ZERO 3 ZERO 4 ZERO 4 rows select ed. SQL> NVL2 The NVL2 f unction accepts three parameters. If the f irst parameter value is not null it returns the value in the second parameter. If the f irst parameter value is null, it returns the thir d paramet er. The f ollowing query shows NVL2 in act ion. SQL> SELECT id, NVL2(col1, col2, col3) AS output FRO M null_test_tab ORDER BY id;
ID OUTPUT ---------- ---------1 TW O 2 THREE 3 THREE 4 THREE 4 rows select ed. SQL> The f irst row in the test table has a not null value in COL1, so the value of COL2 is ret urned. All other rows cont ains null in COL1, so the value of COL3 is returned.
COALESCE The COALESCE f unction was introduced in Oracle 9i. It accepts two or more parameters and retur ns the f irst non -null value in a list. If all parameters contain null values, it returns null. SQL> SELECT id, COALESCE(col1, col2, col3) AS output FROM null_test _tab ORDER BY id; ID OUTPUT ---------- ---------1 ONE 2 TW O 3 THREE 4 THREE 4 rows select ed. SQL> NULLIF The NULLI F f unction was introduced in Oracle 9i. It accepts two paramet ers and returns null if both parameters are eq ual. If they are not equal, the f irst parameter value is r eturned. In our test table the values of COL3 and COL4 are equal in row 4, so we would only expect null ret urned f or that row using the f ollowing query. SQL> SELECT id, NULLIF(col3, col4) AS output FRO M null_t est_tab ORDER BY id; ID OUTPUT ---------- ---------1 THREE 2 THREE
3 THREE 4 4 rows select ed. SQL>
PL/SQL PL/SQL is a procedural extension to Oracle SQL. PL/SQL is integrated with Oracle Database, enabling you to use all of the Oracle Database SQL statements, f unctions, and data t ypes. You can use PL/ SQL to control the f low of a SQL program, use var iables, and write err or -handling procedures . A primary bene fit of PL/SQL is the ability to s to re application lo gic in the database i tself. A PL/SQL procedure o r function is a schema o bject that consists of a se t of S QL s tatements and other PL/S QL cons tructs, g rouped togethe r, s tored in th e database , and run as a uni t to sol ve a s pe cific problem or to pe rform a set of rela ted task s. The principal benefit of se rver -side programming is tha t b uilt -in functionality ca n be deplo yed an ywh ere. Oracle Da tabase can also sto re p rogram u nits wri tten in Ja va . A Ja va sto red procedure is a Ja va me thod published to SQL and s to red in th e database for general use . You can call exis ting PL/SQL programs from Ja va a nd Ja va programs from PL/SQL.
It is a procedural language using sql concepts. It is an extension to the sql.
pl/sql program It is a collection of programming stmts and with sql concepts ( queries ) to perf orm user required tasks . W hat is pl/sql? Pl/sql is a collect ion of User def ined objects like Programs, procedur es, Funct ions, Triggers, Types, Packages and so on. Advantages:
Multiple queries ar e executed par llelly. It reduces number of hits to the dat abase. In pl/sql user can cr eate the objects according to his requirements. It increases n/ w performance. Modularit y ( dividing a big task into smaller modules ) Enhansibilit y ( It can easily accept the f uture changes ) Reusabilit y
2 Categories of Programs i) Anonymous Blocks ( Programs ) ii) Sub Pr ograms (Stored Procedures and Functions)
i) Anonymous Blocks It is a program which is not saved inside t he DataBase. 2 t ypes of anon ymous blocks a) Static Programs It is not accepting runtime input values and Always generate same output, Input value is f ixed. b) Dynamic Programs It will accept RunTim e Input values and G enerates dif f erent outputs based on diff erent input values.
STRUCTURE OF PL/SQL PROGRAM : DECL ARE [ Optional ] <declaration stmts >; BEGIN [ Mandator y ] ;