SQL> select *from persons;
no rows selected
SQL> insert into person
values('ramu',35); 1 row
created.
SQL> insert into person
values('ravi',35); 1 row
created.
SQL> select *from person;
NAME AGE
-------------------- ----------
ramu 35
ravi 35
SQL> commit; Commit complete.
SQL> savepoint ramu; Savepoint created.
SQL> insert into person
values('rajesh',65);
1 row created.
SQL> select * from person;
NAME AGE
-------------------- ----------
ramu 35
ravi 35
rajesh 65
SQL> rollback to ramu;
Rollback complete.
SQL> select *from person;
NAME AGE
-------------------- ----------
ramu 35
ravi 35
SQL> select *from persons;
no rows selected
SQL> desc persons;
Name
Null? Type
-----------------------------------------
-------- ----------------------------
PNAME
VARCHAR2(20)
PAGE NOT NULL
NUMBER(3)
SQL> drop table persons;
Table dropped.
SQL> select * from person;
NAME AGE
-------------------- ----------
ramu 35
ravi 35
SQL> alter table person modify age
number(4);
Table altered.
SQL> desc person;
Name
Null? Type
-----------------------------------------
-------- ----------------------------
NAME
VARCHAR2(20)
AGE NOT NULL
NUMBER(4)
alter command comes with 4 options
2 alter-modify
3 alter-add
4 alter-rename
5 alter-drop;
SQL> alter table person rename age
to xage;
alter table person rename age to xage
*
ERROR at line 1:
ORA-14155: missing PARTITION or
SUBPARTITION keyword
SQL> alter table person rename to
persons; Table altered.
SQL> alter table persons add(mobile
number(10));
Table altered.
SQL> desc persons;
Name
Null? Type
-----------------------------------------
-------- ----------------------------
NAME
VARCHAR2(20)
AGE NOT NULL
NUMBER(4)
MOBILE
NUMBER(10)
SQL> alter table persons drop
column mobile;
Table altered.
SQL> desc persons;
Name
Null? Type
-----------------------------------------
-------- ----------------------------
NAME
VARCHAR2(20)
AGE NOT NULL
NUMBER(4)
SQL> flashback table persons to
before drop;
flashback table persons to before drop
*
ERROR at line 1:
ORA-38305: object not in RECYCLE BIN
SQL> drop table persons;
Table dropped.
SQL> purge table persons;
purge table persons
*
ERROR at line 1:
ORA-38307: object not in RECYCLE BIN
SQL> select seq1.nextval from dual;
NEXTVAL
----------
25
SQL> select seq1.currval from dual;
CURRVAL
----------
25
SQL> create sequence seq2 minvalue
1 maxvalue 1000 start with 1 increment by -2
cache 5;
Sequence created.
SQL> select seq1.currval from dual;
CURRVAL
----------
25
SQL> select seq2.currval from dual;
select seq2.currval from dual
*
ERROR at line 1:
ORA-08002: sequence SEQ2.CURRVAL is
not yet defined in this session
SQL> select seq2.nextval from dual;
NEXTVAL
----------
1
SQL> select seq2.nextval from dual;
select seq2.nextval from dual
*
ERROR at line 1:
ORA-08004: sequence SEQ2.NEXTVAL goes
below MINVALUE and cannot be instantiated
SQL> select seq2.currval from dual;
CURRVAL
----------
1
SQL> select * from dept;
DEPTNO DNAME
NOEMP
---------- --------------------
----------
1 production 22
2 marketing 30
3 finance 15
4 auditing 20
5 hr 10
6 management 25
6 rows selected.
SQL> create synonym ramusyn for
dept;
Synonym created.
SQL> select * from ramusyn;
DEPTNO DNAME
NOEMP
---------- --------------------
----------
1 production 22
2 marketing 30
3 finance 15
4 auditing 20
5 hr 10
6 management 25
6 rows selected.
SQL> select * from ramusyn;
DEPTNO DNAME
NOEMP
---------- --------------------
----------
1 production 22
2 marketing 30
3 finance 15
4 auditing 20
5 hr 10
6 management 25
6 rows selected.
SQL> create table un(accno
number(3) unique, not null);
create table un(accno number(3)
unique, not null)
*
ERROR at line 1:
ORA-00904: : invalid identifier
SQL> create table un(accno
number(3) unique);
Table created.
SQL> create table un1(accno
number(3) not null);
Table created.
SQL> insert into un1
values(&accno);
Enter value for accno: 100
old
1: insert into un1 values(&accno)
new
1: insert into un1 values(100)
1 row created.
SQL> /
Enter value for accno: 100
old
1: insert into un1 values(&accno)
new
1: insert into un1 values(100)
1 row created.
SQL> select * from un1;
ACCNO
----------
100
100
SQL> /
ACCNO
----------
100
100
SQL> insert into un1 values(&accno);
Enter value for accno:
old
1: insert into un1 values(&accno)
new
1: insert into un1 values()
insert into un1 values()
*
ERROR at line 1:
ORA-00936: missing expression
SQL> insert into un
values(&accno);
Enter value for accno: 100
old
1: insert into un values(&accno)
new
1: insert into un values(100)
1 row created.
SQL> /
Enter value for accno: 100
old
1: insert into un values(&accno)
new
1: insert into un values(100)
insert into un values(100)
*
ERROR at line 1:
ORA-00001: unique constraint
(SYSTEM.SYS_C004030) violated
SQL> /
Enter value for accno:
old
1: insert into un values(&accno)
new
1: insert into un values()
insert into un values()
*
ERROR at line 1:
ORA-00936: missing expression
SQL> /
Enter value for accno: null
old
1: insert into un values(&accno)
new
1: insert into un values(null)
1 row created.
SQL> select *from un;
ACCNO
----------
100
SQL> ed
SP2-0110: Cannot create save file
"afiedt.buf"
SQL> select *from master;
no rows selected
SQL> desc master;
Name
Null? Type
-----------------------------------------
-------- ----------------------------
ACCNO NOT NULL
NUMBER
NAME
VARCHAR2(20)
ADDRESS
VARCHAR2(20)
AMOUNT
NUMBER
SQL> create table transc(accno
number references master (accno), trans
varchar2
(1), amount number);
Table created.
SQL> *
ERROR at line 1:
ORA-00907: missing right parenthesis
A live
demonstration for the primary key, foreign keys and not null keys
SQL> CREATE TABLE CUSTOMERS(
2 ID INT
NOT NULL,
3 NAME VARCHAR (20) NOT NULL,
4 AGE INT NOT NULL,
5 ADDRESS CHAR (25) ,
6 SALARY DECIMAL (18, 2),
7 PRIMARY KEY (ID)
8 );
Table created.
SQL> CREATE TABLE ORDERS (
2 ID INT NOT NULL,
3 DATE DATETIME, //here the problem is coming
4 CUSTOMER_ID INT
references CUSTOMERS(ID),
5 AMOUNT double,
6 PRIMARY KEY (ID)
7 );
DATE DATETIME, ERROR at line 3: ORA-00904: : invalid identifier
SQL> create table orders(id int not
null, c_id int references customers(id), pri
mary key(id));
Table created.
SQL> desc customers;
Name
Null? Type
-----------------------------------------
-------- ----------------------------
ID NOT
NULL NUMBER(38)
NAME NOT NULL
VARCHAR2(20)
AGE NOT NULL
NUMBER(38)
ADDRESS
CHAR(25)
SALARY
NUMBER(18,2)
SQL> desc orders;
Name
Null? Type
----------------------------------------- --------
----------------------------
ID NOT
NULL NUMBER(38)
C_ID
NUMBER(38)
SQL> insert into customers
values(&id , '&name' , &age , '&address' , &salary);
Enter value for id: 10
Enter value for name: ramu
Enter value for age: 36
Enter value for address: jimma
Enter value for salary: 1000
old
1: insert into customers values(&id , '&name' , &age ,
'&address' , &salary)
new
1: insert into customers values(10 , 'ramu' , 36 , 'jimma' , 1000)
1 row created.
SQL> insert into orders
values(12345, 11);
insert into orders values(12345, 11)
*ERROR at line 1: ORA-02291: integrity
constraint (SYSTEM.SYS_C004040) violated - parent key not found
SQL> insert into orders
values(12345, 10);
1 row created.
కామెంట్లు లేవు:
కామెంట్ను పోస్ట్ చేయండి