Bhujbal Knowledge City
MET Institute of Technology-Polytechnic
Chapter 3. Interactive SQL and Advance
SQL: SQL Performance Tuning
3.1 In-built Functions : String, Arithmetic,
3.2 Date and Time, Aggregate functions.
3.3 Queries using Group by, having and order by clause
3.3 Joins- Inner and outer join, Sub queries.
3.4 Views- View, create view Command, Updating Views
3.4 Updating Views
3.4 Views and Joins
3.4 Views and sub queries, Dropping Views.
3.5 Sequences: creating Sequences
3.5 Altering Sequences, Dropping Sequences.
3.6 Indexes: Index Types, Creating an Index,
3.6 Simple, Unique Index
3.7 Composite Index, Dropping Indexes.
3.8 Synonyms: Creating Synonyms, Dropping Synonyms.
Prepared by: Prof. P. B. Khairnar
, SQL Functions
Sr. String Arithmetic Date & Time Aggregate
N
o.
1 LOWER ABS SYSDATE Min
2 UPPER CEIL ADD_MONTHS(d,n) Max
3 INITCAP EXP LAST_DAY Sum
4 LPAD FLOOR MONTHS_BETWEEN (d1,d2) Avg
5 RPAD LN NEXT_DAY(date, weekday) Count
6 LTRIM LOG Count (*)
7 RTRIM MOD
8 TRANSLATE POWER (m, n)
9 REPLACE ROUND ()
10 LENGTH SIGN
11 SUBSTR SQRT
12 CONCATENATE GREATEST
13 SOUNDEX LEAST Prepared by: Prof. P. B. Khairnar
, String functions
1) Lower :- Select Lower (‘MET') from Dual;
Output: met
2) Upper :- Select Upper (‘dbmS') from Dual;
Output: DBMS
3) INITCAP:-Select INITCAP(‘dbms') from Dual;
Output: Dbms
4) LPad:- Select lpad('RDBMS',10,'^') from Dual;
Output: ^^^^^RDBMS
5) RPad:- Select rpad('RDBMS',10,'^') from Dual;
Output: RDBMS^^^^^
Prepared by: Prof. P. B. Khairnar
, 6) LTrim:- Select ltrim('Pune Tech-Max Publication','Pune') from
Dual;
Output: Tech-Max Publication
7) RTrim:-Select rtrim('Pune Tech-Max Publication','Publication')
from Dual;
Output: Pune Tech-Max
8) Translate:- Translate single character with single character
Select Translate('Jack','J','P') from Dual;
Output: Pack
9) Replace:- Replace single character with multiple charecters.
Select Replace('Kamesh and Kalpesh', ‘ka',‘R') from Dual;
Output: Rmesh and rpesh
Prepared by: Prof. P. B. Khairnar
MET Institute of Technology-Polytechnic
Chapter 3. Interactive SQL and Advance
SQL: SQL Performance Tuning
3.1 In-built Functions : String, Arithmetic,
3.2 Date and Time, Aggregate functions.
3.3 Queries using Group by, having and order by clause
3.3 Joins- Inner and outer join, Sub queries.
3.4 Views- View, create view Command, Updating Views
3.4 Updating Views
3.4 Views and Joins
3.4 Views and sub queries, Dropping Views.
3.5 Sequences: creating Sequences
3.5 Altering Sequences, Dropping Sequences.
3.6 Indexes: Index Types, Creating an Index,
3.6 Simple, Unique Index
3.7 Composite Index, Dropping Indexes.
3.8 Synonyms: Creating Synonyms, Dropping Synonyms.
Prepared by: Prof. P. B. Khairnar
, SQL Functions
Sr. String Arithmetic Date & Time Aggregate
N
o.
1 LOWER ABS SYSDATE Min
2 UPPER CEIL ADD_MONTHS(d,n) Max
3 INITCAP EXP LAST_DAY Sum
4 LPAD FLOOR MONTHS_BETWEEN (d1,d2) Avg
5 RPAD LN NEXT_DAY(date, weekday) Count
6 LTRIM LOG Count (*)
7 RTRIM MOD
8 TRANSLATE POWER (m, n)
9 REPLACE ROUND ()
10 LENGTH SIGN
11 SUBSTR SQRT
12 CONCATENATE GREATEST
13 SOUNDEX LEAST Prepared by: Prof. P. B. Khairnar
, String functions
1) Lower :- Select Lower (‘MET') from Dual;
Output: met
2) Upper :- Select Upper (‘dbmS') from Dual;
Output: DBMS
3) INITCAP:-Select INITCAP(‘dbms') from Dual;
Output: Dbms
4) LPad:- Select lpad('RDBMS',10,'^') from Dual;
Output: ^^^^^RDBMS
5) RPad:- Select rpad('RDBMS',10,'^') from Dual;
Output: RDBMS^^^^^
Prepared by: Prof. P. B. Khairnar
, 6) LTrim:- Select ltrim('Pune Tech-Max Publication','Pune') from
Dual;
Output: Tech-Max Publication
7) RTrim:-Select rtrim('Pune Tech-Max Publication','Publication')
from Dual;
Output: Pune Tech-Max
8) Translate:- Translate single character with single character
Select Translate('Jack','J','P') from Dual;
Output: Pack
9) Replace:- Replace single character with multiple charecters.
Select Replace('Kamesh and Kalpesh', ‘ka',‘R') from Dual;
Output: Rmesh and rpesh
Prepared by: Prof. P. B. Khairnar
