Если у вас возникли сложности с курсовой, контрольной, дипломной, рефератом, отчетом по практике, научно-исследовательской и любой другой работой - мы готовы помочь.
Предоплата всего
Подписываем
Министерство образования и науки РФ
Владимирский Государственный Университет
Кафедра УИТЭС
Лабораторная работа №2
Выполнил:
студент гр. УИуб-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, что способствовало закреплению ранее полученных знаний на практике.