SELECT *
FROM USLUGI
WHERE type_us = 'tarif'
ORDERBY STOIMOST
| SHIFR | NAME1 | STOIMOST | TYPE_US |
| 1 | Bezlimit 256 | 350 | tarif |
| 2 | Bezlimit 512 | 500 | tarif |
| 3 | Bezlimit 768 | 600 | tarif |
| 4 | Bezlimit 1024 | 700 | tarif |
| 5 | Bezlimit 2048 | 1200 | tarif |
| 6 | Bezlimit 10000 | 10000 | tarif |
3. Вывести историю оплат заданного пользователя. Например, npi001.
SELECT *
FROM OPLATA
WHERE ID_D = 'npi001'
| ID_KARTI | DATA_VREMYA | SUMMA | ID_D |
| 403827 | 09.12.2010 12:56:58 | 500 | npi001 |
| 356813 | 14.12.2010 22:02:02 | 100 | npi001 |
| 81533 | 16.12.2010 1:02:59 | 100 | npi001 |
| 3 | 03.12.2010 15:48:00 | 100 | npi001 |
| 4 | 04.12.2010 5:02:00 | 100 | npi001 |
| 36 | 04.12.2010 5:03:00 | 200 | npi001 |
| 1 | 03.12.2010 15:47:00 | 500 | npi001 |
| 2 | 03.12.2010 15:47:00 | 200 | npi001 |
4. Найти карту оплаты с заданным номером и паролем.
SELECT *
FROM KARTA
WHERE NOMER = '1'AND PASSWORD_K = '1'
| NOMER | SUMMA | SOSTOYANIE | PASSWORD_K | INN |
| 1 | 500 | 1 | 1 | 7777777777 |
5. Установить статус заданной карты в состояние использована.
UPDATE KARTA
SET SOSTOYANIE = 1
WHERE NOMER = ‘nomer’ AND PASSWORD_K = 'password'
6. Выбрать договор по заданному номеру.
SELECT *
FROM DOGOVOR
WHERE NOMER = 'npi001'
| NOMER | DATE2 | DATE3 | INN | ID_U | MONEY | PASS | STATUS |
| npi001 | 01.11.2010 | 30.04.2013 | 777777777777 | 1 | 1100 | npi001 | 1 |
7. Обновить значение средств на счету в заданном контракте.
UPDATE DOGOVOR
SET MONEY = '100'
WHERE NOMER = 'npi001'
8. Добавить нового пользователя.
INSERT INTO USERI VALUES(14,2345,'Ф. И. О.','Бомж','1234567')
9. Выбрать все номера договоров упорядочив по возрастанию.
SELECT NOMER
FROM DOGOVOR
ORDER BY NOMER
| npi001 |
| npi002 |
| npi003 |
| npi004 |
| npi005 |
| npi006 |
| npi007 |
| npi008 |
| npi009 |
| npi010 |
| npi011 |
| npi012 |
| npi013 |
10. Выдать информацию о пользователе, включая информацию по договору.
SELECT *
FROM USERI,DOGOVOR
WHERE USERI.ID = DOGOVOR.ID_U AND DOGOVOR.NOMER = 'npi001’
| TELEPHONE | NOMER | DATE2 | DATE3 | INN | ID_U | MONEY | PASS | ST |
| 89518477875 | npi001 | 01.11.2010 | 30.04.2013 | 77777777 | 1 | 04.01.1903 | npi001 | 1 |
11. Выдать список услуг с количеством клиентов, которые подключены здесь.
| NAME1 | NUMBER |
| Bezlimit 1024 | 2 |
| Bezlimit 2048 | 2 |
| Bezlimit 256 | 3 |
| Bezlimit 768 | 3 |
| Bezlimit 512 | 3 |
| limit | 13 |
12. Какая услуга ни разу не использована?
SELECT SHIFR, NAME1
FROM USLUGI d1
WHERENOTEXISTS (
SELECT USLUGI.SHIFR
FROM USLUGA_DOGOVOR, USLUGI
WHERE d1.SHIFR = USLUGA_DOGOVOR.SHIFR)
| SHIFR | NAME1 |
| 06.01.1900 | Bezlimit 10000 |
13. Какая прибыль в этом месяце?
SELECTSUM(MONEY) AS "Profit"
FROM DEBIT
WHERE TO_CHAR(TIME1, 'mm.yyyy') = TO_CHAR(sysdate, 'mm.yyyy')
Результат запроса: 1260.
14. Максимальное число байт принято от куда?
SELECT TIME1, WITH_ADRESS, WITH_PORT, BAIT, ip.nomer
FROM IP
WHERE BAIT = (SELECT MAX(BAIT)
FROM IP
WHERE IP.WITH_INTERFASE = 5)
| TIME1 | WITH_ADRESS | WITH_PORT | BAIT | NOMER |
| 21-NOV-10 10.56.00 | 188.134.7.73 | 30128 | 95655656 | npi007 |
15. Выдать полную информацию о клиентах.
SELECT d1.NOMER, d1.money, d4.name1, d1.DATE2, d1.DATE3, d2.fio,
d2.pasport, d2.registration, d2.telephone
FROM DOGOVOR d1, USERI d2 , USLUGA_DOGOVOR d3, USLUGI d4
WHERE (d1.id_u = d2.id) AND d3.nomer = d1.nomer
AND d4.shifr = d3.shifr AND d4.type_us = 'tarif'
| NOMER | MO | NAME1 | DATE2 | DATE3 | FIO | PASPOR | REGISTRATI | TELEPHONE |
| npi013 | 0 | Bezlimit 512 | 17.12.2010 | 17.12.2011 | sdg | 235456 | dhdfe4 | 3245 |
| npi001 | 1100 | Bezlimit 256 | 01.11.2010 | 30.04.2013 | Ivanov Ivan Ivanovich | 705631 | Troickaya 9 | 89518477875 |
| npi002 | 0 | Bezlimit 256 | 23.11.2010 | 23.11.2012 | Zlatous Eduard Petrovich | 7056316 | Troickaya 88 | 89518477872 |
| npi003 | 0 | Bezlimit 512 | 23.11.2010 | 28.07.2012 | Veri Big Evil | 70563162 | Troickaya 77 | 89518477873 |
| npi004 | 0 | Bezlimit 768 | 23.11.2010 | 17.02.2012 | Sv Lol | 70563163 | Troickaya 12 | 89518477877 |
| npi005 | 0 | Bezlimit 1024 | 02.11.2010 | 22.10.2011 | Buga ga ga | 70563164 | Troickaya 33 | 89518477877 |
| npi006 | 0 | Bezlimit 2048 | 25.11.2010 | 28.07.2012 | F I O | 70563165 | Engelsa 88 | 89518477875 |
| npi007 | 0 | Bezlimit 768 | 11.11.2010 | 28.07.2012 | Adolf Gitler | 70563166 | AD 666/2 | 89518477871 |
| npi008 | 0 | Bezlimit 1024 | 09.11.2010 | 28.07.2012 | Ktylxy | 70563167 | Engelsa 66 | 89518477879 |
| npi009 | 0 | Bezlimit 512 | 04.11.2010 | 28.07.2012 | Chikotilo | 70563167 | AD 666/1 | 89518477810 |
| npi010 | 0 | Bezlimit 2048 | 16.11.2010 | 28.07.2012 | Vladimir Vladimirovich Purin | 70563321 | Rai 777 | 89518477812 |
| npi011 | 0 | Bezlimit 256 | 06.12.2010 | 06.12.2011 | Spanch Bob | 123123 | Dno Okeana | 1234578 |
| npi012 | 0 | Bezlimit 768 | 06.12.2010 | 06.12.2011 | sdjfhgsdf | 123112 | sfsdfsdf | 1231231 |
16. Вывести отчет о списании средств с абонента
SELECT *
FROM DEBIT
WHERE ID_D = 'npi001 ’
| TIME1 | MONEY | WHY | ID_D |
| 23.12.2010 | 10 | xz | npi001 |
| 23.12.2010 | 50 | usluga | npi001 |
| 31.12.2010 | 100 | tarif | npi001 |
17. Сколько и на какие порты принималась информация в сумме?
| WITH_PORT | Trafic Mb |
| 20.03.1900 | 2184,57486820221 |
| 04.05.2024 | 1826,659935 |
| 26.06.1982 | 1188,77274 |
| 5892 | 1152,239972 |
| 41178 | 746,3213768 |
| 61468 | 613,3521423 |
| 63499 | 554,7282238 |
| 61655 | 465,7722521 |
| 49977 | 400,795764 |
| 25090 | 394,2087393 |
| 20541 | 337,046443 |
| 35123 | 306,5121174 |
| 56160 | 294,1754141 |
| 24978 | 281,4205179 |
| 52618 | 232,6606636 |
| 17439 | 216,7222147 |
| 36701 | 208,5684862 |
| 18858 | 207,517004 |
| 17355 | 199,2175474 |
| 46021 | 169,297472 |
| 40140 | 168,2296801 |
| 35691 | 167,8863907 |
| 54613 | 139,9933538 |
| 38787 | 131,4555883 |
| 38512 | 127,1801758 |
| 64503 | 113,3399925 |
| 61000 | 103,9505711 |
| 33713 | 102,8360825 |
| 37099 | 89,22577858 |
| 24293 | 88,4114399 |
18. Какой договор истекает в текущем месяце?
SELECT d1.NOMER, d1.DATE2, d1.DATE3, d2.fio, d2.pasport,d2.registration, d2.telephone
FROM DOGOVOR d1, USERI d2
WHERE d1.id_u = d2.id AND (MONTHS_BETWEEN(sysdate, date3) < 0) AND ( MONTHS_BETWEEN(sysdate, date3) > -1)
| NOMER | DATE2 | DATE3 | FIO | PASPORT | REGISTRATION | TELEPHONE |
| npi013 | 17.12.2010 | 27.12.2010 | sdg | 235456 | dhdfe4 | 3245 |
19. Какой договор уже истек?
SELECT d1.NOMER, d1.DATE2, d1.DATE3, d2.fio, d2.pasport,d2.registration, d2.telephone
FROM DOGOVOR d1, USERI d2
WHERE (d1.id_u = d2.id) AND (MONTHS_BETWEEN(date3, sysdate) < 0)
| NOMER | DATE2 | DATE3 | FIO | PASPORT | REGISTRATION | TELEPHONE |
| npi012 | 06.12.2009 | 09.12.2010 | sdjfhgsdf | 123112 | sfsdfsdf | 1231231 |
20. От куда и во сколько принималась информация?
SELECT *
FROM "IP"
WHERE with_interfase=5
| TIME1 | WITH_ADRES | W_P | ON_ADRESS | O_P | BAIT | NOMER |
| 16-NOV-10 01.53. | 205.188.8.238 | 443 | 109.106.195.31 | 7872 | 40 | npi001 |
| 16-NOV-10 01.54. | 195.239.111.21 | 5222 | 109.106.195.31 | 7883 | 280 | npi001 |
| 16-NOV-10 01.54. | 205.188.8.238 | 443 | 109.106.195.31 | 7872 | 40 | npi001 |
| 16-NOV-10 01.55. | 65.55.158.118 | 3544 | 109.106.195.31 | 58497 | 137 | npi001 |
| 16-NOV-10 01.53. | 109.234.153.37 | 5222 | 109.106.195.31 | 7881 | 190 | npi001 |
| 16-NOV-10 01.54. | 195.49.206.219 | 55928 | 109.106.195.31 | 44758 | 768 | npi001 |
| 16-NOV-10 01.54. | 95.139.188.235 | 26871 | 109.106.195.31 | 44758 | 116 | npi001 |
| 16-NOV-10 01.54. | 65.55.158.118 | 3544 | 109.106.195.31 | 58497 | 137 | npi001 |
| 16-NOV-10 01.54. | 195.49.206.219 | 63698 | 109.106.195.31 | 44758 | 96 | npi001 |
| 16-NOV-10 01.54. | 109.234.153.37 | 5222 | 109.106.195.31 | 7881 | 280 | npi001 |
| 16-NOV-10 01.55. | 205.188.8.238 | 443 | 109.106.195.31 | 7872 | 40 | npi001 |
| 16-NOV-10 01.55. | 195.239.111.21 | 5222 | 109.106.195.31 | 7883 | 400 | npi001 |
| 16-NOV-10 01.55. | 65.55.158.118 | 3544 | 109.106.195.31 | 58497 | 137 | npi001 |
| 16-NOV-10 01.55. | 77.37.171.143 | 64200 | 109.106.195.31 | 44758 | 116 | npi001 |
| 16-NOV-10 01.56. | 95.139.188.235 | 27309 | 109.106.195.31 | 44758 | 152 | npi001 |
| 16-NOV-10 01.56. | 195.239.111.21 | 5222 | 109.106.195.31 | 7883 | 320 | npi001 |
| 16-NOV-10 01.55. | 95.139.188.235 | 26871 | 109.106.195.31 | 44758 | 116 | npi001 |
| 16-NOV-10 01.56. | 91.209.196.174 | 80 | 109.106.195.31 | 7930 | 140885 | npi001 |
21. Сколько пользователи скачали?
SELECT NOMER, SUM(BAIT)/(1024*1024) AS "Trafic"
FROM "IP"
WHERE IP.WITH_INTERFASE = 5
GROUPBY NOMER
ORDERBY NOMER
| NOMER | Trafic |
| npi001 | 23.03.1900 |
| npi002 | 38,4699297 |
| npi003 | 17,7961731 |
| npi004 | 1215,42658 |
| npi005 | 84,3097162 |
| npi006 | 1054,26295 |
| npi007 | 2785,73673 |
| npi008 | 9488,6638 |
| npi009 | 472,377748 |
| npi010 | 321,522787 |
22. Какой клиент принес больше всего прибыли?
SELECT *
FROM (SELECTSUM(SUMMA) AS "MONEY", id_d
FROM OPLATA
GROUPBY ID_D
ORDERBY1/MONEY) d
WHERE MONEY = (SELECTMAX(SUM(SUMMA))
FROM OPLATA
GROUPBY ID_D
)
| MONEY | ID_D |
| 1800 | npi001 |
23. Какие клиенты еще не принесли прибыли?
SELECT NOMER, FIO
FROM DOGOVOR INNERJOIN USERI ON DOGOVOR.ID_U = USERI.ID
WHERE NOMER notin
(SELECT id_d
FROM OPLATA
GROUPBY ID_D)
| NOMER | FIO |
| npi013 | sdg |
| npi011 | Spanch Bob |
| npi012 | sdjfhgsdf |
24. Какая услуга подключена у каждого пользователя?