План исследований., страница 2

    • 4 столбца (число строк 105, 3*105, 5*105, 7*105,  8*105)
      • Select Surname, Name, Patronymic, House, max(length(Surname)), min(length(Name)), avg(length(Patronymic)), avg(House) from Abonents where ID < 200000 group by Surname, Name, Patronymic, House
      • Select Surname, Name, Patronymic, House, max(length(Surname)), min(length(Name)), avg(length(Patronymic)), avg(House) from Abonents where ID < 400000 group by Surname, Name, Patronymic, House
      • Select Surname, Name, Patronymic, House, max(length(Surname)), min(length(Name)), avg(length(Patronymic)), avg(House) from Abonents where ID < 600000 group by Surname, Name, Patronymic, House
      • Select Surname, Name, Patronymic, House, max(length(Surname)), min(length(Name)), avg(length(Patronymic)), avg(House) from Abonents where ID < 800000 group by Surname, Name, Patronymic, House
      • Select Surname, Name, Patronymic, House, max(length(Surname)), min(length(Name)), avg(length(Patronymic)), avg(House) from Abonents where ID < 900000 group by Surname, Name, Patronymic, House
    • 5 столбцов (число строк 105, 3*105, 5*105, 7*105,  8*105)
      • Select ATS_number, Class, Type, Off_code, Priv_type, avg(Off_code+Priv_type-Off_city_code), max(Class)-min(Type), sum(Class-Type) from Phones where Number < 200000 group by ATS_number, Class, Type, Off_code, Priv_type
      • Select ATS_number, Class,Type, Off_code, Priv_type, avg(Off_code+Priv_type-Off_city_code), max(Class)-min(Type), sum(Class-Type) from Phones where Number < 400000 group by ATS_number, Class, Type, Off_code, Priv_type
      • Select ATS_number, Class,Type, Off_code, Priv_type, avg(Off_code+Priv_type-Off_city_code), max(Class)-min(Type), sum(Class-Type) from Phones where Number < 600000 group by ATS_number, Class, Type, Off_code, Priv_type
      • Select ATS_number, Class,Type, Off_code, Priv_type, avg(Off_code+Priv_type-Off_city_code), max(Class)-min(Type), sum(Class-Type) from Phones where Number < 800000 group by ATS_number, Class, Type, Off_code, Priv_type
      • Select ATS_number, Class,Type, Off_code, Priv_type, avg(Off_code+Priv_type-Off_city_code), max(Class)-min(Type), sum(Class-Type) from Phones where Number < 900000 group by ATS_number, Class, Type, Off_code, Priv_type
  • Группировка 1, 2,…, 5 столбцов (используя HAVING)
    • 1 столбец (число строк 105, 3*105, 5*105, 7*105,  8*105)
      • Select ATS_number, avg(Number), max(Number),min(Number) from Phones where Number < 200000 group by ATS_number having ATS_number > 15
      • Select ATS_number, avg(Number), max(Number),min(Number) from Phones where Number < 400000 group by ATS_number having ATS_number > 15
      • Select ATS_number, avg(Number), max(Number),min(Number) from Phones where Number < 600000 group by ATS_number having ATS_number > 15
      • Select ATS_number, avg(Number), max(Number),min(Number) from Phones where Number < 800000 group by ATS_number having ATS_number > 15
      • Select ATS_number, avg(Number), max(Number),min(Number) from Phones where Number < 900000 group by ATS_number having ATS_number > 15
    • 2 столбца (число строк 105, 3*105, 5*105, 7*105,  8*105)
      • Select ATS_number, Class, avg(Number), max(Number),min(Number), count (*) from Phones where Number < 200000 group by ATS_number, Class having ATS_number > Class and count(*) <5
      • Select ATS_number, Class, avg(Number), max(Number),min(Number), count (*) from Phones where Number < 400000 group by ATS_number, Class having ATS_number > Class and count(*) <5
      • Select ATS_number, Class, avg(Number), max(Number),min(Number), count (*) from Phones where Number < 600000 group by ATS_number, Class having ATS_number > Class and count(*) <5
      • Select ATS_number, Class, avg(Number), max(Number),min(Number), count (*) from Phones where Number < 800000 group by ATS_number, Class having ATS_number > Class and count(*) <5
      • Select ATS_number, Class, avg(Number), max(Number),min(Number), count (*) from Phones where Number < 900000 group by ATS_number, Class having ATS_number > Class and count(*) <5
    • 3 столбца (число строк 105, 3*105, 5*105, 7*105,  8*105)
      • 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 having House between 10 and 30 and count(*)>3 and max(length(Surname)-length(Name))=3
      • 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 having House between 10 and 30 and count(*)>3 and max(length(Surname)-length(Name))=3
      • 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 having House between 10 and 30 and count(*)>3 and max(length(Surname)-length(Name))=3
      • 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 having House between 10 and 30 and count(*)>3 and max(length(Surname)-length(Name))=3
      • 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 having House between 10 and 30 and count(*)>3 and max(length(Surname)-length(Name))=3
    • 4 столбца (число строк 105, 3*105, 5*105, 7*105,  8*105)
      • Select Surname, Name, Patronymic, House, max(length(Surname)), min(length(Name)), avg(length(Patronymic)), avg(House) from Abonents where ID < 200000 group by Surname, Name, Patronymic, House having Surname like "Surname_23" and House > 13 and min(length(Name))>2 and avg(House)!=10
      • Select Surname, Name, Patronymic, House, max(length(Surname)), min(length(Name)), avg(length(Patronymic)), avg(House) from Abonents where ID < 400000 group by Surname, Name, Patronymic, House having Surname like "Surname_23" and House > 13 and min(length(Name))>2 and avg(House)!=10
      • Select Surname, Name, Patronymic, House, max(length(Surname)), min(length(Name)), avg(length(Patronymic)), avg(House) from Abonents where ID < 600000 group by Surname, Name, Patronymic, House having Surname like "Surname_23" and House > 13 and min(length(Name))>2 and avg(House)!=10
      • Select Surname, Name, Patronymic, House, max(length(Surname)), min(length(Name)), avg(length(Patronymic)), avg(House) from Abonents where ID < 800000 group by Surname, Name, Patronymic, House having Surname like "Surname_23" and House > 13 and min(length(Name))>2 and avg(House)!=10
      • Select Surname, Name, Patronymic, House, max(length(Surname)), min(length(Name)), avg(length(Patronymic)), avg(House) from Abonents where ID < 900000 group by Surname, Name, Patronymic, House having Surname like "Surname_23" and House > 13 and min(length(Name))>2 and avg(House)!=10
    • 5 (число строк 105, 3*105, 5*105, 7*105,  8*105)
      • Select ATS_number, Class, Type, Off_code, Priv_type, avg(Off_code+Priv_type-Off_city_code), max(Class)-min(Type), sum(Class-Type) from Phones where Number < 200000 group by ATS_number, Class, Type, Off_code, Priv_type having Off_code =0 and Priv_type between 0 and 1598 and sum(Class-Type) > 5 and ATS_number in (15,68,79,14,32) and Class > 421
      • Select ATS_number, Class, Type, Off_code, Priv_type, avg(Off_code+Priv_type-Off_city_code), max(Class)-min(Type), sum(Class-Type) from Phones where Number < 400000 group by ATS_number, Class, Type, Off_code, Priv_type having Off_code =0 and Priv_type between 0 and 1598 and sum(Class-Type) > 5 and ATS_number in (15,68,79,14,32) and Class > 421
      • Select ATS_number, Class, Type, Off_code, Priv_type, avg(Off_code+Priv_type-Off_city_code), max(Class)-min(Type), sum(Class-Type) from Phones where Number < 600000 group by ATS_number, Class, Type, Off_code, Priv_type having Off_code =0 and Priv_type between 0 and 1598 and sum(Class-Type) > 5 and ATS_number in (15,68,79,14,32) and Class > 421
      • Select ATS_number, Class, Type, Off_code, Priv_type, avg(Off_code+Priv_type-Off_city_code), max(Class)-min(Type), sum(Class-Type) from Phones where Number < 800000 group by ATS_number, Class, Type, Off_code, Priv_type having Off_code =0 and Priv_type between 0 and 1598 and sum(Class-Type) > 5 and ATS_number in (15,68,79,14,32) and Class > 421
      • Select ATS_number, Class, Type, Off_code, Priv_type, avg(Off_code+Priv_type-Off_city_code), max(Class)-min(Type), sum(Class-Type) from Phones where Number < 900000 group by ATS_number, Class, Type, Off_code, Priv_type having Off_code =0 and Priv_type between 0 and 1598 and sum(Class-Type) > 5 and ATS_number in (15,68,79,14,32) and Class > 421
Скачать файл