Манипулирование данными в 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, что способствовало закреплению ранее полученных знаний на практике.

Поиск по сайту: