Access ДҚБЖ сұраулардың екі түрін пайдаланады: QBE – үлгі бойынша сұрау және SQL(Structured Query Language) – құрылымдық сұраныс тілі. Сұраныс үлгісі «Сұраныс құрастырушы» терезесінде арнайы сұрау формасын толтыру арқылы жасалады. SQL – сұраныстарды бағдарламашылар SQL тізбегінен жасайды – нұсқаулар. SQL әдетте «Жасау» қойындысындағы «Сұраныс құрастырушы» пәрмені арқылы ашылатын сұраныс пішінінде бағдарламашылармен жасалады және Көрініс мәзірінен «SQL режимі» таңдалады. SQL тілі деректермен жұмыс істеуге арналған, яғни. реляциялық дерекқорлардағы деректерді құруға, өзгертуге және басқаруға арналған.
ANSI-89 SQL және ANSI-92 SQL стандарттарына сәйкес келетін бірнеше SQL сұрау режимдері (ANSI-89 SQL және ANSI-92 SQL режимінің сұраулары) бар екенін атап өткен жөн.
Нұсқауларда SQL тіліндегі деректер жиынының сипаттамасы бар. SQL мәлімдемелерісөйлемдерден тұрады (SELECT, FROM, WHERE, т.б.). Ұсыныстар SQL тілінде терминдер (операторлар немесе командалар, идентификаторлар, тұрақтылар және т.б.) тұрады. Мәлімдеме оператордан басталып (ТАҢДАУ, ЖАСАУ, INSERT, ЖАҢАРТУ, ЖОЮ, т.б. командаларының бірі) нүктелі үтірмен аяқталады. Негізгі SQL операторлары: SELECT, FROM және WHERE.
Мысалы, SQL мәлімдемесі:
Студенттерді таңдау.Студент коды
Студенттерден;
"SELECT Students.StudentCode" және "FROM Students" тармағынан тұрады.
SELECT сөйлемінде бар операторТАҢДАУ және идентификатор«Студенттер.Студенттік кодекс». Мұнда «Студенттік код» өрісінің толық атауының алдында мәліметтер қорының «Студенттер» кестесінің аты қойылады. ТАҢДАУ – қажетті деректерден тұратын өрісті көрсетеді. FROM сөйлемі FROM сөйлемінен және "Оқушылар" идентификаторынан тұрады. FROM - SELECT сөйлемінде көрсетілген өрістерді қамтитын кестені анықтайды.
Айта кету керек, SQL тілінде сұранысты құру кезінде оның синтаксисін ескеру қажет. SQL тілінің синтаксисі ағылшын тілінің синтаксисіне негізделгеніне қарамастан, әртүрлі ДҚБЖ үшін SQL тілінің нұсқаларының синтаксисі әртүрлі болуы мүмкін.
Сұраныстардың бірнеше түрі бар: жазбаларды таңдау, жаңарту, қосу және жою, өзара сұрау, кестелерді құру және жою, кестелерді біріктіру және т.б. Ең көп таралғаны - үлгі сұрау. Таңдау сұраныстары пайдаланушыға қажетті кестелердегі ақпаратты таңдау үшін қолданылады. Олар тек қатысты кестелер үшін жасалады.
Access 2003 немесе 2007 ДҚБЖ-да SQL сұрау сұрауларын көру үшін үлгі сұрауды құрастыру үшін белсенді терезеде SQL View/Mode командасын орындау керек (1-сурет).
Күріш. 1.
Access 2003 дерекқорынан оқушылардың үлгерімінің «Баға=5» критерийі бойынша деректер үлгісін алу үшін SQL операторын (SELECT) алайық (2-сурет).
Күріш. 2.
SELECT операторынан (1-сурет) келесідей, ол SQL тіліндегі деректер жиынын сипаттайды: SELECT – деректерден тұратын кестелердің атауларының алдында өріс атауларын анықтайды; FROM - кестелердің түйінді өрістері арқылы кестелерді және олардың байланыстарын анықтайды (бұл үшін INNER JOIN ... ON конструкциясы пайдаланылады), оның негізінде деректер таңдалады; WHREME – өрістерді таңдау шарттарын анықтайды; ORDER BY - «Студенттер» кестесінің «Тегі» өрісінің мәндерін өсу реті бойынша сұрыптауды анықтайды (әдепкі бойынша, өсу реті орындалады).
Мәліметтер қорынан деректерді алу нұсқауларынан келесідей, SQL тілі мәліметтер қорынан не алу керектігін сипаттайды, ал орындау ДҚБЖ-ға жүктеледі, өйткені SQL тілінде бағдарламаның орындалуын басқарудың өзіндік құралдары жоқ.
Алдыңғы мақалаларда мәселелер талқыланды. SQL сұраныстары негізінде «sql_training_st.mdb» деректер қоры кестелерінің құрылымын құру технологиясы қарастырылған. Сонымен қатар, SQL сұрауларын пайдалана отырып, ACCESS ДҚБЖ кестелері "sql_training_st.mdb" толтырылды.
Реляциялық мәліметтер қорларында SQL тілі мәліметтерді манипуляциялауға, мәліметтер қорының құрылымын және оның құрамдастарын анықтауға, мәліметтер базасына пайдаланушының кіруін бақылауға және транзакцияларды басқаруға немесе мәліметтер базасындағы өзгерістерді басқаруға арналғаны белгілі.
SQL тілі төрт топтан тұрады:
- мәліметтерді өңдеу тілі DML;
- DDL деректерін анықтау тілі;
- деректерді басқару тілі DCL;
- TCL транзакцияны басқару тілі.
DML тобына SQL сұрауларының төрт негізгі түрі кіреді:
- INSERT – кестенің соңына бір немесе бірнеше жазбаларды қосуға арналған;
- UPDATE – кесте бағандарындағы бар жазбаларды өзгертуге немесе кестедегі деректерді өзгертуге арналған;
- DELETE – кестеден жазбаларды жоюға арналған;
- SELECT – кестелерден деректерді таңдауға арналған.
Дерекқорға түзетуші сұрауларға қатысты SQL сұрауларының алғашқы үш түрі (INSERT, UPDATE, DELETE) бетте талқыланды.
Бұл мақалада Access дерекқор кестелерінен деректерді алуға арналған сұрауларды қарастырамыз.
Access 2003 немесе 2007 дерекқорында сақталған ақпаратты алу үшін кестелерден деректерді таңдау үшін ТАҢДАУ сұрауын пайдалануға болады.
Үлгі алу үшін келесі SQL сұранысын (SQL операторын) құрастырайық, бұл үшін View/SQL Mode командасын орындау арқылы SQL режимін таңдаңыз; Пернетақтадан келесі SQL операторын енгізіңіз:
ТАҢДАУ *
Студенттерден;
Бұл мәлімдеме "SELECT *" және "FROM Students" деген екі тармақтан тұрады. Бірінші тармақта SELECT операторы және идентификатор * бар («идентификатор *» кестенің барлық бағандарының тізімін білдіреді). Екінші тармақта FROM операторы мен «Оқушылар» идентификаторы бар.
FROM - SELECT сөйлемінде көрсетілген өрістерді қамтитын Студенттер кестесін анықтайды. Таңдау сұрауы әрқашан екі оператордан тұратынын атап өткен жөн: SELECT және FROM. Таңдау шарттарына байланысты таңдау сұрауында басқа операторлар болуы мүмкін. 1-суретте деректерді алу сұрауының скриншоты көрсетілген.
Күріш. 1. Деректерді таңдау үшін SQL SELECT сұрауы
Бұл мысалда деректер үлгісі Оқушылар кестесінің барлық бағандарынан жасалған.
Сұранысты «Студент-сұрау1» деген атпен сақтаңыз. «Сақтау» командасын орындау нәтижесінде «Навигациялық аймақта» «Сұраныс: Студенттер-сұраныс1» нысаны пайда болады.
Таңдау сұрауын сақтағаннан кейін «Іске қосу» белгішесін басу арқылы осы сұрауды орындау керек. «Орындау» командасының нәтижелері суретте көрсетілген. 2.
Күріш. 2. Оқушылар кестесінің барлық бағандарынан деректерді таңдау
Зертханалық жұмыс No1
SQL: DATA EXTRACT - командаТАҢДАУ
Жұмыс мақсаты:
- SQL операторларымен танысу;
- ТАҢДАУ пәрмені арқылы Access-те қарапайым SQL сұраныстарын құруды үйрену;
· IN, BETWEEN, LIKE, IS NULL операторларын қолдану.
Жаттығу№1. SQL режимінде СТУДЕНТТЕР кестесіндегі АТ пен ТЕГ өрістерінің барлық мәндерін таңдау үшін сұраныс жасаңыз.
АТТЫ, ТЕГІН ТАҢДАҢЫЗ
СТУДЕНТТЕРДЕН;
Жаттығу№2 . SQL режимінде СТУДЕНТТЕР кестесінің барлық бағандарын таңдау үшін сұрау жасаңыз.
ТАҢДАУ *
СТУДЕНТТЕРДЕН;
№3 тапсырма. SQL режимінде студенттер тұратын қалалардың атауларын таңдау үшін сұрау жасаңыз, олар туралы ақпарат ЖЕКЕ ДЕРЕКТЕР кестесінде.
АЙРЫҚ ҚАЛА ТАҢДАУ
[ЖЕКЕ ДЕРЕКТЕР]ДЕН;
№4 тапсырма. SQL режимінде Иванов тегі бар барлық студенттердің атын шығаратын таңдау сұранысын жасаңыз, олар туралы ақпарат ОҚУШЫЛАР кестесінде бар.
ТАҢДАУ ТЕГІН, АТТЫ
СТУДЕНТТЕРДЕН
ҚАЙДА ТЕГІ="Иванов";
№5 тапсырма. Бюджеттік оқыту нысаны бойынша UIT-22 тобында оқитын студенттердің аты-жөнін алу үшін SQL режимінде таңдау сұранысын жасаңыз.
ТАҢДАУ ТЕГІН, АТТЫ
СТУДЕНТТЕРДЕН
WHERE GROUP="UIT-22" ЖӘНЕ БЮДЖЕТ=шын;
№6 тапсырма. SQL режимінде сұрау жасаңыз. ЕМТИХАН кестесіндегі үлгі үшін тек 4 және 5 баға алған студенттер туралы ақпарат.
ТАҢДАУ *
КІМНЕН [ӨЗГЕРТУЕмтихандар]
ҚАЙДАБАҒАIN(4,5);
№7 тапсырма. IOSU пәнінен емтихан бағасы 3 болатын студенттер туралы ақпаратты таңдау үшін zanpoc және SQL режимін жасаңыз.
ТАҢДАУ *
КІМНЕН [ӨЗГЕРТУЕмтихандар]
ҚАЙДАITEM=" IOSU«ЖәнеБАҒАжоқ (4,5);
№8 тапсырма.Сағаттары 100 мен 130 аралығындағы элементтер үшін жазбаларды таңдау үшін SQL режимінде сұрау жасаңыз.
ТАҢДАУ *
FROMЗАТТАР
ҚАЙДАКӨРІҢІЗ100-ден 130-ға дейін;
№9 тапсырма.Студенттердің кестесінен фамилиялары, мысалы, «С» әрпінен басталатын студенттер туралы ақпаратты таңдау үшін SQL режимінде сұрау жасаңыз.
ТАҢДАУ *
FROMСТУДЕНТТЕР
ҚАЙДАТЕГІЛАЙК"МЕН*";
Қорытынды:Зертханалық жұмыс барысында біз SQL нұсқауларымен таныстық, IN, BETWEEN, LIKE операторлары арқылы SELECT командасы арқылы Access-те қарапайым SQL сұраныстарын құруды үйрендік.
«Дүкен» оқу жобасының сипаттамасыКесте сілтеме диаграммасы
Кестелердің сипаттамасы
m_category - өнім санаттары
m_income - тауардың түсуі
m_outcome – тауарларды тұтыну
m_product - каталог, өнім сипаттамалары
m_supplier - каталог; жеткізуші туралы ақпарат
m_unit - каталог; өлшем бірлік
Осы оқу материалында келтірілген мысалдарды іс жүзінде тексеру үшін сізде келесі бағдарламалық құрал болуы керек:
Microsoft Access 2003 немесе одан кейінгі нұсқасы.
MS Access-те SQL сұранысы. Бастау
Кестенің мазмұнын көру үшін сол жақ панельдегі кесте атауын екі рет басыңыз:
Кесте өрісін өңдеу режиміне ауысу үшін жоғарғы панельде Дизайн режимін таңдаңыз:
SQL сұрауының нәтижесін көрсету үшін сол жақ тақтадағы сұрау атауын екі рет басыңыз:
SQL сұрауын өңдеу режиміне ауысу үшін жоғарғы панельде SQL режимін таңдаңыз:
SQL сұрауы. MS Access-тегі мысалдар. ТАҢДАУ: 1-10
SQL сұрауында SELECT операторы дерекқор кестелерінен таңдау үшін пайдаланылады.SQL сұрау Q001.Қажетті реттілікте тек қажетті өрістерді алу үшін SQL сұрауының мысалы:
SELECT dt, product_id, сома
FROM m_income;
SQL сұрау Q002.Бұл мысалда SQL сұрауында жұлдызша (*) таңбасы m_product кестесінің барлық бағандарын тізімдеу үшін, басқаша айтқанда, m_product қатынасының барлық өрістерін алу үшін пайдаланылады:
ТАҢДАУ *
FROM m_product;
СұранысSQL Q003. DISTINCT операторы қайталанатын жазбаларды жою және бірнеше бірегей жазбаларды алу үшін пайдаланылады:
DISTINCT product_id ТАҢДАУ
FROM m_income;
SQL сұрау Q004. ORDER BY операторы жазбаларды белгілі бір өрістің мәндері бойынша сұрыптау (реттеу) үшін қолданылады. Өріс атауы ORDER BY операторынан кейін көрсетіледі:
ТАҢДАУ *
m_кірістен
Бағасы бойынша ТАПСЫРЫС;
SQL сұрау Q005. ASC операторы ORDER BY операторына қосымша ретінде пайдаланылады және өсу бойынша сұрыптауды көрсету үшін қызмет етеді. DESC операторы ORDER BY операторына қосымша қолданылады және кему бойынша сұрыптауды көрсету үшін қолданылады. ASC де, DESC де көрсетілмеген жағдайда ASC (әдепкі) бар деп есептеледі:
ТАҢДАУ *
m_кірістен
ТАПСЫРЫС БОЙЫНША dt DESC , бағасы;
SQL сұрау Q006.Кестеден қажетті жазбаларды таңдау үшін таңдау шартын білдіретін әртүрлі логикалық өрнектер қолданылады. Логикалық өрнегі WHERE операторынан кейін пайда болады. Сома мәні 200-ден асатын m_income кестесінен барлық жазбаларды алу мысалы:
ТАҢДАУ *
m_кірістен
ҚАЙДА сомасы>200;
SQL сұрау Q007.Күрделі шарттарды өрнектеу үшін AND (конъюнкция), OR (дизъюнкция) және NOT (логикалық терістеу) логикалық операторлары қолданылады. m_outcome кестесінен сома мәні 20 және баға мәні 10-нан үлкен немесе оған тең барлық жазбаларды алу мысалы:
Бағасы
FROM m_outcome
ҚАЙДА сомасы=20 ЖӘНЕ бағасы>=10;
SQL сұрау Q008.Екі немесе одан да көп кестелердің деректерін біріктіру үшін INNER JOIN, LEFT JOIN, RIGHT JOIN нұсқауларын пайдаланыңыз. Келесі мысал dt, product_id, сома, баға өрістерін m_income кестесінен және тақырып өрісін m_product кестесінен шығарады. m_income кесте жазбасы m_income.product_id мәні m_product.id мәніне тең болғанда m_product кестесі жазбасына қосылады:
ON m_income.product_id=m_product.id;
SQL сұрау Q009.Бұл SQL сұрауында ескеретін екі нәрсе бар: 1) іздеу мәтіні жалғыз тырнақшаға алынған ("); 2) күн #Ай/Күн/Жыл# пішімінде, бұл MS Access үшін дұрыс. 2011 жылдың 12 маусымында күнді жазу форматы басқаша болуы мүмкін. Күн пішімін ескеріңіз №6/12/2011#:
SELECT dt, product_id, тақырып, сома, баға
FROM m_income INNER JOIN m_product
WHERE title="Сүт" And dt=#6/12/2011#; !}
SQL сұрау Q010. BETWEEN нұсқауы мәннің белгілі бір диапазонға жататынын тексеру үшін қолданылады. 2011 жылдың 1 маусымы мен 30 маусымы аралығында алынған өнімдер туралы ақпаратты көрсететін мысал SQL сұрауы:
ТАҢДАУ *
FROM m_income INNER JOIN m_product
ON m_income.product_id=m_product.id
ҚАЙДА dt #6/1/2011# мен #6/30/2011# АРАСЫНДА;
SQL сұрауы. MS Access-тегі мысалдар. ТАҢДАУ: 11-20
Бір SQL сұрауы екіншісіне кірістірілуі мүмкін. Ішкі сұрау сұрау ішіндегі сұраудан басқа ештеңе емес. Әдетте ішкі сұрау WHERE сөйлемінде қолданылады. Бірақ ішкі сұрауларды пайдаланудың басқа жолдары бар.Q011 сұрау. m_product кестесіндегі өнімдер туралы ақпарат көрсетіледі, олардың кодтары m_income кестесінде де бар:
ТАҢДАУ *
FROM m_product
WHERE id IN (m_income FROM product_id SELECT);
Сұраныс Q012. m_product кестесіндегі өнімдердің тізімі көрсетіледі, олардың кодтары m_outcome кестесінде жоқ:
ТАҢДАУ *
FROM m_product
WHERE id IN ЕМЕС (ТАҢДАУ product_id FROM m_outcome);
Сұраныс Q013.Бұл SQL сұрауы m_income кестесіндегі, бірақ m_outcome кестесінде емес өнім кодтары мен атауларының бірегей тізімін көрсетеді:
ТАҢДАУ DISTINCT product_id, тақырып
FROM m_income INNER JOIN m_product
ON m_income.product_id=m_product.id
WHERE product_id IN YOX (ТАҢДАУ product_id FROM m_outcome);
Q014 сұрау. m_category кестесінен атаулары M әрпінен басталатын санаттардың бірегей тізімі көрсетіледі:
DISTINCT тақырыпты ТАҢДАҢЫЗ
FROM m_product
ҚАЙДА «М*» сияқты тақырып;
Q015 сұрау.Сұраудағы өрістерге арифметикалық амалдарды орындау және сұраудағы өрістердің атын өзгерту мысалы (бүркеншік ат). Бұл мысалда пайда сатудың 7 пайызы болса, шығыс = сан*баға және әрбір баптың шығыс жазбасы үшін пайда есептеледі:
сома*баға/100*7 AS пайда
FROM m_outcome;
Q016 сұрау.Арифметикалық амалдарды талдау және жеңілдету арқылы сұраныстың орындалу жылдамдығын арттыруға болады:
ТАҢДАҢЫЗ dt, өнім_идентификаторы, сома, баға, сома*баға Нәтиже_сомасы,
нәтиже_қосынды*0,07 AS пайда
FROM m_outcome;
Q017 сұрау.Бірнеше кестелердегі деректерді біріктіру үшін INNER JOIN операторын пайдалануға болады. Келесі мысалда ctgry_id мәніне байланысты m_income кестесіндегі әрбір жазба өнім тиесілі m_category кестесіндегі санат атауымен сәйкестендіріледі:
ТАҢДАУ c.title, b.title, dt, сома, баға, сома*баға AS кіріс_сомасы
FROM (m_income a.product_id=b.id ІШКІ ҚОСЫЛУ РЕТІНДЕ m_product AS b ON)
INNER JOIN m_category AS c ON b.ctgry_id=c.id
ТАРТИП БОЙЫНША c.title, b.title;
Сұраныс Q018. SUM – қосынды, COUNT – шама, AVG – орташа арифметикалық, MAX – максималды мән, MIN – ең төменгі мән сияқты функциялар жиынтық функциялар деп аталады. Олар көптеген мәндерді қабылдайды және оларды өңдегеннен кейін бір мәнді қайтарады. SUM жиынтық функциясын пайдаланып, сома мен баға өрістерінің көбейтіндісінің қосындысын есептеу мысалы:
СОМА(сома*баға) Жалпы_сома ретінде ТАҢДАҢЫЗ
FROM m_income;
Q019 сұрау.Бірнеше жиынтық функцияларды пайдалану мысалы:
ТАҢДАУ сомасын(сома) Сома_сома ретінде, AVG(сома) соманы_AVG ретінде,
MAX(сома) AS Amount_Max, Мин(сома) AS Amount_min,
Сан(*) AS Жалпы_сан
FROM m_income;
Сұраныс Q020.Бұл мысалда 2011 жылдың маусымында капиталдандырылған 1 коды бар барлық тауарлардың сомасы есептеледі:
Табыс_сомасы ретінде соманы(сома*баға) ТАҢДАҢЫЗ
m_кірістен
ҚАЙДА өнім_id=1 ЖӘНЕ dt #6/1/2011# ЖӘНЕ #6/30/2011# АРАСЫНДА;.
Q021 сұрау.Келесі SQL сұрауы 4 немесе 6 коды бар элементтерді сату көлемін есептейді:
Нәтиже_сома ретінде соманы (сома*баға) ТАҢДАҢЫЗ
FROM m_outcome
WHERE product_id=4 НЕМЕСЕ product_id=6;
Q022 сұрау. 2011 жылдың 12 маусымында 4 немесе 6 коды бар қанша тауар сатылғаны есептеледі:
Сома(сома*баға) нәтиже_қосынды ретінде ТАҢДАҢЫЗ
FROM m_outcome
ҚАЙДА (өнім_идентификаторы=4 НЕМЕСЕ өнім_идентификаторы=6) ЖӘНЕ dt=#6/12/2011#;
Q023 сұрау.Тапсырма мынау. Капиталдандырылған «Нан-тоқаш өнімдері» санатындағы тауарлардың жалпы сомасын есептеңіз.
Бұл мәселені шешу үшін үш кестемен жұмыс істеу керек: m_income, m_product және m_category, себебі:
- m_income кестесінде капиталдандырылған тауарлардың саны мен бағасы сақталады;
- әрбір өнімнің санат коды m_product кестесінде сақталады;
- тақырып категориясының атауы m_category кестесінде сақталады.
Бұл мәселені шешу үшін біз келесі алгоритмді қолданамыз:
- ішкі сұранысты пайдаланып m_category кестесінен «Нан-тоқаш өнімдері» категориясының кодын анықтау;
- әрбір сатып алынатын өнімнің санатын анықтау үшін m_income және m_product кестелерін қосу;
- санат коды жоғарыда көрсетілген ішкі сұрауда анықталған кодқа тең тауарлар үшін түбіртек сомасын есептеу (=саны*бағасы).
ТАҢДАУ
FROM m_product INNER JOIN РЕТІНДЕ m_income AS b ON a.id=b.product_id
WHERE ctgry_id = (SELECT id FROM m_category WHERE title="Нан-тоқаш өнімдері"); !}
Q024 сұрау.«Нан-тоқаш өнімдері» санатындағы капиталдандырылған тауарлардың жалпы сомасын есептеу мәселесін келесі алгоритм бойынша шешеміз:
- m_income кестесіндегі әрбір жазба үшін оның өнім_идентификаторының мәніне байланысты m_категория кестесінен санат атауымен сәйкестендіріңіз;
- санаты «Нан-тоқаш өнімдері» болып табылатын жазбаларды таңдау;
- түбіртек сомасын есептеңіз = саны * бағасы.
FROM (m_product INNER JOIN РЕТІНДЕ m_income AS b ON a.id=b.product_id)
WHERE c.title="Нан-тоқаш өнімдері";
!}
Q025 сұрау.Бұл мысал қанша тауардың тұтынылғанын есептейді:
COUNT(өнім_идентификаторы) өнім_cnt ретінде ТАҢДАҢЫЗ
FROM (ТАҢДАУ DISTINCT product_id FROM m_outcome) AS t;
Q026 сұрау. GROUP BY операторы жазбаларды топтау үшін пайдаланылады. Әдетте жазбалар бір немесе бірнеше өрістердің мәні бойынша топтастырылады және әрбір топқа кейбір жиынтық әрекет қолданылады. Мысалы, келесі сұрау тауарларды сату туралы есепті жасайды. Яғни, тауарлардың атаулары мен олардың сатылған сомасынан тұратын кесте жасалады:
Тақырыпты ТАҢДАҢЫЗ, СУМ(сома*баға) нәтиже_қосынды ретінде
FROM m_product AS a INNER JOIN m_outcome AS b
ON a.id=b.product_id
GROUP BY BY;
Q027 сұрау.Санат бойынша сату есебі. Яғни, өнім санаттарының атаулары, осы санаттардың өнімдері сатылған жалпы сома және орташа сатылым сомасы қамтылған кесте жасалады. ROUND функциясы орташа мәнді жүздікке дейін дөңгелектеу үшін қолданылады (ондық бөлгіштен кейінгі екінші сан):
ТАҢДАУ c.title, SUM(сома*баға) Нәтиже_қосынды,
ROUND(AVG(сома*баға),2) AS нәтиже_қосындыорта
FROM (m_product AS a.id=b.product_id)
INNER JOIN m_category AS c ON a.ctgry_id=c.id
GROUP BY c.title;
Q028 сұрау.Оның түбіртектерінің жалпы және орташа саны әрбір өнім үшін есептеледі және жалпы түсімдері 500-ден кем емес өнімдер туралы ақпаратты көрсетеді:
ТАҢДАУ өнім_идентификаторы, СОМА(сома) сома_сома ретінде,
Дөңгелек(Орташа(сома),2) AS сома_орта
m_кірістен
өнім_идентификаторы бойынша ТОПТАУ
БАР сома(сома)>=500;
Q029 сұрау.Бұл сұрау әрбір өнім үшін оның 2011 жылдың екінші тоқсанында жасалған түсімдерінің сомасы мен орташа мәнін есептейді. Егер өнім түбіртегінің жалпы сомасы кемінде 1000 болса, онда осы өнім туралы ақпарат көрсетіледі:
Тақырыпты ТАҢДАҢЫЗ, SUM(сома*баға) Кіріс_сомасы ретінде
FROM m_income a INNER JOIN m_product b ON a.product_id=b.id
ҚАЙДА dt #4/1/2011# ЖӘНЕ #6/30/2011# АРАСЫНДА
ТАҚЫРЫП БОЙЫНША ТОПТАУ
СОМА БАР(сома*баға)>=1000;
Q030 сұрау.Кейбір жағдайларда кейбір кестенің әрбір жазбасын басқа кестенің әрбір жазбасымен сәйкестендіру қажет; ол декарттық туынды деп аталады. Осындай байланыс нәтижесінде пайда болатын кесте Декарт кестесі деп аталады. Мысалы, кейбір А кестесінде 100 жазба, ал В кестесінде 15 жазба болса, онда олардың Декарт кестесі 100*15=150 жазбадан тұрады. Келесі сұрау m_income кестесіндегі әрбір жазбаны m_outcome кестесіндегі әрбір жазбамен қосады:
FROM m_income, m_ntice;
Q031 сұрау.Жазбаларды екі өріс бойынша топтастыру мысалы. Келесі SQL сұранысы әрбір жеткізуші үшін одан алынған тауардың көлемі мен санын есептейді:
SUM(сома*баға) Кіріс_сомасы
Q032 сұрау.Жазбаларды екі өріс бойынша топтастыру мысалы. Келесі сұрау әрбір жеткізуші үшін біз сататын өнімдерінің көлемі мен санын есептейді:
жеткізуші_идентификаторын, өнім_идентификаторын, СУМ(сома) сомасын_сома ретінде ТАҢДАУ,
GROUP BY BY жабдықтаушы_идентификаторы, өнім_идентификаторы;
Q033 сұрау.Бұл мысалда жоғарыдағы екі сұрау (q031 және q032) ішкі сұраулар ретінде пайдаланылады. LEFT JOIN әдісін қолданатын осы сұраулардың нәтижелері бір есепке біріктіріледі. Келесі сұрау әрбір жеткізуші үшін алынған және сатылған өнімдердің саны мен сомасы туралы есепті көрсетеді. Кейбір өнім әлдеқашан алынған болса, бірақ әлі сатылмаған болса, бұл жазбаның нәтиже_сомасы ұяшығы бос болатынын ескеріңіз. бұл сұрау салыстырмалы күрделі сұрауларды ішкі сұрау ретінде пайдаланудың мысалы ғана. Бұл SQL сұрауының үлкен деректер көлемімен өнімділігі күмәнді:
ТАҢДАУ *
FROM
SUM(сома*баға) Кіріс_сомасы
ON a.product_id=b.id GROUP BY BY product_id, product_id) a AS
СОЛ ҚОСЫЛУ
(Таңдау жеткізуші_идентификаторы, өнім_идентификаторы, СУМ(сома) сома_сома ретінде,
SUM(сома*баға) Нәтиже_сомасы
FROM m_outcome AS a INNER JOIN m_product AS b
ON a.product_id=b.id ТОБЫ БОЙЫНША жеткізуші_идентификаторы, өнім_идентификаторы) AS b
ҚОСУЛЫ (a.product_id=b.product_id) ЖӘНЕ (a.supplier_id=b.supplier_id);
Q034 сұрау.Бұл мысалда жоғарыдағы екі сұрау (q031 және q032) ішкі сұраулар ретінде пайдаланылады. RIGTH JOIN әдісін қолданатын осы сұраулардың нәтижелері бір есепте біріктірілген. Келесі сұрауда әрбір клиенттің өзі пайдаланған төлем жүйелеріне және ол салған инвестиция сомасына сәйкес төлемдер сомасы туралы есеп көрсетіледі. Келесі сұрау әрбір жеткізуші үшін алынған және сатылған өнімдердің саны мен сомасы туралы есепті көрсетеді. Кейбір өнім сатылған болса, бірақ әлі келмеген болса, бұл жазбаның кіріс_сомасы ұяшығы бос болатынын ескеріңіз. Мұндай бос ұяшықтардың болуы сатуды есепке алудағы қатенің көрсеткіші болып табылады, өйткені сатылымға дейін ең алдымен сәйкес тауардың келуі қажет:
ТАҢДАУ *
FROM
(Таңдау жеткізуші_идентификаторы, өнім_идентификаторы, СУМ(сома) сома_сома ретінде,
SUM(сома*баға) Кіріс_сомасы
FROM m_income A.product_id=b.id ON ІШКІ ҚОСЫЛУ РЕТІНДЕ m_product AS b.
жеткізуші_идентификаторы, өнім_идентификаторы бойынша ТОПТАУ a
ОҢ ҚОСЫЛУ
(Таңдау жеткізуші_идентификаторы, өнім_идентификаторы, СУМ(сома) сома_сома ретінде,
SUM(сома*баға) Нәтиже_сомасы
FROM m_outcome AS a.product_id=b.id ON INNER JOIN AS b ON m_product
ТОБЫ БОЙЫНША жеткізуші_идентификаторы, өнім_идентификаторы) AS b
ҚОСУЛЫ (a.product_id=b.product_id) ЖӘНЕ (a.product_id=b.product_id);
Q035 сұрау.Өнім бойынша кірістер мен шығыстардың сомасын көрсететін есеп көрсетіледі. Ол үшін m_income және m_outcome кестелері бойынша өнімдер тізімі жасалады, содан кейін осы тізімдегі әрбір өнім үшін m_income кестесі бойынша оның кірісінің сомасы және m_outcome кестесі бойынша оның шығыстарының сомасы есептеледі:
ТАҢДАУ өнім_идентификаторы, СУМ(сомадағы) табыс_сомасы,
СУМ(шығарылатын_сома) нәтиже_сомасы
FROM
(Өнім_идентификаторын ТАҢДАҢЫЗ, сома AS_сома, 0 AS тыс_сома
m_кірістен
БАРЛЫҚ ОДАҚ
ТАҢДАУ өнім_идентификаторы, 0 AS сомасы, сомасы AS тыс_сома
FROM m_outcome) AS t
GROUP BY product_id;
Q036 сұрау. EXISTS функциясы егер оған берілген жиында элементтер болса, TRUE мәнін қайтарады. EXISTS функциясы егер оған берілген жиын бос болса, яғни оның құрамында элементтер жоқ болса, FALSE мәнін қайтарады. Келесі сұрау m_income және m_outcome кестелерінің екеуінде де қамтылған өнім кодтарын көрсетеді:
DISTINCT product_id ТАҢДАУ
FROM m_income AS a
ҚАЙДА БАР(өнім идентификаторын m_нәтижеден ТАҢДАҢЫЗ b
Q037 сұрау. m_income және m_outcome кестелерінің екеуінде де қамтылған өнім кодтары көрсетіледі:
DISTINCT product_id ТАҢДАУ
FROM m_income AS a
WHERE product_id IN IN (m_outcome FROM product_id ТАҢДАҢЫЗ)
Q038 сұрау. m_income кестесінде қамтылған, бірақ m_outcome кестесінде жоқ өнім кодтары көрсетіледі:
DISTINCT product_id ТАҢДАУ
FROM m_income AS a
ЖОҚ ЖОҚ(өнім_идентификаторын m_нәтижеден ТАҢДАҢЫЗ b
WHERE b.product_id=a.product_id);
Q039 сұрау.Ең көп сату сомасы бар өнімдер тізімі көрсетіледі. Алгоритм келесідей. Әрбір өнім үшін оның сатылым сомасы есептеледі. Содан кейін бұл сомалардың максималды мөлшері анықталады. Содан кейін әрбір өнім үшін оның сатылым сомасы қайтадан есептеледі және сату сомасы максимумға тең тауарлардың коды мен сату сомасы көрсетіледі:
өнім_идентификаторын, СУМА(сома*баға) сома_сома ретінде ТАҢДАҢЫЗ
FROM m_outcome
өнім_идентификаторы бойынша ТОПТАУ
СОМА БАР (сома*баға) = (МАКС(с_сома) ТАҢДАУ
FROM (Өнім_идентификаторы бойынша m_нәтиже ТОБЫНАН s_soma РЕТІНДЕ ТАҢДАУ СУМ(сома*баға));
Q040 сұрау.Сақталған сөз IIF (шартты оператор) логикалық өрнекті бағалау және нәтижеге (TRUE немесе FALSE) байланысты әрекетті орындау үшін қолданылады. Келесі мысалда, егер саны 500-ден аз болса, тауарды жеткізу "кішігірім" болып саналады. Әйтпесе, яғни түбіртек саны 500-ден көп немесе оған тең болса, жеткізілім "үлкен" болып саналады:
SELECT dt, product_id, сома,
IIF(m_кірістен түскен сома;
SQL сұрау Q041. IIF операторы бірнеше рет пайдаланылған жағдайда оны SWITCH операторымен ауыстыру ыңғайлырақ. SWITCH операторы (бірнеше таңдау операторы) логикалық өрнекті бағалау және нәтижеге байланысты әрекетті орындау үшін қолданылады. Келесі мысалда, егер лоттағы тауардың саны 500-ден аз болса, жеткізілген лот «кішігірім» болып саналады. Әйтпесе, яғни тауардың саны 500-ден көп немесе оған тең болса, лот «ірі» болып саналады. ":
SELECT dt, product_id, сома,
SWITCH(сома =500,"үлкен") AS белгісі
FROM m_income;
Q042 сұрау.Келесі сұраныста, егер алынған партиядағы тауардың саны 300-ден аз болса, онда партия «аз» болып саналады. Әйтпесе, яғни шарт сомасы SELECT dt, product_id, сома,
IIF(сома IIF(м_табыс сомасы;
SQL сұрау Q043.Келесі сұраныста, егер алынған партиядағы тауардың саны 300-ден аз болса, онда партия «аз» болып саналады. Әйтпесе, яғни шарт сомасы SELECT dt, product_id, сома,
SWITCH(сома сомасы>=1000, "үлкен") AS белгісі
FROM m_income;
SQL сұрау Q044.Келесі сұрауда сатылымдар үш топқа бөлінеді: шағын (150-ге дейін), орташа (150-ден 300-ге дейін), үлкен (300 немесе одан да көп). Әрі қарай, әрбір топ үшін жалпы сома есептеледі:
Санатты ТАҢДАҢЫЗ, ҚОСЫНДЫ (нәтиже_қосынды) Ctgry_Total AS
FROM (ТАҢДАУ соманы*нәтиже_сома ретінде баға,
IIf(сома*баға IIf(сома*баға m_нәтижеден) AS t
GROUP BY Category;
SQL сұрауы Q045. DateAdd функциясы берілген күнге күндерді, айларды немесе жылдарды қосу және жаңа күнді алу үшін пайдаланылады. Келесі сұрау:
1) dt өрісіндегі күнге 30 күн қосады және dt_plus_30d өрісінде жаңа күнді көрсетеді;
2) dt өрісіндегі күнге 1 айды қосады және dt_plus_1m өрісінде жаңа күнді көрсетеді:
ТАҢДАУ dt, dateadd("d",30,dt) AS dt_plus_30d, dateadd("m",1,dt) AS dt_plus_1m
FROM m_income;
SQL сұрау Q046. DateDiff функциясы әртүрлі бірліктерде (күндер, айлар немесе жылдар) екі күн арасындағы айырмашылықты есептеуге арналған. Келесі сұрау dt өрісіндегі күн мен күндер, айлар және жылдардағы ағымдағы күн арасындағы айырмашылықты есептейді:
ТАҢДАУ dt, DateDiff("d",dt,Date()) AS соңғы_күн,
DateDiff("m",dt,Date()) AS соңғы_айлар,
DateDiff("yyyy",dt,Date()) AS соңғы_жылдар
FROM m_income;
SQL сұрау Q047. DateDiff функциясы арқылы тауарды алған күннен бастап (m_income кестесі) ағымдағы күнге дейінгі күндер саны есептеледі және жарамдылық мерзімі салыстырылады (m_product кестесі):
DateDiff("d",dt,Date()) AS соңғы_күндер
FROM m_income INNER JOIN РЕТІНДЕ m_product AS b
ON a.product_id=b.id;
SQL сұрау Q048.Тауарды алған күннен бастап ағымдағы күнге дейінгі күндер саны есептеледі, содан кейін бұл санның жарамдылық мерзімінен асып кетуі тексеріледі:
ТАҢДАУ a.id, product_id, dt, lifedays,
DateDiff("d",dt,Date()) AS соңғы_күндер, IIf(соңғы_күндер>өмір күндері,"Иә","Жоқ") AS date_expire
FROM m_income a INNER JOIN m_product b
ON a.product_id=b.id;
SQL сұрау Q049.Тауарды алған күннен бастап ағымдағы күнге дейінгі айлар саны есептеледі. month_last1 бағанасы айлардың абсолютті санын есептейді, month_last2 бағанасы толық айлардың санын есептейді:
ТАҢДАУ dt, DateDiff("m",dt,Date()) AS month_songgi1,
DateDiff("m",dt,Date())-iif(күн(dt)>күн(күн()),1,0) AS month_songgi2
FROM m_income;
SQL сұрау Q050. 2011 жылға сатып алынған тауарлардың саны мен сомасы туралы тоқсан сайынғы есеп көрсетіледі:
ТАҢДАУ квартал, СУММ(нәтиже_сома) AS Барлығы
FROM (ТАҢДАУ сома*нәтиже_сома АС баға, ай(дт) AS m,
ҚОСУ(м =10,4) AS кварталь
FROM m_income WHERE year(dt)=2011) AS t
Тоқсан бойынша ТОП;
Q051 сұрау.Келесі сұрау пайдаланушылардың жүйеге алынған тауарлар сомасынан асатын көлемдегі тауарларды тұтынуы туралы ақпаратты енгізе алатынын анықтауға көмектеседі:
ТАҢДАУ өнім_идентификаторы, SUM(қосында_сома) AS кіріс_сомасы, SUM(шығыс_сома) AS нәтиже_сомасы
FROM (ТАҢДАУ өнім_идентификаторы, сома*баға қосындысы ретінде, 0 шығыс_сома ретінде
m_кірісінен
БАРЛЫҚ ОДАҚ
Өнім_идентификаторын ТАҢДАҢЫЗ, 0 сомасын_сома ретінде, сома*бағасы_сома ретінде
from m_outcome) AS t
өнім_идентификаторы бойынша ТОПТАУ
СОМА БАР(сомамен)
Q052 сұрау.Сұрау арқылы қайтарылған жолдардың нөмірленуі әртүрлі тәсілдермен жүзеге асырылады. Мысалы, MS Access-те дайындалған есеп жолдарын MS Access-тің өзін пайдаланып қайта нөмірлеуге болады. Сіз сондай-ақ бағдарламалау тілдерін, мысалы, VBA немесе PHP арқылы нөмірді қайталай аласыз. Дегенмен, кейде мұны SQL сұрауының өзінде жасау керек. Сонымен, келесі сұрау ID өріс мәндерінің өсу ретіне сәйкес m_income кестесінің жолдарын нөмірлейді:
COUNT(*) ТАҢДАУ N, b.id, b.product_id, b.summa, b.price
FROM m_income a INNER JOIN m_income b ON a.id GROUP BY b.id, b.product_id, b.summa, b. Price;
Q053 сұрау.Сату көлемі бойынша өнімдердің ішінде үздік бес өнім көрсетіледі. Алғашқы бес жазба TOP нұсқауы арқылы басып шығарылады:
ТОП 5, өнім_идентификаторы, сома(сома*баға) жиынтық ретінде ТАҢДАҢЫЗ
FROM m_outcome
өнім_идентификаторы бойынша ТОПТАУ
сома (сома*баға) БОЙЫНША ТАПСЫРЫС;
Q054 сұрау.Сату көлемі бойынша өнімдердің ең үздік бес өнімі көрсетіледі және нәтижесінде жолдар нөмірленеді:
COUNT(*) ТАҢДАУ N, b.product_id, b.summa
FROM
FROM m_outcome GROUP BY product_id) AS a
ІШКІ ҚОСЫЛУ
(Таңдау өнім_идентификаторы, сома(сома*баға) жиынтық ретінде,
жиынтық*10000000+өнім_идентификаторы AS идентификаторы
FROM m_outcome GROUP BY product_id) AS b
ON a.id>=b.id
b.product_id, b.summa
COUNT(*)РЕПТІК БОЙЫНША COUNT(*);
Q055 сұрау.Келесі SQL сұрауы MS Access SQL жүйесінде COS, SIN, TAN, SQRT, ^ және ABS математикалық функцияларын пайдалануды көрсетеді:
ТАҢДАУ (m_income ішінен санауды(*) таңдаңыз) N ретінде, 3,1415926 pi, k,
2*pi*(k-1)/N x ретінде, COS(x) COS_ ретінде, SIN(x) SIN_ ретінде, TAN(x) TAN_,
SQR(x) SQRT_ ретінде, x^3 "x^3", ABS(x) ABS_ ретінде
FROM (COUNT(*) ЕСЕП ТАҢДАУ k
FROM m_income AS a.idGROUP BY BY b.id) t;
SQL сұрауы. MS Access-тегі мысалдар. ЖАҢАРТУ: 1-10
U001 сұрауы.Келесі SQL өзгерту сұрауы m_income кестесіндегі 3 коды бар тауарлардың бағасын 10%-ға арттырады:ЖАҢАРТУ m_income SET бағасы = баға*1.1
WHERE product_id=3;
U002 сұрау.Келесі SQL жаңарту сұрауы m_income кестесіндегі барлық өнімдердің санын атаулары «Мұнай» сөзінен басталатын 22 бірлікке арттырады:
ЖАҢАРТУ m_come SET сомасы = сома+22
WHERE product_id IN (m_product FROM ИД ТАҢДАУ ҚАЙДА "Мұнай*" сияқты тақырып);
U003 сұрау. m_outcome кестесін өзгертуге арналған келесі SQL сұрауы Sladkoe LLC шығарған барлық тауарлардың бағасын 2 пайызға төмендетеді:
ЖАҢАРТУ m_нәтиже SET бағасы = баға*0,98
WHERE product_id IN
(m_product a INNER JOIN m_supplier FROM a.id ТАҢДАУ b
ON a.supplier_id=b.id WHERE b.title="LLC)"Сладкое"");. !}
Access бағдарламасындағы сұраныстар деректер қоры кестелеріндегі деректерді таңдауға, жаңартуға және өңдеуге арналған негізгі құрал болып табылады. Access реляциялық деректер қоры концепциясына сәйкес сұраныстарды орындау үшін SQL (Structured Query Language) тілін пайдаланады. SQL тілінің нұсқауларын қолдану арқылы Access бағдарламасындағы кез келген сұрау орындалады.
Сұраныстың негізгі түрі – таңдау сұранысы. Бұл сұраудың нәтижесі сұрау жабылғанға дейін бар жаңа кесте болып табылады. Жазбалар сұраныс негізіндегі кестелердің жазбаларын біріктіру арқылы қалыптасады. Кесте жазбаларын біріктіру әдісі олардың деректер схемасында байланысын анықтау немесе сұрауды құру кезінде көрсетіледі. Сұрауда тұжырымдалған таңдау шарттары кестелерді біріктіру нәтижесін құрайтын жазбаларды сүзуге мүмкіндік береді.
Access-те сұраулардың бірнеше түрін жасауға болады:
- үлгі сұрау- бір кестеден немесе сұраныстан немесе бірнеше өзара байланысты кестелерден және басқа сұраныстардан деректерді таңдайды. Нәтиже - сұрау жабылғанға дейін бар кесте. Нәтижелер кестесінің жазбалары көрсетілген таңдау шарттарына сәйкес және бірнеше кестелерді пайдалану кезінде олардың жазбаларын біріктіру арқылы құрылады;
- кесте құруға сұраныс- өзара байланысты кестелерден және басқа сұраныстардан мәліметтерді таңдайды, бірақ таңдау сұранысынан айырмашылығы, нәтижені жаңа тұрақты кестеде сақтайды;
- жаңарту, қосу, жою туралы сұраулар- нәтижеде кестелердегі деректер өзгеретін әрекетке сұраныстар.
Дизайн режиміндегі Access бағдарламасындағы сұраныстар пайдаланылған кестелерді көрсететін деректер схемасын және сұраныс кестесінің құрылымы мен жазбаларды таңдау шарттары құрастырылған сұрау пішінін қамтиды (4.1-сурет).
Сұраныс арқылы деректерді өңдеудің келесі түрлерін орындауға болады:
- сұраныс кестесіне пайдаланушы таңдаған кесте өрістерін қосу;
- алынған жазбалардың әрқайсысында есептеулер жүргізу;
- іріктеу шарттарына сәйкес келетін жазбаларды таңдау;
- өзара байланысты кестелердің жазбаларын біріктіру негізінде жаңа виртуалды кесте құру;
- бір немесе бірнеше өрістерде бірдей мәндері бар, топтың басқа өрістерінде бір уақытта статистикалық функцияларды орындайтын және нәтижесінде әрбір топ үшін бір жазбаны қамтитын жазбаларды топтау;
- бұрыннан бар кестелердің деректерін пайдалана отырып, жаңа мәліметтер қоры кестесін құру;
- жазбалардың таңдалған жиынындағы өрістерді жаңарту;
- дерекқор кестесінен жазбалардың таңдалған ішкі жиынын жою;
- жазбалардың таңдалған жиынын басқа кестеге қосыңыз.
Access бағдарламасындағы сұраулар басқа сұраулар, пішіндер және есептер үшін жазба көздері ретінде қызмет етеді. Сұранысты пайдалана отырып, бірнеше кестелерден белгілі бір тақырыптық аймақтың құжатын жасау үшін толық ақпаратты жинауға болады, содан кейін оны форманы - осы құжаттың электрондық көрінісін құру үшін пайдалануға болады. Егер пішін немесе есеп бірнеше өзара байланысты кестелер негізінде шебер арқылы жасалса, онда жазбалар көзі ретінде олар үшін сұрау автоматты түрде жасалады.
Мұны күшейту үшін бейне оқулықты қараңыз.