select last_name,
salary
from employees
where job_id='SA_REP';
Results:
LAST_NAME SALARY
------------------------- ----------------------
Tucker 10000
Bernstein 9500
Hall 9000
Olsen 8000
Cambrault 7500
Tuvault 7000
King 10000
Sully 9500
McEwen 9000
Smith 8000
Doran 7500
Sewall 7000
Vishney 10500
Greene 9500
Marvins 7200
Lee 6800
Ande 6400
Banda 6200
Ozer 11500
Bloom 10000
Fox 9600
Smith 7400
Bates 7300
Kumar 6100
Abel 11000
Hutton 8800
Taylor 8600
Livingston 8400
Grant 7000
Johnson 6200
30 rows selected
==> SQL Queries using MOD
select last_name,
salary,
MOD(salary, 5000)
from employees
where job_id='SA_REP';
Results :
LAST_NAME SALARY MOD(SALARY,5000)
------------------------- ---------------------- ----------------------
Tucker 10000 0
Bernstein 9500 4500
Hall 9000 4000
Olsen 8000 3000
Cambrault 7500 2500
Tuvault 7000 2000
King 10000 0
Sully 9500 4500
McEwen 9000 4000
Smith 8000 3000
Doran 7500 2500
Sewall 7000 2000
Vishney 10500 500
Greene 9500 4500
Marvins 7200 2200
Lee 6800 1800
Ande 6400 1400
Banda 6200 1200
Ozer 11500 1500
Bloom 10000 0
Fox 9600 4600
Smith 7400 2400
Bates 7300 2300
Kumar 6100 1100
Abel 11000 1000
Hutton 8800 3800
Taylor 8600 3600
Livingston 8400 3400
Grant 7000 2000
Johnson 6200 1200
30 rows selected
Sunday, August 15, 2010
Oracle 11g: Using Number Functions ROUND and TRUNC
select 45.923 as ringgit
from dual;
Results:
RINGGIT
----------------------
45.923
===> SQL Queries:
select round(45.923,2),
round(45.923,0),
round(45.923,-1)
from dual;
select trunc(45.923,2),
trunc(45.923),
trunc(45.923,-1)
from dual;
Results:
ROUND(45.923,2) ROUND(45.923,0) ROUND(45.923,-1)
---------------------- ---------------------- ----------------------
45.92 46 50
TRUNC(45.923,2) TRUNC(45.923) TRUNC(45.923,-1)
---------------------- ---------------------- ----------------------
45.92 45 40
from dual;
Results:
RINGGIT
----------------------
45.923
===> SQL Queries:
select round(45.923,2),
round(45.923,0),
round(45.923,-1)
from dual;
select trunc(45.923,2),
trunc(45.923),
trunc(45.923,-1)
from dual;
Results:
ROUND(45.923,2) ROUND(45.923,0) ROUND(45.923,-1)
---------------------- ---------------------- ----------------------
45.92 46 50
TRUNC(45.923,2) TRUNC(45.923) TRUNC(45.923,-1)
---------------------- ---------------------- ----------------------
45.92 45 40
Oracle 11g: Using Character-Manipulation Functions
select employee_id,
first_name,
last_name
job_id
from employees
Results
EMPLOYEE_ID FIRST_NAME JOB_ID
---------------------- -------------------- -------------------------
100 Steven King
101 Neena Kochhar
102 Lex De Haan
103 Alexander Hunold
104 Bruce Ernst
105 David Austin
106 Valli Pataballa
107 Diana Lorentz
108 Nancy Greenberg
109 Daniel Faviet
110 John Chen
111 Ismael Sciarra
112 Jose Manuel Urman
113 Luis Popp
114 Den Raphaely
115 Alexander Khoo
116 Shelli Baida
117 Sigal Tobias
118 Guy Himuro
119 Karen Colmenares
120 Matthew Weiss
121 Adam Fripp
122 Payam Kaufling
123 Shanta Vollman
124 Kevin Mourgos
125 Julia Nayer
126 Irene Mikkilineni
127 James Landry
128 Steven Markle
129 Laura Bissot
130 Mozhe Atkinson
131 James Marlow
132 TJ Olson
133 Jason Mallin
134 Michael Rogers
135 Ki Gee
136 Hazel Philtanker
137 Renske Ladwig
138 Stephen Stiles
139 John Seo
140 Joshua Patel
141 Trenna Rajs
142 Curtis Davies
143 Randall Matos
144 Peter Vargas
145 John Russell
146 Karen Partners
147 Alberto Errazuriz
148 Gerald Cambrault
149 Eleni Zlotkey
150 Peter Tucker
151 David Bernstein
152 Peter Hall
153 Christopher Olsen
154 Nanette Cambrault
155 Oliver Tuvault
156 Janette King
157 Patrick Sully
158 Allan McEwen
159 Lindsey Smith
160 Louise Doran
161 Sarath Sewall
162 Clara Vishney
163 Danielle Greene
164 Mattea Marvins
165 David Lee
166 Sundar Ande
167 Amit Banda
168 Lisa Ozer
169 Harrison Bloom
170 Tayler Fox
171 William Smith
172 Elizabeth Bates
173 Sundita Kumar
174 Ellen Abel
175 Alyssa Hutton
176 Jonathon Taylor
177 Jack Livingston
178 Kimberely Grant
179 Charles Johnson
180 Winston Taylor
181 Jean Fleaur
182 Martha Sullivan
183 Girard Geoni
184 Nandita Sarchand
185 Alexis Bull
186 Julia Dellinger
187 Anthony Cabrio
188 Kelly Chung
189 Jennifer Dilly
190 Timothy Gates
191 Randall Perkins
192 Sarah Bell
193 Britney Everett
194 Samuel McCain
195 Vance Jones
196 Alana Walsh
197 Kevin Feeney
198 Donald OConnell
199 Douglas Grant
200 Jennifer Whalen
201 Michael Hartstein
202 Pat Fay
203 Susan Mavris
204 Hermann Baer
205 Shelley Higgins
206 William Gietz
107 rows selected
===> SQL Query using characters-manipulation functions
select employee_id,
CONCAT(first_name, last_name) EMPLOYEENAME,
job_id,
LENGTH(last_name),
INSTR(last_name,'a') "Contains 'a'?"
from employees
where SUBSTR(job_id, 4)='REP'
Results
EMPLOYEE_ID EMPLOYEENAME JOB_ID LENGTH(LAST_NAME) Contains 'a'?
---------------------- --------------------------------------------- ---------- --------
150 PeterTucker SA_REP 6 0
151 DavidBernstein SA_REP 9 0
152 PeterHall SA_REP 4 2
153 ChristopherOlsen SA_REP 5 0
154 NanetteCambrault SA_REP 9 2
155 OliverTuvault SA_REP 7 4
156 JanetteKing SA_REP 4 0
157 PatrickSully SA_REP 5 0
158 AllanMcEwen SA_REP 6 0
159 LindseySmith SA_REP 5 0
160 LouiseDoran SA_REP 5 4
161 SarathSewall SA_REP 6 4
162 ClaraVishney SA_REP 7 0
163 DanielleGreene SA_REP 6 0
164 MatteaMarvins SA_REP 7 2
165 DavidLee SA_REP 3 0
166 SundarAnde SA_REP 4 0
167 AmitBanda SA_REP 5 2
168 LisaOzer SA_REP 4 0
169 HarrisonBloom SA_REP 5 0
170 TaylerFox SA_REP 3 0
171 WilliamSmith SA_REP 5 0
172 ElizabethBates SA_REP 5 2
173 SunditaKumar SA_REP 5 4
174 EllenAbel SA_REP 4 0
175 AlyssaHutton SA_REP 6 0
176 JonathonTaylor SA_REP 6 2
177 JackLivingston SA_REP 10 0
178 KimberelyGrant SA_REP 5 3
179 CharlesJohnson SA_REP 7 0
202 PatFay MK_REP 3 2
203 SusanMavris HR_REP 6 2
204 HermannBaer PR_REP 4 2
33 rows selected
first_name,
last_name
job_id
from employees
Results
EMPLOYEE_ID FIRST_NAME JOB_ID
---------------------- -------------------- -------------------------
100 Steven King
101 Neena Kochhar
102 Lex De Haan
103 Alexander Hunold
104 Bruce Ernst
105 David Austin
106 Valli Pataballa
107 Diana Lorentz
108 Nancy Greenberg
109 Daniel Faviet
110 John Chen
111 Ismael Sciarra
112 Jose Manuel Urman
113 Luis Popp
114 Den Raphaely
115 Alexander Khoo
116 Shelli Baida
117 Sigal Tobias
118 Guy Himuro
119 Karen Colmenares
120 Matthew Weiss
121 Adam Fripp
122 Payam Kaufling
123 Shanta Vollman
124 Kevin Mourgos
125 Julia Nayer
126 Irene Mikkilineni
127 James Landry
128 Steven Markle
129 Laura Bissot
130 Mozhe Atkinson
131 James Marlow
132 TJ Olson
133 Jason Mallin
134 Michael Rogers
135 Ki Gee
136 Hazel Philtanker
137 Renske Ladwig
138 Stephen Stiles
139 John Seo
140 Joshua Patel
141 Trenna Rajs
142 Curtis Davies
143 Randall Matos
144 Peter Vargas
145 John Russell
146 Karen Partners
147 Alberto Errazuriz
148 Gerald Cambrault
149 Eleni Zlotkey
150 Peter Tucker
151 David Bernstein
152 Peter Hall
153 Christopher Olsen
154 Nanette Cambrault
155 Oliver Tuvault
156 Janette King
157 Patrick Sully
158 Allan McEwen
159 Lindsey Smith
160 Louise Doran
161 Sarath Sewall
162 Clara Vishney
163 Danielle Greene
164 Mattea Marvins
165 David Lee
166 Sundar Ande
167 Amit Banda
168 Lisa Ozer
169 Harrison Bloom
170 Tayler Fox
171 William Smith
172 Elizabeth Bates
173 Sundita Kumar
174 Ellen Abel
175 Alyssa Hutton
176 Jonathon Taylor
177 Jack Livingston
178 Kimberely Grant
179 Charles Johnson
180 Winston Taylor
181 Jean Fleaur
182 Martha Sullivan
183 Girard Geoni
184 Nandita Sarchand
185 Alexis Bull
186 Julia Dellinger
187 Anthony Cabrio
188 Kelly Chung
189 Jennifer Dilly
190 Timothy Gates
191 Randall Perkins
192 Sarah Bell
193 Britney Everett
194 Samuel McCain
195 Vance Jones
196 Alana Walsh
197 Kevin Feeney
198 Donald OConnell
199 Douglas Grant
200 Jennifer Whalen
201 Michael Hartstein
202 Pat Fay
203 Susan Mavris
204 Hermann Baer
205 Shelley Higgins
206 William Gietz
107 rows selected
===> SQL Query using characters-manipulation functions
select employee_id,
CONCAT(first_name, last_name) EMPLOYEENAME,
job_id,
LENGTH(last_name),
INSTR(last_name,'a') "Contains 'a'?"
from employees
where SUBSTR(job_id, 4)='REP'
Results
EMPLOYEE_ID EMPLOYEENAME JOB_ID LENGTH(LAST_NAME) Contains 'a'?
---------------------- --------------------------------------------- ---------- --------
150 PeterTucker SA_REP 6 0
151 DavidBernstein SA_REP 9 0
152 PeterHall SA_REP 4 2
153 ChristopherOlsen SA_REP 5 0
154 NanetteCambrault SA_REP 9 2
155 OliverTuvault SA_REP 7 4
156 JanetteKing SA_REP 4 0
157 PatrickSully SA_REP 5 0
158 AllanMcEwen SA_REP 6 0
159 LindseySmith SA_REP 5 0
160 LouiseDoran SA_REP 5 4
161 SarathSewall SA_REP 6 4
162 ClaraVishney SA_REP 7 0
163 DanielleGreene SA_REP 6 0
164 MatteaMarvins SA_REP 7 2
165 DavidLee SA_REP 3 0
166 SundarAnde SA_REP 4 0
167 AmitBanda SA_REP 5 2
168 LisaOzer SA_REP 4 0
169 HarrisonBloom SA_REP 5 0
170 TaylerFox SA_REP 3 0
171 WilliamSmith SA_REP 5 0
172 ElizabethBates SA_REP 5 2
173 SunditaKumar SA_REP 5 4
174 EllenAbel SA_REP 4 0
175 AlyssaHutton SA_REP 6 0
176 JonathonTaylor SA_REP 6 2
177 JackLivingston SA_REP 10 0
178 KimberelyGrant SA_REP 5 3
179 CharlesJohnson SA_REP 7 0
202 PatFay MK_REP 3 2
203 SusanMavris HR_REP 6 2
204 HermannBaer PR_REP 4 2
33 rows selected
Oracle 11g: Character-Manipulation Functions
select 'Hello World' from dual;
select CONCAT('Hello World', 'Peah') from dual;
select SUBSTR('Hello World', 1,5) from dual;
select LENGTH('Hello World') from dual;
select INSTR('Hello World', 'W') from dual;
select LPAD('Hello World', 20,'*') from dual;
select RPAD('Hello World', 20,'*') from dual;
select REPLACE('Hello World', 'l','x') from dual;
select TRIM('H' FROM 'Hello World') from dual;
Results
'HELLOWORLD'
------------
Hello World
CONCAT('HELLOWORLD','PEAH')
---------------------------
Hello WorldPeah
SUBSTR('HELLOWORLD',1,5)
------------------------
Hello
LENGTH('HELLOWORLD')
----------------------
11
INSTR('HELLOWORLD','W')
-----------------------
7
LPAD('HELLOWORLD',20,'*')
-------------------------
*********Hello World
RPAD('HELLOWORLD',20,'*')
-------------------------
Hello World*********
REPLACE('HELLOWORLD','L','X')
-----------------------------
Hexxo Worxd
TRIM('H'FROM'HELLOWORLD')
-------------------------
ello World
select CONCAT('Hello World', 'Peah') from dual;
select SUBSTR('Hello World', 1,5) from dual;
select LENGTH('Hello World') from dual;
select INSTR('Hello World', 'W') from dual;
select LPAD('Hello World', 20,'*') from dual;
select RPAD('Hello World', 20,'*') from dual;
select REPLACE('Hello World', 'l','x') from dual;
select TRIM('H' FROM 'Hello World') from dual;
Results
'HELLOWORLD'
------------
Hello World
CONCAT('HELLOWORLD','PEAH')
---------------------------
Hello WorldPeah
SUBSTR('HELLOWORLD',1,5)
------------------------
Hello
LENGTH('HELLOWORLD')
----------------------
11
INSTR('HELLOWORLD','W')
-----------------------
7
LPAD('HELLOWORLD',20,'*')
-------------------------
*********Hello World
RPAD('HELLOWORLD',20,'*')
-------------------------
Hello World*********
REPLACE('HELLOWORLD','L','X')
-----------------------------
Hexxo Worxd
TRIM('H'FROM'HELLOWORLD')
-------------------------
ello World
Oracle 11g: Case-Manipulation Sample
select ('peAhShAn') from dual;
select lower('peAhShAn') from dual;
select upper('peAhShAn') from dual;
select Initcap('peAhShAn') from dual;
Results:
('PEAHSHAN')
------------
peAhShAn
LOWER('PEAHSHAN')
-----------------
peahshan
UPPER('PEAHSHAN')
-----------------
PEAHSHAN
INITCAP('PEAHSHAN')
-------------------
Peahshan
select lower('peAhShAn') from dual;
select upper('peAhShAn') from dual;
select Initcap('peAhShAn') from dual;
Results:
('PEAHSHAN')
------------
peAhShAn
LOWER('PEAHSHAN')
-----------------
peahshan
UPPER('PEAHSHAN')
-----------------
PEAHSHAN
INITCAP('PEAHSHAN')
-------------------
Peahshan
Saturday, August 14, 2010
Oracle 11g: Installing the Oracle 11g Sample Schemas
I wanted to test all SQL scripts without manually creating the tables. So, I downloaded the sample schema from here.
2. You will be receiving a zip file that contains these sql files.
3. You can refer to the article (refer to step 1) for steps on how to install the schema samples. My way is a bit different, I used the 'Oracle SQL Developer'.
Running script through 'Oracle SQL Developer'
1. If you are using the 'Oracle SQL Developer', first thing that you have to do is to connect either as 'SYS' or 'SYSDBA' that has the system's privileges.
2. Open the files that you have unzipped.
3. Press F5
2. You will be receiving a zip file that contains these sql files.
3. You can refer to the article (refer to step 1) for steps on how to install the schema samples. My way is a bit different, I used the 'Oracle SQL Developer'.
Running script through 'Oracle SQL Developer'
1. If you are using the 'Oracle SQL Developer', first thing that you have to do is to connect either as 'SYS' or 'SYSDBA' that has the system's privileges.
2. Open the files that you have unzipped.
- If you are accessing the database via "SYSDBA" , simply run the hr_main.sql..all tables will be created automatically..
- BUT.if you're accessing through "SYSTEM", run hr_cre.sql (tables) first then hr_popul.sql (data)
3. Press F5
Sunday, July 4, 2010
MOSS 2007: Enforce document storage business rules by using Document Policy.
o Create a custom document policy.
o Deploy a document policy by using a policy feature.
o Specify logic for a document policy by using a policy resource
Note: Script may have not entirely workable. Please recheck the steps.
o Expiration: Manage document retention rules by using the expiration feature policy.
o Expiration: Launch a workflow when a document expires.
o Deploy a document policy by using a policy feature.
o Specify logic for a document policy by using a policy resource
- . Create Policy Feature: Code Snippet
- . Create Policy Manifest: Code Snippet
- . Create Policy Configuration Page: Code Snippet
- . Register the Policy Feature in the Policy Catalog.
Note: Script may have not entirely workable. Please recheck the steps.
o Expiration: Manage document retention rules by using the expiration feature policy.
o Expiration: Launch a workflow when a document expires.
Saturday, July 3, 2010
How To Paste Your Code Snippet in Blogspot.
1. Goto Blogcrowds
2. Copy and paste your coding in the text area. Click 'Parse' button.
3. Copy the parsed code snippet, and paste it in the HTML mode in your post.
4. Add <blockquote></blockquote>to any lines to get much elegant look.
OR....
1. Goto to Bloggerindraft. Make sure that you have logged in into your Blogspot account.
2. Follow steps from this site, BlogPandit.
Reference: BlogPandit
2. Copy and paste your coding in the text area. Click 'Parse' button.
3. Copy the parsed code snippet, and paste it in the HTML mode in your post.
4. Add <blockquote></blockquote>to any lines to get much elegant look.
OR....
1. Goto to Bloggerindraft. Make sure that you have logged in into your Blogspot account.
2. Follow steps from this site, BlogPandit.
Reference: BlogPandit
SPContext. What Is It? (More Complex Example)
Perform a query on the current site for cases where item IDs are greater than 100.
SPSiteDataQuery oSiteQuery = new SPSiteDataQuery();
oSiteQuery.Query = "<Where>" +
" <Gt>" +
" <FieldRef Name="ID" />" +
" <Value Type = "Number"> 100 </Value>" +
" </Gt>" +
"</Where>";
oSiteQuery.ViewFields = "<FieldRef Name="Title" />"
DataTable oQueryResults = SPContext.Current.Web.GetSiteData(oSiteQuery);
oQueryResults.TableName = "queryTable";
oQueryResults.WriteXML("C:\queryTable.xml");
SPContext. What Is It?
1. Its a SharePoint Class.
2. It represents the context in an HTTP request.
3. We use SPContext to return context (background / situation / perspective)information about such objects as:
- Current Web App
- Current Site Collection
- Current Site
- Current List
- Current List Item
e.g:
- SPWebApplication WebAppCur = SPContext.Current.WebApplication;
- SPSite SiteCollCur = SPContext.Current.Site;
- SPWeb WebCur = SPContext.Current.Web;
- SPList ListCur = SPContext.Current.List;
Note: Site represents SiteCollection and Web represents Site in SharePoint 2007.
2. It represents the context in an HTTP request.
3. We use SPContext to return context (background / situation / perspective)information about such objects as:
- Current Web App
- Current Site Collection
- Current Site
- Current List
- Current List Item
e.g:
- SPWebApplication WebAppCur = SPContext.Current.WebApplication;
- SPSite SiteCollCur = SPContext.Current.Site;
- SPWeb WebCur = SPContext.Current.Web;
- SPList ListCur = SPContext.Current.List;
Note: Site represents SiteCollection and Web represents Site in SharePoint 2007.
Subscribe to:
Posts (Atom)
