Gpp. Satyam Theatre, technologies Ameerpet, Hyderabad - 500 016. ssenmaie TRAINING BERENS ENT E-mail:info@nareshit.com www.nareshit.com Ph:23746666, 23734842 Cell: 9000994007, 3000994008Oracle 11g Material —————————— fhat is Database Actually? Database is a platform where we can place the data fro the future references. Database is a collection of interrelated data, i.e. database always stores data along with 's relationships. What js Database Management System? ‘A database Management System is essentially a collection interrelated data and a set of programs to access this data, This collection of data is called the Database. The primary objective of a DBMS is to provide a convenient environment to retrieve and store database information. Database System support single user and multi-user environment. While on one hand DBMS permits only one person to access the database at a given time, on the other RDBMS allows many users simultaneous access to the database. ‘A Database System consists of two parts namely, Database Management System and Database Application. Database Management System is the program that organizes and maintains the information whereas the Database Applications is the program that lets us view, retrieve and update information stored in the DBMS. + Database is a collection of data in one or more files for the future reference, * Database is a collection of interrelated data, i.e. database always stores data along with it’s relationships. Database Modeis: The main object'of the database is store the inter related data and maintain the data. The very basic elementary piece of data is called as ‘data item'.we assume that item cannot be subdivided into smaller data types and at the same time retain any meaning to the users of the data. The relationship among the data items, which shows how they are related, is called as Data model’. The database models are 1. File Management System(FMS) 2. Hierarchicla database System(HOS) 3. Network database System(NDS) 4, Relational database Management System (RDBMS). File Management System: The File Management System was the first method used to store data in a computerized database, Each data item is stored on disk sequentially in one large file. In order to locate one particular item the search starts at the beginning and each item checked subsequently till the match is found.A particular relationship cannot be drawn between the items other than the sequence in which it is stored. Drawbacks: + A particular record can not be located quickly. If the data has be stored, the whole file has to be read and rewritten in the new order, Tt will not support data types. It will not support to store or allocate memory dynamically, Data con not be shared with concurrent users. Data duplication. Very poor security: Operating system provide only a password mechanism for security, This is not sufficiently flexible to enforce security polices in which different users have permission to access different subsets of the data * Retrieving data from a file is not faster: We have to write special pragrams to answer each question a user may want to ask about the data, These programs are likely te be complex because of the large volume of data to be searched. Naresh i Technologies, Opp. Satyam Theatre, Anieerpet, Hyderabad, Ph: 23746666, 9000994008Oracle 11g Material Hierarchical Database System(HDS): as Dent bag [on] (ioe | (amr (Cien ][om ] [Tn | I L aim fo own fof ae | waa Jol oe Jo ooo] Fig 1. Data storage is in form of a parent-child relationship.The origin of a data tree is the root,Data located at different levels along a particular branch from the root is called the node.The last node in the series is called the leaf.This model supports One-to-Many relationship.From the figure 1.0 it can be seen that the nodes in the third level are interrelated.Each child has pointer to nuierous and there is just one pointer to the parent thus resulting in a One-to-Many relationship. Suppose an information is required ,say ID 3.1, it is not necessary for the DBMS to search the entire file to locate the data.Insted, it first follows the Dept3 branch and fetches the data. Disadvantages: Tt Is not possible to enter a new level into the system. As and when such a need arises the entire structure has to be revamped. Another disadvantage is that this model does not support Many-to-Many relationship. In case this sort of relation is required then multiple copies of the same data have to made which result in redundancy. To overcome this drawback, tive Network Database Model was introduced. Note: The terminal points shows, for example below ID. 1.n.indicate that similar data are present at that point. Network Database Systems(NDS' The main idea behind the NDS model is to bring about Many-to_Many relationship. The relationship between the different data items is called as sets. This system also uses a pointer to locate a particular record(i.c called as Physical Links) Disadvantages: ‘The use of pointers leads to complexity in the structure, As a result of the increased complexity mapping of related data become very difficult. Relational Mode(RDBMS):- + The Model was first outlined by E.F Codd 1970, + The Components of Relation Model are: © Collection of objects or relations that stores the data © Aset of operations that can act on the relations to produce other relations. + Data integrity for accuracy and consistency. + Ithas only logical representations.( i.e in the form of table(rows and columns.) * Intersection of rows and columns gives single value. Naresh I Technologies, Opp. Satyam Theatre, Ameerpet, Hyderabad, Ph: 23746666, 9000994008. Oracle 11g Material No data redundancy . No physical link relations are maintain logicaliy. Supports NULL values, integrity constraints. Provides high security. Supports unlimited size. supports to store any data types (number characters date, images audio text files.) Data can be stored among several users at time. * Data can also be shared across several plot forms. * Oracle 10g is proven to be the fastest database for Transaction processing Datawarehousing, and third party applications on servers of all sizes, + Use of Flashback Queries Alt queries the database by TIME or user specified SCN (System Change Number). 2)It uses Oracle's multi version read-consistency capabilities to restore data by applying UNDO as needed ENTITY RELATIONSHIP Model: « Inan effective system data is divided into discrete categories or entities. ‘An ER-Model is an illustration of various entities in a business and the relationships beti-een them. + ICs built during the analysis phase of the System Developing Life Cycle. * ER-Model separates the information required & the business from the activities performed. ER-MODEL Benefit: ‘It document information for the organization in a clear, precise format. * Provides a clear picture of the scope of the information requirement. + Provides an easily understood pictorial map for the database design. + Itoffers an effective framework for integrating multiple application. Key Components in ER-MODEL: * ENTITY: It is a thing of significance about which the information need to be know. + ATTRIBUTES: It is something that describes or qualifies an entity. ¥* Each attribute may be mandatory or optional but one attribute Mandatory. Relation among dat: + Arelationship is defined as “an association among entities. + Arelationship type is an association of entity types. + Several relationships may exist between the same entity. The three different types of relationships recognized among various data stored in the database are: © One-to One + Qne-to Many(or Many-to-One) + Many-to-Many ‘Naresh i Technologies, Opp. Satyam Theatre, Ameetpet, Hyderabad, Ph: 23746666, 9000994008One-to-One: * Consider for example a set of students in a class. Each student can only one roll number. Similarly, each roll number can be associated only with one student, This is the case of One-to-One relationship Fig 1.2 illustrates this relationship. Student 1 ot Assigned > Rotlno 1 Student 2 Rollno 2 Many-to-One: One student can register for only one particular course at a time, where a number of students could register for the same course, This is illustrated with Fig1.3 Students |. Student2 Register Course Student 3 : ig 1.3 Many-to Many: A vendor can sell a number of items and many vendors can sell particular iter. This can be understand from Fig 1.4 given below. Vendor 1 Item 1 Vendor 2 Item 2 Fig 1.4 Relation Database Terminology: Row or Tuple + Terepresents all data required for a particular instance in entity, + Each row is an entity is uniquely identified by declaring it has PRIMARY KEY or UNIQUE. + The order of the rows is not significant, while retrieving the data. Column Or Attribute: «It represent one kind of data in a table. + The column order is not significant when storing the data. A Field: «It can be found at the Intersection of row and a column. + Afield can have only one value, or may not have a value at all, the al-ence of value in Oracle is represented as NULL. Relating Multiple Tables: ‘* Each table contains data that describes exactly only one entity. * Data about different entities is stored in different tables. * RDBMS enables the data in one table to be related to another table b* sing the Foreign keys. + AForeign Key is a column or a set of Column that refer to a Primary |. «in the same table or another table, Rational Database Properties: ‘+ Should not specify the access route to the tables, and should not rev-» he physical arrange. Naresh i Technologies, Opp. Satyam Theatre, Ameerpet, Hyderabad, Phi 23746666, 90°: 4008"Oracle 11g Matertat —— eee ee «The Database is accessed using Structured Query Language(SQL) + The language is a collection of set operators. Communicating Wit! MS: + The Structured Query Language is used to Communicate with RDBMS. Structure Query Language: ‘* Structure Query Language and commonly pronounced, as “SEQUEL” (Structured English Query Language) * Dr E.f Codd published the paper on relational database model in June 1970. IBM Corporation , Inc. SEQUEL later become SQL. allows the user to communicate as the server. It is easy to learn and use. It is functionally complete, by allowing the use to define, retrieve and manipulate the data. Components of SQL: * Oracle SQL Complies with industry accepted standards. * The SQL Contains 5 Sub Language. zeData Retrieval/ Query Language( DRL/DQL) SELECT ‘* It used to retrieve the information from database objects for read only purpose, 4 Data Manipulation Language(DML). «It used to manipulate the data in database objects INSERT(new content) UPDATE(modify) DELETE(remove) 24Data Definition Language(DDL) «Used to define database objects creation modification removing CREATE = ALTER DROP TRUNCATE = RENAME x Data Control Language(DCL) * It used to share the information between users GRANT(give) REVOKE(cancel) © Transaction Control Language(TCL) "It used to save or cancel DML operations COMMIT ROLLBACK SAVEPOINT Naresh i Technologies, Opp. Satyam Theatre, Ameerpet, Hyderabad, Phi: 23746666, 9000994008 -5 -Oracte Lig Material Oracle Architecture: User SQL * Net nnn Processes | Listener Multithreaded Server =I Shared Pool System Global Area | Program Global Area Private To distributed database nodes RECO . icen | [ ucwe__|[__cxer DBWR SHON nrchive Destination a ("seco | bogs j [Datat.dbf | ing redologs = * | ee [men Data abr Init.org Controt File | [> el 7 Fig 4.1 User Processes © When a user runs an application program, such as a Pro*C program, or an Oracle tool, such as Server Manager, Oracle creates a user process to run the user's application. -->user process will create in client side. -->SQL * Net Listener will run on server for particular port. --> Listener will listen for incoming request and service the request.(it will create a server process on server?) Dispatcher Processes(Dnnn). + The Dispatcher processes allow user processes to share a limited number of server processes. + Without a dispatcher, each user process requires one dedicated server process. Multithreaded Server(Shared server) > In dedicated server architecture each user process will create a Server process on the server where as in shared server architecture each server process will be shared by multiple user process. Naresh i Technologies, Opp. Satyam Theatre, Ameerpet, Hyderabad, Ph: 23746666, 9000994008Oracte 11g Material —— ee Oracle SGA(System Global Area): * Oracle allocates @ memory area called the System Global Area(SGA) and starts ‘one or more Orarle processes. * A System Global Area(SGA) is group of shared memory structures that contain data and control information for one Oracle database instance, + Note: The combination of SGA and the Oracle processes is called an Oracle database instance. * The SGA, consists of several elements. Buffer Cache: ‘+ The buffer cache stores Oracle data in memory for users to view or change .In this way, user never make changes directly to disk files. Redo Log Buffer: * The log buffer stores special information called redo, which helps oracle reconstruct data changes in the event of system failure. Shared Poot + Components of the shared pool include the library cache, for storing parsed SQL statements for reuse by other users. Library Cache ‘The library cache includes shared SQL areas, private SQL areas, PL/SQL procedures and packages, and control structures such as locks and library cache handles. Dictionary Cache + The data Dictionary is a collection of database tables and views ,and its users. Data Dictionary stores Names of all tables and views in the database >Names and data types of columns in database tables > Privileges of all oracles users. Server Processes: «Server process created on behalf of each user’s application may perform one or more of the following: © Parse and execute SQL statements issued via the application. © Read necessary data blocks form disk(datafiles) into the shared database buffers of the SGA, if the blocks are not already presents in the SGA, Background Processes The background processes in an Oracle instance include the following Lock(LCKn) ‘+ With the parallel Server option, up to ten Lock processes(LCK0,....LCK9) provide inter-instance lacking. However, a single LCK process(LCK0) is sufficient for most Parallel Server Systems. Database Writer(DBWR_ + Database Writer process writes buffers to datafiles. + The primary job of the DBWR process is to keep the buffer cache “clean” by writing dirty buffers to disk. Log Writer Process(LGWR) + The Log Writer Process(LGWR) writer the redo log buffer to a redo fog file an disk, * LGWR writes one contiguous portion of the buffer to disk, LGWR writes a commit record when a user process commits a transaction. Recoverer Process(RECO) * The Recoverer process(RECO) is a process used with the distributed option that automatically resolves failures involving distributed transactions. * The remote server is not available or the network connection has not been re- established ,RECO automatically tries to connect again a timed interval. Checkpoint Process(CKPT) * When a checkpoint occurs, Oracle must update the headers of all datafiles vo record the details of the checkpoint, This is done by CKPT. Naresh i Technologies, Opp. Satyam Theatre, Ameerpet, Hyderabad, Ph: 23746666, 9000994008 -7 -Oracle Hg Material Cee Process Monit IN): ‘+ The process Monitor (PMON) performs process recovery when a user process fails. + PMON is responsible for cleaning up the cache and freeing resources that the process was using Ex; It resets the status of the active transaction table, release locks. Snapshot Refresh Processes(Snpn) + With the distributed option, up to ten Snapshot Refresh processes(SNPO,...SNP9) can automatically refresh table snapshots. + These processes wake up periodically and reftesh any snapshots that are scheduled to be automatically refreshed. ‘System Monitor(SMON) * The System Monitor. process(SMON) performs instance recovery at instance start up. + SMON is also responsible for cleaning up temporary segments that are no longer in use. Datafites. «This mandatory disk components is used for storing oracle dictionary and application database object. * Datafiles stores Oracle data. * Each datafile can be associated with only ane database. Redo Logs . ‘+ This mandatory disk components is used for storing redo infermation on disk. {ic rollback segment data). Control file + The Physical locations of both datafiles and redo logs in the server's files system are stored in your control files. Parameter Files(init.ora’ + This mandatory disk components is used for configuring how oracle operates while it is running . Sql * Plus Buffer: + SQL * Plus ORACLE has developed it’s environment tool. We can use directly SQL & PL/SQL statements in this environment tool. All commands of SQL are typed at the SQL prompt. Only one SQL Statement is managed in the SQL Buffer. The Current SQL statement replaces the previous SQL statement in the Buffer. + SQL statement can be divided into different lines within the SQL Buffer. * Only one line i,e., the current line can be active at a time in the SQL Buffer. + At SQL prompt, editing is possible only in the current SQL Buffer tine. + Every statement of SQL should be terminated Using(;}. + To run the previous or current SQL statement in the Buffer type’ at SQI. prompt. + To open the SQL Editor type ed at SQL prompt. * It provides. © SQL commands © PL/SQL block ‘i © It has own commands to set environment as reautirement. © To generate report. © To save SQL command as file(.sql) © Tosave PL/SQL command as file(.sq]) © To save output as file(.Ist) Naresh i Teeluologies, Opp. Satyom Theatre, Ameerpet, Hyderabad, Ph: 23746666, 9900994008y Oracle Lig Material reatin« Managing Tables: "A Oracle database can contain multiple data structures. «The different Database objects in Oracle are: TABLE: Used to store data, Sasic Unit VIEW: Logically represent subsets of data from one or more tables. ‘SEQUENCE: Used to Generate Primary Key values. INDEX: It is used to improve the performance of some queries. SYNONYM: Used to give alternate names to objects. Table in Oracle: + Table can be crated at any time, even when the users are using the database. «Size of the table need not be specified. For create table: 1)Table Name 2)Column Name 3)Data Type Table Restriction: + The user should have permission or CREATE TABLE command, and storage area, + Table name must be unique in schema «The Table name should begin with a letter and can be 1-30 characters long + Maximum 1000 calumns(8.0), 256 in 7.x * Names can contain: eA Z a — 2 0-9 $F + Names can not be duplicated for another object in the same ORACLE Server. «Name cannot be oracle server reserved words. * Names are not case sensitive, COLUMN NAME: ~The column name should begin with a letter and can be 1-30 characters long, + Column name is Unique in a table. + Column name can be renamed, + Maximum columns in table is 1000. Data Types in Oracle: * Each value in ORACLE is manipulated by a data type, + The values of one data type are different from another data type. ‘+ The data type defines the domain of values that each column can contain. "The Built-in-data types af ORACLE are Categorized as CHARACTER Data Types NUMBER Data Types LONG and RAW Data Types DATETIME Data Types ROWID Data Types Character Data Type: ‘They store character data which can Alphanumeric data. * The Information can be © Words Database Character set © National Character set They are less restrictive than other data types an have very few properties, + They data is stored in strings with byte values + The different character data types are: CHAR NCHARVARCHAR VARCHAR2 NVARCHAR2 Naresh i Technologies, Opp. Satyam Theatre, Ameerpet, Hyderabad, Ph: 23746666, 9000994008» 9-Oracte Lig Material Dai t * It specifies fixed length character string. "The size should be specified. * If the data is less than the original size, blank pads are applied. * The default length is 1 Byte and the Maximum is 2000 Bytes. NCHAR Data Type: "Its first defined in ORACLE 9i, and contains Unicode data only. * The column's maximum length is determined by the National Character set definition. * The Maximum size allowed is 2000 Bytes and size has to be specified. * If the data short than the actual size then the blank pads are applied Varchar2 Data Type: - * A data type used for storing text data. + The Minimum size is 1 Byte and the Maximum size is 4000 Bytes. + It occupies only that space for which the data is supplied. + Any text character (including special characters, number, dashes, and so on) can be stored. Iv 2 Data Type: «tis first defined in ORACLE 9i, and contains Unicode data only, + The Minimum size is 1 Byte and the Maximum size is 4000 Bytes. Number Data Type: Number(Precision, Scale) + Number data type stores the numeric data, the precision is the total nc of digits required and scale stands for the rounding of decimal place. + Range of precision is from 1 to 38. + Range of Scale is -84 to 127. Integer Data Type: * Integer data type will converted as Number Data type with the maxim size 38 digit as precision . LONG: "This data type is used to store characters or numbers. + Maximum size limit is 2 GB. * only one Long column is valid per table. Date & Time Data Type: «Its used to store dates and time information. + The information revealed by date is: *Century —*Year *Month *Date *Hour *Minute — *Second «The default date format in ORACLE is DD-MON-YY. + The default time accepted by ORACLE date is 12:00:00 AM(Midni:ht), * The default date accept by ORACLE data is the First day of the Current month, + The Date range provide by Oracle is JANUARY 1,4712 BC to DECEMBER 31,9999 AD, estamp Date Type: + Itis an extension of the DATE data type. + Ttstores *Day *Month = *Year = *Hour *Minute . —*Seco nd Naresh i Technologies, Opp. Saiyam Theatre, Ameerpet, Hyderabad, Ph: 23746666, 9000994008 - 10-Oracle Lig Material Syntax: TIMESTAMP(Fraciional-Seconds-Preci «Fractional -Seconds-Precision optional specifies the number of digits in the. fractional part of the SECOND datetime field, «= Itcan be a number in the range of 0-9, with default as 6. It stores binary information like Photos, Signature, Thumb impressions ect. Maximum length is 2000 Bytes. ‘The Oracle converts the RAW and LONG RAW data into Hexadecimal form. Each Hexadecimal character represent four bites of RAW data. + It stores the binary data similar to RAW but can store-more bytes than RAW, + Maximum length is 268. "No size is required. Large Object(LOB) Data Types: The Built in LOB data types are *BLOB *cLoB *NCLOB + These data types are stored inerally. + The Bfile is an LOB which is stored externally. ‘+ The LOB data type can store large and unstructured data like Text, Image, Video and Spatial deta. © The maximum size is upto 4GB. * LOB columns contains LOB locators, Which can refer to out-of-line to or in-line LOB values. + _LOB's selection actually return the LOB’s locator. BEILE Data Typ + Ttenables access to binary file LOB's which are stored in the systems outside ORACLE. + A BFILE column or the attributes stores the BFILE locator. + The BFILE locator maintains the directary alias and the filename. ‘+ The Binary File LOB’s do not participate in transaction and are not recaverable. + The maximum size is 4 GB. BLOB Data Type: + It stored unstructured Binary Large Objects, + They are Bit streams with no character set semantics. ‘+ They are provide with full transactional support. CLOB Data Type: + It Dynamic data type. + They are provide with full transactional support. + The maximum size is 4 GB. NCLOB Data Type: + Ie stores Unicode data using the National Character set ROWID Data Type: * Each row in the database has as address. + The rows address can be queried using the pseudo column ROWID. + ROWIO's efficient support partitioned table and Indexes. Codd’s Rules:- Dr E.F.CODD’s listed 12 rules for relational databases In 1985. 1, The Relational data base management rule: + Arelational data base management system must use Only its relational capabilities to manage the information stared in the database. 2. The Information rule: * All information in relational database must be represented as values within columns in rows. + Pointers are variables that contain address of the memory location where the data is stored. ‘Naresh i Technologies, Opp. Satyam Theatre, Ameerpet, Hyderabad, Ph: 23746666, 9000994008. - 11 -Oracle 11g Material 3, The Guaranteed Access rule: + Every item of data must be logically addressable with tne help of a table name, primary key value and column name. 4. The Systematic Treatment of NULL value rule: + Arelational database must support the NULL value for representing missing or inapplicable data. 5. The Dynamic On-line catalog Based on the Relational Model Rule: «The structure of the data base should be represented at Logical level in a tabular form. + The representation of database structure as a collection of tables is known as catalog or data dictionary. 6, The_Comprehensive Data sublanguage rule: * An RDBMS support at least one clearly defined language that can be used interactively as well as can be embedded in programs.(SQL). 7. The view updating rule: * All theoretically updatable views must be updatable in table. 8. The High-Level insert Update and delete rule: +” The date retrieval from a relational database must be possible in sets that may comprise data from multiple rows or multiple tables. The physical Data Independence rule: + The database user need not known about the physical Implementation of data storage and retrieval. 7 10. The Logical Data Independence rule: +” The data base user's view of a data should not be effected by any change in the database tables.(Materialized view). 11, The Integrity Independence rule: +. Data integrity rules must be defendable in the relational database and should be stored in data dictionary, 12. The Distribution Independence rule: + The system must be able to access or manipulate the data that is distributed in other systems. 13. Non-subversion Rule: * If a database management system has a low level language, then it cannot be used to violate the integrity rules or constrains that have been expressed using a higher-level language. To Create Table: Syntax: Sql>Create Table (,......); Not ‘The character ';’ is the terminator for SQL statement. Example: Sql>Create Table Emp_det (_ Empno Number(4), Ename Varchar2(15), Job Varchar(15), Mgr Number(4), Hiredate Timestamp, Sal Number(6,2), Comm Number(6,2}, Ephoto BLOB, Deptno Number(2) ); Sql>Create Table Student_Det (StudNo Number(4), Frame Varchar2(15), Lname Varchar2(15), DOB Date, DO) Date, Fees Number(6,2),Gender Char ); Naresh j Technologies, Opp. Satyam Theatre, Ameerpet, Hyderabad, Ph: 2374666: 969994008 - 12+Oracte 11g Material Inserting Data in Tabi * Arow is inserted into a table by.using INSERT command. * Rows can insert into a Table Views Base Table. A partition of a Table. A Sub Partition of a Composite Partition Table. An Object Table. ‘An Object View’s Base Table. coooe Inserting of data into a table can be executed in two ways. «Conventional INSERT. « Direct-Path INSERT. In conventional Insert statement, Oracle reuses free space in the table into which the data in being Insert and. Maintains Referential Integrity Constraints, In Direct-Path insert, Oracle append the insert data after existing data in the Table, the free space is not reused. Syntai SqI>INSERT INTO [list of columns] VALUES(list of values); Sql> INSERT Into Student_Det Values (1001, ‘THOMAS’, 'SIEBEL’, '30-DEC-81', “02-JAN-91', 30000, "M’ ); * In this case the values should be provided to all the columns that exists inside the table, + The order of values declared in the Values clause should follow the original order of the columns in the table. The Character and Date type data should be declared in single Quotes. Number information can be applied normally. Inserting Data into Required Columns: 5 Sql> Insert Into Student_Det( StudNo, Frame, Lname , DOJ ) Values (1002, DAN’, ‘SMITH’, '12-JAN-09" ); * In this case the Order of columns declared in insert need not be the same as that of the original table order. ‘+ The data values in the values caluse should match with that of INSERT list. ~ + The columns not supplied with data are filled with NULL values, Until the NOT NULL constraint is declared. Inserting NULL values into Table: © Null represent information that is not available. + Nyll Value is “o Unknown value Undefined value Not equal to 0 or blank space represented with ‘NULL’ keyword. Not allowed arithmetic, relational operation on Null, If performed it return Null only. o RDBMS must support NULL Values. * NULL values can be inserted in two ways. © Implicit->Omit the columns from list oracle supplies NULL. values. © Explicit->Specify the NULL keyword. Sql>Insert Into Student Det ( StudNo, Fname, Lname, DOB, DOI, Fees, Gender ) Values ( 1003, ‘RAMA’, NULL, '12-JAN-81', NULL, 30000, 'M'); Naresh i Techuologies, Opp. Satyam Theatre, Ameerpet, Hyderabad, Ph: 23746666, 9000994008 ~ 13-Oracle 11g Material Inserting special Values into Table: | SYSDATE FUNCTION + SYSDATE is Pseudo-column. + This function return the current date& time. ISER FUNCTION: + This function returns the user name of the user who has logged in. Example: SQL>CREATE TABLE Student (Stuid Number(4),Name Clob,DOB Date,DOJ Date, Fees Number(8,2),Gender char); Example: ‘SqI>INSERT INTO student VALUES(1001 user,'01-JAN-85',sysdate,2000,'M'); ‘Substitution Variables: + These variables are used to stored values temporaily + The values can be stored temporarily through Single Ampersand(&) | Double Ampersand(&&) “DEFINE and ACCEPT Commands ‘+ The Single Ampersand substitution variable applies for each instance when the SQL statement is create or execute. + The Double Ampersand substitution variable applies for all instances until that SQL statement is existing, USING SINGLE AMPERSAND SUBSTITUTION VARIABLE: ‘Sql> INSERT INTO dept VALUES(&Dno,'&Dname’,'&Loc’); Sql> INSERT INTO emp(Empno,Ename,Hiredate,Deptno) VALUES (f&Eno,'&Ename’,'&Hiredate', &dno); S Note: To run command is used for executing a previous command... >run or >/ USING DOUBLE AMPERSAND SUBSTITUTION VARIABLE: SqI>INSERT INTO student VALUES(8Sid,'&Name’,'&Dob' ,&Doj, &8fee,’&Gender’); DEFINING CUSTOMIED PROMPTS: ‘Sql>ACCEPT Deptno prompt 'Please Enter the deptnumber Sql>ACCEPT Dname prompt 'Please Enter the Deptname: Sq!>ACCEPT Location prompt 'Please Enter the Location:’ Sql> INSERT INTO dept VALUES(&Deptno,&Dname,&Location); (CREATING AN .LST FILE: + The List file is used to save all information which performed in SQL *Plus 1)In SQL’* Plus Navigation: File>Spool->Spool File 2) Give the file name 3) Performed all tasks. 4) Now Spoo! Off. CREATING AN SQL SCRIPT FILE: ‘The SAVE Command is used to store the current contents of the SQL Buffer. Steps: 1)At SQL prompt type the full name of the path where the file bas to be created. 2)Give the name of the file with .sql extension. 3)If the has to be replaced with the same existing name then use REPLACE ALL clause, Naresh i Technologies, Opp. Satyam Theatre, Ameerpet, Hyderabad, Ph: 23746666, 4110994008 - 14Oracle 11g Material eS Example: Sql>SAVE E:\nitdir\ins Sal> SAVE E:\nitdir\ins REPLACE To Get Script: - Get used to display the script file . ‘Sq!>Get E:\nitdir\ins ‘To Run Scrip! Run is used to display the script and run the file. SqI>RUN E:\nitdir\ins To simple the script file. Sql>@ E:\nitdir\ins To Open the Script file in editor ‘Sqi>ed E:\nitdir\ins Data Retrieval Languages‘ ‘DI je QUERY: It is an operation that retrieves data from one or more table or view. ‘SELECT Statement: ‘+ Select used to retrieve data from one or more than table ,view, object tables. + It is for read only purpose. + The select is the most frequently used command as access to information is needed all the time. PREREQUISTIES: + The user ust have the SELECT privileges on the specified object. CAPABILITES OF SQL SELECT STATEMENT: The different types SELECT criteria. © SELECT: a It chooses the rows in a table that are expected to return by a query. - + PROJECTION: © It chooses the colurnns in a table that are expected to return by a query + JOIN: © It chooses the data in from one or more numbers of tables. Pata Retrieving Languag ‘Syntax: Sql>SELECT *|{[DISTINCT/UNIQUE] column | expression [Alias],....} FROM table. SELECT identifies what columns, specifies a list of column(one/more) FROM identifies which table DISTINCE — >Suppress Duplicates. * Select all Columns Columns/ Expression >Select the Named Columns or the expression. Alias Gives select columns different headings WRITING SOL STATEMENTS TO SELECT DATA FROM TABLES. The sample table. 1)DEPT Column Name Data Type DEPTNO NUMBER DNAME VARCHAR2 (14) Loc VARCHAR2 (13) Naresh i Technologies, Opp. Satyam Theatre, Ameerpet, Hyderabad, Ph : 23746666, 9000994008 - 15 -2)EMP Column Name EMPNO ENAME JOB MGR HIREDATE SAL coMM DEPTNO 3)SALGRADE Column Name GRADE LOSAL HISAL Dep! SQL> Select *From Dept; DEPTNO DNAME 10 ACCOUNTING 20 RESEARCH 30 SALES 40 OPERATIONS SQL> SELECT * FROM MY_EMP; EMPNO ENAME JOB MGR 7369 SMITH CLERK =—-7902 7499 ALLEN SALESMAN 7698 7521 WARD SALESMAN .7698 7566 JONES © MANAGER 7839 7654 MARTIN SALESMAN 7698 7698 BLAKE © MANAGER 7839 7782 CLARK = MANAGER 7839 7788 SCOTT ANALYST _ 7566 7839 KING PRESIDENT 7844 TURNER SALESMAN 7698 7876 ADAMS CLERK 7788 7900 JAMES CLERK_—-7698 7902 FORD = ANALYST 7566 7934 MILLER CLERK 7782 14 rows selected. SQL> Select *From Salgrade; GRADE LOSAL 1 700 1200 2 1201 1400 3 i401 2000 4 2001 3000 5 3001 9999 HISAL Oracle 11g Material Data Type NUMBER(4) VARCHAR2(10) VARCHAR2(9) NUMBER(4) DATE NUMBER(7,2) MUMBER(7,2) NUMBER(2) Data Type NUMBER. NUMBER NUMBER Loc NEW YORK DALLAS CHICAGO BOSTON HIREDATE © SAL COMM DEPTNO 17-DEC-80 800 20 20-FEB-81 1600 300 30 22-FEB-81 1250 500 30 02-APR-81 2975 20 28-SEP-81 1250 1400 30 O1-MAY-81 2850 30 09-JUN-81 2450 10 09-DEC-82 3000 20 17-NOV-81 5000 10 08-SEP-81 1500 0 30 12-JAN-83 1100 20 03-DEC-81 950 30 03-DEC-81 3000 20 23-JAN-82 1300 10 Naresh i Technologies, Opp. Satyam Theatre, Ameerpet, Hyderabad, Ph : 23746666, 9000994008 - 16 -c Oracle 11g Materiat ES etrieving Data from All Columns: ‘Sql>SELECT *FROM Emp; Sql>SELECT *FROM Dept; Sql>SELECT *FROM Salgrade; * In this the '*’ is a projection operator. + It project data from all the columns existing in the table with all records, + The data is displayed in a table format. Retrieving Data from Specific Columns: ‘Sql> SELECT empno, ename, job, sal. FROM Emp; Sq!>SELECT toc, dname, deptno FROM Dept; Sql> SELECT Hisal,Losal,Grade FROM Saigrade; + The columns names need not be in the same order as table. + The columns should be separated using comma. + The column names can be separated into different lines within the ‘SQL Buffer. + The casing of coluinn names is not important. wh He ling Default: «The default justification of the data after it is retrieved from the table is... LEFT >Date and Character RIGHT Number Data « The default Columns Heading display in UPPER case : Applying Arithmetic Operations in Select Statements: ¢ Arithmetic Expressions can be implemented through SELECT statement. + Arithmetic Expressions can be implemented to o Modify the way the data is displayed. © Perform calculations. ADA metic Expression can contain: * Simple columns names: * Constant numeric values * Arithmetic operators. ARITHMETIC OPERATORS: + The Arithmetic operations can be used to create expressions on NUMBER and DATE data, + The Arithmetic operators supported are... o + Addition © => Subtraction © *9 Multiplication of > division. +The Arithmetic operators can be used in any clause of a SQL statement. except the FROM clause. + SQL * Plus ignores Blank Spaces before and after the Arithmetic operator. Sql> Select empno,ename,sal,sal+500 FROM Emp; Sql> Select empno,ename,sal,sal-1000 FROM Emp; Operator Precedence: * Multiplication and Division take priority over addition and subtractions(*/+-) + Operators of the same priority are evaluated from left to right. © To prioritize evaluation and to increase clarity parenthesis can be implemented. Sql> Select empno,ename,sal, 12*sal+100 from Emp; Sql>Select erpno,ename,sal,(12*sal) +100 from Emp; Sql>Select empno,ename,sal,12*(sal+500) fram Emp; Naresh i Technologies, Opp. Satyam Theatre, Ameerpet, Hyderabad, Ph: 23746666, 9000994008 - 17Hal Ni es: NULL: Unknown value Undefined value Not equal to 0 or blank space. If @ row lacks the data for a particular column, than that value is said to be NULL or to contain NULL. + It represented with NULL keyword + No operations allowed on Null,/ if performed any arithmetic operations it return nul! only). + RDBMS must support NULL Values. ‘Sql>Select ename,job,sal,comm from emp; Sql> Select ename,job,sal,comm,12*sal+comm from emp; ener: inction: This functions work with any data type and pertain to using null value. NVL Function: ‘© The NVL function is used to convert a NULL value to an actual value. ‘Syntax: NVL(Exprl,Expr2) Expri: is the source value or expression that may contain NULL. Expr2; is the target value for converting NULL. NVL Conversions Data © NVL(Number_column, 6) © NVL(date_column, ‘01-JAN-09') © NVL(Character_column, "Unavailabie’) Note : If Expri is Character data then Expr2 may any Data type. ‘Sql>Select NVL(100,200) from dual; ‘Sqi> Select NVL(null,200) from dual; Sql> Select ename,sal,comm,sal+NVL(comm,0) from emp; Sql> Select ename,sal,comm,(sal*12)-+NVL(comm,0) from emp; Sql> Select ename,sal,comm,(sal+500)+NVL(comm,0) from emp; NVL2 Function: Syntax: NVL2(exprt expr2,expr3) © Ifexpri is aot null, NVL2 returns expr2.if expr is null, NVL2 returns expr3. Expr may any data type. ‘0 The data type of the return value is always the same as the data type of expr2_, unless Expr2 is character data . Example:- 1) select nvl2(comm,0,1000) from emp; 2) select sal,comm, sal+nvi2(comm,100,2000) from emp; SQL>select ename,sal,comm, nvi2(comm,'sal+comm’,'sal’) income from emp where deptno in(10,30); NULLIF Function: ‘Syntax NULLIF(expri,expr2) © Compares two expressions and returns null if they are equalor the first if there ate not equal. Example: sql>SELECT NULLIF(100,200) from dual; sql>SELECT ENAME,LENGTH(ENAME) “expri", JOB,LENGTH(JOB) "expr2", NULLIF(LENGTH(ENAME),LENGTH(JOB)) result from emp; Naresh i Technologies, Opp. Satyam Theatre, Ameerpet, Hyderabad, Ph; 23746666, 9000994008 - 18 -Oracle 11g Material Note: © The data type of the expri is same as the data type of expr2. o The NULLIF function is logically equivalent to CASE expression. COALESCE: ‘« It return first non-null expression ih the expression list. ‘Sql> SELECT coalesce(100,600,200) FROM dual; Sql> SELECT coalesce(null,600,200) FROM dual; Sql>SELECT Ename,Deptno,COALESCE(COMM,SAL,10) COMM FROM EMP OCP Questions : 1) The EMPLOYEE tables has these columns: LAST_NAME VARCHAR2(35) SALARY NUMBER(8,2) COMMISSION_PCT NUMBER(5,2) You want to display the name and annual salary multiplied by the commission_pct for all employees. For records that have a NULL commission_pct, a zero must be displayed against the calculated column, Which SQL statement displays the desired results? A, SELECT last_naize, (salary * 12) * commission_pct FROM EMPLOYEES; B. SELECT last_name, (salary * 12) * IFNULL(commission_pct, 0) FROM EMPLOYEES; C. SELECT last_name, (salary * 12) * NVL2(commission_pct, 0) FROM EMPLOYEES; D. SELECT last_name, (salary * 12) * NVL(commission_pct, 0) FROM EMPLOYEES; Defining a Column Alia : ‘+ An Alias is an alternate name given for any Oracle Object. * Aliases in Oracle are of two types. *Column Alias *Table Alias Column alias rename a Column Heading. * Alias Headings appear in uppercase By default. * Specify the alias after the column in the SELECT list using a space as a separator, + AS keyword between the column name and alias is optional. ‘+ The Alias contains spaces or special characters (such as # or $), or is case sensitive, enclose the alias in double quotation marks (" "). + Analias cannot be used, any where in the SELECT list for operational purpose. + Analias effectively renames the SELECT list item for the duration of the Query, Sql> SELECT Empno EmpNumber, Ename EmpName, Sal “EmpSalary” Job Designation FROM Emp; Sql> SELECT Grade AS “SalGrade", —_Hisal “High Salary Range", Losal “Lower Salary Range” From Salgrade; Sql> Select empno “EmpNumber", Sal “Basic’Sal*0.25 HRA, Sal*0,20 DA,Sal*0.15 "Pf", Sal+Sal*0.25+Sal*0.20-Sal*0.15 “Gross” FROM em| * The Concatenates operator concatenate columns or character strings or expressions or constant to other values or columns. * Is represented by two vertical bars (/|)- » The resultant column that is a character . Sql> SELECT empno||ename FROM Emp; : Sql> Select 'The Basic Salary of ‘||Ename|| ‘is Rs '||Sal Employee From Emp; Sql>Select Empno| |Ename||Ename||" ,Designation is ‘||job “Employees Information " From Emp; Naresh i Technologies, Opp. Satyam Theatre, Ameerpet, Hyderabad, Ph: 23746666, 9000994008 - 19-' Oracle 11g Material ———————————————— LITERALS IN ORACLE: ‘+ A Literal and Constant value are synonyms to one another and refer to a fixed data value, + The types of Literals recognized by Oracle are Text Literals o Number Literals o Interval Literals Text Literals: It specifies a text ot character literal. It is used to specify values whenever ‘text’ or CHAR appear in *Expression *Condition *SQL Function * SQL Statements. + should be enclesed' in single quotes. + Atext literal can have a maximum length of 4000 Bytes, Example: ‘Employee Information’ ‘Manager's Specification’ ral Cl cter Strin: + Aliteral value is a character, a number, of a date that is included in the SELECT list. * Aliteral value not a column name or a column alias. * Aliteral is printed for each row returned, that is retrieved by the SELECT ‘statement, . Literal strings of free-format text can be included in the query. A free-format text treated the same as a column in the SELECT list. Date and character literal must be enclosed within the single quotation marks *". Litera} increase the readability of the output. Sql>Select Ename ||":'|| ‘Month Salary="|| Sal As Salaries From: Em; ‘Sqi>Select ‘The Designation of ‘|| Ename || is ‘|| job As Designation “rom Emp; Sql> Select ‘The employee name is: ‘|/Ename||' and *}]'Designa‘'on is :'{Liob from Emp; Sql> Select "The Annual Salary of ')\Ename|| is ‘||Sal*12 As Annual_ Salary From Emp; Sql> Select Dname||'Department is Located at ‘||Loc From Dept ; Sql> Select Ename| |" Joined the Organization on *}{ Hiredate Fram Emp; Sql> Select Ename||' Works in Department Number '||Deptno||" as ‘ ||job From Emp; Sql> Select ‘The Employee Name is: *||Ename||, Designation is *}}»b FROM Emp; Sql> SELECT 'The Gross salary of ‘jjEname\{' is '{{(Sal+Sal*.254S= °.20- Sal*,15) FROM Emp; Duplicate Rows: + Unless we indicate otherwise, iSQL*Plus displays the results c’ - query without eliminating duplicate rows. + To eliminate duplicate rows in the result, the DISTINCT keywe- . used. + We can specify multiple columns after the DISTINCT qualifier + The DISTINCT qualifier affects ail the selected columns, and — esult is, every distinct combination of the columns. Sql>Select DISTINCT Job From Emp; Sql> Select DISTINCT Job,Deptno From Emp; Sql>Select DISTINCT Deptno,Job From Emp; Naresh i Technologies, Opp. Satyam Theatre, Ameerpet, Hyderabad, Ph: 23746666, 90. '208. = 20-Gracie ig Material iter Of Re The number of rows returned by a query can be limited using the WHER clause, ‘+The method of restriction is the basis of the WHERE clause in SQL. + A WHRE Clause contains a condition that must be met and should directly follow the From Clause. SqI>SELECT *|{{DISTINCT] columnjexpression [alias],...} FROM table [WHERE condition(s)}; «The WHERE clause can compares ‘© Values in Columns © Literal Values o Arithmetic Expressions © Function ‘+ The components of WHER clause are © Coiumn Name 0° Comparison Operator © Column Name, constant or list of values. «The Character strings and dates should be enclosed in single quotation marks. «Character values are case sensate and Date vaiues are format sensitive (DD-MON-YY) «The Comparison operator are used in conditions that compare one expression to another. Relational Or_Coniparison operators: F< > AEST DEES Example: Sql>SELECT empno, ename, job, sal from emp; Sql>SELECT loc, dname, deptno from dept; Sql>SELECT * from emp; Sql>SELECT * from students; SqI>SELECT * from emp WHERE deptno=10; Logical Operators: « A togical condition combines the result of two component conditions to produce a single result, * Three logical operators are available in Oracle. AND OR NOT ND Operator: ‘* The AND operator allows creating an SQL statement based on two or more conditions being met. «It Returns FALSE if either is FALSE, else returns unknown, Truth Table: AND TRUE TRUE TRUE FALSE TRUE FALSE TRUE FALSE FALSE FALSE FALSE FALSE Exampl Sql> Select Ename,Sal,Job Emp Where (Sal>=1500 AND Sal<=5000) AND Job="MANAGER’; Naresh i Technologies, Opp. Satyam Theatre, Ameerpet, Hyderabad, Ph: 23746666, 9000994008 - 21 -Ig Material ————————————————— OR Operator: ‘It return TRUE if either component conditions is TRUE. « Itreturns FALSE if both are FALSE, else return unknown. ‘Truth Table: OR TRUE TRUE TRUE TRUE FALSE TRUE FALSE TRUE TRUE FALSE FALSE FALSE Sql>Select Empno,Ename,Sal,Deptno From Emp Where Sal>=2000 OR Deptno=20; Sql>Select Empno,Ename,Job,Hiredate From Emp Where Job=’MANAGER’ OR Deptno=30; Sql>Select Empno,Ename,Job,Deptno From Emp Where. (Deptno=10 OR Deptno=20) OR JOB="MANAGER’; Sql> Select Ename,Job From Emp Where (Job="CLERK' or Job='SALESMAN’ or ob='ANALYST'); Sql> Select Ename,Hiredate,Deptno From Emp Where Job=’MANAGER’ OR Deptno=30; Sql>Select Ename,Sal,Job From EmpWhere (Sal<=2500 OR Sal>=5000) 01 MANAGER’; NOT Operator: «It returns TRUE if the following condition is FALSE. * It returns FALSE if the following condition is TRUE. If the condition is Unknown, it returns Unknown. Combination of AND OR Operator Jol Sql> Select Ename,Sal From Emp Where (Job='CLERK' or Jot ‘And Sal>3000; ‘PRESIDENT’ or Jol ANALYST’) Sql> Select Empno,Ename,Job,Sal From Emp. Where (Sal>1500 OR Job='MANAGER') AND Deptno=10; Sql> Select Empno,Ename,Job,Sal From Emp Where (Deptno=20 OR Job='MANAGER’) AND Sal>=3000; Not Operator Example: Sql> Select Empno,Ename,Job,Sal From Emp Where NOT Ename='SMITH'; Sql>Select Empno,Ename,Jeb,Sal From Emp Where NOT Sal>=5000; Sql> Select Empno,Ename,Job,Sal From Emp Where NOT Job='CLERK'; Sql> Select Empno,Ename,Job,Sal From Emp Where NOT Sal<=5000; Naresh i Technologies, Opp. Satyam Theatre, Ameerpet, Hytlerabad, Phi: 23746666, 9000994008 - 22-‘Sql> Select Empno,Ename,Job,Hiredate From Emp Where NOT Hiredate='17-DEC-80; Sql> Select Empno,Ename,Job,Hiredate From Emp Where NOT JOb="CLERK’ AND Deptno=20; Some Things To Note: Sql» Select Ename,Sal,Job From Emp Where Job>'CLERK'; Sql> Select Ename,Sal,Job From Emp Where Job<'CLERK'; ‘Sqi> Select Ename, Deptno,Hiredate From Emp Where Hiredate>'20- DEC-81'; Sql> Select Ename,Deptno,Hiredate From Emp Where Hiredate<'20- DEC-81'; Sql> Select Ename,Job,Hiredate From Emp Where Job*='CLERK’; Sql> Select Ename,Job,Hiredate From Emp Where NOT Job*= "CLERK; Sql> Select Ename,job, Hiredate From Emp Where NOT Job='CLERK'; Sql> Select Ename,Job,Hiredate From Emp Where NOT Hiredate='17-DEC-1980'; Sql> Select Ename,Job,Hiredate From Emp Where NOT Hiredate>'L8-DEC-1981'; Rules of Precedenc © The default Precedence order is All comparison operators NOT logical condition AND logical condition OR logical condition oo00 Note: * Override rules of precedence by using parentheses. Example: Sql> SELECT Ename,Job, SalFROM Emp WHERE Job = ‘CLERK’ OR Job = 'MANAGER' AND Sal> + Sql> SELECT Ename,Job, Sal FROM Emp WHERE (Job = 'CLERK' OR Job = 'MANAGER’) AND Sa!~ L_*Plus Operat: + BETWEEN .... AND.....; NOT BEWEEN ....AND.... + BETWEEN js used to o display rows based on a range of vz + The declared range is inclusive. + The lower limit should be declared first. Sql>Select Empno,Ename,comm. From Emp Where Comm, Between 500 AND 1000; ———— Naresh i Technologies, Opp. Satyam Theatre, Ameerpet, Hyderabad, Ph:Oracle 11g Material ————$—$—$—$—_[_$—[{—_—[—~_—_${_ {_{_—————————————————— Sql>Select Ename,Sal,Job From Where Sal NOT Between 1000 AND 1500; Sql?Select Ename,Sal,Job From Emp Where Job Between ‘MANAGER’ AND ‘SALESMAN’; Sql>Select Ename,Sal,Job From Emp Where Job NOT Between "MANAGER’ AND ‘SALESMAN’; sql>Select Ename,Sal,Job,Hiredate From Emp Where Hiredate Between '17-FEB-1981' AND '20-JUN-1983'; sql>Select Ename,Sal,Job,Hiredate From Emp Where Hiredate NOT Between '17-FEB-1981' AND '20-JUN-1983'; |OT IN Operator: + The Operator is used to test for values in a specified list. «The Operator can be used upon any datatype. Sql>Select Ename,Sal,Job From Emp Where Ename IN(FORD'/SMITH’); Sql>Select Empno,Job,Sal From Emp Where Ename NOT IN(FORD‘/SMITH’); Sql>Select Ename,Sal,Deptno From Emp Where Deptno IN(20,30); Sql>Select Ename,Sal,Deptno From Emp Where Deptno NOT IN(2C -~); Sql>Select Ename,Sal,Deptno From Emp Where Hiredate IN('20-FEB-1981'/09-JUN-1981'); Sql>Select Euame,Sal,Deptna From ‘Emp Where. Hiredate NOT IN(‘20-FEB-1981'/09-JUN-1981 ; LIKE Operator: NOT LIKE Operator: Use the LIKE condition to perform wildcard . ‘The LIKE Operator searches of valid search string values. Search conditions can contain either literal characters or numbers. The available wild cards are % - Itis represent any sequence of Zero or more character. _>Represent any single character, only at that position only, +The Wild Card symbols can be used in any combination with literal « .sracter. + For finding exact match for ‘%’ and '-' the ESCAPE option has to be » sed, which is ‘Y' symbol with ESCAPE option. SqI>SELECT Empno,fname From Emp Where Ename LIKE 'M%'; Sql> SELECT Empno,Ename From Emp WHERE Ename NOT UKEM%; ‘Sql>SELECT Empno,Ename From Emp WHERE Ename LIKE '_O%'; ‘Sql> SELECT Empno,Ename From Emp WHERE Ename NOT LIKE *_0%'; Naresh i Technologies, Opp. Satyam Theatre, Ameerpet, Hyderabad, Ph: 23746666, 90994008 - 24-Oracle 11g Material Sql> SELECT Empno,Ename From Emp WHERE Ename LIKE 'SM%'; ‘Sql> SELECT Empno,Ename,Job From Emp WHERE Job LIKE ‘____'; Sql> SELECT Ename,Hiredate From Emp WHERE Hiredate LIKE '%-FEB-1981'; Sql> SELECT Ename,Hiredate From Emp WHERE Hiredate LIKE “%JAN%"; ‘Sql> Select *From Dept Where Dname LIKE '_-\_%' ESCAPE (update dept set dname='SO_FT_WARE’ where deptno=50;) IS NULL Oper: yT NULL Opera’ * The Operator tests for NULL values. + Its the only operator that can be used to test for NULL’s. « NULL value means the value is unav ailable, unassigned, unknown, or inapplicable, SqI>SELECT Ename,Deptne,Comm From Emp WHERE Comm IS NULL; SqI>SELECT Ename,Deptno,MgrJob From Emp WHERE Mgr IS NULL; Sql> SELECT Ename,Deptno,Comm From Emp WHERE Comm>=0; Sq!>SELECT Ename,Deptno,Comm From Emp WHERE Comm IS NOT NULL; Sql>SELECT Ename,Deptno,Comm From Emp WHERE Mgr IS NOT NULL; ORDER BY Claus + The Order of rows returned in a query result is undefined, The ORDER BY clause can be used to sort the rows, ‘The ORDER BY clause must be the last clause of the SQL statement. An expression, or an alias, or calumn position as the sort condition. Default ordering of Data is Ascending. "Number 1-999 **Dates Earlist-Latest **String A-Z;NULLS Last. + Syntax: SELECT expr FROM table (WHERE condition(s) } [ORDER BY {column, expr} [ASC|DESC)]; + The default order upon column is Ascending, to change the default ordering DESC should be used after the column name. + Sorting can be implemented on column aliases and can also be implemented upon multiple columns. Sql>Select Ename,Job,Sal,Deptno From Emp ORDER BY Sal; Sq!>Select Ename,Job,Sal,Deptno From Emp ORDER BY Sal DESC; Sql>Select Ename,Job,Sal,Deptno From Emp Where Job=‘CLERK’ ORDER BY Sal; Sql>Select Ename,Job,Sal,Deptno From Emp Where Sal>=2000 ORDER BY Deptno,Ename DESC; Naresh i Technologies, Opp. Satyam Theatre, Ameerpet, Hyderabad, Ph: 23746666, 9000994008 - 25-Oraclé 11g Material Sql>Select Ename,Job,Sal,Sal*42 Annsal From Emp ORDER BY AnnSal; Sql>Select Ename,Job,Sal, Deptno From Emp ORDER BY Deptno,Job,Sal ; Sql>Select Ename,Job,Sal,Deptno From Emp ORDER BY 2 DESC; Sql>Select * From Emp ORDER BY 5 DESC; The single row functions can appear In: **SELECT List **WHERE List *#Start With Clause **CONNECT BY Clause The types of single row functions are ** CHARACTER “NUMBER. **DATE _- **CONVERSION Multiple Row Functions: + These function manipulate group of rows to give one result per group of rows. They are used to manipulate data items, «© They accept one or mare arguments and return one value far each raw returned by the query. + An argument can be User-supplied constant © Variable value © Column name o Expression Syntax: Func_Name(Column/Expr, [Arg1,Arg2,...-]) Features of single-row functions include: ‘Acting on each row returned in the query. Returning one result per row. Returning a data value of a different type than that referenced, Expecting one of more arguments, Can be used in SELECT, WHERE, and ORDER BY clauses. Can be nested. Specification Behavior of Function; + Character functions: Accept character input and can return both character and . number values, + Number functions: Accept numeric input and return numeric values. + Date functions: Operate on values of the DATE data type (All date functions return a value of DATE data type except the MONTHS_BETWEEN function, which returns a number.) + Conversion functions: Convert a value from one data type to another. ‘General functions: o NVL o NVQ co NULLIF © COALSECE o CASE o DECODE Naresh i Technotogies, Opp. Satyam Theatre, Ameerpet, Hyderabad, Ph: 23746666, 90 994008 - 26-Oracle 11g Material —— Character Functions: + They return the data type VARCHAR2, limited to a length of 4000 Bytes. + If the return value length exceeds, then the return value is truncated, without an error. * Functions can be divided into Case-manipulation functions. © Character-manipulation functions. Character Function Purpose; LOWER(column|expression) * It converts alpha character values to lowercase . + The return value has the same data type as argument char type(CHAR or VARCHAR2) Syntax: LOWER(column | xpression) Sqi>Select LOWER( NARESH I TECHNOLOGIES ") From Dual; Sql>Select Ename,Job,LOWER(MY DATA’) From Emp; Sql>Select Ename,LOWER(Ename) From Emp Where Deptno=10; Sqi> Select The 'IiEname || "s Designation is ‘|| Job. from EmpWhere LOWER(Job)="manager’ pper Function: + Tt Converts the Alpha character values to Upper Case, + The return value has the same data type as the argument char. Syntax: UPPER(Column | Expression) Sq!> Select Upper{’Naresh’ i Technolagies ") From Dual; $q!>Select Ename,Job, Upper (‘My Data’) From Emp; Sql>Select Ename,Job, Upper (Ename), Upper (Job) From Emp Where Deptno=20; Sql> Select Ename,Job From Emp Where Job=Upper(*Manager’); Sql> Select Upper('E.F Codd’) "Capitalised" From Dual; Sql>Select ‘The "||Ename jj ""s Designation is '{| Lower(Job) From Emp Where Job=Upper(‘managet’} Order by Sal; Sql> Select Upper("The '||Ename|| ' Basic Salary is "||Sal) "Emp Salaries" ,Job From Emp Where Job In(Upper('Manager'),Upper(‘clerk’)) Order by Sal Desc; INSTCAP Function: © It returns a string with the first letter of each word in upper case, keeping all othered «letters in Lower case, Sql> Select initcap(Ename) From emp; Sal> Select Initcap(‘naresh i technologies ") From Dual; Sql>Select ‘The Job Title for'{{Inticap(Ename)||" is'|] Lower(Job) ‘Details From Emp; Sql>Select Ename,Upper(Ename), Lower(Ename),Initeap(Ename) From Emp; Sql>Select Empno,Initcap(Ename),Deptno From Emp — Where Ename=Upper(‘ford’); CONCAT Function: + It Concatenates the first characters value to the second character value. Only two parameters accept. + Itreturn the character data type, Naresh i Technologies, Opp. Satyam Theatre, Ameerpet, Hyderabad, Ph; 23746666, 9000994008 - 27Oracle 11g Material Syntax: CONCAT (Columni/Exp1, Column2/Exp2) ‘Sql>Select Concat(‘Oracle’,/Naresh Technologies’) From Emp; Sql>Select Ename,Job,Concat(Ename,Job) From Emp Where Deptno=: Sql> Select. Concat(Concat(Ename,Job),Sal) From Emp; Sql> Select Concat("The Employee Name is',Initcap(Ename)) As "Employee Names " From Emp Where Deptno in(10,20); Sql>Select Concat(Concat(Initcap(Ename),'is a’),Job) Job From Emp Where Deptno in(10,20); Sql>Select Concat(‘aFname’,’&Sname’) "Full Name” From Dual; 0; ‘SUB STRING Function: «Returns specified characters from character value, string from a specified position ‘m’ to 'n’ characters long. + To extract the portion of the string it is mainly used. Points to Remember. * If mis 0, it is treated as 1. * If mis positive, Oracle counts from the beginning of char to find the first character. If n is Omitted, Oracle returns all characters to the end of char. If nis less than. 1 or 0, A null is returned.Floating points numbers passed as arguments to substr are automatically converted to Integers. ‘Syntax: SUBSTR(Col/Exp,m,[n]) C>char to be searched. M> Starting position . N> Occurrence Number, Sql> Select Substr(‘SIVA RAMA KRISHNA’,1,4) From Dual; ‘Sql> Select Substr('SIVA RAMA KRISHNA’,6,4) From Dual; ‘Sqi>Select Substr('SIVA RAMA KRISHNA',11) From Dual; Sql>Select Substr('SIVA RAMA KRISHNA’,-7) From Dual; Sql> Select Substr('SIVA RAMA KRISHNA',-12,4) From Dual; Sql> Select Substr('SIVA RAMA KRISHNA',-12,4) From Dual; Sql> Select Ename,Job From Emp Where Substr(Job,6)=Upper(‘men'); Sql>SelectConcat(Initcap(Ename),Concat('is a ‘,Concat(Initcap(Substr(Job,1,3))," Eater.'))) From Emp Where Substr(Job,4,3)=Upper(’Age’); LENGTH Function: + Returns the number of characters in a value, + If the char has data type CHAR, the length includes all trailing blanks. * Ifthe char is NULL, if retrun NULL. Syntax: LENGTH(Column|Expression) Sql> Select Length(’E.F CODD’) From Dual; Sqi> Select Length(Ename)||' Characters exists in ‘|[Initcar’“name)||' "'s Name.'As "Names and Lengths " From Emp; Sql>Select Initcap(Ename),Job From Where Length(Ename)=5; Sal> Select Initcap(Ename),Job From Emp Where Substr(Job,4,Length(Substr(Job,4,3)))='AGE'; Naresh i Technologies, Opp. Satyam Theatre, Ameerpet, Hyderabad, Ph: 23746666," 994008 -28-‘ Oracle 11g Material Real Time flo INSTRING Function: + It returns the numeric position of a named character. ‘Syntax: INSTR(Column | Expression, ‘C’, [,m], [n] ) Searches for Column / Expression beginning with its ‘n’th character for the 'm’ th occurrences of character ‘C’, and return the position of the character , + ‘m’ can be positive or negative, if negative searches backward from the end of Column / Expression. + The value of 'n’ should be positive. * The default value of both ‘m’ and ‘n’ are 1. ‘+ If search is unsuccessful, the return value is zero. ‘Sql> Select Instr('SIVA RAMA KRISHNA’,'A',1,1) From Dual; Sql> Select Instr(’SIVA RAMA, KRISHNA’,‘A',17,2) From Dual; Sql> Select Instr('SIVA RAMA KRISHNA','MA',7,1) From Dual; Sql> Select Instr('SIVA RAMA KRISHNA’/'A',-1,1) From Dual; Sql> Select Instr(Job,'A’,1,2) From Emp Where Job="MANAGER’; Sql> Select Instr(3ob,'A',2) From Emp Where Job='MANAGER’ Sql> Select Instr(Job,'A’) From Emp Where Job="MANAGER'; LPAD Function: . + Pads the character value right-justified to a total width of n character positions. «The default padding character is space. Syntax: LPAD(Column | Expression, n, ‘C') «Fill extra spaces with char ‘C’ up to'n’ position on left side. Sql> Select Lpad('Page 1',20,'*) From Dual; Sql> Select Lpad('Page 1',20) From Dual; Sql> Select Lpad(Ename,20,'@") From Emp Where Deptno=10; RPAD Function Pads the character value left-justified to a total width of n character positions. ‘+ The default padding character is space. ‘Syntax: RPAD(Column | Expression, n, 'C’) Sql> Sal> Select Rpad('Page 1',20,"*") From Dual; Sq\> Select Rpad('Page 1',20) From Dual; Sql> Select Rpad(Ename,20,'@") From Emp Where Deptno=10 Sql>Select Ename,Lpad(Ename, 10),Rpad(Ename, 10, Deptno=10; Sql> Select Ename,Lpad(Rpad(Ename,10,'-"),15,'-') From Emp FromEmpWhere Real Time Scenario: . TRIM Function: * It enables to trim heading character from a character string. + All the left most characters that appear in the set are removed. Syntax: LTRIM(Char,Set) Sql> Select Ltrim(’xyxyORACLE 10g','xy') From Dual; Sql> Select Ltrim('MM KRISHNA','M') From Dual; RTRIM Function: + Itenables to trim heading character from a character string, + All the right mest characters that appear in the set are removed. —————————————————— Naresh i Technologies, Opp. Satyam Theatre, Ameerpet, Hyderabad, Ph: 23746666, 9000994008 - 29 -rach: 1g Material Syntax: RTRIM(Char,Set) Sai> Select Rtrim(‘ORACLE 10gxyxy','xy') From Dual; Sql> Select Rtrim(’KRISHNA AAA\,‘A’) From Dual; Sql> Select Rtrim(Job,ER'),Job From Emp Where Ltrim(Job,'MAN') Like ‘GER’; TRIM Function: + Trims heading or trailing characters (o* both) from a character string. + If trim_character or trim_source is a character literal, you must enclose it in single quotes. If Leading is specified concentrates on leading characters. If Trailing is specified concentrates on trailing characters. If Both of none is specified concentrates both on leading and trailing. Returns the varchar2 type. ‘Sql> Select Trim( 'S' From 'MITHSS') From Dual; ‘Sql> Select Trim( 'S' From 'SSMITH') From Dual; Sql> Select Trim( 'S' From 'SSMITHSS') From Dual; Sql> Select Trim(Leading 'S"From 'SSMITHSS') From Dual Sql> Select Trim(Trailing 'S' From 'SSMITHSS') From Dual; Sql> Select Trim(Both 'S' From 'SSMITHSS') From Dual; REPLACE Function: + Tt return the every Occurrence of search string replace by the replacement string. = If the replacement string is omitted or null, all occurrences of serch string are removed + If substitutes one string for another as well as to remove character strings. Syntax: REPLACE(text,Search_string, [Replacement_string]) Sql> Select Replace('Led',’L','R’) From Dual; Sql> Select Replace('Led’,'L!,'Ra') From Dual; . Sql> Select Replace('Led’,‘Le','R') From Dual; Sql> Select Ename,Replace(Job,’MAN’,'DAM') From EmpWhere Job='MANAGER'; Sql> Select Job,Replace(Job,’P") From Emp Where Job="PRESIDEN Sql> Select Job,Replace(Job,'MAN','EXECUTIVE') From EmpWhere SALESMAN" Real Time Scenario: TRANSLATE Functio: + Used to Translate Character by character in a String, Syntax: TRANSLATE(char,From ,To) + returns a char with all occurrences of each character in ‘From’ replaced by corresponding character in ‘To’. * Characters in char that are not in From are not replaced. + The argument From can contain more characters than To. «If the extra characters appear in Char, they are removed from the return value. ‘Sql> Select Translate(Job,'P’,' ') From Emp Where Job='PRESIDENT'; Sql> Select Transiate(Job,’MN','DM') From Emp Where Job="MANAGER'; Sql> Select Job, Translate(Job,’A’,'0") From Emp Where Job="SALESMA Sal> Select Transiate('Led’,'Le',R') From Dual; Real Time Scenario: CHR Functic Tt returns a character having the binary equivalent to ‘n’. + It returns the equivalent for ‘n’ in database character set or national character set. Naresh i Technologies, Opp. Satyam Theatre, Ameerpet, Hyderabad, Ph: 23746666, 9000994008 - 30-Oracle 11g Material Syntax: CHR(n) Sql>Select Chr(75)||Chr(82)||Chr(73)||Chr(83)] |Chr(72)| |Chr(78)||Chr(65) Name From Dual; ASCII Functi + Tt returns the decimal representation in the character database set of the first characters of the Char, Syntax: ASCII(Char) Sql> Select Ascii('A') From Dual; Sql> Select Enarhe,Ascii(Ename) From Emp; Sql> Select Ascii(‘&name') From Dual; NUMBER Function: + These function accept number input and return numeric values. + Many functions return values that are accurate to 38 decimal digits. ROUND Function: Systax: ROUND(m.n) It returns ‘m’ round to ‘n’ places right of the decimal point. + If'n’ omitted , n is rounded to 0, places + ‘n’can be negative , and rounds off the digits to the left of the decimal point. + ‘n’ must be an integer. Sql>Select 19.637 Num1ROUND(19.637,1) Rounded From Dual; Sql>Select 19.637 Num-,ROUND(19.637,-1) Rounded From Dual; Sql>Select 7843.637 Nurt_ROUND(7843.637,2) Rounded, ROUND(7843.637,-1) Rounded, ROUND(7843,637,-2)Rounded, ROUND(7843.637,-3)Rounded, ROUND(7843.637,-4)Rounded From Dual; TRUNCATE Fun: Systax:TRUNC(m,n) * It returns ‘m’ Truncated to ‘n‘ decimal places. + If'n’ omitted , n is truncated to 0 decimal places. « ‘n’ can be negative to truncate 'm’ digits left of the decimal point. Sql>Select 19.637 Num1,TRUNC(19.637,1) Truncated From Dual; Sql> Select 19.637 Num1-, TRUNC(19.637,-1) Truncated From Dual; Sql> Select 7843.637 Numi, TRUNC(7843,637, 2) Truncated, TRUNC(7843.637,-1) Truncated, TRUNC(7843.637,-2)Truncated, TRUNC(7843.637,-3)Truncated, “TRUNC(7843.637,-4) Truncated From Dual; CEIL Function: Syntax:CEIL(n) + Returns the Largest integer greater than or equal to 'n’. «The adjustment is done to the highest Nearest decimal value, ‘Sql>Selectgl9.001 Num1,CEIL(19.001) Ceiled From Dual; Sql> Select 19.34 Num1,CEIL(19.32) Celled ,CEIL(19.2) Celled ,CEIL(19) Ceiled From Dual; FLOOR Func! Syntax: FLOOR(n) + Returns the smallest integer less than or equal than ‘n’, «The adjustment is dane to the lowest Nearest decimal value. $q|>Select 19.001 Num1, FLOOR(19.999) Floor From Dual; Sal> Select 18.34 Num1,FLOOR(18.34) Floor,FLOOR(18.9) Floor,FLOOR(18) Floor From Dual; MODULUS Function: Syntax; MOD(m,n) ‘+ ItReturns remainder of ‘m' + Itreturns‘m’ ifn’ is 0. vided by ‘n’. Sql> Select MOD(100,10) Modulus, MOD(17,4) Modulus From Dual; —————————————— Naresh i Technologies, Opp. Satyam Theatre, Ameerpet, Hyderabad, Ph: 2374666, 9000994008 - 31 -Oracle 11g Material POWER Function: Syntax: POWER(m,n) «It Returns ‘m’ Raised to the ‘n’th power. The base ‘m’ and the exponent 'n’ can be any number. Sql> Select POWER(5,2) Power, POWER(-5,2)Power From Dual; Sql> Select POWER(S,-2) Power, POWER(-5,-2)Power From Dual; SQUARE Functi Syntax: SQRT(n) ‘+ It Returns Square Root of ‘n’ as Real Value. «The Value of 'n’ cannot be negative. Sql> Select SQRT(25) From Dual; ABSOLUTE Function: Syntax: ABS(n) + It Returns the Absolute value of ‘n’ Sql> Select ABS(-100) From Dual; Sql>Select Sal, Comm, Sal-Comm, ABS(Sal-Comm) FROM Emp; ‘SIGN Function: ‘Syntax: SIGN(n) + It Returns the SIGN, Specific o Ifn<0,returns -1 o Ifn=0,returns 0 o Ifn>0,returns 1 Sql> Select SIGN(-10), SIGN(10), SIGN(0) From Dual; Sql> Select Sal, Comm, SIGN(Sal-Comm), ABS(Sal-Comm) “ROM Emp Where SIGN(Sal-Comm Working With Dates: + Oracle stores dates in an internal numeric format. + The dates in Oracle range from January 1,4712 BC to Dr >mber 31,9999 AD. The default display and Input format for any date is DD-i. JN-YY. The numeric format represents **Century **Year —**Month = **Day_-—**Hours. **Minutes **Seconds SYSDATE: * Itis a date function that returns current date and time. + SYSDATE is generally selected upon a DUAL Table, Sql>Select SYSDATE From Dual; Date Arithmetic: + As database stores dates as numbers, it allows to perform <. ilations using arithmetic operators such as addition and subtraction. = We can perform the following operations....... o Date + Number Date Adds a number of days to a aor o Date-Number Date Subtracts a number of days’ a date. © Date- Date Number of days Subtracts one date nother. Date + Number/24 Date Adds a number of hours to uate. Sql>Select Sysdate From Dual; Sql>Select Sysdate,Sysdate+10 From Dual; Sql> Select Sysdate,Sysdate-+48/24 From Dual; Sql>Select Ename,Hiredate,Hiredate+10 From Emp; Sql>Select Ename,Hiredate,Hiredate-5 From Emp; Naresh i Technologies, Opp. Satyam Theatre, Ameerpet, Hyderabad, Phi: 2° "000994008 - 32-Oracle Ig Material eee Sql>Select Ename,Hiredate,Sysdate-Hiredate “ExofEmps" From Emp; Sql> Select Ename,Round((Sysdate-Hiredate)/7) Weeks From Emp; Sql>Select Empno, Hiredate, Round((Sysdate-Hiredate)/365) From Emp; DATE Function: ‘Add_ months Function: ‘Syntax: ADD_MONTHS(D, +(or)-N) + Adds ‘N’ number of calendar months to date. * The value of 'N’ must be an integer and can be negative. Sql> Select Sysdate, Add_months(Sysdate,1) From Dual; Sql> Select Ename, Sal, Hiredate, Add_months(Hiredate,1) From Emp Where Deptno=30; Months Between Functior Syntax: Months_between(D1,02) + It gives the different between dates D1 and D2 In months. « If D1 is later than D2, the result is Positive, else Negative. + IfD1 and D2 are either the same days of the months or both last days of the months, the result is always an integer. Sql>Select Ename, Hiredate, Round(Months_Between(Sysdate,Hiredate)/12) " Experience In Years" From Emp; Sql> Select Ename, Hiredate,Months_Between(Sysdate,Hiredate) From Emp Where Months_Between(Sysdate, Hiredate)<320, Next Day Fun: Syntax: Next_day (0, Char) "Tt returns the date of the first week day named by char, that is later than the data D. + The CHAR must be a day of the week in the sessions data language. + The day of the week can be full name or the abbreviation. Sql>Select Sysdate, Next_day(Sysdate,WED’) From Dual; Sql>Select Sal, Hiredate,Next_day(Hiredate, MONDAY’) From Emp; Last_Day Function: Syntax: Last_day(D) * It returns the date of the last day of the month that contains D. +. Mostly used to determine how many days are left in the current month. Sal> Select Sysdate, Last_day(Sysdate) Last, Last_day(Sysdate)-Sysdate Daysleft From Dual ; Real Time Scena Sql> Select Add_months(Last_day(Sysdate),-1)+1 From Dual; Rounding of Dates: Syntax: Round (Date,'Format’) = Returns Date rounded to the Unit specified by the format. = If format is omitted, Date is rounded to the nearest day. Sql> Select Round(Sysdate,'DAY’) From Dual; Sql> Select Round(Sysdate,'MONTH') From Dual; ‘Sql> Select Round(Sysdate, YEAR’) From Dual; Truncating Dates: ‘Synatx:Trunc(Date,'Format’) * Return Date with the time portion of the day truncated to the specified unit, «If format is omitted, data is truncated to the nearest day. Sql> Select Round(Sysdate,'DAY’), Trunc(Sysdate,'DAY') From Dual; Naresh i Technologies, Opp. Satyam Theatre, Ameerpet, Hyderabad, Ph: 23746666, 9000994008 -33-Oracle Lig Material aS Sql> Select Round(Sysdate,MONTH'), Trunc(Sysdate,'MONTH') From Dual; ‘Sql> Select Round(Sysdate,'YEAR'), Trunc(Sysdate,'YEAR') From Dual; Conversion Function: «The Conversation functions convert a value from one data type to another. + The Data type conversion in Oracle is. two types. -*Implicit_ Data type Conversion **Explicit Data type Conversion Implicit Data type Conversion: ‘+ Implicit Date type conversion work according to the convention specified by oracle. ‘+The Assignment succeeds if the Oracle server can convert the date type of value. CHAR to NUMBER conversion succeed only if the character string represent a valid NUMBER, + CHAR to DATES conversion succeed only if the character string represent the default format of DD-MON-YY. ignment Oy 7 © Varchar2/Char Number © Varchar2/Char >Date + Number Varchar2 * Date Varchar2 Explicit Data type Conversion: * SQl provided three function to convert a value from one data type to another. + The explicit conversation function are © TO_LCHAR > To Character Conversion. 0 TO_DATE — > To Date Conversion. © To_Number > To Number Conversion TO_CHAR Conversion: * This function can be used in two ways. © TO_CHAR(Number Conversion) © TO_CHAR(Date Conversion) TO _CHAR(Number Conversion) ‘Syntax: TO_CHAR(NUMBER,fmt) * Converts Number of Number data type to a value of VARCHAR2 data type. + ‘fmt’ is the optional number format, that can be used. O_CHAR(Date Conversion) ‘Syntax: TO_CHAR(DATE,fmt) * Converts Date of Date data type to a value of VARCHAR? data type in the format specified. “fmt! is the optional Date format, that can be used. al Indicator:D>99D99 It returns the specified position of the decimal character . The default decimal delimiter is period‘! Only one decimal indicator can be specified in a number format model. Deci Sql> Select 1234,TO_CHAR(1234,'9999D99") From Dual; Sql> Select 1234,TO_CHAR(1234,'999D99") From Dual; Scientific Notation Indicator:EEEE>9.9EEEE + Returns a Numeric value using scientific notation. Sql> Select TO_CHAR(5634,'9.9EEEE') From Dual; Group Separator: 6396999 ¢ Returns the specified position of the Group separator + Multiple Group separators can be specified. Sql> Select TO_CHAR(1234567,'99G99G9999") From Dual; Sql> Select Sal,TO_CHAR(Sal,'9G999') From Emp; Naresh i Technologies, Opp. Satyam Theatre, Ameerpet, Hyderabad, Ph: 23746666, 9994008 -34-Oracle 13x Materiat Local Currency Indicator: [1999 OR 9991. ‘+ Returns the specified position of the focal currency symbol. Sq/>Select 1234,TO_CHAR(1234,'L9999') From Dual; SqlSelect Sal,TO_CHAR(Sal,'(999999") Currency From Emp Where Deptno=10; ‘Sql> Select Sal,TO_CHAR(Sal,'L99G999D99",'NLS_CURRENCY=IndRupees’) Sal From Emp Where Deptno=20; Trailing Minus Indicator: MI>9999MI + Return negative value with a trailing mfnus sign’. + Returns positive value with a trailing Blank. + ‘MI’ Format shauld be declared as Trailing argument only. Sql?select -10000,TO_CHAR(-10000,'L99G999D99MI') From Dual ; Sq|> select Sal,Comm,Comm-Sal, TO_CHAR(Comm-Sal,'L99G999D99MI') From Empi Negative Number Indicator:PR>9999PR ‘Returns negative number in ‘<>’. + St Can appear only as trailing declaration. Sql select TO_CHAR(-1000,'L99G999D99FR’) From Dual; Sql> select Sal,Comm,Comm-Sal, 70_CHAR(Comm-Sal,'L9999PR’) From Emp; Roman Number Indicator: + RN-Returns Upper Roman Number. +m Returns Lower Roman Number. + The value can be an integer between 1 and 3999. ‘Sql> select 12,TO_CHAR(12,'RN'),TO_CHAR(12,"m') From Dual; Sign Indicator:S>S99999 OR 999995 + Returns Negative value with a ieading Minus Sign. + Returns Positive value with a leading Plus Sign, + Returns Negative value with Trailing Minus Sign. Returns Positive value with a Trailing Plus Sign, *S' can appear as First or Last value. Sql> select 1000,TO_CHAR(1000,'S9999'), TO_CHAR(-1000,'S9998") From Dual; Sql>select TO_CHAR(1000,'9999S'),T0_CHAR(-1000,'9999S") From Dual; Sql>select Sai,TO_CHAR(Sal,'S99999'),TO_CHAR(Sal,'99995') From Emp; Sqi>select Sal, Comm,TO_CHAR(Comm-Sal,'599999'), TO_CHAR(Comm-Sal,'9999S') From Emp; Hexadecimal Indicator: X>XXXX + Returns the Hexadecimal value of the specified number of Digits. + Ifthe Number is not an integer, oracle rounds it to an integer, + Accepts only positive values or 0. Sql>select 2000,TO_CHAR(2000,'XXXX') From Dual; Sql> Select Ename,Sal,TO_CHAR(Sal,'XXXX') Hexsal_ From Emp; Group Separator:>9,999 + Returns a comma in the specified position. + Multiple commas can be specified. Sql> select 20000,TO_CHAR(20000,'99,999.99') From Dual; Sql> Select Ename,Sal,TO_CHAR(Sal,'99,959.99') From Emp; Decimal Zacicator: 399.99 > Returns @ decimal point, at the specified position > Only one period can be specified in a number format model. Sql> Select 20000,TO_CHAR(20000,'L99,999,99") From Dual; Sql> Select Ename,Sal,TO_CHAR(Sal,'L99,999.99") From Emp; Naresh i Technologies, Opp. Satyam Theatre, Ameerpet, Hyderabad, Ph: 23746666, 9000994008 - 35 -Doll lar Indicator: $ >$9999 «Return value with a leading dollar sign. Sql> Select 20000,TO_CHAR(20000,'$99,999.99') From Dual; ‘Sql> Select Ename,Sal,TO_CHAR(Sal,'$99,999.99") From Emp; ‘Zero Indicator:0->0999 OR 9990 + Returns Lading OR Trailing Zeros. Sql>select 1000,TO_CHAR(1000,'0999999"),TO_CHAR(1000,'09999990') From Dual; Sql> select Ename,Sal,TO_CHAR(Sal,'$099,999.99") From Emp; Digit Place Marker: 939999 + Returns value with a specified number of digits with a Leading space when positive or Leading minus when Negative. ‘Sql> select 1000,600,TO_CHAR(1000-600,"29999"), TO_CHAR(600-1000,'99999') From Dual; Sql>select 20.25,20,TO_CHAR(20.55-20,'99999'), TO_CHAR(20.25-20,'99999') From Dual; ISQ Currency Indicator: C-9C3999 + Return specified position of the ISO Currency Symbol. ‘Sql>select 1000,TO_CHAR(1000,'C9999.99') Fram Dual; Sql>select Ename,Sal, TO_CHAR(Sal,'C9999.99") From Emp; Date Format Models: + The date format models can be used in the TO_CHAR function to translate a DATE value from original format to user format. Data Format “ements: ‘+ Aate format model is composed of one or more date format elements. ‘+ For input format models, format items cannot appear twice, and format items that represents similar information cannot be combined. * Capitalization in a spelled word, abbreviation, or roman numeral follows capitalization in the corresponding format element. * Punctuation such as Hyphens, Slashes, Commas, periads and Colons. AD or A.D./BC or B.C, Indicator: Indicator AD/BC with or without periods. ‘Sql> Select Sysdate,TO_CHAR(Sysdate,AD') From Dual; Sql>Select TO_CHAR(Sysdate,'B.C.’), TO_CHAR(Sysdate/A.D.’) From Dual; Sql>Select Ename,Sal,Hiredate, TO_CHAR(Hiredate/A.D.’) From Emp; Meridian Indicator: * It indicates meridian indicator with or without periods. Sql>Select Sydate,TO_CHAR(Sysdate,’A.M.’), TO_CHAR(Sysdate,PM" From Dual; ‘Sqi>Select Ename,Sal,Hiredate,TO_CHAR(Hiredate/AM’) From Emp Century Indicator: CC * Indicates the century. ‘Sql> Select Sydate,TO_CHAR(Sysdate/CC-AD') From Dual; a Naresh i Technologies, Opp. Satyam Theatre, Ameerpet, Hyderabad, Ph: 23746666, 90° 94008 -36-‘ Oracle 11g Material ——————— Numeric Week day indicator:03(1-7) * Returns the week day number. Sql>Select Sysdate,TO_CHAR(Sysdate/D’) From Dual; ‘Sql> Select Ename,Sal, Hiredate,TO_CHAR(Hiredate,D’) From Emp; Week day spelling indicator: Day. © Pads to a length of 9 characters. Sql> Select Sysdate,TO_CHAR(Sysdate,'DAY') From Dual; Sql> Select Ename,Hiredate,TO_CHAR(Hiredate,'DAN") Fram Emp Where ‘TO_CHAR(Hiredate,'DY')="MON'; Month day indicator:DD + It indicates the day of the Month(1-31) ‘Sql Select Sysdate,TO_CHAR(Sysdate,'DD-DAY') From Dual; Sql> Select Ename, Hiredate,TO_CHAR(Hiredate,'D0_DAY") From Emp; Sql> Select Ename,Hiredate,TO_CHAR(Hiredate,'DY') From Emp Where TO_CHAR(Hiredate, 'DD-DY")='28-MON'; Year day indicator:DDD + Itindicates the day of the Yaer(1-366) Sql> Select Sysdate, TO_CHAR(Sysdate,'DDD') From Dual; Sql> Select Ename, Hiredate,TO- CHAR(Hiredate,'DDD') From Emp; Sql> Select Ename,Hiredate,TO_CHAR(Hiredate,'DDD') From Emp Where TO_CHAR(Hiredate,'DY')='MON'; Abbreviated week day: DY + It indicates the day of the Yaer(1-366) Sql> Select Sysdate,TO_CHAR(Sysdate,'D-DY-DAY') From Dual; Sql> Select Ename,itiredate,TO_CHAR(Hiredate,'D-DY-DAY') From Emp Where Deptno in(10,20); ISO standard year week indicator: 1W + Specifies the week of the yaer(1-52 or 1-53) based on the ISO standard, Sql> Select Sysdate,1O_CHAR(Sysdate,"IW') From Dual; Sql>Select Ename,Hiredate,TO_CHAR(Hiredate,'IW’) From Emp; ISO standard 4 digit year indicator: IYYY + Specifies 4 digit year based on the ISO standard. + It can even be used in combination of IYY,1Y,I. Sql> Select Sysdate, TO_CHAR(Sysdate,'IYYY') From Dual; Sql>Select Ename,Hiredate,TO_CHAR(Hiredate,‘IYYY’) From Emp Where TO_CHAR(Hiredate,'DY")='MON'; Four Digit Year Indicator: YYYY or SYYY + Return four digit year,’S’ prefixes BC dates with '-'. + It can even be used in combination of YYY or YY or Y. + Y,YYY returns year with comma in that position, Sql>Select to_char(sysdate,'syyyy') from dual SqiSelect Hiredate,TO_CHAR(Hiredate,'YYYY'), TO_CHAR(Hiredate,'YYY') From Emp; Sql>Select TO_CHAR(Sysdate,"YYY"),TO_CHAR(Sysdate, YYY'), TO_CHAR(Sysdate,'YY') From Emy Spelled Year Indicator: YEAR or SYEAR + Returns the numerical year in spelling. Sql> Select SYSDATE, TO_CHAR(Sysdate,'Year'),TO_CHAR(Sysdate,"YEAR') From Dual; Sql>_ Select Empno,Ename,Miredate,TO_CHAR(Hiredate,'Year') From Emp; Naresh i Technologies, Opp. Satyam Theatre, Ameerpet, Hyderabad, Ph: 23746666, 9000994008 - 37-Oracle 11g Maieriat ——————————————— Week of the month indicator: W Specifies the week of the month(1-5). «Week starts on the first day of the month and ends on the seventh day. Sql> Select Sysdate,TO_CHAR(Sysdate,'W") From Dual; Sql> Select Empno,Ename,Hiredate,TO_CHAR(Hiredate,'W') From Emp; ‘Year week Indicator: WW + Specifies the week of the year(1-53) + Week 1 starts on the first day of the year and continues to the seventh day in that year. Sql> Select Sysdate,TO_CHAR(Sysdate,'WW') From Dual; Sql> Select Empno,Ename,Hiredate, TO_CHAR(Hiredate,, WW") From Emp; Quarter of the Year Indicator:Q ‘+ Returns the Quarter if the year. © Quarter starting with the month of Janvary and ending with every three months. Sql> Select Sysdate,TO_CHAR(Sysdate,'Q') From Dual; Sql> Select Empno,Ename,Hiredate, TO_CHAR(Hiredate,'Q') From Emp Where O_CHAR(Hiredate,'Q)=3;, Julian Day indicato¢: 3 ‘+ Returns the JULIAN Day of the given date, + This the number of day since January 1,4712 BC. + Number specified with ‘J’ must be integers. Sql> Select Sysdate,TO_CHAR(Sysdate,'J') From Dual; Sql> Select Empna,Ename,Hiredate,TO_CHAR(Hiredate,"J-DDD-DD-D') From Emp; Number Month Indicator:MM ‘Returns the numeric abbreviation of the month. Sql> Select Sysdate,TO_CHAR(Sysdate,'MM-YYYY') From Dual; ‘SqI> Select Ename,TO_CHAR(Hiredate,'DD-MM-YYYY') From Emp Where TO_CHAR(Hiredate,'MM‘)=12; Abbreviated Month Indicator:MON « Returns the abbreviated naie of the Month. Sql> Select Sysdate,TO_CHAR(Sysdate,'MON') From Dual; Sql> Select Hiredate,TO_CHAR(Sysdate,'MONTH') From Emp; ‘Twelve Hour Clock Mode: HH or HH12 ‘© Itis default clock mode. © Returns the hour of the day in twelve hour clock mode. Sql> Select Sysdate,TO_CHAR(Sysdate,'HH"), TO_CHAR(Sysdate,'HH12,PM") From Dual; Sql> Select Ename,Hiredate, TO_CHAR(Hiredate,'HH12:PM') From Em ‘Twenty Hour Clock Mode:HH24 «Returns the hour of the day in twenty four hour clock mode.(0-23) Sql> Select Sysdate,TO_CHAR(Sysdate,HH24") From Dual; RealTime Scenario: Minutes Indicator: MI Returns the minutes from the given date(0-53). Naresh i Technologies, Opp. Satyam Theatre, Ameerpet, Hyderabad, Pl: 23746666, 900994008 38 -

You might also like

• Oracle Madhu Sir

  

  No ratings yet

  Oracle Madhu Sir

  352 pages
• SSIS Practical Training Course

  

  No ratings yet

  SSIS Practical Training Course

  4 pages
• Oracle 10g Material Final

  

  No ratings yet

  Oracle 10g Material Final

  145 pages
• NareshIT Oracle

  

  100% (1)

  NareshIT Oracle

  140 pages
• PL/SQL Exception Handling Guide

  

  No ratings yet

  PL/SQL Exception Handling Guide

  107 pages
• Oracle Server Basics & Architecture

  

  No ratings yet

  Oracle Server Basics & Architecture

  154 pages
• PL SQL

  

  No ratings yet

  PL SQL

  37 pages
• Oracle 10g - SQL (Fast N Final)

  

  No ratings yet

  Oracle 10g - SQL (Fast N Final)

  73 pages
• SQL Oracle11g Notes

  

  No ratings yet

  SQL Oracle11g Notes

  125 pages
• SQL

  

  100% (11)

  SQL

  18 pages
• Oracle

  

  100% (1)

  Oracle

  236 pages
• Introduction To ORACLE

  

  No ratings yet

  Introduction To ORACLE

  9 pages
• By Ram Reddy by Ram Reddy: #209, Nilagiri Block, Adithya Enclave, Ameerpet, HYD @8801408841, 8790998182

  

  No ratings yet

  By Ram Reddy by Ram Reddy: #209, Nilagiri Block, Adithya Enclave, Ameerpet, HYD @8801408841, 8790998182

  255 pages
• Unix Linux Training Course Best Unix Linux Training Institute Hyderabad India Nareshit PDF Free

  

  No ratings yet

  Unix Linux Training Course Best Unix Linux Training Institute Hyderabad India Nareshit PDF Free

  5 pages
• Data Structures: CRT Technical by Naresh I Technologies

  

  No ratings yet

  Data Structures: CRT Technical by Naresh I Technologies

  7 pages
• Informatica 9.x Course Curriculum

  

  No ratings yet

  Informatica 9.x Course Curriculum

  8 pages
• Informatica, DAC & OBIA Training Notes

  

  0% (1)

  Informatica, DAC & OBIA Training Notes

  96 pages
• PHP Naresh Technologies PDF

  

  No ratings yet

  PHP Naresh Technologies PDF

  258 pages
• Oracle Joins

  

  No ratings yet

  Oracle Joins

  11 pages
• Oracle SQL Notes

  

  No ratings yet

  Oracle SQL Notes

  66 pages
• PLSQL Project

  

  No ratings yet

  PLSQL Project

  23 pages
• PL SQL For Advance Learners

  

  No ratings yet

  PL SQL For Advance Learners

  4 pages
• T-SQL Interview Questions and Answers

  

  No ratings yet

  T-SQL Interview Questions and Answers

  3 pages
• SQL PL SQL Content

  

  No ratings yet

  SQL PL SQL Content

  4 pages
• SQL Developer Resume

  

  No ratings yet

  SQL Developer Resume

  2 pages
• Oracle SQL and PL/SQL Basics

  

  No ratings yet

  Oracle SQL and PL/SQL Basics

  22 pages
• Stored Procedures in PL/SQL

  

  No ratings yet

  Stored Procedures in PL/SQL

  22 pages
• SQL Narayana Reddy

  

  100% (1)

  SQL Narayana Reddy

  124 pages
• BOOK-Deva-Oracle Material

  

  No ratings yet

  BOOK-Deva-Oracle Material

  177 pages
• Oracle Database Guide 2020

  

  No ratings yet

  Oracle Database Guide 2020

  168 pages
• PL/SQL Cursors and Data Types Guide

  

  No ratings yet

  PL/SQL Cursors and Data Types Guide

  35 pages
• Difference Between DBMS and RDBMS

  

  No ratings yet

  Difference Between DBMS and RDBMS

  16 pages
• Oracle SQL Syllabus

  

  No ratings yet

  Oracle SQL Syllabus

  9 pages
• Madhu - Software - Sparks - SQL - PLSQL Guide

  

  No ratings yet

  Madhu - Software - Sparks - SQL - PLSQL Guide

  215 pages
• New Obiee 11g

  

  No ratings yet

  New Obiee 11g

  287 pages
• Oracle SQL and PL SQL Notes

  

  No ratings yet

  Oracle SQL and PL SQL Notes

  438 pages
• Y.Ramesh Kumar: Tata Consultancy Services

  

  No ratings yet

  Y.Ramesh Kumar: Tata Consultancy Services

  4 pages
• PL SQL Developer

  

  No ratings yet

  PL SQL Developer

  3 pages
• SQL Interview Q&A Guide

  

  No ratings yet

  SQL Interview Q&A Guide

  29 pages
• Oracle PL/SQL Interview Q&A Guide

  

  No ratings yet

  Oracle PL/SQL Interview Q&A Guide

  16 pages
• SQL Notes by Topic

  

  No ratings yet

  SQL Notes by Topic

  134 pages
• PLSQL

  

  No ratings yet

  PLSQL

  196 pages
• Chapter 1: Top 50 Technical and Non-Technical Questions: See Our Other Step by Step Video Series Below

  

  No ratings yet

  Chapter 1: Top 50 Technical and Non-Technical Questions: See Our Other Step by Step Video Series Below

  104 pages
• Oracle 11c Krishna Reddy Notes

  

  No ratings yet

  Oracle 11c Krishna Reddy Notes

  246 pages
• Lab Manual

  

  No ratings yet

  Lab Manual

  162 pages
• CSE II-II SEM (DBMS Lab Manual) PDF

  

  100% (1)

  CSE II-II SEM (DBMS Lab Manual) PDF

  124 pages
• DBMS English

  

  No ratings yet

  DBMS English

  4 pages
• Database Basics for Beginners

  

  No ratings yet

  Database Basics for Beginners

  16 pages
• Dbms Manual

  

  No ratings yet

  Dbms Manual

  144 pages
• Database Learning for Students

  

  No ratings yet

  Database Learning for Students

  161 pages
• Dbms Lab Manual

  

  No ratings yet

  Dbms Lab Manual

  144 pages
• Unit-I: Database Concepts: A Relational Approach: Database - Relationships - DBMS

  

  No ratings yet

  Unit-I: Database Concepts: A Relational Approach: Database - Relationships - DBMS

  15 pages
• RDBMS Overview and Oracle Tools

  

  No ratings yet

  RDBMS Overview and Oracle Tools

  89 pages
• Cse II-dbms Lab Manual

  

  No ratings yet

  Cse II-dbms Lab Manual

  162 pages
• DBMS Manual

  

  No ratings yet

  DBMS Manual

  56 pages
• BScComputerScience RDBMS&ORACLE

  

  No ratings yet

  BScComputerScience RDBMS&ORACLE

  245 pages
• Dbms Lab Manuval

  

  No ratings yet

  Dbms Lab Manuval

  148 pages
• Oracle Order Management Overview

  

  No ratings yet

  Oracle Order Management Overview

  305 pages
• HND Computing UNIT 38: Database Management: Faisal Saghir

  

  No ratings yet

  HND Computing UNIT 38: Database Management: Faisal Saghir

  49 pages
• Classification of DBMS

  

  No ratings yet

  Classification of DBMS

  50 pages
• NLP Presentation

  

  No ratings yet

  NLP Presentation

  23 pages
• NLP Presentation

  

  No ratings yet

  NLP Presentation

  23 pages
• AIDI 1003 Presentation

  

  No ratings yet

  AIDI 1003 Presentation

  9 pages
• Call For Applications Adjunct Faculty Member Positions October 24 Ver1

  

  No ratings yet

  Call For Applications Adjunct Faculty Member Positions October 24 Ver1

  3 pages
• Solution

  

  No ratings yet

  Solution

  3 pages
• Content: Unit 1

  

  No ratings yet

  Content: Unit 1

  219 pages