contents · 2020. 10. 9. · 4 | p a g e this can be accomplished through a combination of the...

1 | P a g e

Contents

UNIT : 1: CODD'S LAW : ....................................................................................................................... 2

Explain Dr. E. F. Codd’s Rules for RDBMS........................................................................... 2

UNIT : 2 : SQL (STRUCTURE QUUERY LANGUUAGE) ............................................................... 7

Introduction To Structure Query Language(SQL) .................................................................... 7

Features of SQL ................................................................................................................................... 7

Rules for SQL ....................................................................................................................................... 7

SQL Delimiters ..................................................................................................................................... 8

2.1 ORACLE DATA-TYPE. ............................................................................................................ 8

2.2 ORACLE DDL,DML,DCL,DQL. ............................................................................................ 10

2.3 RANGE SEARCHING AND PATTERN MATCHING .......................................................... 12

2.4 ORACLE FUNCTION ................................................................................................................. 16

2.5: Manipulation Date ................................................................................................................... 31

2.6 :Sub-Query .................................................................................................................................. 33

2.7:Joins .............................................................................................................................................. 34

2.8 Using The Union, Intersect and Minus Clause: ............................................................... 35

ADVANCED SQL ....................................................................................................................................... 68

2.7: Indexes ......................................................................................................................................... 68

2.10:Views ........................................................................................................................................... 74

2.11 : Sequences: .............................................................................................................................. 79

2.12 : Granting And Revoking Permissions .............................................................................. 81

UNIT : 3: PL\SQL (PROCEDURAL \ PROGRAMMING LANGUAGE) .................................... 88

3.1 PL\SQL BLOCK STATEMENT ............................................................................................... 88

2 | P a g e

3.1.1 USING VARIABLES, CONSTANT AND DATATYPE .................................................. 89

3.1.4 CONTROL STATEMENT ................................................................................................... 89

The GOTO Statement: ................................................................................................................. 90

change the flow of control within a PL/SQL block. ........................................................... 90

Syntax: .............................................................................................................................................. 90

GOTO ; .......................................................................................................... 90

3.2 CURSOR (EXPLICIT , IMPLICIT) ........................................................................................... 98

UNIT : 1: CODD'S LAW :

Explain Dr. E. F. Codd’s Rules for RDBMS

Dr. E. F. Codd is an IBM researcher who first developed the relational data model in 1970. In 1985, Dr. Codd published a list of 12 rules that define ideal relational database and has provided a guideline for the design of all relational database systems. Dr. Codd has used the term guideline because till date no commercial relational database system fully conforms to all 12 rules. For a few years, scorecards were kept that rated each commercial product’s conformity to Codd’s rules. Today, the rules are not talked about as much but remain a goal for relational database design. Rule 1: The Information Rule:

3 | P a g e

All data should be presented in table form. Means CLIENT_MASTER table represent following: TABLE: CLIENT_MASTER COMMAND: CREATE TABLE CLIENT_MASTER ( CLIENTNO VARCHAR2(6) PRIMARY KEY, NAME VARCHAR2(20) NOT NULL, ADDRESS1 VARCHAR2(30), ADDRESS2 VARCHAR2(30), CITY VARCHAR2(15), PINCODE NUMBER(8), STATE VARCHAR2(15), BALDUE NUMBER(10,2) ); STRUCTURE: Name Null? Type ---------------------------- -------- ---- CLIENTNO NOT NULL VARCHAR2(6) NAME NOT NULL VARCHAR2(20) ADDRESS1 VARCHAR2(30) ADDRESS2 VARCHAR2(30) CITY VARCHAR2(15) PINCODE NUMBER(8) STATE VARCHAR2(15) BALDUE NUMBER(10,2) Rule 2: Guaranteed Access Rules: All data should be accessible without ambiguity.

4 | P a g e

This can be accomplished through a combination of the table name, Primary Key, and column name. EXAMPLE: Emp_info Emp_Salary_info EmpId Name EmpId Salary 91 John 91 20000 92 Thomas 92 40000 OR Each and every data is guaranteed to be logically accessible by resorting to a combination of table name, primary key value and column name. Rule 3: Systematic Treatment of Null Values:

A field should be allowed to remain empty. This involves the support of a null value, which is distinct from an empty string or number with a value of zero. Of course, this can’t apply to primary key. In addition, most database implementations support the concept of a not- null field constraint that prevents null values in a specific table column. EXAMPLE: Emp Id Name Phone No Mobile No 09BCA01 John 2326864 9725142752 09BCA02 Thomas 2555631 - NULL Values are supported in the fully relational DBMS for representing missing information in a systematic way independent of data type. NULL values are distinct from empty character string or a string of blank character and distinct from 0 or any other number. Rule 4: Dynamic On-Line Catalog based on the Relational Model: A relational database must provide access to its structure through the same tools that are used to access the data. EXAMPLE: This is usually accomplished by storing the structure definition within special system tables. Rule 5: Comprehensive Data Sublanguage Rule: The Relational database must support at least one clearly defined language that includes functionality for data definition, data manipulation, data integrity and database transaction control. Data Definition View Definition Data Manipulation

5 | P a g e

Integrate Constraints Authorization Transaction Control All commercial relational database use forms of standard SQL (i.e. Structure Query Language) as their supported comprehensive language. Rule 6: View Updating Rule: Data can be presented in different logical combination called views. Each view should support the same full range of data manipulation that has direct access to a table available. In practice, providing update and delete access to logical view is difficult and not fully supported by any current database. EXAMPLE: All views those are theoretically updatable by the system. This rule is not really implemented yet any available. Rule 7: High –Level Insert, Update, and Delete: Data can be retrieved from a relation database in sets constructed of data from multiple rows and/or multiple tables. This rule states that insert, update, and delete operations should be supported for any retrievable set rather than just for a single row in a single table. EXAMPLE: Suppose if we need to change ID then it will reflect everywhere automatic. Rule 8: Physical Data independence:

The user is isolated from the physical method of storing and retrieving information from the database. Changes can be made to the underlying architecture (hardware, disk storage methods) without affecting how the user accesses it. EXAMPLE: The user is isolated (Separated) from the physical method of storing and retrieving information from the database in which affecting directly in database. Rule 9: Logical Data Independence:

6 | P a g e

How data is viewed should not be changed when the logical structure (table’s structure) of the database changes. This rule is particularly difficult to satisfy. Most databases rely on strong ties between the data viewed and the actual structure of the underlying tables. EXAMPLE: In this rule we want to retrieve any ID, when we retrieve Data without ID that time we can not satisfy this rule. Rule 10: Integrity Independence: The database language (like SQL) should support constraints on user input that maintain database integrity. This rule is not fully implemented by most major vendors. At a minimum, all databases do preserve two constraints through SQL. No component of a primary key can have a null value. If a foreign key is defined in one table, any value in it must exist as a primary key in another table. All databases do preserved to constrain through SQL. Primary Key can not have NULL. Foreign Key is define in one table any value in it must exists as a primary key in another table. Integrity constraints specific to a particular relation database must be definable in the relational data sub-language & storable in a catalog not in the application program. Rule 11: Distribution independence: A user should be totally unaware of whether or not the database is distributed (whether parts of database exist in multiple locations) make this rule difficult to implement. A variety of reasons make this rule difficult to implement. EXAMPLE: This rule difficult to implement due to variety of reasons. Rule 12: Non subversion Rule: There should be no way to modify the database structure other than through the multiple row database language (like SQL). Most databases today support administrative tools that allow some direct

7 | P a g e

manipulation of the data structure. EXAMPLE: If a relational system supports a low level (Single record at a time) language that low level language can not be used to sub word or by pass the integrity rules or constraints expressed in the higher level (multiple record at a time) relational language.

UNIT : 2 : SQL (STRUCTURE QUUERY LANGUUAGE)

Introduction To Structure Query Language(SQL)

Structure Query Language (SQL) is language that provides an interface to relational database systems. SQL was developed by IBM in the 1970s for use in System R, and is a de facto standard, as well as an ISO and ANSI standard. SQL is often pronounced SEQUEL and SEQUEL means Structure English QUEry Language.

Features of SQL

SQL can be used by a range of users, including those with little or no programming experience. It is a non procedural language. It reduces the amount of time required for creating and maintaining systems. It is an English-like language.

Rules for SQL

SQL starts with a verb(i.e. a SQL action word). EXAMPLE: SELECT statements. This verb may have additional adjectives. EXAMPLE: FROM

8 | P a g e

Each verb is followed by number of clauses. EXAMPLE: FROM, WHERE, HAVING A space separates clause. EXAMPLE: DROP TABLE EMP; A Comma (,) separates parameters without a clause. A ‘;’ is used to end SQL statements. Statements may be split across lines but keywords may not. Lexical units such as identifiers, operator names, literals are separated by one or more spaces or other delimiters that will not be confused with the lexical unit. Reserved words cannot be used as identifiers unless enclosed with double quotes. Reserved words are: AS, BY, CREATE, SELECT, VARCHAR, BETWEEN etc. Identifiers can contain up to 30 characters and must start with an alphabetic character. Character and date literals must be enclosed within single quotes. Numeric literals can be represented by simple values such as 0.32,-34,01991, and so on, scientific notation as 2E5 meaning 2x10 to power of 5=200,000. Comments may be enclosed between /* and */ symbols and may be multi line. Single line comments may be prefixed with a – symbol.

SQL Delimiters

Delimiters are symbol or compound symbols, which have a special meaning within SQL and PL/SQL statements. + Addition “ Quote identifier - Subtraction : Host Variable * Multiplication ** Exponential / Division != ^= Relational => < Relational = Relational () Expression or list := Assignment ; Terminator => Association % Attribute indicator || Concatenation , Item separator > Label @ Remote access indicator -- Comment ‘ Character string delimiter /* */ Comment (Multi-line)

2.1 ORACLE DATA-TYPE.

The Data Types:-

The various data types, recognized by the oracle engine and

permitted to be used for creating an oracle table column are defined below:

CHAR (size) :

9 | P a g e

This data type is used to store character string values of

fixed length.The size in brackets determines the number of characters the

cell can hold. The maximum number of characters this data type can hold is

255 characters.

ORACLE compares CHAR values using blank-padded comparison

semantics. E.g. if a value that is inserted in a cell of CHAR data type is

shorter that the size it is defined for then it will be padded with spaces on

the right until it reaches the size characters in length.

VARCHAR (size) / VARCHAR2 (size) :

This data type is used to store variable length alphanumeric

data.The maximum this data type can hold is 2000 characters.

Note:- One difference between this data type and CHAR data type is

VARCHAR values using non-padded comparison semantics. Inserted values

will not be padded with spaces.

NUMBER (P, S) :

The NUMBER data type is used to store numbers.The precision (P)

determine the maximum length of the data, whereas the scale (S) determine the

number of places to the right of the decimal.If scale is omitted then the default

is zero, if the precision is omitted values are stored with their original precision

up to maximum.

DATE :

This data type is used to represent date and time.The standard format is

DD-MON-YY as in 7-DEC-09 To enter dates other than the standard format

use the appropriate functions. Date Time stores date in the 24-hr format.

By default, the time in a date field is 12:00:00 am, if no time portion is

specified. The default date for a date filed is the first day of the current month.

LONG :

This data type is used to store variable length character strings

10 | P a g e

containing up to 2GB. LONG values cannot be indexed, and the normal

character functions such as SUBSTR cannot be applied to LONG values.

RAW/ LONG RAW :

The RAW / LONG RAW data type can be used store binary data, such as

digitized picture or image. Raw data type ca have maximum length of 255

bytes. LONG RAW data type can contain up to 2GB Values stored in columns

having LONG RAW data type cannot be indexed.

2.2 ORACLE DDL,DML,DCL,DQL.

Components of SQL DDL(Data Definition Language) : It is a set of SQL commands used to create, modify and delete database structures but not data. These commands are normally not used by a general user, who should be accessing the database via an application. They are normally used by the DBA to a limited extent, a database designer or application developer. DDL must have the CREATE object privilege and a Tablespace area in which to create objects. DML(Data Manipulation Language) : It is the area of SQL that allows changing data within the database. DCL(Data Control Language) : It is the component of SQL statement that control access to data and to the database. Occasionally DCL statements are grouped with DML statements. DQL(Data Query Language) : It is the component of SQL statement that allows getting data from the database and imposing ordering upon it. In includes the SELECT statement.

11 | P a g e

This command is the heart of SQL. It allows getting the data out of the database perform operations with it. When a SELECT is fired against a table or tables the result is compiled into a further temporary table, which is displayed or perhaps received by the program i.e. a front-end. Examples of DDL, DML and DCL commands DDL: Data Definition Language statements Examples: CREATE To create objects in the database ALTER Alters the structure of the database DROP Delete objects from the database TRUNCATE Remove all records from a table, including all spaces allocated for the records are removed COMMENT Add comments to the data dictionary GRANT Gives user’s access privileges to database REVOKE Withdraw access privileges given with the GRANT Command DML: Data Manipulation Language statements Examples: INSERT Insert data into a table UPDATE Updates existing data within a table DELETE DELETE Deletes all records from a table, the space for the records remain CALL Call a PL/SQL or JAVA subprogram EXPLAINPLAN Explain access path to data LOCK TABLE Control concurrency DCL: Data Control Language statements Examples:

12 | P a g e

COMMIT Save work done SAVEPOINT Identify a point in a transaction to which you can later roll back ROLLBACK Restore database to original since the last COMMIT SET TRANSACTION Change transaction options like what rollback segment to use GRANT / REVOKE EGrant or take back permissions to or from the oracle users. DQL: Data Query Language statements Examples: SELECT Retrieve data from the a database

2.3 RANGE SEARCHING AND PATTERN MATCHING

1) Range Searching: In order to select data that is within a range of values, the BETWEEN operator is used. The BETWEEN operator allows the selection of rows that contain values within a specified lower and upper limit. The range coded after the word BETWEEN is inclusive. The lower values must be coded first. The two values in between the range must be linked with the keyword AND. The BETWEEN operator can be used with both character and numeric data types. However, the data types can not be mixed. Example: The lower value of a range of values from a character column and the other from a numeric column. Synopsis: Tables: TRANS_MSTR Columns: All columns Technique: Functions:

13 | P a g e

TO_CHAR(), Operators: BETWEEN, Clauses: WHERE Solution: SELECT * FROM TRANS_MSTR WHERE TO_CHAR (DT,’MM’) BETWEEN 01 AND 03; Equivalent to: SELECT * FROM TRANS_MSTR WHERE TO_CHAR (DT.’MM’) >= 01 AND TO_CHAR (DT,’MM’)

14 | P a g e

Tables: TRANS_MSTR Columns: ACCT_NO Technique: Functions: TO_CHAR(), Operators: BETWEEN, Clauses: WHERE Solution: SELECT DISTINCT FROM TRANS_MSTR WHERE TO_CHAR (DT,’MM’) NOT BETWEEN 01 AND 04; Output: ACCT_NO ----------------- SB9

2) Pattern Matching: The use of the LIKE predicate The comparison operators discussed so far have compared one value, exactly to one other value. Such precision may not always be desired necessary. For this purpose oracle provides the LIKE predicate. The LIKE predicate allows comparison of one string value with another string value, which is not identical. This achieved by using wildcard characters. Two characters that are available:

For character data types: % allows to match any string of any length (include zero length) _ allows matching on a single character Example: List the customer whose being with the letters ‘Ch’

Synopsis: Tables: CUST_MSTR Columns: FNAME,LNAME,DOB_INC Technique: Operators: BETWEEN, Clauses: WHERE, Others: ALIAS

Solution: SELECT FNAME, LNAME, DOB_INC “BIRTHDATE”, OCCUP FROM CUST_MSTR WHERE FNAME LIKE ‘CH%’;

15 | P a g e

Output: FNAME LNAME Birthday OCCUP Chriselle Bayross 29-OCT-82 Service Chhaya Bankar 06-OCT-76 Service

Explanation: In the above examples, all those records where the values held in the FNAME beings with Ch are displayed. The % indicates that any number of characters can follow the letters Ch.

The IN and NOT IN predicates: The arithmetic operator (=) compares a single value to another single value. In case a value needs to be compared to a list of values then the IN predicate is used. The IN predicates helps reduce the need to use multiple OR conditions. Example: List the customer details of the customers named Hansel, Mamta, Namita and Aruna.

Synopsis: Tables: CUST_MSTR Columns: FNAME,LNAME,DOB_INC Technique: Operators: IN, Clauses: WHERE, Others: ALIAS

Solution: SELECT FNAME, LNAME, DOB_INC “birthday”, OCCUP FROM CUST_MSTR WHERE FNAME IN (‘Hansel’,.Mamta’,Namita’,’Aruna’);

Output: FNAME LNAME Birthday OCCUP ------------------------------------------------------------------ Mamta Muzumdar 28-AUG-75 Service Hansel Colaco 01-JAN-82 Service Namita Kanade 10-JUN-78 Self Employed

Explanation: The above example, displays all those records where the FNAME filed holds any one of the four specified Values.

16 | P a g e

2.4 ORACLE FUNCTION

ORACLE FUNCTIONS Oracle functions serve the purpose of manipulating data items and returning a result. Functions are also capable of accepting user-supplied variables or constants and operating on them. Variables or constants are called arguments.

1) Function Name (argument1, argument2,…) Oracle Function can be clubbed together depending upon whether they operate on a single row or a group of rows retrieved from a table.

2) Group Functions (Aggregate Functions) Functions that act on a set of a value are called Group Functions. For Example, SUM, is a function, which calculates the total set of number. A group functions return as a single result row for a group of queried rows.

3) Scalar Functions (Single Row Functions) Functions that act on only one value at a time are called Scalar Functions.For example, LENGTH is a function, which calculates the length of one particular string value. A single row function returns one result for every row of a queried table or view.

AGGREGATE FUNCTIONS

1) AVG: Returns an average value of ‘n’, ignoring null values in a column. SYNTAX: AVG ([ | ] ) EXAMPLE: SELECT AVG (CURBAL)”Average Balance” FROM ACCT_MSTR; OUTPUT: Average Balance ------------------------ 1100

17 | P a g e

2) MIN: Returns a minimum value of expr. SYNTAX: MIN ([ | ] ) EXAMPLE: SELECT MIN(CURBAL)”Minimum Balance” FROM ACCT_MSTR; OUTPUT: Minimum Balance ------------------------ 500 3) COUNT(expr): Returns the number of rows where expr is not null. SYNTAX: COUNT ([ | ] ) EXAMPLE: SELECT COUNT(ACCT_NO)”No. of Accounts” FROM ACCT_MSTR; OUTPUT: No. of Accounts ------------------------ 10 4) COUNT (*): Returns the no of rows in the table, including duplicates and those with nulls. SYNTAX: COUNT(*) EXAMPLE: SELECT COUNT(*)”No. of Records” FROM ACCT_MSTR; OUTPUT: No. of Records ------------------------ 10 5) MAX: Return the maximum value of expr.

SYNTAX: MAX ([ | ] ) EXAMPLE: SELECT MAX(CURBAL)”Maximum Balance” FROM ACCT_MSTR;

18 | P a g e

OUTPUT: Maximum Balance

----------------------- 2000

6) SUM: Return the sum of the value of ‘n’. SYNTAX: SUM ([ | ] ) EXAMPLE: SELECT SUM(CURBAL)”Total Balance” FROM ACCT_MSTR; OUTPUT: Total Balance ------------------------ 11000

NUMERIC FUNCTIONS

1) ABS: Returns the absolute value of ‘n’. SYNTAX: ABS(n) EXAMPLE: SELECT ABS(-15)”Absolute” FROM DUAL; OUTPUT: Absolute ------------------ 15

2) POWER: Returns m raised to the nth power. N must be an integer, else an error is returned. SYNTAX: POWER(m, n) EXAMPLE: SELECT POWER(3,2)”Raised” FROM DUAL; OUTPUT: Raised -------------- 9 3) ROUND:

19 | P a g e

Returns n, rounded to m places to the right of a decimal point, if m is omitted, n is rounded to 0 places. m can be negative to round off digits to the left of the decimal point. m must be an integer. SYNTAX: ROUND(n[,m]) EXAMPLE: SELECT ROUND(15.19,1)”Round” FROM DUAL; OUTPUT: Round -------------- 15.2

4) SQRT: Returns square root of n, If n

20 | P a g e

SYNTAX: EXTRACT( {year | month | day | hour | minute | second | timezone_hour | timezone_minute | timezone_region | timezone_abbr} FROM { date_value | interval-value }) EXAMPLE: SELECT EXTRACT(YEAR FROM DATE ‘2004-07-02’) “Year”, EXTRACTMONTH FROM SYSDATE) “Month” FROM DUAL; OUTPUT: Year Month ----------------------- 2002 7

7) GREATEST: Returns the greatest value in a list of expressions. SYNTAX: GREATEST(expr1, expr2, . . . . expr_n) Where, expr1, expr2, . . . . expr_n are expression that are evaluated by the greatest function. EXAMPLE: SELECT GREATEST(4,5,17)”Num”, GREATEST(‘4’,’5’,’17’) “Text” FROM DUAL; OUTPUT: Num Text ------------------- 5

8) LEAST: Returns the least value in a list of expressions. SYNTAX: LEAST(expr1, expr2, . . . . expr_n) where, expr1, expr2, . . . .expr_n are expression that are evaluated by the least function. EXAMPLE:

21 | P a g e

SELECT LEAST(4,5,17)”Num”, LEAST(‘4’,’5’,’17’) “Text” FROM DUAL; OUTPUT: Num Text ------------------- 17

9) MOD: Returns the remainder of a first number divided by second number passed a parameter. If the second number bis zero, the result is the same as the first number. SYNTAX: MOD(m, n) EXAMPLE: SELECT MOD(15,7)”Mod1”,MOD(15.5,7)”Mod2” FROM DUAL; OUTPUT: Mod1 Mod2 ---------------------- 1 1.7 10) TRUNC: Returns a number truncated to a certain number of decimal places. The decimal place value must be a integer. If this parameter is omitted, the TRUNC function will truncate the number to 0 decimal places. SYNTAX: TRUNC(number, [decimal_places]) EXAMPLE: SELECT TRUNC(125.815,1)”Trunc1”,TRUNC(125.815,- 2)”Trunc2” FROM DUAL; OUTPUT: Trunc1 Trunc2 ---------------------- 100

11) FLOOR: Returns the largest integer value that is equal to or less than a number. SYNTAX: FLOOR(n) EXAMPLE:

22 | P a g e

SELECT FLOOR(24.8)”Flr1”, FLOOR(13.15’) “Flr2” FROM DUAL; OUTPUT: Flr1 Flr2 ------------------- 13

12) CEIL: Returns the smallest integer value that is greater than or equal to a number.. SYNTAX: CEIL(n) EXAMPLE: SELECT CEIL(24.8)”Ceil1”, CEIL(13.15’) “Ceil2” FROM DUAL; OUTPUT: Ceil1 Ceil2 ------------------- 14

STRING FUNCTION 1) LOWER: Returns char, with all letters in lowercase. SYNTAX: LOWER (char) EXAMPLE: SELECT LOWER (‘IVAN BAYROSS’)”Lower” FROM DUAL; OUTPUT: Lower ------------------- ivan bayross 2) INITCAP: Returns a string with the first letter of each word in upper case. SYNTAX: INITCAP(char) EXAMPLE: SELECT INITCAP(‘IVAN BAYROSS’)”Title Case” FROM DUAL; OUTPUT:

23 | P a g e

Title Case ------------------- Ivan Bayross 3) UPPER: Returns char, with all letters forced to uppercase. SYNTAX: UPPER(char) EXAMPLE: SELECT UPPER(‘Ms. Carol’)”capitalised” FROM DUAL; OUTPUT: Capitalised ------------------- MS. CAROL 4) SUBSTR: Returns a portion of characters, beginning at character m, and going up to character n. if n is omitted, the result returned is upto the last character in the string. The first position of char is 1. SYNTAX: SUBSTR(, , []) where, string is the source string. start_position is the position for extraction. The first position in the string is always 1. length is the number of characters to extract. EXAMPLE: SELECT SUBSTR(‘SECURE’,3,4’)”Substring” FROM DUAL; OUTPUT: Substring ------------------- CURE

ASCII: Returns the NUMBER code that represents the specified character. If more than one character is entered, the function will return the value for the first character and ignore all of the characters after the first. SYNTAX: ASCII() where, single_character is the specified character to retrive the NUMBER code for, EXAMPLE: SELECT ASCII(‘a’)”ASCII1”, ASCII(‘A’)”ASCII2” FROM DUAL;

24 | P a g e

OUTPUT: ASCII1 ASCII2 ---------------------------- 65 5) COMPOSE: Returns a Unicode string. It can be a char, varchar2, nchar, nvarchar2, clob, or clob. SYNTAX: COMPOSE() Below is a listing of unistring values that can be combined with other characters in the compose function. Unistring Value Resulting character UNISTR(‘\0300’) grave accent (‘) UNISTR(‘\0301’) acute accent (‘) UNISTR(‘\0302’) circumflex (^) UNISTR(‘\0303’) tilde (~) UNISTR(‘\0308’) umlaut (“) EXAMPLE: SELECT COMPOSE(‘a’ || UNISTR(‘\0301’)) “Composed” FROM DUAL; OUTPUT: Composed ------------------ a 6) DECOMPOSE: Accepts a string and returns a Unicode string. SYNTAX: DECOMPOSE() EXAMPLE: SELECT DECOMPOSE(COMPOSE(‘a’ || UNISTR(‘\0301’))) “Decomposed” FROM DUAL; OUTPUT: Decomposed ------------------- ‘a ‘ 7) INSTR: Returns the location of a substring in a string. SYNTAX: INSTR(, , [], []) where, string1 is the string to search, string2 is the substring to search for in string 1.

25 | P a g e

start_position is the position in string 1 where the search will start. were nth_position is the nth appearance of string2. if omitted, it defaults to 1. EXAMPLE: SELECT INSTR(‘SCT on the net’,’t’) “Instr1”, INSTR(‘SCT on the net’,’t’,1,2) “Instr2” FROM DUAL; OUTPUT: Instr1 Instr2 --------------------- 14 8) TRANSLATE: Replaces a sequence of characters in a string with another set of characters. However, it replaces a single character at a time. SYNTAX: INITCAP(char) EXAMPLE: SELECT TRANSLATE(‘1sct523’,’123’,’7a9’) “Change” FROM DUAL; OUTPUT: Change ------------------- 7sct5a9 9) LENGTH: Returns the length of a word. SYNTAX: LENGTH(word) EXAMPLE: SELECT LENGTH(‘PIYUSH’)”Length” FROM DUAL; OUTPUT: Length ---------------- 6 10) LTRIM: Removes characters from the left of char with initial characters removed upto the first character not in set. SYNTAX: LTRIM(char[,set]) EXAMPLE: SELECT LTRIM(‘PIYUSH’,’P’)”LTRIM” FROM DUAL; OUTPUT:

26 | P a g e

LTRIM --------------- IYUSH 11) RTRIM: Returns char, with final characters removed after the last character not in the set. ‘set’ is optional, it defaults to spaces. SYNTAX: RTRIM(char,[set]) EXAMPLE: SELECT RTRIM(‘PIYUSH’,’H’) ”RTRIM” FROM DUAL; OUTPUT: RTRIM --------------- PIYUS 12) TRIM: removes all specified characters either from the beginning or the ending of a string. SYNTAX: RTRIM(char,[set]) EXAMPLE: SELECT RTRIM(‘PIYUSH’,’H’) ”RTRIM” FROM DUAL; OUTPUT: RTRIM --------------- PIYUS

13) RTRIM: Returns char, with final characters removed after the last character not in the set. ‘set’ is optional, it defaults to spaces. SYNTAX: TRIM( [leading | trailing | both [ FROM ]] ) where, leading – remove trim_string from the front of string1. trailing – remove trim_string from the end of string1. both – remove trim_string from the front and end of string1. EXAMPLE: SELECT TRIM(‘ PIYUSH ’) ”Trim both sides” FROM DUAL; OUTPUT: Trim both sides ---------------------

27 | P a g e

PIYUSH 14) LPAD: Returns char1, left-padded to length n with the sequence of characters specified in char2. if char2 is not specified Oracle uses blanks by default. SYNTAX: LPAD(char1,n [,char2]) EXAMPLE: SELECT LPAD(‘Page 1’,10,’*’) ”LPAD” FROM DUAL; OUTPUT: LPAD --------------- ****Page1 15) RPAD: Returns char1, right-padded to length n with the sequence of characters specified in char2. if char2 is not specified Oracle uses blanks by default. SYNTAX: RPAD(char1,n [,char2]) EXAMPLE: SELECT RPAD(‘FNAME’,10,’x’) ”RPAD” FROM DUAL; where FNAME is = PIYUSH OUTPUT: RPAD ----------------- PIYUSHxxxx 16) VSIZE: Returns the number of bytes in the internal representation of an expression. SYNTAX: VSIZE() EXAMPLE: SELECT VSIZE(‘SCT on the net’) ”Size” FROM DUAL; OUTPUT: Size ------------ 14 CONVERSION FUNCTIONS TO_NUMBER: Converts char, a CHARACTER value expressing a number, to a

28 | P a g e

NUMBER datatype. SYNTAX: TO_NUMBER(char) EXAMPLE: UPDATE ACCT_MSTR SET Curbal = Curbal + TO_NUMBER(SUBSTR(‘$100’,2,3)); OUTPUT: 10 rows updated TO_CHAR: (number conversion): Converts a value of a NUMBER datatype to a character datatype, using the optional format string. TO_CHAR() accepts a number (n) and a numeric format (fmt) in which the number has to appear. If fmt is omitted, n is converted to a char value exactly long enough to hold all significant digits. SYNTAX: TO_CHAR(n[,fmt]) EXAMPLE: SELECT TO_CHAR(17145,’4099,999’) “Char” FROM DUAL; OUTPUT: Char -------------- $017,145 TO_CHAR: (date conversion): Converts a value of a DATE datatype to a CHAR value, TO_CHAR) accepts a date, as well as the format (fmt) in which the date has to appear. Fmt must be a date format. If fmt is omitted, the date is converteharacter value using the default date format, i.e. “DD-MON-YY”. SYNTAX: TO_CHAR(date[,fmt]) EXAMPLE: SELECT TO_CHAR(DT,’Month DD, YYYY’) “New Date Format” FROM Trans_Mstr where Trans_No = ‘T1’; OUTPUT: New Date Format --------------------------- January 05, 2003 DATE CONVERSION FUNCTIONS TO_DATE: Converts a characters field to a date field.

29 | P a g e

SYNTAX: TO_DATE(char [,fmt]) EXAMPLE: INSERT INTO CUST_MSTR(CUST_NO, FNAME, MNAME, LNAME, DOB_INC) VALUES(‘C1’,’Ivan’,Nelson’,’bayross’, TO_DATE(’25-JUN-1952 10:55 A.M.’,’DD-MON-YY HH:MI A.M.’)); OUTPUT: 1 rows created. 1) ADD_MONTHS: returns date after adding the number of months specified in the function. SYNTAX: ADD_MONTHS(d,n) EXAMPLE: SELECT ADD_MONTHS(SYSDATE,4) “Add Months” FROM DUAL; OUTPUT: Add Months -------------------- 01-NOV-04 2) LAST_DAY: returns the last date of the month specified with the function. SYNTAX: LAST_DAY(d) EXAMPLE: SELECT SYSDATE, LAST_DAY(SYSDATE) “LastDay” FROM DUAL; OUTPUT: SYSDATE LastDay --------------------------------- 01-JUL-04 31-JUL-04 3) MONTH_BETWEEN: Returns number of months between d1 and d2. SYNTAX: MONTHS_BETWEEN(d1, d2) EXAMPLE: SELECT MONTHS_BETWEEN(’02-FEB-92’,’02-JAN-92’) “Months” FROM DUAL;

30 | P a g e

OUTPUT: Months -------------- 1 4) NEXT_DAY: Returns the date of the first weekday named bby char that is after the date named by date char must be a ady of the week. SYNTAX: NEXT_DATE(date, char) EXAMPLE: SELECT NEXT_DAY(’06-JULY-02’,’Saturday’) “NEXT DAY” FROM DUAL; OUTPUT: NEXT DAY ------------------- 13-JULY-02 5) ROUND: Returns the date rounded to a specified unit of measure. If the second parameter is omitted, the ROOUND function will round the date to the nearest day. SYNTAX: ROUND(date, char) EXAMPLE: SELECT ROUND(TO_DATE(’01-JUL-04’0,’YYYY’) “Year” FROM DUAL; OUTPUT: Year ------------------- 01-JAN-05 6) NEW_TIME: Returns the date after converting if from time zone1 to a date in time zone2. SYNTAX: NEW_TIME(date, zone1, zone2) EXAMPLE: SELECT NEW_TIME(TO_DATE(’2004/07/01 01:45’,’yyyy/mm/dd HH24:MI’),’AST’,’MST’) “MST” FROM DUAL; OUTPUT: MST

31 | P a g e

------------------- 30-JUN-04

2.5: Manipulation Date

• Manipulating Dates in SQL

If a ‘date’ has to be retrieved or inserted into a table

in a format other than the default one, oracle

provides the TO_CHAR and TO_DATE functions to

do this.

TO_CHAR

The TO_CHAR function the retrival of data in a

format different from the default format.

Syntax:

TO_CHAR (date value [,fmt])

Example:

TO_CHAR(‘3-jan-10’,’DD/MM/YY’)

It can also extract a part of the date, i.e the date,

month, or the year from the date value and use it for

sorting or grouping of data according to the date,

month, or year

32 | P a g e

Special Date Formats Using The TO_CHAR Function

Sometimes, the date value is required to be display in special

format like 12th of january,2010. For this oracle provides with special

alphabets, which can be used in the format specified with the TO_CHAR

function.

1.Use of TH in the to_char function

SELECT order_no, to_char(order_date,’DDth-MON-YY’)

FROM sales_order;

2. Use of SP in to_char function

Example:

SELECT order_no, client_no, to_char(order_date,’dd/mm/yy’)

FROM sales_order

ORDER BY to_char (order_date,’MM’);

TO_DATE

TO_DATE converts a char value into date value. It allows a user to insert date into a date column in any required format, by specifying the character value of the date to be inserted and its format.

Syntax:

TO_DATE(char value [,fmt])

where ‘char value’ stands for the value to be inserted in the date column, and ‘fmt’ is a date format in which the ‘char value’ is specified.

Example:

TO_DATE (’03/01/10’,’DD/MM/YY’)

33 | P a g e

‘DDSP’ indicates that the date(DD) must be displayed by spelling

the date such as ONE, TWELVE etc.

SELECT order_no, to_char(order_date, ‘DDSP’)

FROM sales_order;

3. Use of ‘SPTH’ in the to_char function

‘SPTH’ displays the date(DD) with ‘th’ added to the spelling

fourteenth, twelfth.

SELECT order_no, to_char(order_date, ‘DDSPTH’)

FROM sales_order;

2.6 :Sub-Query

• Subqueries:

A subquery is a form of an SQL statement that appears inside

another SQL statement.

It is also termed as a nested query.

The statement containing a subquery is called a parent statement.

The parent statement uses the rows returned by the subquery.

For Example:

1. SELECT * FROM sales_order

WHERE client_no = (SELECT client_no FROM client_master

WHERE name = ‘xyz’);

34 | P a g e

2. Find out all the products that are not being sold from the

product_master table, based on the products ac tually sold

as shown in the sales_order_datails table.

SELECT product_no, description

FROM product_master WHERE product_ no NOT IN

(SELECT product_no FROM sales_order_details);

2.7:Joins

Joining Multiple Tables (Equi Joins):

Sometimes we require to treat multiple tables as though they were

a single entity. Then a single SQL sentence can manipulate data from all the

tables.

To achieve this, we have to join tables. Tables are joined on

columns that have the same data type and data width in the tables.

Example:

SELECT order_no, name, to_char(order_date,’DD/MM/YY’) “Order

Date” FROM sales_order,client_master

WHERE client_master.client_no=sales_order.client_no

ORDER BY to_char(order_date,’DD/MM/YY’);

Joining A Table to Itself (self Joins):

35 | P a g e

In some situations, you may find it necessary to join a table to

itself, this is referred to as a self-join.

In a self-join, two rows from the same table combine to form a

result row.

To join a table to itself, two copies of the very same table have to be

opened in memory. Hence in the FROM clause, the table name needs to be

mentioned twice.

Since the table names are the same, the second table will overwrite

the first table and in effect, result in only one table being in memory. This is

because a table name is translated into a specific memory location.

To avoid this, each table is opened under an alias. Now these table

aliases will cause two identical tables to be opened in different memory

locations.

This will result in two identical tables to be physically present in

the computer’s memory.

Using the table alias names these two identical tables can be

joined.

FROM tablename [alias1], tablename [alias2] …..

2.8 Using The Union, Intersect and Minus Clause:

Union Clause:

Multiple queries can be put together and their output combined

using the union clause.

The union clause merges the output of two or more queries into a

single set of rows and columns.

Example:

SELECT salesman_no “ID”, name

FROM salesman_master

36 | P a g e

WHERE city=“mumbai”

UNION

SELECT client_no “ID”, name

FROM client_master

WHERE city=“mumbai”;

The Restrictions On Using a Union are as Follows:

--- Number of columns in all the queries should be the same.

--- The datatype of the columns in each query must be same.

--- Unions cannot be used in subqueries.

--- Aggregate functions cannot be used with union clause.

Intersect Clause:


using the intersect clause.

The intersect clause outputs only rows produced by both the

queries intersected.

Example:

SELECT salesman_no, name FROM salesman_master

WHERE city=‘mumbai’

INTERSECT

37 | P a g e

SELECT salesman_master.salesman_no,name

FROM salesman_master,sales_order

WHERE salesman_master.salesman_no=

salesman_order.salesman_no;

Minus Clause:


using the minus clause.

The minus clause outputs the rows produced by the first query,

after filtering the rows retrieved by the second query.

Example:

SELECT product_no FROM product_master

MINUS

SELECT product_no FROM sales_order_details;

To create Client master table

Field Name Data Type Size Constraint

CLIENT_NO VARCHAR 6 PRIMARY KEY

NAME CHAR 20

CITY CHAR 10

PIN_CODE NUMBER 6

STATE CHAR 15

BAL_DUE NUMBER 10

38 | P a g e

CREATE TABLE TBL_CLIENTMSTR

(

CLIENT_NO VARCHAR(6) PRIMARY KEY,

NAME CHAR(20),

CITY CHAR(10),

PIN_CODE NUMBER(6),

STATE CHAR(15),

BAL_DUE NUMBER(10),

CHECK(CLIENT_NO LIKE 'C%')

);

To insert below data in client master table

CLIENT_NO NAME CITY PIN_CODE STATE BAL_DUE

C00001 Ivan Bayross Bombay 400054 Maharastra 15000

C00002 Vandana Saitwal Madras 780001 Tamil Nadu 0

C00003 Pramda Jaguste Bombay 400057 Maharastra 5000

C00004 Basu Navingi Bombay 400057 Maharastra 0

C00005 Ravi Sreedharan Delhi 100001 Delhi 2000

C00006 Rukmini Bombay 400050 Maharatra 0

insert into tbl_clientmstr

values('C00001','Ivan Bayross','Bombay',400054,'Maharastra',1500);


values('C00002','Vandana Satiwal','Madras',780001,'Tamil Nadu',0);

39 | P a g e


values('C00003','Pramada Jaguste','Bombay',400057,'Maharastra',5000);


values('C00004','Basu Navingi','Bombay',400057,'Maharastra',0);


values('C00005','Ravi Sreedharan','Delhi',100001,'Delhi',2000);


values('C00006','Rukmani','Bombay',400050,'Maharastra',0);

To create Product master table


PRODUCT_NO VARCHAR 6 PRIMARY KEY

DESCRIPTION VARCHAR 20

PROFIT_PERCENT NUMBER 5,2

UOM CHAR 6 DEFAULT ‘Piece’

QTY_ON_HAND NUMBER 5

SELL_PRICE NUMBER 6

COST_PRICE NUMBER 6

CREATE TABLE TBL_PRODUCTMSTR

(

40 | P a g e

PRODUCT_NO VARCHAR(6) PRIMARY KEY,

DESCRIPTION VARCHAR(20),

PROFIT_PERCENT NUMBER(5,2),

UOM CHAR(6) DEFAULT 'PIECE',

QTY_ON_HAND NUMBER(5),

SELL_PRICE NUMBER(6),

COST_PRICE NUMBER(6),

CHECK(PRODUCT_NO LIKE 'P%')

);

To insert below data in Product master table

PRODUCT

_NO DESCRIP TION

PROFIT_

PERCENT UOM

QTY_ON_H

AND

REORDER

_LEVEL

SELL_PRI

CE

COST

_PRICE

P00001 1.44 Floppies 5 Piece 100 20 525 500

P03453 Monitors 6 Piece 10 3 12000 11280

P06734 Mouse 5 Piece 20 5 1050 1000

P07865 1.22 Floppies 5 Piece 100 20 525 500

P07868 Key Boards 2 Piece 10 3 3150 3050

P07885 CD Drive 2.5 Piece 10 3 5250 5100

P07965 540 HDD 4 Piece 10 3 8400 8000

P07975 1.44 Drive 5 Piece 10 3 1050 1000

P08865 1.22 Drive 5 Piece 2 3 1050 1000

insert into tbl_productmstr

values('P00001','1.44 Floppies',5,'Piece',100,525,500);

41 | P a g e


values('P03453','Monitors',6,'Piece',10,12000,11280);


values('P06734','Mouse',5,'Piece',20,1050,1000);


values('P07865','1.22 Floppies',5,'Piece',100,525,500);


values('P07868','Key Boards',2,'Piece',10,3150,3050);


values('P07885','CD Drive',2.5,'Piece',10,5250,5100);


values('P07965','540 HDD',4,'Piece',10,8400,8000);


values('P07975','1.44 Drive',5,'Piece',10,1050,1000);


values('P08865','1.22 Drive',5,'Piece',2,1050,1000);

42 | P a g e

To create Salesman master table


SALESMAN_NO VARCHAR 6 PRIMARY KEY

SALESMAN_NAME CHAR 15

ADDRESS1 CHAR 10

ADDRESS2 CHAR 106

CITY CHAR 10

PIN_CODE NUMBER 6

STATE CHAR 10

SALAMT NUMBER 5

TGT_TO_GET NUMBER 5

YTD_SALES NUMBER 5

REMARKS CHAR 5 DEFAULT ‘GOOD’

CREATE TABLE TBL_SALESMANMSTR

(

SALESMAN_NO VARCHAR(6) PRIMARY KEY,

SALESMAN_NAME CHAR(15),

ADDRESS1 CHAR(10),

ADDRESS2 CHAR(106),

CITY CHAR(10),

PIN_CODE NUMBER(6),

STATE CHAR(10),

SALAMT NUMBER(5),

43 | P a g e

TGT_TO_GET NUMBER(5),

YTD_SALES NUMBER(5),

REMARKS CHAR(5) DEFAULT 'GOOD',

CHECK(SALESMAN_NO LIKE 'S%')

);

To insert below data in Salesman master table

SALES

MAN

_NO

SALESMAN

_NAME

ADDRE

SS1

ADDRES

S2 CITY

PIN_

CODE STATE

SAL

AMT

TGT_

TO_

GET

YTD_

SALES REMARKS

S00001 Kiran A/14 Worli Bombay 400002 Maharastra 3000 100 50 GOOD

S00002 Manish 65 Narima

n Bombay 400001 Maharastra 3000

200 100 GOOD

S00003 Ravi P-7 Bandra Bombay 400032 Maharastra 3000 200 100 GOOD

S00004 Ashish A/5 Juhu Bombay 400044 Maharastra 3500 200 150 GOOD

insert into tbl_salesmanmstr

values('S00001','Kiran','A/14','Worli','Bombay','400002','Maharastra',3000,100,50,'GOOD');


values('S00002','Manish','65','Nariman','Bombay','400001','Maharastra',3000,200,100,'GOOD');


values('S00003','Ravi','P-7','Bandra','Bombay','400032','Maharastra',3000,200,100,'GOOD');


values('S00004','Ashish','A/5','Juhu','Bombay','400044','Maharastra',3500,200,150,'GOOD');

44 | P a g e

To create Sales order table


ORDER_NO VARCHAR 6 PRIMARY KEY

ORDER_DATE DATE

CLIENT_NO VARCHAR 6 FOREING KEY

DELY_TYPE CHAR 1

BILL_YN CHAR 1

SALESMAN_NO VARCHAR 6

DELY_DATE DATE

ORDER_STATUS CHAR 10

CREATE TABLE TBL_SALESORDER

(

ORDER_NO VARCHAR(6) PRIMARY KEY,

ORDER_DATE DATE,

CLIENT_NO VARCHAR(6) REFERENCES TBL16_CLIENTMSTR(CLIENT_NO),

DELY_TYPE CHAR(1),

BILL_YN CHAR(1),

SALESMAN_NO VARCHAR(6) REFERENCES TBL16_SALESMANMSTR(SALESMAN_NO),

DELY_DATE DATE,

ORDER_STATUS CHAR(10),

CHECK(ORDER_NO LIKE 'O%')

);

45 | P a g e

To insert below data in Sales order table

ORDER

_NO ORDER DATE

CLIEN

_NO

DELY

_TYPE

BILL

_YN

SALES

MAN_ NO DELY_ DATE ORDER_ STATUS

O19001 12-JAN-96 C00001 F N S00001 20-JAN-96 IN PROCESS

O19002 25-JAN-96 C00002 P N S00002 27-JAN-96 CANCELLED

O46865 18-FEB-96 C00003 F Y S00003 20-FEB-96 FULFILLED

O19003 03-APR-96 C00001 F Y S00001 07-APR-96 FULFILLED

O46866 20-MAY-96 C00004 P N S00002 22-MAY-96 CANCELLED

O19008 24-MAY-96 C00005 F N S00004 26-MAY-96 IN PROCESS

insert into tbl_salesorder

values('O19001','12-JAN-96','C00001','F','N','S00001','20-JAN-96','IN PROCESS');


values('O19002','25-JAN-96','C00002','P','N','S00002','27-JAN-96','CANCELLED');


values('O46865','18-FEB-96','C00003','F','Y','S00003','20-FEB-96','FULFILLED');


values('O19003','03-APR-96','C00001','F','Y','S00001','07-APR-96','FULFILLED');


46 | P a g e

values('O46866','20-MAY-96','C00004','P','N','S00002','22-MAY-96','CANCELLED');


values('O19008','24-MAY-96','C00005','F','N','S00004','26-MAY-96','IN PROCESS');

To create Sales order details table


ORDER_NO VARCHAR 6 FOREIN KEY

PRODUCT_NO VARCHAR 6

QTY_ORDERED NUMBER 3

QTY_DISP NUMBER 3

PRODUCT_RATE NUMBER 6

CREATE TABLE TBL_SALESORDER_DETAIL

(

ORDER_NO VARCHAR(6) REFERENCES TBL16_SALESORDER(ORDER_NO),

PRODUCT_NO VARCHAR(6) REFERENCES TBL16_PRODUCTMSTR(PRODUCT_NO),

QTY_ORDERED NUMBER(3),

QTY_DISP NUMBER(3),

PRODUCT_RATE NUMBER(6)

);

To insert below data in Sales order table

47 | P a g e

ORDER

_NO

PRODUCT

_NO

QTY

_ORDERED

QTY

_DISP

PRODUCT

_RATE

O19001 P00001 4 4 525

O19001 P07965 2 1 8400

O19001 P07885 3 1 5250

O19002 P00001 10 0 525

O46865 P07868 3 3 3150

O46865 P07885 3 1 5250

O46865 P00001 10 10 525

O46865 P03453 4 4 1050

O19003 P03453 2 2 1050

O19003 P06734 1 1 12000

O46866 P07965 1 0 8400

O46866 P07975 1 0 1050

O19008 P00001 10 5 525

O19008 P07975 5 3 1050

insert into tbl_salesorder_detail

values('O19001','P00001',4,4,525);


values('O19001','P07965',2,1,8400);


values('O19001','P07885',3,1,5250);

48 | P a g e


values('O19002','P00001',10,0,525);


values('O46865','P07868',3,3,3150);


values('O46865','P07885',3,1,5250);


values('O46865','P00001',10,10,525);


values('O46865','P03453',4,4,1050);


values('O19003','P03453',2,2,1050);


values('O19003','P06734',1,1,12000);


values('O46866','P07965',1,0,8400);

49 | P a g e


values('O46866','P07975',1,0,1050);


values('O19008','P00001',10,5,525);


values('O19008','P07975',5,3,1050);

2) Exercises On Compilations On Table Data:

1 Find The Names Of All Clients S Having ‘a’ As The Second Letter In Their Names.

ANS.

SQL> select name

2 from tbl_clientmstr

3 where name like '_a%';

NAME

--------------------

Vandana Satiwal

Basu Navingi

Ravi Sreedharan

2 Find Out The Clients Who Stay In A City Whose Second Letter Is ’a’.

ANS.

SQL> select city

50 | P a g e


3 where city like '_a%';

CITY

----------

Madras

3 Find Out The List Of All Clients Who Stay In ‘Bombay ’ Or ‘ Delhi’

ANS.

SQL> select *


3 where city='Bombay' OR city='Delhi';

CLIENT NAME CITY PIN_CODE STATE BAL_DUE

------ -------------------- ---------- ---------- --------------- ----------


C00003 Pramada Jaguste Bombay 400057 Maharastra 5000

C00004 Basu Navingi Bombay 400057 Maharastra 0

C00005 Ravi Sreedharan Delhi 100001 Delhi 2000

C00006 Rukmani Bombay 400050 Maharastra 0

4 Print The List Of Clients Whose Bal_Due Is Greater Then The Value 10,000.

ANS.

SQL> select *


3 where bal_due>10000;


51 | P a g e

------ -------------------- ---------- ---------- --------------- ----------


5 Print Information From Sales_Order Table For Orders Placed In The Month Of January.

ANS.

SQL> select *

2 from tbl_salesorder

3 where order_date like '%%_JAN_%%';

ORDER_ ORDER_DAT CLIENT D B SALESM DELY_DATE ORDER_STAT

------ --------- ------ - - ------ ---------

----------

O19001 12-JAN-96 C00001 F N S00001 20-JAN-96 IN PROCESS

O19002 25-JAN-96 C00002 P N S00002 27-JAN-96 CANCELLED

6 Display The Order Information Client_No ‘C00001’ And ‘C00002’.

ANS.

SQL> select *


3 where client_no like 'C00001' OR client_no like 'C00002';

CLIENT NAME CITY PIN_CODE STATE

BAL_DUE

------ -------------------- ---------- ---------- ------------ --- ----------


C00002 Vandana Satiwal Madras 780001 Tamil Nadu 0

7 Find The Product Whose Selling Price Is More Then 2000 And Less Than Or Equal To 5000.

SQL> select *

52 | P a g e

2 from tbl_productmstr

3 where sell_price>2000 AND sell_price select description,sell_price,sell_price*0.15


3 where sell_price>1500;

DESCRIPTION SELL_PRICE SELL_PRICE*0.15

-------------------- ---------- ---------------

Monitors 12000 1800

Key Boards 3150 472.5

CD Drive 5250 787.5

540 HDD 8400 1260

9 List The Name, City And State Of Client Who Are Not In The State ‘Maharastra’.

ANS.

SQL> select *


3 where state NOT IN 'Maharastra';

53 | P a g e

CLIENT NAME CITY PIN_CODE STATE

BAL_DUE

------ -------------------- ---------- ---------- --------------- ---------

C00002 Vandana Satiwal Madras 780001 Tamil Nadu

0

C00005 Ravi Sreedharan Delhi 100001 Delhi

2000

10 Count The Total Number Of Orders.

ANS.

SQL> select count(order_no) from tbl_salesorder;

COUNT(ORDER_NO)

---------------

6

11 Calculate The Average Price Of All Products.

ANS.

SQL> select avg(sell_price) from tbl_productmstr;

AVG(SELL_PRICE)

---------------

3666.66667

12 Determine The Maximum And Minimum Product Price. Rename O/P As Max_Price And

Min_Price Respectively.

ANS.

SQL> select min(sell_price),max(sell_price)

54 | P a g e

2 from tbl_productmstr;

MIN(SELL_PRICE) MAX(SELL_PRICE)

--------------- ---------------

525 12000

13 Count The Number Of Products Having Price Greater Then Or Equal To 1500.

ANS.

SQL> select COUNT(sell_price)


3 where sell_price>=1500;

COUNT(SELL_PRICE)

-----------------

4

14 Find All Products Who’s Qty On Hand Is Less Then Reorder Level.

ANS.

SQL> select *


3 where QTY_ON_HAND

55 | P a g e

3)Exercise Of Data Manipulation:

1 Display The Order Number & Day On Which Client Placed There Order.

ANS.

SQL> select order_no,TO_CHAR(order_date,'day')

2 from tbl_salesorder;

ORDER_ TO_CHAR(O

------ ---------

O19001 friday

O19002 thursday

O46865 sunday

O19003 wednesday

O46866 monday

O19008 Friday

2 Display The Month & Date When The Order must Be Delivered.

ANS.

SQL> select *

2 from tbl_salesorder

3 where order_status='FULFILLED';

ORDER_ ORDER_DAT CLIENT D B SALESM DELY_DATE ORDER_STAT

------ --------- ------ - - ------ --------- ----------

O46865 18-FEB-96 C00003 F Y S00003 20-FEB-96 FULFILLED

O19003 03-APR-96 C00001 F Y S00001 07-APR-96 FULFILLED

3 Display The Order_Date In The Format “Dd-Months-Yy”.

56 | P a g e

ANS.

SQL> select TO_CHAR(order_date,'dd-mon-yy')


TO_CHAR(O

---------

12-jan-96

25-jan-96

18-feb-96

03-apr-96

20-may-96

24-may-96

4 Find Out The Day 15 Days After Today Date.

ANS.

SQL> select sysdate+15 from dual;

SYSDATE+1

---------

11-AUG-17

5 Find Out The Number Of Days Elapsed Between Todays Date And The Delivery Date Of The

Order Placed By The Clients.

ANS.

SQL> select dely_date-sysdate


DELY_DATE-SYSDATE

57 | P a g e

-----------------

-7860.5628

-7853.5628

-7829.5628

-7782.5628

-7737.5628

-7733.5628

4) Exercise On Using Having And Grouped By Clauses:

1 Print The Description And Total Quantity Sold For Each Product.

ANS.

SQL> select description,sum(qty_on_hand)


3 GROUP BY description;

DESCRIPTION SUM(QTY_ON_HAND)

-------------------- ----------------

1.44 Floppies 100

CD Drive 10

1.22 Drive 2

540 HDD 10

1.22 Floppies 100

Key Boards 10

Mouse 20

1.44 Drive 10

Monitors 10

58 | P a g e

2 Find The Values Of Each Product Sold.

ANS.

SQL> select DESCRIPTION,SUM(sell_price)


3 GROUP BY DESCRIPTION;

DESCRIPTION SUM(SELL_PRICE)

-------------------- ---------------

1.44 Floppies 525

CD Drive 5250

1.22 Drive 1050

540 HDD 8400

1.22 Floppies 525

Key Boards 3150

Mouse 1050

1.44 Drive 1050

Monitors 12000

3 Calculate ‘Avg’ Qty Sold For Each Product Client That Has Maximum Order Value Of 15000.00

ANS.

SQL> select c.client_no,c.name,AVG(sd.qty_disp)"avg sales"

2 from tbl_clientmstr c,tbl_salesorder s,tbl_salesorder_detail sd

3 GROUP BY c.client_no,name

4 HAVING max(sd.qty_ordered*sd.product_rate)>15000;

59 | P a g e

CLIENT NAME avg sales

------ -------------------- ----------

C00004 Basu Navingi 2.5

C00003 Pramada Jaguste 2.5

C00006 Rukmani 2.5

C00001 Ivan Bayross 2.5

C00002 Vandana Satiwal 2.5

C00005 Ravi Sreedharan 2.5

4 Find Out The Sum Total Of All The Billed Order For The Month Of January.

ANS.

SQL> select s.order_no,s.order_date,sum(sd.qty_ordered*sd.product_rate)"ordered

bill"

2 from tbl_salesorder s,tbl_salesorder_detail sd

3 where sd.order_no=s.order_no AND s.bill_yn='y' AND to_char(order_date,'MON'

)='JAN'

4 group by s.order_no,s.order_date;

no rows selected

5) Exercises On Join And Correlation:

1 Find Out The Product, Which Have Been Sold To ‘Ivan Byross’.

ANS.

SQL> select sd.product_no,p.description

2 from tbl_salesorder_detail sd,tbl_salesorder s,tbl_productmstr p,tbl_clientmstr c

3 where p.product_no=sd.product_no AND c.client_no=s.client_no AND name='Ivan

60 | P a g e

Bayross';

PRODUC DESCRIPTION

------ --------------------

P00001 1.44 Floppies

P07965 540 HDD

P07885 CD Drive


P07868 Key Boards

P07885 CD Drive

P03453 Monitor

P06734 Mouse

2 Find Out The Products And Their Quentity That Will Have To Deliver In The Current Month.

ANS.

SQL> select sd.product_no,p.description,sum(sd.qty_ordered)

2 from tbl_salesorder_detail sd,tbl_salesorder s,tbl_productmstr p

3 where sd.product_no=p.product_no AND to_char(dely_date,'MON-YY')=to_char(sy

sdate,'MON-YY')

4 group by sd.product_no,p.description;

no rows selected

3 Find Out The Description And Product No Of Constantly Sold Products.

ANS. SQL> select distinct p.product_no,description

2 from tbl_salesorder_detail sd,tbl_productmstr p

61 | P a g e

3 where p.product_no=sd.product_no;

PRODUC DESCRIPTION

------ --------------------

P07868 Key Boards

P03453 Monitors

P06734 Mouse

P07975 1.44 Drive


P07885 CD Drive

P07965 540 HDD

4 Find Out The Names Of Clients Who Have Purchased ‘CD Drive’.

ANS.

SQL> select distinct s.client_no,c.name

2 from tbl_salesorder s,tbl_salesorder_detail sd,tbl_clientmstr c,tbl_productmstr p

3 where p.product_no=sd.product_no AND s.order_no=sd.order_no AND c.client_no

=s.client_no AND description='CD Drive';

CLIENT NAME

------ --------------------

C00003 Pramada Jaguste

C00001 Ivan Bayross

5 List The Product No And Order No Of Customer Having Quantity Ordered Less Than 5 From The

Sales Order Details Table For The Product 1.44 Floppies.

ANS.

62 | P a g e

SQL> select sd.product_no,sd.order_no

2 from tbl_salesorder_detail sd,tbl_salesorder s,tbl_productmstr p

3 where s.order_no=sd.order_no AND p.product_no=sd.product_no AND sd.qty_orde

red select sd.product_no,p.description,sum(qty_ordered)

2 from tbl_salesorder_detail sd,tbl_salesorder s,tbl_productmstr p,tbl_clientmstr c

3 where s.order_no=sd.order_no AND sd.product_no=p.product_no AND s.client_no

=c.client_no AND (c.name='Ivan Bayross' OR c.name='Vandana Satiwal')

4 group by sd.product_no,p.description;

PRODUC DESCRIPTION SUM(QTY_ORDERED)

------ -------------------- ----------------

P03453 Monitors 2

P06734 Mouse 1

P00001 1.44 Floppies 14

P07885 CD Drive 3

P07965 540 HDD 2

7 Find The Products And There Quantities For The Order Placed Client No ‘C00001’ And ‘C00002’.

63 | P a g e

ANS.

SQL> select s.client_no,sd.product_no,p.description,sum(qty_o

ed"

2 from tbl_salesorder_detail sd,tbl_salesorder s,tbl

6_clientmstr c

3 where s.order_no=sd.order_no AND sd.product_no=p.product

=c.client_no

4 group by s.client_no,sd.product_no,p.description;

CLIENT PRODUC DESCRIPTION units ordered

------ ------ -------------------- -------------

C00001 P07885 CD Drive 3

C00002 P00001 1.44 Floppies 10

C00001 P03453 Monitors 2

C00003 P07868 Key Boards 3

C00004 P07965 540 HDD 1

C00005 P00001 1.44 Floppies 10

C00001 P07965 540 HDD 2

C00003 P07885 CD Drive 3

C00001 P06734 Mouse 1

C00004 P07975 1.44 Drive 1

C00001 P00001 1.44 Floppies 4

CLIENT PRODUC DESCRIPTION units ordered

64 | P a g e

------ ------ -------------------- -------------

C00003 P00001 1.44 Floppies 10

C00003 P03453 Monitors 4

C00005 P07975 1.44 Drive 5

6) Exercise of Sub Queries:

1 Find The Product No And Description Of Non-Moving Products.

ANS.

SQL> select product_no,description


3 where product_no NOT IN(select product_no FROM tbl_salesorder_detail);

PRODUC DESCRIPTION

------ --------------------


P08865 1.22 Drive

2 Find The Customer Name, Address1, Address2, City And Pincode For The Client Who Has Placed

Order No ‘O19001’.

ANS.

SQL> select *


3 where client_no IN(select client_no FROM tbl16_salesorder WHERE order_no='O

19001');


------ -------------------- ---------- ---------- --------------- ----------


65 | P a g e

3 Find The Clients Names Who Have Placed Orders Before The Month Of May ’96.

ANS.

SQL> select client_no


3 where client_no IN(select client_no FROM tbl_salesorder WHERE to_char(ord

er_date,'MON-YY')='MAY-17');

no rows selected

4 Find Out If The Product ‘1.44 Drive’ Has Been Ordered By Any Client And Print The Client No,

Name To Whom It Was Sold.

ANS.

SQL> select client_no,name


3 where client_no IN(select client_no FROM tbl_salesorder WHERE order_no IN

(select order_no FROM tbl_salesorder_detail WHERE product_no IN(select product

_no FROM tbl_productmstr WHERE description='1.44 Drive')));

CLIENT NAME

------ --------------------

C00004 Basu Navingi

C00005 Ravi Sreedharan

5 Find The Names Of Clients Who Have Placed Orders Worth Rs.10, 000 Or More Than.

ANS.

SQL> select name

66 | P a g e


3 where client_no IN(select client_no FROM tbl_salesorder_detail WHERE(qty_

ordered*product_rate)>=10000);

NAME

--------------------

Ivan Bayross

Vandana Satiwal

Pramada Jaguste

Basu Navingi

Ravi Sreedharan

Rukmani

7) Exercise On Constructing Sentences With Data:

1

Print Information From Product_Mst, Sales_Order_Detail Tables In The Following Format For All

The Records:

{Description} Worth Rs.{Total Salea For The Product} Was Sold.

ANS.

SQL> select p.description||'worth rs',sum(sd.product_rate)||'was sold'

2 from tbl_productmstr p,tbl_salesorder_detail sd

3 group by p.description;

P.DESCRIPTION||'WORTHRS' SUM(SD.PRODUCT_RATE)||'WASSOLD'

---------------------------- ------------------------------------------------

1.44 Floppiesworth rs 48750was sold

CD Driveworth rs 48750was sold

67 | P a g e

1.22 Driveworth rs 48750was sold

540 HDDworth rs 48750was sold

1.22 Floppiesworth rs 48750was sold

Key Boardsworth rs 48750was sold

Mouseworth rs 48750was sold

1.44 Driveworth rs 48750was sold

Monitorsworth rs 48750was sold

2

Print Information From Product_Mst, Sales_Order_Detail Table In The Following format For All

The Records:

{Description} Worthrs. {Total Seles For The Product} Was Ordered In The Month Of {Order_Date

In Month Format}.

ANS.

3

Print Informatio From Cliet_Mst,Product_Mst,Sales_Order Tables In The Following Format For

All The Records:

{Cust_Name} Has Placed Order {Order No}On {Order_Date}.

68 | P a g e

ADVANCED SQL

2.7: Indexes

When the user fires a SELECT statement to search for a particular

record, the oracle engine must first locate the table in memory and then the

oracle engine then performs a sequential search to locate records that match

user-defined criteria.

Indexing a table is an ‘access strategy’, that is a way to sort and

search records in the table.

Indexes are essential to improve the speed with which the record/s

can be located and retrieved from a table.

An indexes is an ordered list of the content of a column, or a

group of columns of a table.

Indexing involves forming a two dimension matrix completely

independent of the table on which the index is being created.

--- A column which will hold sorted data, extracted from the

table on which the index is being created.

--- An address field that identifies the location of the record in

the oracle database. This address field is called Rowid.

When data is inserted in the table, the oracle engine inserts the data value in the index.

For every data value held in the index the oracle engine inserts a unique

rowid value . This is done for every data value inserted into the index, without

exception. This rowid indicates exactly where the record is stored in the table.

hence once the appropriate index data values have been located,

the oracle engine locates an associated record in the table using the rowid

found in the table.

The records in the index are sorted in the ascending order of the

index column/s.

69 | P a g e

If the SELECT statement has a where clause for the table column

that is indexed, the oracle engine will scan the index sequentially looking for a

match of the search criteria rather than the table column itself.

The sequential search is done using an ASCII compare routine to scan

the columns of an index.

Address field in the index:

Address field is an index is called ROWID.

ROWID is an internal generated and maintained, binary value,

which identifies a record.

The information in the ROWID columns provides oracle engine the

location of the table and a specific record in the oracle database.

Each time a record is inserted into the table, oracle locates free

space in the data blocks in the data files. Oracle then inserts a record in the

table and makes an entry in the index. The entry made in the index consists of

table data combined with the oracle engine created rowid for the table record.

The ROWID format used by oracle is as follows:

BBBBBBB.RRRR.FFFF

where,

FFFF is a unique number given by the oracle engine to each Data File.

Datafiles are the files used by the oracle engine to store user data. Each data

file is given a unique number at the time of data file creation. The oracle engine

uses this number to identify the data file in which sets of table records are

stored.

BBBBBBB is a block number in which record is stored.each data file is

further divided into data blocks and each is given a unique number. The

unique number assigned to the first data block in a data file 0. Thus the block

number can be used to identify the data block in which a record is stored.

RRRR is a unique record number. Each data block can store one or

more records. Thus each record in the data block is given a unique record

70 | P a g e

number. The unique record number assigned to the first record in each data

block is 0. Thus record number can be used to identify a record stored in a

block.

Duplicate/Unique Index:

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

-- Indexes that allow duplicate values for the indexed

columns

-- Indexes that deny duplicate values for the indexedcolumns

Creation of Index:

An index can be created on one or more columns. Based on the

number of columns include in the index, an index can be:

-- Simple Index

-- Composite Index

Creating Simple Index:

An index created on a single column of a table it is called

Simple Index.

Syntax:

CREATE INDEX indexname

ON tablename (columnname);

Example:

CREATE INDEX ex_index

ON ex_t (no);

71 | P a g e

Creating Composite Index:

An index created on more than one column it is called composite

Index.

Syntax:

CREATE INDEX indexname

ON tablename (columnname, columnname);

Example:

CREATE INDEX ex_index

ON ex_t (order_no, product_no);

The indexes in the above example do’t enforce uniqueness, to

create the unique index, the keyword UNIQUE should be included in the create

index command.

Creation of Unique Index:

An unique index can also be created on one or more columns. If an

index is created on a single column it is called simple unique index.

Syntax:

CREATE UNIQUE INDEX indexname

ON tablename(columnname);

If an index is created on more than one column it is called

composite unique index.

Syntax:

CREATE UNIQUE INDEX indexname

ON tablename(columnname, columnname);

72 | P a g e

Example:

CREATE UNIQUE INDEX ex_un_index

ON ex_t (no);

Dropping Indexes:

Indexes associated with the tables can be removed by using the

DROP INDEX command.

Syntax:

DROP INDEX indexname

Example:

DROP INDEX ex_un_index;

Using ROWID To Delete Duplicate Rows From A Table:

If the user enters duplicate records in a table, a DELETE

statement with where clause will delete all the records that satisfy the where

condition specified in the DELETE statement.

For Example,

DELETE FROM client_mst

WHERE cno in (‘C001’,’C002’,’C003’);

will delete all the records with client no ‘C001’,’C002’,’C003’.

We required that oracle engine must retain one record and

delete all other duplicate records.

73 | P a g e

To retain one record, the where clause must be defined on

the column that uniquely identifies a record. Oracle will assign a unique rowid

value that points to a record within a block in the data file for each record even

if user enters duplicate records.

DELETE statement must be written such that the WHERE

clause is defined using the rowid column. The value for the where clause in

the DELETE statement must be selected by using a SELECT statement that

retrieves the rowid of the first row in each set of duplicate records in the table.

Example:

DELETE FROM client_mst

WHERE rowid NOT IN

(SELECT min(rowid) FROM client_mst

GROUP BY cno,name,bal_due);

Using ROWNUM in SQL Statement:

ROWNUM column returns a number indicating the order in

which oracle engine selects the row from a table or a set of joined rows.

ROWNUM can be used to limit the number of rows retrieved.

Example:

SELECT ROWNUM, cno, name

FROM client_mst

WHERE ROWNUM < 8;

Note:

74 | P a g e

The oracle engine assigns a ROWNUM value to each row as it is

retrieved, before rows are sorted on the column/s in the ORDER BY clause.

The order in which data is retrieved is dependent upon the indexes

created on the table.

If an index is created on the column/s used in the order by clause, the

oracle engine uses the index to retrieve data in a sorted order. Thus the

ROWNUM will be in the order of the rows retrieved from the index.

If an index is not created on the column/s used in the order by clause,

the oracle engine will retrieve data from the table in the order of data insertion

and thus an ORDERBY clause does not affect the ROWNUM of each row.

2.10:Views

After a table is created and populated with data, it may become necessary to

prevent all users from accessing all columns of table, for data security reason.

This would mean creating several tables having the appropriate number

of columns and assigning specific users to each table, as required.

This will answer data security requirements very well but will give rise to

a great deal of redundant data being resident in tables, in the database.

To reduce redundant data to the minimum possible, oracle allows

the creation of an object called a view.

View stored only a definition in oracle’s system catalogue.

When a reference is made to a view, its definition is scanned, the

base table is opened and the view created on top of the base table.

Hence, a view holds no data at all, until a specific call to the view is

made. This reduce redundant data on the HDD to a very large extent.

75 | P a g e

When a view is used to manipulate table data, the underlying base

table will be completely invisible. This will give the level of data security

required.

The oracle engine treats a view just as though it was a base table.

A query fired on a view will run slower that a query fired on a base

table. This is because the view definition has to be retrieved from oracle’s

system catalogue, the base table ha s to be identified an opened in memory and

then the view has to be constructed on top of the base table, only then will the

query actually execute and return the active data set.

Some view’s are used only for looking at table data. Other view’s

can be used to Insert, Update and Delete table data as well as view data.

If a view is used to only look at table data nothing else the view is

called a Read-Only view.

A view that is used to look at table data as well as Insert, Update

and Delete table data is called an Updateable view.

Reason why views are created are:

-- When data security is required

-- When data redundancy is to be kept to the minimum while

maintaining data security.

Creation Of Views:

Syntax:

CREATE VIEW viewname AS

SELECT columnname, columnname

FROM tableaname

WHERE columnname = expressionlist

76 | P a g e

GROUP BY grouping criteria

HAVING predicate

Note:

The ORDER BY clause cannot be used while creating a view.

Example:

CREATE VIEW ex_view AS

SELECT * FROM client_mat

The columns of the view can take on different names from the table

columns, if required.

Example:

CREATE VIEW ex_view AS

SELECT name, address1 add1, address2

add2,city,state

FROM client_mst

Selecting data set from view:

Once a view has been created, it can be queried exactly like a base table.

syntax:

SELECT columnname, columnname

FROM viewname;

Example:

77 | P a g e

SELECT name,address1,city

FROM ex_view

WHERE city IN (‘surat’, ‘baroda’);

Updateable Views:

Views can also be used for data manipulation. Views on which

data manipulation can be done are called updateable views.

When you give an updateable view name in the Update, Insert or

Delete SQL statement, modifications to data will be passed to the underlying

table.

For a view to be updateable, it should meet the following

criteria:

1

contents · 2020. 10. 9. · 4 | p a g e this can be accomplished through a combination of the...

Documents