План исследований.

Страницы работы

9 страниц (Word-файл)

Скачать файл

Содержание работы

План исследований.

Простые запросы

Выбор всех строк и столбцов

Select * from Phones

Выбор неповторяющихся строк (distinct)

Select distinct Number from Charges

Выбор с сортировкой (используя ORDER BY для 1, 2, …,5 столбцов)

Select * from Phones ORDER BY Number
Select * from Phones ORDER BY Number, ATS_number
Select * from Phones ORDER BY Number, ATS_number, Class
Select * from Phones ORDER BY Number, ATS_number, Class, Type
Select * from Phones ORDER BY Number, ATS_number, Class, Type, Priv_type

Выбор строк, удовлетворяющих условию (используя WHERE)

Простое условие >,<,= (насколько эффективно реализован полный перебор)

Select * from Phones where Number >539462
Select * from Phones where Number <731459
Select * from Phones where Number = 123456

Between А and B (сравнение по реализации с A<= x <=B)

Select * from Phone_class where Class between 345821 and 689321
Select * from Phone_class where Class >= 345821 and Class <=689321

Like (длина шаблона > 5)

Select * from Abonents where Surname like ‘Surname_51550%’
Select * from Abonents where Surname like ‘Surname_5312%’
Select * from Abonents where Surname like ‘Surname_105%’
Select * from Abonents where Surname like ‘Surname_2%’

In (сравнение по реализации с x=A,x=B,…,x=Z)

Select * from Regions where Region_code in (12, 147, 1236, 18945, 123456, 10254, 159357, 14789, 12369, 14789)
Select * from Regions where

Region_code=12 or Region_code=147 or Region_code=1236 or Region_code=18945 or Region_code=123456 or Region_code=10254 or Region_code=159357 or Region_code=14789 or Region_code=12369 or Region_code=14789.

Использование функций

Время выполнения простой функции (sum, count, avg, min, max)

Select sum(Off_code) AS Result from Phones
Select count (*) AS Result from ATS_type
Select avg(Off_city_code) AS Result from Phones
Select min(Number) AS Result from Abonents
Select max(Number) AS Result from Abonents

Время выполнения комбинации простых функций (2-3 функции)

Select max (Number), min (Number) from Phones
Select avg(Indebtedness),min(Indebtedness), max(Indebtedness) from Charges
Select max(length(Surname) + length (Name)), avg(Priv_exist) from Queue
Select count(*), sum(ATS_number), max(Type), min(Region_code), avg(Free_phones) from ATS

Использование статистических функций

стандартное отклонение для значений в указанном столбце

Select stdev(Number) from Phones
Select stdev(Number) from Phones where Number >= (select avg(Number) from Phones)
Select stddev(Number) from Phones (DB2)
Select stddev(Number) from Phones where Number >= (select avg(Number) from Phones) (DB2)

несмещенная оценка дисперсии

Select var(Number) from Phones (SQL Server)
Select var(Number) from Phones where Number <= (select avg(Number) from Phones) (SQL Server)
Select variance (Number) from Phones (Informix)
Select variance (Number) from Phones where Number <= (select avg(Number) from Phones) (Informix)
Select variance (Number) from Phones (DB2)
Select variance (Number) from Phones where Number <= (select avg(Number) from Phones) (DB2)

Группировка

Группировка 1, 2,…, 5 столбцов (используя GROUP BY)

1 столбец (число строк 10⁵, 3*10⁵, 5*10⁵, 7*10⁵, 8*10⁵)

Select ATS_number, avg(Number), max(Number),min(Number) from Phones where Number <200000 group by ATS_number
Select ATS_number, avg(Number), max(Number),min(Number) from Phones where Number <400000 group by ATS_number
Select ATS_number, avg(Number), max(Number),min(Number) from Phones where Number <600000 group by ATS_number
Select ATS_number, avg(Number), max(Number),min(Number) from Phones where Number <800000 group by ATS_number
Select ATS_number, avg(Number), max(Number),min(Number) from Phones where Number <900000 group by ATS_number

2 столбца (число строк 10⁵, 3*10⁵, 5*10⁵, 7*10⁵, 8*10⁵)

Select ATS_number, Class, avg(Number), max(Number),min(Number), count (*) from Phones where Number < 200000 group by ATS_number, Class
Select ATS_number, Class, avg(Number), max(Number),min(Number), count (*) from Phones where Number < 400000 group by ATS_number, Class
Select ATS_number, Class, avg(Number), max(Number),min(Number), count (*) from Phones where Number < 600000 group by ATS_number, Class
Select ATS_number, Class, avg(Number), max(Number),min(Number), count (*) from Phones where Number < 800000 group by ATS_number, Class
Select ATS_number, Class, avg(Number), max(Number),min(Number), count (*) from Phones where Number < 900000 group by ATS_number, Class

3 столбца (число строк 10⁵, 3*10⁵, 5*10⁵, 7*10⁵, 8*10⁵)

Select House, Surname, Name, max(length(Surname)-length(Name)), min(length(Surname)+length(Name)), avg((length(Name)-length(Surname))/length(Name)), count(*) from Abonents where ID < 200000 group by House, Surname, Name
Select House, Surname, Name, max(length(Surname)-length(Name)), min(length(Surname)+length(Name)), avg((length(Name)-length(Surname))/length(Name)), count(*) from Abonents where ID < 400000 group by House, Surname, Name
Select House, Surname, Name, max(length(Surname)-length(Name)), min(length(Surname)+length(Name)), avg((length(Name)-length(Surname))/length(Name)), count(*) from Abonents where ID < 600000 group by House, Surname, Name
Select House, Surname, Name, max(length(Surname)-length(Name)), min(length(Surname)+length(Name)), avg((length(Name)-length(Surname))/length(Name)), count(*) from Abonents where ID < 800000 group by House, Surname, Name
Select House, Surname, Name, max(length(Surname)-length(Name)), min(length(Surname)+length(Name)), avg((length(Name)-length(Surname))/length(Name)), count(*) from Abonents where ID < 900000 group by House, Surname, Name

1 2 3 4

Информация о работе

ВУЗ:

Новосибирский государственный технический университет (НГТУ)

Размер файла:

72 Kb

Скачали:

Скачать файл