Добавил:
Novilit
Опубликованный материал нарушает ваши авторские права? Сообщите нам.
Вуз:
Предмет:
Файл:log5
.txt 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;
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
---------- ------------------------------ ----------
--------------------------------------------------
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
---------- ------------------------------ ----------
--------------------------------------------------
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
---------- ------------------------------ ----------
--------------------------------------------------
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
---------- ------------------------------ ----------
--------------------------------------------------
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;