START DATABASE_
ü Data definition language- ddl
Creating table
Dropping table
ü Data manipulation language- dml
Data insertion
1.Programm-
Personal oracle for windows
Start database
2.Programm
Oracle for windows – SQL Plus 3.2
#Username - Scott
#Password - Tiger
1. To see all tables –
SQL>select * from cat; after this sign (;) (Enter) is must to complete the curie.
2. To see content of table-
SQL>select * from emp; [emp-table name]
3. To create new table-
SQL>create table stud(
Stud no number(5),
Stud name varchar2(20),
Stud date date,
Stud sex char (1));
Number (6,2) – 100.50
4. To see structure of table-
SQL>desc stud;
5. Inserting records in table-
SQL>insert into stud values (1,’ayyad tausif’,’30.Jan.87’,’m’);
6. To enter specific record in table-
a) Using null
SQL>insert into stud values (1,null);
b) Insert into stud (stud no) values (1);
7. Inserting records in table using ‘&’ operator –
SQL>insert into stud values (&stud no, ‘stud name’);
SQL>/ -this formula is use for repetition last command.
8. To delete the table –
SQL>drop table stud;
9. Operators & clauses- [using where]
= -Equal to
> -Less than
< -Greater than
! = -Not equal
and -for single record & different field
or
like SQL>select * from emp where ename like ‘A%’;
not like SQL>select * from emp where ename not like ‘S%’;
Between SQL>select * from emp where deptno between 10 and 30;
Not in SQL>select * from emp where deptno not in (20);
In SQL>select * from emp where deptno in (30);
SQL>Select * from emp where deptno in (10,20);
< SQL>select * from emp where sal<200;
> SQL>select * from emp where sal>2000;
>< SQL>select * from emp where sal>’2000’ and <’3000';
SQL>select * from emp where sal>2000 and sal<5000;
= SQL>select * from emp where deptno =10;
SQL>select * from emp where deptno=10 and deptno=20;
* Error (no rows selected b’coz 1 row is not show two deptno, HEREWE CAN’T USE ‘and’ operator.)
SQL>select * from emp where deptno=10 or deptno=30; SQL>select * from emp where hiredate between ’10.jan.03’ and ’01.jan.06’;
# SQL>ed; -to open a notepad & correction.
# Alt_space_c -to close the notepad.
# SQL>/ (enter) -to run corrected curie.
SQL>insert into stud (sname) values (nasir);
SQL>insert into stud (rollno, sname) values (1,’asif’);
SQL>insert into stud values (& rollno,’& sname’,’& address’);
Enter value for rollno: 1
Enter value for sname: tausif
Enter value for address: pune
SQL>/ enter
SQL>create table stud (
2 rollno number (3) primary key,
3 sname varchar2 (20) not null,
4 address varchar2 (30));
Primary key- there is necessity to enter the primary key.
NOT NULL IS A PRIMARY KEY.
SQL>select * from emp order by ename; SORTING
SQL>select * from emp order by ename desc; DESORTING
SQL>Select distinct job from emp;
SQL>L;
IT IS USED FOR REPEATE AGAIN LAST COMMAND.
WE CAN USE L, L2, L3, ETC.,
IF OUR COMMAND WILL BE IN 2/3 ETC., LINE.
FOR SAVE CORRECTIONS AND EXIT NOTPAD USE THESE KEYS: -
Alt-F-S-Alt-F-X.
FUNCTIONS OF ORACLE
NUMERIC
CHARACTER
DATE GROUP LIST CONVERSION OTHER
A. NUMERIC-
a. Average: -
SQL>select avg (sal) from emp;
Returns average value field, ignoring null values.
b. Minimum: -
SQL>select min (sal) from emp;
Returns minimum value of field.
c. Maximum: -
SQL>select max (sal) from emp;
Returns maximum value of field.
d. Count: -
SQL>select count (sal) from emp;
Returns no. of rows where field is not null.
e. Count (*): -
SQL>Select count (*) from emp;
Returns the no. of rows in the table including duplicate & those with null.
f. Sum: -
SQL>select sum (sal) from emp;
Returns sum values of specific field.
g. Absolute: -
SQL>Select abs (sal) from emp;
Returns absolute data in field.
h. Power: -
Syntax_Power (m, n).
Returns m raised to nthpower, n must be an integer.
SQL>Select power (3,2) “raised” from dual;
i. Round: -
Syntax_Round (n, m).
Returns n rounded to m place right of the decimal point.
SQL>Select round (sal, 0) from emp;
O/p_20.
j. Square root: -
Returns square root of field or date. It returns a real result.
SQL>Select sqrt (sal) from emp;
SQL>Select sqrt (9) from dual;
B. CHARACTER: -
a. LOWER: -
SQL>Select lower (ename) from emp;
Returns character with all letters in lowercase.
SQL>Select lower (SMITH) from dual;
b. UPPER: -
SQL>Select upper (ename) from emp;
SQL>Select upper (smith) from dual;
Returns character with all letters in uppercase.
c. INITIAL/INITCAP: -
SQL>Select initcap (ename) from emp;
Returns string with the first letters in uppercase.
d. SUBSTRING: -
SQL>Select substr (ename, 2) from emp;
Returns a portion of character beginning at specified character no.
SQL>Select substr (ename, 2,4) from emp;
Returns operation of string beginning at 2 & 4 character long o/p john__ ohn.
e. Length: -
SQL>Select length (ename) from emp;
Returns the length of character.
Left trim: -
SQL>Select ltrim (ename’A’) from emp;
SQL>Select ltrim (‘aaaaAsad’,’a’) from dual;
Answer- Asad
f. Right trim: -
SQL>Select rtrim (ename’s’) from emp;
SQL>Select rtrim (‘Asadsssss’,’s’) from dual;
g. Left padded: -
SQL>Select lpad (ename, 15,’$’) from emp;
SQL>Select lpad (Smith, 10,’#’) from dual;
Answer- #####Smith
h. Right pad: -
SQL>select rpad (Smith, 10,’*’) from dual;
Answer- Smith*****
SQL>Select rpad (ename, 10,’$’) from emp;
i. Instring: -
SQL>Select ename, instr (ename,’A’) from emp;
SQL>Select ename, replace (ename, ’AL
Ename Replace
SMITH SMITH
SCOTT SCOTT
ALLEN XYLEN
TIGER TIGER
SONAL SONXY
ALTAF XYTAF
SQL>Select ename, translate (ename, ‘AL
Ename Translate
SMITH SMITH
ALLEN XYYEN
MARTIN MXRTIN
C. DATE: -
26TH.December.2006
(Hiredate,’ddth, month, yyyy’),
(Hiredate,’day, month, yyyy),
(Hiredate,’ddth, month, year’),
(Hiredate,’fmdd, month, year’),
(Hiredate,’fmdd, mmth, year’),
(Hiredate,’fmddsp, mm, yysp’).
SQL>select least (1,2,3,4) from dual;
O/p Returns small values.
SQL>select greatest (1,2,3,4,5) from dual;
O/p Returns greatest values.
SQL>Select floor (19.9) from dual;
O/p floor (19.9).
SQL>Select ceil (19.1) from dual;
O/p 20
CONCATE: -
SQL>Select concate (ename,’job is’) from emp;
SQL>Select concate (ename, concate (‘job is, job)) from emp;
SQL>Select ename||’job is’||job from emp;
SOUNDEX: -
SQL>Select * from stud where soundex (sname)=soundex (‘smith’);
Smith_smyth_smeeth_smieth like this….
D. GROUP BY
SQL>select sum (sal) from emp group by deptno;
SQL>Select deptno, sum (sal) from emp group by deptno;
Use of group by clause- such as min, max, sum, avg, etc. is necessary to take with group by clause.
HAVING CLAUSE: -
SQL>select deptno, avg (sal) from emp group by deptno having deptno=10;
ALTER FUNCTION: -
SQL>alter table stud
2 modify (…………
Update function: -
SQL>Update stud set address=’Dhule’;
SQL>Update stud set address=’Nasik
DELETING FIELD: -
SQL>DESC STUD;
SQL>CREATE TABLE TEACH
2 AS SELECT SRNO, SNAME FROM STUD;
SQL>DESC TEACH;
SQL>CREATE TABLE STUD
2 AS SELECT * FROM TEACH;
SQL>DROP TABLE TEACH;
PRIMARY KEY: -
SQL>Create table stud (
2 rno number (3) primarky key,
3 name varchar2 (50),
4 address varchar2 (100));
table created.
TO SAVE THE DATA: -
SQL>COMMITT;
SQL>SLECET DESCRIPTION, SELL_PRICE, SELL_PRICE*15 FROM PRODUCT_MASTER WHERE SELL PRICE>1500;
SQL>SELECT DESCRIPTION, SELL_PRICE, SELL_PRICE*15”NEW_PRICE” FROM PRODUCT_MASTER WHERE SELL_PRICE>1500;
SQL>SELECT * FROM PRODUCT_MASTER ORDER BY DESCRIPTION; (A-Z)/(Z-A)..
Q: - Divide the cost of product ‘540 HDD’ by difference between its price and 100.
SQL>SELECT DESCRIPTION, COST_PRICE, SELL_PRICE-100 FROM PRODUCT_MASTER WHERE DESCRIPTION=’540 HDD’;
SELECT DESCRIPTION, COST_PRICE, SELL_PRICE, SELL_PRICE-100, COST_PRICE/(SELL_PRICE-100) FROM PRODUCT_MASTER WHERE DESCRIPTION=’540 HDD’;
Q: - List all the orders that where canceled in the month of JAN.
SQL>SELECT * SALES_ORDER WHERE S_ORDER_DATE BETWEEN ’01.JAN.96’ AND ’31.JAN.96’ AND ORDER_STATUS=’CANCELED’;
CORRECT METHOD: -
Some changes:
WRONG SENTENCE: ->
SELECT* FORM EMP;
C/from/form;
c/-ma/-mas
Check (client_no.like ‘A%’)
Check (sal !=0)
Check (order_status in (_ _ _ _))
Default ‘N’
Check (billed_yn in (‘Y’,’N’))
SQL>SELECT NAME, S_ORDER_NO, S_ORDER_DATE FROM CLIENT_MASTER, SALES_ORDER WHERE CLIENT_MASTER.CLIENT_NO=SALES_ORDER.CLIENT_NO;
SQL>SELECT NAME||’HAS PLACED ORDER’||S_ORDER_NO||’ON’||S_ORDER_DATE FROM CLIENT_MASTER, SALES_ORDER WHERE CLIENT_MASTER.CLIENT_NO=SALES_ORDER.CLIENT_NO;
Q: -Calculate the average qty sold for each client that has a maximum order value of 15000.00.
SQL>SELECT NAME, SALES_ORDER.S_ORDER_NO, AVG (QTY_DISP), SUM (QTY_DISP*PRODUCT_RATE) FROM CLIENT_MASTER, SALES_ORDER_DETAILS WHERE CLIENT_MASTER.CLIENT_NO=SALES_ORDER.CLIENT_NO AND SALES_ ORDER.S_ORDER_NO=SALES_ORDER_DETAILS.S_ORDER_NO GROUP BY NAME, SALES_ORDER.S_ORDER_NO HAVING SUM (QTY_DISP*PRODUCT_RATE)<=15000;
ITS FINISHED
FORMS DESIGNER
THREE BASIC POINTS: -
Ø BLOCK
Ø CANVACE
Ø WINDOW
TRIGER: - 3 BASIC POINTS
ü FORM LEVEL
ü BLOCK LEVEL
ü FIELD LEVEL
Object navigator: -
Item
Layout editor: -
Forms designer
PL/SQL editor: -
Coding
FOR EVERY EVENT 1 TRIGGER.
FORMS DESIGNER
(DEVELOPER 2000/4.5)
BLOCK_SELECT_CONNECT_OK_SELECT TABLES=ITEM_SELECT COLUMNS=LAYOUT_OK.
LAYOUT EDITORè
OBJECT NAVIGATOR_LOV_CREATE_WRITE SELECT QUERY_OK.
# LOV-PROPERTIESè
- AUTO DESPLAY-TRUE (DEFAULT)
- AUTO SKIP -TRUE (NECESSARY) =>
COLUMN MAPPING =>
q RETURN ITEM_(BLOCK NAME.TEXT BOX)=> OK.
q CODING FOR DESPLAY LOV (PUSH BUTTON)
DECLARE
F:=Boolean;
BEGIN
F:=show_lov (‘client_lov’);
END;
COMPILE_CLOSE_RUN.
CODING FOR WINDOW MAXIMIZESè
TRIGGER LEVELèFRAME LEVEL
TRIGGER NAMEè WHEN NEW FORM INSTANCE.
CODING (PL/SQL)è
TRIGGERè
CREATEè
WHEN NEW FORM INSTANCEè
PROPERTY (FORM_MDI_WINDOW, WINDOW_STATE, MAXIMIZE)
SET_WINDOW_PROPERTY (‘WINDOW 0’,WINDOW_STATE, MAXIMIZE);
TITLE SETè
SET_WINDOW_PROPERTY (FORMS_MDI_WINDOW, TITLE,’THIS PRODUCT IS DEVELOPED BY “ALTAMASH”)
DATEè
TEXT ITEM_ Property set_
Name=s_date
Data type=date
Format mass=dd, month, yy
Default value=$$date$$
Enabled=false
Navigator=false
Base table item=false
FOR ITEM_
Format mass=dd, month, yy
HH: MI A.M.
Default value= $$date time$$
Master details blockè
Create master blockè
Object navigatorè
Blockècreate ionic bollun
Pushèselect details tableè
Master details tabèselect master blockè then joined condition will be generated automatically the click onèOK (save)
# BASE TABLE FALSE ITEM è
TAKE A TEXT BOX FROM TOOLBAR
SET PROPERTY
PROPERTY Value
Name client_name
Max. length 100
BASE TABLE ITEM FALSE (DOUBLE CLICK)
[CHANGE IN LOV] WRITTEN ITEM
# ALERTS è
GO TO OBJECT NAVIGATOR
SELECT ALERTSèCREATE ICONIC BUTTON FROM TOOLBAR.
SET PROPERTY OF ALERTS
PROPERTY VALUE
NAME NEXT RECORDS
TITLE NEXT ENTRY (1 NECESSARY
BUTTON 1 NEXT 2 OPTIONAL)
BUTTON 2 FINISHED
BUTTON 3 CANCLE
MESSAGE DO YOU WANT TO FINISH OR CANCLE..
Display Alertsè
Trigger name=key next item.
Coding- (PL/SQL)è
DECLARE
F number;
BEGIN
F:=show alert (‘next record’)
If F=alert_button 1 then
Next record;
End if;
If F=alert_button 2 then go_item (‘sales_order.executive_query’)
End if;
If F=alert_button 3 then clear_block (no_commit),
And if;
END;
COMPILEè
PUSH_BUTTONèset propertyè
Property Value
Name Executive_query
Iconic True
Icon name Executive_query
MY COMPUTERè D è ORAWIN95 èFORMS45èDEMOSèICONèCHOOSE ICONS
DISPLAY ICONIC BUTTON HELPè
GO TO OBJECT NAVIGATORèCLICK ON CREATE ICONIC BUTTONèCLICK ON FINDèGO TO DèORAWIN95èFORMS45èPL/SQL LIBèHINT PLIèOPEN.
CLICK ON ATTACHèYES.
TRIGGER NAME
WHEN MOUSE ENTERèTRIGGER LVL FORMS LVL
CODING (PL/SQL)
Hint.show button help
When timer-expired
Form lvl
Codingà
Hint. How button help handler;
PUSH BUTTONè CODING
WHEN BUTTON PRESSEDè
CODINGè
1] INSERTè
Create_record;
Commit;
Clear_block (no_commit);
2] EXECUTIVE QUERYè
executive_query;
3] EXITè
exit_form;
4] FIRSTè
first_record;
5] PREVIOUSè
previous_record;
6] LASTè
last_record;
7] DELETEè
delete from client_master where client_no=:client_master.client_no;
clear_block (no_commit);
commit;
8] MODIFYè
update client_master set table’s field=:blockname.block’s textbox
name=:client_master.name
where
client_no=:client_master.client_no;
clear block (no_commit),
commit;
9] LISTè
DECLARE
F boolean
BEGIN
F:=show_lov(‘client_lov’)
END;
10] INSERTè
create_record;
commit;
clear_block (no_commit);
REPORTS DESIGNER
DEVELOPER 2000 REPORTS 2.5
CREATE NEW REPORTS à
1. Click on “Tool” on top menu bar.
2. Popup menu will appear.
3. Select “Data Model Editor” and click.
4. Select query from left vertical toolbar that is click on icon SQL.
5. Cursor will be changed, click on empty space.
6. Query with default name “Q-1” will appear.
7. Right click to “Q-1” go to properties.
8. “Query Q-1” window will appear.
9. Write SQL Query in “Select statement” tab.
SQL is as “select * from emp;”
10.Click on Apply and then OK.
11.Group “4-1” of query “Q-1” will be created in Data Model Window.
12.Then go to ToolàDefault Layout.
13.Default Layout Window will appear. Select “Tabular” from style tab.
14.Click on Data Selection Tab.
15.Select “Down” from “ Repeat” cembalist.
16.You can hide column in Repeat by deselecting required column.
17.You can assign table to each column as you required.
18.Also you can change the width & height.
19.Click on OK.
20.Default report will appear.
21.You can design the report as you wish.
DESIGN REPORT
1. Click on Run Icon Button from toolbar.
2. Parameter forms appear.
3. Click on RUN REPORT in parameter form.
4. Record will be displayed.
DESIGN PARAMETER FORM
1. GO TOà
Tools à Default parameter form.
2. Window will appear.
3. Deselect DESTYPE & DESNAME parameter.
4. Delete all Title & Hint tiat field.
5. Click OK.
6. Parameter form gets changed.
USER PARAMETER
1.Data Modelà
QueryàPropertyàSelect Statementàselect * from client_master where client_no=:client_no;àObject navigatoràGo toàUser parameteràthen open property.
2.Data selection tabà
Enter Queryà
Select client_no from client_master;
Data type change=1st column of above queryàApplyàOKàRun.
2.Data selection tabà
Enter Queryà
Select client_no from client_master;
Data type change=1st column of above queryàApplyàOKàRun.
DIFFICULTIESà UPDATE TABLE COMMAND.
PRIMARY KEY, NULL VALUES
اﻠﮄﻡﺶ ﻋﻠﻯ
No comments:
Post a Comment