Лабораторная работа 2 Манипулирование данными в MS SQL Server Выполнил- студент гр
Работа добавлена на сайт samzan.net:
Министерство образования и науки РФ
Владимирский Государственный Университет
Кафедра УИТЭС
Лабораторная работа №2
Манипулирование данными в MS SQL Server
Выполнил:
студент гр. УИуб-109
Бачурин П.Н.
Проверил:
доц. Градусов А.Б.
Владимир 2011
Цель работы: практическое освоение команд SELECT, INSERT, UPDATE, и DELETE.
Ход работы
Открываем созданную на прошлом занятии базу данных. Ниже приведены созданные и заполненные ранее таблицы базы данных.
1. Таблица «Продавцы»:
2. Таблица «Заказы»:
3. Таблица «Заказчики»:
4. Таблица «Товар»:
Требуется выполнить следующие задания:
1. Напишите команду SELECT, которая бы вывела номер заказа, сумму и дату для всех строк из таблицы “Заказы”.
select id_zakaz, sum_zakaz, date_zakaz
from zakaz
В результате выполнения запроса получим следующую виртуальную таблицу:
Аналогично будут выполняться последующие запросы.
2. Напишите запрос, который вывел бы все строки из таблицы “Заказчики”, для которых номер продавца = 1001.
select *
from zakazchik
where id_zakazchik=1001
3. Напишите запрос, который вывел бы коды всех продавцов в текущем порядке из таблицы “Заказы”, без каких бы то ни было повторений.
select distinct id_prod
from zakaz
4. Напишите запрос к таблице “Заказчики”, который содержит всех заказчиков с рейтингом =< 100, если они не находятся во Владимире.
select *
from zakazchik
where rating_zakazchik<=100 and not (gorod_zakazchik=’Владимир’)
5. Напишите запрос, который мог бы вывести все заказы на 3 или 4 Октября 2006.
select *
from zakaz
where datepart(mm, date_zakaz)=10
and (datepart(dd, date_zakaz)=3 or datepart(dd, date_zakaz)=4)
6. Напишите запрос, который может вывести всех заказчиков, чьи имена начинаются с буквы попадающей в диапазон от A до П.
select fio_zakazchik
from zakazchik
where fio_zakazchik between ‘А’ and ‘Р’
7. Напишите запрос, который выбрал бы наименьшую сумму для каждого заказчика.
select b.fio_zakazchik, min(sum_zakaz)
from zakaz a, zakazchik b
where a.id_zakazchik=b.id_zakazchik
group by b.fio_zakazchik
8. Напишите запрос, который выбрал бы высший рейтинг в каждом городе.
select gorod_zakazchik, max(rating_zakazchik)
from zakazchik
group by gorod_zakazchik
9. Напишите запрос, который выводил бы список заказчиков в нисходящем порядке. Вывод поля рейтинг должен сопровождаться именем заказчика и его номером.
select rating_zakazchik, fio_zakazchik, id_zakazchik
from zakazchik
order by rating_zakazchik desc
10. Напишите запрос, который бы выдавал имена продавца и заказчика для каждого заказа после номера заказа.
select a.id_zakaz, b.fio_zakazchik, c.fio_prod
from zakaz a, zakazchik b, prodav c
where a.id_zakazchik=b.id_zakazchik and a.id_prod=c.id_prod
11. Напишите запрос, который бы выводил всех заказчиков, обслуживаемых продавцом с комиссионными выше 12% . Выведите имя заказчика, имя продавца, и ставку комиссионных продавца.
select b.fio_zakazchik, c.fio_prod, c.comis_prod
from zakaz a, zakazchik b, prodav c
where a.id_zakazchik=b.id_zakazchik and a.id_prod=c.id_prod
and c.comis_prod>12
12. Напишите запрос, который вычислил бы сумму комиссионных продавца для каждого заказа заказчика с рейтингом выше 100.
select b.fio_zakazchik,
sum(c.comis_prod*c.plan_prod/100) as ‘Сумма комиссионных’
from zakaz a, zakazchik b, prodav c
where a.id_zakazchik=b.id_zakazchik and a.id_prod=c.id_prod
and b.rating_zakazchik>100
group by fio_zakazchik
13. Напишите запрос, который бы вывел все пары продавцов, живущих в одном и том же городе. Исключите комбинации продавцов с ними же, а также дубликаты строк, выводимых в обратном порядке.
select distinct a.fio_prod, b.fio_prod
from prodav a, prodav b
where a.gorod_prod=b.gorod_prod and not(a.fio_prod=b.fio_prod)
14. Напишите запрос с подзапросом, который вывел бы фамилии и рейтинги всех заказчиков, у которых они имеют усредненные порядки.
select fio_zakazchik, rating_zakazchik
from zakazchik
where rating_zakazchik between (select avg(rating_zakazchik)
from zakazchik)–10
and (select avg(rating_zakazchik)
from zakazchik)+10
15. Напишите запрос, который бы выбрал общую сумму всех заказов для каждого продавца, у которого эта общая сумма больше, чем сумма наибольшего заказа в таблице “Заказы”.
select a.fio_prod, sum(b.sum_zakaz)
from prodav a, zakaz b
where a.id_prod=b.id_prod
group by a.fio_prod
having sum(b.sum_zakaz)>max(b.sum_zakaz)
16. Напишите команду SELECT, использующую подзапрос, которая выберет фамилии и коды всех заказчиков с максимальными для их городов рейтингами.
select a.id_zakazchik, a.fio_zakazchik
from zakazchik a
where a.rating_zakazchik=(select max(b.rating_zakazchik)
from zakazchik b
where a.gorod_zakazchik=b.gorod_zakazchik)
17. Напишите два запроса, которые выберут всех продавцов (по их фамилии и коду), имеющих в своих городах заказчиков, которых они не обслуживают. Один запрос - с использованием объединения и один - с подзапросом.
1 способ:
select distinct a.id_prod, a.fio_prod
from prodav a, zakaz b, zakazchik c
where not (a.id_prod=b.id_prod and b.id_zakazchik=c.id_zakazchik)
and a.gorod_prod=c.gorod_zakazchik
2 способ:
select distinct a.id_prod, a.fio_prod
from prodav a, zakazchik b
where a.gorod_prod=b.gorod_zakazchik
and not exists (select *
from prodav a, zakaz b, zakazchik c
where a.id_prod=b.id_prod
and b.id_zakazchik=c.id_zakazchik)
18. Напишите запрос, который бы использовал оператор EXISTS для извлечения всех продавцов, которые имеют заказчиков с рейтингом 300.
select fio_prod
from prodav
where exists (select *
from prodav a, zakaz b, zakazchik c
where a.id_prod=b.id_prod
and b.id_zakazchik=c.id_zakazchik
and c.rating_zakazchik=300)
19. Напишите команду, которая бы удаляла всех заказчиков, у которых рейтинг ниже, двадцати.
delete from zakazchik
where rating_zakazchik<20
20. Напишите команду, которая бы увеличила на двадцать процентов комиссионные всех продавцов имеющих план продаж выше, чем 300.
update prodav
set comis_prod=comis_prod*1.2
where plan_prod>300
21. Напишите команду, которая бы удалила все заказы заданного заказчика из таблицы “Заказы”.
delete from zakaz
where id_zakazchik=(select id_zakazchik
from zakazchik
where fio_zakazchik=’Негодяй’)
22. Напишите команду, которая бы увеличила рейтинг всех заказчиков в Москве на 100.
update zakazchik
set rating_zakazchik=rating_zakazchik+100
where gorod_zakazchik=’Москва’
23. Продавец X оставил компанию. Переназначьте его заказчиков продавцу Y.
update zakaz
set id_prod=(select id_prod
from prodav
where fio_prod=’Y’)
Вывод: в ходе работы решены 23 задачи с использованием SELECT, INSERT, UPDATE, и DELETE, что способствовало закреплению ранее полученных знаний на практике.
2. Что мы знаем о сове, или Какая птица самая умная
3. это собрания официальных представителей выступающих от имени государств которые созываются на ограниченн
4. Лабораторна робота 1'4 Лабораторна робота 2'11 Ла
5. Лабораторная работа 3 Определение модуля Юнга на интерферометре Майкельсона
6. Виробничо-комерційна діяльність ресторану Гомін Карпат
7. доклад толкование норм права Работа студента Юридического факультета Первого курса Пе
8. Назовите общие свойства сенсорных систем
9. Как работать над ошибками Адресована учащимся 57 классов общеобразоват
10. Развитие семантического компонента у дошкольников с общим недоразвитием речи
11. і У 1974 році 4 країни Італія Франція Німеччина і Швейцарія заснували Європейську рок н рольну асоціацію
12. часа в день. У отдельных категорий населения еще больше
13. Sollst Kochenk Gro~mutter der LebkuchenmnnUnd die lte ihn n und ntwortet-Wir hben j Ehre Woche Mehl Nein Und du Gro~mutter gehe uf die Website поскреби амбару помети
14. темах. 6.За оскорбления- личностные по признаку национальной и религиозной принадлежности наказание бан бе
15. Этапы расчета заработной платы.html
16. Тема- Анаэробная клостридиальная инфекция- газовая гангрена этиология патогенез профилактика клиника ле
17. КОНТРОЛЬНАЯ РАБОТА ПО ДИСЦИПЛИНЕРусский язык и культура речи Выполнила-Студентка1
18. Конспект лекций по курсу ~~Электрические станции и подстанции
19. ТЕМАТИЧНІ МОДЕЛІ ФУНКЦІОНУВАННЯ БАГАТОПОТОКОВИХ ГНУЧКИХ АВТОМАТИЗОВАНИХ ЛІНІЙ І ОПТИМІЗАЦІЯ ПАРАМЕТР.
20. 1020 год Бюджет Договор 1