SQL> select * from foo;
ID VAL
---------- ----------
1 1
2 3
3 3
SQL> set linesize 200
SQL> set pagesize 30
SQL> alter table foo add (key int);
Table altered.
SQL> update foo
2 set key = id * val
3 ;
3 rows updated.
SQL> select * from foo;
ID VAL KEY
---------- ---------- ----------
1 1 1
2 3 6
3 3 9
SQL> alter table foo add constraint k3 unique(key);
Table altered.
SQL> insert into foo values(4,3,9);
insert into foo values(4,3,9)
*
ERROR at line 1:
ORA-00001: unique constraint (ALEX.K3) violated
SQL> alter table foo
2 disable constraint k3;
Table altered.
SQL> insert into foo values(4,3,9);
1 row created.
SQL> select * from foo;
ID VAL KEY
---------- ---------- ----------
4 3 9
1 1 1
2 3 6
3 3 9
SQL> alter table foo enable constraint k3;
alter table foo enable constraint k3
*
ERROR at line 1:
ORA-02299: cannot validate (ALEX.K3) - duplicate keys found
SQL> delete from foo
2 where id=4;
1 row deleted.
SQL> select * from foo;
ID VAL KEY
---------- ---------- ----------
1 1 1
2 3 6
3 3 9
SQL> alter table foo enable constraint k3;
Table altered.
SQL> insert into foo values(4,3,9);
insert into foo values(4,3,9)
*
ERROR at line 1:
ORA-00001: unique constraint (ALEX.K3) violated
SQL> rem NOW WE WILL TRY DATES!
SQL> create table times( id int primary key, stamp date);
Table created.
SQL> clear scr
SQL> insert into times values(1, '2:34pm');
insert into times values(1, '2:34pm')
*
ERROR at line 1:
ORA-01843: not a valid month
SQL> insert into times values(1, '10-02-2012');
insert into times values(1, '10-02-2012')
*
ERROR at line 1:
ORA-01843: not a valid month
SQL> insert into times values(1, 'OCT-02-2012');
insert into times values(1, 'OCT-02-2012')
*
ERROR at line 1:
ORA-01858: a non-numeric character was found where a numeric was expected
SQL> insert into times values(1, '02-OCT-2012');
1 row created.
SQL> select * from times;
ID STAMP
---------- ---------
1 02-OCT-12
SQL> clear scr
SQL> insert into times values(2, to_date('5:36','HH:MI'));
1 row created.
SQL> select * from times;
ID STAMP
---------- ---------
1 02-OCT-12
2 01-OCT-12
SQL> select id, to_char(stamp, 'HH:MI') from times;
ID TO_CH
---------- -----
1 12:00
2 05:36
SQL> select id, to_char(stamp, 'MON====> HH:MI') from times;
ID TO_CHAR(STAMP,
---------- --------------
1 OCT====> 12:00
2 OCT====> 05:36
SQL>
SQL> select * from foo;
ID VAL KEY
---------- ---------- ----------
1 1 1
2 3 6
3 3 9
SQL> upadate foo
SP2-0734: unknown command beginning "upadate fo..." - rest of line ignored.
SQL> update foo
2 set val = 5;
3 rows updated.
SQL> slect * from foo;
SP2-0734: unknown command beginning "slect * fr..." - rest of line ignored.
SQL> select * from foo;
ID VAL KEY
---------- ---------- ----------
1 5 1
2 5 6
3 5 9
SQL> update foo
2 set val = id;
3 rows updated.
SQL> select * from foo;
ID VAL KEY
---------- ---------- ----------
1 1 1
2 2 6
3 3 9
SQL> update foo set
2 val = id+(val*key) - 7;
3 rows updated.
SQL> select * from foo;
ID VAL KEY
---------- ---------- ----------
1 -5 1
2 7 6
3 23 9
SQL> update foo
2 set val = 0
3 where id = 1;
1 row updated.
SQL> select * from foo;
ID VAL KEY
---------- ---------- ----------
1 0 1
2 7 6
3 23 9
SQL> update foo
2 set val = id+key
3 where id =1 OR (id > 1 AND key < 7);
2 rows updated.
SQL> select * from foo;
ID VAL KEY
---------- ---------- ----------
1 2 1
2 8 6
3 23 9
SQL> update foo
2 set val = 10
3 where NOT (id > 2);
2 rows updated.
SQL> select * from foo;
ID VAL KEY
---------- ---------- ----------
1 10 1
2 10 6
3 23 9
SQL> update foo
2 set val = id*key - val, key = key+1
3 ;
3 rows updated.
SQL> select * from foo;
ID VAL KEY
---------- ---------- ----------
1 -9 2
2 2 7
3 4 10
SQL> update foo
2 set key = key+100, val = key -1;
3 rows updated.
SQL> select * from foo;
ID VAL KEY
---------- ---------- ----------
1 1 102
2 6 107
3 9 110
SQL> select table_name from user_tables;
TABLE_NAME
------------------------------
CONTINENTS
LIST
MODELS
CAMPUSES
CARMAKERS
COUNTRIES
MAKES
CARDATA
SHARKS
AIRPORTS
MARATHON
FACULTY
DISCENR
DISCIPLINES
ENROLLMENTS
DEGREES
FEES
FLIGHTS
AIRLINES
TEACHERS
LEAGUES
ATTENDANCE
ST01
ROOMS
CUSTOMERS
BEERS
BEERS1
TABLE_NAME
------------------------------
TIMES
BARS
DRINKS
LOCATIONS
ALLELES
PNEXT
SPECIES
ATTRIBUTES
TYPES
ANEW
POKEMON
EVOLUTION
REACTORS
RECEIPTS
ITEMS
EARTHQUAKES
LOCATION
EMPLOYEE
GOODS
BIGQUAKES
FOO
TIMETEST
TOURNAMENT
TST1
EMPLOYEES
52 rows selected.
SQL> select * from sharks;
TAG NAME HEIGHT WEIGHT CAUGHT TIMESTAMP
---------- -------------------- ---------- ---------- --------- ---------
101 Bob 110 400
107 Mary 110 404
104 Pat 100
202 Tim 400
102 Sue 120 600
SQL> delete from sharks;
5 rows deleted.
SQL> select * from sharks;
no rows selected
SQL> select * from foo;
ID VAL KEY
---------- ---------- ----------
1 1 102
2 6 107
3 9 110
SQL> delete from foo
2 where id * val < key/10;
1 row deleted.
SQL> select * from foo;
ID VAL KEY
---------- ---------- ----------
2 6 107
3 9 110
SQL> insert into foo value(100, 12, 34);
insert into foo value(100, 12, 34)
*
ERROR at line 1:
ORA-00928: missing SELECT keyword
SQL> insert into foo values (100,12,34);
1 row created.
SQL> insert into foo values (120, 40, 54);
1 row created.
SQL> select * from foo;
ID VAL KEY
---------- ---------- ----------
100 12 34
120 40 54
2 6 107
3 9 110
SQL> delete from foo
2 where id <200 and id >99;
2 rows deleted.
SQL> select * from foo;
ID VAL KEY
---------- ---------- ----------
2 6 107
3 9 110
SQL> exit