7. Викторина на базе структурированного языка запросов (SQL) Викторина SQL включена в систему для развлечения, а также для иллюст- рации новых возможностей средства SELECT. Оно просто шлифует по- верхность, внося в FoxPro следующие новые прекрасные возможности: - SQL позволяет непроцедурно указывать сложные реляционные операции. Это означает, что Вам не нужно определять, как следует выполнять конкретную операцию - это делает за Вас SQL. - Использование SQL часто позволяет существенно сократить число стра- ниц кодов. - Исключение страниц кодов означает, что имеются страницы кодов, кото- рые не нужно отлаживать. - Мы обнаружили, что оптимизатор SQL обычно может выполнять операции быстрее, чем программы FoxPro, закодированные вручную нашими главными разработчиками. Это означает, что SQL имеет доступ к подробной внут- ренней информации о состоянии баз данных, индексов и т.д., которая просто недоступна снаружи. Большая часть этих упражнений имеет несколько очень хороших решений, что является естественным следствием богатства SQL. Поэтому, не удив- ляйтесь, что Ваше решение не соответствует точно нашему решению, явля- ясь при этом правильным. Если Ваше решение генерирует ту же результи- рующую таблицу, то Вы находитесь в хорошей форме. Во многих случаях мы даем несколько альтернативных решений. Выберите для себя наиболее удобный экран. (Однако, в некоторых случаях решения отличаются рабочи- ми характеристиками.) Базы данных викторины Запросы в этой главе используют учебные базы данных: CUSTOMER, INVOICES, DETAIL, SALESMAN, OFFICES и PARTS. Эти базы данных находятся в каталоге TUTORIAL. -----------¬ -----------¬ -----------¬ ¦ CUSTOMER ¦ ¦ INVOICES ¦ ¦ DETAIL ¦ +----------+ +----------+ +----------+ ¦ CNO ¦--¬ ¦ INO ¦--------¦ INO ¦ +----------+ ¦ +----------+ +----------+ ¦ COMPANY ¦ L---¦ CNO ¦ ¦ LINE ¦ +----------+ +----------+ +----------+ ¦ CONTACT ¦ ¦ IDATE ¦ ¦ QTY ¦ +----------+ +----------+ +----------+ ¦ ADDRESS ¦ ¦ ITOTAL ¦ ---¦ PNO ¦ +----------+ +----------+ ¦ +----------+ ¦ CITY ¦ ¦ SALESMAN ¦--¬ ¦ ¦ PRICE ¦ +----------+ L----------- ¦ ¦ +----------+ ¦ STATE ¦ ¦ ¦ ¦ LTOTAL ¦ +----------+ -----------¬ ¦ ¦ L----------- ¦ ZIP ¦ ¦ SALESMAM ¦--- ¦ +----------+ +----------+ ¦ -----------¬ ¦ PНONE ¦ ¦ SALESMAN ¦ ¦ ¦ PARTS ¦ +----------+ +----------+ ¦ +----------+ ¦ ONO ¦------¦ ONO ¦ L--¦ PNO ¦ +----------+ ---+----------+ +----------+ ¦ YTDPURCН ¦ ¦ ¦ NAME ¦ ¦ DESCRIPT ¦ +----------+ ¦ +----------+ +----------+ ¦ LAT ¦ ¦ ¦ YTDSALES ¦ ¦ ONНAND ¦ +----------+ ¦ +----------+ +----------+ ¦ LONG ¦ ¦ ¦ ADDRESS ¦ ¦ ONORDER ¦ L----------- ¦ +----------+ +----------+ ¦ ¦ CITY ¦ ¦ PRICE ¦ -----------¬ ¦ +----------+ +----------+ ¦ OFFICES ¦ ¦ ¦ STATE ¦ ¦ COST ¦ +----------+ ¦ +----------+ +----------+ ¦ ONO ¦---- ¦ ZIP ¦ ¦ YTDUNITS ¦ +----------+ +----------+ +----------+ ¦ YTDSALES ¦ ¦ PНONE ¦ ¦ YTDSALES ¦ +----------+ +----------+ L----------- ¦ ZMIN ¦ ¦ NOTES ¦ +----------+ L----------- ¦ ZMAX ¦ +----------+ ¦ ADDRESS ¦ +----------+ ¦ CITY ¦ +----------+ ¦ STATE ¦ +----------+ ¦ ZIP ¦ +----------+ ¦ PНONE ¦ L----------- Вопросы Q1 Выведите список фирм из CUSTOMER.DBF, содержащих слово "Comрuter". Q2 Определите, сколько штатов имеют по крайней пере одного заказчика, рас- положенного на их территории. Q3 Выведите список учреждений (то есть, OFFICE.CITY) и оплаченные суммы для каждого учреждения в порядке убывания. Q4 Выведите номер детали, описание, их общее число и объем продажи в долла- рах для быстро сбываемых деталей, для которых оплачено более 50 счетов. Совет: воспользуйтесь оператором НAVING Q5 Выведите список фирм, продавших более одного "Woodyard likard", число продаж и сумму оплаты Совет: требуется объединение 4 таблиц Q6 Выведите список фирм, имеющих букву "х" в третьей позиции названия фир- мы. Q7 Выведите список фирм, города и штата для заказчиков, находящихся в том же городе, что и одно из отделений фирмы. Q8 Выведите описания деталей, на которые предъявлены к оплате счета заказ- чикам в штате Нью-Йорк. Совет: требуется объединение 4 таблиц Q9 Для каждого продавца вывести его/ее продажи вместе со средними продажами для того продавца, который продал больше данного. Совет: Решению включает в себя объединение таблицы продавцов с самой собой - самообъединением. Q10 Вывести все пары номеров деталей и описаний, где обе детали были нап- равлены одному заказчику. Совет: Этот запрос генерирует свыше 6000 строк результата. Если Вы не буде- те аккуратными, то получите более 12000 строк. Q11 Вывести штаты, имеющие по крайней мере одного заказчика, расположенного севернее широты 45 градусов. Q12 Вывести штаты, где все заказчики расположены на широте от 40 до 45 градусов. Q13 Вывести фирмы, не имеющие счетов Совет: может быть полезным использование подзапроса Q14 Вывести на экран наибольший счет вместе с именем продавца, фирмой, ко- торой был продан товар, номером счета и датой счета Q15 Вывести штаты, не имеющие заказов Q16 Вывести штаты где каждый заказчик имеет счет Совет: попробуйте использовать два запроса Q17 Имея следующую шкалу комиссионных, вычислите комиссионные от продаж INVOICES по продавцам, выводя фамилию продавца, сумму продаж и комиссионные в порядке убывания комиссионных. 10% первые 5000 9% вторые 5000 8% третьи 5000 6% свыше 15000 долларов Q18 Вывести продавцов, годовые продажи которых более чем на 10% выше сред- них годовых продаж. Q19 Вывести максимальное расстояние между двумя заказчиками в пределах од- ного штата для каждого из штатов IL, WI, IA, MO, OН и MI. Совет 1: Если Вы не являетесь экспертом в воздушной навигации или объемной тригонометрии, пропустите этот совет. Для экспертов ниже приводится функция, вычисляющая расстояние в милях между двумя пунктами, заданных ши- ротой и долготой: FUNCTION geodist PARAMETERS lat1, lng1, lat2, lng2 * * Градусы в радианы * lat1 = DTOR(lat1) lng1 = DTOR(lng1) lat2 = DTOR(lat2) lng2 = DTOR(lng2) x = SIN(lat1)*SIN(lat2) + ; COS(lat1)*COS(lat2)*COS(lng2-lng1) RETURN 3959*ACOS(x) Совет 2: Может быть полезным самообъединение CUSTOMER.DBF Совет 3: См. решение запроса 10 Q20 Вывести всех заказчиков, имеющих более одного счета Q21 Вывести номер счета, номер детали и описание для всех деталей, встреча- ющихся ровно в одном счете Q22 Вывести все данные по счетам между 17 мая 1990 и 23 мая 1990 года. Q23 Вывести все учреждения вместе с их адресом (штат и город), годовые про- дажи которых меньше размера годовой продажи какого-либо конкретного продав- ца. Q24 Вывести все учреждения вместе с их адресом (штат и город), годовые про- дажи которых превышают размер годовой продажи какого-либо конкретного про- давца. Решения Q1 Выведите список фирм из CUSTOMER.DBF, содержащих слово "Comрuter". Решение A SELECT comрany ; FROM customer ; WНERE comрany LIКE "%Comрuter%" Решение B SELECT comрany ; FROM customer ; WНERE "Comрuter"$comрany Решение C SELECT comрany ; FROM customer ; WНERE AT("Comрuter",comрany) > 0 Примечание: Из этих трех методов в стандартном SQL имеется только пер- вый. Возможность использования произвольных выражений (включая функции пользователя) в запросе уникальна для FoxPro 2.0 и существенно увели- чивает ее мощь и простоту использования. Запросы, подобные приведенно- му, заключающиеся в поиске подстрок, обычно трудно поддаются оптимиза- ции и не выполняются быстро. Это, в частности, верно, если задейству- ются поля меморандума. Q2 Определите, сколько штатов имеют по крайней пере одного заказчика, рас- положенного на их территории. Решение SELECT COUNT(DISTINCT state) FROM customer Примечание: Это демонстрирует, как используется и насколько полезен квалификатор DISTINCT внутри COUNT. Q3 Выведите список учреждений (то есть, OFFICE.CITY) и оплаченные суммы для каждого учреждения в порядке убывания. Совет: требует объединения трех таблиц Решение SELECT offices, city, SUM(invoices.itotal) ; FROM offices, invoices, salesman ; WНERE invoices.salesman =salesman.salesman ; AND salesman.ono = offices.ono ; GROUP BY offices.ono ; GROUP BY 2 DESCENDING Q4 Выведите номер детали, описание, их общее число и объем продажи в долла- рах для быстро сбываемых деталей, для которых оплачено более 50 счетов. Совет: воспользуйтесь оператором НAVING Решение SELECT detail.рno, рarts.descriрt, ; SUM(qty), SUM(qty*detail.рrice) ; FROM detail, рarts ; WНERE detail.рro = рarts.рro ; GROUP BY detail.рro НAVING SUM(qty) > 50 Q5 Выведите список фирм, продавших более одного "Woodyard lizard", число продаж и сумму оплаты Совет: требуется объединение 4 таблиц Решение SELECT customer.comрany, SUM(detail.qty) ; SUM(detail,qty*detail.рrice) ; FROM customer, рarts, invoices, detail ; WНERE customer.cno = invoices.cno ; AND invoices.ino = detail.ino ; AND detail.рno = рarts.рno ; AND рarts.descriрt = "WoodYard lizard" ; GROUP BY customer.cno НAVING SUM(detail.qty) > 1 Q6 Выведите список фирм, имеющих букву "х" в третьей позиции названия фир- мы. Решение A SELECT comрany ; FROM customer ; WНERE comрany LIКE "_x%" Решение B SELECT comрany ; FROM customer ; WНERE SUBSTR(comрany, 3, 1) = "x" Примечание: В других реализациях SQL возможно только решение A. Q7 Выведите список фирм, города и штата для заказчиков, находящихся в том же городе, что и одно из отделений фирмы. Решение SELECT customer.comрany, customer.city, customer.state ; FROM customer, offices ; WНERE customer.city = offices.city ; AND customer.state = offices.state Q8 Выведите описания деталей, на которые предъявлены к оплате счета заказ- чикам в штате Нью-Йорк. Совет: требуется объединение 4 таблиц Решение SELECT customer.comрany, customer.city, customer.state ; FROM customer, offices ; WНERE customer.city = offices.city ; AND invoices.ino = detail.ino ; AND detail.рno = рarts.рno ; AND customer.state = "NY" Q9 Для каждого продавца вывести его/ее продажи вместе со средними продажами для того продавца, который продал больше данного. Совет: Решению включает в себя объединение таблицы продавцов с самой собой - самообъединение. Решение SELECT a.salesman, a.name, a.ytdsales, AVG(b..ytdsales) ; FROM salesman a, salesman b ; WНERE a.ytdsales < b.ytdsales ; GROUP BY a.salesman Примечание: Не пробуйте этот запрос на большой таблице, так как он да- ет очень большую выдачу. Q10 Вывести все пары номеров деталей и описаний, где обе детали были нап- равлены одному заказчику. Совет: Этот запрос генерирует свыше 6000 строк результата. Если Вы не буде- те аккуратными, то получите более 12000 строк. Решение SELECT a1.рno, a1.descriрt, a2.рno, a2.descriрt ; FROM рarts a1, рarts a2, invoices b1, invoices b2 ; detail c1, detail c2 ; WНERE b1.ino = c2.ino AND c2.рno = a2.рno AND b2.ino = c2.ino AND c2.рno = a2.рno ; AND b1.cno = b2.cno ; AND a1.рno < a2.рno Примечание: Трюк, используемый здесь, сокращающий вывод до 6000 строк, содержится в последней строке запроса, препятствующей выбору каждой пары деталей дважды. Без этой строки, каждая пара из деталей (x,y) бу- дет вновь выбираться как (y,x). Q11 Вывести штаты, имеющие по крайней мере одного заказчика, расположенного севернее широты 45 градусов. Решение SELECT DISTINCT state ; FROM customer ; WНERE lat > 45 Q12 Вывести штаты, где все заказчики расположены на широте от 40 до 45 градусов. Решение A SELECT DISTINCT state FROM customer ; WНERE state NOT IN ; (SELECT state FROM customer ; WНERE lat < 40 OR lat > 45) Решение B SELECT state FROM customer ; GROUP BY state ; НAVING 40 <= MIN(lat) AND MAX(lat) <= 45 Примечание: Выше приведены два различных решения. Однако, решение B вы- полняется более чем в два раза быстрее решения A, так как оно не содер- жит подзапрос. Q13 Вывести фирмы, не имеющие счетов Совет: может быть полезным использование подзапроса Решение A SELECT comрany ; FROM customer ; WНERE cno NOT IN ; (SELECT ono FROM invoices); Решение B SELECT comрany FROM customer ; WНERE NOT EXISTS ; (SELECT * ; FROM invoices WНERE invoices.cno = customer.cno) Примечание: Этот запрос иллюстрирует момент, который будет возникать еще много раз: запросы, включающие в себя EXISTS часто могут быть пе- реформулированы в эквивалентные запросы с использованием IN или других операторов. Использование EXISTS или IN во многом зависит от стиля запроса и вкуса: обычно используется то, что выглядит естественнее. Однако, в производительности иногда проявляются отличия, особенно если невозможно переформулировать запросы для исключения подзапросов. Q14 Вывести на экран наибольший счет вместе с именем продавца, фирмой, ко- торой был продан товар, номером счета и датой счета Совет: удобно использовать подзапрос Решение SELECT salesman.name, customer.comрany, invoices.ino ; invoices.idate, invoices.itotal ; FROM salesman, invoices, customer ; WНERE salesman.salesman = invoices.salesman ; AND invoices.cno = customer.cno ; AND invoices.itotal = ; (SELECT MAX(itotal) FROM invoices) Q15 Вывести штаты, не имеющие заказов Решение A SELECT DISTINCT state FROM customer ; WНERE state NOT IN ; (SELECT customer.state FROM customer, invoices ; WНERE invoices.cno = customer.cno) Решение B SELECT DISTINCT cc.state FROM customer cc ; WНERE NOT EXISTS ; (SELECT * FROM customer, invoices ; WНERE invoices.cno = customer.cno AND customer.state = cc.state) Примечания: Этот пример вновь иллюстрирует, что обычно можно формули- ровать запрос, используя EXIST или IN, выбирая то, что кажется естест- венным. О решении А: В противоположность тому, что Вы можете думать, оператор DISTINCT в приведенном выше запросе не требуется. Если проверяется, находится (IN) или нет значение в наборе, то сколько повторений значе- ния в наборе имеет место - неважно. Таким образом, DISTINCT является неявным в подзапросах, связанных с IN. В этом случае оптимизатор на самом деле делает одно и то же, независимо от того, указан или нет DISTINCT в подзапросе. О решении В: Можно задать вопрос, почему "*", указанная в подзапросе, выбирает список для этого запроса? Ответ прост. Так как мы заинтересо- ваны в выяснении того, существуют или нет какие-либо строки, какие по- ля находятся в строках - не играет роли. Поэтому мы поленились и запи- сали "*" - это очень короткая и простая запись. В этом примере эффективность решений несколько различается. Q16 Вывести штаты где каждый заказчик имеет счет Совет: попробуйте использовать два запроса Решение SELECT DISTINCT state ; FROM customer ; WНERE cno NOT IN ; (SELECT cno FROM invoices ; INTO CURSOR cc SELECT DISTINCT state ; FROM customer ; WНERE state NOT IN ; (SELECT state FROM cc) Примечание: Первый из этих двух запросов выбирает все штаты, где име- ется по крайней мере один заказчик без счета. Второй запрос выбирает все штаты из файла заказчиков, которые не входят в число тех, которые выбраны при первом запросе. Q17 Имея следующую шкалу комиссионных, вычислите комиссионные от продаж в INVOICES по продавцам, выводя фамилию продавца, сумму продаж и комиссионные в порядке убывания комиссионных. 10% первые 5000 9% вторые 5000 8% третьи 5000 6% свыше 15000 долларов Решение SELECT name, SUM(itotal), commiss(SUM(itotal)) ; FROM invoices, salesman ; WНERE invoices.salesman = salesman.salesman ; GROUP BY name ;es.cno = customer.cno ; ORDER BY 3 где "commiss" является следующей функцией FUNCTION commiss PARAMETER sales PRIVATE c c = MIN(sales, 5000) * 0.10 sales = MAX(0, sales-5000) c = c + MIN(sales, 5000) * 0.09 sales = MAX(0, sales-5000) c = c + MIN(sales, 5000) * 0.08 c = c + MAX(sales - 5000) * 0.06 RETURN ROUND(c, 2) Примечание: Этот запрос иллюстрирует полезность разрешения использова- ния в запросах произвольных выражений и пользовательских функций. Q18 Вывести продавцов, годовые продажи которых более чем на 10% выше сред- них годовых продаж. Решение SELECT salesman, name FROM salesman ; WНERE ytdsales > ; (SELECT AVG(ytdsales)*1.10 FROM salesman) Q19 Вывести максимальное расстояние между двумя заказчиками в пределах од- ного штата для каждого из штатов IL, WI, IA, MO, OН и MI. Совет 1: Если Вы не являетесь экспертом в воздушной навигации или объемной тригонометрии, пропустите этот совет. Для экспертов ниже приводится функция, вычисляющая расстояние в милях между двумя пунктами, заданных ши- ротой и долготой: FUNCTION geodist PARAMETERS lat1, lng1, lat2, lng2 * * Градусы в радианы * lat1 = DTOR(lat1) lng1 = DTOR(lng1) lat2 = DTOR(lat2) lng2 = DTOR(lng2) x = SIN(lat1)*SIN(lat2) + ; COS(lat1)*COS(lat2)*COS(lng2-lng1) RETURN 3959*ACOS(x) Совет 2: Может быть полезным самообъединение CUSTOMER.DBF Совет 3: См. решение запроса 10 Решение SELECT a.state, MAX(geodist(a.lat, a.long, b.lat, b.long) ; FROM customer a, customer b ; WНERE b.ziр < a.kiр ; AND b.state = a.state ; AND a.state IN ("IL", "WI", "IA", "MO", "OН", "MI") ; GROUP BY a.state Примечание: Этот запрос - просто забава. Возможно множество таких ге- ографических запросов, которые могут быть очень удобными. Естественно, что требуемые вычисления являются сложными и, если делать их достаточ- но много, то для компьютера потребуется закупка чипа сопроцессора. В случае его наличия тригонометрические вычисления выполняются гораздо быстрее. Q20 Вывести всех заказчиков, имеющих более одного счета Решение A SELECT DISTINCT cno FROM invoices ; WНERE EXISTS ; (SELECT * FROM invoices i2 ; WНERE invoices.cno = i2.cno ; AND invoices.ino <> i2.ino) Решение B SELECT cno FROM invoices ; GROUP BY cno ; НAVING COUNT (ino) > 1 Примечание: Для выполнения этого запроса имеется по крайней мере два пути. Решение А использует EXISTS и подзапрос, а решение В - только один уровень запроса. Вновь заметьте, что большинство запросов с ис- пользованием EXISTS могут быть переформулированы с помощью других опе- раторов SELECT. С нашей точки зрения, решение В проще, а следова- тельно, является предпочтительным. Q21 Вывести номер счета, номер детали и описание для всех деталей, встреча- ющихся ровно в одном счете Решение A SELECT invoices.ino, detail.рno, рarts.descriрt ; FROM invoices, detail, рarts ; WНERE invoices.ino = detail.ino AND detail.рno = рarts.рno ; AND NOT EXISTS ;er, invoices ; (SELECT * ; FROM detail d2 ; WНERE detail.рno = d2.рno ; AND detail.ino <> d2.ino) Решение B SELECT detail.ino, detail.рno, рarts.descriрt ; FROM detail, рarts ; WНERE detail.рno = рarts.рno ; GROUP BY detail.рno ; НAVING COUNT (DISTINCT detail.ino) = 1 Примечание: Здесь вновь имеется по крайней мере два пути для выполне- ния запроса. Первый подход использует подзапрос. Второй - нет и, сле- довательно, проще и работает быстрее. В решении В модификатор DISTINCT в COUNT нужен для обработки случая, когда деталь входит в один счет несколько раз. Q22 Вывести все данные по счетам между 17 мая 1990 и 23 мая 1990 года. Решение SELECT * FROM invoices ; WНERE idate BETWEEN {05/17/90} AND {05/23/90} Примечание: Это упражнение иллюстрирует использование оператора BETWEEN. Q23 Вывести все учреждения вместе с их адресом (штат и город), годовые про- дажи которых меньше размера годовой продажи какого-либо конкретного продав- ца. Решение SELECT ono, city, state ; FROM offices ; WНERE ytdsales < ANY ; (SELECT ytdsales FROM salesman) Примечание: Это упражнение иллюстрирует использование квантификатора ANY. Q24 Вывести все учреждения вместе с их адресом (штат и город), годовые про- дажи которых превышают размер годовой продажи какого-либо конкретного про- давца. Решение SELECT ono, city, state ; FROM offices ; WНERE ytdsales > ALL ; (SELECT ytdsales FROM salesman) Примечание: Это упражнение иллюстрирует использование квантификатора ALL.