Мартин Грубер - Понимание SQL (991940), страница 49
Текст из файла (страница 49)
Курсоры бывают обьявлеными (если определяют запрос в котором будут содержаться), открытыми (если выполняют запрос), изакрытыми (если удаляют вывод запроса из курсора). Если курсор открыт, командаFETCH, используется чтобы перемещать его по очереди к каждой строке вывода запроса.* Курсоры являются модифицируемыми или только-чтение. Чтобы стать модифицируемым, курсор должен удовлетворять всем критериям которым удовлетворяетпросмотр; кроме того, он не должен использовать предложений ORDER BY илиUNION, которые в любом случае не могут использоваться просмотрами. Не модифицируемый курсор является курсором только-чтение.* Если курсор модифицируемый, он может использоваться для определения, какие строки задействованы вложенными командами UPDATE и DELETE через предложение WHERE CURRENT OF.
DELETE или UPDATE должны быть вне той таблицы ккоторой курсор обращается в запросе.* SQLCODE должен быть обьявлен как переменная числового типа для каждойпрограммы которая будет использовать вложенный SQL. Его значение установливается автоматически после выполнения каждой команды SQL.* Если команда SQL выполнена как обычно, но не произвела вывода или ожидаемого изменения в базе данных, SQLCODE = 100. Если команда произвела ошибку,SQLCODE будет равняться некоторому аппаратно-определенному отрицательномучислу, которое описывает ошибку. В противном случае, SQLCODE = 0.* Предложение WHENEVER может использоваться для определения действиякоторое нужно предпринять когда SQLCODE = 100 (не найдено) или когда SQLCODEравен отрицательному числу (SQLERROR). Действием может быть или переход к некоторой определенной метке в программе (GOTO <label>) или отсутствие какого-либодействия вообще (продолжить).
Последнее, установлено по умолчанию.* Числовые переменные могут также использоваться как переменные indicator.Переменные indicator следуют за другим именами переменных в команде SQL, безкаких бы то ни было посторонних символов кроме (необязательного) словаINDICATOR.* Обычно, значение переменной indicator = 0. Если команда SQL пытается поместить NULL значение в главную переменную которая использует indicator, indicatorбудет установлен в отрицательное значение. Этот факт можно использовать, чтобыпредотвращать ошибки и для помечания NULL значений SQL для специальной обработки их в главной программе.* Переменная indicator может использоваться для вставки NULL значений в команды SQL — INSERT или UPDATE. Она также может принимать положительное значение указывающее на длинну отбрасываемой части строки не поместившейся впредельные границы какой-нибудь переменной, куда эта строка помещалась.РАБОТА С SQLОбратите внимание: Ответы для этих упражнений написаны в псевдокодах, являющихся английским языком описания логики, которой должна следовать программа.
Это сделано для того, чтобы помомочь читателям, которые могут быть незнакомыс Паскалем (или любым другим языком). Кроме того, это лучше сфокусирует вашевнимание на включемых понятиях, опуская частности того или другого языка. Чтобыне противоречить нашим примерам, стиль псевдокода будет напоминать Паскаль.Мы опустим из программ все, что не относится напрямую к рассматриваемымвопросам, например, определение устройств ввода-вывода, подключение к базе данных, и так далее. Конечно, имеется много способов чтобы выполнять такие упражнения; и совсем не обязательно, что представленые варианты решений являютсясамыми удачными.1. Разработайте простую программу, которая выберет все комбинации полей snum иcnum из таблиц Порядков и Заказчиков и выясните, всегда ли предыдущая комбинация такая же как последующая. Если комбинация из таблицы Порядков не найдена в таблице Заказчиков, значе ние поля snum для этой строки будет измененона удовлетворяющее условию совпадения.
Вы должны помнить, что курсор с подзапросом — модифицируем (ANSI ограничение, также применимо к просмотрам, ичто базисная целостность базы данных это не тоже самое что про верка на ошибку(т.е. первичные ключи уникальны, все поля cnums в таблице Порядков правильны,и так далее). Проверьте раздел обьявлений, и убедитесь что там обьявлены всеиспользуемые курсоры.2. Предположим, что ваша программа предписывает ANSI запрещение курсоры илипросмотры использующие модифицируемые подзапросы.
Как вы должны изменитьвышеупомянутую программу?3. Разработайте программу, которая подсказывает пользователям изменить значенияполя city продавца, автоматически увеличивает комиссионные на .01 для продавца,переводимого в Барселону и уменьшает их на .01 для продавца, переводимого вСан Хосе. Кроме того, продавец, находящийся в Лондоне, должен потерять .02 изсвоих комиссионных, независимо от того, меняет он город или нет, в то время какпродавец, не находящийся в Лондоне, должен иметь увеличение комиссионных на.02. Изменение в комиссионных, основывающееся на нахождении продавца в Лондоне, может применяться независимо от того, куда тот переводится.
Выясните, могут ли поле city или поле comm содержать NULL значения, и обработайте их, какэто делается в SQL. Предупреждение: эта программа имеет некоторые сокращения.(См. Приложение A для ответов.)ПриложениеAОТВЕТЫ ДЛЯУПРАЖНЕНИЙГлава 11. cnum2. rating3. Другим словом для строки является запись. Другим словом для столбца являетсяполе.4. Потому что строки, по определению, находятся без какого либо определенногоупорядочения.Глава 21.2.3.4.Символ (или текст) и номерНетЯзык Манипулирования Данными (ЯЗЫК DML)Это слово в SQL имеет специальное учебное значениеГлава 31.SELECT onum, amt, odateFROM Orders;2.SELECT *FROM CustomersWHERE snum = 1001;3.SELECT city, sname, snum, commFROM Salespeople;4.SELECT rating, cnameFROM CustomersWHERE city = 'SanJose';5.SELECT DISTINCT snumFROM Orders;Глава 41.SELECT *FROM OrdersWHERE amt > 1000;2.SELECT sname, cityFROM SalespeopleWHERE city = 'London' AND comm > .10;3.SELECT *FROM CustomersWHERE rating > 100 OR city = 'Rome';илиSELECT *FROM CustomersWHERE NOT rating < = 100 OR city = 'Rome';илиSELECT *FROM CustomersWHERE NOT (rating < = 100 AND city < > 'Rome');Могут быть еще другие решения.4.onum30013003300530093007300830103011amt18.69767.195160.451713.2375.754723.001309.959891.88odate10/03/199010/03/199010/03/199010/04/199010/04/199010/05/199010/06/199010/06/1990cnum20082001200320022004200620042006snum100710011002100310021001100210015.onum30013003300630093007300830103011amt18.69767.191098.161713.2375.754723.001309.959891.88odate10/03/199010/03/199010/03/199010/04/199010/04/199010/05/199010/06/199010/06/1990cnum20082001200820022004200620042006snum100710011007100310021001100210016.SELECT *FROM Salespeople;Глава 51.SELECT *FROM OrdersWHERE odate IN (10/03/1990,10/04/1990);2.
иSELECT *FROM OrdersWHERE odate BETWEEN 10/03/1990 AND 10/04,1990;3.SELECT *FROM CustomersWHERE snum IN (1001,1004);4.SELECT *FROM CustomersWHERE cname BETWEEN 'A' AND 'H';ПРИМЕЧАНИЕ: В ASCII базовой системе Hoffman не будет выведен из-за конечныхпробелов после H. По той же самой причине вторая граница не может быть G, поскольку она не выведет имена Giovanni и Grass. G может использоваться в сопровождении с Z, так чтобы следовать за другими символами в алфавитном порядке, ане предшествовать им, как это делают пробелы.5.SELECT *FROM CustomersWHERE cname LIKE 'C%';6.SELECT *FROM OrdersWHERE amt <> 0 AND (amt IS NOT NULL);илиSELECT *FROM OrdersWHERE NOT (amt = 0 OR amt IS NULL);Глава 61.SELECT COUNT(*)FROM OrdersWHERE odate = 10/03/1990;2.SELECT COUNT (DISTINCT city)FROM Customers;3.SELECT cnum, MIN (amt)FROM OrdersGROUP BY cnum;4.SELECT MIN (cname)FROM CustomersWHERE cname LIKE 'G%';5.SELECT city, MAX (rating)FROM CustomersGROUP BY city;6.SELECT odate, count (DISTINCT snum)FROM OrdersGROUP BY odate;Глава 71.SELECT onum, snum, amt * .12FROM Orders;2.SELECT 'For the city ', city, ', the highest rating is ', MAX (rating)FROM CustomersGROUP BY city;3.SELECT rating, cname, cnumFROM CustomersORDER BY rating DESC;4.SELECT odate, SUM (amt)FROM OrdersGROUP BY odateORDER BY 2 DESC;Глава 81.SELECT onum, cnameFROM Orders, CustomersWHERE Customers.cnum = Orders.cnum;2.SELECT onum, cname, snameFROM Orders, Customers, SalespeopleWHERE Customers.cnum = Orders.cnum AND Salespeople.snum = Orders.snum;3.SELECT cname, sname, commFROM Salespeople, CustomersWHERE Salespeople.snum = Customers.snum AND comm * .12;4.SELECT onum, comm * amtFROM Salespeople, Orders, CustomersWHERE rating > 100 ANDOrders.cnum = Customers.cnum ANDOrders.snum = Salespeople.snum;Глава 91.SELECT first.sname, second.snameFROM Salespeople first, Salespeople secondWHERE first.city = second.city AND first.sname < second.sname;Псевдонимам нет необходимости иметь именно такие имена.2.SELECT cname, first.onum, second.onumFROM Orders first, Orders second, CustomersWHERE first.cnum = second.cnum ANDfirst.cnum = Customers.cnum ANDfirst.onum < second.onum;Ваш вывод может иметь некоторые отличия, но в вашем ответе все логические компоненты должны быть такими же.3.SELECT a.cname, a.cityFROM Customers a, Customers bWHERE a.rating = b.rating AND b.cnum = 2001;Глава 101.SELECT *FROM OrdersWHERE cnum = (SELECT cnumFROM CustomersWHERE cname = 'Cisneros');илиSELECT *FROM OrdersWHERE cnum IN (SELECT cnumFROM CustomersWHERE cname = 'Cisneros');2.SELECT DISTINCT cname, ratingFROM Customers, OrdersWHERE amt > (SELECT AVG (amt)FROM Orders)AND Orders.cnum = Customers.cnum;3.SELECT snum, SUM (amt)FROM OrdersGROUP BY snumHAVING SUM (amt) > (SELECT MAX (amt)FROM Orders);Глава 111.SELECT cnum, cnameFROM Customers outerWHERE rating = (SELECT MAX (rating)FROM Customers innerWHERE inner.city = outer.city);2.
Решение с помощью соотнесенного подзапроса:SELECT snum, snameFROM Salespeople mainWHERE city IN (SELECT cityFROM Customers innerWHERE inner.snum <> main.snum);3. Решение с помощью объединения:SELECT DISTINCT first.snum, snameFROM Salespeople first, Customers secondWHERE first.city = second.city AND first.snum <> second.snum;Соотнесенный подзапрос находит всех заказчиков, не обслуживаемых даннымпродавцом, и выясняет: живет ли кто-нибудь из них в его городе. Решение с помощьюобьединения является более простым и более интуитивным. Оно находит случаи, гдеполя city совпадают, а поля snums нет. Следовательно, обьединение является болееизящным решением для этой проблемы, чем то, которое мы исследовали до этого.Имеется еще более изящное решение с помощью подзапроса, с которым Вы столкнетесь позже.Глава 121.SELECT *FROM Salespeople firstWHERE EXISTS (SELECT *FROM Customers secondWHERE first.snum = second.snum AND rating = 300);2.SELECT a.snum, sname, a.city, commFROM Salespeople a, Customers bWHERE a.snum = b.snum AND b.rating = 300;3.SELECT *FROM Salespeople aWHERE EXISTS (SELECT *FROM Customers bWHERE b.city = a.city AND a.snum <> b.snum);4.SELECT *FROM Customers aWHERE EXISTS (SELECT *FROM Orders bWHERE a.snum = b.snum AND a.cnum <> b.cnum)Глава 131.SELECT *FROM CustomersWHERE rating >= ANY (SELECT ratingFROM CustomersWHERE snum = 1002);2.cnum2002200320042008cnameGiovanniLiuGrassCisneroscityRomeSan JoseBerlinSanJoserating200200300300snum10031002100210073.SELECT *FROM SalespeopleWHERE city <> ALL (SELECT cityFROM Customers);илиSELECT *FROM SalespeopleWHERE NOT city = ANY (SELECT cityFROM Customers);4.SELECT *FROM OrdersWHERE amt > ALL (SELECT amtFROM Orders a, Customers bWHERE a.cnum = b.cnum AND b.city = 'London');5.SELECT *FROM OrdersWHERE amt > (SELECT MAX (amt)FROM Orders a, Customers bWHERE a.cnum = b.cnum AND b.city = 'London');Глава 141.SELECT cname, city, rating, 'High Rating'FROM CustomersWHERE rating >= 200UNIONSELECT cname, city, rating, ' Low Ratlng'FROM CustomersWHERE rating < 200;илиSELECT cname, city, rating, 'High Rating'FROM CustomersWHERE rating >= 200UNIONSELECT cname, city, rating, ' Low Rating'FROM CustomersWHERE NOT rating >= 200;Различие между этими двумя предложениями — в форме второго предиката.
![](https://s.studizba.com/z.php?f=/templates/si/i/noavatar.png&w=100&h=100&t=1"){kind=avatar}
