Тема: многотабличные запросы на чтение (объединения).

 

1. Вывести список всех заказов, включая номер и стоимость заказа, а также имя клиента и лимит кредита.

2. Вывести список всех служащих, включая города и регионы, в которых они работают.

3. Вывести список офисов, включая имена и должности их руководителей

4. Вывести список всех заказов, в том числе их стоимости и описания товаров.

5. Вывести список заказов стоимостью выше $25000, включая имя служащего, принявшего заказ, и имя клиента, сделавшего его.

6. Получить все комбинации служащих и офисов, где плановый объем продаж служащего больше, чем план какого-либо офиса.

7. Показать имя, офис и объем продаж каждого служащего.

8. Вывести список всех служащих и их руководителей.

9. Вывести список служащих, планы которых превышают планы их руководителей.

10. Вывести список служащих, которые работают со своими руководителями в различных офисах, включая имена и офисы как служащих, так и руководителей

11. Каковы средний плановый и средний фактический объемы продаж в нашей компании?

12. Какова сумма всех заказов, принятых Биллом Адамсом (Bill Adams)?

13. Вычислить среднюю цену товаров от производителя ACI?

14. Вычислить среднюю стоимость заказа, размещенного компанией Acme Mfg (идентификатор клиента 3103).

15. Каковы наименьший и наибольший плановые объемы продаж?

16. Каков наибольший процент выполнения плана среди всех служащих?

17. Сколько клиентов у нашей компании?

18. Сколько служащих перевыполнили план?

 

 

5. Контрольные и самостоятельные работы

А. Спроектировать БД «Библиотека», ввести данные и сформулировать 10 запросов.

 

 

create table Categories_annkos

(

catnum integer not null primary key,

catname char(15) not null

);

 

insert into Categories_annkos values (111, 'Áàçû äàííûõ');

insert into Categories_annkos values (112, 'ÎÎÏ');

insert into Categories_annkos values (113, 'Ëîãèêà');

insert into Categories_annkos values (114, 'Èñòîðèÿ');

insert into Categories_annkos values (115, 'Ôèëîñîôèÿ');

 

select * from Categories_annkos;

drop table Categories_annkos;

-------------------------------------------------

create table Author_annkos

(

autnum integer not null primary key,

autname char(15) not null,

autsurname char(15) not null,

birth_year integer,

comment char(30)

);

--drop table Author_annkos;

select * from Author_annkos;

 

insert into Author_annkos values (211, 'Ì.', 'Ãðàáåð', null, null);

insert into Author_annkos values (212, 'Äæ.Ñ.', 'Áîóìàí', null, null);

insert into Author_annkos values (213, 'Í.', 'Âåðò', null, null);

insert into Author_annkos values (214, 'Ñ.Ô.', 'Ïëàòîíîâ', null, null);

insert into Author_annkos values (215, 'Å.Ê.', 'Âîéøâèëëî', null, null);

insert into Author_annkos values (216, 'Ì.Ã.', 'Äåãòÿðåâ', null, null);

insert into Author_annkos values (217, 'Å.', 'Ìàìàåâ', null, null);

insert into Author_annkos values (218, 'À.', 'Âèøíåâñêèé', null, null);

insert into Author_annkos values (219, 'Ò.À.', 'Ïàâëîâñêàÿ', null, null);

insert into Author_annkos values (220, 'Þ.À.', 'Ùóïàê', null, null);

 

------------------------------------------------------

create table Publishing_house_annkos

(

pubnum integer not null primary key,

pubname char(20) not null,

city char(15) not null,

 

);

 

select * from Publishing_house_annkos;

 

insert into Publishing_house_annkos values (311, 'Âåñü ìèð', 'Ìîñêâà');

insert into Publishing_house_annkos values (312, 'ÂËÀÄÎÑ-ÏÐÅÑÑ', 'Ìîñêâà');

insert into Publishing_house_annkos values (313, 'Èíòåðïðàêñ', 'Ìîñêâà');

insert into Publishing_house_annkos values (314, 'Âèëüÿìñ', 'Ìîñêâà');

insert into Publishing_house_annkos values (315, 'Ëîðè', 'Ìîñêâà');

insert into Publishing_house_annkos values (316, 'ÁÕÂ', 'Ñàíêò-Ïèòåðáóðã');

insert into Publishing_house_annkos values (317, 'Ïèòåð', 'Ñàíêò-Ïèòåðáóðã');

 

---------------------------------------------------------

create table Books_annkos

(

isbn integer not null primary key,

bookname varchar(60) not null,

catnum integer not null,

pubyear integer not null,

FOREIGN KEY (catnum) references Categories_annkos (catnum),

);

select * from Books_annkos;

 

insert into Books_annkos values (411, 'SQL',111, 2003);

insert into Books_annkos values (412, 'Èñòîðèÿ Ñîâåòñêîãî ãîñóäàðñòâà',114, 2000);

insert into Books_annkos values (413, 'Ïîëíûé êóðñ ëåêöèé ïî ðóññêîé èñòîðèè',114, 2002);

insert into Books_annkos values (414, 'Ëîãèêà:Ó÷åá.äëÿ âóçîâ',113, 2001);

insert into Books_annkos values (415, 'Ëîãèêà ñ ýëåìåíòàìè ýïèñòåìîëîãèè è íàó÷íîé ìåòîäîëîãèè',113, 1994);

insert into Books_annkos values (416, 'Ïðàêòè÷åñêîå ðóêîâîäñòâî ïî SQL',111, 2001);

insert into Books_annkos values (417, 'Microsoft SQL Server 2000',111, 2002);

insert into Books_annkos values (418, 'Microsoft SQL Server 7 äëÿ ïðîôåññèîíàëîâ',111, 2001);

insert into Books_annkos values (419, 'Ñ++. Îáúåêòíî-îðèåíòèðîâàííîå ïðîãðàììèðîâàíèå',112, 2005);

insert into Books_annkos values (420, 'Ñ/ Ñ++. Ñòðóêòóðíîå ïðîãðàììèðîâàíèå',112, 2005);

drop table Books_annkos;

-----------------------------------------------------------

create table Book_Author_annkos

(

isbn integer not null references Books_annkos,

autnum integer not null references Author_annkos,

primary key(isbn,autnum)

);

 

select * from Book_Author_annkos;

 

insert into Book_Author_annkos values (411,211);--'SQL'

insert into Book_Author_annkos values (412,213);--'Èñòîðèÿ Ñîâåòñêîãî ãîñóäàðñòâà'

insert into Book_Author_annkos values (413,214);--'Ïîëíûé êóðñ ëåêöèé ïî ðóññêîé èñòîðèè'

insert into Book_Author_annkos values (414,215);--Ëîãèêà:Ó÷åá.äëÿ âóçîâ'

insert into Book_Author_annkos values (414,216);--Ëîãèêà:Ó÷åá.äëÿ âóçîâ'

insert into Book_Author_annkos values (415,215);--'Ëîãèêà ñ ýëåìåíòàìè ýïèñòåìîëîãèè è íàó÷íîé ìåòîäîëîãèè'

insert into Book_Author_annkos values (415,216);--'Ëîãèêà ñ ýëåìåíòàìè ýïèñòåìîëîãèè è íàó÷íîé ìåòîäîëîãèè'

insert into Book_Author_annkos values (416,212);--'Ïðàêòè÷åñêîå ðóêîâîäñòâî ïî SQL'

insert into Book_Author_annkos values (417,217);--'Microsoft SQL Server 2000'

insert into Book_Author_annkos values (418,217);--'Microsoft SQL Server 7 äëÿ ïðîôåññèîíàëîâ'

insert into Book_Author_annkos values (418,218);--'Microsoft SQL Server 7 äëÿ ïðîôåññèîíàëîâ'

insert into Book_Author_annkos values (419,219);--'Ñ++. Îáúåêòíî-îðèåíòèðîâàííîå ïðîãðàììèðîâàíèå'

insert into Book_Author_annkos values (419,220);--'Ñ++. Îáúåêòíî-îðèåíòèðîâàííîå ïðîãðàììèðîâàíèå'

insert into Book_Author_annkos values (420,219);--'Ñ/ Ñ++. Ñòðóêòóðíîå ïðîãðàììèðîâàíèå'

insert into Book_Author_annkos values (420,220);--'Ñ/ Ñ++. Ñòðóêòóðíîå ïðîãðàììèðîâàíèå'

--drop table Book_Author_annkos;

---------------------------------------------------------------

create table Book_Publishing_annkos

(

isbn integer not null references Books_annkos,

pubnum integer not null references Publishing_house_annkos,

primary key(isbn,pubnum)

);

 

select * from Book_Publishing_annkos;

 

insert into Book_Publishing_annkos values (411,315);

insert into Book_Publishing_annkos values (412,311);

insert into Book_Publishing_annkos values (413,311);

insert into Book_Publishing_annkos values (414,312);

insert into Book_Publishing_annkos values (415,313);

insert into Book_Publishing_annkos values (416,314);

insert into Book_Publishing_annkos values (417,316);

insert into Book_Publishing_annkos values (418,317);

insert into Book_Publishing_annkos values (419,317);

insert into Book_Publishing_annkos values (420,317);

--drop table Book_Publishing_annkos;

------------------------------------------------------------------

 

--ÇÀÏÐÎÑÛ

 

--1 Íàéòè âñå êíèãè, ó êîòîðûõ 2 àâòîðà, è âûâåñòè êíèãè, ðàçäåëû è àâòîðîâ

select bookname,catname,autsurname

from Categories_annkos,Author_annkos,Books_annkos,Book_Author_annkos

where Books_annkos.isbn in

(select isbn

from Book_Author_annkos

group by isbn

having count(autnum)=2)

and Categories_annkos.catnum=Books_annkos.catnum

and Author_annkos.autnum=Book_Author_annkos.autnum

and Books_annkos.isbn=Book_Author_annkos.isbn;

 

--2 Íàéòè âñå êíèãè, êîòîðûå íàïèñàëà Ïàâëîâñêàÿ

 

select bookname,autsurname

from Author_annkos,Books_annkos,Book_Author_annkos

where Author_annkos.autnum =219

and Author_annkos.autnum=Book_Author_annkos.autnum

and Books_annkos.isbn=Book_Author_annkos.isbn;

 

--èëè

 

select bookname,autsurname

from Author_annkos,Books_annkos,Book_Author_annkos

where Book_Author_annkos.autnum =

(select autnum

from Author_annkos

where autsurname='Ïàâëîâñêàÿ')

and Author_annkos.autnum=Book_Author_annkos.autnum

and Books_annkos.isbn=Book_Author_annkos.isbn;

 

--3 Íàéòè âñåõ àâòîðîâ, èçäàâàâøèõ êíèãè â èçäàòåëüñòâå Ïèòåð

 

select distinct autsurname

from Author_annkos,Books_annkos,Book_Author_annkos,Book_Publishing_annkos

where Book_Author_annkos.autnum in

(select autnum

from Book_Author_annkos

where isbn in

(select isbn

from Book_Publishing_annkos

where pubnum=317))

and Author_annkos.autnum=Book_Author_annkos.autnum

and Books_annkos.isbn=Book_Author_annkos.isbn

and Book_Publishing_annkos.isbn=Books_annkos.isbn;

 

--4 Íàéòè âñå êíèãè ïî ÎÎÏ

select bookname,catname

from Books_annkos,Categories_annkos

where Books_annkos.catnum =112

and Books_annkos.catnum=Categories_annkos.catnum;

 

--èëè

select bookname,catname

from Books_annkos,Categories_annkos

where Books_annkos.catnum =

(select catnum

from Categories_annkos

where catname='ÎÎÏ')

and Books_annkos.catnum=Categories_annkos.catnum;

 

--5 Âûâåñòè âñåõ àâòîðîâ, íàïèñàâøèõ 2 êíèãè

 

select bookname,catname,autsurname

from Categories_annkos,Author_annkos,Books_annkos,Book_Author_annkos

where Author_annkos.autnum in

(select autnum

from Book_Author_annkos

group by autnum

having count(isbn)=2)

and Categories_annkos.catnum=Books_annkos.catnum

and Author_annkos.autnum=Book_Author_annkos.autnum

and Books_annkos.isbn=Book_Author_annkos.isbn

order by 3;

 

--6 Íàéòè âñå èçäàòåëüñòâà, èçäàâøèå êíèãè ïî èñòîðèè

 

select distinct pubname, city

from Books_annkos,Book_Publishing_annkos,Publishing_house_annkos

where Publishing_house_annkos.pubnum in

(select pubnum

from Book_Publishing_annkos

where isbn in

(select isbn

from Books_annkos

where catnum=114))

and Books_annkos.isbn=Book_Publishing_annkos.isbn

and Book_Publishing_annkos.pubnum=Publishing_house_annkos.pubnum;

 

--7 Íàéòè âñåõ àâòîðîâ, íàïèñàâøèõ êíèãè ïî SQL

 

select distinct autsurname,autname

from Author_annkos,Books_annkos,Book_Author_annkos

where Author_annkos.autnum in

(select autnum

from Book_Author_annkos

where isbn in

(select isbn

from Books_annkos

where catnum=111))

and Books_annkos.isbn=Book_Author_annkos.isbn

and Book_Author_annkos.autnum=Author_annkos.autnum;

 

--8 Âûâåñòè âñå êíèãè, èçäàâàâøèåñÿ â Ìîñêâå

 

select distinct bookname,catname

from Categories_annkos,Author_annkos,Books_annkos,Book_Author_annkos

where Books_annkos.isbn in

(select isbn

from Book_Publishing_annkos

where pubnum in

(select pubnum

from Publishing_house_annkos

where city='Ìîñêâà'))

and Categories_annkos.catnum=Books_annkos.catnum

and Author_annkos.autnum=Book_Author_annkos.autnum

and Books_annkos.isbn=Book_Author_annkos.isbn;

 

--9 Íàéòè âñå êíèãè èçäàâàâøèåñÿ äî 2003 ãîäà, íî íå ïî Èñòîðèè

 

select distinct bookname,Categories_annkos.catname,pubyear

from Categories_annkos,Books_annkos

where pubyear < 2003

and Books_annkos.catnum <>

(select catnum

from Categories_annkos

where catname = 'Èñòîðèÿ')

and Categories_annkos.catnum=Books_annkos.catnum;

 

--10 Âûâåñòè âñå êàòåãîðèè, â êîòîðûõ áîëüøå, ÷åì 2 êíèãè

 

select catname

from Categories_annkos

where catnum in

(select catnum

from Books_annkos

group by catnum

having count(isbn)>2);

 

Б. Разработать небольшую БД «Студенты», ввести данные и реализовать запросы

I. Создать и описать 3 таблицы базы данных «Студенты»

J Первая таблица Students - Студенты

поля:

stcode – int, PK – Код студента

stsurname – char (15) – Фамилия студента

stname – char (10) – Имя студента

stnumgroup – char (10) – Номер группы студента

 

J Вторая таблица Subjects (предметы) - Предметы

поля:

sbcode – int, PK – Код предмета

subname char (50) – Название предмета

 

J Третья таблица Marks - Оценки

поля:

mnumber – int, PK – Номер оценки

stcode – int, FK – Код студента

sbcode – int, FK – Код предмета

mark – int – Оценка

II. Внести данные в каждую из таблиц, например:

Insert into Students value (111, ‘Sergunin, ‘Sergey, ‘ПС031’); 10 записей

Insert into Subjects value (2534, ‘DataBase’); 07 записей

Insert into Marks value (1, 111, 2534, 4); 10 - 12 записей

 

III. Выполнить запросы:

1. 1 билет

a. Вывести на экран номер группы, фамилию студента, а также название предмета и оценку по нему. Упорядочить по возрастанию номера группы.

b. Вывести на экран список предметов и среднюю оценку по каждому предмету.

c. Вывести на экран список тех студентов, у которых есть «2», и подсчитать количество «2» у каждого студента.

d. Вывести на экран список предметов и среднюю оценку по каждому предмету.

e. Вывести на экран список студентов, которые получили по две «5».

f. Вывести на экран список номеров групп, предметов и миниальную оценку по каждому предмету.

2. 2 билет

a. Вывести на экран номер группы, фамилию студента, а также название предмета и оценку по нему. Упорядочить названию предмета.

b. Вывести на экран список предметов и максимальную оценку по каждому предмету.

c. Вывести на экран список тех студентов, у которых есть «5», и подсчитать количество «5» у каждого студента.

d. Вывести на экран список студентов, которые получили по две «2».

e. Для студентов, получивших «4» или «5», вывести на экран номер группы, фамилию студента, а также название предмета и оценку по нему. Упорядочить по номеру группы и оценке.

f. Вывести на экран список номеров групп, предметов и среднюю оценку по каждому предмету.

3. 3 билет

a. Вывести на экран номер группы, фамилию студента, а также название предмета и оценку по нему. Упорядочить по номеру группы и названию предмета.

b. Вывести на экран список предметов и минимальную оценку по каждому предмету.

c. Вывести на экран список тех студентов, у которых есть «4», и подсчитать количество «4» у каждого студента.

d. Вывести на экран список студентов, которые получили по три «2».

e. Для студентов, получивших «5», вывести на экран номер группы, фамилию студента, а также название предмета и оценку по нему. Упорядочить по возрастанию номера группы.

f. Вывести на экран список номеров групп, предметов и среднюю оценку по каждому предмету.

4. 4 билет

a. Вывести на экран номер группы, фамилию студента, а также название предмета и оценку по нему. Упорядочить по номеру группы и названию предмета.

b. Вывести на экран список предметов и среднюю оценку по каждому предмету.

c. Вывести на экран список тех студентов, у которых есть «2», и подсчитать количество «2» у каждого студента.

d. Вывести на экран список студентов, которые получили по две «5».

e. Для студентов, получивших «2» или «5», вывести на экран N группы, фамилию студента, а также название предмета и оценку по нему. Упорядочить по N группы и оценке.

f. Вывести на экран список номеров групп, предметов и среднюю оценку по каждому предмету.