log5

SQL> connect system/password
Connected.
SQL> grant alter on new_table to user;

Grant succeeded.

SQL> connect user/password;
Connected.
SQL> select*from people






SQL> select*from people;
ID NAME AGE
---------- ------------------------------ ----------
EMAIL
--------------------------------------------------
PASSWD FUNC
-------------------------------------------------- ---------------
1 Vlad 23
vladislav@mail.ry
qwerty doctor

2 Masha 19
mary@mail.ru
asdfg teacher

3 Denis 20
denis@mail.ru
zxcvb engineer

4 Tanya 25
tatyana@mail.ru
12345 policeman

5 Sergey 25
sergey@mail.ru
67890 programmer


SQL> select avg(age) from new_table;
AVG(AGE)
----------
22.4

SQL> select*from new_table where age>all(select avg(age) from new_table);

ID NAME AGE
---------- ------------------------------ ----------
EMAIL
--------------------------------------------------
PASSWD FUNC
-------------------------------------------------- ---------------
1 Vlad 23
vladislav@mail.ry
qwerty doctor

4 Tanya 25
tatyana@mail.ru
12345 policeman

5 Sergey 25
sergey@mail.ru
67890 programmer



SQL> select (age*2)from new_table where age<=all(select avg(age) from people);
(AGE*2)
----------
46
50
50

SQL> update new_table
2 set age=age*2
3* where age<=all(select avg(age)from new_table);
3 rows updated.


SQL> select*from new_table;
ID NAME AGE
---------- ------------------------------ ----------
EMAIL
--------------------------------------------------
PASSWD FUNC
-------------------------------------------------- ---------------
1 Vlad 46
vladislav@mail.ry
qwerty doctor

2 Masha 19
mary@mail.ru
asdfg teacher

3 Denis 20
denis@mail.ru
zxcvb engineer

4 Tanya 50
tatyana@mail.ru
12345 policeman

5 Sergey 50
sergey@mail.ru
67890 programmer

SQL> rollback;

Rollback complete.


SQL> select*from new_table;

ID NAME AGE
---------- ------------------------------ ----------
EMAIL
--------------------------------------------------
PASSWD FUNC
-------------------------------------------------- ---------------
1 Vlad 23
vladislav@mail.ry
qwerty doctor

2 Masha 19
mary@mail.ru
asdfg teacher

3 Denis 20
denis@mail.ru
zxcvb engineer

4 Tanya 25
tatyana@mail.ru
12345 policeman

5 Sergey 25
sergey@mail.ru
67890 programmer

SQL> alter table new_table
2* add day_of_birth integer;
Table NEW_TABLE altered.

SQL> update new_table
2 set day_of_birth=16
3 where id=1;


SQL> update new_table
2 set day_of_birth=23
3* where id=2;



SQL> update new_table
2 set day_of_birth=19
3* where id=3;



SQL> update new_table
2 set day_of_birth=1
3* where id=4;



SQL> update new_table
2 set day_of_birth=29
3* where id=5;
1 row updated.

SQL> select age, day_of_birth from new_table;

AGE DAY_OF_BIRTH
---------- ------------
23 16
19 23
20 19
25 1
25 29


SQL> select age from new_table
2 intersect
3* select day_of_birth from new_table;
AGE
----------
23
19



SQL> spool out;