ORACLE Features

1. ORACLE Features A feature is a set of functionality that enhances the use of Oracle database. They do not extend the capabilities of Oracle Server, rather, they use them. 1.Scalability and Performance Concurrency Read Consistency Locking Mechanisms Portability 2. Manageability Self managing database SQL*Plus Scheduler Resource Manager

2. 3. Backup and Recovery 4. High availability 5. Business Inteligence Data Warehousing

3. History of SQL: • Dr. E. F. Codd published the paper, "A Relational Model of Data for Large Shared Data Banks", in June 1970 in the Association of Computer Machinery (ACM) journal, Communications of the ACM. Codd’s model is now accepted as the definitive model for relational database management systems (RDBMS). • The language, Structured English Query Language ("SEQUEL") was developed by IBM Corporation, Inc., to use Codd’s model. SEQUEL later became SQL (still pronounced "sequel"). In 1979, Relational Software, Inc. (now Oracle Corporation) introduced the first commercially available implementation of SQL. Today, SQL is accepted as the standard RDBMS language.

4. Oracle Data Types: • Each value manipulated by Oracle has a datatype. • A value’s datatype associates a fixed set of properties with the value. These properties cause Oracle to treat values of one datatype differently from values of another. For example, you can add values of NUMBER datatype, but not values of RAW datatype. • Oracle data types are categorised as follows. • Oracle_built_in_datatypes • ANSI_supported_datatypes 3. user_defined_types 4. Oracle_supplied_types

5. Oracle Built-in Datatypes : 1. character_datatypes 2. number_datatypes 3. long_and_raw_datatypes 4. datetime_datatypes 5. large_object_datatypes 6. rowid_datatypes 1. Character_datatypes: Character datatypes store character (alphanumeric) data, which are words and free-form text, in the database character set or national character set. They are less restrictive than other datatypes and consequently have fewer properties. 1. CHAR Datatype 2. NCHAR Datatype 3. NVARCHAR2 Datatype 4. VARCHAR2 Datatype

6. 1.1 CHAR Datatype: i) The CHAR datatype specifies a fixed-length character string. ii) Oracle subsequently ensures that all values stored in that column have the length specified by size. iii) If you insert a value that is shorter than the column length, Oracle blank-pads the value to column length. If you try to insert a value that is too long for the column, Oracle returns an error. iv) The default length for a CHAR column is 1 byte and the maximum allowed is 2000 bytes. A 1-byte string can be inserted into a CHAR(10) column, but the string is blank-padded to 10 bytes before it is stored. v) When you create a table with a CHAR column, by default you supply the column length in bytes. The BYTE qualifier is the same as the default. vi) If you use the CHAR qualifier, for example CHAR(10 CHAR), you supply the column length in characters. A character is technically a codepoint of the database character set. Its size can range from 1 byte to bytes, dependingon the database character set.

7. 1.2 NCHAR Datatype: i)Beginning with Oracle9i, the NCHAR datatype is redefined to be a Unicode-only datatype. When you create a table with an NCHAR column, you define the column length in characters. You define the national character set when you create your database. ii) The column’s maximum length is determined by the national character set definition. Width specifications of character datatype NCHAR refer to the number of characters. The maximum column size allowed is 2000 bytes.

8. 1.3 NVARCHAR2 Datatype i)Beginning with Oracle9i, the NVARCHAR2 datatype is redefined to be a Unicode-only datatype. When you create a table with an NVARCHAR2 column, you supply the maximum number of characters it can hold. Oracle subsequently stores each value in the column exactly as you specify it, provided the value does not exceed the column’s maximum length. ii) The column’s maximum length is determined by the national character set definition. Width specifications of character datatype NVARCHAR2 refer to the number of characters. The maximum column size allowed is 4000 bytes. 1.4 VARCHAR2 Datatype • The VARCHAR2 datatype specifies a variable-length character string. When you create a VARCHAR2 column, you supply the maximum number of bytes or characters of data that it can hold. Oracle subsequently stores each value in the column exactly as you specify it, provided the value does not exceed the column’s maximum length. If you try to insert a value that exceeds the specified length, Oracle returns an error.

9. 1.5 VARCHAR Datatype: • The VARCHAR datatype is currently synonymous with the VARCHAR2 datatype. Oracle recommends that you use VARCHAR2 rather than VARCHAR. In the future, VARCHAR might be defined as a separate datatype used for variable-length character strings compared with different comparison semantics 2. Number_datatypes: The NUMBER datatype stores zero, positive, and negative fixed and floating-point numbers with magnitudes between 1.0 x 10-130 and 9.9...9 x 10125 (38 nines followed by 88 zeroes) with 38 digits of precision. If you specify an arithmetic expression whose value has a magnitude greater than or equal to 1.0 x 10126, Oracle returns an error. Specify a fixed-point number using the following form: NUMBER(p,s) where: p is the precision, or the total number of digits. Oracle guarantees the portability of numbers with precision ranging from 1 to 38. s is the scale, or the number of digits to the right of the decimal point. The scale

10. 3. long_and_raw_datatypes: LONG columns store variable-length character strings containing up to 2 gigabytes, or 231-1 bytes. LONG columns have many of the characteristics of VARCHAR2 columns. You can use LONG columns to store long text strings. The length of LONG values may be limited by the memory available on your computer 4. Datetime and Interval Datatypes: The datetime datatypes are DATE, TIMESTAMP, TIMESTAMP WITH TIME ZONE and TIMESTAMP WITH LOCAL TIME ZONE. Values of datetime datatypes are sometimes called "datetimes". The interval datatypes are INTERVAL YEAR TO MONTH and INTERVAL DAY TO SECOND. Values of interval datatypes are sometimes called "intervals". Both datetimes and intervals are made up of fields. The values of these fields determine the value of the datatype. The table that follows lists the datetime fields and their possible values for datetimes and intervals

11. DATE Datatype: The DATE datatype stores date and time information. Although date and time information can be represented in both character and number datatypes, the DATE datatype has special associated properties. For each DATE value, Oracle stores information: century, year, month, date, hour, minute, and second. • TIMESTAMP Datatype: The TIMESTAMP datatype is an extension of the DATE datatype. It stores the year, month, and day of the DATE datatype, plus hour, minute, and second values. Specify the TIMESTAMP datatype as follows: TIMESTAMP [ (fractional_seconds_precision)] where fractional_seconds_precision optionally specifies the number of digits in the fractional part of the SECOND datetime field and can be number in the range 0 to 9. The default is 6. For example, you specify TIMESTAMP as a literal as follows: TIMESTAMP’1997-01-31 09:26:50.124’

12. TIMESTAMP (precision) WITH TIME ZONE – A fixed-sized value of 13 bytes, which represents a date/time value along with a time zone setting. There are two ways one can set the time zone. The first is by using the UTC offset, say ‘+10:0’, or secondly by the region name, say ‘Australia/Sydney’. • TIMESTAMP (precision) WITH LOCAL TIME – A variable value ranging from 7 to 11 bytes. This particular datatype is similar to the TIMESTAMP WITH TIME ZONE datatype. The difference is that the data is normalised to the database time zone when stored. The entry is manipulated to concur with the client’s time zone when retrieved.

13. In order to communicate with the database , SQL supports 4 categories of commands. 1. Data Definition Language [DDL] : 2. Data Manipulation Language [DML] : 3. Transaction Control Language [TCL] : 4. Data Control Language [DCL] :

14. Data Definition Language [DDL] : DDL is used for following purposes. 1. to create an object 2. to alter the structure of an object 3. to drop the object DDL provides following commands. 1. create table 2. alter table 3. truncate table 4. drop table

15. Create table Command: Syntax: create table <tablename> (column definition1, column definition2, …….); Column definition is, column name data type (size) Example: create table student(rno number(3), sname varchar2(10), course varchar2(10)); After successful execution of the query the msg displayed will be- “table created”

16. Naming Conventions For The Table Name: • first letter should be an alphabate 2. reserve words cannot be used 3. maximum length of table name is 30 characters 4. table name should be unique 5. underscore, numerals and letters are allowed but not blank spaces and single quotes.

17. Alter Table: This command is used to – • change structure of the table • modify existing column (to change data type or size of data type) • Add new column 4. To drop integrity constraints Syntax: Alter table tablename modify (column definition, ……..); Alter table tablename add (column definition, ……..); Example: Alter table student add (fees number(5,2)); Alter table student modify (rno number(5));

18. Truncate Table: To retain structure of the table. Syntax: truncate table <table name>; Example: truncate table student;

19. Drop Table: To drop the structure of the table. Syntax: drop table <tablename>; Example: drop table student;

20. Desc command: To view structure of the table we use this command. desc student;

21. Data Manipulation Language [DML] : DML is used for following purposes. 1. to manipulate existing objects 2. to query the object DML provides following commands. 1. insert 2. select 3. update 4. delete

22. Insert Command: To add one or more records in the table. insert into <table name> values (a list of values); While using this command, 1. values should be seperated by commas 2. varchar, varchar2, long, row, date data types should be enclosed in single quotes. 3. values must be entered in the same order as they are defined in the table. Inserting Multiple Records with Single “Insert Command”: insert into student values(&rno, ‘&sname’, ‘&course’);

23. Note: 1. To skip a few fields in a record the necessary ones have to be explicitly specified in the insert table. insert into <table name (column names)> values (a list of values); 2. Another way of skipping one or more fields is to enter null against that column values. 3. Inserting date values in a table is similar to inserting character values (enclosing single quotes). Standard format for date data type is ‘dd-mon-yy’. 4. If user wants to enter date into another format then the specialized conversion function will have to be used.

24. Viewing Data In The Tables: • To view All Rows And All Columns: select * from tablename; Note: 1. Oracle allows the user to use meta character(*), which is expanded by Oracle as All Columns. 2. It will compile the sentense, executes it, and retrieves data for all columns or rows from the table. select * from student;

25. Filtering Table Data: • The ways of filtering table data will be, 1. selected columns and all rows 2. selected rows and all columns 3. selected rows and selected columns 1. selected columns and all rows: select columnname1, ……columnname n from tablename; Select rno, sname from student;

26. 2. selected rows and all columns: 1. If we want to retrieve particular record from the table, its retrieval must be based on a specific condition. 2. Oracle provides use of ‘where clause’ in an SQL statement to apply a filter on the rows. 3. When ‘where clause’ is added to SQL statement, the Oracle server compares each record from the table with the condition specified in the where clause. 4. Oracle displays only those records that satisfy the specific condition. • Syntax: select * from tablename where search condition; select * from student where sname=‘Akash’; Note: We can use all standard operators in the search condition.

27. 3. selected rows and selected columns: To view a specific data set from the table with selected columns the syntax is, select columnname, columnname from tablename where search condition; select rno, sname from student where fees < 45000;

28. Elimination Of Duplicates From Select Statement: A table could hold a duplicate records. To see only unique roworacle provides use of ‘unique’ keyword. Select distinct columnname From tablename; It will scans through the values of the columns specified and displays unique values from amongst them. select distinct jobs from emp;

29. Delete Operation: It isused to remove one or more rows from the table. 1. Removal Of All Rows: delete from tablename; 2. Removal Of Selected Rows: delete from tablename where search condition; • Updating Contents Of A Table: It is used to change or modify data values in a table. • Updating Of All Rows: update tablename set columnname=expr, columnname=expr; 2. Updating Of Selected Rows: update tablename set columnname=expr, columnname=expr where search condition;

30. Renaming Table: rename oldname to newtablename; rename student to studentInfo; • Destroing Tables: drop table tablename;

31. Operators In SQL Plus: There are 3 different types of operators supported by SQL Plus. 1. Arithmetic Operators 2. Comparison Operators 3. Logical Operators • Arithmetic Operators: i) Oracle allow us to use these operators while viewing table records or while manipulating data. ii) To perform operations based on number values these operators are used. select sname, fees*0.05 from student; select sname, fees*0.05 new_fees from student;

32. Logical Operators: i) They can be used in SQL sentence. ii) They are AND, OR, NOT. • Comparison Operators: i) These operators are used in search condition to compare one expression with another. ii) They are =, !=, <,>, <=, >=, between, in, like, null iii) between, in, like, null are also used to check not condition.

33. Range Searching: [Between Operator]: • To select data from range of values between operator is used. • It allows selection of rows within specific limit. (upper and lower) • The both coded limits are inclusive. • The lower value should be coded first and two values should be linked by ‘And’ keyword. • Between can be used character and number data type but not a mixture of both. select sname, course from student where rno between 40 and 50; vi) We can also use NOT with Between. select sname, course from student where rno not between 40 and 50;

34. Pattern Matching: [Like, In]: • Like allow comparison of one string value with another string value which is not identical. • This is achieved by using wild cards- % : matches any string _ : matches any single character. select * from student where sname like ‘sa%’; select * from student where sname like ‘_a%’ or sname like ‘_r%’;

35. In Operator: i) It is used to check single value against multiple values. select sname, course from student where sname in (‘ram’, ‘geeta’, ‘ashok’) select sname, course from student where sname not in (‘ram’, ‘geeta’, ‘ashok’)

36. Operator Precedence: Arithmetic comparison not and or

37. Inserting Data Using Table: i) Insertion Of Data Row: insert into tablename select columnname, columnname from tablename; ii) Insertion Of Data Set: insert into tablename select columnname, columnname from tablename where search condition;